全面解析OracleProcedure基本語法

1、全面解析oraciep基本語法ure關(guān)鍵字or存儲過程1基.本結(jié)構(gòu)op存儲過程名字參數(shù)1INNUMBER,參數(shù)2INNUMBER變量1INTEGER:=0;變量2DATE;存儲過程名字SELECTINTO將ele查詢的結(jié)果存入到變量中，可以同時(shí)將多個(gè)列存儲多個(gè)變量中，必須有一條記錄，否則拋出異常如果沒有記錄拋出OO例子：col變量I變量oOerucerePTIOO判斷dosomething循環(huán)eHILEV_TEST=1LOOPENDLOOP;變.量賦值用or使用curorOcurORcur_resulcurLOOPcUf名recUrre歹列名OOP帶參數(shù)的curorOOP變量值LOOPFETC

2、HC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dooeENDLOOP;O用IIdeeloerdeu連接數(shù)據(jù)庫后建立一個(gè)eO在窗口輸入調(diào)用R的代碼開始deu單步調(diào)試轉(zhuǎn)載：oracle存儲過程關(guān)鍵字ora存儲過程存儲過程創(chuàng)建語法：TOC o 1-5 h zcreaeorrelacen存儲過程名（ara,araeoute變量類型（值范圍）變量類型（值范圍）eeleccou變量表o列名aram判斷條件eele列名變量o表列名ara;ouPuul打印信息l判斷條件eouPuul打印信息lea異常名（O）dxceptioneoereRollback;nd;注意事項(xiàng)：

3、1存儲過程參數(shù)不帶取值范圍，表示傳入，OU表示輸出變量帶取值范圍，后面接分號在判斷語句前最好先用count(*)函數(shù)判斷是否存在該條操作記錄用o。給變量賦值在代碼中拋異常用raise+異常名以命名的異常命名的系統(tǒng)異常產(chǎn)生原因ACCESS_INTO_NULL未定義對象CASE_NOT_FOUNDCASE中若未包含相應(yīng)的WHEN,并且沒有設(shè)置ELSE時(shí)COLLECTION_IS_NULL集合元素未初始化CURSER_ALREADY_OPEN游標(biāo)已經(jīng)打開DUP_VAL_ON_INDEX唯一索引對應(yīng)的列上有重復(fù)的值INVALID_CURSOR在不合法的游標(biāo)上進(jìn)行操作INVALID_

4、NUMBER內(nèi)嵌的SQL語句不能將字符轉(zhuǎn)換為數(shù)字NO_DATA_FOUND使用selectinto未返回行,或應(yīng)用索引表未初始化的TOO_MANY_ROWS執(zhí)行selectinto時(shí)，結(jié)果集超過一行ZERO_DIVIDE除數(shù)為0SUBSCRIPT_BEYOND_COUNT元素下標(biāo)超過嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT使用嵌套表或VARRAY時(shí)，將下標(biāo)指定為負(fù)數(shù)VALUE_ERROR賦值時(shí)，變量長度不足以容納實(shí)際數(shù)據(jù)LOGIN_DENIEDPL/SQL應(yīng)用程序連接到oracle數(shù)據(jù)庫時(shí),提供了不正確的用戶名或密碼NOT_LOGGED_ONPL/SQL應(yīng)用程

5、序在沒有連接oralce數(shù)據(jù)庫的情況下訪問數(shù)據(jù)PROGRAM_ERRORPL/SQL內(nèi)部問題,可能需要重裝數(shù)據(jù)字典pl./SQL系統(tǒng)包ROWTYPE_MISMATCH宿主游標(biāo)變量與PL/SQL游標(biāo)變量的返回類型不兼容SELF_IS_NULL使用對象類型時(shí)，在null對象上調(diào)用對象方法STORAGE_ERROR運(yùn)行PL/SQL時(shí),超出內(nèi)存空間SYS_INVALID_ID無效的ROWID字符串TIMEOUT_ON_RESOURCEOracle在等待資源時(shí)超時(shí)語法及示例：1、存儲過程創(chuàng)建存儲過程的語法：CREATEORREPLACEPROCEDUREprocedure_name(parameter_

6、list)IS|ASlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersENDprocedure_name;其中：procedure_name是過程的名稱。parameter_list是參數(shù)列表。local_declarations是局部聲明。executable_statements是可執(zhí)行語句。exception_handlers是異常處理程序。示例1：演示創(chuàng)建過程(參數(shù)列表中為IN參數(shù)賦予一個(gè)默認(rèn)值，不能為OUT、INOUT參數(shù)賦予默認(rèn)值)createorreplaceprocedurefind_e

7、mp(emp_noinnumber:=7900)asempnamevarchar2(20);beginselectenameintoempnamefromempwhereempno=emp_no;dbms_output.put_line雇員姓名是|empname);exceptionwhenno_data_foundthendbms_output.put_li調(diào)用過程：EXECUTEprocudure_name(parameters_list);也可以在過程里面調(diào)用，直接寫上procudure_name而不必寫EXECUTE。示例2:演示創(chuàng)建帶OUT參數(shù)的過程createorreplacepr

8、oceduretest(value1varchar2,value2outnumber)isidentitynumber;beginselectsalintoidentityfromempwhereempno=value1;ifidentity2000thenvalue2:=1000;elsevalue2:=500;endif;end;調(diào)用帶OUT參數(shù)的過程：declarevalue2number;begintest(7900,value2);dbms_output.put_line(value2);end;示例3：演示創(chuàng)建帶INOUT參數(shù)的過程createorreplaceprocedures

9、wap(p1inoutnumber,p2inoutnumber)isv_tempnumber;beginv_temp:=p1;p1:=p2;p2:=v_temp;end;調(diào)用帶INOUT參數(shù)的過程：declarenum1number:=100;num2number:=200;beginswap(num1,num2);dbms_output.put_line(num1=|num1);dbms_output.put_line(num2=|num2);end;示例4：將過程的執(zhí)行權(quán)限授予其他用戶GRANTEXECUTEONfind_empTOscott;GRANTEXECUTEONswapTOPUB

10、LIC;將find_emp過程的執(zhí)行權(quán)限授予給用戶scott,將執(zhí)行swap過程的權(quán)限授予所有數(shù)據(jù)庫用戶。刪除過程語法：DROPPROCEDUREprocudure_name;2、函數(shù)定義函數(shù)的語法如下：CREATEORREPLACEFUNCTIONfunction_name(parameter_list)RETURNdatatypeIS|ASlocal_declarationsBEGINexecutable_statementsEXCEPTIONexception_handlersENDfunction_name;其中：function_name是函數(shù)的名稱。parameter_list是參

11、數(shù)列表。local_declarations是局部聲明。executable_statements是可執(zhí)行語句。exception_handlers是異常處理程序。使用函數(shù)時(shí)注意：形式參數(shù)必須只使用數(shù)據(jù)庫類型，不得使用3L/SQL類型。函數(shù)的返回類型也必須是數(shù)據(jù)庫類型。函數(shù)不能單獨(dú)執(zhí)行，只能通過SQL語句或PL/SQL程序塊來調(diào)用。示例5：演示如何創(chuàng)建函數(shù)createorreplacefunctionfun_helloreturnvarchar2isbeginreturn朋友,您好;end;調(diào)用函數(shù)：selectfun_hellofromdual;函數(shù)的授權(quán)：同過和的授權(quán)一樣具體請看示例4。刪

12、除函數(shù)：DROPFUNCTIONfunction_name過程和函數(shù)的差異過程函數(shù)作為PL/SQL語句執(zhí)行作為表達(dá)式的一部分調(diào)用在規(guī)范中不包含RETURN子句必須在規(guī)范中包含RETURN子句不返回任何值必須返回單個(gè)值可以包含RETURN語句，但是與函數(shù)不同，它不能用于返回值必須包含至少一條RETURN語句3、程序包創(chuàng)建包規(guī)范的語法：CREATEORREPLACEPACKAGEpackage_nameIS|ASPublictypeanditemdeclarationsSubprogramspecificationsENDpackage_name;其中：package_name是包的名稱。Publ

13、ictypeanditemdeclarations是聲明類型、常量、變量、異常和游標(biāo)等。Subprogramspecifications聲明PL/SQL子程序。示例6：演示創(chuàng)建程序包規(guī)范createorreplacepackagepack_opisprocedurepro_print_ename(idnumber);procedurepro_print_sal(idnumber);functionfun_re_date(idnumber)returndate;end;創(chuàng)建包主體的語法：CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASPublictypean

14、ditemdeclarationsSubprogrambodiesBEGINInitialization_statementsENDpackage_name;其中：package_name是包的名稱。Publictypeanditemdeclarations是聲明類型、常量、變量、異常和游標(biāo)等。Subprogrambodies是定義公共和私有PL/SQL子程序。示例7：演示創(chuàng)建程序包主體createorreplacepackagebodypack_opisprocedurepro_print_ename(idnumber)isnameemp.ename%type;beginselectenam

15、eintonamefromempwhereempno=id;dbms_output.put_line職員姓名：|name);endpro_print_ename;procedurepro_print_sal(示例8：調(diào)用程序包中創(chuàng)建的過程和函數(shù)execpack__print_ename(7900);execpack__print_sal(7900);selectpack_op.fun_re_date(7900)fromdual;示例9：演示程序包中的游標(biāo)創(chuàng)建包規(guī)范createorreplacepackagepack_empiscursorcur_empreturnemp

16、%rowtype;procedurepro_cur;endpack_emp;創(chuàng)建包主體createorreplacepackagebodypack_empiscursorcur_empreturnemp%rowtypeisselect*fromemp;procedurepro_curisrec_empemp%rowtype;beginopencur_emp;loopfetchcur_empintorec_emp;exitwhencur_emp%notfound;ifrec_emp.sal1000t調(diào)用程序包中的過程以調(diào)用程序包中的游標(biāo)execpack__cur;示例10:存儲過程返回游標(biāo)的子程序包(此程序包返回r_cur游標(biāo))CREATEORREPLACEpackageSCOTT.pk_wtistypemytypeisrefcursor;procedurep_wt(mycsoutmytype);end;CREATEORREPLACEpackageBODYSCOTT.pk_wtisprocedurep_wt(mycsoutmytype)isr_curmytype;beginopenr_curforselect*fromemp;mycs:=r_cur;