1、子程序和程序包2回顾q游标用于处理查询结果集中的数据q游标类型有：隐式游标、显式游标和 REF 游标q隐式游标由 PL/SQL 自动定义、打开和关闭q显式游标用于处理返回多行的查询q显式游标可以删除和更新活动集中的行q要处理结果集中所有记录时，可使用循环游标q在声明 REF 游标时，不需要将 SELECT 语句与 其关联3目标q创建和使用子程序 q创建和使用程序包4子程序 2-1q命名的 PL/SQL 块，编译并存储在数据库中。q子程序的各个部分：q声明部分q可执行部分q异常处理部分(可选)q子程序的分类：q过程 执行某些操作q函数 执行操作并返回值5子程序 2-2子程序的优点：q模块化q将程
2、序分解为逻辑模块q可重用性q可以被任意数目的程序调用q可维护性q简化维护操作q安全性q通过设置权限，使数据更安全6过程 8-1q过程是用于完成特定任务的子程序 q例如：前往售票厅询问关于车票的信息 排队等候 在柜台购买车票 7过程 8-2创建过程的语法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;创建过程，可指定运行过程需传递的参数处理异常 包括在过程中要执行的语句 8过程 8-3CREATE OR REPLACE PROCEDURE find_emp(emp_no NUMBER)AS empname VARCHAR2(20)
3、;BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE(雇员姓名是 | empname);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (雇员编号未找到);END find_emp;/9过程 8-4过程参数的三种模式：qINq用于接受调用程序的值q默认的参数模式qOUTq用于向调用程序返回值 qIN OUTq用于接受调用程序的值，并向调用程序返回更新的值10过程 8-5SQL CREATE OR REPLACE PRO
4、CEDURE itemdesc(item_code IN VARCHAR2)IS v_itemdesc VARCHAR2(5);BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode = item_code; DBMS_OUTPUT.PUT_LINE(item_code| 项目的说明为|v_itemdesc);END;/SQL SET SERVEROUTPUT ONSQL EXECUTE itemdesc(i201);执行过程的语法： EXECUTE procedure_name(parameters_list);1
5、1过程 8-6SQL CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, value2 OUT NUMBER )IS identity NUMBER;BEGIN SELECT ITEMRATE INTO identity FROM itemFile WHERE itemcode = value1; IF identity SET SERVEROUT ONSQL DECLARE num1 NUMBER := 100; num2 NUMBER := 200;BEGIN swap(num1, num2); DBMS_OUTPUT.PUT_LIN
6、E(num1 = | num1); DBMS_OUTPUT.PUT_LINE(num2 = | num2);END;/13过程 8-8q将过程的执行权限授予其他用户：q删除过程：SQL GRANT EXECUTE ON find_emp TO MARTIN;SQL GRANT EXECUTE ON swap TO PUBLIC;SQL DROP PROCEDURE find_emp;14函数 4-1q函数是可以返回值的命名的 PL/SQL 子程序。 q创建函数的语法： CREATE OR REPLACE FUNCTION (param1,param2)RETURN IS|AS local de
7、clarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;15函数 4-2q定义函数的限制：q函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数q形参不能是 PL/SQL 类型q函数的返回类型也必须是数据库类型q访问函数的两种方式：q使用 PL/SQL 块q使用 SQL 语句16函数 4-3q创建函数：q从 SQL 语句调用函数：CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2ISBEGIN RETURN 朋
8、友，您好;END;/SQL SELECT fun_hello FROM DUAL;17函数 4-4CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER;BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price = min_price AND price CREATE OR REPLACE PACKAG
9、E cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2);END cur_pack;/SQL CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vco
10、de VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN OPEN ord_cur(vcode); LOOP FETCH 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有关子程序和程序包的信息qUSER_OBJECTS 视图包含用户创建的子程序和程序包的信息qUSER_SOURCE 视图存储子程序和程序包的源代码SELECT
11、 object_name, object_typeFROM USER_OBJECTSWHERE object_type IN (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY);SELECT line, text FROM USER_SOURCEWHERE NAME=TEST;28总结q子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用q有两种类型的PL/SQL子程序，即过程和函数q过程用户执行特定的任务，函数用于执行任务并返回值q程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装q程序包由两部分组成，即包规范和包主体q使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
