1、第七章,子程序和程序包,2,回顾,游标用于处理查询结果集中的数据 游标类型有：隐式游标、显式游标和 REF 游标 隐式游标由 PL/SQL 自动定义、打开和关闭 显式游标用于处理返回多行的查询 显式游标可以删除和更新活动集中的行 要处理结果集中所有记录时，可使用循环游标 在声明 REF 游标时，不需要将 SELECT 语句与 其关联,3,目标,创建和使用子程序 创建和使用程序包,4,子程序 2-1,命名的 PL/SQL 块，编译并存储在数据库中。 子程序的各个部分： 声明部分 可执行部分 异常处理部分(可选) 子程序的分类： 过程 执行某些操作 函数 执行操作并返回值,5,子程序 2-2,子程
2、序的优点： 模块化 将程序分解为逻辑模块 可重用性 可以被任意数目的程序调用 可维护性 简化维护操作 安全性 通过设置权限，使数据更安全,6,过程 8-1,过程是用于完成特定任务的子程序 例如：,前往售票厅,询问关于车票的信息,排队等候,在柜台购买车票,7,过程 8-2,创建过程的语法：CREATE OR REPLACE PROCEDURE () IS|AS BEGINEXCEPTION END;,创建过程，可指定运行过程需传递的参数,处理异常,包括在过程中要执行的语句,8,过程 8-3,CREATE OR REPLACE PROCEDUREfind_emp(emp_no NUMBER) AS
3、empname VARCHAR2(20); BEGINSELECT ename INTO empnameFROM EMP WHERE empno = emp_no;DBMS_OUTPUT.PUT_LINE(雇员姓名是 | empname); EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE (雇员编号未找到); END find_emp; /,9,过程 8-4,过程参数的三种模式： IN 用于接受调用程序的值 默认的参数模式 OUT 用于向调用程序返回值 IN OUT 用于接受调用程序的值，并向调用程序返回更新的值,10,过程 8-5,S
4、QL CREATE OR REPLACE PROCEDUREitemdesc(item_code IN VARCHAR2) ISv_itemdesc VARCHAR2(5); BEGINSELECT itemdesc INTO v_itemdescFROM itemfileWHERE itemcode = item_code;DBMS_OUTPUT.PUT_LINE(item_code|项目的说明为|v_itemdesc); END; / SQL SET SERVEROUTPUT ON SQL EXECUTE itemdesc(i201);,执行过程的语法：EXECUTE procedure_
5、name(parameters_list);,11,过程 8-6,SQL CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, value2 OUT NUMBER ) ISidentity NUMBER; BEGINSELECT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1;IF identity 200 THENvalue2:=100;END IF; END;,DECLAREvalue1 VARCHAR2(5) := i202;value2 NUMBER; BEG
6、INtest (value1, value2);DBMS_OUTPUT.PUT_LINE(value2 的值为| TO_CHAR(value2); END; /,12,过程 8-7,CREATE OR REPLACE PROCEDUREswap(p1 IN OUT NUMBER, p2 IN OUT NUMBER) ISv_temp NUMBER; BEGINv_temp := p1;p1 := p2;p2 := v_temp; END; /,SQL SET SERVEROUT ON SQL DECLAREnum1 NUMBER := 100;num2 NUMBER := 200; BEGIN
7、swap(num1, num2);DBMS_OUTPUT.PUT_LINE(num1 = | num1);DBMS_OUTPUT.PUT_LINE(num2 = | num2); END; /,13,过程 8-8,将过程的执行权限授予其他用户：删除过程：,SQL GRANT EXECUTE ON find_emp TO MARTIN; SQL GRANT EXECUTE ON swap TO PUBLIC;,SQL DROP PROCEDURE find_emp;,14,函数 4-1,函数是可以返回值的命名的 PL/SQL 子程序。 创建函数的语法：CREATE OR REPLACE FUNC
8、TION (param1,param2) RETURN IS|AS local declarations BEGINExecutable Statements;RETURN result; EXCEPTIONException handlers; END;,15,函数 4-2,定义函数的限制： 函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数 形参不能是 PL/SQL 类型 函数的返回类型也必须是数据库类型 访问函数的两种方式： 使用 PL/SQL 块 使用 SQL 语句,16,函数 4-3,创建函数：从 SQL 语句调用函数：,CREATE OR REPLACE FUNC
9、TION fun_helloRETURN VARCHAR2 IS BEGINRETURN 朋友，您好; END; /,SQL SELECT fun_hello FROM DUAL;,17,函数 4-4,CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 ASmin_price NUMBER;max_price NUMBER; BEGINSELECT MAX(ITEMRATE), MIN(ITEMRATE)INTO max_price, min_priceFROM itemfile;IF price
10、 = min_price AND price = max_price THENRETURN 输入的单价介于最低价与最高价之间;ELSERETURN 超出范围;END IF; END; /,DECLAREP NUMBER := 300;MSG VARCHAR2(200); BEGINMSG := item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG); END; /,18,过程和函数的比较,19,自主事务处理 2-1,自主事务处理 主事务处理启动独立事务处理 然后主事务处理被暂停 自主事务处理子程序内的 SQL 操作 然后终止自主事务处理 恢复主事务处理
11、PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理,20,自主事务处理 2-2,自主事务处理的特征： 与主事务处理的状态无关 提交或回滚操作不影响主事务处理 自主事务处理的结果对其他事务是可见的 能够启动其他自主事务处理,21,程序包,程序包是对相关过程、函数、变量、游标和异常等对象的封装 程序包由规范和主体两部分组成,声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等,声明程序包私有对象和实现在包规范中声明的子程序和游标,程序包,规范,主体,22,创建程序包 2-1,程序包规范CREATE OR REPLACEPACKAGE pac
12、kage_name IS|AS Public item declarations Subprogram specification END package_name;,程序包主体CREATE OR REPLACE PACKAGE BODY package_name IS|AS Private item declarations Subprogram bodies BEGIN Initialization END package_name;,23,创建程序包 2-2,CREATE OR REPLACE PACKAGE pack_me ISPROCEDURE order_proc (orno VA
13、RCHAR2);FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2; END pack_me; /,CREATE OR REPLACE PACKAGE BODY pack_me ASPROCEDURE order_proc (orno VARCHAR2) ISstat CHAR(1);BEGINSELECT ostatus INTO stat FROM order_masterWHERE orderno = orno;END order_proc;FUNCTION order_fun(ornos VARCHAR2)RETURN VARCHAR2
14、ISicode VARCHAR2(5);ocode VARCHAR2(5);BEGINEND order_fun; END pack_me; /,24,程序包的优点,模块化 更轻松的应用程序设计 信息隐藏 新增功能 性能更佳,25,程序包中的游标 2-1,游标的定义分为游标规范和游标主体两部分 在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 RETURN子句指定的数据类型可以是： 用 %ROWTYPE 属性引用表定义的记录类型 程序员定义的记录类型,26,程序包中的游标 2-2,SQL CREATE OR REPLACE PACKAGE cur_pack ISCURSO
15、R ord_cur(vcode VARCHAR2)RETURN order_master%ROWTYPE;PROCEDURE ord_pro(vcode VARCHAR2); END cur_pack; /,SQL CREATE OR REPLACE PACKAGE BODY cur_pack ASCURSOR ord_cur(vcode VARCHAR2)RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode;PROCEDURE ord_pro(vcode VARCHAR2) ISor_rec
16、 order_master%ROWTYPE;BEGINOPEN ord_cur(vcode); LOOPFETCH ord_cur INTO or_rec;EXIT WHEN ord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(返回的值为 | or_rec.orderno);END LOOP;END ord_pro; END cur_pack; /,27,有关子程序和程序包的信息,USER_OBJECTS 视图包含用户创建的子程序和程序包的信息USER_SOURCE 视图存储子程序和程序包的源代码,SELECT object_name, object_type FROM
17、 USER_OBJECTS WHERE object_type IN (PROCEDURE, FUNCTION,PACKAGE, PACKAGE BODY);,SELECT line, text FROM USER_SOURCE WHERE NAME=TEST;,28,总结,子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用 有两种类型的PL/SQL子程序，即过程和函数 过程用户执行特定的任务，函数用于执行任务并返回值 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装 程序包由两部分组成，即包规范和包主体 使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳,
