第十章子程序与程序包.docx

1、第十章子程序与程序包子程序与程序包1. 子程序概述 什么是子程序？一个命名的 PL/SQL 块，编译并存储在数据库中。 为什么要使用子程序？在第八，九章，我们学习了PL/SQL块和游标，可以在一个代码块中解决复杂的业务逻辑，但是我们也发现，我们的代码块是临时的，只能使用一次，如果这个业务我们需要再次使用，我们需要重新编写。基于此，我们将要重用的业务块进行命名，存储在数据库中，这就是子程序。 子程序的构成？子程序的结构和普通的PL/SQL块是一致的，也包括如下部分: 声明部分 可执行部分 异常处理部分(可选) 子程序的分类？ 存储过程 函数 子程序的优点? 模块化【将程序分解为逻辑模块】 可重用

2、性【可以被任意数目的程序调用】 可维护性【简化维护操作】 安全性【通过设置权限，使数据更安全】2. 存储过程过程是用于完成特定任务的子程序，通过使用过程不仅可以简化客户端应用程序的开发和维护，而且还可以提高应用程序的运行性能。1) 创建存储过程的语法如下：CREATE OR REPLACE PROCEDURE ()-创建过程，可指定运行过程需传递的参数IS|AS -可以声明变量BEGIN -包括在过程中要执行的语句EXCEPTION -处理异常END;注过程体内不能使用查询语句，只能用于赋值（SQL语句块都如此） 如果过程体语句有错误也能创建成功 没有参数就不写，不用() 2) 案例一【无参】

3、：查询出EMP表中工资最高的员工编号，姓名，工资-查询出工资最高的员工编号，姓名，工资create or replace procedure proc_oneasv_no emp.empno%type;v_name emp.ename%type;v_sal emp.sal%type;begin select max(sal) into v_sal from emp; select empno,ename into v_no,v_name from emp where sal=v_sal; dbms_output.put_line(编号|v_no|,姓名:|v_name|工资:|v_sal);e

4、xception when no_data_found then dbms_output.put_line(没有找到数据); when too_many_rows then dbms_output.put_line(数据超过一行);end;-执行过程Exec proc_one;Exec proc_one();3) 案例二【无参】：将FORD的工资调整为5000，再次运行上面代码将出错，原因是什么？如何解决。create or replace procedure proc_twoastype my_cursor is ref cursor return emp%rowtype;-使用REF游标p

5、roc_cursor my_cursor;v_sal emp.sal%type;v_row emp%rowtype;begin select max(sal) into v_sal from emp; open proc_cursor for select * from emp where sal=v_sal; loop fetch proc_cursor into v_row; exit when proc_cursor%notfound; dbms_output.put_line(编号|v_row.empno|,姓名:|v_row.ename|工资:|v_row.sal); end loo

6、p;exception when no_data_found then dbms_output.put_line(没有找到数据); when too_many_rows then dbms_output.put_line(数据超过一行);end;4) 案例三【有参】：请查询出指定部门的员工人数，平均工资，最高工资与最低工资？如果才能指定部门呢？ORACLE过程中提供了参数选项，我们可以通过参数来解决此类问题，ORACLE过程参数有如下3中模式： IN 用于接受调用程序的值 默认的参数模式 OUT 用于向调用程序返回值 IN OUT 用于接受调用程序的值，并向调用程序返回更新的值 该案例中，我们

7、需要将指定部门传入到过程中，使用in模式-create or replace procedure proc_three(v_deptno in emp.deptno%type)create or replace procedure proc_three(v_deptno emp.deptno%type default 10)asv_count int;v_avg number(10,2);v_max number(10,2);v_min number(10,2);begin select count(*),avg(sal),max(sal),min(sal) into v_count,v_av

8、g,v_max,v_min from emp where deptno=v_deptno; dbms_output.put_line(部门编号:|v_deptno|人数:|v_count|,平均工资:|v_avg|最高工资:|v_max|最低工资:|v_min);end;5) 案例四【有参】：请查询出达到用户指定工资线的员工人数，并返回分析：1：该案例需要用户传入工资线，需要in模式参数2个。 2：要返回符合条件的人数，需要out模式参数1个。create or replace procedure proc_four( x in number, y in number, z out numbe

9、r)as begin dbms_output.put_line(查询前人数:|z); select count(*) into z from emp where sal=x and sal”指明参数值对应那个参数,如Declarev_count number;beginv_count:=10;proc_four(y=6000,x=3000,z=v_count);dbms_output.put_line(v_count:|v_count);end; 组合传递传递参数时同时使用位置传递和名称传递【SQLServer中是不允许的】,如:declarev_count number;beginv_cou

10、nt:=10;proc_four(3000,z=v_count,y=6000);dbms_output.put_line(v_count:|v_count);end;注意:前面的参数按位置才有效。8) 为用户授予执行存储过程的权限存储过程创建后，只属于当前用户，其他非dba用户想要调用，必须有dba或当前用户授予其他用户当前过程的execute权限，如下：grant execute on proc_four to newUsers;9) 查看存储过程存储过程创建后，可以通过user_objects查看当前模式下实体信息，如果要查看创建源码可以通过user_source查看select * fr

11、om user_objects;select name,line,text from user_source where name=PROC_FOUR;- PROC_FOUR是存储过程名,要大写10) 删除存储过程Drop procedure proc_one3. 函数函数是带返回值的命名的 PL/SQL 子程序,而且在函数体内最少有一个返回语句.在创建函数时，通过return子句指定函数返回值类型，在函数体的任何地方用户都可以通过return语句从函数中返回，这里是return值一定与声明时return子句指定类型相同。1) 创建函数的语法:CREATE OR REPLACE FUNCTIO

12、N (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;说明:创建函数与创建过程的语法大部分类似，只是创建函数时在is或as关键字前多了一个return子句，并且函数体内必须有return，其他用法一致，另外在参数模式上，虽然函数也支持in,out,in out三种模式，但通常只使用in模式。 定义函数的限制： 形参不能是 PL/SQL 类型 函数的返回类型也必须是数据库类型 访问函数的两种方式： 使用 P