oracle过程、函数和程序包.ppt

过程、函数和包,目标,在完成本章学习后，你将掌握下列技能；,PL/SQL过程的定义与用法PL/SQL函数的定义与用法PL/SQL包的定义与用法,本章内容,PL/SQL过程PL/SQL函数PL/SQL包,导航,PL/SQL过程PL/SQL函数PL/SQL包,PL/SQL过程,过程是指用于执行特定操作的PL/SQL块通过使用过程，不仅可以简化客户应用的开发和维护，而且可以提高应用程序的运行性能。语法：CREATEORREPLACEPROCEDUREprocedure_name(argument1mode1datatype1,argument2mode2datatype2,)IS|ASPL/SQLBlock;其中procedure_name指定过程名，argument指定过程参数；IS或AS用于开始PL/SQL块。,PL/SQL过程-建立无参数的过程,下面以建立用于输出当前日期和时间的过程out_time为例。CREATEORREPLACEPROCEDUREout_timeISBEGINDBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DDHH24:MI:SS);DBMS_OUTPUT.PUT_LINE(sysdate);END;/,PL/SQL过程-建立带有输入参数的过程,通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默认参数模式就是输入参数可以使用IN关键字显式定义输入参数。,CREATEORREPLACEPROCEDUREadd_empl(empnoempl.empno%TYPE,enameempl.ename%TYPE,ebirthdayempl.ebirthday%TYPE,egradeempl.egrade%TYPE,ejobempl.ejob%TYPE,emajorempl.emajor%TYPE,indateempl.indate%TYPE,salaryempl.salary%TYPE,allowempl.allow%TYPE,deptnoempl.deptno%TYPE)ISBEGININSERTINTOemplVALUES(empno,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;,PL/SQL过程-建立带有输出参数的过程,通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要制定参数模式为OUT。下面建立用于更新雇员工资并输出雇员姓名的过程update_sal为例。,CREATEORREPLACEPROCEDUREupdate_sal(enovarhcar2,salNUMBER,nameOUTVARCHAR2)ISBEGINUPDATEemplSETsalary=salWHEREempno=enoRETURNINGenameINTOname;END;/,PL/SQL过程-建立带有输入输出参数的过程,通过在过程中使用输入输出函数，可以调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为INOUT。,CREATEORREPLACEPROCEDUREdevide(num1INOUTNUMBER，num2INOUTNUMBER)ISv1NUMBER;v2NUMBER;BEGINv1:=TRUNC(num1/num2);v2:=MOD(num1,num2);num1:=v1;num2:=v2;END;/,PL/SQL过程-调用过程,在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令；而在PL/SQL块中过程可以直接应用。过程调用带参数分4种情况：如果无参数，那么可以直接引用过程名；如果有输入参数，那么需要为输入参数提供数值；如果带有输出参数，那么需要使用变量接收输出结果；如果有输入输出参数，那么调用时需要使用具有输入值的变量。,PL/SQL过程-调用无参数的过程,直接引用过程名execout_time,PL/SQL过程-调用带有输入参数的过程,需要为输入参数提供数据值execadd_empl(1111,1980-3-4,lei,programmer,1,7369,SYSDATE,800,null,30),PL/SQL过程-调用带有输出参数的过程,需要使用变量接受输出参数的数据值DECLAREv_nameempl.ename%TYPE;BEGINupdate_sal(/输入雇员号的值：1111输入工资的值：2500姓名：lei,PL/SQL过程-调用带有输入输出参数的过程,调用之前需要定义变量存放输入值，并在调用结束之后使用变量输出数据。DECLAREn1NUMBER:=/输入n1的值：100输入n2的值：30商：3，余数10,为参数传递变量或数据,当为参数传递变量或数据，可以采用以下3种方法位置传递名称传组合传递,PL/SQL过程-使用位置传递为参数传递变量和数据,位置传递是指按照参数定义顺序依次为每个参数提供变量或者数据值当使用位置传递时，必须为所有参数提供变量或者输入数据CALLadd_empl(2222,1978-12-2，alex,NULL,1，NULL,SYSDATE,800,NULL,30);,PL/SQL过程-使用名称传递为参数传递变量和数据,名称传递是指通过制定参数名为其提供变量或者数据当使用名称传递时，需要使用=为参数提供变量或者数值execadd_emp(empno=3333,ebirghday=,1989-2-2，indate=null,egrade=1,ename=JOHN,ejob=null,major=null,salary=null,allow=null,deptno=null),PL/SQL过程-使用组合传递为参数传递变量和数据,组合传递是指在调用子程序时结合使用位置传递和名称传递execadd_emp(4444,1985-2-3,AGASI,tester,1,null,indate=SYSDATE,salary=1200,allow=0,deptno=30),PL/SQL过程-过程开发示例,为过程参数指定默认值CREATEORREPLACEPROCEDUREadd_emp(empnoempl,empno%TYPE,ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE,ejobempl.ejob%TYPEDEFAULTprogrammer,egradeempl.egrade%TYPEDEFAULT1,majorempl.mgr%TYPE,indateempl.indate%TYPEDEFAULTSYSDATE,salaryempl.salary%TYPEDEFAULT1000,allowempl.allow%TYPEDEFAULT0,deptnoempl.deptno%TYPE)ISBEGININSERTINTOempVALUES(empno,ebirthday,ename,ejob,egrademajor,indate,salary,allow,deptno);END;/,PL/SQL过程-使用异常处理,CREATEORREPLACEPROCEDUREupdate_sal(nameempl.ename%TYPE,salempl.salary%TYPE)ISe_no_rowsEXCEPTION;BEGINUPDATEemplSETsalary=salWHERELOWER(ename)=LOWER(name);IFSQL%NOTFOUNDTHENRAISEe_no_rows;ENDIF;EXCEPTIONWHENe_no_rowsTHENDBMS_OUTPUT.PUT_LINE(这雇员不存在);END;Execupdate_sal(jarbus,1500)这雇员不存在Execupdate_sal(allen,1500),PL/SQL过程-维护过程,删除过程DROPPROCEDURE显示编译错误，查询数据字典USER_ERRORS，或者执行SHOWERRORS命令。确定过程状态,查询数据字典USER_OBJECTS。编译过程，ALTERPROCEDURECOMPILE;查看过程代码，通过查询数据字典USER_SOURCE。,导航,PL/SQL过程PL/SQL函数PL/SQL包,函数,函数是一种命名存储块，可以接收输入并进行定义的逻辑处理以后把结果返回给调用者。与过程相似，只是函数必须有返回值。,函数-创建,基本语法CREATEORREPLACEFUNCTIONfunction_name(parameterIN|INOUTdate_type1,parameterIN|INOUTdate_type2,RETURNreturn_type;IS|ASdeclarationsectionBEGINexecutablestatementsEXCEPTIONexceptionhandlers;ENDfunction_name;,函数-例子,下列是求从1到某个数累加的例子CREATEORREPLACEFUNCTIONsum(nINnumber)RETURNnumberASresultnumber:=0;BEGINFORiin1nLOOPresult:=result+i;ENDLOOP;return(result);ENDsum;,函数-调用,调用函数的时候需要保存返回值例子DECLAREnum1number:=10;sum1number:=0;BEGINsum1:=sum(num1);DBMS_OUTPUT.PUT_LINE(“result=“|sum1);,导航,PL/SQL过程PL/SQL函数PL/SQL包,PL/SQL包,包用于逻辑组合相关的自定义类型、常量、变量、游标、过程和函数。简化了客户应用开发，提高性能，实现信息隐藏、子程序重载等功能。,PL/SQL包-规范,包规范用于定义包的公用组件，包括常量、变量、游标、自定义类型、过程和函数等。建立包规范语法CREATEORREPLACEPACKAGEpackage_nameIS|AS-定义公用常量、变量等-定义公用过程和函数等。ENDpackage_name;,PL/SQL包-例子,CREATEORREPLACEPACKAGEemp_packageISg_deptnoempl.deptno%TYPE:=30;PROCEDUREadd_employee(empnoempl.empno%TYPE,ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE,ejobempl.ejob%TYPEDEFAULTprogrammer,egradeempl.egrade%TYPE,majorempl.mgr%TYPE,indateempl.indate%TYPEDEFAULTSYSDATE,salaryempl.salary%TYPEDEFAULT1000,allowempl.allow%TYPE,deptnoempl.deptno%TYPEDEFAULTg_deptno);PROCEDUREfire_employee(enoNUMBER);FUNCTIONget_sal(enoNUMBER)RETURNNUMBER;ENDemp_package;,PL/SQL包-建立包体,包体用于实现包规范所定义的公用过程和函数，包体不仅可用与实现公用过程和函数，而且可以定义包的私有组件（变量、常量等）建立包体语法CREATEORREPLACEPACKAGEBODYpackage_nameIS|AS-定义私有变量、常量等-实现公用过程和函数ENDpackage_name;,PL/SQL包-例子,CREATEORREPLACEPACKAGEBODYemp_packageISFUNCTIONvalidate_deptno(dnoempl.deptno%TYPE)RETURNBOOLEANIStempINT;BEGINSELECT1INTOtempFROMdeptWHEREdeptno=dno;RETURNTRUE;EXCEPTIONWHENNO_DATA_FOUNDTHENRETURNFALSE;END;PROZCEDUREadd_employee(empnoempl.empno%TYPE,ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE,ejobempl.ejob%TYPEDEFAULTprogrammer,egradeempl.egrade%TYPE,majorempl.mgr%TYPE,indateempl.indate%TYPEDEFAULTSYSDATE,salaryempl.salary%TYPEDEFAULT1000,allowempl.allow%TYPE,deptnoempl.deptno%TYPEDEFAULTg_deptno);,ISBEGINIFvalidate_deptno(deptno)THENINSER