40子程序和程序包

2-1Copyright©OracleCorporation,2001.Allrightsreserved.子程序和程序包2-2Copyright©OracleCorporation,2001.Allrightsreserved.回顾•游标用于处理查询结果集中的数据•游标类型有：隐式游标、显式游标和REF游标•隐式游标由PL/SQL自动定义、打开和关闭•显式游标用于处理返回多行的查询•显式游标可以删除和更新活动集中的行•要处理结果集中所有记录时，可使用循环游标•在声明REF游标时，不需要将SELECT语句与其关联2-3Copyright©OracleCorporation,2001.Allrightsreserved.目标•创建和使用子程序•创建和使用程序包2-4Copyright©OracleCorporation,2001.Allrightsreserved.子程序2-1•命名的PL/SQL块，编译并存储在数据库中。•子程序的各个部分：–声明部分–可执行部分–异常处理部分(可选)•子程序的分类：–过程－执行某些操作–函数－执行操作并返回值2-5Copyright©OracleCorporation,2001.Allrightsreserved.子程序2-2子程序的优点：•模块化–将程序分解为逻辑模块•可重用性–可以被任意数目的程序调用•可维护性–简化维护操作•安全性–通过设置权限，使数据更安全2-6Copyright©OracleCorporation,2001.Allrightsreserved.过程8-1•过程是用于完成特定任务的子程序•例如：前往售票厅询问关于车票的信息排队等候在柜台购买车票2-7Copyright©OracleCorporation,2001.Allrightsreserved.过程8-2创建过程的语法：CREATE[ORREPLACE]PROCEDUREprocedurename[(parameterlist)]IS|ASlocalvariabledeclarationBEGINexecutablestatements[EXCEPTIONexceptionhandlers]END;创建过程，可指定运行过程需传递的参数处理异常包括在过程中要执行的语句2-8Copyright©OracleCorporation,2001.Allrightsreserved.过程8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇员编号未找到');ENDfind_emp;/2-9Copyright©OracleCorporation,2001.Allrightsreserved.过程8-4过程参数的三种模式：•IN–用于接受调用程序的值–默认的参数模式•OUT–用于向调用程序返回值•INOUT–用于接受调用程序的值，并向调用程序返回更新的值2-10Copyright©OracleCorporation,2001.Allrightsreserved.过程8-5SQLCREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc);END;/SQLSETSERVEROUTPUTONSQLEXECUTEitemdesc('i201');执行过程的语法：EXECUTEprocedure_name(parameters_list);2-11Copyright©OracleCorporation,2001.Allrightsreserved.过程8-6SQLCREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity200THENvalue2:=100;ENDIF;END;DECLAREvalue1VARCHAR2(5):='i202';value2NUMBER;BEGINtest(value1,value2);DBMS_OUTPUT.PUT_LINE('value2的值为'||TO_CHAR(value2));END;/2-12Copyright©OracleCorporation,2001.Allrightsreserved.过程8-7CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,p2INOUTNUMBER)ISv_tempNUMBER;BEGINv_temp:=p1;p1:=p2;p2:=v_temp;END;/SQLSETSERVEROUTONSQLDECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE('num1='||num1);DBMS_OUTPUT.PUT_LINE('num2='||num2);END;/2-13Copyright©OracleCorporation,2001.Allrightsreserved.过程8-8•将过程的执行权限授予其他用户：•删除过程：SQLGRANTEXECUTEONfind_empTOMARTIN;SQLGRANTEXECUTEONswapTOPUBLIC;SQLDROPPROCEDUREfind_emp;2-14Copyright©OracleCorporation,2001.Allrightsreserved.函数4-1•函数是可以返回值的命名的PL/SQL子程序。•创建函数的语法：CREATE[ORREPLACE]FUNCTIONfunctionname[(param1,param2)]RETURNdatatypeIS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;2-15Copyright©OracleCorporation,2001.Allrightsreserved.函数4-2定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句2-16Copyright©OracleCorporation,2001.Allrightsreserved.函数4-3•创建函数：•从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQLSELECTfun_helloFROMDUAL;2-17Copyright©OracleCorporation,2001.Allrightsreserved.函数4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice=min_priceANDprice=max_priceTHENRETURN'输入的单价介于最低价与最高价之间';ELSERETURN'超出范围';ENDIF;END;/DECLAREPNUMBER:=300;MSGVARCHAR2(200);BEGINMSG:=item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG);END;/2-18Copyright©OracleCorporation,2001.Allrightsreserved.过程和函数的比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN语句2-19Copyright©OracleCorporation,2001.Allrightsreserved.自主事务处理2-1•自主事务处理–主事务处理启动独立事务处理–然后主事务处理被暂停–自主事务处理子程序内的SQL操作–然后终止自主事务处理–恢复主事务处理•PRAGMAAUTONOMOUS_TRANSACTION用于标记子程序为自主事务处理2-20Copyright©OracleCorporation,2001.Allrightsreserved.自主事务处理2-2•自主事务处理的特征：–与主事务处理的状态无关–提交或回滚操作不影响主事务处理–自主事务处理的结果对其他事务是可见的–能够启动其他自主事务处理2-21Copyright©OracleCorporation,2001.Allrightsreserved.程序包•程序包是对相关过程、函数、变量、游标和异常等对象的封装•程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体2-22Copyright©OracleCorporation,2001.Allrightsreserved.创建程序包2-1程序包规范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主体CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];2-23Copyright©OracleCorporation,2001.Allrightsreserved.创建程序包2-2CREATEORREPLACEPACKAGEpack_meISPROCEDUREorder_proc(ornoVARCHAR2);FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;/CREATEORREPLACEPACKAGEBODYpack_meASPROCEDUREorder_proc(ornoVARCHAR2)ISstatCHAR(1);BEGINSELECTostatusINTOstatFROMorder_masterWHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun(ornosVAR