《oracle过程函数和程序包.ppt》由会员分享，可在线阅读，更多相关《oracle过程函数和程序包.ppt（39页珍藏版）》请在三一办公上搜索。
1、过程、函数和包,目标,在完成本章学习后，你将掌握下列技能；,PL/SQL过程的定义与用法PL/SQL函数的定义与用法PL/SQL包的定义与用法,本章内容,PL/SQL过程PL/SQL函数PL/SQL包,导航,PL/SQL过程PL/SQL函数PL/SQL包,PL/SQL过程,过程是指用于执行特定操作的PL/SQL块通过使用过程，不仅可以简化客户应用的开发和维护，而且可以提高应用程序的运行性能。语法：CREATE OR REPLACE PROCEDURE procedure_name(argument1mode1 datatype1,argument2mode2 datatype2,)IS|ASP
2、L/SQL Block;其中procedure_name指定过程名，argument指定过程参数；IS或AS用于开始PL/SQL块。,PL/SQL过程-建立无参数的过程,下面以建立用于输出当前日期和时间的过程out_time为例。CREATE OR REPLACE PROCEDURE out_timeISBEGIN DBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS);DBMS_OUTPUT.PUT_LINE(sysdate);END;/,PL/SQL过程-建立带有输入参数的过程,通过使用输入参数，可以将应用程序数据传递到过程。
3、当定义过程参数时，默认参数模式就是输入参数可以使用IN关键字显式定义输入参数。,CREATE OR REPLACE 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
4、.deptno%TYPE)IS BEGIN INSERT INTO empl VALUES(empno,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;,PL/SQL过程-建立带有输出参数的过程,通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要制定参数模式为OUT。下面建立用于更新雇员工资并输出雇员姓名的过程update_sal为例。,CREATE OR REPLACE PROCEDURE update_sal(eno varhcar2,sal NUMBER,name OUT
5、VARCHAR2)IS BEGIN UPDATE empl SET salary=sal WHERE empno=eno RETURNING ename INTO name;END;/,PL/SQL过程-建立带有输入输出参数的过程,通过在过程中使用输入输出函数，可以调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为IN OUT。,CREATE OR REPLACE PROCEDURE devide(num1 IN OUT NUMBER，num2 IN OUT NUMBER)IS v1 NUMBER;v2 NUMBER;BEGIN v1:
6、=TRUNC(num1/num2);v2:=MOD(num1,num2);num1:=v1;num2:=v2;END;/,PL/SQL过程-调用过程,在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令；而在PL/SQL块中过程可以直接应用。过程调用带参数分4种情况：如果无参数，那么可以直接引用过程名；如果有输入参数，那么需要为输入参数提供数值；如果带有输出参数，那么需要使用变量接收输出结果；如果有输入输出参数，那么调用时需要使用具有输入值的变量。,PL/SQL过程-调用无参数的过程,直接引用过程名exec out_time,PL/SQL过程-调用带有输入参数的过程,需要为输
7、入参数提供数据值exec add_empl(1111,1980-3-4,lei,programmer,1,7369,SYSDATE,800,null,30),PL/SQL过程-调用带有输出参数的过程,需要使用变量接受输出参数的数据值DECLARE v_name empl.ename%TYPE;BEGIN update_sal(/输入 雇员号的值：1111输入工资的值：2500姓名：lei,PL/SQL过程-调用带有输入输出参数的过程,调用之前需要定义变量存放输入值，并在调用结束之后使用变量输出数据。DECLARE n1 NUMBER:=/输入n1的值：100输入n2的值：30商：3，余数10,
8、为参数传递变量或数据,当为参数传递变量或数据，可以采用以下3种方法位置传递名称传组合传递,PL/SQL过程-使用位置传递为参数传递变量和数据,位置传递是指按照参数定义顺序依次为每个参数提供变量或者数据值当使用位置传递时，必须为所有参数提供变量或者输入数据CALL add_empl(2222,1978-12-2，alex,NULL,1，NULL,SYSDATE,800,NULL,30);,PL/SQL过程-使用名称传递为参数传递变量和数据,名称传递是指通过制定参数名为其提供变量或者数据当使用名称传递时，需要使用=为参数提供变量或者数值exec add_emp(empno=3333,ebirghd
9、ay=,1989-2-2，indate=null,egrade=1,ename=JOHN,ejob=null,major=null,salary=null,allow=null,deptno=null),PL/SQL过程-使用组合传递为参数传递变量和数据,组合传递是指在调用子程序时结合使用位置传递和名称传递exec add_emp(4444,1985-2-3,AGASI,tester,1,null,indate=SYSDATE,salary=1200,allow=0,deptno=30),PL/SQL过程-过程开发示例,为过程参数指定默认值CREATE OR REPLACE PROCEDURE
10、 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 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.dep
11、tno%TYPE)ISBEGIN INSERT INTO emp VALUES(empno,ebirthday,ename,ejob,egrade major,indate,salary,allow,deptno);END;/,PL/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
12、 SQL%NOTFOUND THEN RAISE e_no_rows;END IF;EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(这雇员不存在);END;Exec update_sal(jarbus,1500)这雇员不存在Exec update_sal(allen,1500),PL/SQL过程-维护过程,删除过程 DROP PROCEDURE 显示编译错误，查询数据字典USER_ERRORS，或者执行SHOW ERRORS命令。确定过程状态,查询数据字典USER_OBJECTS。编译过程，ALTER PROCEDURE COMPILE;查
13、看过程 代码，通过查询数据字典USER_SOURCE。,导航,PL/SQL过程PL/SQL函数PL/SQL包,函数,函数是一种命名存储块，可以接收输入并进行定义的逻辑处理以后把结果返回给调用者。与过程相似，只是函数必须有返回值。,函数-创建,基本语法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
14、EPTION exception handlers;ENDfunction_name;,函数-例子,下列是求从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;,函数-调用,调用函数的时候需要保存返回值例子DECLARE num1 number:=10;sum1 number:=0;BEGIN sum1:=sum(num1);DBM
15、S_OUTPUT.PUT_LINE(“result=“|sum1);,导航,PL/SQL过程PL/SQL函数PL/SQL包,PL/SQL包,包用于逻辑组合相关的自定义类型、常量、变量、游标、过程和函数。简化了客户应用开发，提高性能，实现信息隐藏、子程序重载等功能。,PL/SQL包-规范,包规范用于定义包的公用组件，包括常量、变量、游标、自定义类型、过程和函数等。建立包规范语法CREATE OR REPLACE PACKAGE package_nameIS|AS-定义公用常量、变量等-定义公用过程和函数等。END package_name;,PL/SQL包-例子,CREATE OR REPLAC
16、E PACKAGE emp_package ISg_deptno empl.deptno%TYPE:=30;PROCEDURE 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 DE
17、FAULT 1000,allow empl.allow%TYPE,deptno empl.deptno%TYPE DEFAULT g_deptno);PROCEDURE fire_employee(eno NUMBER);FUNCTION get_sal(eno NUMBER)RETURN NUMBER;END emp_package;,PL/SQL包-建立包体,包体用于实现包规范所定义的公用过程和函数，包体不仅可用与实现公用过程和函数，而且可以定义包的私有组件（变量、常量等）建立包体语法CREATE OR REPLACE PACKAGE BODY package_name IS|AS-定义私
18、有变量、常量等-实现公用过程和函数END package_name;,PL/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_FOUND THEN RETURN FALSE;END;PROZCEDURE add_employe
19、e(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.deptno%TYPE DEFAULT g_deptno);
20、,ISBEGIN IF validate_deptno(deptno)THEN INSERT INTO emp VALUES(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 em
21、pno=eno;RETURN v_sal;END;END emp_package;,PL/SQL包-调用包组件,调用包公用变量例子：emp_package.g_deptno:=20调用包公用过程例子：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 20,调用包公用函数 例子：VAR salary NUMBER exec:salary:=emp_package.get_sal(1566)PRINT salary 执行结果：salary-2975,本章内容回顾,掌握过程的用法掌握函数的用法掌握包的用法,
