《Oracle數(shù)據(jù)庫應(yīng)用》課件11

第11章PL/SQLPL/SQL基礎(chǔ)1PL/SQL支持的SQL語句2流程控制語句3游標(biāo)4異常5存儲過程6函數(shù)7觸發(fā)器811.1PL/SQL基礎(chǔ)PL(ProceduralLanguage)/SQL為Oracle數(shù)據(jù)庫提供的過程化編程語言可用于分組SQL語句，將它們一起發(fā)向服務(wù)器，減少網(wǎng)絡(luò)傳輸，提高程序運行效率具有良好的可移植性，可運行于任何有OracleServer的地方提供了完善的程序控制結(jié)構(gòu)，可以實現(xiàn)復(fù)雜、功能強大的應(yīng)用程序集成于OracleServer與Oracle應(yīng)用開發(fā)工具之中，可方便地實現(xiàn)分布式計算1.PL/SQL程序塊的結(jié)構(gòu)一段完整的PL/SQL程序塊結(jié)構(gòu)DECLARE

說明部分;BEGIN

執(zhí)行部分;EXCEPTION

異常處理部分;END;/說明部分/異常處理部分可選標(biāo)識符遵循先定義后使用的原則每一條語句必須以分號；結(jié)束在執(zhí)行部分不能使用DDL語句當(dāng)執(zhí)行部分發(fā)生一個錯誤時，將會引起一個異常異常發(fā)生后，正常的執(zhí)行被停止并且轉(zhuǎn)移到異常程序處理異常處理完后，將結(jié)束對應(yīng)PL/SQL塊的執(zhí)行注釋/*注釋多行文本*/--注釋一行文本NULL語句屬于可執(zhí)行語句在SQL*Plus中，PL/SQL程序執(zhí)行完成時總是返回提示“PL/SQL過程已成功完成”PL/SQL程序塊可以嵌套2.PL/SQL編寫規(guī)則標(biāo)識符以字母開頭，后面可以是字母、數(shù)字、$、下劃線和#標(biāo)識符最長不超過30個字符標(biāo)識符的作用范圍：視具體情況而定程序塊、子程序、包等PL/SQL運算符邏輯運算：AND、OR、NOT算術(shù)運算：+、-、*、/、**(冪)關(guān)系運算：=、!=、<>、>、<、>=、<=、LIKE、BETWEENxANDy、ISNULL集合運算：IN

字符串運算：+、-、||(連接)IN運算要求集合元素用圓括號括起來xin(‘男’,‘女’)判斷某個列或表達式的值是否為空(NULL)，應(yīng)使用ISNULL運算符LIKE：匹配運算_代表任意一個字符(必須有一個字符)%代表任意多個字符(可為0個字符)匹配字符可以多次使用，可以組合使用標(biāo)識符命名規(guī)范PL/SQL中運算符的優(yōu)先級在PL/SQL程序中使用的變量PL/SQL變量非PL/SQL變量PL/SQL變量類型標(biāo)量數(shù)據(jù)類型(Scalar)：存放一個值復(fù)合類型(Composite)：包含多個項的值包括TABLE、RECORD、NESTEDTABLE和VARRAY等類型參照類型(Reference)：為參照表類型，在一個基表的記錄中存放被參照表中記錄的指針大對象類型(LOB)：被稱為定位器(Locators)，存放指向數(shù)據(jù)的指針，包括CLOB、BLOB、NCLOB和BFILE等類型部分常用PL/SQL標(biāo)量數(shù)據(jù)類型PL/SQL中的變量和常量必須先定義然后才能使用賦值語句語法變量名:=

PL/SQL表達式;例題：P172-176復(fù)合數(shù)據(jù)類型%TYPE類型描述符簡化變量的定義自動保持某些變量在數(shù)據(jù)類型上的一致性減少程序維護工作使用以下形式定義變量表名.列名%TYPE已定義的變量或常量名%TYPE取數(shù)據(jù)表中某列的類型或已定義的某些數(shù)據(jù)對象的類型作為新常量或變量的類型例題：P176記錄類型記錄類型用于表示邏輯相關(guān)的一組信息定義記錄類型的語法TYPErecord_type_nameISRECORD(field1data_type,field2data_type,

……);引用記錄類型變量的域記錄變量名.域名%ROWTYPE新記錄變量名已定義的記錄類型變量%ROWTYPE新記錄變量名表名%ROWTYPE例題：P177PL/SQL沒有輸入和輸出能力，需借助DBMS_OUTPUT包非PL/SQL變量非PL/SQL變量包括兩種類型替換變量：用于輸入，使用&前綴引用替換變量獲取輸入數(shù)據(jù)綁定變量：用于輸出，將運算結(jié)果返回到主機環(huán)境替換變量在SQL*Plus中使用DEFINE定義替換變量替換變量沒有類型，只是一個代碼替換操作替換變量可不事先定義就直接使用環(huán)境變量VERIFY用于設(shè)定是否顯示替換后的語句行綁定變量使用VARIABLE定義使用PRINT語句輸出綁定變量的值在PL/SQL塊中使用綁定變量方法:綁定變量名允許服務(wù)器輸出PL/SQL程序運行結(jié)果SETserveroutputON變量的作用范圍被限定在定義變量的塊內(nèi)。在該塊之外，變量不可見，也不能起作用出錯報告函數(shù)(用于PL/SQL塊的例外處理部分)SQLCODE：返回最近例外的錯誤碼SQLERRM：返回與SQLCODE返回錯誤碼相對應(yīng)的錯誤信息不可在SQL語句中直接使用這兩個函數(shù)先用變量接收它們的返回值通過變量來使用它們的值一般情況下，應(yīng)建立一個錯誤信息日志表，記載在系統(tǒng)運行中發(fā)生的錯誤信息出錯報告函數(shù)使用示例DECLAREv_codeNUMBER;v_msgVARCHAR2(255);BEGIN

……EXCEPTION

……WHENOTHERSTHENROLLBACK;

v_code:=SQLCODE;v_msg:=SQLERRM;INSERTINTOerror_msgVALUES(SYSTIMESTAMP,v_code,v_msg);END;3.PL/SQL程序的形式和調(diào)試環(huán)境PL/SQL程序的幾種形式無名塊存儲過程/函數(shù)包(命名的PL/SQL程序塊)數(shù)據(jù)庫觸發(fā)器OracleFormsBuilder等開發(fā)工具中的過程/函數(shù)OracleFormsBuilder等開發(fā)工具中的觸發(fā)器PL/SQL編程指導(dǎo)原則使用注釋文檔化代碼遵循代碼字符大小寫規(guī)范遵循命名規(guī)范使用縮進增強程序的可讀性適當(dāng)換行，分行書寫語句11.2PL/SQL支持的SQL語句SELECTINTO語句從數(shù)據(jù)庫中查詢/提取出數(shù)據(jù)，并賦值給相關(guān)變量語法SELECT[DISTINCT|ALL]select_listINTOvariable_list|record_nameFROMtable_name|view_name|subquery[WHEREsearch_condition];SELECTINTO語句應(yīng)僅返回一條記錄，返回空行或者多條記錄都會引起系統(tǒng)預(yù)定義的異常NO_DATA_FOUND或TOO_MANY_ROWS發(fā)生INSERT/UPDATE/DELETE更新語句的格式與SQL語言格式相同支持子查詢列出的字段類型和個數(shù)要與子查詢的字段類型和個數(shù)一致例題：P179-18011.3流程控制語句條件語句IF分支語句CASEIF條件THEN

語句序列;ENDIF;IF條件THEN

語句序列1;ELSE

語句序列2;ENDIF;IF條件lTHEN

語句序列1;ELSIF

條件2THEN

語句序列2;ELSE

語句序列3;ENDIF;CASE表達式

WHEN常量1THEN語句1;

WHEN常量2

THEN語句2;

……ELSE語句x;ENDCASE;循環(huán)控制語句基本循環(huán)語句LOOP

語句序列;

EXITWHEN布爾表達式;ENDLOOP;數(shù)值FOR循環(huán)語句FOR計數(shù)器變量IN[REVERSE]下界..上界LOOP

語句序列;ENDLOOP;WHILE循環(huán)語句WHILE條件LOOP

語句序列;ENDLOOP;游標(biāo)FOR循環(huán)語句跳轉(zhuǎn)控制語句EXIT語句用于退出當(dāng)前循環(huán)GOTO語句用于無條件地轉(zhuǎn)向一個有標(biāo)號的語句或一個PL/SQL塊NULL語句不作任何操作，只是將控制轉(zhuǎn)移到下一條語句例題：P182-183IFconditionTHENEXIT;ENDIF;EXIT[WHENcondition];11.4游標(biāo)SELECTINTO在PL/SQL程序塊中使用時，返回的查詢結(jié)果只能包含一條記錄對于查詢結(jié)果為多條時，SQL提供了游標(biāo)機制來解決該問題游標(biāo)cursor是指向查詢結(jié)果集的一個指針，通過游標(biāo)可將查詢結(jié)果集中的記錄逐一取出游標(biāo)分為兩類：顯式游標(biāo)由程序員定義和維護，名稱由程序員確定一般用于需要對多條記錄按順序一條一條進行處理的場合，被處理的數(shù)據(jù)集稱為活動數(shù)據(jù)集隱式游標(biāo)系統(tǒng)自動創(chuàng)建并管理對應(yīng)于DML語句和SELECT語句的執(zhí)行空間和相應(yīng)的信息，系統(tǒng)自動將此類游標(biāo)命名為SQL1.顯式游標(biāo)顯式游標(biāo)的使用過程：(1)聲明游標(biāo)指定游標(biāo)所對應(yīng)的SELECT語句(2)打開游標(biāo)執(zhí)行游標(biāo)對應(yīng)的查詢，建立結(jié)果數(shù)據(jù)集指針指向第一條數(shù)據(jù)記錄(3)獲取數(shù)據(jù)并進行處理提取指針指向的當(dāng)前記錄數(shù)據(jù)到接收變量中指針后移一條記錄(4)關(guān)閉游標(biāo)當(dāng)最后一條記錄處理完成后應(yīng)關(guān)閉游標(biāo)釋放已經(jīng)建立的SQL工作區(qū)定義游標(biāo)語法CURSOR游標(biāo)名[(形式參數(shù)表)]ISSELECT子句;若無參數(shù)，參數(shù)的括號應(yīng)一并省略掉打開游標(biāo)語法：OPEN游標(biāo)名[(實際參數(shù)表)];使用游標(biāo)獲取數(shù)據(jù)語法：FETCH游標(biāo)名INTO變量名表;關(guān)閉游標(biāo)的語法：CLOSE游標(biāo)名;帶參數(shù)的游標(biāo)游標(biāo)中帶有參數(shù)，使用不同的參數(shù)值打開游標(biāo)將得到不同的結(jié)果集語法：CURSORcursor_name(param_namedatatype)isselect_statement;游標(biāo)的使用流程(1)用OPEN語句打開游標(biāo)(2)用FETCH語句獲取一行數(shù)據(jù)到接收變量中，并進行處理。當(dāng)獲取不到新記錄時轉(zhuǎn)到(4)(3)返回到(2)繼續(xù)取下一行數(shù)據(jù)(4)用CLOSE語句關(guān)閉游標(biāo)關(guān)閉的游標(biāo)可使用OPEN語句再次打開，由此得到新狀態(tài)的數(shù)據(jù)集可打開的游標(biāo)數(shù)由參數(shù)OPEN_CURSOR限定，默認值為300。例題：P1852.游標(biāo)的屬性顯式游標(biāo)的屬性%ISOPEN布爾型。如游標(biāo)已打開，值為TRUE%NOTFOUND布爾型。如最近一次FETCH沒有返回結(jié)果，值為TRUE%FOUND布爾型。如最近一次FETCH有返回結(jié)果，則值為TRUE%ROWCOUNT數(shù)值型。取值為到當(dāng)前為止返回的記錄數(shù)游標(biāo)的屬性可用于IF、EXIT等語句，不能直接在SQL語句中使用游標(biāo)屬性的引用方法：將屬性直接寫在游標(biāo)名后隱式游標(biāo)的屬性執(zhí)行SQL語句時，系統(tǒng)隱含創(chuàng)建并打開一個游標(biāo)Oracle統(tǒng)一用“SQL”作為隱式游標(biāo)名隱式游標(biāo)也有四個屬性判斷是否有滿足條件的被修改記錄，如沒有，則插入新記錄UPDATEempSETsal=sal*1.05WHEREempno=v_empno;IFSQL%NOTFOUNDTHENINSERTINTOempVALUES(v_empno,v_ename,...);ENDIF;提取并處理10條記錄LOOPFETCHemp_cursorINTOv_empno,v_ename;EXITWHENemp_cursor%ROWCOUNT>10

ORemp_cursor%NOTFOUND;

……ENDLOOP;例題：P1863.用于游標(biāo)的循環(huán)使用FETCH語句每次只能獲取一條記錄，若依次獲取結(jié)果集中的所有記錄，需使用循環(huán)重復(fù)執(zhí)行FETCHLOOP循環(huán)LOOP

語句;

……ENDLOOP;LOOP循環(huán)當(dāng)FETCH語句不能從游標(biāo)當(dāng)前行取出數(shù)據(jù)時，循環(huán)結(jié)束FOR循環(huán)FOR記錄型變量名IN游標(biāo)名LOOP

語句;

……ENDLOOP;例題：P187FOR與其它循環(huán)語句相比所具有的優(yōu)勢：循環(huán)控制變量不需事先定義使用FOR之前，系統(tǒng)能自動打開游標(biāo)。FOR循環(huán)結(jié)束后，系統(tǒng)能自動關(guān)閉游標(biāo)，無需人為操作在FOR中，系統(tǒng)能自動執(zhí)行FETCH，無需人為操作4.使用游標(biāo)更新表中的數(shù)據(jù)用游標(biāo)鎖定記錄在定義游標(biāo)的SELECT語句中帶FORUPDATE子句在UPDATE語句中使用游標(biāo)限定要修改的記錄UPDATEtableSETcolumn_name=sql_expressionWHERECURRENTOFcursor_name;在DELETE語句中使用游標(biāo)限定要刪除的記錄DELETEfromtableWHERECURRENTOFcursor_name;例題：P18811.5異常異常是PL/SQL執(zhí)行期間引發(fā)的一個錯誤或警告可由Oracle錯誤引發(fā)或由程序顯式地引發(fā)任何異常都將終止當(dāng)前PL/SQL程序塊的執(zhí)行PL/SQL中包括3種異常：預(yù)定義異常：21個常見錯誤，由SQL語句隱式引發(fā)非預(yù)定義異常：Oracle服務(wù)器返回的其他錯誤用戶自定義異常：由用戶定義并用RAISE語句顯式引發(fā)異常處理部分的語法EXCEPTIONWHENexception1THENstatement1;statement2;……[WHENexception2THENstatement1;statement2;……][WHENOTHERSTHENstatement1;statement2;……]1.系統(tǒng)預(yù)定義異常系統(tǒng)對常見發(fā)生的錯誤事先定義好的異常，有錯誤編號和異常名稱常見的預(yù)定義異常：ACCESS_INTO_NULLCASE_NOT_FOUNDCOLLECTION_IS_NULLCURSOR_ALREADY_OPENDUP_VAL_ON_INDEXINVALID_CURSORINVALID_NUMBERLOGIN_DENIEDNO_DATA_FOUNDNOT_LOGGED_ONPROGRAM_ERRORROWTYPE_MISMATCHSELF_IS_NULLSTORAGE_ERRORSUBSCRIPT_BEYOND_COUNTSUBSCRIPT_OUTSIDE_LIMITSYS_INVALID_IDTIMEOUT_ON_RESOURCETOO_MANY_ROWSVALUE_ERRORZERO_DIVIDENO_DATA_FOUND和TOO_MANY_ROWS的防止SETserveroutputONDECLAREretNUMBER;BEGINSELECTCOUNT(*)INTOretFROMempWHEREempno=9;

--NO_DATA_FOUNDIFret=0THENDBMS_OUTPUT.PUT_LINE('沒有符合條件的記錄！');

return;ENDIF;--TOO_MANY_ROWSIFret>1THENDBMS_OUTPUT.PUT_LINE('數(shù)據(jù)有錯誤，符合條件的記錄有多條！');

return;ENDIF;……END;例題：P1902.非預(yù)定義異常OracleServer錯誤號格式ORA-數(shù)字非預(yù)定義異常的處理過程如下：(1)聲明一個異常類型變量異常類型變量名EXCEPTION;(2)將異常類型變量與OracleServer錯誤號進行關(guān)聯(lián)PRAGMAEXCEPTION_INIT(異常類型變量名,OracleServer錯誤號);(3)在異常處理部分引用異常類型變量名捕獲并處理該異常例題：P1913.用戶自定義異常用戶自定義的異常處理需要3個步驟：(1)在PL/SQL塊的說明部分定義異常類型變量異常類型變量名EXCEPTION;(2)在PL/SQL塊的執(zhí)行部分將其引發(fā)RAISE異常類型變量名;(3)在異常處理部分引用異常類型變量名捕獲并處理該異常例題：P19111.6存儲過程存儲過程能完成一定處理/計算功能并存儲在數(shù)據(jù)字典中的程序，調(diào)用時在數(shù)據(jù)庫服務(wù)器上運行，如存儲過程、函數(shù)、觸發(fā)器一種命名的PL/SQL程序塊，可以設(shè)置0個或多個輸入、輸出參數(shù)經(jīng)編譯后存儲在數(shù)據(jù)庫中，執(zhí)行效率比SQL語句高創(chuàng)建存儲過程語法CREATE[ORREPLACE]PROCEDUREprocedure_name[(argument[{IN|OUT|INOUT}]datatype,…)]{IS|AS}

說明部分;BEGIN

執(zhí)行部分;

EXCEPTION

異常處理部分;END[procedure_name];對比SQLServer存儲過程的語法：

CREATEPROC[EDURE]存儲過程名[;編號] [{@參數(shù)數(shù)據(jù)類型}[VARYING][=默認值] [OUTPUT

]][,...n]

WITH {RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTION}]

AS

SQL語句[...n]

參數(shù)IN型參數(shù)：由實際參數(shù)單向傳遞給形式參數(shù)OUT型參數(shù)：用于返回值INOUT型參數(shù)：給定實際參數(shù)值并返回一個結(jié)果值默認為IN形式參數(shù)說明時不可指定數(shù)據(jù)寬度形式參數(shù)應(yīng)以“P_”前綴命名注釋：--、/**/、REMSHOWERRORS查看創(chuàng)建錯誤SHOWERRORS[{FUNCTION|PROCEDURE|PACKAGE|PACKAGEBODY|TRIGGER|VIEW}[schema.]name]不可在存儲過程中引用綁定變量1）無參數(shù)的存儲過程不帶輸入?yún)?shù)和輸出參數(shù)2）帶in參數(shù)的存儲過程帶輸入?yún)?shù)，可將數(shù)據(jù)傳遞給存儲過程3）帶out參數(shù)的存儲過程可利用輸出參數(shù)輸出數(shù)據(jù)4）帶inout參數(shù)的存儲過程參數(shù)類型為輸入輸出型例題：P192存儲過程的調(diào)用方式execute/execcallbegin...end;例題：P194有參數(shù)的存儲過程調(diào)用1、按參數(shù)名稱傳遞參數(shù)，無需考慮參數(shù)順序如：SQL>execinsert_emp(eno=>7369,ename=>’smith’,sal=>1500);2、按參數(shù)位置傳遞參數(shù)，需考慮參數(shù)順序、類型和個數(shù)如：SQL>execinsert_emp(7369,’smith’,1500);3、有的參數(shù)按名稱傳遞，有的參數(shù)按位置傳遞。必須將按位置的參數(shù)值寫在最左邊，按名稱的參數(shù)寫在最右邊如：SQL>execinsert_emp(7369,’smith’,sal=>1500);查看存儲過程參數(shù)DESC[RIBE]procedure_name刪除存儲過程DROPPROCEDUREprocedure_name;函數(shù)程序塊，可通過return返回函數(shù)的執(zhí)行結(jié)果使用函數(shù)可簡化客戶端程序的開發(fā)，提高程序性能11.7函數(shù)創(chuàng)建函數(shù)語法CREATE[ORREPLACE]FUNCTIONfunction_name[(argument[IN]datatype,…)]

RETURNreturn_type{IS|AS}

說明部分;BEGIN

……return返回值表達式;EXCEPTION

異常處理部分;

return返回值表達式;END[function_name];例題：P195在SQL*Plus中調(diào)用函數(shù)方法1：借助虛表dualSELECTfunction_nameFROMDUAL;SELECTfunction_name(para1,para2)FROMDUAL;方法2：使用綁定變量variable綁定變量名變量類型;exec:綁定變量名:=函數(shù)名(參數(shù));print綁定變量名方法3：使用dbms_outputexecdbms_output.put_line(函數(shù)名(參數(shù)));例題：P196查看函數(shù)的參數(shù)和返回值類型DESCRIBEfunction_name刪除函數(shù)DROPFUNCTIONfunction_name;11.8觸發(fā)器觸發(fā)器是特定事件出現(xiàn)的時候，自動執(zhí)行的代碼塊，類似于存儲過程，但用戶不能直接調(diào)用它們觸發(fā)器的功能允許/限制對表的修改自動生成派生列，比如自增字段強制數(shù)據(jù)一致性提供審計和日志記錄防止無效的事務(wù)處理啟用復(fù)雜的業(yè)務(wù)邏輯觸發(fā)器概述觸發(fā)器的組成部分觸發(fā)器名稱觸發(fā)語句觸發(fā)器限制觸發(fā)操作觸發(fā)器類型按觸發(fā)時間劃分Before觸發(fā)器After觸發(fā)器Insteadof觸發(fā)器按觸發(fā)事件劃分語句觸發(fā)器行觸發(fā)器數(shù)據(jù)庫事件觸發(fā)器系統(tǒng)事件觸發(fā)器用戶事件觸發(fā)器

1.DML觸發(fā)器創(chuàng)建DML觸發(fā)器觸發(fā)事件：INSERT、UPDATE和DELETE中觸發(fā)事件的作用對象：指定表/視圖和表的某些列觸發(fā)器主體執(zhí)行的時機：BEFORE、AFTER觸發(fā)器的類型：行觸發(fā)器、語句觸發(fā)器觸發(fā)動作的條件：WHEN條件子句如何區(qū)分新老記錄：NEW、OLD這兩個變量只有在使用了關(guān)鍵字FOREACHROW才存在.且update語句兩個都有，insert只有:new,delect只有:old觸發(fā)器主體：滿足觸發(fā)條件后的動作，為PL/SQL塊創(chuàng)建DML觸發(fā)器的語法CREATE[ORREPLACE]TRIGGER[schema.]trigger_name{BEFORE|AFTER}{DELETE|INSERT|UPDATE[OFcolumn1,...]}ON[schema.]table[REFERENCING{OLD[AS]old|NEW[AS]new}][FOREACHROW][WHEN(when_condition)]pl/sql_block;在觸發(fā)器主體PL/SQL塊引用新、舊值行的列:new.列名:old.列名行級觸發(fā)器創(chuàng)建觸發(fā)器時使用foreachrow子句，DML語句每操作一行記錄，該觸發(fā)器就被觸發(fā)一次語句級觸發(fā)器創(chuàng)建觸發(fā)器時未使用foreachrow子句，DML語句執(zhí)行之前或之后，該觸發(fā)器只被觸發(fā)一次例題：P200使用條件謂詞對各DML操作分別做出響應(yīng)DML條件謂詞包括插入：INSERTING若激發(fā)觸發(fā)器的操作是insert，則inserting為true修改：UPDATING若激發(fā)觸發(fā)器的操作是update，則updating為trueUPDATING可帶列名參數(shù)，如UPDATING('sal')刪除：DELETING若激發(fā)觸發(fā)器的操作是delete，則deleting為true例題：P2012.替代觸發(fā)器(INSTEADOF)在oracle中，若視圖的數(shù)據(jù)源來自單表，則該視圖可進行更新。若視圖數(shù)據(jù)源來自兩個以上表時，這個視圖不可更新有時為了操作方便，需要對多表視圖進行更新。這時，可通過建立替代觸發(fā)器來替代該視圖原有更新以達到多表更新的效果語法：CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOFevent1[ORevent2ORevent3]

ON

view_name[REFERENCINGOLDASold|NEWASnew][FOR