Oracle第章LSQL語(yǔ)言基礎(chǔ)

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

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

詞法單元數(shù)據(jù)類型變量與常量PL/SQL記錄編譯指示PL/SQL中的SQL語(yǔ)句1014.2.1PL/SQL程序結(jié)構(gòu)PL/SQL塊的組成PL/SQL塊分類11（1）PL/SQL塊的組成成PL/SQL程序的基基本單元元是語(yǔ)句句塊，所所有的PL/SQL程序都是是由語(yǔ)句句塊構(gòu)成成的。。一個(gè)完整整的PL/SQL語(yǔ)句塊由由3個(gè)部分組組成。12聲明部分分主要用于于聲明變變量、常常量、數(shù)數(shù)據(jù)類型型、游標(biāo)標(biāo)、異常常處理名名稱以及及本地（（局部））子程序序定義等等。可執(zhí)行部分執(zhí)行部分是PL/SQL塊的功能實(shí)現(xiàn)現(xiàn)部分。該部部分通過(guò)變量量賦值、流程程控制、數(shù)據(jù)據(jù)查詢、數(shù)據(jù)據(jù)操縱、數(shù)據(jù)據(jù)定義、事務(wù)務(wù)控制、游標(biāo)標(biāo)處理等實(shí)現(xiàn)現(xiàn)塊的功能。。異常處理部分分異常處理部分分用于處理該該塊執(zhí)行過(guò)程程中產(chǎn)生的異異常。13注意：執(zhí)行部分是必必須的，而聲聲明部分和異異常部分是可可選的可以在一個(gè)塊塊的執(zhí)行部分分或異常處理理部分嵌套其其他的PL/SQL塊；所有的PL/SQL塊都是以“END;”結(jié)束。14DECLAREv_enameVARCHAR2(10);BEGINSELECTenameINTOv_enameFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_ename);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchaemployee');END;15DECLAREv_salNUMBER(6,2);v_deptnoNUMBER(2);BEGINBEGINSELECTdeptnoINTOv_deptnoFROMempWHEREempno=7844;END;SELECTavg(sal)INTOv_salFROMempWHEREdeptno=v_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);END;16注意若要在SQL*Plus環(huán)境中看到DBMS_OUTPUT.PUT_LINE方法的輸出結(jié)結(jié)果，必須將將環(huán)境變量SERVEROUTPUT設(shè)置為ON。SETSERVEROUTPUTON17（2）PL/SQL塊分類匿名塊匿名塊是指動(dòng)動(dòng)態(tài)生成，只只能執(zhí)行一次次的塊，不能能由其他應(yīng)用用程序調(diào)用。。命名塊命名塊是指一一次編譯可多多次執(zhí)行的PL/SQL程序，包括函函數(shù)、存儲(chǔ)過(guò)過(guò)程、包、觸觸發(fā)器等。它它們編譯后放放在服務(wù)器中中，由應(yīng)用程程序或系統(tǒng)在在特定條件下下調(diào)用執(zhí)行。。18命名塊示例CREATEORREPLACEPROCEDUREshowavgsal(p_deptnoNUMBER)ASv_salNUMBER(6,2);BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(v_sal);ENDshowavgsal;1914.2.2詞法單元字符集標(biāo)識(shí)符分隔符常量值注釋20（1）字符集PL/SQL的字符集包括括：大小寫字母：：A~Z，a~z數(shù)字：0~9空白：制表符符、空格和回回車數(shù)字符號(hào)：+-*/〈〉=標(biāo)點(diǎn)符號(hào)：~!@#$%^&*（）_|{}[]？；：,.“‘注意PL/SQL字符集不區(qū)分分大小寫。21（2）標(biāo)識(shí)符標(biāo)識(shí)符用于定定義PL/SQL變量、常量、、異常、游標(biāo)標(biāo)名稱、游標(biāo)標(biāo)變量、參數(shù)數(shù)、子程序名名稱和其他的的程序單元名名稱等。在PL/SQL程序中，標(biāo)識(shí)識(shí)符是以字母母開頭的，后后邊可以跟字字母、數(shù)字、、美元符號(hào)（（$）、井號(hào)（#）或下劃線（（_），其最大長(zhǎng)長(zhǎng)度為30個(gè)字符，并且且所有字符都都是有效的。。例如，X，v_empno，v_$等都是有效的的標(biāo)識(shí)符，而而X+y，_temp則是非法的標(biāo)標(biāo)識(shí)符。注意如果標(biāo)識(shí)符區(qū)區(qū)分大小寫、、使用預(yù)留關(guān)關(guān)鍵字或包含含空格等特殊殊符號(hào)，則需需要用“”括起來(lái)，稱為為引證標(biāo)識(shí)符符。例如標(biāo)識(shí)識(shí)符“mybook”和“exception”。22（3）分隔符+-*/=:=<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||=>**-分隔符是指有有特定含義的的單個(gè)符號(hào)或或組合符號(hào)23（4）常量值字符型文字以單引號(hào)引起起來(lái)的字符串串，在字符串串中的字符區(qū)區(qū)分大小寫。。如果字符串串中本身包含含單引號(hào)，則則用兩個(gè)連續(xù)續(xù)的單引號(hào)進(jìn)進(jìn)行轉(zhuǎn)義。數(shù)字型文字分為整數(shù)與實(shí)實(shí)數(shù)兩類。其其中，整數(shù)沒(méi)沒(méi)有小數(shù)點(diǎn)，，如123；而實(shí)數(shù)有小小數(shù)點(diǎn)，如123.45。可以用科學(xué)學(xué)計(jì)數(shù)法表示示數(shù)字型文字字，如123.45可以表示為1.2345E2。布爾型文字預(yù)定義的布爾爾型變量的取取值，包括TRUE，F(xiàn)ALSE，NULL三個(gè)值。日期型文字表示日期值，，其格式隨日日期類型格式式不同而不同同。24（5）注釋單行注釋--多行注釋以“/*”開始，以“*/”結(jié)束。DECLAREv_departmentCHAR(10);--variabletoholdthedepartmentnameBEGIN/*querythedepartmentnamewhichdepartmentnumberis10ouputthedepartmentnameintov_department*/SELECTdnameINTOv_departmentFROMdeptWHEREdeptno=10;END;2514.2.3數(shù)據(jù)類型數(shù)字類型字符類型日期/區(qū)間類型行標(biāo)識(shí)類型布爾類型原始類型LOB類型引用類型記錄類型集合類型%TYPE與%ROWTYPE26PL/SQL中常用的基本本數(shù)據(jù)類型分類數(shù)據(jù)類型數(shù)字類型NUMBER、BINARY_NUMBERPLS_NUMBER字符類型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/區(qū)間類型DATE、TIMESTAMP、INTERVAL行標(biāo)識(shí)類型ROWID、UROWID布爾類型BOOLEAN（TRUE、FALSE、NULL）原始類型RAW、LONGRAWLOB類型CLOB、BLOB、NCLOB、BFILE引用類型REFCURSOR，REFobject_type。記錄類型RECORD集合類型TABLE、VARRAY27數(shù)字類型NUMBER類型以十進(jìn)制制形式存儲(chǔ)整整數(shù)和浮點(diǎn)數(shù)數(shù)，語(yǔ)法為NUMBER（p，s）。其中，p為精度，即所所有有效數(shù)字字位數(shù)；s為刻度范圍，，即小數(shù)位數(shù)數(shù)。p的取值范圍為為1～38。BINARY_INTEGER類型用于表示示從-2147483647～+2147483647之間的整數(shù)，，以二進(jìn)制形形式存儲(chǔ)。當(dāng)當(dāng)發(fā)生溢出時(shí)時(shí)，將自動(dòng)轉(zhuǎn)轉(zhuǎn)換成NUMBER類型。PLS_INTEGER類型表示范圍圍與BINARY_INTEGER相同，但發(fā)生生溢出時(shí)會(huì)產(chǎn)產(chǎn)生錯(cuò)誤。28字符類型PL/SQL中的字符類型型與Oracle數(shù)據(jù)庫(kù)中的字字符類型類似似，但是允許許字符串的長(zhǎng)長(zhǎng)度有所不同同。VARCHAR2，CHAR主要用于存儲(chǔ)儲(chǔ)來(lái)自本地?cái)?shù)數(shù)據(jù)庫(kù)字符集集的字符，而而NCHAR，NVARCHAR2用于存儲(chǔ)來(lái)自自國(guó)家字符集集的字符串。。類型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB29日期/區(qū)間類型DATE：與數(shù)據(jù)庫(kù)中中的DATE類型相同，存存儲(chǔ)日期和時(shí)時(shí)間信息，包包括世紀(jì)、年年、月、日、、小時(shí)、分和和秒，不包括括秒的小數(shù)部部分。TIMESTAMP：與DATE類型相似，但但包括秒的小小數(shù)部分，有有以下3種形式。TIMESTAMP[(p)]：其中p為秒字段的的小數(shù)部分分精度。TIMESTAMP[(p)]WITHTIMEZONE：返回當(dāng)前前時(shí)區(qū)的時(shí)時(shí)間戳。TIMESTAMP[(p)]WITHLOACLTIMEZONE：返回?cái)?shù)據(jù)據(jù)庫(kù)時(shí)區(qū)的的時(shí)間戳。。30INTERVAL：用于存儲(chǔ)儲(chǔ)兩個(gè)時(shí)間間戳之間的的時(shí)間間隔隔，有下面面兩種形式式。INTERVALYEAR[(p)]TOMONTH：兩個(gè)時(shí)間間戳相差的的年數(shù)和月月數(shù)。INTERVALDAY[(dp)]TOSECOND[(sp)]：兩個(gè)時(shí)間間戳相差的的天數(shù)和秒秒數(shù)。31行標(biāo)識(shí)類型型ROWID表示行的物物理地址UROWID既可以表示示行的物理理地址，也也可以表示示行的邏輯輯地址。布爾類型（（BOOLEAN）只能在PL/SQL中使用，其其取值為邏邏輯值，包包括TRUE、FALSE、NULL。原始類型與Oracle數(shù)據(jù)庫(kù)中的的原始類型型相似，但但子節(jié)數(shù)不不同。類型PL/SQL中最大字節(jié)數(shù)Oracle中最大字節(jié)數(shù)RAW327672000LONGRAW327672G32LOB類型包括BLOB，CLOB，NCLOB和BFILE四種類型。。其中BLOB存放二進(jìn)制制數(shù)據(jù)，CLOB，NCLOB存放文本數(shù)數(shù)據(jù)，而BFILE存放指向操操作系統(tǒng)文文件的指針針。LOB類型變量可可以存儲(chǔ)4GB的數(shù)據(jù)量。。引用類型引用類型類類似于其他他高級(jí)語(yǔ)言言中的指針針類型。在在PL/SQL中，引用類類型包括游游標(biāo)的引用用類型和對(duì)對(duì)象的引用用類型，即即REFCURSOR和REFobject_type。33記錄類型記錄類型是是復(fù)合類型型，類似于于C語(yǔ)言中的結(jié)結(jié)構(gòu)體，是是一個(gè)包含含若干個(gè)成成員分量的的復(fù)合類型型。在使用記錄錄類型時(shí)，，需要先在在聲明部分分定義記錄錄類型和記記錄類型的的變量，然然后在執(zhí)行行部分引用用該記錄類類型變量或或其成員分分量。集合類型集合類型是是復(fù)合類型型，包括索索引表類型型、嵌套表表類型和可可變數(shù)組類類型。集合類型與與記錄類型型的區(qū)別在在于，記錄錄類型中的的成員分量量可以是不不同類型的的，類似于于結(jié)構(gòu)體，，而集合類類型中所有有的成員分分量必須具具有相同的的數(shù)據(jù)類型型，類似于于數(shù)組。34%TYPE與%ROWTYPE如果要定義義一個(gè)類型型與某個(gè)變變量的數(shù)據(jù)據(jù)類型或數(shù)數(shù)據(jù)庫(kù)表中中某個(gè)列的的數(shù)據(jù)類型型一致（不不知道該變變量或列的的數(shù)據(jù)類型型）的變量量，可以利利用%TYPE來(lái)實(shí)現(xiàn)。如果要定義義一個(gè)與數(shù)數(shù)據(jù)庫(kù)中某某個(gè)表結(jié)構(gòu)構(gòu)一致的記記錄類型的的變量，可可以使用%ROWTYPE來(lái)實(shí)現(xiàn)。注意變量的類型型隨參照的的變量類型型、數(shù)據(jù)庫(kù)庫(kù)表列類型型、表結(jié)構(gòu)構(gòu)的變化而而變化；如果數(shù)據(jù)庫(kù)庫(kù)表列中有有NOTNULL約束，則%TYPE與%ROWTYPE返回的數(shù)據(jù)據(jù)類型沒(méi)有有此限制。。35DECLAREv_salemp.sal%TYPE;v_empemp%ROWTYPE;BEGINSELECTsalINTOv_salFROMempWHEREempno=7844;SELECT*INTOv_empFROMempWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp.ename||v_emp.sal);END;3614.2.4變量與常量量變量與常量量的定義變量的作用用域37變量聲明（1）變量與常常量的定義義變量定義的的一般格式式variable_name[CONSTANT]datatype[NOTNULL][DEFAULT|:=expression];說(shuō)明變量或常量量名稱是一一個(gè)PL/SQL標(biāo)識(shí)符，應(yīng)應(yīng)符合標(biāo)識(shí)識(shí)符命名規(guī)規(guī)范；每行只能定定義一個(gè)變變量；如果加上關(guān)關(guān)鍵字CONSTANT，則表示所所定義的是是一個(gè)常量量，必須為為它賦初值值；如果定義變變量時(shí)使用用了NOTNULL關(guān)鍵字，則則必須為變變量賦初值值；如果變量沒(méi)沒(méi)有賦初值值，則默認(rèn)認(rèn)為NULL；使用DEFAULT或“:=”運(yùn)算符為變變量初始化化。38DECLAREv1NUMBER(4);v2NUMBER(4)NOTNULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULLTHENDBMS_OUTPUT.PUT_LINE('V1ISNULL!');ENDIF;DBMS_OUTPUT.PUT_LINE(v2||''||v3);END;39（2）變量的作作用域變量的作用用域是指變變量的有效效作用范圍圍，從變量量聲明開始始，直到塊塊結(jié)束。如果PL/SQL塊相互嵌套套，則在內(nèi)內(nèi)部塊中聲聲明的變量量是局部的的，只能在在內(nèi)部塊中中引用，而而在外部塊塊中聲明的的變量是全全局的，既既可以在外外部塊中引引用，也可可以在內(nèi)部部塊中引用用。如果內(nèi)部塊塊與外部塊塊中定義了了同名變量量，則在內(nèi)內(nèi)部塊中引引用外部塊塊的全局變變量時(shí)需要要使用外部部塊名進(jìn)行行標(biāo)識(shí)。40<<OUTER>>DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTERV_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;4114.2.5PL/SQL記錄用戶定義記記錄類型及及變量利用%ROWTYPE獲取記錄類類型定義變變量記錄類型變變量的應(yīng)用用在SELECT語(yǔ)句中使用用記錄類型型變量在INSERT語(yǔ)句中使用用記錄類型型變量在UPDATE語(yǔ)句中使用用記錄類型型變量在DELETE語(yǔ)句中使用用記錄類型型變量42（1）用戶定義義記錄類型型及變量定義記錄類類型的語(yǔ)法法為TYPErecord_typeISRECORD(field1datatype1[NOTNULL][DEFAULT|:=expr1],field2datatype2[NOTNULL][DEFAULT|:=expr2],……fieldndatatypen[NOTNULL][DEFAULT|:=exprn]);注意：相同記錄類類型的變量量可以相互互賦值；不同記錄類類型的變量量，即使成成員完全相相同也不能能相互賦值值；記錄類型只只能應(yīng)用于于定義該記記錄類型的的PL/SQL塊中，即記記錄類型是是局部的。43利用記錄類類型以及記記錄類型變變量，保存存員工信息息。DECLARETYPEt_empISRECORD(empnoNUMBER(4),enameCHAR(10),salNUMBER(6,2));v_empt_emp;BEGINSELECTempno,ename,salINTOv_empFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.ename||''||v_emp.sal);END;44（2）利利用用%ROWTYPE獲取取記記錄錄類類型型定定義義變變量量DECLAREv_emp1emp%ROWTYPE;v_emp2emp%ROWTYPE;CURSORc_empISSELECTempno,enameFROMempWHEREdeptno=10;v_emp10c_emp%ROWTYPE;BEGINSELECT*INTOv_emp1FROMempWHEREempno=7844;OPENc_emp;LOOPFETCHc_empINTOv_emp10;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp10.empno||‘'||v_emp10.ename);ENDLOOP;CLOSEc_emp;END;45（3）記記錄錄類類型型變變量量的的應(yīng)應(yīng)用用在SELECT語(yǔ)句句中中使使用用記記錄錄類類型型變變量量在SELECTINTO語(yǔ)句句中中使使用用記記錄錄類類型型變變量量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename||''||v_emp.sal);END;注意意記錄錄類類型型變變量量中中分分量量的的個(gè)個(gè)數(shù)數(shù)、、順順序序、、類類型型應(yīng)應(yīng)該該與與查查詢?cè)兞辛斜肀碇兄辛辛械牡膫€(gè)個(gè)數(shù)數(shù)、、順順序序、、類類型型完完全全匹匹配配。。46在SELECT語(yǔ)句句中中使使用用記記錄錄類類型型變變量量在SELECTINTO語(yǔ)句句中中使使用用記記錄錄類類型型變變量量成成員員DECLAREv_empemp%ROWTYPE;BEGINSELECTempno,ename,salINTOv_emp.empno,v_emp.ename,v_emp.salFROMempWHEREempno=7844;DBMS_OUTPUT.PUT_LINE(v_emp.empno||v_emp.ename||v_emp.sal);END;47在INSERT語(yǔ)句句中中使使用用記記錄錄類類型型變變量量在VALUES子句句中中使使用用記記錄錄類類型型變變量量DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:='BEIJING';V_dept.dname:='COMPUTER';INSERTINTODEPTVALUESv_dept;END;注意意記錄錄類類型型變變量量中中分分量量的的個(gè)個(gè)數(shù)數(shù)、、順順序序、、類類型型應(yīng)應(yīng)該該與與表表中中列列的的個(gè)個(gè)數(shù)數(shù)、、順順序序、、類類型型完完全全匹匹配配。。48在INSERT語(yǔ)句句中中使使用用記記錄錄類類型型變變量量在VALUES子句中使用記記錄類型變量量成員DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;INSERTINTOemp(empno,ename,mgr,sal)VALUES(1234,'TOM',v_emp.mgr,v_emp.sal);END;49在UPDATE語(yǔ)句中使用記記錄類型變量量在SET子句中使用記記錄類型變量量（使用ROW關(guān)鍵字）DECLAREv_deptdept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:='TIANJIN';V_dept.dname:='COMPUTER';UPDATEdeptSETROW=v_deptWHEREdeptno=50;END;注意記錄類型變量量中分量的個(gè)個(gè)數(shù)、順序、、類型應(yīng)該與與表中列的個(gè)個(gè)數(shù)、順序、、類型完全匹匹配。50在UPDATE語(yǔ)句中使用記記錄類型變量量在SET子句中使用記記錄類型變量量成員DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;UPDATEempSETsal=v_emp.sal,comm=v_mWHEREempno=7369;END;51在DELETE語(yǔ)句中使用記記錄類型變量量DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=7844;DELETEFROMempWHEREdeptno=v_emp.deptno;END;5214.2.6編譯指示編譯指示是對(duì)對(duì)編譯程序發(fā)發(fā)出的特殊指指令，也稱為為偽指令，不不會(huì)改變程序序含義。它只只是向編譯程程序傳遞信息息，類似于嵌嵌入在SQL中的注釋。在PL/SQL中使用PRAGMA關(guān)鍵字通知編編譯程序，PL/SQL語(yǔ)句的剩余部部分是一個(gè)編編譯指示或命命令。編譯指指示在編譯時(shí)時(shí)被處理，而而不會(huì)在運(yùn)行行時(shí)被執(zhí)行，，類似于C語(yǔ)言中的#define。53PL/SQL提供以下4種編譯指示EXCEPTION_INIT：告訴編譯程程序?qū)⒁粋€(gè)特特定的錯(cuò)誤號(hào)號(hào)與程序中所所聲明的異常常標(biāo)識(shí)符關(guān)聯(lián)聯(lián)起來(lái)。RESTRICT_REFERENCES：告訴編譯程程序打包程序序的純度，即即對(duì)函數(shù)中可可以使用的SQL語(yǔ)句和包變量量進(jìn)行限制。。SERIALLY_REUSEABLE：告訴PL/SQL運(yùn)行引擎時(shí)，，在數(shù)據(jù)引用用之間不要保保持包級(jí)數(shù)據(jù)據(jù)。AUTONOMOUS_TRANSACTION：告訴編譯程程序，該程序序塊為自治事事務(wù)，即該事事務(wù)的提交和和回滾是獨(dú)立立進(jìn)行的。5414.2.7PL/SQL中SQL語(yǔ)句由于PL/SQL執(zhí)行采用早期期綁定，即在在編譯階段對(duì)對(duì)變量進(jìn)行綁綁定，識(shí)別程程序中標(biāo)識(shí)符符的位置，檢檢查用戶權(quán)限限、數(shù)據(jù)庫(kù)對(duì)對(duì)象等信息，，因此在PL/SQL中只允許出現(xiàn)現(xiàn):SELECTDML(UPDATE、DELETE、INSERT)事務(wù)控制語(yǔ)句句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL語(yǔ)句不可以直直接使用55通常，利用SQL語(yǔ)句對(duì)數(shù)據(jù)庫(kù)庫(kù)進(jìn)行操作時(shí)時(shí)，各種相關(guān)關(guān)量都在代碼碼中以常量的的形式指定，，而在PL/SQL中可以通過(guò)變變量動(dòng)態(tài)指定定各種相關(guān)量量的值，從而而實(shí)現(xiàn)對(duì)數(shù)據(jù)據(jù)庫(kù)的動(dòng)態(tài)操操作。DECLAREv_empnoNUMBER(4);BEGINv_empno:=&x;UPDATEempSETsal=sal+100WHEREempno=v_empno;END;56SELECT語(yǔ)句在PL/SQL程序中，使用用SELECT…INTO語(yǔ)句查詢一個(gè)個(gè)記錄的信息息。其語(yǔ)法為：SELECTselect_list_itemINTOvariable_list|record_variableFROMtableWHEREcondition;57根據(jù)員工名或或員工號(hào)查詢?cè)儐T工信息，，程序?yàn)椋篋ECLAREv_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;58注意：SELECT…INTO語(yǔ)句只能查詢?cè)円粋€(gè)記錄的的信息，如果果沒(méi)有查詢到到任何數(shù)據(jù)，，會(huì)產(chǎn)生NO_DATA_FOUND異常；如果查查詢到多個(gè)記記錄，則會(huì)產(chǎn)產(chǎn)生TOO_MANY_ROWS異常。INTO句子后的變量量用于接收查查詢的結(jié)果，，變量的個(gè)數(shù)數(shù)、順序應(yīng)該該與查詢的目目標(biāo)數(shù)據(jù)相匹匹配，也可以以是記錄類型型的變量。59用SELECT…INTO語(yǔ)句查詢10號(hào)部門所有員員工信息。DECLAREv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREdeptno=10;END;/*ERROR位于第1行:ORA-01422:實(shí)際返回的行行數(shù)超出請(qǐng)求求的行數(shù)ORA-06512:在line460DML語(yǔ)句PL/SQL中DML語(yǔ)句對(duì)標(biāo)準(zhǔn)SQL語(yǔ)句中的DML語(yǔ)句進(jìn)行了擴(kuò)擴(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;61WHERE標(biāo)識(shí)符的區(qū)分分系統(tǒng)首先查看看WHERE子句中的標(biāo)識(shí)識(shí)符是否與表表中的列名相相同，如果相相同，則該標(biāo)標(biāo)識(shí)符被解釋釋為列名；如如果沒(méi)有同名名列，系統(tǒng)檢檢查該標(biāo)識(shí)符符是不是PL/SQL語(yǔ)句塊的變量量。字符串比較填充比較：通通過(guò)在短字符符串后添加空空格，使兩個(gè)個(gè)字符串達(dá)到到相同長(zhǎng)度，，然后根據(jù)每每個(gè)字符的ASCII碼進(jìn)行比較。。非填充比較：：根據(jù)每個(gè)字字符的ASCII碼進(jìn)行行比較較，最最先結(jié)結(jié)束的的字符符串為為小。。62那么何何時(shí)采采用填填充比比較，，何時(shí)時(shí)采用用非填填充比比較呢呢？PL/SQL中規(guī)定定，對(duì)對(duì)定長(zhǎng)長(zhǎng)的字字符串串（CHAR類型的的字符符串和和字符符串常常量））采用用填充充比較較；如如果比比較的的字符符串中中有一一個(gè)是是變長(zhǎng)長(zhǎng)字符符串（（VARCHAR2類型的的字符符串）），則則采用用非填填充比比較。。63例如，，已知知emp表中ename列類型型為VARCHAR2（10），執(zhí)執(zhí)行下下面的的代碼碼。DECLAREv_enameCHAR(10):='TURNER';--v_enameVARCHAR2(20);--v_enameemp.ename%TYPE:='TURNER';v_salemp.sal%TYPE;BEGINSELECTsalINTOv_salFROMempWHEREename=v_ename;dbms_output.put_line(v_sal);END;/DECLARE*第1行出現(xiàn)現(xiàn)錯(cuò)誤誤:ORA-01403:未找到數(shù)據(jù)據(jù)ORA-06512:在line664產(chǎn)生錯(cuò)誤的的原因是VARCHAR2（10）類型與CHAR（10）類型比較較時(shí)采用非非填充比較較，因此無(wú)無(wú)法查詢到到員工名為為“TURNER”的員工?？煽梢詫_ename變量類型修修改為VARCHAR2（10）類型，也也可以直接接采用emp.ename%TYPE方式定義。。因此，為了了保證程序序的正確執(zhí)執(zhí)行，一定定要使PL/SQL語(yǔ)句塊中的的變量與要要比較的數(shù)數(shù)據(jù)庫(kù)列擁?yè)碛邢嗤牡臄?shù)據(jù)類型型，可以使使用%TYPE或%ROWTYPE來(lái)定義變量量。65RETURNING如果要查詢?cè)儺?dāng)前DML語(yǔ)句操作的的記錄的信信息，可以以在DML語(yǔ)句末尾使使用RETURNING語(yǔ)句返回該該記錄的信信息。RETURNING語(yǔ)句的基本本語(yǔ)法：RETURNINGselect_list_itemINTOvariable_list|record_variable;66DECLAREv_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;6714.3控制結(jié)構(gòu)選擇結(jié)構(gòu)循環(huán)結(jié)構(gòu)跳轉(zhuǎn)結(jié)構(gòu)6814.3.1選擇結(jié)構(gòu)IF語(yǔ)句CASE語(yǔ)句69（1）IF語(yǔ)句語(yǔ)法IFcondition1THENstatements1;[ELSIFcondition2THENstatements2;]……[ELSEelse_statements];ENDIF;注意條件是一個(gè)個(gè)布爾型變變量或表達(dá)達(dá)式，取值值只能是TRUE，F(xiàn)ALSE，NULL。70例如，輸入入一個(gè)員工工號(hào)，修改改該員工的的工資，如如果該員工工為10號(hào)部門，工工資增加100；若為20號(hào)部門，工工資增加160；若為30號(hào)部門，工工資增加200；否則增加加300。71DECLAREv_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:=160;ELSIFv_deptno=30THENv_increment:=200;ELSEv_increment:=300;ENDIF;UPDATEempSETsal=sal+v_incrementWHEREempno=v_empno;END;72由于PL/SQL中的邏輯運(yùn)運(yùn)算結(jié)果有有TRUE，F(xiàn)ALSE和NULL三種，因此此在進(jìn)行選選擇條件判判斷時(shí)，要要考慮條件件為NULL的情況。例例如，下面面兩個(gè)程序序，如果不不考慮條件件為NULL的情況，則則運(yùn)行結(jié)果果是一致的的，但是若若考慮條件件為NULL的情況，則則結(jié)果就不不同了。7374為了避免條條件為NULL時(shí)出現(xiàn)歧義義，應(yīng)該在在程序中進(jìn)進(jìn)行條件是是否為NULL的檢查。75（2）CASE語(yǔ)句基本語(yǔ)法CASEWHENcondition1THENstatements1;WHENcondition2THENstatements2;……WHENconditionnTHENstatementsn;[ELSEelse_statements;]ENDCASE;注意在CASE語(yǔ)句中，當(dāng)當(dāng)?shù)谝粋€(gè)WHEN條件為真時(shí)時(shí)，執(zhí)行其其后的操作作，操作完完后結(jié)束CASE語(yǔ)句。其他他的WHEN條件不再判判斷，其后后的操作也也不執(zhí)行。。76根據(jù)輸入的的員工號(hào)，，修改該員員工工資。。如果該員員工工資低低于1000，則工資增增加200；如果工資資在1000～2000之間，則增增加150；如果工資資在2000～3000之間，則增增加100；否則增加加50。77DECLAREv_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;78等值比較的的CASE語(yǔ)句基本語(yǔ)語(yǔ)法CASEtest_valueWHENvalue1THENstatements1;WHENvalue2THENstatements2;……WHENvaluenTHENstatementsn;[ELSEelse_statements;]ENDCASE;79DECLAREv_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;8014.3.2循環(huán)結(jié)構(gòu)簡(jiǎn)單循環(huán)WHILE循環(huán)FOR循環(huán)81（1）簡(jiǎn)單循環(huán)環(huán)語(yǔ)法LOOPsequence_of_statement;EXIT[WHENcondition];ENDLOOP;注意在循環(huán)體中中一定要包包含EXIT語(yǔ)句，否則則程序進(jìn)入入死循環(huán)82執(zhí)行CREATETABLEtemp_table(num_colNUMBER,info_colCHAR(10))語(yǔ)句創(chuàng)建temp_table表，然后利利用循環(huán)向向temp_table表中插入50條記錄。DECLAREv_counterBINARY_INTEGER:=1;BEGINLOOPINSERTINTOtemp_tableVALUES(v_Counter,'Loopindex');v_counter:=v_counter+1;EXITWHENv_counter>50;ENDLOOP;END;83（2）WHILE循環(huán)基本語(yǔ)法WHILEconditionLOOPsequence_of_statement;ENDLOOP;84利用用WHILE循環(huán)環(huán)向向temp_table表中中插插入入50條記記錄錄。。DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counter<=50LOOPINSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_counter+1;ENDLOOP;END;85（3）FOR循環(huán)環(huán)基本本語(yǔ)語(yǔ)法法FORloop_counterIN[REVERSE]low_bound..high_boundLOOPsequence_of_statement;ENDLOOP;注意意：：循環(huán)環(huán)變變量量不不需需要要顯顯式式定定義義，，系系統(tǒng)統(tǒng)隱隱含含地地將將它它聲聲明明為為BINARY_INTEGER變量量；；系統(tǒng)統(tǒng)默默認(rèn)認(rèn)時(shí)時(shí)，，循循環(huán)環(huán)變變量量從從下下界界往往上上界界遞遞增增計(jì)計(jì)數(shù)數(shù)，，如如果果使使用用REVERSE關(guān)鍵鍵字字，，則則表表示示循循環(huán)環(huán)變變量量從從上上界界向向下下界界遞遞減減計(jì)計(jì)數(shù)數(shù)；；循環(huán)環(huán)變變量量只只能能在在循循環(huán)環(huán)體體中中使使用用，，不不能能在在循循環(huán)環(huán)體體外外使使用用。。86利用用FOR循環(huán)環(huán)向向temp_table表中中插插入入50條記記錄錄。。BEGINFORv_counterIN1..50LOOPINSERTINTOtemp_tableVALUES(v_counter,'LoopIndex');ENDLOOP;END;8714.3.3跳轉(zhuǎn)轉(zhuǎn)結(jié)結(jié)構(gòu)構(gòu)語(yǔ)法法格格式式：：《標(biāo)號(hào)號(hào)》…GOTO標(biāo)號(hào)號(hào)；；說(shuō)明明：：塊內(nèi)內(nèi)可可以以跳跳轉(zhuǎn)轉(zhuǎn)，，內(nèi)內(nèi)層層塊塊可可以以跳跳到到外外層層塊塊，，但但外外層層塊塊不不能能跳跳到到內(nèi)內(nèi)層層。。IF語(yǔ)句句不不能能跳跳入入。。不不能能從從循循環(huán)環(huán)體體外外跳跳入入循循環(huán)環(huán)體體內(nèi)內(nèi)。。不不能能從從子子程程序序外外部部跳跳到到子子程程序序中中。。由于于goto語(yǔ)句句的的缺缺點(diǎn)點(diǎn)，，建建議議盡盡量量少少用用甚甚至至不不用用goto語(yǔ)句句。。88DECLAREv_counterBINARY_INTEGER:=1;BEGIN<<LABEL>>INSERTINTOtemp_tableVALUES(v_counter,'Loopindex');v_counter:=v_Counter+1;IFv_counter<=50THENGOTOLABEL;ENDIF;END;8914.4游標(biāo)游標(biāo)的概念及及類型顯式游標(biāo)隱式游標(biāo)游標(biāo)變量9014.4.1游標(biāo)的概念及類型游標(biāo)的概念游標(biāo)（CURSOR）是Oracle系統(tǒng)在內(nèi)存中中開辟的一個(gè)個(gè)工作區(qū)，在在其中存放SELECT語(yǔ)句返回的查查詢結(jié)果。使用游標(biāo)時(shí)，，SELECT語(yǔ)句查詢的結(jié)結(jié)果可以是單單條記錄，多多條記錄，也也可以是零條條記錄。游標(biāo)工作區(qū)中中，存在著一一個(gè)指針（POINTER）,在初始狀態(tài)它它指向查詢結(jié)結(jié)果的首記錄錄。91游標(biāo)的類型顯式游標(biāo)由用戶定義、、操作，用于于處理返回多多行數(shù)據(jù)的SELECT查詢。隱式游標(biāo)由系統(tǒng)自動(dòng)進(jìn)進(jìn)行操作，用用于處理DML語(yǔ)句和返回單單行數(shù)據(jù)的SELECT查詢。9214.4.2顯式游標(biāo)顯式游標(biāo)的操操作顯式游標(biāo)的屬屬性參數(shù)化顯式游游標(biāo)顯式游標(biāo)的檢檢索利用游標(biāo)更新新或刪除數(shù)據(jù)據(jù)93（1）顯式游標(biāo)的的操作步驟定義游標(biāo)打開游標(biāo)檢索游標(biāo)關(guān)閉游標(biāo)94定義游標(biāo)語(yǔ)法CURSORcursor_nameISselect_statement;說(shuō)明游標(biāo)必須在PL/SQL塊的聲明部分分進(jìn)行定義；；游標(biāo)定義時(shí)可可以引用PL/SQL變量，但變量量必須在游標(biāo)標(biāo)定義之前定定義；定義游標(biāo)時(shí)并并沒(méi)有生成數(shù)數(shù)據(jù)，只是將將定義信息保保存到數(shù)據(jù)字字典中；游標(biāo)定義后，，可以使用cursor_name%ROWTYPE定義游標(biāo)類型型變量。95打開游標(biāo)語(yǔ)法OPENcursor_name;說(shuō)明檢查變量的值值執(zhí)行游標(biāo)定義義時(shí)對(duì)應(yīng)的SELECT語(yǔ)句，將查詢?cè)兘Y(jié)果檢索到到工作區(qū)中。。游標(biāo)指針指向向第一個(gè)元組組一旦游標(biāo)打開開，就無(wú)法再再次打開，除除非先關(guān)閉如果游標(biāo)定義義中的變量值值發(fā)生變化，，則只能在下下次打開游標(biāo)標(biāo)時(shí)才起作用用。96檢索游標(biāo)語(yǔ)法格式FETCHcursor_nameINTOvariable_list|record_variable;說(shuō)明在使用FETCH語(yǔ)句之前必須須先打開游標(biāo)標(biāo)對(duì)游標(biāo)第一次次使用FETCH語(yǔ)句時(shí)，游標(biāo)標(biāo)指針指向第第一條記錄，，因此操作的的對(duì)象是第一一條記錄，使使用后，游標(biāo)標(biāo)指針指向下下一條記錄。。游標(biāo)指針只能能向下移動(dòng)，，不能回退INTO子句中的變量量個(gè)數(shù)、順序序、數(shù)據(jù)類型型必須與工作作區(qū)中每行記記錄的字段數(shù)數(shù)、順序以及及數(shù)據(jù)類型一一一對(duì)應(yīng)。97關(guān)閉游標(biāo)語(yǔ)法格式CLOSEcursor_name;說(shuō)明游標(biāo)所對(duì)應(yīng)的的內(nèi)存工作區(qū)區(qū)變?yōu)闊o(wú)效，，釋放與游標(biāo)標(biāo)相關(guān)的系統(tǒng)統(tǒng)資源。98根據(jù)輸入的部部門號(hào)查詢某某個(gè)部門的員員工信息，部部門號(hào)在程序序運(yùn)行時(shí)指定定。99DECLAREv_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;100（2）顯式游標(biāo)的的屬性%ISOPEN布爾型。如果果游標(biāo)已經(jīng)打打開，返回TRUE,否則為FALSE。%FOUND布爾型，如果果最近一次使使用FETCH語(yǔ)句，有返回回結(jié)果則為TRUE,否則為FALSE;%NOTFOUND布爾型，如果果最近一次使使用FETCH語(yǔ)句,沒(méi)有返回結(jié)果果則為TRUE,否則為FALSE;%ROWCOUNT數(shù)值型，返回回到目前為止止從游標(biāo)緩沖沖區(qū)檢索的元元組數(shù)。%BULK_ROWCOUNT（i）數(shù)值型，用于于取得FORALL語(yǔ)句執(zhí)行批綁綁定操作時(shí)第第i個(gè)元素所影響響的行數(shù)。101（3）參數(shù)化顯式式游標(biāo)參數(shù)化游標(biāo)定定義語(yǔ)法格式式CURSORcursor_name(parameter1datatype[,parameter2datatype…])ISselect_statement打開參數(shù)化游游標(biāo)的方法OPENcursor_name(parameter1[,parameter2…])102注意：定義參數(shù)化游游標(biāo)時(shí)，只能能指定參數(shù)的的類型，而不不能指定參數(shù)數(shù)的長(zhǎng)度、精精度、刻度；；打開帶參數(shù)的的游標(biāo)時(shí)，實(shí)實(shí)參的個(gè)數(shù)和和數(shù)據(jù)類型等等必須與游標(biāo)標(biāo)定義時(shí)形參參個(gè)數(shù)和數(shù)據(jù)據(jù)類型等相匹匹配。103DECLARECURSORc_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;104（4）顯式游標(biāo)的的檢索利用簡(jiǎn)單循環(huán)環(huán)檢索游標(biāo)利用WHILE循環(huán)檢索游標(biāo)標(biāo)利用FOR循環(huán)檢索游標(biāo)標(biāo)105利用簡(jiǎn)單循環(huán)環(huán)檢索游標(biāo)語(yǔ)法DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;LOOPFETCH…INTO…;EXITWHENcursor_name%NOTFOUND;……ENDLOOP;CLOSEcursor_name;END;注意EXITWHEN子句應(yīng)該是FETCH……INTO語(yǔ)句的下一條條語(yǔ)句。106利用簡(jiǎn)單循環(huán)環(huán)統(tǒng)計(jì)并輸出出各個(gè)部門的的平均工資。。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;107利用WHILE循環(huán)檢索索游標(biāo)語(yǔ)法DECLARECURSORcursor_nameISSELECT…;BEGINOPENcursor_name;FETCH…INTO…;…WHILEcursor_name%FOUNDLOOPFETCH…INTO…;……ENDLOOP;CLOSEcursor;END;注意在循環(huán)體體外進(jìn)行行一次FETCH操作，作作為第一一次循環(huán)環(huán)的條件件。108利用WHILE循環(huán)統(tǒng)計(jì)計(jì)并輸出出各個(gè)部部門的平平均工資資。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;109利用FOR循環(huán)檢索索游標(biāo)語(yǔ)法DECLARECURSORcursor_nameISSELECT…;BEGINFORloop_variableINcursor_nameLOOP……ENDLOOP;END;110系統(tǒng)隱含含地定義義了一個(gè)個(gè)數(shù)據(jù)類類型為%ROWTYPE的變量，，并以此此作為循循環(huán)的計(jì)計(jì)算器。。系統(tǒng)自動(dòng)動(dòng)打開游游標(biāo)，不不用顯式式地使用用OPEN語(yǔ)句打開開；系統(tǒng)重復(fù)復(fù)地自動(dòng)動(dòng)從游標(biāo)標(biāo)工作區(qū)區(qū)中提取取數(shù)據(jù)并并放入計(jì)計(jì)數(shù)器變變量中。。系統(tǒng)自動(dòng)動(dòng)進(jìn)行%FOUND屬性檢查查以確定定是否有有數(shù)據(jù)當(dāng)游標(biāo)工工作區(qū)中中所有的的記錄都都被提取取完畢或或循環(huán)中中斷時(shí)，，系統(tǒng)自自動(dòng)地關(guān)關(guān)閉游標(biāo)標(biāo)。111利用FOR循環(huán)統(tǒng)計(jì)計(jì)并輸出出各個(gè)部部門的平平均工資資。DECLARECURSORc_dept_statISSELECTdeptno,avg(sal)avgsalFROMempGROUPBYdeptno;BEGINFORv_deptINc_dept_statLOOPDBMS_OUTPUT.PUT_LINE(v_dept.deptno||''||v_dept.avgsal);ENDLOOP;END;112由于用FOR循環(huán)檢索索游標(biāo)時(shí)時(shí)，游標(biāo)標(biāo)的打開開、數(shù)據(jù)據(jù)的檢索索、是否否檢索到到數(shù)據(jù)的的判斷以以及游標(biāo)標(biāo)的關(guān)閉閉都是自自動(dòng)進(jìn)行行的，因因此，可可以不在在聲明部部分定義義游標(biāo)，，而在FOR語(yǔ)句中直直接使用用子查詢?cè)儭EGINFORv_empIN(select*fromempwheredeptno=10)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno||''||v_emp.ename);

ENDLOOP;END;113（5）利用游游標(biāo)更新新或刪除除數(shù)據(jù)游標(biāo)定義義語(yǔ)法CURSORcursor_nameISSELECTselect_list_itemFROMtableFORUPDATE[OFcolumn_reference][NOWAIT];注意打開游標(biāo)標(biāo)時(shí)對(duì)相相應(yīng)的表表加鎖（（通常SELECT操作不在在數(shù)據(jù)上上設(shè)置任任何鎖）），其他他用戶不不能對(duì)該該表進(jìn)行行DML操作；若數(shù)據(jù)對(duì)對(duì)象已經(jīng)經(jīng)被其他他會(huì)話加加鎖，則則當(dāng)前會(huì)會(huì)話掛起起等待（（默認(rèn)狀狀態(tài)），，若指定定了NOWAIT子句，則則不等待待，返回回ORACLE錯(cuò)誤。對(duì)于多表表查詢時(shí)時(shí)，可以以通過(guò)OF子句指定定某個(gè)要要加鎖的的表的列列的形式式，對(duì)特特定的表表加鎖，，而其他他表不加加鎖；否否則所有有表都加加鎖。當(dāng)用戶執(zhí)執(zhí)行COMMIT或ROLLBACK操作時(shí)，，數(shù)據(jù)上上的鎖會(huì)會(huì)自動(dòng)被被釋放。。114更新或修修改數(shù)據(jù)據(jù)的語(yǔ)法法為UPDATE|DELETE…WHERECURRENTOFcursor_name注意如果游標(biāo)標(biāo)定義時(shí)時(shí)沒(méi)有使使用FORUPDATE子句，則則不能利利用該游游標(biāo)修改改或刪除除數(shù)據(jù)庫(kù)庫(kù)中的數(shù)數(shù)據(jù)。115修改員工工的工資資，如果果員工的的部門號(hào)號(hào)為10，則工資資提高100；如果部部門號(hào)為為20，則工資資提高150；如果部部門號(hào)為為30，則工資資提高200；否則工工資提高高250。116DECLARECURSORc_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;COMMIT;END;11714.4.3隱式游標(biāo)標(biāo)概念所有的SQL語(yǔ)句都有有一個(gè)執(zhí)執(zhí)行的緩緩沖區(qū)，，隱式游游標(biāo)就是是指向該該緩沖區(qū)區(qū)的指針針，由系系統(tǒng)隱含含地打開開、處理理和關(guān)閉閉。隱式式游標(biāo)又又稱為SQL游標(biāo)。隱式游標(biāo)標(biāo)主要用用于處理理INSERT、UPDATE，DELETE以及單行行的SELECT…INTO語(yǔ)句，沒(méi)沒(méi)有OPEN，F(xiàn)ETCH，CLOSE等操作命命令。118隱式游標(biāo)標(biāo)屬性SQL%ISOPEN：布爾型型值，判判斷隱式式游標(biāo)是是否已經(jīng)經(jīng)打開。。對(duì)用戶戶而言，，該屬性性值始終終為FALSE，因?yàn)椴俨僮鲿r(shí)系系統(tǒng)自動(dòng)動(dòng)打開，，操作完完后立即即自動(dòng)關(guān)關(guān)閉。SQL%FOUND：布爾型型值，判判斷當(dāng)前前的操作作是否會(huì)會(huì)對(duì)數(shù)據(jù)據(jù)庫(kù)產(chǎn)生生影響。。如果有有數(shù)據(jù)的的插入、、刪除、、修改或或查詢到到數(shù)據(jù)，，則返回回TRUE，否則返返回FALSE。SQL%NOTFOUND：布爾型型值，判判斷當(dāng)前前的操作作是否對(duì)對(duì)數(shù)據(jù)庫(kù)庫(kù)產(chǎn)生影影響。如如果沒(méi)有有數(shù)據(jù)的的插入、、刪除、、修改或或沒(méi)有查查詢到數(shù)數(shù)據(jù)，則則返回TRUE，否則返返回FALSE。SQL%ROWCOUNT：數(shù)值型型，返回回當(dāng)前操操作所涉涉及的數(shù)數(shù)據(jù)庫(kù)中中的行數(shù)數(shù)。119修改員工工號(hào)為1000的員工工工資，將將其工資資增加100。如果該該員工不不存在，，則向emp表中插入入一個(gè)員員工號(hào)為為1000，工資為為1600的員工。。120BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;12114.4.4游標(biāo)變量量概念游標(biāo)變量量是一個(gè)個(gè)指向多多行查詢?cè)兘Y(jié)果集集的指針針，不與與特定的的查詢綁綁定，因因此具有有非常大大的靈活活性，可可以在打打開游標(biāo)標(biāo)變量時(shí)時(shí)定義查查詢，可可以返回回不同結(jié)結(jié)構(gòu)的結(jié)結(jié)果集使用游標(biāo)標(biāo)變量包包括游標(biāo)引用用類型（（REFCURSOR）聲明游標(biāo)標(biāo)變量打開游標(biāo)標(biāo)變量檢索游標(biāo)標(biāo)變量關(guān)閉游標(biāo)標(biāo)變量122（1）定義游游標(biāo)引用用類型及及游標(biāo)變變量語(yǔ)法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用用于指指定定定義的的游標(biāo)標(biāo)類型型返回回結(jié)果果集的的類型型，該該類型型必須須是記記錄類類型。。如果果定義義游標(biāo)標(biāo)引用用類型型時(shí)帶帶有RETURN子句，，則用用其定定義的的變量量稱為為強(qiáng)游游標(biāo)變變量，，否則則稱為為弱游游標(biāo)變變量。。在Oracle10g中，系系統(tǒng)預(yù)預(yù)定義義了一一個(gè)游游標(biāo)引引用類類型，，稱為為SYS_REFCURSOR，可以以直接接使用用它定定義游游標(biāo)變變量。。123語(yǔ)法ref_cursor_type_namevariable_name;例如TYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;124（2）打開開游標(biāo)標(biāo)變量量語(yǔ)法OPENcursor_variableFORselect_statement;注意如果打打開的的游標(biāo)標(biāo)變量量是強(qiáng)強(qiáng)游標(biāo)標(biāo)變量量，則則查詢?cè)冋Z(yǔ)句句的返返回類類型必必須與與游標(biāo)標(biāo)引用用類型型定義義中RETURN子句指指定的的返回回類型型相匹匹配。。例如OPENv_empFORSELECT*FROMemp;OPENv_generalFORSELECTempno,ename,sal,deptnoFROMemp;OPENmy_cursorFORSELECT*FROMdept;125（3）檢索索游標(biāo)標(biāo)變量量語(yǔ)法LOOPFETCHcursor_variableINTOvariable1,variable2,…;EXITWHENcursor_variable%NOTFOUND;……ENDLOOP;注意檢索游游標(biāo)變變量時(shí)時(shí)只能能使用用簡(jiǎn)單單循環(huán)環(huán)或WHILE循環(huán)，，不能能采用用FOR循環(huán)。。126（4）關(guān)閉閉游標(biāo)標(biāo)變量量語(yǔ)法CLOSEcursor_variable;127DECLARETYPEemp_cursor_typeISREFCURSORRETURN