Oracle数据库子程序和程序包.ppt

子程序和程序包,2,回顾,游标用于处理查询结果集中的数据游标类型有：隐式游标、显式游标和REF游标隐式游标由PL/SQL自动定义、打开和关闭显式游标用于处理返回多行的查询显式游标可以删除和更新活动集中的行要处理结果集中所有记录时，可使用循环游标在声明REF游标时，不需要将SELECT语句与其关联,3,目标,创建和使用子程序创建和使用程序包,4,子程序2-1,命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程执行某些操作函数执行操作并返回值,5,子程序2-2,子程序的优点：模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全,6,过程8-1,过程是用于完成特定任务的子程序例如：,前往售票厅,询问关于车票的信息,排队等候,在柜台购买车票,7,过程8-2,创建过程的语法：CREATEORREPLACEPROCEDURE()IS|ASBEGINEXCEPTIONEND;,创建过程，可指定运行过程需传递的参数,处理异常,包括在过程中要执行的语句,8,过程8-3,CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE(雇员姓名是|empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(雇员编号未找到);ENDfind_emp;/,9,过程8-4,过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值,10,过程8-5,SQLCREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code|项目的说明为|v_itemdesc);END;/SQLSETSERVEROUTPUTONSQLEXECUTEitemdesc(i201);,执行过程的语法：EXECUTEprocedure_name(parameters_list);,11,过程8-6,SQLCREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentitySETSERVEROUTONSQLDECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE(num1=|num1);DBMS_OUTPUT.PUT_LINE(num2=|num2);END;/,13,过程8-8,将过程的执行权限授予其他用户：删除过程：,SQLGRANTEXECUTEONfind_empTOMARTIN;SQLGRANTEXECUTEONswapTOPUBLIC;,SQLDROPPROCEDUREfind_emp;,14,函数4-1,函数是可以返回值的命名的PL/SQL子程序。创建函数的语法：CREATEORREPLACEFUNCTION(param1,param2)RETURNIS|ASlocaldeclarationsBEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;,15,函数4-2,定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句,16,函数4-3,创建函数：从SQL语句调用函数：,CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN朋友，您好;END;/,SQLSELECTfun_helloFROMDUAL;,17,函数4-4,CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice=min_priceANDpriceCREATEORREPLACEPACKAGEcur_packISCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPE;PROCEDUREord_pro(vcodeVARCHAR2);ENDcur_pack;/,SQLCREATEORREPLACEPACKAGEBODYcur_packASCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPEISSELECT*FROMorder_masterWHEREVENCODE=vcode;PROCEDUREord_pro(vcodeVARCHAR2)ISor_recorder_master%ROWTYPE;BEGINOPENord_cur(vcode);LOOPFETCHord_curINTOor_rec;EXITWHENord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(返回的值为|or_rec.orderno);ENDLOOP;ENDord_pro;ENDcur_pack;/,27,有关子程序和程序包的信息,USER_OBJECTS视图包含用户创建的子程序和程序包的信息USER_SOURCE视图存储子程序和程序包的源代码,SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN(PROCEDURE,FUNCTION,PACKAGE,PACKAGEBODY);,SELECTline,textFROMUSER_SOURCEWHERENAME=TEST;,28,总结,子程序是命名的PL/SQL块