子程序和程序包

ACCP V4.0,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,*,第七章,子程序和程序包,回顾,游标用于处理查询结果集中的数据,游标类型有：隐式游标、显式游标和 REF 游标,隐式游标由 PL/SQL 自动定义、打开和关闭,显式游标用于处理返回多行的查询,显式游标可以删除和更新活动集中的行,要处理结果集中所有记录时，可使用循环游标,在声明 REF 游标时，不需要将 SELECT 语句与 其关联,2,目标,创建和使用子程序,创建和使用程序包,3,子程序 2-1,命名的 PL/SQL 块，编译并存储在数据库中。,子程序的各个部分：,声明部分,可执行部分,异常处理部分(可选),子程序的分类：,过程 执行某些操作,函数 执行操作并返回值,4,子程序 2-2,子程序的优点：,模块化,将程序分解为逻辑模块,可重用性,可以被任意数目的程序调用,可维护性,简化维护操作,安全性,通过设置权限，使数据更安全,5,过程 8-1,过程是用于完成特定任务的子程序,例如：,前往售票厅,询问关于车票的信息,排队等候,在柜台购买车票,6,过程 8-2,创建过程的语法：,CREATE OR REPLACE PROCEDURE, (),IS|AS,BEGIN,EXCEPTION,END;,创建过程，可指定运行过程需传递的参数,处理异常,包括在过程中要执行的语句,7,过程 8-3,CREATE OR REPLACE PROCEDURE,find_emp(emp_no NUMBER),AS,empname VARCHAR2(20);,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;,/,8,过程 8-4,过程参数的三种模式：,IN,用于接受调用程序的值,默认的参数模式,OUT,用于向调用程序返回值,IN OUT,用于接受调用程序的值，并向调用程序返回更新的值,9,过程 8-5,SQL CREATE OR REPLACE PROCEDURE,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 ON,SQL EXECUTE itemdesc(i201);,执行过程的语法：,EXECUTE procedure_name(parameters_list);,10,过程 8-6,SQL 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 ON,SQL DECLARE,num1 NUMBER := 100;,num2 NUMBER := 200;,BEGIN,s, num2);,DBMS_OUTPUT.PUT_LINE(num1 = | num1);,DBMS_OUTPUT.PUT_LINE(num2 = | num2);,END;,/,12,过程 8-8,将过程的执行权限授予其他用户：,删除过程：,SQL GRANT EXECUTE ON find_emp TO MARTIN;,SQL GRANT EXECUTE ON s PUBLIC;,SQL DROP PROCEDURE find_emp;,13,函数 4-1,函数是可以返回值的命名的 PL/SQL 子程序。,创建函数的语法：,CREATE OR REPLACE FUNCTION, (param1,param2),RETURN IS|AS,local declarations,BEGIN,Executable Statements;,RETURN result;,EXCEPTION,Exception handlers;,END;,14,函数 4-2,定义函数的限制：,函数只能接受,IN,参数，而不能接受,IN OUT,或,OUT,参数,形参不能是,PL/SQL,类型,函数的返回类型也必须是数据库类型,访问函数的两种方式：,使用,PL/SQL,块,使用,SQL,语句,15,函数 4-3,创建函数：,从 SQL 语句调用函数：,CREATE OR REPLACE FUNCTION fun_hello,RETURN VARCHAR2,IS,BEGIN,RETURN 朋友，您好;,END;,/,SQL SELECT fun_hello FROM DUAL;,16,函数 4-4,CREATE 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 PACKAGE 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(vcode 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;,/,26,有关子程序和程序包的信息,USER_OBJECTS 视图包含用户创建的子程序和程序包的信息,USER_SOURCE 视图存储子程序和程序包的源代码,SELECT object_name, object_type,FROM USER_OBJECTS,WHERE object_type IN (PROCEDURE, FUNCTION,PACKAGE, PACKAGE BODY);,SELECT line, text FROM USER_SOURCE,WHERE NAME=TEST;,27,总结,子程序是命名的 PL/SQL 块，可带参数并可在需要时随时调用,有两种类型的PL/SQL子程序，即过程和函数,过程用户执行特定的任务，函数用于执行任务并返回值,程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装,程序包由两部分组成，即包规范和包主体,使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳,28,