Oracle——PL/SQL 语句

目录:　　1、什么是PL/SQL

　　　　 2、PL/SQL 语法基础

　　 　　3、PL/SQL 实例

　　　　　　　一、过程 实例

　　　　　　   二、函数 实例

　　　　　　   三、游标的使用 实例

　　　　　　　四、动态sql 实例

　　　　　　　五、触发器 实例

---

 1、什么是PL/SQL

　　PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。

2、PL/SQL 语法基础
　　参考：Link

3、PL/SQL 实例
一、过程实例
（1）模拟一次 100块钱 的转账

set serveroutput on; --每次会话开始打开这个开关来显示输出信息
declare 
v_num number :=0;
begin 
    update t_account set AResidualAmount = AResidualAmount-100 where anumber='201330340121';
    
    v_num:=SQL%rowcount;
    if v_num <= 0 then
        dbms_output.put_line('201330340106 账户扣款不成功，转账失败！');
        return ;
    end if;

    update t_account set AResidualAmount = AResidualAmount+100 where anumber='201330340106';
    if v_num <= 0 then
         dbms_output.put_line('201330340106 账户入款不成功，转账失败！');
         rollback;
         return;
    end if;
    
    insert into t_trade_detail 
      values(SEQ_TRADE_DETAIL.nextval,'201330340121',100,sysdate,'3','201330340106','转出备注');
    if v_num <= 0 then
         dbms_output.put_line('201330340121 账户扣款明细写入不成功，转账失败！');
         rollback;
         return;
    end if;

    insert into t_trade_detail
      values(SEQ_TRADE_DETAIL.nextval,'201330340106',100,sysdate,'2','201330340121','转入备注'); 
    
    if v_num <= 0 then
         dbms_output.put_line('201330340106 账户入款明细写入不成功，转账失败！');
         rollback;
         return;
    end if;
    
    dbms_output.put_line('201330340121 向 201330340106 账户成功！');
    commit;
exception 
       when others then 
             dbms_output.put_line('转账发生错误：'||SQLERRM);
       rollback;
end;  

（2）根据员工的编号，输出该员工的基本信息（姓名，雇佣日期，薪水，提成比例）

create or replace procedure p_queryemp(i_empid employees.employee_id%type)
as
v_ename varchar(50);
v_date employees.hire_date%type;
v_salary employees.salary%type;
v_commission employees.commission_pct%type;

begin
  select first_name||' '||last_name ename,hire_date,salary,commission_pct
    into v_ename ,v_date,v_salary ,v_commission
    from employees where employee_id = i_empid;
    dbms_output.put_line(v_ename ||v_date||v_salary ||v_commission);
exception
when no_data_found then
    dbms_output.put_line('没有找到员工信息！');
when others then
    dbms_output.put_line(sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_queryemp(1100);

二、函数实例

（1）根据商品ID查询商品价格

create or replace function f_queryprice(i_gid t_goods.gid%type) return number
as
v_price number(8,2);
begin
  select gprice* nvl(gdiscount,1) into v_price
  from t_goods where i_gid=gid;
  return v_price;
  
exception
    when no_data_found then
    return -1;
  when others then 
  dbms_output.put_line(sqlerrm);
  return -2;
  
end;
/
show error;

set outputserver on;
select f_queryprice('G04001') from dual;

（2）自动生成插入表的数据ID

create or replace function f_createomid_procure return varchar2
as
v_pmid varchar2(12);
v_num number;
v_max t_main_procure.pmid%type;
begin
    v_pmid:='P'||to_char(sysdate,'yymmdd');
    
    select count(pmid) into v_num
      from t_main_procure where to_char(sysdate,'yymm')=to_char(pdate,'yymm');
  if v_num=0 then
    v_pmid := v_pmid||'00001';
  else
    select max(pmid) into v_max
      from t_main_procure where to_char(sysdate,'yymm')=to_char(pdate,'yymm');
     v_pmid :=v_pmid || trim(to_char(to_number(substr(v_max,8,5))+1,'00000'));
  end if;
  
  return v_pmid;
exception
  when others then
    dbms_output.put_line(sqlerrm);
    return null;
end;
/
show error;

（3）实现采购单编码规则：Pyymmdd0000N,p160712000001

create or replace function f_createomid return varchar2
as
v_omid varchar2(12);
v_num number;
v_max t_main_order.omid%type;
begin
  v_omid:=to_char(sysdate,'yyyymm');
  select count(omid) into v_num
    from t_main_order where to_char(odate,'yyyymm')=to_char(sysdate,'yyyymm');
  if v_num=0 then
    v_omid:=v_omid||'000001';
  else
  select max(omid) into v_max
    from t_main_order where to_char(odate,'yyyymm')=to_char(sysdate,'yyyymm');
  v_omid:=v_omid||trim((to_char(to_number(substr(v_max,7,6))+1,'000000')));
  end if;
  return v_omid;
  
exception 
  when others then
  return null;
end;
/
show error;



insert into t_main_procure values(f_createomid_procure,'000001',sysdate,null,1,'备注');
select * from t_main_procure;
select * from t_order_items;

（4）实现输入一个员工编号，返回他应该缴的个人所得税

create or replace function f_tax(i_empid employees.employee_id%type)return number
as
v_sal number;
v_tax number;
v_excess number;
begin
select salary into v_sal
    from employees where employee_id = i_empid;
v_excess:=v_sal-3500;
if(v_sal<=0) then v_tax:=0; 
elsif(v_sal<=1500) then 
  v_tax:=v_excess*0.03;
elsif(v_sal<=4500) then 
  v_tax:=v_excess*0.1-105;
elsif(v_sal<=9000) then 
  v_tax:=v_excess*0.2-555;
elsif(v_sal<=35000)then
  v_tax:=v_excess*0.25-1005;
elsif(v_sal<=55000)then
  v_tax:=v_excess*0.3-2755;
elsif(v_sal<=80000)then
  v_tax:=v_excess*0.35-5505;
else
  v_tax:=v_excess*0.45-13505;
end if;
return v_tax;

exception
 when others then 
  return null;
end;
/
show error;
select salary ,f_tax(201) from employees where employee_id=201;

（5）编写一个程序，实现输入一个员工编号，返回它的工资

create or replace function f_sal(i_empid employees.employee_id%type) return number
as
v_sal number;
begin
  select salary into v_sal
    from employees where employee_id = i_empid;
  return v_sal;
  
exception
  when no_data_found then
    return -1;
end;
/
show error;

select f_sal(100) from dual;

三、游标的使用实例

for版本

create or replace procedure p_querydepartmentemp(i_depno in employees.department_id%type)
as
v_ename varchar(50);
v_date employees.hire_date%type;
v_salary employees.salary%type;
v_commission employees.commission_pct%type;
cursor cur_emp is select first_name||' '||last_name ename,hire_date,salary,commission_pct
    from employees where department_id = i_depno;
v_record_emp cur_emp%rowtype;
begin
    for v_record_emp in cur_emp loop
        --dbms_output.put_line(v_ename||' ' ||v_date||' '||v_salary ||' '||v_commission); 
        dbms_output.put_line(v_record_emp.ename||' ' ||v_record_emp.hire_date||' '||v_record_emp.salary ||' '||v_record_emp.commission_pct);
    end loop;
exception
when no_data_found then
    dbms_output.put_line('没有找到员工信息！');
when others then
    dbms_output.put_line(sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_querydepartmentemp(50); 

while版本

create or replace procedure p_querydepartmentemp(i_depno in employees.department_id%type)
as
v_ename varchar(50);
v_date employees.hire_date%type;
v_salary employees.salary%type;
v_commission employees.commission_pct%type;
cursor cur_emp is select first_name||' '||last_name ename,hire_date,salary,commission_pct
    from employees where department_id = i_depno;
v_record_emp cur_emp%rowtype;
begin
    open cur_emp;
    --fetch cur_emp into v_ename ,v_date,v_salary ,v_commission;
    fetch cur_emp into v_record_emp;
    while(cur_emp%found) loop
        --dbms_output.put_line(v_ename||' ' ||v_date||' '||v_salary ||' '||v_commission); 
        dbms_output.put_line(v_record_emp.ename||' ' ||v_record_emp.hire_date||' '||v_record_emp.salary ||' '||v_record_emp.commission_pct);
        fetch cur_emp into v_record_emp;
    end loop;
    close cur_emp;
exception
when no_data_found then
    dbms_output.put_line('没有找到员工信息！');
when others then
    dbms_output.put_line(sqlerrm);
end;
/
show error;

set serveroutput on;
exec p_querydepartmentemp(7); ---?

四、动态sql 实例

（1）在不知不清楚表名，列名的情况下使用删除表的存储过程  （使用 sql 执行 drop 语句 （DDL语句） ）

create or replace procedure p_droptable(i_tablename varchar)
as
v_sql varchar2(100);
begin
  v_sql:='drop table'||i_tablename;
  execute immediate v_sql;
end;
/
show error;

 （2）使用动态sql+游标 创建用户

create or replace procedure p_createuser
as
cursor cur_uiid is select uiid from t_user;
v_uiid t_user.uiid%type;
begin
  open cur_uiid;
  fetch cur_uiid into v_uiid;
  while(cur_uiid%found) loop
    execute immediate 'create user '|| v_uiid ||' identified by 123';
    execute immediate 'grant connect,resource to '||v_uiid;
    dbms_output.put_line(v_uiid||'创建成功');
    fetch cur_uiid into v_uiid;
  end loop;
  close cur_uiid;
exception
  when others then
    dbms_output.put_line(sqlerrm);
end;
/
show error;

五、触发器 实例

触发器：特殊的存储过程，不需要人工调用，在满足触发器的条件是自动触发

操作：针对数据库级：数据库级触发器

         针对表对象： 表级触发器

         简单视图时可以修改数据，用一个替换的触发器来替代修改视图的语句，替代触发器

实例：

（1）订单明细的 增删改
① 在订单中，当在订单明细里面添加一条明细记录，订单主表的总金额增加

create or replace trigger tr_insertorderitems 
  after insert on t_order_items
  for each row 
begin
  update t_main_order set oamount= nvl(oamount,0)+ nvl(:new.oprice,0)*nvl(:new.onum,0)
    where omid=:new.omid;
end;
/
show error;

②当订单的明细记录有删除的时候，订单主表的总金额要减少

create or replace trigger tr_deleteorderitems
  after delete on t_order_items
  for each row
begin
  update t_main_order set oamount= nvl(oamount,0)-nvl(:old.oprice,0)*nvl(:old.onum,0)
  where omid = :old.omid;
end;
/
show error;

③当订单的明细的数量和单价修改是，订单主表的总金额要发生变化

create or replace trigger tr_updateorderitems
  after update on t_order_items
  for each row
begin
  update t_main_order set oamount= nvl(oamount,0)+
  (nvl(:new.oprice,0)*nvl(:new.onum,0)-nvl(:old.oprice,0)*nvl(:old.onum,0))
  where omid = :new.omid;
end;
/
show error;

测试（1）：

insert into t_order_items values('201607000003','G04002',f_queryprice('G04002'),2,null);
delete from t_order_items where omid='201607000003' and gid='G04002';
update t_order_items set onum=onum-1, oprice=oprice-10 where omid='201607000003' and gid='G04001';

（2）已经审核的单据不能添加删除明细

create or replace trigger tr_checkinsertorderitems
  before insert on t_order_items
  for each row 
declare
  v_state t_main_order.ostate%type;
begin
  select ostate into v_state from t_main_order where omid = :new.omid;
  if v_state<>'1' then 
    ---抛出异常
    raise_application_error(-20001,'已经审核的订单无法添加明细！');
  end if;
end;
/
show error;

（3）只要一个商品获得评价为差评，价格就降低一个百分点

create or replace trigger tr_insertevaluation
  after insert on t_user_evaluation
  for each row
declare 
v_etype t_user_evaluation.ueid%type;
begin
  if :new.uetype='C' then 
    update t_goods set gdiscount = nvl(gdiscount,0)*0.99 
      where gid = :new.gid;
  end if;
end;
/
show error;

----测试语句
insert into t_user_evaluation 
values(to_char(sysdate,'yymmdd')||trim(to_char(seq_ueid.nextval,'00')),'201607000003','G04001',sysdate,'C','这东西，烂！');

　提示：如果多个触发器满足：针对同一个对象，同样是before 或者after，同样是行级或者语句级触发器，则不同的操作可以把这些触发器组合在一起

（4）对（1）的改进，将三个对订单详细表的修改集中到一个触发器

create or replace tr_orderitems
 after insert or update or delete on t_order_items
 for each row
 begin
  if inserting then
  
  elsif deleting then
  
  elsif updating then 
  
  end if;
 end;

注意：触发器要注意避免多个触发器导致操作的重复