游标管理存储过程和函数

存储过程和函数子程序2-1命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程－执行某些操作函数－执行操作并返回值子程序2-2子程序的优点：模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全过程8-2创建过程的语法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;创建过程，可指定运行过程需传递的参数处理异常包括在过程中要执行的语句过程8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇员编号未找到');ENDfind_emp;/过程8-4过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值过程8-5SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');执行过程的语法：

EXECUTEprocedure_name(parameters_list);过程8-6SQL>CREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity<200THENvalue2:=100;ENDIF;END;DECLAREvalue1VARCHAR2(5):='i202';value2NUMBER;BEGINtest(value1,value2);DBMS_OUTPUT.PUT_LINE('value2的值为'||TO_CHAR(value2));END;/过程8-7CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,p2INOUTNUMBER)ISv_tempNUMBER;BEGINv_temp:=p1;p1:=p2;p2:=v_temp;END;/SQL>SETSERVEROUTONSQL>DECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE('num1='||num1);DBMS_OUTPUT.PUT_LINE('num2='||num2);END;/过程8-8将过程的执行权限授予其他用户：删除过程：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;SQL>DROPPROCEDUREfind_emp;函数4-1函数是可以返回值的命名的PL/SQL子程序。

创建函数的语法：

CREATE[ORREPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;函数4-2定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型Record访问函数的两种方式：使用PL/SQL块使用SQL语句函数4-3创建函数：从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQL>SELECTfun_helloFROMDUAL;函数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;/过程和函数的比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至