第5章 子程序和程序包

1、第5章 子程序和程序包子程序是已命名的PL/SQL块，它们存储和在数据库中，可以为它们指定参数，可以从任何数据库客户端和应用程序中调用它们。命名的PL/SQL程序包括存储过程和函数，程序包是存储过程和函数的集合。一、子程序子程序具有声明部分、可执行部分和可选的异常处理部分。声明部分包含类型、游标、常量、变量、嵌套子程序的声明。可执行部分包含赋值、控制执行过程和操纵数据库的语句。异常处理部分包含异常处理程序。子程序的优点。（1）模块化；（2）可重用性；（3）可维护性；（4）安全性；子程序有两种类型：（1）过程；（2）函数；一般使用过程来完成某种操作，使用函数完成操作并返回值。1、过程。Oracl

2、e的过程很类似sql server中的存储过程。他可以被赋予参数，存储在数据库中，由其他应用程序或PL/SQL调用。创建过程的格式如下：Create or replace procedure 过程名 (parameter_list) /*过程名如果带有参数，在这里写参数*/ Is | as local_declarations /*局部变量等的声明*/ begin执行语句 exceptionexception_handlers /*这里写异常处理的代码*/ end;例一SQLcreate or replace procedure find_emp_2(emp_no in number) ise

3、mpname varchar(10);begin select ename into empname from scott.emp where empno=emp_no; dbms_output.put_line(雇员姓名|empname);exception when no_data_found then dbms_output.put_line(未找到雇员姓名);end find_emp_2;2、执行过程在sql提示符下，使用execute语句来执行过程。语法如下。Execute 过程名 (参数1，参数2，参数n)；3、调用程序是通过参数向被调用的过程传递值的。参数传递的模式有3种。In、

4、out、in out，分别对应输入、输出、输入输出。默认是输入参数。如例一。例二演示如何创建带in和out参数的过程。SQLcreate or replace procedure max_sal_emp(dept_no in emp.deptno%type,max_sal out emp.sal%type,state out varchar2)isrow_count number(10);begin select max(sal) into max_sal from scott.emp where deptno=dept_no; select count(*) into row_count f

5、rom scott.emp where deptno=dept_no; dbms_output.put_line(共访问了|row_count|条记录); dbms_output.put_line(其中最大值是|max_sal); state:=true;exception when no_data_found then state:=false;end;可以在一个匿名的PL/SQL程序中执行max_sal_emp过程，以显示输出参数的结果。当过程返回值时，只能通过另一个程序查看该值。因此需要编写一个程序，显示out参数的值。以下代码用于显示max_sal_emp过程的返回值。SQLdecla

6、re max_val emp.sal%type;state varchar2(10);beginmax_sal_emp (20,max_val,state);if state=true then dbms_output.put_line(最大工资|to_char(max_val);else dbms_output.put_line(没有查询到数据);end if;end;再看一个例子，也是带in和out 参数，用了游标。-下面的代码创建了过程-create or replace procedure get_ename(v_empno in emp.empno%type,v_ename out

7、emp.ename%type)ascursor c_emp is select empno,ename from emp where empno=v_empno;v_emp c_emp%rowtype;begin open c_emp; fetch c_emp into v_emp; v_ename:=v_emp.ename; close c_emp;end;-下面的代码用来查看结果-declare name emp.ename%type;begin get_ename(7369,name); dbms_output.put_line(name);end;例三。演示如何创建带in out参数的

8、过程。SQL CREATE OR REPLACE PROCEDURE swap(p1 in out number, p2 in out number)as v_temp number(10);begin v_temp:=p1; p1 :=p2; p2 :=v_temp;end;/要执行上述swap过程，请输入以下代码。调用过程swap，并查看结果，可以使用匿名的pl/sql语句来执行，如下。SQL DECLARENum1 number :=100;Num2 number :=200;Begin Swap(num1,num2); Dbms_output.put_line(num1= | num1

9、); Dbms_output.put_line(num2= | num2);End;4、过程创建之后，只有创建该过程的用户或管理员才能调用它。其他用户如果要调用该过程，需要得到过程的execute权限。下面代码将权限赋给某个用户或角色。SQLGRANT EXECUTE ON find_emp to martin;SQLGRANT EXECUTE ON swap to public5、删除过程。格式：drop procedure 过程名;二、函数函数与过程相似，也是数据库中存储的已命名的PL/SQL程序块。函数的主要特性是必须返回一个值。用return语句指定函数返回值的数据类型。在函数体的任何

10、地方都可以通过return语句从函数返回一个值。1、定义函数的格式。Create or replace function function_name (参数1，参数2，参数n) /*有参数就写，没有就不写*/return datatype /*datatype是指希望函数返回什么样的数据类型*/ is | as local_declarations /*这里写变量的定义*/begin executable_statements; /*这里写函数体的执行语句*/exception exception_handlers;end;/*注意*/（1）函数只能带有in参数，不能带有in out参数和ou

11、t参数。（2）形式参数只能使用数据库类型，不能使用PL/SQL类型。（3）函数的返回语句中返回表达式的值的类型必须与定义的类型一致。例四下列语句创建了一个简单函数。SQL CREATE OR REPLACE FUNCTION fun_helloReturn varchar2 is -注意，这里的is即使后没跟变量，也要写。Begin Return 朋友，你好;End;/与过程不同，函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。比如：SQLselect fun_hello from dual;例五。下面的例子说明了如何使用参数及定义返回类型。SQL create or repl

12、ace functionitem_price_range(price number)return varchar2 as min_sal number(10);max_sal number(10);Begin Select max(sal),min(sal) Into max_sal,min_sal from scott.emp; If price=min_sal and price DECLARE p number :=3000; msg varchar2(200);Begin Msg := item_price_range(p); Dbms_output.put_line(msg);End

13、;当然，具体到上述函数item_price_range，用PL/SQL块完成了调用，也可以直接写select item_price_range(3000) from dual; 效果一样。2、函数的授权。与过程相同，用户要调用其他用户的函数也需要得到授权。权限的名称也是execute。下面语句将fun_hello函数的调用权限授予用户martin。SQLgrant execute on fun_hello to martin;3、删除函数SQLDrop function fun_hello;4、使用函数的限制。（1）从select语句调用的任何函数均不能修改数据库表；（2）函数调用执行upda

14、te的存储过程，则该函数不能在sql语句内使用。5、函数和过程的比较。过程函数作为PL/SQL语句执行作为表达式的一部分被调用在规范中不包括return子句必须包含return子句不返回任何值必须返回单个的值可以包含return语句，但不能用于返回值，需要返回值就采用out参数。必须包含至少一条return语句三、自主事务处理见如下例子。例六创建一个过程P2SQLcreate or replace procedure p2asa varchar2(10);beginselect ename into a from emp where empno=7788;dbms_output.put_lin

15、e(a);rollback;end;再创建一个过程P1SQLcreate or replace procedure p1asb varchar2(10);c varchar2(10);beginupdate emp set ename=st where empno=7788;select ename into b from emp where empno=7788;dbms_output.put_line(更新后调用p2前|b);p2();select ename into c from emp where empno=7788; dbms_output.put_line(更新后调用p2后|c

16、);end;执行。begin p1();end;上述例子中，过程p1调用了过程P2，但由于过程P2中有回滚事务rollback的操作，使得过程P1的更新操作也失败。如果在过程p2中加入PRAGMA AUTONOMOUS_TRANSACTION ,如下所示，就不会出现上述现象了。我们称P2使用了自主提交事务。SQLCREATE OR REPLACE PROCEDURE P2 AS a VARCHAR2(50)； PRAGMA AUTONOMOUS_TRANSACTION; BEGIN Select venadd1 into a From vendor_master where vencode=v

17、002; Dbms_output.put_line(a); Rollback;End;自主事务处理的特点有：1）自主事务处理结果的变化不依赖于主事务处理的状态。2）自主事务处理提交或回退时，不影响主事务处理的结果。3）自主事务处理还可以启动其他自主事务处理。4）自主事务处理的提交，对主事务处理是不可见的。四、程序包程序包是一种规范，它是对相关PL/SQL类型、子程序、游标、异常、变量和常量的封装。它包含程序包规范和程序包主体。在程序包规范中，可以声明变量、常量、异常、游标、子程序。程序包主体用于实现在程序包规范中定义的游标、子程序。程序包的组成部分如下所示。过程A声明公用变量程序包规范公用过程

18、私有过程过程B定义程序包主体过程A定义共有过程局部变量1、程序包规范创建程序包规范的格式CREATE OR REPLACE package_name /* package_name 指程序包名*/Is | as public type and item declarations /*在这里声明类型、常量、变量、异常、游标等*/subprogram specifications /*声明PL/SQL子程序*/end;例七。创建了一个程序包，声明了子程序order_proc和order_fun。SQLCreate or replace package pack_me isProcedure ord

19、er_proc (orno varchar2);Function order_fun (ornos varchar2) return varchar2; End;2、程序包主体程序包主体包含在程序包规范中声明的每个游标和子程序的具体实现。私有声明也可以包括在程序包主体中。创建程序包主体的格式如下。Create or replace package body package_nameIs | as public type and item declarations subprogram bodies begin语句体end;/例八。演示如何创建pack_meSQL CREATE OR REPLA

20、CE PACKAGE BODY pack_me asProcedure order_proc (orno varchar2) is Stat char(1);Begin Select ostatus into stat from order_master where orderno=orno; If stat=P then Dbms_output.put_line(暂挂的订单); Else Dbms_output.put_line(已完成的订单); End if;End;Function order_fun (ornos varchar2)Return varchar2接下面代码Is Icod

21、e varchar2(5); Ocode varchar2(5); Qtyord number; Qtydeld number;BeginSelect qty_ord,qty_deld,itemcode,orderno Into qtyord,qtydeld,icode,ocode From orderno=ornos;If qtyordEXECUTE pack_me.order_proc(o002);为了执行在程序包中定义的函数，可参考下列代码。SQLvariable msg varchar2(10)SQLexecute:msg:=pack_me.order_fun(o002)SQLPrin

22、t msgSQLdeclare msg varchar2(10);Begin Msg := pack_me.order_fun(o002); Dbms_output.put_lind (值是 | msg);End;再看一个例子，讲解代码“第5章程序包举例.sql” 。3、程序包的优点1）、模块化；2）、更轻松的应用程序设计；3）、信息隐藏；4）、性能更好。4、程序包中的游标在程序包中可以定义和使用游标，游标的定义分为游标规范和游标主体两部分。在更改游标主体时，无需更改游标规范。此外，在包规范中声明游标规范时必须通过return子句指定游标的返回类型。Return子句指示从游标获取并返回的数据元

23、素。实际上，这些数据元素由该游标的select语句确定，但是，select语句仅出现在主体中，而不是出现在规范中。游标规范必须包含程序使用游标所需的所有信息，因此需要返回数据类型。例九演示如何在程序包中声明游标和使用游标。SQLcreate or replace package cur_pack is Cursor ord_cur (vcode varchar2 )Return order_master% rowtype;Procedure ord_pro ( vcode varchar2 );End cur_pack;/上面这段代码为表order_master定义了ord_cur游标，还创建

24、了ord_pro过程以检索 vcode。接着再创建cur_pack包的主体。SQLcreate or replace package body cur_pack asCursor ord_cur ( vcode varchar2)Return order_master% rowtype isSelect * from order_master where vencode=vcode;Procedure ord_pro ( vcode varchar2 ) is Or_rec order_master%rowtype;Begin Open ord_cur (vode); Loop Fetch o

25、rd_cur into or_rec; Exit when ord_cur%notfound; Dbms_output.put_line (返回的值为 | or_rec.orderno ); End loop;End ord_proc;End cur_pack;/如果要执行ord_pro过程，请输入以下命令。比如，要用供应商编号v001打开cur_pack包中的ord_pro过程，代码如下。Exec cur_pack.ord_pro (v001);5、获取子程序和程序包的信息。子程序和程序包是数据库中存储的对象，oracle会在数据字典中存储所有对象的信息。通过查询USER_OBJECTS数据

26、字典视图，可以获得子程序和程序包的信息。第一步，获取系统中对象的名字。如：例十。从USER_OBJECTS中查询有什么样的对象。SQL column object_name format A18SQL select object_name , object_type from USER_OBJECTS Where object_type in (procedure,function,package,package, body);第二步，通过查询USER_SOURCE数据字典视图，可以根据对象名获取相关信息。如。例十一。获取该对象的相关代码（假设通过上面操作已经知道了对象名）。SQLcolumn

27、 line format 9999SQLcolumn text format A50SQLselect line,text from USER_SOURCE where name=test;那么，USER_SOURCE包含哪些信息呢？可以通过执行下列语句获取。SQLdesc USER_SOURCE结果如下。名称 类型 NAME VARCHAR2(30)TYPE VARCHAR2(12)LINE NUMBERTEXT VARCHAR2(4000)例十二。获取程序包中的子程序规范信息。SQLdesc 程序包的名字总结。1）子程序是命名的PL/SQL块，可带参数并可在需要时随时调用。2）PL/SQL

28、有两种类型的子程序，即过程和函数。3）过程用于执行特定任务，函数用于执行任务并返回值。4）程序包是对相关类型、变量、常量、游标、异常、过程和函数的封装。5）程序包由包规范和包主体两部分组成。6）包规范是包的接口，包含公用对象及其类型。7）包主体实现包规范中的游标和子程序，包主体中的声明仅限于在包内使用。8）程序包中游标的定义分为游标规范和游标主体两部分。作业。1、以下不属于命名的PL/SQL块的是_A) 程序包 B) 过程 C) 游标 D) 函数2、执行特定任务的子程序是_A) 函数 B) 过程 C) 程序包 D) 游标3、子程序的_模式参数可以在调用子程序时指定一个常量。A) IN B) O

29、UT C) IN OUT D) INOUT4、下面关于主事务处理与自主事务处理的说法错误的是_A) 自主事务处理结果的变化不依赖于主事务处理的状态。B) 自主事务处理提交或回退时，不影响主事务处理的结果。C) 自主事务处理的提交，对主事务处理是不可见的。D) 自主事务处理还可以启动其他自主事务处理。5、公用的子程序和常量在_中声明A) 过程 B) 游标 C) 包规范 D) 包主体6、数据字典视图_中包含存储过程的代码文本。A) USER_OBJECTS B) USER_TEXT C) USER_SOURCE D) USER_DESC7、创建包含下列字段的salary_details表Empco

30、de varchar2(10) primary key,Workingdays number,Salary number编写一个过程，根据empcode计算雇员在扣除税款（税率为5%）后的净收入，并将净收入显示出来。8、利用上题创建的salary_details表，创建work_days( ) 函数，接受empcode雇员编号，并检查所有雇员的工作天数。标准的工作天数为22天，雇员每少工作一天，从其工资中扣除50元。函数返回雇员最后的工资，工作天数多余标准天数不加工资。工资扣完为止，不可以为负值。9、创建包含以下列的my_toys表ID varchar2(5),NAME varchar2(20

31、),PRICE number创建包含一个过程和一个函数的toypack包，将过程和函数分别命名为updatetoyprice和avgtoyprice。过程应将每个玩具的单价增加10%，直到所有玩具的平均价格达到400。此外，过程还应保证任一玩具的价格不超过500。10、采用scott用户的emp表作为数据源，编写一个程序包，此程序包有2个过程和1个函数。第一个过程根据职员编号打印职员姓名，第二个过程根据职员编码打印职员薪水。函数根据职员编号返回职员的就职年份。答案与提示：第7题：创建表create table salary_details(empcode varchar2(10) primar

32、y key,workingdays number(10),salary number(10);创建过程create or replace procedure rate_on_sal(e_code salary_details.empcode%type)asv_salary number(10);begin select salary into v_salary from salary_details where empcode=e_code; v_salary:=v_salary*0.95; dbms_output.put_line(v_salary);end;beginrate_on_sal(2);end;第8题：CREATE OR REPLACE FUNCTION work_days(EMP_ID VARCHAR2)RETURN NUMBERAS v_days NUMBER; v_sal REAL;BEGI