第十章子程序与程序包.docx
《第十章子程序与程序包.docx》由会员分享，可在线阅读，更多相关《第十章子程序与程序包.docx（17页珍藏版）》请在冰豆网上搜索。
第十章子程序与程序包
子程序与程序包
1.子程序概述
✓什么是子程序？
一个命名的PL/SQL块，编译并存储在数据库中。
✓为什么要使用子程序？
在第八，九章，我们学习了PL/SQL块和游标，可以在一个代码块中解决复杂的业务逻辑，但是我们也发现，我们的代码块是临时的，只能使用一次，如果这个业务我们需要再次使用，我们需要重新编写。
基于此，我们将要重用的业务块进行命名，存储在数据库中，这就是子程序。
✓子程序的构成？
子程序的结构和普通的PL/SQL块是一致的，也包括如下部分:
声明部分
可执行部分
异常处理部分（可选）
✓子程序的分类？
存储过程
函数
✓子程序的优点?
模块化【将程序分解为逻辑模块】
可重用性【可以被任意数目的程序调用】
可维护性【简化维护操作】
安全性【通过设置权限，使数据更安全】
2.存储过程
过程是用于完成特定任务的子程序，通过使用过程不仅可以简化客户端应用程序的开发和维护，而且还可以提高应用程序的运行性能。
1）创建存储过程的语法如下：
CREATE[ORREPLACE]PROCEDURE
[（）]--创建过程，可指定运行过程需传递的参数
IS|AS
--可以声明变量
BEGIN
--包括在过程中要执行的语句
[EXCEPTION
]--处理异常
END;
[注]过程体内不能使用查询语句，只能用于赋值（SQL语句块都如此）
如果过程体语句有错误也能创建成功
没有参数就不写，不用（）
2）案例一【无参】：
查询出EMP表中工资最高的员工编号，姓名，工资
--查询出工资最高的员工编号，姓名，工资
createorreplaceprocedureproc_one
as
v_noemp.empno%type;
v_nameemp.ename%type;
v_salemp.sal%type;
begin
selectmax（sal）intov_salfromemp;
selectempno,enameintov_no,v_namefromempwheresal=v_sal;
dbms_output.put_line（'编号'||v_no||',姓名:
'||v_name||'工资:
'||v_sal）;
exception
whenno_data_foundthen
dbms_output.put_line（'没有找到数据'）;
whentoo_many_rowsthen
dbms_output.put_line（'数据超过一行'）;
end;
--执行过程
Execproc_one;
Execproc_one（）;
3）案例二【无参】：
将FORD的工资调整为5000，再次运行上面代码将出错，原因是什么？
如何解决。
createorreplaceprocedureproc_two
as
typemy_cursorisrefcursorreturnemp%rowtype;--使用REF游标
proc_cursormy_cursor;
v_salemp.sal%type;
v_rowemp%rowtype;
begin
selectmax（sal）intov_salfromemp;
openproc_cursorforselect*fromempwheresal=v_sal;
loop
fetchproc_cursorintov_row;
exitwhenproc_cursor%notfound;
dbms_output.put_line（'编号'||v_row.empno||',姓名:
'||v_row.ename||'工资:
'||v_row.sal）;
endloop;
exception
whenno_data_foundthen
dbms_output.put_line（'没有找到数据'）;
whentoo_many_rowsthen
dbms_output.put_line（'数据超过一行'）;
end;
4）案例三【有参】：
请查询出指定部门的员工人数，平均工资，最高工资与最低工资？
如果才能指定部门呢？
ORACLE过程中提供了参数选项，我们可以通过参数来解决此类问题，ORACLE过程参数有如下3中模式：
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
INOUT
用于接受调用程序的值，并向调用程序返回更新的值
⏹该案例中，我们需要将指定部门传入到过程中，使用in模式
--createorreplaceprocedureproc_three（v_deptnoinemp.deptno%type）
createorreplaceprocedureproc_three（v_deptnoemp.deptno%typedefault10）
as
v_countint;
v_avgnumber（10,2）;
v_maxnumber（10,2）;
v_minnumber（10,2）;
begin
selectcount（*）,avg（sal）,max（sal）,min（sal）intov_count,v_avg,v_max,v_minfromemp
wheredeptno=v_deptno;
dbms_output.put_line（'部门编号:
'||v_deptno||'人数:
'||v_count||',平均工资:
'||v_avg||'最高工资:
'||v_max||'最低工资:
'||v_min）;
end;
5）案例四【有参】：
请查询出达到用户指定工资线的员工人数，并返回
分析：
1：
该案例需要用户传入工资线，需要in模式参数2个。
2：
要返回符合条件的人数，需要out模式参数1个。
createorreplaceprocedureproc_four（
xinnumber,
yinnumber,
zoutnumber
）
as
begin
dbms_output.put_line（'查询前人数:
'||z）;
selectcount（*）intozfromempwheresal>=xandsal<=y;
dbms_output.put_line（'查询后人数:
'||z）;
end;
-----------------------------调用----------------------------
declare
v_countnumber;
begin
v_count:
=10;--测试out模式参数值是否传入
proc_four（3000,6000,v_count）;
dbms_output.put_line（'v_count:
'||v_count）;
6）案例四【有参】将所有员工工资添加指定金额，然后返回当前员工中最高工资
分析：
1：
添加指定金额工资，需要in模式参数
2：
返回最高工资，需要out模式参数
3：
2个参数都是与公司相关，可以合二而一
createorreplaceprocedureproc_five（
xinoutnumber
）
as
begin
dbms_output.put_line（'要添加工资额:
'||x）;
updateempsetsal=sal+100;
selectmax（sal）intoxfromemp;
dbms_output.put_line（'最高工资为:
'||x）;
end;
declare
v_salnumber;
begin
v_sal:
=100;
proc_five（v_sal）;
dbms_output.put_line（'v_sal:
'||v_sal）;
end;
7）调用存储过程并传递参数
为存储过程传递参数可采用三种形式，分别为:
⏹位置传递
顾名思义，参数值与过程中参数位置对应，如
Execproc_four（3000,6000,v_count）;
⏹名称传递
传递参数时使用关联符号”=>”指明参数值对应那个参数,如
Declare
v_countnumber;
begin
v_count:
=10;
proc_four（y=>6000,x=>3000,z=>v_count）;
dbms_output.put_line（'v_count:
'||v_count）;
end;
⏹组合传递
传递参数时同时使用位置传递和名称传递【SQLServer中是不允许的】,如:
declare
v_countnumber;
begin
v_count:
=10;
proc_four（3000,z=>v_count,y=>6000）;
dbms_output.put_line（'v_count:
'||v_count）;
end;
注意:
前面的参数按位置才有效。
8）为用户授予执行存储过程的权限
存储过程创建后，只属于当前用户，其他非dba用户想要调用，必须有dba或当前用户授予其他用户当前过程的execute权限，如下：
grantexecuteonproc_fourtonewUsers;
9）查看存储过程
存储过程创建后，可以通过user_objects查看当前模式下实体信息，如果要查看创建源码可以通过user_source查看
select*fromuser_objects;
selectname,line,textfromuser_sourcewherename='PROC_FOUR';
--PROC_FOUR是存储过程名,要大写
10）删除存储过程
Dropprocedureproc_one
3.函数
函数是带返回值的命名的PL/SQL子程序,而且在函数体内最少有一个返回语句.
在创建函数时，通过return子句指定函数返回值类型，在函数体的任何地方用户都可以通过return语句从函数中返回，这里是return值一定与声明时return子句指定类型相同。
1）创建函数的语法:
CREATE[ORREPLACE]FUNCTION
[（param1,param2）]
RETURNIS|AS
[localdeclarations]
BEGIN
ExecutableStatements;
RETURNresult;
EXCEPTION
Exceptionhandlers;
END;
说明:
创建函数与创建过程的语法大部分类似，只是创建函数时在is或as关键字前多了一个return子句，并且函数体内必须有return，其他用法一致，另外在参数模式上，虽然函数也支持in,out,inout三种模式，但通常只使用in模式。
❑定义函数的限制：
❑形参不能是PL/SQL类型
❑函数的返回类型也必须是数据库类型
❑访问函数的两种方式：
❑使用P
升级会员 