目录: 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 语法基础
参考：
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)asv_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);exceptionwhen 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 numberasv_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 varchar2asv_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 varchar2asv_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 numberasv_sal number;v_tax number;v_excess number;beginselect 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 numberasv_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)asv_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;exceptionwhen 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)asv_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;exceptionwhen 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 语句 （） ）
create or replace procedure p_droptable(i_tablename varchar)asv_sql varchar2(100);begin v_sql:='drop table'||i_tablename; execute immediate v_sql;end;/show error;
（2）使用动态sql+游标 创建用户
create or replace procedure p_createuserascursor 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 rowbegin 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 rowbegin 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 rowdeclare 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;
注意：触发器要注意避免多个触发器导致操作的重复