oracle过程、函数和程序包8231.pptx

《oracle过程、函数和程序包8231.pptx》由会员分享，可在线阅读，更多相关《oracle过程、函数和程序包8231.pptx（39页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、7过程、函数和包Oracle 数据库应用与设计杭州电子科技大学 2目标在完成本章学习后，你将掌握下列技能；l PL/SQL 过程的定义与用法l PL/SQL 函数的定义与用法l PL/SQL 包的定义与用法Oracle 数据库应用与设计杭州电子科技大学 3本章内容l PL/SQL 过程l PL/SQL 函数l PL/SQL 包Oracle 数据库应用与设计杭州电子科技大学 4导航l PL/SQL 过程l PL/SQL 函数l PL/SQL 包Oracle 数据库应用与设计杭州电子科技大学 5PL/SQL 过程l 过程是指用于执行特定操作的PL/SQL 块l 通过使用过程，不仅可以简化客户应用的

2、开发和维护，而且可以提高应用程序的运行性能。l 语法：CREATE OR REPLACE PROCEDURE procedure_name(argument1mode1 datatype1,argument2mode2 datatype2,)IS|ASPL/SQL Block;其中procedure_name 指定过程名，argument 指定过程参数；IS 或AS 用于开始PL/SQL 块。Oracle 数据库应用与设计杭州电子科技大学 6PL/SQL 过程-建立无参数的过程l 下面以建立用于输出当前日期和时间的过程out_time 为例。CREATE OR REPLACE PROCEDUR

3、E out_timeISBEGIN DBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS);DBMS_OUTPUT.PUT_LINE(sysdate);END;/Oracle 数据库应用与设计杭州电子科技大学 7PL/SQL 过程-建立带有输入参数的过程l 通过使用输入参数，可以将应用程序数据传递到过程。l 当定义过程参数时，默认参数模式就是输入参数l 可以使用IN 关键字显式定义输入参数。Oracle 数据库应用与设计杭州电子科技大学 8CREATE OR REPLACE PROCEDURE add_empl(empno emp

4、l.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 V ALUES(empno,ebirthday,ename,ejob,egrade,emajo

5、r,indate,salary,allow,depno);END;Oracle 数据库应用与设计杭州电子科技大学 9PL/SQL 过程-建立带有输出参数的过程l 通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。l 当定义输出参数时，需要制定参数模式为OUT。l 下面建立用于更新雇员工资并输出雇员姓名的过程update_sal 为例。Oracle 数据库应用与设计杭州电子科技大学 10CREATE OR REPLACE PROCEDURE update_sal(eno varhcar2,sal NUMBER,name OUT V ARCHAR2)IS BEGIN UPDATE emp

6、l SET salary=sal WHERE empno=eno RETURNING ename INTO name;END;/Oracle 数据库应用与设计杭州电子科技大学 11PL/SQL 过程-建立带有输入输出参数的过程l 通过在过程中使用输入输出函数，可以调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。l 当定义输入输出参数时，需要指定参数模式为IN OUT。Oracle 数据库应用与设计杭州电子科技大学 12CREATE OR REPLACE PROCEDURE devide(num1 IN OUT NUMBER，num2 IN OUT NUMBER)IS v1

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

8、 数据库应用与设计杭州电子科技大学 14PL/SQL 过程-调用无参数的过程l 直接引用过程名exec out_timeOracle 数据库应用与设计杭州电子科技大学 15PL/SQL 过程-调用带有输入参数的过程l 需要为输入参数提供数据值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_

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

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

11、为参数传递变量和数据l 名称传递是指通过制定参数名为其提供变量或者数据l 当使用名称传递时，需要使用=为参数提供变量或者数值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 组合传递是指在调用子程序时结合使用位置传递和名称传递exec add_emp(4444,1985-2-3

12、,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,ename empl.ename%TYPE,ejob empl.ejob%TYPE DEFAULT programmer,egrade empl.egrade%TYPE DEFAULT

13、 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 V ALUES(empno,ebirthday,ename,ejob,egrade major,indate,salary,allow,deptno);END;/Oracle 数据库应用与设计杭州电子科技大学 23PL/SQL 过程-使用异

14、常处理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 SQL%NOTFOUND THEN RAISE e_no_rows;END IF;EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(这雇员不存在);END;l Exec update_sal(jarb

15、us,1500)这雇员不存在l Exec update_sal(allen,1500)Oracle 数据库应用与设计杭州电子科技大学 24PL/SQL 过程-维护过程l 删除过程 DROP PROCEDURE l 显示编译错误，查询数据字典USER_ERRORS，或者执行SHOW ERRORS 命令。l 确定过程状态,查询数据字典USER_OBJECTS。l 编译过程，ALTER PROCEDURE COMPILE;l 查看过程 代码，通过查询数据字典USER_SOURCE。Oracle 数据库应用与设计杭州电子科技大学 25导航l PL/SQL 过程l PL/SQL 函数l PL/SQL 包

16、Oracle 数据库应用与设计杭州电子科技大学 26函数l 函数是一种命名存储块，可以接收输入并进行定义的逻辑处理以后把结果返回给调用者。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 executable statementsEXC

17、EPTION 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;Oracle 数据库应用与设计杭州电子科技大学 29函数-调用l 调用函数的时候需要保存返回值l 例子DECLARE n

18、um1 number:=10;sum1 number:=0;BEGIN sum1:=sum(num1);DBMS_OUTPUT.PUT_LINE(“result=“|sum1);Oracle 数据库应用与设计杭州电子科技大学 30导航l PL/SQL 过程l PL/SQL 函数l PL/SQL 包Oracle 数据库应用与设计杭州电子科技大学 31PL/SQL 包l 包用于逻辑组合相关的自定义类型、常量、变量、游标、过程和函数。l 简化了客户应用开发，提高性能，实现信息隐藏、子程序重载等功能。Oracle 数据库应用与设计杭州电子科技大学 32PL/SQL 包-规范包规范用于定义包的公用组件，

19、包括常量、变量、游标、自定义类型、过程和函数等。建立包规范语法CREATE OR REPLACE 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,enam

20、e 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.deptno%TYPE DEFAULT g_deptno);PROCEDURE fire_employee(eno NUMBER);FUNCTION get_sal(eno N

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

22、p_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_FOUND THEN RETURN FALSE;END;PROZCEDURE add_employee(empno empl.empno%TYPE,ebirthday empl.ebirthday%TYPE,ename empl.ename%TYPE,ejo

23、b 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);Oracle 数据库应用与设计杭州电子科技大学 36ISBEGIN IF validate_deptno(deptno)THEN INSERT INTO em

24、p V ALUES(empno,ebirthday,ename,ejob,egrade,major,indate,salary,allow,deptno);END IF;END;PROCEDURE fire_employee(eno NUMBER)ISBEGINFUNCTION get_sal(eno NUMBER)RETURN NUMBERIS v_sal empl.salary%TYPE;BEGIN SELECT salary INTO v_sal FROM empl WHERE empno=eno;RETURN v_sal;END;END emp_package;Oracle 数据库应用

25、与设计杭州电子科技大学 37PL/SQL 包-调用包组件 l 调用包公用变量例子：emp_package.g_deptno:=20l 调用包公用过程例子：emp_package.g_deptno:=20emp_package.add_employee(1566,1982-9-12,MARY,1,major=7788,comm=0);select ename,deptno FROM empl WHERE empno=1566执行结果：ENAME DEPTNO-MARY 20Oracle 数据库应用与设计杭州电子科技大学 38l 调用包公用函数 例子：V AR salary NUMBER exec:salary:=emp_package.get_sal(1566)PRINT salary 执行结果：salary-2975Oracle 数据库应用与设计杭州电子科技大学 39本章内容回顾l 掌握过程的用法l 掌握函数的用法l 掌握包的用法