游標(biāo)管理存儲(chǔ)過(guò)程和函數(shù)

存儲(chǔ)過(guò)程和函數(shù)子程序2-1命名的PL/SQL塊，編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中。子程序的各個(gè)部分：聲明部分可執(zhí)行部分異常處理部分(可選)子程序的分類：過(guò)程－執(zhí)行某些操作函數(shù)－執(zhí)行操作并返回值子程序2-2子程序的優(yōu)點(diǎn)：模塊化將程序分解為邏輯模塊可重用性可以被任意數(shù)目的程序調(diào)用可維護(hù)性簡(jiǎn)化維護(hù)操作安全性通過(guò)設(shè)置權(quán)限，使數(shù)據(jù)更安全過(guò)程8-2創(chuàng)建過(guò)程的語(yǔ)法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;創(chuàng)建過(guò)程，可指定運(yùn)行過(guò)程需傳遞的參數(shù)處理異常包括在過(guò)程中要執(zhí)行的語(yǔ)句過(guò)程8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇員姓名是'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇員編號(hào)未找到');ENDfind_emp;/過(guò)程8-4過(guò)程參數(shù)的三種模式：IN用于接受調(diào)用程序的值默認(rèn)的參數(shù)模式OUT用于向調(diào)用程序返回值INOUT用于接受調(diào)用程序的值，并向調(diào)用程序返回更新的值過(guò)程8-5SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'項(xiàng)目的說(shuō)明為'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');執(zhí)行過(guò)程的語(yǔ)法：

EXECUTEprocedure_name(parameters_list);過(guò)程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;/過(guò)程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;/過(guò)程8-8將過(guò)程的執(zhí)行權(quán)限授予其他用戶：刪除過(guò)程：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;SQL>DROPPROCEDUREfind_emp;函數(shù)4-1函數(shù)是可以返回值的命名的PL/SQL子程序。

創(chuàng)建函數(shù)的語(yǔ)法：

CREATE[ORREPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;函數(shù)4-2定義函數(shù)的限制：函數(shù)只能接受IN參數(shù)，而不能接受INOUT或OUT參數(shù)形參不能是PL/SQL類型函數(shù)的返回類型也必須是數(shù)據(jù)庫(kù)類型Record訪問(wèn)函數(shù)的兩種方式：使用PL/SQL塊使用SQL語(yǔ)句函數(shù)4-3創(chuàng)建函數(shù)：從SQL語(yǔ)句調(diào)用函數(shù)：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQL>SELECTfun_helloFROMDUAL;函數(shù)4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice>=min_priceANDprice<=max_priceTHENRETURN'輸入的單價(jià)介于最低價(jià)與最高價(jià)之間';ELSERETURN'超出范圍';ENDIF;END;/DECLAREPNUMBER:=300;MSGVARCHAR2(200);BEGINMSG:=item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG);END;/過(guò)程和函數(shù)的比較過(guò)程函數(shù)作為PL/SQL語(yǔ)句執(zhí)行作為表達(dá)式的一部分調(diào)用在規(guī)格說(shuō)明中不包含RETURN子句必須在規(guī)格說(shuō)明中包含RETURN子句不返回任何值必須返回單個(gè)值可以包含RETURN語(yǔ)句，但是與函數(shù)不同，它不能用于返回值必須包含至