Oracle PL/SQL存储过程，函数，包，触发器的施用

PL/SQL高级编程部分
无名块不存储在数据库中，并且不能从其他的PL/SQL块中调用
有名块存储在数据库数据字典中，可以在客户端与服务器端的任何工具和任何应用中运行
(1).存储过程

(2).存储函数

(3).包

(4).触发器
过程执行某一动作，函数计算一个值，包将相关的过程函数逻辑上捆绑在一起，触发器隐式执行某一动作
存储过程和函数是把一个PL/SQL块编译后存进数据库数据字典中，以后可以重复使用的模式对象
注意：在存储过程和函数中的形式参数及返回结果如果是字符型，不要指定指定长度

1.存储过程
(1).创建存储过程

Sql代码??

1. create?[OR?REPLACE]?PROCEDURE?过程名[(参数名[IN|OUT|IN?OUT]数据类型...)]??
2. {IS?|?AS}??
3. ????[说明部分]??
4. BEGIN??
5. ????语句系列??
6. [EXCEPTION?出错处理]??
7. END[过程名];??

?

参数说明：

IN 输入参数：用来从调用环境中向存储过程传递值，即IN模式参数不能出现在赋值语句式边
OUT输出参数：用来从存储过程中返回值给调用者，在过程体内，必须给OUT模式参数赋值，OUT模式参数
可以出现在赋值语句的左边.没有值的OUT模式参数不能出现赋值语句的右边.
IN OUT：输入参数，输出参数.即可从调用者向存储过程传递值，过程执行后又可返回改变后的值给调用者.?
eg:
给某一指定的员工涨指定数量的工资，该存储过程有两个形式参数：emp_id和v_increase，没有指定参数
的模式，默认是IN 模式.

Sql代码??

1. create?procedure?raise_salary(emp_id?integer,?v_increase?real)?is??
2. BEGIN??
3. ??update?emp?set?sal?=?sal?+?v_increase?where?empno?=?emp_id;??
4. ??commit;??
5. end?raise_salary;??

?
根据给定的员工号(通过IN模式参数代入)，用OUT模式参数返回员工的姓名,工资和奖金信息.

Sql代码??

1. create?or?replace?procedure?query_emp(v_emp_no???IN?emp.empno%type,??
2. ??????????????????????????????????????v_emp_name?out?emp.ename%type,??
3. ??????????????????????????????????????v_emp_sal??out?emp.sal%type?v_emp_comm?out?emp.comm%type)?is??
4. BEGIN??
5. ??select?ename,?sal,?comm??
6. ????into?v_emp_name,?v_emp_sal,?v_emp_comm??
7. ????from?emp??
8. ???where?empno?=?v_emp_no;??
9. end?query_emp;??

?

2.存储函数
(1).创建存储函数的语法如下：

?

Sql代码??

1. create?[or?replace?]function?函数名[(参数名[in]数据类型...)]?return?数据类型??
2. {is|as}??
3. ????[说明部分]??
4. BEGIN??
5. ????语句序列??
6. ????return?(表达式)??
7. [EXCEPTION?例外处理程序]??
8. END?函数名;??

?
编写一个函数，计算几个人的平均工资，并在函数体的循环过程中输出结果

Sql代码??

1. create?or?replace?function?average_sal(v_n?in?number(3))?return?number??
2. is???
3. ???cursor?c_emp?is?select?empno,sal?from?emp;??
4. ???v_total_sal?emp.sal%type:=0;??
5. ???v_counter?number;??
6. ???v_emp_no?emp.empno%type??
7. BEGIN??
8. ???FOR?r_emp?in?c_emp?loop?exit?when?c_emp%?rowcount>v_n?or?c_emp%notfound;??
9. ???????????v_total_sal:=v_total_sal+r_emp.sal;??
10. ???????????v_counter:=c_emp%rowcount;??
11. ???????????v_emp_no:=r_emp.empno;??
12. ???????????dbms_output.put_line('loop='||v_counter||';empno='||v_emp_no);??
13. ???end?loop;??
14. ???return?(v_total_sal/v_counter);??
15. end?average_sal;??

?

注意存储过程与函数的区别

1...返回值不同:存储函数有零个或多个参数，函数可以有IN参数，但不能有OUT参数。函数只返回一个值，函数值的返回是靠return 子句返回的
2...存储过程有零个或多个参数，地程可以有IN参数，在调用过程时通过参数列表接受IN参数的输入。过程不返回值，其返回值是靠OUT参数带出来的
过程可以有零个或多个OUT参数返回结果
调用过程的语句可以作为单独的可执行语句在PL/SQL块中单独出现
过程名(实际参数1，实际参数2....)
函数可以在任何表达式能够出现的地方被调用，调用函数的语句不能作为可执行语句单独出现在PL/SQL块中
变量名：函数名(实际参数1，实际参数2....);

?

存储过程的调用
(1).在PL/SQL块中的调用
eg:

Sql代码??

1. DECLARE??
2. ??V_empno?emp.empno%type?:=?7777;??
3. ??v_ename?emp.ename%type;??
4. ??v_sal???emp.sal%type;??
5. ??v_comm??emp.comm%type;??
6. BEGIN??
7. ??query_emp(v_empno,?v_ename,?v_sal,?v_comm);??
8. ??dbms_output.put_line(v_ename?||?'?'?||?v_sal?||?'?'?||?v_comm);??
9. END;??

?

?

函数的调用

Sql代码??

1. DELCARE???
2. ??v_empno?number:=111;??
3. ??v_sal?nubmer;??
4. BEGIN??
5. ??v_sal:=get_sal(v_empno);??
6. ??dbms_output.put_line(v_sal);??
7. end;??

?

(2).在SQL*PLUS中的调用
过程的调用
eg:

Sql代码??

1. set?serveroutput?on/*激活DBMS_OUTPUT系统包*/??
2. ACCEPT?p_emp_no?prompt?'please?enter?the?employee?number:';/*接受员工号*/??
3. variable?v_emp_name?varchar2(14);/*定义存放OUT参数输出结果的变量?*/??
4. variable?v_emp_sal?number;??
5. variable?v_emp_comm?number;??
6. execute?query_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);??

?

注意在SQL*PLUS中，用variable定义的变量在引用时，必须前面加昌号(:),用accept接收的变量在引用时，必须前面加& 符号
函数的调用
函数不能作为一条语句单独出现，只能出现在别的过程中作为别的过程的参数
SQL>EXECUTE dbms_output.put_line(get_sal(0000));
或者
SQL>SELECT get_sal(0000) from dual;

?

3.包
(1).创建包的说明：

Sql代码??

1. create?[or?replace]?package?包名??
2. ???{is|as}??
3. ???公共变量的定义??
4. ???公共类型的定义??
5. ???公共出错处理的定义??
6. ???公共游标的定义??
7. ???函数说明??
8. ???过程说明??
9. ??
10. end;??

?

eg:制作一个包的说明，生成一个管理雇员薪水的包sal_package,其中包括一个为雇员加薪的过程和降薪的过程
并且在包中还有两个记录所有雇员薪水增加和减少的全局变量

Sql代码??

1. create?package?sal_package?is??
2. ??procedure?raise_sal(v_empno?emp.empno%type),?v_sal_increment?emp.sal%type);??
3. ??procedure?reduce_sal(v_empno?emp.empno%type,?v_sal_reduce?emp.sal%type);??
4. ??v_raise_sal??emp.sal%type?:=?0;??
5. ??v_reduce_sal?emp.sal%type?:=?0;??
6. end;??

?

?

创建包的主体

Sql代码??

1. create?[or?replace]?package?BODY?包名??
2. ???{is|as}??
3. ???公共变量的定义??
4. ???公共类型的定义??
5. ???公共出错处理的定义??
6. ???公共游标的定义??
7. ???函数说明??
8. ???过程说明??
9. ??
10. end;??

?制作sal_package包的包主体

?

Sql代码??

1. create?or?replace?package?body?sal_package?is??
2. ??procedure?raise_sal(v_empno?emp.empno%type,?v_sal_increment?emp.sal%type)?is??
3. ??BEGIN??
4. ????UPDATE?emp?set?sal?=?sal?+?v_sal_increment?where?empno?=?v_empno;??
5. ????commit?work;??
6. ????v_raise_sal?:=?v_raise_sal?+?v_sal_increment;??
7. ??end;??
8. ??procedure?reduce_sal(v_empno?emp.empno%type,?v_sal_reduce?emp.sal%type)?is??
9. ??BEGIN??
10. ????update?emp?set?sal?=?sal?-?v_sal_reduce?where?empno?=?v_empno;??
11. ????commit?work;??
12. ????v_reduce_sal?:=?v_reduce_sal?+?v_sal?+?reduce;??
13. ??end;??
14. end;??

?

包的调用:包名.过程名
SQL>EXECUTE sal_package.raise_sal(111,23423);

?

4.触发器
eg:
制作一个数据库触发器,将那些超过其工种工资范围的员工信息记录到audit_message表中,在sal_guide记录
了每一工种的工资范围

Sql代码??

1. create?or?replace?trigger?check_sal??
2. ??before?insert?or?update?of?sal,?job?on?emp??
3. ??for?each?row??
4. ??when?(new.job?<>?'persident')??
5. DECLARE??
6. ??v_minsal?sal_guide.minsal%type;??
7. ??v_maxsal?sal_guide.maxsal%type;??
8. ??e_sal_out_of_range?exception;??
9. BEGIN??
10. ??select?minsal,?maxsal??
11. ????into?v_minsal,?v_maxsal??
12. ????from?sal_guide??
13. ???where?job?=?:new.job;??
14. ??if?:new.sal?<?v_minsal?or?:new?:sal?>?v_maxsal?then??
15. ????raise?e_sal_out_of?range;??
16. ??end?if;??
17. exception??
18. ??when?e_sal_out_of_range?then??
19. ????insert?into?audit_message??
20. ??????(line_nr,?line)??
21. ????values??
22. ??????(1,??
23. ???????'Salary'?||?TO_CHAR(:new.sal)?||?'is?out?of?range?for?employee'?||??
24. ???????TO_CHAR(:new.empno));??
25. END;??

?

(1).触发器的组成
触发时间：before after
触发事件：insert update delete
触发器类型：statement,row (语句级，行级)
触发器体(完整的PL/SQL块)
触发器可分语句级触发器，行级触发器
(2).语句级触发器
创建语法如下：

Sql代码??

1. create?[or?replace]?trigger?trigger_name??
2. ??{before|after}?event1[or?event2]?on?table_name??

?

eg:
创建一个before 型语句级触发器，限制一周内向emp表插入数据的时间，如果是周六，周日
或晚上6点到第二天早上8点之间插入，则中断操作，并提示用户不允许在此时间向emp表插入

Sql代码??

1. create?or?replace?trigger?secure_emp??
2. ??before?insert?on?emp??
3. BEGIN??
4. ??IF?(TO_CHAR(sysdate,?'DY')?IN?('SAT',?'SUN'))?or??
5. ?????(TO_CHAR(sysdate,?'HH24')?NOT?BETWEEN?'8'?and?'18')?then??
6. ????raise_application_error(-20500,??
7. ????????????????????????????'you?may?only?insert?into?emp?during?normal?hours.');??
8. ??END?IF;??
9. END;??

?

(3).使用触发器谓词(inserting,updating,deleting)
通过谓词可以创建一个包含多个触发事件的触发器
对上例进行扩展不但限制插入数据的时间，还限制进行数据修改和删除的时间

Sql代码??

1. create?or?replace?trigger?secure_emp??
2. before?delete?or?insert?or?update?on?emp??
3. BEGIN???
4. ???if?(TO_CHAR(sysdate,'DY'?IN('SAT','SUN'))??OR?(TO_NUMBER(sysdate,'HH24')NOT?BETWEEN?8?AND?18?)THEN??
5. ????if?deleting?then??
6. ???????raise_application_error(-20502,'You?may?only?deletefrom?emp?during?normal?hours.?');??
7. ????elsif?inserting?then??
8. ???????raise_application_error(-20500,'You?may?only?insert?into?emp?during?mormal?hours');??
9. ????else??
10. ???????RAISE_APPLICATION_ERROR(-20504,'You?may?only?update?emp?table?during?normal?hours.');??
11. ????end?if?;??
12. ???end?if?;??
13. end;??

?

(4).行级触发器
创建语法

Sql代码??

1. create?[or?replace]trigger?trigger_name??
2. ???{before|after}?event1[or?event2....]?on?table_name??
3. ???for?each?row?[when?restricting_condition]??

?
PL/SQL
eg:创建一个行级触发器，将每个用户对数据库emp表进行数据库操纵(插入，更新，删除)的次数记录到
audit_table表中

Sql代码??

1. create?or?replace?trigger?audit_emp??
2. ??after?delete?or?insert?or?update?on?emp??
3. ??for?each?row??
4. BEGIN??
5. ??IF?DELETING?THEN??
6. ????update?audit_table??
7. ???????set?del?=?del?+?1??
8. ?????where?user_name?=?user??
9. ???????and?table_name?=?'emp'??
10. ???????and?column_name?is?null;??
11. ??elsif?inserting?then??
12. ????update?audit_table??
13. ???????set?ins?=?ins?+?1??
14. ?????where?user_name?=?user??
15. ???????and?table_name?=?'emp'??
16. ???????and?column_name?is?null;??
17. ??else??
18. ????update?audit_table??
19. ???????set?upd?=?upd?+?1??
20. ?????where?user_name?=?user??
21. ???????and?table_name?=?'emp'??
22. ???????and?column_name?is?null;??
23. ??end?if;??
24. end;??

?
使用行级触发器的标识符(:OLD和:NEW)
在列名前加上：OLD标识符表示该列变化前的值，加上：NEW标识符表示变化后的值
eg:
在行级触发器加WHEN限制条件，根据销售员工资的改变自动计算销售员的奖金

Sql代码??

1. create?or?replace?trigger?derive_comm??
2. ??before?update?of?sal?on?emp??
3. ??for?each?row??
4. ??when?(new.job?=?'SALESMAN')??
5. BEGIN??
6. ??:new.comm?:=?:old.comm?*?(:new.sal?/?:old.sal);??
7. end;??

?