oracle过程、函数和程序包

过程、函数和包Oracle数据库应用与设计杭州电子科技大学2目标在完成本章学习后，你将掌握下列技能；PL/SQL过程的定义与用法PL/SQL函数的定义与用法PL/SQL包的定义与用法Oracle数据库应用与设计杭州电子科技大学3本章内容PL/SQL过程PL/SQL函数PL/SQL包Oracle数据库应用与设计杭州电子科技大学4导航PL/SQL过程PL/SQL函数PL/SQL包Oracle数据库应用与设计杭州电子科技大学5PL/SQL过程过程是指用于执行特定操作的PL/SQL块通过使用过程，不仅可以简化客户应用的开发和维护，而且可以提高应用程序的运行性能。语法：CREATE[ORREPLACE]PROCEDUREprocedure_name(argument1[mode1]datatype1,argument2[mode2]datatype2,…){IS|AS}PL/SQLBlock;其中procedure_name指定过程名，argument指定过程参数；IS或AS用于开始PL/SQL块。Oracle数据库应用与设计杭州电子科技大学6PL/SQL过程-建立无参数的过程下面以建立用于输出当前日期和时间的过程out_time为例。CREATEORREPLACEPROCEDUREout_timeISBEGINDBMS_SESSION.SET_NLS(‘NLS_DATE_FORMAT’,’’’YYYY-MM-DDHH24:MI:SS’’’);DBMS_OUTPUT.PUT_LINE(sysdate);END;/Oracle数据库应用与设计杭州电子科技大学7PL/SQL过程-建立带有输入参数的过程通过使用输入参数，可以将应用程序数据传递到过程。当定义过程参数时，默认参数模式就是输入参数可以使用IN关键字显式定义输入参数。Oracle数据库应用与设计杭州电子科技大学8CREATEORREPLACEPROCEDUREadd_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;Oracle数据库应用与设计杭州电子科技大学9PL/SQL过程-建立带有输出参数的过程通过使用输入参数，可以将数据或消息传递到调用环境和应用程序。当定义输出参数时，需要制定参数模式为OUT。下面建立用于更新雇员工资并输出雇员姓名的过程update_sal为例。Oracle数据库应用与设计杭州电子科技大学10CREATEORREPLACEPROCEDUREupdate_sal(enovarhcar2,salNUMBER,nameOUTVARCHAR2)ISBEGINUPDATEemplSETsalary=salWHEREempno=enoRETURNINGenameINTOname;END;/Oracle数据库应用与设计杭州电子科技大学11PL/SQL过程-建立带有输入输出参数的过程通过在过程中使用输入输出函数，可以调用过程时输入数据到过程，在调用结束后输出数据到调用环境和应用程序。当定义输入输出参数时，需要指定参数模式为INOUT。Oracle数据库应用与设计杭州电子科技大学12CREATEORREPLACEPROCEDUREdevide(num1INOUTNUMBER，num2INOUTNUMBER)ISv1NUMBER;v2NUMBER;BEGINv1:=TRUNC(num1/num2);v2:=MOD(num1,num2);num1:=v1;num2:=v2;END;/Oracle数据库应用与设计杭州电子科技大学13PL/SQL过程-调用过程在SQL*PLUS中调用过程时，需要使用CALL或者EXECUTE命令；而在PL/SQL块中过程可以直接应用。过程调用带参数分4种情况：–如果无参数，那么可以直接引用过程名；–如果有输入参数，那么需要为输入参数提供数值；–如果带有输出参数，那么需要使用变量接收输出结果；–如果有输入输出参数，那么调用时需要使用具有输入值的变量。Oracle数据库应用与设计杭州电子科技大学14PL/SQL过程-调用无参数的过程直接引用过程名execout_timeOracle数据库应用与设计杭州电子科技大学15PL/SQL过程-调用带有输入参数的过程需要为输入参数提供数据值execadd_empl(1111,’1980-3-4’,’lei’,’programmer’,1,7369,SYSDATE,800,null,30)Oracle数据库应用与设计杭州电子科技大学16PL/SQL过程-调用带有输出参数的过程需要使用变量接受输出参数的数据值DECLAREv_nameempl.ename%TYPE;BEGINupdate_sal(&eno,&salary,v_name);DBMS_OUTPUT.PUT_LINE(‘name:’||v_name);END;/输入雇员号的值：1111输入工资的值：2500姓名：leiOracle数据库应用与设计杭州电子科技大学17PL/SQL过程-调用带有输入输出参数的过程调用之前需要定义变量存放输入值，并在调用结束之后使用变量输出数据。DECLAREn1NUMBER:=&n1;n2NUMBER:=&n2;BEGINdivide(n1,n2);DBMS_OUTPUT.PUT_LINE(‘商:’||n1||’,余数:’||n2);END;/输入n1的值：100输入n2的值：30商：3，余数10Oracle数据库应用与设计杭州电子科技大学18为参数传递变量或数据当为参数传递变量或数据，可以采用以下3种方法•位置传递•名称传•组合传递Oracle数据库应用与设计杭州电子科技大学19PL/SQL过程-使用位置传递为参数传递变量和数据位置传递是指按照参数定义顺序依次为每个参数提供变量或者数据值当使用位置传递时，必须为所有参数提供变量或者输入数据CALLadd_empl(2222,‘1978-12-2’，’alex’,NULL,1，NULL,SYSDATE,800,NULL,30);Oracle数据库应用与设计杭州电子科技大学20PL/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)Oracle数据库应用与设计杭州电子科技大学21PL/SQL过程-使用组合传递为参数传递变量和数据组合传递是指在调用子程序时结合使用位置传递和名称传递execadd_emp(4444,‘1985-2-3’,’AGASI’,‘tester’,1,null,indate=SYSDATE,salary=1200,allow=0,deptno=30)Oracle数据库应用与设计杭州电子科技大学22PL/SQL过程-过程开发示例为过程参数指定默认值CREATEORREPLACEPROCEDUREadd_emp(empnoempl,empno%TYPE,ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE,ejobempl.ejob%TYPEDEFAULT‘programmer’,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;/Oracle数据库应用与设计杭州电子科技大学23PL/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)Oracle数据库应用与设计杭州电子科技大学24PL/SQL过程-维护过程删除过程DROPPROCEDURE显示编译错误，查询数据字典USER_ERRORS，或者执行SHOWERRORS命令。确定过程状态,查询数据字典USER_OBJECTS。编译过程，ALTERPROCEDURE…COMPILE;查看过程代码，通过查询数据字典USER_SOURCE。Oracle数据库应用与设计杭州电子科技大学25导航PL/SQL过程PL/SQL函数PL/SQL包Oracle数据库应用与设计杭州电子科技大学26函数函数是一种命名存储块，可以接收输入并进行定义的逻辑处理以后把结果返回给调用者。与过程相似，只是函数必须有返回值。Oracle数据库应用与设计杭州电子科技大学27函数-创建基本语法CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter[{IN|INOUT}]date_type1,parameter[{IN|INOUT}]date_type2,…]RETURNreturn_type;{IS|AS}declarationsectionBEGINexecutablestatementsEXCEPTIONexceptionhandlers;END[function_name];Oracle数据库应用与设计杭州电子科技大学28函数-例子下列是求从1到某个数累加的例子CREATEORREPLACEFUNCTIONsum(nINnumber)RETURNnumberASresultnumber:=0;BEGINFORiin1…nLOOPresult:=result+i;ENDLOOP;return(result);ENDsum;Oracle数据库应用与设计杭州电子科技大学29函数-调用调用函数的时候需要保存返回值例子DECLAREnum1number:=10;sum1number:=0;BEGINsum1:=sum(num1);DBMS_OUTPUT.PUT_LINE(“result=“||sum1);Oracle数据库应用与设计杭州电子科技大学30导航PL/SQL过程PL/SQL函数PL