oracle过程、函数和程序包

1、7过程、函数和包过程、函数和包Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 2目标目标在完成本章学习后，你将掌握下列技能；在完成本章学习后，你将掌握下列技能；lPL/SQL过程的定义与用法过程的定义与用法lPL/SQL函数的定义与用法函数的定义与用法lPL/SQL包的定义与用法包的定义与用法Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 3本章内容本章内容lPL/SQL过程过程lPL/SQL函数函数lPL/SQL包包Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 4导航导航lPL/SQL过程过程l

2、PL/SQL函数函数lPL/SQL包包Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 5PL/SQL过程过程l过程是指用于执行特定操作的过程是指用于执行特定操作的PL/SQL块块l通过使用过程，不仅可以简化客户应用的开发和维护，通过使用过程，不仅可以简化客户应用的开发和维护，而且可以提高应用程序的运行性能。而且可以提高应用程序的运行性能。l语法：语法：CREATE OR REPLACE PROCEDURE procedure_name(argument1mode1 datatype1,argument2mode2 datatype2,)IS|ASPL/SQL B

3、lock;其中其中procedure_name指定过程名，指定过程名，argument指定过程指定过程参数；参数；IS或或AS用于开始用于开始PL/SQL块。块。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 6PL/SQL过程过程-建立无参数的过程建立无参数的过程l下面以建立用于输出当前日期和时间的过程下面以建立用于输出当前日期和时间的过程out_time为例。为例。CREATE OR REPLACE PROCEDURE out_timeISBEGIN DBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:M

4、I:SS); DBMS_OUTPUT.PUT_LINE(sysdate);END;/Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 7PL/SQL过程过程-建立带有输入参数的过程建立带有输入参数的过程l通过使用输入参数，可以将应用程序数据传通过使用输入参数，可以将应用程序数据传递到过程。递到过程。l当定义过程参数时，当定义过程参数时，默认参数模式就是输入默认参数模式就是输入参数参数l可以使用可以使用IN关键字显式定义输入参数。关键字显式定义输入参数。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 8CREATE OR REPLAC

5、E PROCEDURE add_empl( empno empl.empno%TYPE,ename empl.ename%TYPE, ebirthday empl.ebirthday%TYPE, egrade empl.egrade%TYPE, ejob empl.ejob%TYPE,emajor empl.emajor%TYPE, indate empl.indate%TYPE,salary empl.salary%TYPE, allow empl.allow%TYPE,deptno empl.deptno%TYPE) IS BEGIN INSERT INTO empl VALUES(emp

6、no,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 9PL/SQL过程过程-建立带有输出参数的过程建立带有输出参数的过程l通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。l当定义输出参数时，需要制定参数模式为当定义输出参数时，需要制定参数模式为OUT。l下面建立用于更新雇员工资并输出雇员姓名的过程下面建立用于更新雇员工资并输出雇员姓名的过程update_sal

7、为例。为例。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 10CREATE OR REPLACE PROCEDURE update_sal( eno varhcar2, sal NUMBER, name OUT VARCHAR2) IS BEGIN UPDATE empl SET salary=sal WHERE empno=eno RETURNING ename INTO name;END;/Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 11PL/SQL过程过程-建立带有输入输出参数的过程建立带有输入输出参数的过程l通过在过

8、程中使用输入输出函数，可以调用过程通过在过程中使用输入输出函数，可以调用过程时输入数据到过程，在调用结束后输出数据到调时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。用环境和应用程序。l当定义输入输出参数时，需要指定参数模式为当定义输入输出参数时，需要指定参数模式为IN OUT。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 12CREATE OR REPLACE PROCEDURE devide( num1 IN OUT NUMBER，num2 IN OUT NUMBER) IS v1 NUMBER; v2 NUMBER;BEGIN v1:=TRU

9、NC(num1/num2); v2:=MOD(num1,num2); num1:=v1; num2:=v2;END;/Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 13PL/SQL过程过程-调用过程调用过程l在在SQL*PLUS中调用过程时，需要使用中调用过程时，需要使用CALL或者或者EXECUTE命令；而在命令；而在PL/SQL块中过程可以直接应用。块中过程可以直接应用。l过程调用带参数分过程调用带参数分4种情况：种情况：如果无参数，那么可以直接引用过程名；如果无参数，那么可以直接引用过程名；如果有输入参数，那么需要为输入参数提供数值；如果有输入参数，那么

10、需要为输入参数提供数值；如果带有输出参数，那么需要使用变量接收输出结果；如果带有输出参数，那么需要使用变量接收输出结果；如果有输入输出参数，那么调用时需要使用具有输入值的变量。如果有输入输出参数，那么调用时需要使用具有输入值的变量。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 14PL/SQL过程过程-调用无参数的过程调用无参数的过程l直接引用过程名直接引用过程名exec out_timeOracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 15PL/SQL过程过程-调用带有输入参数的过程调用带有输入参数的过程l需要为输入参数提供数据

11、值需要为输入参数提供数据值exec add_empl(1111, 1980-3-4, lei, programmer, 1, 7369, SYSDATE, 800, null, 30)Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 16PL/SQL过程过程-调用带有输出参数的过程调用带有输出参数的过程l需要使用变量接受输出参数的数据值需要使用变量接受输出参数的数据值DECLARE v_name empl.ename%TYPE;BEGIN update_sal(&eno,&salary,v_name); DBMS_OUTPUT.PUT_LINE(n

12、ame:|v_name);END;/输入输入 雇员号的值：雇员号的值： 1111输入工资的值：输入工资的值： 2500姓名：姓名：leiOracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 17PL/SQL过程过程-调用带有输入输出参数的过程调用带有输入输出参数的过程l调用之前需要定义变量存放输入值，并在调用结束之后调用之前需要定义变量存放输入值，并在调用结束之后使用变量输出数据。使用变量输出数据。DECLARE n1 NUMBER:=&n1; n2 NUMBER:=&n2;BEGIN divide(n1,n2); DBMS_OUTPUT.PUT_LI

13、NE(商商:|n1|,余数余数:|n2);END; /输入输入n1的值：的值：100输入输入n2的值：的值：30商：商：3，余数，余数10Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 18为参数传递变量或数据为参数传递变量或数据当为参数传递变量或数据，可以采用以下当为参数传递变量或数据，可以采用以下3种方法种方法位置传递位置传递名称传名称传组合传递组合传递Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 19PL/SQL过程过程-使用位置传递为参数传递变量和数据使用位置传递为参数传递变量和数据l位置传递位置传递是指按照参数定义顺序

14、依次为每个参数是指按照参数定义顺序依次为每个参数提供变量或者数据值提供变量或者数据值l当使用位置传递时，当使用位置传递时，必须为所有参数提供变量或必须为所有参数提供变量或者输入数据者输入数据CALL add_empl(2222,1978-12-2， alex,NULL, 1， NULL,SYSDATE,800,NULL,30);Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 20PL/SQL过程过程-使用名称传递为参数传递变量和数据使用名称传递为参数传递变量和数据l名称传递名称传递是指通过制定参数名为其提供变量或者数据是指通过制定参数名为其提供变量或者数据l当使

15、用名称传递时，需要使用当使用名称传递时，需要使用=为参数提供变量或者为参数提供变量或者数值数值exec add_emp(empno=3333,ebirghday=,1989-2-2，indate=null, egrade=1, ename=JOHN,ejob=null,major=null, salary=null,allow=null,deptno=null)Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 21PL/SQL过程过程-使用组合传递为参数传递变量和数据使用组合传递为参数传递变量和数据l组合传递组合传递是指在调用子程序时结合使用位置传递和是指在调用子

16、程序时结合使用位置传递和名称传递名称传递exec add_emp(4444, 1985-2-3,AGASI , tester,1, null, indate=SYSDATE, salary=1200, allow=0, deptno=30)Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 22PL/SQL过程过程-过程开发示例过程开发示例l为过程参数指定默认值为过程参数指定默认值CREATE OR REPLACE PROCEDURE add_emp( empno empl,empno%TYPE, ebirthday empl.ebirthday%TYPE, ena

17、me empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE DEFAULT 1, major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE DEFAULT 0, deptno empl.deptno%TYPE)ISBEGIN INSERT INTO emp VALUES(empno,ebirthday,e

18、name,ejob,egrade major, indate, salary,allow, deptno);END;/Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 23PL/SQL过程过程-使用异常处理使用异常处理CREATE OR REPLACE PROCEDURE update_sal( name empl.ename%TYPE, sal empl.salary%TYPE) IS e_no_rows EXCEPTION;BEGIN UPDATE empl SET salary=sal WHERE LOWER(ename)=LOWER(name); IF S

19、QL%NOTFOUND THEN RAISE e_no_rows; END IF;EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(这雇员不存在这雇员不存在);END;lExec update_sal(jarbus,1500) 这雇员不存在这雇员不存在lExec update_sal(allen,1500)Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 24PL/SQL过程过程-维护过程维护过程l删除过程删除过程 DROP PROCEDURE l显示编译错误，查询数据字典显示编译错误，查询数据字典USER_ER

20、RORS，或者执行或者执行SHOW ERRORS命令。命令。l确定过程状态确定过程状态, 查询数据字典查询数据字典USER_OBJECTS。l编译过程，编译过程，ALTER PROCEDURE COMPILE;l查看过程查看过程 代码，通过查询数据字典代码，通过查询数据字典USER_SOURCE。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 25导航导航lPL/SQL过程过程lPL/SQL函数函数lPL/SQL包包Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 26函数函数l函数是一种命名存储块，可以接收输入并进函数是一种命名存

21、储块，可以接收输入并进行定义的逻辑处理以后把结果返回给调用者。行定义的逻辑处理以后把结果返回给调用者。l与过程相似，只是函数必须有返回值。与过程相似，只是函数必须有返回值。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 27函数函数-创建创建l基本语法基本语法CREATE OR REPLACE FUNCTION function_name (parameterIN|IN OUT date_type1,parameterIN|IN OUT date_type2,RETURN return_type;IS|AS declaration sectionBEGIN ex

22、ecutable statementsEXCEPTION exception handlers;ENDfunction_name; Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 28函数函数-例子例子 l下列是求从下列是求从1到某个数累加的例子到某个数累加的例子CREATE OR REPLACE FUNCTION sum(n IN number) RETURN numberAS result number:=0;BEGIN FOR i in 1n LOOP result:=result+i; END LOOP; return(result);END sum;O

23、racle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 29函数函数-调用调用l调用函数的时候需要保存返回值调用函数的时候需要保存返回值l例子例子DECLARE num1 number:=10; sum1 number:=0;BEGIN sum1:=sum(num1); DBMS_OUTPUT.PUT_LINE(“result=“|sum1);Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 30导航导航lPL/SQL过程过程lPL/SQL函数函数lPL/SQL包包Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学

24、 31PL/SQL包包l包用于逻辑组合相关的自定义类型、常量、包用于逻辑组合相关的自定义类型、常量、变量、游标、过程和函数。变量、游标、过程和函数。l简化了客户应用开发，提高性能，实现信息简化了客户应用开发，提高性能，实现信息隐藏、子程序重载等功能。隐藏、子程序重载等功能。Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 32PL/SQL包包-规范规范包规范用于定义包的公用组件，包括常量、变包规范用于定义包的公用组件，包括常量、变量、游标、自定义类型、过程和函数等。量、游标、自定义类型、过程和函数等。建立包规范语法建立包规范语法CREATE OR REPLACE

25、PACKAGE package_nameIS|AS - 定义定义公用常量、变量等公用常量、变量等 -定义定义公用过程和函数等。公用过程和函数等。END package_name;Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 33PL/SQL包包-例子例子CREATE OR REPLACE PACKAGE emp_package ISg_deptno empl.deptno%TYPE:=30;PROCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename emp

26、l.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.deptno%TYPE DEFAULT g_deptno);PROCEDURE fire_employee(eno NUMBER);FUNCTION get_sal(eno

27、NUMBER) RETURN NUMBER;END emp_package;Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 34PL/SQL包包-建立包体建立包体l包体用于实现包规范所定义的公用过程和函数，包包体用于实现包规范所定义的公用过程和函数，包体不仅可用与实现公用过程和函数，而且可以定义体不仅可用与实现公用过程和函数，而且可以定义包的私有组件（变量、常量等）包的私有组件（变量、常量等）l建立包体语法建立包体语法CREATE OR REPLACE PACKAGE BODY package_name IS|AS -定义私有变量、常量等定义私有变量、常量等 -

28、实现实现公用过程和函数公用过程和函数END package_name;Oracle 数据库应用与设计数据库应用与设计杭州电子科技大学杭州电子科技大学 35PL/SQL包包-例子例子CREATE OR REPLACE PACKAGE BODY emp_package ISFUNCTION validate_deptno(dno empl.deptno%TYPE) RETURN BOOLEANIS temp INT;BEGIN SELECT 1 INTO temp FROM dept WHERE deptno=dno; RETURN TRUE;EXCEPTION WHEN NO_DATA_FOUN

29、D THEN RETURN FALSE; END;PROZCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.dep