第15章 PLSQL程序設(shè)計(jì)

1第15章PL/SQL程序設(shè)計(jì)2本章內(nèi)容PL/SQL概述PL/SQL基礎(chǔ)控制結(jié)構(gòu)游標(biāo)異常處理存儲(chǔ)子程序包觸發(fā)器3本章要求掌握PL/SQL程序設(shè)計(jì)基礎(chǔ)知識(shí)掌握存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器的應(yīng)用415.1PL/SQL概述PL/SQL特點(diǎn)PL/SQL功能特性PL/SQL執(zhí)行過(guò)程與開(kāi)發(fā)工具515.1.1PL/SQL特點(diǎn)與SQL語(yǔ)言緊密集成。減小網(wǎng)絡(luò)流量，提高應(yīng)用程序的運(yùn)行性能。模塊化的程序設(shè)計(jì)功能，提高了系統(tǒng)可靠性。服務(wù)器端程序設(shè)計(jì)，可移植性好。615.1.2PL/SQL功能特性語(yǔ)句塊結(jié)構(gòu)異常處理變量和類(lèi)型條件語(yǔ)句循環(huán)結(jié)構(gòu)游標(biāo)過(guò)程、函數(shù)和觸發(fā)器包集合動(dòng)態(tài)SQL對(duì)象特性715.1.3PL/SQL執(zhí)行過(guò)程與開(kāi)發(fā)工具PL/SQL塊SQL語(yǔ)句客戶(hù)端應(yīng)用程序PL/SQL引擎數(shù)據(jù)庫(kù)服務(wù)器過(guò)程化語(yǔ)句執(zhí)行器SQL執(zhí)行器塊中SQL語(yǔ)句PL/SQL執(zhí)行過(guò)程

8PL/SQL開(kāi)發(fā)工具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper915.2PL/SQL基礎(chǔ)PL/SQL程序結(jié)構(gòu)

詞法單元

數(shù)據(jù)類(lèi)型變量與常量編譯指示PL/SQL中的SQL語(yǔ)句10PL/SQL程序結(jié)構(gòu)PL/SQL塊的組成PL/SQL塊分類(lèi)11PL/SQL塊的組成PL/SQL語(yǔ)言以塊為單位，塊中可以嵌套子塊。

一個(gè)基本的PL/SQL塊由3部分組成：聲明（DECLARE），可執(zhí)行部分（BEGIN），異常處理部分EXCEPTION）。

12聲明部分聲明部分以關(guān)鍵字DECLARE開(kāi)始，BEGIN結(jié)束。主要用于聲明變量、常量、數(shù)據(jù)類(lèi)型、游標(biāo)、異常處理名稱(chēng)以及本地（局部）子程序定義等。

可執(zhí)行部分執(zhí)行部分是PL/SQL塊的功能實(shí)現(xiàn)部分，以關(guān)鍵字BEGIN開(kāi)始，EXCEPTION或END結(jié)束（如果PL/SQL塊中沒(méi)有異常處理部分，則以END結(jié)束）。該部分通過(guò)變量賦值、流程控制、數(shù)據(jù)查詢(xún)、數(shù)據(jù)操縱、數(shù)據(jù)定義、事務(wù)控制、游標(biāo)處理等實(shí)現(xiàn)塊的功能。異常處理部分異常處理部分以關(guān)鍵字EXCEPTION開(kāi)始，END結(jié)束。該部分用于處理該塊執(zhí)行過(guò)程中產(chǎn)生的異常。

13注意：執(zhí)行部分是必需的，而聲明部分和異常部分是可選的；可以在一個(gè)塊的執(zhí)行部分或異常處理部分嵌套其他的PL/SQL塊；所有的PL/SQL塊都是以“END;”結(jié)束14PL/SQL塊分類(lèi)匿名塊命名塊函數(shù)存儲(chǔ)過(guò)程包觸發(fā)器1515.5.2詞法單元字符集標(biāo)識(shí)符分隔符常量值注釋16字符集大小寫(xiě)字母：A~Z，a~z數(shù)字：0~9空白：制表符、空格和回車(chē)數(shù)字符號(hào)：+-*/〈

〉=標(biāo)點(diǎn)符號(hào)：~!@#$%^&*（）_|{}[]？

；

：,.“

‘

17標(biāo)識(shí)符標(biāo)識(shí)符以字母開(kāi)頭，后邊可以跟字母、數(shù)字、貨幣符號(hào)、下劃線(xiàn)和”#”

標(biāo)識(shí)符的最大長(zhǎng)度為30字符，并且所有字符都是有效的。合法：Xv_studentIDTempVar非法：X+y_temp18+-*/=:=<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||=>**-分隔符19常量值字符型常量數(shù)字型常量布爾型常量：TURE、FALSE、NULL日期型常量2015.2.3數(shù)據(jù)類(lèi)型數(shù)字類(lèi)型字符類(lèi)型日期/區(qū)間類(lèi)型行標(biāo)識(shí)類(lèi)型布爾類(lèi)型原始類(lèi)型LOB類(lèi)型記錄類(lèi)型集合類(lèi)型21PL/SQL中常用的基本數(shù)據(jù)類(lèi)型分類(lèi)數(shù)據(jù)類(lèi)型數(shù)字類(lèi)型NUMBER、BINARY_NUMBERPLS_NUMBER字符類(lèi)型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/區(qū)間類(lèi)型DATE、TIMESTAMP、INTERVAL行標(biāo)識(shí)類(lèi)型ROWID、UROWID布爾類(lèi)型BOOLEAN（TRUE、FALSE、NULL）原始類(lèi)型RAW、LONGRAWLOB類(lèi)型CLOB、BLOB、NCLOB、BFILE記錄類(lèi)型RECORD集合類(lèi)型TABLE、VARRAY22記錄類(lèi)型的定義TYPErecord_typeISRECORD(field1datatype1[NOTNULL][DEFAULT|:=expr1],field2datatype2[NOTNULL][DEFAULT|:=expr2],……fieldndatatypen[NOTNULL][DEFAULT|:=exprn]);2315.2.4變量與常量變量與常量的定義變量的作用域24變量聲明變量與常量的定義變量定義的一般格式：<variablename>[CONSTANT]<datatype>[[NOTNULL]{DEFAULT|:=}<expression>];說(shuō)明每行只能定義一個(gè)標(biāo)識(shí)符。如果加上關(guān)鍵字CONSTANT,則表示所定義的標(biāo)識(shí)符為一個(gè)常量，必須為它賦初值。如果定義的標(biāo)識(shí)符不能為空，則必須加上關(guān)鍵字NOTNULL,并賦初值。為標(biāo)識(shí)符賦值時(shí)，使用賦值符號(hào)‘：=’，默認(rèn)值為空。25DECLAREv1NUMBER(4);v2NUMBER(4)NOTNULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULLTHENDBMS_OUTPUT.PUT_LINE('V1ISNULL!');ENDIF;DBMS_OUTPUT.PUT_LINE(v2||''||v3);END;declarebegindbms_output.put_line('helloworld');end;Setserverouton;declarev_jobvarchar2(10);v_salnumber;beginv_job:='clerk';selectmax(sal)intov_salfromempwherejob=v_job;dbms_output.put_line(v_sal);end;26declareinumber:=1;beginforiin1..5loopdbms_output.put_line(i||‘dlifangshi‘||i*i*i);endloop;end;2728聲明一個(gè)變量，使它的類(lèi)型與某個(gè)變量或數(shù)據(jù)庫(kù)基本表中某個(gè)列的數(shù)據(jù)類(lèi)型一致，可以使用%TYPE。示例v_empno1emp.empno%TYPE;v_empno2v_empno1%TYPE;29變量的作用域變量的作用域是指變量的有效作用范圍，從變量聲明開(kāi)始，直到塊結(jié)束。如果PL/SQL塊相互嵌套，則在內(nèi)部塊中聲明的變量是局部的，只能在內(nèi)部塊中引用，而在外部塊中聲明的變量是全局的，既可以在外部塊中引用，也可以在內(nèi)部塊中引用。如果內(nèi)部塊與外部塊中定義了同名變量，則在內(nèi)部塊中引用外部塊的全局變量時(shí)需要使用外部塊名進(jìn)行標(biāo)識(shí)。30<<OUTER>>DECLAREv_enameCHAR(15);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTER.V_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;3115.2.5編譯指示編譯指示是對(duì)編譯程序發(fā)出的特殊指令，也稱(chēng)偽指令。關(guān)鍵字：PRAGMAPL/SQL提供以下四種編譯指示：EXCEPTION_INIT告訴編譯程序?qū)⒁粋€(gè)特定的錯(cuò)誤號(hào)與程序中所聲明的異常標(biāo)識(shí)符關(guān)聯(lián)起來(lái)。RESTRICT_REFERENCES告訴編譯程序打包程序的純度，即對(duì)函數(shù)中可以使用的SQL語(yǔ)句和包變量進(jìn)行限制。32SERIALLY_REUSEABLE告訴PL/SQL運(yùn)行時(shí)引擎，在數(shù)據(jù)引用之間不要保持包級(jí)數(shù)據(jù)。AUTONOMOUS_TRANSACTION告訴編譯程序，該程序塊為自治事務(wù)，即該事務(wù)的提交和回滾是獨(dú)立進(jìn)行的。3315.2.6PL/SQL中SQL語(yǔ)句可以在PL/SQL中執(zhí)行的SQL語(yǔ)句包括SELECTDML(UPDATE、DELETE、INSERT)事務(wù)控制語(yǔ)句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語(yǔ)句不可以直接使用34SELECT…INTOSELECT…INTO語(yǔ)句只能查詢(xún)一個(gè)記錄的信息，如果沒(méi)有查詢(xún)到任何數(shù)據(jù)，則會(huì)產(chǎn)生NO_DATA_FOUND異常；如果查詢(xún)到多個(gè)記錄，則會(huì)產(chǎn)生TOO_MANY_ROW異常。INTO句子后的變量用于接收查詢(xún)的結(jié)果，變量的個(gè)數(shù)、順序應(yīng)該與查詢(xún)的目標(biāo)數(shù)據(jù)相匹配，也可以是記錄類(lèi)型的變量。DML語(yǔ)句35DECLAREv_empemp%ROWTYPE;v_enameemp.ename%type;v_salemp.sal%type;BEGINSELECT*INTOv_empFROMempWHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.sal);selectename,salINTOv_ename,v_salFROMempWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_ename||''||v_sal);END;36DML語(yǔ)句PL/SQL中DML語(yǔ)句對(duì)標(biāo)準(zhǔn)SQL語(yǔ)句中的DML語(yǔ)句進(jìn)行了擴(kuò)展，允許使用變量。示例DECLAREv_empnoemp.empno%TYPE:=7500;BEGININSERTINTOemp(empno,ename,sal,deptno)VALUES(v_empno,'JOAN',2300,20);UPDATEempSETsal=sal+100WHEREempno=v_empno;DELETEFROMempWHEREempno=v_empno;END;37WHERE標(biāo)識(shí)符的區(qū)分系統(tǒng)首先查看WHERE子句中的標(biāo)識(shí)符是否與表中的列名相同，如果相同，則該標(biāo)識(shí)符被解釋為列名；如果沒(méi)有同名列，系統(tǒng)檢查該標(biāo)識(shí)符是不是PL/SQL語(yǔ)句塊的變量。字符串比較填充比較：通過(guò)在短字符串后添加空格，使兩個(gè)字符串達(dá)到相同長(zhǎng)度，然后根據(jù)每個(gè)字符的ASCII碼進(jìn)行比較。非填充比較：根據(jù)每個(gè)字符的ASCII碼進(jìn)行比較，最先結(jié)束的字符串為小。PL/SQL中規(guī)定，對(duì)定長(zhǎng)的字符串（CHAR類(lèi)型的字符串和字符串常量）采用填充比較；如果比較的字符串中有一個(gè)是變長(zhǎng)字符串（VARCHAR2類(lèi)型的字符串），則采用非填充比較。38RETURNING如果要查詢(xún)當(dāng)前DML語(yǔ)句操作的記錄的信息，可以在DML語(yǔ)句末尾使用RETURNING語(yǔ)句返回該記錄的信息。RETURNING語(yǔ)句的基本語(yǔ)法：RETURNINGselect_list_itemINTOvariable_list|record_variable;39DECLAREv_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;4015.3控制結(jié)構(gòu)選擇結(jié)構(gòu)循環(huán)結(jié)構(gòu)跳轉(zhuǎn)結(jié)構(gòu)4115.3.1選擇結(jié)構(gòu)IF語(yǔ)句IFcondition1THENstatements1;[ELSIFcondition2THENstatements2;]……[ELSEelse_statements];ENDIF;

注意條件是一個(gè)布爾型變量或表達(dá)式，取值只能是TRUE，F(xiàn)ALSE，NULL。42例如，輸入一個(gè)員工號(hào)，修改該員工的工資，如果該員工為10號(hào)部門(mén)，工資增加100；若為20號(hào)部門(mén)，工資增加150；若為30號(hào)部門(mén)，工資增加200；否則增加300。43DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptnoINTOv_deptnoFROMempWHEREempno=v_empno;IFv_deptno=10THENv_increment:=100;ELSIFv_deptno=20THENv_increment:=150;ELSIFv_deptno=30THENv_increment:=200;ELSEv_increment:=300;ENDIF;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;44搜索式CASE語(yǔ)句基本語(yǔ)法CASEWHENcondition1THENstatements1;WHENcondition2THENstatements2;

……WHENconditionnTHENstatementsn;[ELSEelse_statements;]ENDCASE;45等值比較的CASE語(yǔ)句基本語(yǔ)法CASEtest_valueWHENvalue1THENstatements1;WHENvalue2THENstatements2;

……WHENvaluenTHENstatementsn;[ELSEelse_statements;]ENDCASE;

46DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTdeptnoINTOv_deptnoFROMempWHEREempno=v_empno;CASEv_deptnoWHEN10THENv_increment:=100;WHEN20THENv_increment:=150;WHEN30THENv_increment:=200;ELSEv_increment:=300;ENDCASE;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;47根據(jù)輸入的員工號(hào)，修改該員工工資。如果該員工工資低于1000，則工資增加200；如果工資在1000-2000之間，則增加150；如果工資在2000-3000之間，則增加100；否則增加50。48DECLAREv_salemp.sal%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=&x;SELECTsalINTOv_salFROMempWHEREempno=v_empno;CASEWHENv_sal<1000THENv_increment:=200;WHENv_sal<2000THENv_increment:=150;WHENv_sal<3000THENv_increment:=100;ELSEv_increment:=50;ENDCASE;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;4915.3.2循環(huán)結(jié)構(gòu)簡(jiǎn)單循環(huán)WHILE循環(huán)FOR循環(huán)50簡(jiǎn)單循環(huán)語(yǔ)法

LOOPsequence_of_statement;EXIT[WHENcondition];ENDLOOP;注意：在循環(huán)體中一定要包含EXIT語(yǔ)句，否則程序進(jìn)入死循環(huán)。51例如，執(zhí)行CREATETABLEtemp_table(num_colNUMBER,info_colCHAR(10))語(yǔ)句創(chuàng)建temp_table表，然后利用循環(huán)向temp_table表中插入50條記錄。程序?yàn)椋篋ECLAREv_counterBINARY_INTEGER:=1;BEGINLOOPINSERTINTOtemp_tableVALUES(v_Counter,'Loopindex');v_counter:=v_counter+1;EXITWHENv_counter>50;ENDLOOP;END;52WHILE循環(huán)基本語(yǔ)法

WHILEconditionLOOPsequence_of_statement;ENDLOOP;53例如，利用WHILE循環(huán)向temp_table表中插入50條記錄。程序?yàn)椋篋ECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter<=50LOOPINSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_counter+1;ENDLOOP;END;54FOR循環(huán)基本語(yǔ)法

FORloop_counterIN[REVERSE]low_bound..high_boundLOOPsequence_of_statement;ENDLOOP;注意：循環(huán)變量不需要顯式定義，系統(tǒng)隱含地將它聲明為BINARY_INTEGER變量；系統(tǒng)默認(rèn)時(shí)，循環(huán)變量從下界往上界遞增計(jì)數(shù)，如果使用REVERSE關(guān)鍵字，則表示循環(huán)變量從上界向下界遞減計(jì)數(shù)；循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。55例如，利用FOR循環(huán)向temp_table表中插入50條記錄。程序?yàn)椋築EGINFORv_counterIN1..50LOOPINSERTINTOtemp_tableVALUES(v_counter,'LoopIndex');ENDLOOP;END;5615.3.3跳轉(zhuǎn)結(jié)構(gòu)語(yǔ)法格式：《標(biāo)號(hào)》…GOTO標(biāo)號(hào)；說(shuō)明：塊內(nèi)可以跳轉(zhuǎn)，內(nèi)層塊可以跳到外層塊，但外層塊不能跳到內(nèi)層。IF語(yǔ)句不能跳入。不能從循環(huán)體外跳入循環(huán)體內(nèi)。不能從子程序外部跳到子程序中。由于goto語(yǔ)句的缺點(diǎn)，建議盡量少用甚至不用goto語(yǔ)句。5715.4游標(biāo)游標(biāo)的概念及類(lèi)型

顯式游標(biāo)

隱式游標(biāo)58DECLAREv_counterBINARY_INTEGER:=1;BEGIN<<LABEL>>INSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_Counter+1;IFv_counter<=50THENGOTOLABEL;ENDIF;END;5915.4游標(biāo)游標(biāo)的概念及類(lèi)型顯式游標(biāo)隱式游標(biāo)6015.4.1游標(biāo)的及類(lèi)型游標(biāo)的概念游標(biāo)（CURSOR）是Oracle系統(tǒng)在內(nèi)存中開(kāi)辟的一個(gè)工作區(qū)，在其中存放SELECT語(yǔ)句返回的查詢(xún)結(jié)果。使用游標(biāo)時(shí)，SELECT語(yǔ)句查詢(xún)的結(jié)果可以是單條記錄，多條記錄，也可以是零條記錄。游標(biāo)工作區(qū)中，存在著一個(gè)指針（POINTER）,在初始狀態(tài)它指向查詢(xún)結(jié)果的首記錄。61游標(biāo)的類(lèi)型顯式游標(biāo)由用戶(hù)定義、操作，用于處理返回多行數(shù)據(jù)的SELECT查詢(xún)。隱式游標(biāo)由系統(tǒng)自動(dòng)進(jìn)行操作，用于處理DML語(yǔ)句和返回單行數(shù)據(jù)的SELECT查詢(xún)。

6215.4.2顯式游標(biāo)顯式游標(biāo)的操作顯式游標(biāo)的屬性參數(shù)化顯式游標(biāo)顯式游標(biāo)的檢索利用游標(biāo)更新或刪除數(shù)據(jù)63顯式游標(biāo)的操作步驟定義游標(biāo)打開(kāi)游標(biāo)檢索游標(biāo)關(guān)閉游標(biāo)64定義游標(biāo)語(yǔ)法格式CURSORcursor_nameISselect_statement;說(shuō)明游標(biāo)必須在PL/SQL塊的聲明部分進(jìn)行定義；游標(biāo)定義時(shí)可以引用PL/SQL變量，但變量必須在游標(biāo)定義之前定義；定義游標(biāo)時(shí)并沒(méi)有生成數(shù)據(jù)，只是將定義信息保存到數(shù)據(jù)字典中；游標(biāo)定義后，可以使用cursor_name%ROWTYPE定義游標(biāo)類(lèi)型變量。65打開(kāi)游標(biāo)語(yǔ)法格式OPENcursor_name;說(shuō)明檢查變量的值執(zhí)行游標(biāo)定義時(shí)對(duì)應(yīng)的SELECT語(yǔ)句，將查詢(xún)結(jié)果檢索到工作區(qū)中。游標(biāo)指針指向第一個(gè)元組一旦游標(biāo)打開(kāi)，就無(wú)法再次打開(kāi)，除非先關(guān)閉如果游標(biāo)定義中的變量值發(fā)生變化，則只能在下次打開(kāi)游標(biāo)時(shí)才起作用。66檢索游標(biāo)語(yǔ)法格式FETCHcursor_nameINTOvariable_list|record_variable;

說(shuō)明在使用FETCH語(yǔ)句之前必須先打開(kāi)游標(biāo)對(duì)游標(biāo)第一次使用FETCH語(yǔ)句時(shí)，游標(biāo)指針指向第一條記錄，因此操作的對(duì)象是第一條記錄，使用后，游標(biāo)指針指向下一條記錄。游標(biāo)指針只能向下移動(dòng)，不能回退INTO子句中的變量個(gè)數(shù)、順序、數(shù)據(jù)類(lèi)型必須與工作區(qū)中每行記錄的字段數(shù)、順序以及數(shù)據(jù)類(lèi)型一一對(duì)應(yīng)。67關(guān)閉游標(biāo)語(yǔ)法格式

CLOSEcursor_name;說(shuō)明游標(biāo)所對(duì)應(yīng)的內(nèi)存工作區(qū)變?yōu)闊o(wú)效，釋放與游標(biāo)相關(guān)的系統(tǒng)資源。68根據(jù)輸入的部門(mén)號(hào)查詢(xún)某個(gè)部門(mén)的員工信息，部門(mén)號(hào)在程序運(yùn)行時(shí)指定。69DECLAREv_deptnoemp.deptno%TYPE;CURSORc_empISSELECT*FROMempWHEREdeptno=v_deptno;v_empc_emp%ROWTYPE;BEGINv_deptno:=&x;OPENc_emp;LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename||''||v_emp.sal||''||v_deptno);ENDLOOP;CLOSEc_emp;END;70顯式游標(biāo)的屬性

%ISOPEN布爾型。如果游標(biāo)已經(jīng)打開(kāi)，返回TRUE,否則為FALSE。%FOUND布爾型，如果最近一次使用FETCH語(yǔ)句，有返回結(jié)果則為T(mén)RUE,否則為FALSE;%NOTFOUND布爾型，如果最近一次使用FETCH語(yǔ)句,沒(méi)有返回結(jié)果則為T(mén)RUE,否則為FALSE;%ROWCOUNT數(shù)值型，返回到目前為止從游標(biāo)緩沖區(qū)檢索的元組數(shù)。

71參數(shù)化顯式游標(biāo)參數(shù)化游標(biāo)定義語(yǔ)法格式：CURSORcursor_name(parameter1datatype[,parameter2datatype…])ISselect_statement

打開(kāi)參數(shù)化游標(biāo)的方法OPENcursor_name(parameter1[,parameter2…])72注意：定義游標(biāo)時(shí)，只能指定參數(shù)的類(lèi)型，而不能指定參數(shù)的長(zhǎng)度、精度、刻度；打開(kāi)帶參數(shù)的游標(biāo)時(shí)，實(shí)參的個(gè)數(shù)和數(shù)據(jù)類(lèi)型等必須與游標(biāo)定義時(shí)形參個(gè)數(shù)和數(shù)據(jù)類(lèi)型等相匹配。73DECLARECURSORc_emp(p_deptnoemp.deptno%TYPE)ISSELECT*FROMempWHEREdeptno=p_deptno;v_empc_emp%ROWTYPE;BEGINOPENc_emp(10);LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;CLOSEc_emp;OPENc_emp(20);LOOPFETCHc_empINTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;CLOSEc_emp;END;74顯式游標(biāo)的檢索利用簡(jiǎn)單循環(huán)檢索游標(biāo)利用WHILE循環(huán)檢索游標(biāo)利用FOR循環(huán)檢索游標(biāo)75利用簡(jiǎn)單循環(huán)檢索游標(biāo)DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;LOOPFETCH…INTO…;EXITWHENcursor_name%NOTFOUND;

……ENDLOOP;CLOSEcursor_name;END;

76利用簡(jiǎn)單循環(huán)統(tǒng)計(jì)并輸出各個(gè)部門(mén)的平均工資。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;v_deptc_dept_stat%ROWTYPE;BEGINOPENc_dept_stat;LOOPFETCHc_dept_statINTOv_dept;EXITWHENc_dept_stat%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;CLOSEc_dept_stat;END;77利用WHILE循環(huán)檢索游標(biāo)DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;FETCH…INTO…;WHILEcursor_name%FOUNDLOOPFETCH…INTO…;

……ENDLOOP;CLOSEcursor;END;

78利用WHILE循環(huán)統(tǒng)計(jì)并輸出各個(gè)部門(mén)的平均工資。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;v_deptc_dept_stat%ROWTYPE;BEGINOPENc_dept_stat;FETCHc_dept_statINTOv_dept;WHILEc_dept_stat%FOUNDLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);FETCHc_dept_statINTOv_dept;ENDLOOP;CLOSEc_dept_stat;END;79利用FOR循環(huán)檢索游標(biāo)DECLARECURSORcursor_nameISSELECT…;BEGINFORloop_variableINcursor_nameLOOP

……ENDLOOP;END;80FOR循環(huán)說(shuō)明系統(tǒng)隱含地定義了一個(gè)數(shù)據(jù)類(lèi)型為%ROWTYPE的變量，并以此作為循環(huán)的計(jì)算器。系統(tǒng)自動(dòng)打開(kāi)游標(biāo)，不用顯式地使用OPEN語(yǔ)句打開(kāi)；系統(tǒng)重復(fù)地自動(dòng)從游標(biāo)工作區(qū)中提取數(shù)據(jù)并放入計(jì)數(shù)器變量中。系統(tǒng)自動(dòng)進(jìn)行%FOUND屬性檢查以確定是否有數(shù)據(jù)當(dāng)游標(biāo)工作區(qū)中所有的記錄都被提取完畢或循環(huán)中斷時(shí)，系統(tǒng)自動(dòng)地關(guān)閉游標(biāo)。81利用FOR循環(huán)統(tǒng)計(jì)并輸出各個(gè)部門(mén)的平均工資。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;BEGINFORv_deptINc_dept_statLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;END;82隱式FOR游標(biāo)BEGIN

FORv_empIN(select*fromempwheredeptno=10)LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);

ENDLOOP;END;83利用游標(biāo)更新或刪除數(shù)據(jù)游標(biāo)定義語(yǔ)法

CURSORcursor_nameISSELECTselect_list_itemFROMtableFORUPDATE

更新或修改數(shù)據(jù)的語(yǔ)法為：

UPDATE|DELETE…WHERECURRENTOFcursor_name

注意由于COMMIT語(yǔ)句會(huì)釋放會(huì)話(huà)擁有的任何鎖，因此如果在檢索游標(biāo)的循環(huán)內(nèi)使用COMMIT語(yǔ)句會(huì)釋放定義游標(biāo)時(shí)對(duì)數(shù)據(jù)加的鎖，從而導(dǎo)致利用游標(biāo)修改或刪除數(shù)據(jù)的操作失敗。84修改員工的工資，如果員工的部門(mén)號(hào)為10，工資提高100；部門(mén)號(hào)為20，工資提高150；部門(mén)號(hào)為30，工資提高200；否則工資提高250。85DECLARECURSORc_empISSELECT*FROMempFORUPDATE;v_incrementNUMBER;BEGINFORv_empINc_empLOOPCASEv_emp.deptnoWHEN10THENv_increment:=100;WHEN20THENv_increment:=150;WHEN30THENv_increment:=200;ELSEv_increment:=250;ENDCASE;UPDATEempSETsal=sal+v_incrementWHERECURRENTOFc_emp;ENDLOOP;END;8615.4.3隱式游標(biāo)用于處理INSERT、UPDATE、DELETE和SELECT…INTO語(yǔ)句沒(méi)有OPEN、FETCH、CLOSE命令屬性

SQL%ISOPEN SQL%FOUND SQL%NOTFOUNDSQL%ROWCOUNT87修改員工號(hào)為1000的員工工資，將其工資增加100。如果該員工不存在，則向emp表中插入一個(gè)員工號(hào)為1000，工資為1500的員工。BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1500);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1500);ENDIF;END;8815.5異常處理異常概述異常處理過(guò)程異常的傳播8915.5.1異常概述Oracle錯(cuò)誤處理機(jī)制異常的類(lèi)型90Oracle錯(cuò)誤處理機(jī)制概念一個(gè)錯(cuò)誤對(duì)應(yīng)一個(gè)異常，當(dāng)錯(cuò)誤產(chǎn)生時(shí)拋出相應(yīng)的異常，并被異常處理器捕獲，程序控制權(quán)傳遞給異常處理器，由異常處理器來(lái)處理運(yùn)行時(shí)錯(cuò)誤。91異常的類(lèi)型預(yù)定義的Oracle異常非預(yù)定義的Oracle異常用戶(hù)定義的異常92預(yù)定義的異常異常情況名錯(cuò)誤代碼描述CURSOR_ALREADY_OPENORA-06511嘗試打開(kāi)已經(jīng)打開(kāi)的游標(biāo)

INVALID_CURSORORA-01001不合法的游標(biāo)操作（如要打開(kāi)已經(jīng)關(guān)閉的游標(biāo)）

NO_DATA_FOUNDORA-01403沒(méi)有發(fā)現(xiàn)數(shù)據(jù)

TOO_MANY_ROWSORA-01422一個(gè)SELECTINTO語(yǔ)句匹配多個(gè)數(shù)據(jù)行INVALID_NUMBERORA-01722轉(zhuǎn)換成數(shù)字失敗

（‘X’）

VALUE_ERRORORA-06502截?cái)?、算法或轉(zhuǎn)換錯(cuò)誤，通常出現(xiàn)在賦值錯(cuò)誤

ZERO_DIVIDEORA-01476除數(shù)為0

ROWTYPE_MISMATCHORA-06504主機(jī)游標(biāo)變量與PL/SQL游標(biāo)變量類(lèi)型不匹配93異常情況名錯(cuò)誤代碼描述DUP_VAL_ON_INDEXORA-00001違反唯一性約束或主鍵約束SYS_INVALID_ROWIDORA-01410轉(zhuǎn)換成ROWID失敗TIMEOUT_ON_RESOURCEORA-00051在等待資源中出現(xiàn)超時(shí)LOGIN_DENIEDORA-01017無(wú)效用戶(hù)名/密碼CASE_NOT_FOUNDORA-06592沒(méi)有匹配的WHEN子句N(xiāo)OT_LOGGED_ONORA-01012沒(méi)有與數(shù)據(jù)庫(kù)建立連接STORAGE_ERRORORA-06500PL/SQL內(nèi)部錯(cuò)誤PROGRAM_ERRORORA-06501PL/SQL內(nèi)部錯(cuò)誤94異常情況名錯(cuò)誤代碼描述ACCESS_INTO_NULLORA-06530給空對(duì)象屬性賦值COLLECTION_IS_NULLORA-06531對(duì)某NULLPL/SQL表或可變數(shù)組試圖應(yīng)用集合方法，而不是EXISTS

SELF_IS_NULLORA-30625調(diào)用空對(duì)象實(shí)例的方法SUBSCRIPT_BEYOND_COUNTORA-06533對(duì)嵌套表或數(shù)組索引引用時(shí)超出集合中元素的數(shù)量SUBSCRIPT_OUTSIDE_LIMITORA-06532對(duì)嵌套表或可變數(shù)組索引的引用超出聲明的范圍95非預(yù)定義異常在語(yǔ)句塊的聲明部分聲明一個(gè)異常名稱(chēng)e_integrityEXCEPTION;通過(guò)PRAGMAEXCEPTION-INIT將異常與一個(gè)Oracle錯(cuò)誤號(hào)相關(guān)聯(lián):PRAGMAEXCEPTION-INIT(e_integrity.-2291)在異常處理部分捕捉并處理異常：WHENe_integrityTHEN...

96用戶(hù)自定義的異常用戶(hù)自定義異常必須在聲明部分進(jìn)行聲明。當(dāng)異常發(fā)生時(shí)，系統(tǒng)不能自動(dòng)觸發(fā)，需要用戶(hù)使用RAISE語(yǔ)句。在異常處理部分捕捉并處理異常。9715.5.2異常處理過(guò)程在聲明部分為錯(cuò)誤定義異常，包括非預(yù)定義異常和用戶(hù)定義異常。e_exceptionEXCEPTION;PRAGMAEXCEPTION_INIT(e_exceptioin,-#####);在執(zhí)行過(guò)程中當(dāng)錯(cuò)誤產(chǎn)生時(shí)拋出與錯(cuò)誤對(duì)應(yīng)的異常。RAISEuser_define_exception；在異常處理部分通過(guò)異常處理器捕獲異常，并進(jìn)行異常處理。98異常的捕獲與處理異常處理器的基本形式為EXCEPTIONWHENexception1[ORexcetpion2…]THENsequence_of_statements1;WHENexceptioin3[ORexception4…]THENsequence_of_statements2;

……WHENOTHERSTHENsequence_of_statementsn;END;99注意：一個(gè)異常處理器可以捕獲多個(gè)異常，只需要在WHEN子句中用OR連接即可；一個(gè)異常只能被一個(gè)異常處理器捕獲，并進(jìn)行處理。100查詢(xún)名為SMITH的員工工資，如果該員工不存在，則輸出“Thereisnotsuchanemployee!”；如果存在多個(gè)同名的員工，則輸出其員工號(hào)和工資。DECLAREv_salemp.sal%type;BEGINSELECTsalINTOv_salFROMempWHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemplyee!');WHENTOO_MANY_ROWSTHENFORv_empIN(SELECT*FROMempWHEREename='SMITH')LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.sal);ENDLOOP;END;101刪除dept表中部門(mén)號(hào)為10的部門(mén)信息，如果不能刪除則輸出“Therearesubrecordsinemptable!”。DECLAREe_deptno_fkEXCEPTION;PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdeptWHEREdeptno=10;EXCEPTIONWHENe_deptno_fkTHENDBMS_OUTPUT.PUT_LINE('Therearesubrecordsinemptable!');END;102修改7844員工的工資，保證修改后工資不超過(guò)6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;END;103OTHERS異常處理器OTHERS異常處理器是一個(gè)特殊的異常處理器，可以捕獲所有的異常。通常，OTHERS異常處理器總是作為異常處理部分的最后一個(gè)異常處理器，負(fù)責(zé)處理那些沒(méi)有被其他異常處理器捕獲的異常。104DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thereissomewronginselecting!');END;105可以通過(guò)兩個(gè)函數(shù)來(lái)獲取錯(cuò)誤相關(guān)信息。SQLCODE：返回當(dāng)前錯(cuò)誤代碼。如果是用戶(hù)定義錯(cuò)誤返回值為1；如果是ORA-1403：NODATAFOUND錯(cuò)誤，返回值為100；其他Oracle內(nèi)部錯(cuò)誤返回相應(yīng)的錯(cuò)誤號(hào)。SQLERRM：返回當(dāng)前錯(cuò)誤的消息文本。如果是Oracle內(nèi)部錯(cuò)誤，返回系統(tǒng)內(nèi)部的錯(cuò)誤描述；如果是用戶(hù)定義錯(cuò)誤，則返回信息文本為“User-definedException”。106DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;v_codeNUMBER(6);v_textVARCHAR2(200);BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENv_code:=SQLCODE;v_text:=SQLERRM;DBMS_OUTPUT.PUT_LINE(v_code||''||v_text);END;10715.5.3異常的傳播可執(zhí)行部分異常的傳播如果當(dāng)前語(yǔ)句塊有該異常的處理器，則執(zhí)行之，并且成功完成該語(yǔ)句塊。然后，控制權(quán)傳遞到外層語(yǔ)句塊。

如果當(dāng)前語(yǔ)句塊沒(méi)有該異常的處理器，則通過(guò)在外層語(yǔ)句塊中產(chǎn)生該異常來(lái)傳播該異常。然后，執(zhí)行對(duì)外層語(yǔ)句塊執(zhí)行步驟1。如果沒(méi)有外層語(yǔ)句塊，則該異常將傳播到調(diào)用環(huán)境。

108DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');END;/Thereisnotsuchanemployee!Nowthisisoutputtedbyouterblock!109DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREdeptno=10;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanoneemployee!');END;/Therearemorethanoneemployee!110聲明部分異常的傳播聲明部分的異常立刻傳播到外層語(yǔ)句塊，即使當(dāng)前語(yǔ)句塊有異常處理器。

異常處理部分的異常的傳播異常處理器中產(chǎn)生的異常，可以有RAISE語(yǔ)句顯式產(chǎn)生，也可以通過(guò)運(yùn)行時(shí)錯(cuò)誤而隱含產(chǎn)生。異常立即被傳播到外層語(yǔ)句塊。

111BEGINDECLAREv_numberNUMBER(6):='ABC';BEGINv_number:=10;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyinnerblock!');END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyouterblock!');END;/Thisisoutputtedbyouterblock!11215.6存儲(chǔ)子程序存儲(chǔ)過(guò)程函數(shù)局部子程序113存儲(chǔ)子程序是指被命名的PL/SQL塊，以編譯的形式存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，可以在應(yīng)用程序中進(jìn)行調(diào)用，是PL/SQL程序模塊化的一種體現(xiàn)。存儲(chǔ)子程序是以獨(dú)立對(duì)象的形式存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，因此是一種全局結(jié)構(gòu)，與之對(duì)應(yīng)的是局部子程序，即嵌套在PL/SQL塊中的局部過(guò)程和函數(shù)，其存儲(chǔ)位置取決于其所在的父塊的位置。114存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的創(chuàng)建存儲(chǔ)過(guò)程的調(diào)用存儲(chǔ)過(guò)程的管理115存儲(chǔ)過(guò)程的創(chuàng)建CREATE[ORREPLACE]PROCEDUREprocedure_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])AS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[procedure_name];

PROCEDUREBODY116參數(shù)模式IN

當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被傳遞給過(guò)程。在過(guò)程內(nèi)，該參數(shù)起常數(shù)作用，可讀不可寫(xiě)。調(diào)用結(jié)束，實(shí)參值不變。（默認(rèn)參數(shù)類(lèi)型）OUT

當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被忽略。在過(guò)程內(nèi)，該參數(shù)起未初始化的變量作用，值為NULL。過(guò)程內(nèi)，該參數(shù)可讀可寫(xiě)。調(diào)用結(jié)束，形參賦給實(shí)參。INOUT

當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被傳遞給過(guò)程。在過(guò)程內(nèi)，該參數(shù)起已初始化變量作用，過(guò)程內(nèi)，該參數(shù)可讀可寫(xiě)。調(diào)用結(jié)束，形參賦給實(shí)參。117參數(shù)限制聲明形參時(shí)不能定義形參的長(zhǎng)度或精度、刻度參數(shù)傳遞IN參數(shù)為引用傳遞，即實(shí)參的指針被傳遞給形參；OUT、INOUT參數(shù)為值傳遞，即實(shí)參的值被復(fù)制給形參。118創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以部門(mén)號(hào)為參數(shù)，查詢(xún)?cè)摬块T(mén)的平均工資，并輸出該部門(mén)中比平均工資高的員工號(hào)、員工名。CREATEORREPLACEPROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno||''||'averagesalaryis:'||v_sal);FORv_empIN(SELECT*FROMempWHEREdeptno=p_deptnoANDsal>v_sal)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdoesn’’texists!');ENDshow_emp;119通常，存儲(chǔ)過(guò)程不需要返回值，如果需要返回一個(gè)值可以通過(guò)函數(shù)調(diào)用實(shí)現(xiàn)。但是，如果希望返回多個(gè)值，可以使用OUT或INOUT模式參數(shù)來(lái)實(shí)現(xiàn)。120創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以部門(mén)號(hào)為參數(shù)，返回該部門(mén)的人數(shù)和最高工資。CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROMempWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedepartmentdon’’texists!');ENDreturn_deptinfo;121存儲(chǔ)過(guò)程的調(diào)用在SQL*PLUS中調(diào)用EXECprocedure_name(parameter_list)EXECUTEshow_emp(10)在PL/SQL塊中調(diào)用BEGINprocedure_name(parameter_list);END；122DECLAREv_avgsalemp.sal%TYPE;v_countNUMBER;BEGINshow_emp(20);return_deptinfo(10,v_avgsal,v_count);DBMS_OUTPUT.PUT_LINE(v_avgsal||''||v_count);END;123存儲(chǔ)過(guò)程的管理修改存儲(chǔ)過(guò)程CREATEORREPLACEPROCEDURE重新編譯存儲(chǔ)過(guò)程ALTERPROCEDUREprocedure_nameCOMPILE；刪除存儲(chǔ)過(guò)程DROPPROCEDUREprocedure_name名；查看過(guò)程源代碼selecttextfromuser_sourcewherename=procedure_name;12415.6.2函數(shù)函數(shù)概述函數(shù)的創(chuàng)建函數(shù)的調(diào)用函數(shù)的管理125函數(shù)概述函數(shù)用于返回特定數(shù)據(jù)，可以返回一個(gè)或多個(gè)值。在一個(gè)函數(shù)中必須包含一個(gè)或多個(gè)RETURN語(yǔ)句函數(shù)調(diào)用是PL/SQL表達(dá)式的一部分，而過(guò)程調(diào)用可以是一個(gè)獨(dú)立的PL/SQL語(yǔ)句126函數(shù)的創(chuàng)建CREATE[ORREPLACE]FUNCTIONfunction_name(parameter1_name[mode]datatype[DEFAULT|:=value][,parameter2_name[mode]datatype[DEFAULT|:=value],…])RETURNreturn_datatypeAS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/END[function_name];FUNCTIONBODY127創(chuàng)建一個(gè)以部門(mén)號(hào)為參數(shù)，返回該部門(mén)最高工資的函數(shù)。CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROMempWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thedeptnoisinvalid!');ENDreturn_maxsal;128函數(shù)的調(diào)用在SQL語(yǔ)句中調(diào)用函數(shù)在PL/SQL中調(diào)用函數(shù)129DECLAREv_salemp.sal%TYPE;BEGINFORv_deptIN(SELECTDISTINCTdeptnoFROMemp)LOOPv_sal:=return_maxsal(v_dept.deptno);DBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_sal);ENDLOOP;END;

130函數(shù)的管理修改函數(shù)CREATEORREPLACEFUNCTIONfunction_name重新編譯存儲(chǔ)過(guò)程ALTERFUNCTIONfunction_nameCOMPILE；刪除存儲(chǔ)過(guò)程DROPFUNCTIONfunction_name；查看過(guò)程源代碼selecttextfromuser_sourcewherename=function_name;13115.6.3局部子程序局部子程序嵌套在其他PL/SQL塊中的子程序。只能在其定義的塊內(nèi)部被調(diào)用，而不能在其父塊外被調(diào)用。使用局部子程序時(shí)需要注意：局部子程序只在當(dāng)前語(yǔ)句塊內(nèi)有效；局部子程序必須在PL/SQL塊聲明部分的最后進(jìn)行定義；局部子程序必須在使用之前聲明，如果是子程序間相互引用，則需要采用預(yù)先聲明；局部子程序可以重載。132在一個(gè)塊內(nèi)部定義一個(gè)函數(shù)和一個(gè)過(guò)程。函數(shù)以部門(mén)號(hào)為參數(shù)返回該部門(mén)的平均工資；過(guò)程以部門(mén)號(hào)為參數(shù)，輸出該部門(mén)中工資低于部門(mén)平均工資的員工的員工號(hào)、員工名。133DECLAREv_deptnoemp.deptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal;PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROMempWHEREsal<return_avgsal(p_deptno);BEGINFORv_empINc_empLOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);ENDLOOP;ENDshow_emp;BEGINv_deptno:=&x;v_avgsal:=return_avgsal(v_deptno);show_emp(v_deptno);END;134存儲(chǔ)子程序與局部子程序區(qū)別在于：存儲(chǔ)子程序己經(jīng)編譯好放在數(shù)據(jù)庫(kù)服務(wù)器端，可以直接調(diào)用，而局部子程序存在于定義它的語(yǔ)句塊中，在運(yùn)行時(shí)先進(jìn)行編譯；存儲(chǔ)子程序不能重載，而局部子程序可以進(jìn)行重載；存儲(chǔ)子程序可以被任意的PL/SQL塊調(diào)用，而局部子程序只能在定義它的塊中被調(diào)用。135在一個(gè)PL/SQL塊中重載兩個(gè)過(guò)程，一個(gè)以員工號(hào)為參數(shù)，輸出該員工信息；另一個(gè)以員工名為參數(shù)，輸出員工信息。利用這兩個(gè)過(guò)程分別查詢(xún)員工號(hào)為7902，7934，以及員工名為SMITH，F(xiàn)ORD的員工信息。136DECLAREPROCEDUREshow_empinfo(p_empnoemp.empno%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename||''||v_emp.deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');ENDshow_empinfo;

PROCEDUREshow_empinfo(p_enameemp.ename%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREename=p_ename;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.deptno);137EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanonemployee!');ENDshow_empinfo;BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo('SMITH');show_empinfo('FORD');END;138存儲(chǔ)子程序與局部子程序區(qū)別存儲(chǔ)子程序己經(jīng)編譯好放在數(shù)據(jù)庫(kù)服務(wù)器端，可以直接調(diào)用，而局部子程序存在于定義它的語(yǔ)句塊中，在運(yùn)行時(shí)先進(jìn)行編譯；存儲(chǔ)子程序不能重載，而局部子程序可以進(jìn)行重載；存儲(chǔ)子程序可以被任意的PL/SQL塊調(diào)用，而局部子程序只能在定義它的塊中被調(diào)用13915.7包包概述包的創(chuàng)建包的調(diào)用包的重載包的初始化包的管理140包概述包是包含一個(gè)或多個(gè)子程序單元（過(guò)程、函數(shù)等）的容器包是全局的包類(lèi)型數(shù)據(jù)庫(kù)內(nèi)置包用戶(hù)創(chuàng)建的包包由包規(guī)范和包體兩部分組成，在數(shù)據(jù)庫(kù)中獨(dú)立存儲(chǔ)141包規(guī)范聲明了軟件包中所有內(nèi)容，如過(guò)程、函數(shù)、游標(biāo)、類(lèi)型、異常和變量等，其中過(guò)程和函數(shù)只包括原型信息，不包含任何子程序代碼。包體中包含了在包頭中的過(guò)程和函數(shù)的實(shí)現(xiàn)代碼。包體中還可以包括在規(guī)范中沒(méi)有聲明的變量、游標(biāo)、類(lèi)型、異常、過(guò)程和函數(shù)，但是它們是私有元素，只能由同一包體中其他過(guò)程和函數(shù)使用。142創(chuàng)建包規(guī)范語(yǔ)法CREATEORREPLACEPACKAGEpackage_name

IS|AS[PRAGMASERIALLY_RESUABLE]type_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_declaration|function_declarationEND[package_name];143注意：元素聲明的順序可以是任意的，但必須先聲明后使用；所有元素是可選的；過(guò)程和函數(shù)的聲明只包括原型，不包括具體實(shí)現(xiàn)。144創(chuàng)建一個(gè)軟件包，包括2個(gè)變量、2個(gè)過(guò)程和1個(gè)異常。CR