數(shù)據(jù)庫原理與應(yīng)用 Oracle版 教學(xué)課件 馬忠貴 寧淑榮 曾廣平 姚琳- 第6章 PL SQL編程基礎(chǔ)

數(shù)據(jù)庫原理與應(yīng)用（Oracle版）2第6章PL/SQL編程基礎(chǔ)6.1PL/SQL簡介6.2PL/SQL程序結(jié)構(gòu)6.3PL/SQL控制結(jié)構(gòu)6.4異常處理6.5游標(biāo)6.6存儲過程6.7函數(shù)6.8觸發(fā)器6.9程序包6.1PL/SQL簡介PL/SQL語言（ProceduralLanguage/SQL，過程化SQL語言）是Oracle推出的過程化的SQL編程語言，使用PL/SQL可以為SQL語言引入結(jié)構(gòu)化的程序處理能力，例如可以在PL/SQL中定義常量、變量、游標(biāo)、存儲過程等，可以使用條件、循環(huán)等流程控制語句。PL/SQL的這種特性使得開發(fā)人員可以在數(shù)據(jù)庫中添加業(yè)務(wù)邏輯，并且由于業(yè)務(wù)邏輯與數(shù)據(jù)均位于數(shù)據(jù)庫服務(wù)器端，比客戶端編寫的業(yè)務(wù)邏輯能提供更好的性能。36.1PL/SQL簡介PL/SQL是一種塊結(jié)構(gòu)的語言，它將一組語句放在一個塊中，一次性發(fā)送給服務(wù)器，PL/SQL引擎分析收到的PL/SQL語句塊中的內(nèi)容，把其中的過程控制語句由PL/SQL引擎自身去執(zhí)行，把PL/SQL塊中的SQL語句交給服務(wù)器的SQL語句執(zhí)行器執(zhí)行。PL/SQL塊發(fā)送給服務(wù)器后，先被編譯然后執(zhí)行，對于有名稱的PL/SQL塊（如存儲過程、函數(shù)、觸發(fā)器、程序包）可以單獨編譯，永久的存儲在數(shù)據(jù)庫中，隨時準(zhǔn)備執(zhí)行。46.1PL/SQL簡介6.1PL/SQL簡介編寫和編譯PL/SQL程序塊主要分以下6個步驟：1）啟動SQL*Plus工具。2）打開PL/SQL程序文件，例如：SQL>EDITc:\plsqlblock1.sql;3）在編輯窗口中輸入PL/SQL語句，在END;結(jié)束符的下一行開頭加“/”作為結(jié)束標(biāo)志。4）保存剛輸入的PL/SQL塊，關(guān)閉編輯窗口。5）激活dbms_output包，編譯和運(yùn)行塊。SQL>SETSERVEROUTPUTON;SQL>STARTc:\plsqlblock1.sql;6）如果編譯有錯，回到第3）步檢查語法，然后回到第5）步重新編譯。直到成功為止。56.2PL/SQL程序結(jié)構(gòu)基本塊結(jié)構(gòu)每個PL/SQL邏輯塊包括3部分，語法如下：[DECLARE

聲明變量、常量、游標(biāo)、自定義異常]--聲明語句（1）BEGIN

SQL語句

PL/SQL語句

--執(zhí)行語句（2）[EXCEPTION

異常發(fā)生時執(zhí)行的動作]--異常執(zhí)行語句（3）END;66.2PL/SQL程序結(jié)構(gòu)變量定義變量定義的方法如下：DECLARE變量名[CONSTANT]數(shù)據(jù)類型[NOTNULL][:=值|DEFAULT值];在程序中為變量賦值的語法如下：變量名:=值或PL/SQL表達(dá)式;76.2PL/SQL程序結(jié)構(gòu)【例】變量的定義和初始化。SQL>SETSERVEROUTPUTONSQL>DECLARE--聲明部分標(biāo)識 v_jobVARCHAR2(9); v_countBINARY_INTEGERDEFAULT0; v_total_salNUMBER(9,2):=0; v_dateDATE:=SYSDATE; c_tax_rateCONSTANTNUMBER(3,2):=8.25; v_validBOOLEANNOTNULL:=TRUE; BEGIN v_job:='MANAGER';--在程序中賦值 DBMS_OUTPUT.PUT_LINE(v_job);--輸出變量v_job的值 DBMS_OUTPUT.PUT_LINE(v_count);--輸出變量v_count的值 DBMS_OUTPUT.PUT_LINE(v_date);--輸出變量v_date的值 DBMS_OUTPUT.PUT_LINE(c_tax_rate);--輸出變量c_tax_rate的值 END;86.2PL/SQL程序結(jié)構(gòu)定義字段變量的方法DECLARE變量名表名.字段名%TYPE;【例】根據(jù)表的字段定義變量。SQL>SETSERVEROUTPUTONSQL>DECLARE v_enameemp.ename%TYPE;--根據(jù)字段定義變量 BEGIN SELECTenameINTOv_enameFROMempWHERE

empno=7788; DBMS_OUTPUT.PUT_LINE(v_ename);--輸出變量的值 END;96.2PL/SQL程序結(jié)構(gòu)記錄變量的定義方法如下：DECLARE記錄變量名表名%ROWTYPE;獲得記錄變量的字段的方法是：記錄變量名.字段名，如。106.2PL/SQL程序結(jié)構(gòu)PL/SQL常見的運(yùn)算符1）算術(shù)運(yùn)算：加(+)、減(?)、乘(*)、除(/)、指數(shù)(**)。2）關(guān)系運(yùn)算：小于(<)、小于等于(<=)、大于(>)、大于等于(>=)、等于(=)、不等于(!=或<>)。3）字符運(yùn)算：連接(||)。4）邏輯運(yùn)算：與(AND)、或(OR)、非(NOT)。ISNULL：用來判斷運(yùn)算對象是否為空，為空則返回TRUE。LIKE：用來判斷字符串是否與模式匹配。BETWEEN…AND…：判斷值是否位于一個區(qū)間。IN(…)：測試運(yùn)算對象是否在一組值的列表中。116.3PL/SQL控制結(jié)構(gòu)PL/SQL程序段中有3種控制結(jié)構(gòu)：條件結(jié)構(gòu)循環(huán)結(jié)構(gòu)順序結(jié)構(gòu)126.3PL/SQL控制結(jié)構(gòu)條件結(jié)構(gòu)分支結(jié)構(gòu)IF條件表達(dá)式1THEN

語句序列1;[ELSIF條件表達(dá)式2THEN

語句序列2;ELSE

語句序列n;]ENDIF;136.3PL/SQL控制結(jié)構(gòu)條件結(jié)構(gòu)根據(jù)具體情況，分支結(jié)構(gòu)可以有以下幾種形式：IF…THEN…ENDIFIF…THEN…ELSE…ENDIFIF…THEN…ELSIF…ELSE…ENDIF【例】如果溫度大于30℃，則顯示“溫度偏高”。SQL>SETSERVEROUTPUTONSQL>DECLARE v_tempratureNUMBER(5):=32; v_resultBOOLEAN:=FALSE; BEGIN v_result:=v_temprature>30; IFv_resultTHEN DBMS_OUTPUT.PUT_LINE('溫度'||v_temprature||'度，偏高'); ENDIF; END;146.3PL/SQL控制結(jié)構(gòu)條件結(jié)構(gòu)選擇結(jié)構(gòu)(CASE語句適用于分情況的多分支處理)1）基本CASE結(jié)構(gòu)基本CASE結(jié)構(gòu)的語法如下：CASE選擇變量名

WHEN表達(dá)式1THEN語句序列1

WHEN表達(dá)式2THEN語句序列2

……

WHEN表達(dá)式nTHEN語句序列n

ELSE語句序列n+1ENDCASE;156.3PL/SQL控制結(jié)構(gòu)條件結(jié)構(gòu)選擇結(jié)構(gòu)2）表達(dá)式結(jié)構(gòu)CASE語句變量:=CASE選擇變量名

WHEN表達(dá)式1THEN值1

WHEN表達(dá)式2THEN值2

……

WHEN表達(dá)式nTHEN值n

ELSE值n+1END;166.3PL/SQL控制結(jié)構(gòu)條件結(jié)構(gòu)選擇結(jié)構(gòu)搜索CASE結(jié)構(gòu)CASE

WHEN條件表達(dá)式1THEN語句序列1

WHEN條件表達(dá)式2THEN語句序列2

WHEN條件表達(dá)式nTHEN語句序列n

ELSE語句序列n+1ENDCASE;176.3PL/SQL控制結(jié)構(gòu)循環(huán)結(jié)構(gòu)基本LOOP循環(huán)基本LOOP循環(huán)的語法如下：LOOP--循環(huán)起始標(biāo)識

執(zhí)行語句;EXIT[WHEN條件];ENDLOOP;--循環(huán)結(jié)束標(biāo)識該循環(huán)的作用是反復(fù)執(zhí)行LOOP與ENDLOOP之間的語句。EXIT用于在循環(huán)過程中退出循環(huán)，WHEN用于定義EXIT的退出條件。如果沒有WHEN條件，遇到EXIT語句則無條件退出循環(huán)。186.3PL/SQL控制結(jié)構(gòu)循環(huán)結(jié)構(gòu)FOR…LOOP循環(huán)FOR…LOOP循環(huán)是固定次數(shù)循環(huán)，語法如下：FOR控制變量IN[REVERSE]下限..上限LOOP

執(zhí)行語句;ENDLOOP;循環(huán)控制變量是隱含定義的，不需要聲明。下限和上限用于指明循環(huán)次數(shù)。正常情況下循環(huán)控制變量的取值由下限到上限遞增，REVERSE關(guān)鍵字表示循環(huán)控制變量的取值由上限到下限遞減。196.3PL/SQL控制結(jié)構(gòu)循環(huán)結(jié)構(gòu)WHILE…LOOP循環(huán)WHILE循環(huán)是有條件循環(huán)，其格式如下：WHILE條件LOOP

執(zhí)行語句;ENDLOOP;當(dāng)條件滿足時，執(zhí)行循環(huán)體；當(dāng)條件不滿足時，則循環(huán)結(jié)束。如果第一次判斷條件為假，則不執(zhí)行循環(huán)體。206.3PL/SQL控制結(jié)構(gòu)GOTO語句GOTO語句的格式如下：GOTO標(biāo)簽標(biāo)記;這是個無條件轉(zhuǎn)向語句。執(zhí)行GOTO語句時，控制會立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語句（使用<<>>聲明）。PL/SQL中對GOTO語句有一些限制，對于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。216.4異常處理異常處理的語法異常處理部分一般放在PL/SQL程序體的后半部，語法如下：EXCEPTION

WHEN表達(dá)式1THEN<異常處理語句1>

WHEN表達(dá)式2THEN<異常處理語句2>

……

WHEN表達(dá)式nTHEN<異常處理語句n>

WHENOTHERSTHEN<其它異常處理語句>END;226.4異常處理異常處理的分類1）預(yù)定義異常處理Oracle預(yù)定義的異常情況大約有24個。對這種異常情況的處理，無需在程序中定義，由Oracle自動將其觸發(fā)。2）非預(yù)定義異常處理即其他標(biāo)準(zhǔn)的Oracle錯誤。對這種異常情況的處理，需要用戶在程序中定義，然后由Oracle自動將其觸發(fā)。3）用戶自定義異常處理程序執(zhí)行過程中，出現(xiàn)編程人員認(rèn)為的非正常情況。當(dāng)與一個異常錯誤相關(guān)的錯誤出現(xiàn)時，就會隱含觸發(fā)該異常錯誤。用戶定義的異常錯誤是通過顯式使用RAISE語句來觸發(fā)。當(dāng)觸發(fā)一個異常錯誤時，控制就轉(zhuǎn)向到EXCEPTION塊異常錯誤部分，執(zhí)行錯誤處理代碼。236.5游標(biāo)SQL是面向集合的，其結(jié)果一般是集合量（多條記錄），而PL/SQL的變量一般是標(biāo)量。其一組變量一次只能存放一條記錄。所以僅僅使用變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求。因為查詢結(jié)果的記錄數(shù)是不確定的，事先也就不知道要聲明幾個變量。為此。在PL/SQL中引入了游標(biāo)（Cursor）的概念，用游標(biāo)來協(xié)調(diào)這兩種不同的處理方式246.5游標(biāo)對于不同的SQL語句，游標(biāo)的使用情況不同：SQL語句

顯式游標(biāo)非查詢語句

隱式游標(biāo)結(jié)果是單行的查詢語句

隱式或顯式游標(biāo)結(jié)果是多行的查詢語句

顯式游標(biāo)256.5游標(biāo)顯式游標(biāo)與隱式游標(biāo)的比較266.6存儲過程存儲過程是PL/SQL語句和可選控制流語句的預(yù)編譯集合，以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其他強(qiáng)大的編程功能。存儲過程在數(shù)據(jù)庫開發(fā)過程以及數(shù)據(jù)庫維護(hù)和管理等任務(wù)中有非常重要的作用。276.6存儲過程創(chuàng)建存儲過程CREATE[ORREPLACE]PROCEDURE[模式名.]存儲過程名 [參數(shù)名[IN|OUT|INOUT]數(shù)據(jù)類型,…]{IS|AS} [變量的聲明部分]BEGIN <執(zhí)行部分>EXCEPTION <可選的異常錯誤處理程序>END[存儲過程名];286.6存儲過程調(diào)用存儲過程存儲過程建立完成后，只要通過授權(quán)，用戶就可以在SQLPLUS、Oracle開發(fā)工具或第三方開發(fā)工具中來調(diào)用運(yùn)行。Oracle使用EXECUTE語句來實現(xiàn)對存儲過程的調(diào)用：EXEC[UTE]存儲過程名(參數(shù)1,參數(shù)2…);刪除存儲過程DROPPROCEDURE存儲過程名;296.7函數(shù)函數(shù)一般用于計算和返回一個值，可以將經(jīng)常需要進(jìn)行的計算寫成函數(shù)。函數(shù)的調(diào)用是表達(dá)式的一部分，而存儲過程的調(diào)用是一條PL/SQL語句。函數(shù)與存儲過程在創(chuàng)建的形式上有些相似，也是編譯后放在內(nèi)存中供用戶使用，只不過調(diào)用時函數(shù)要用表達(dá)式，而不像存儲過程只需調(diào)用過程名。另外，函數(shù)必須有一個返回值，而存儲過程則沒有。306.7函數(shù)創(chuàng)建函數(shù)CREATE[ORREPLACE]FUNCTION[模式名.]函數(shù)名 [參數(shù)名[IN]數(shù)據(jù)類型,…] RETURN數(shù)據(jù)類型{IS|AS} [變量的聲明部分]BEGIN <執(zhí)行部分>

（RETURN表達(dá)式）EXCEPTION <可選的異常錯誤處理程序>END[函數(shù)名];316.7函數(shù)調(diào)用函數(shù)調(diào)用函數(shù)時可以用全局變量接收其返回值。如：SQL>VARIABLEemp_numNUMBER;SQL>EXECUTEemp_num:=fun_empcount(10);刪除函數(shù)DROPFUNCTION函數(shù)名;326.8觸發(fā)器在Oracle系統(tǒng)里，觸發(fā)器類似過程和函數(shù)，都有聲明、執(zhí)行和異常處理過程的PL/SQL塊。觸發(fā)器在數(shù)據(jù)庫里以獨立的對象存儲，它與存儲過程不同的是，存儲過程通過其它程序來啟動運(yùn)行或直接啟動運(yùn)行，而觸發(fā)器是由觸發(fā)事件來啟動運(yùn)行。即觸發(fā)器是當(dāng)某個事件發(fā)生時自動地隱式運(yùn)行，并且觸發(fā)器不能接收參數(shù)，所以運(yùn)行觸發(fā)器就叫觸發(fā)。336.8觸發(fā)器創(chuàng)建觸發(fā)器CREATEORREPLACETRIGGER[模式名.]觸發(fā)器名 [BEFORE|AFTER][INSERT|DELETE|UPDATE{OF}]ON[模式名.]表名 [FOREACHROW--包含該選項時為行級觸發(fā)器，不包含時為語句級觸發(fā)器 [WHEN字句]]--觸發(fā)條件，僅在行級觸發(fā)器中使用 [DECLARE聲明變量、常量等] BEGIN <觸發(fā)操作> END;346.8觸發(fā)器刪除觸發(fā)器DROPTRIGGERtrigger_emp_secure;356.9程序包程序包(PACKAGE，簡稱包)是一組相關(guān)過程、函數(shù)、變量、常量和游標(biāo)等PL/SQL程序設(shè)計元素的組合，作為一個完整的單元存儲在數(shù)據(jù)庫中，用名稱來標(biāo)識程序包。它具有面向?qū)ο蟪绦蛟O(shè)計語言的特點，是對這些PL/SQL程序設(shè)計元素的封裝。程序包類似于C#和JAVA等面向?qū)ο笳Z言中的類，其中變量相當(dāng)于類中的成員變量，而存儲過程和函數(shù)相當(dāng)于類方法。把相關(guān)的模塊歸類成為程序包，可使開發(fā)人員利用面向?qū)ο蟮姆椒ㄟM(jìn)行存儲過程的開發(fā)，從而提高系統(tǒng)性能。366.9程序包創(chuàng)建程序包一個程序包由2個獨立的部分組成：包說明和包主體。包說明部分是程序包與應(yīng)用程序之間的接口，僅聲明程序包內(nèi)數(shù)據(jù)類型、變量、常量、游標(biāo)、存儲過程、函數(shù)和異常錯誤處理等元素，這些元素為包的公有元素。包主體則是包說明部分的具體實現(xiàn)，它定義了包說明部分所聲明的游標(biāo)、存儲過程、函數(shù)等的具體實現(xiàn)。在包主體中還可以聲明程序包的私有元素。376.9程序包包說明部分的創(chuàng)建語法如下：CREATE[ORREPLACE]PACKAGE<包名> [AUTHID{CURRENT_USER|DEFINER}]{IS|AS} [公有數(shù)據(jù)類型定義[公有數(shù)據(jù)類型定義]…] [公有游標(biāo)聲明[公有游標(biāo)聲明]…] [公有變量、常量聲明[公有變量、常量聲明]…] [公有函數(shù)聲明[公有函數(shù)聲明]…] [公有過程聲明[公有過程聲明]…]END[包名];386.9程序包包主體部分的創(chuàng)建語法如下：CREATE[ORREPLACE]PACKAGEBODY<包名>{IS|AS} [私有數(shù)據(jù)類型定義[私有數(shù)據(jù)類型定義]…] [私有變量、常量聲明[私有變量、常量聲明]…] [私有異常錯誤聲明[私有異常錯誤聲明]…] [私有函數(shù)聲明和定義[私有函數(shù)聲明和定義]…] [私有函過程聲明和定義[私有函過程聲明和定義]…] [公有游標(biāo)定義[公有游標(biāo)定義]…] [公有函數(shù)定義[公有函數(shù)定義]…] [公有過程定義[公有過程定義]…]BEGIN

執(zhí)行部分(初始化部分)END[包名];396.9程序包程序包的調(diào)用語法為：包名.變量名(常量名)包名.游標(biāo)名包名.函數(shù)名(過程名)刪除程序包DROPPACKAGEemp_pkg;40小結(jié)PL/SQL也是一種程序語言，稱為過程化SQL語言（ProceduralLanguage/SQL）。PL/SQL是Oracle數(shù)據(jù)庫對SQ