《Oracle數(shù)據(jù)庫應(yīng)用開發(fā)及實(shí)踐》課件06-第6章 PL SQL基礎(chǔ)

-1-本章目標(biāo)了解PL/SQL的功能掌握PL/SQL塊結(jié)構(gòu)的組成部分掌握PL/SQL塊結(jié)構(gòu)的分類掌握PL/SQL中的基本數(shù)據(jù)類型和復(fù)合數(shù)據(jù)類型掌握PL/SQL的運(yùn)算符的使用掌握PL/SQL程序控制語句的使用掌握預(yù)定義異常、非預(yù)定義異常、自定義異常的定義和使用-2-PL/SQL簡(jiǎn)介PL/SQL是ProcedureLanguage&StructuredQueryLanguage的縮寫它是對(duì)SQL的擴(kuò)展PL/SQL中存在過程構(gòu)造和數(shù)據(jù)庫訪問在Oracle服務(wù)器和客戶機(jī)應(yīng)用程序中使用支持大型對(duì)象和集合-3-PL/SQL概述PL/SQL由兩部分組成：一部分是數(shù)據(jù)庫引擎部分；另一部分是可嵌入到許多產(chǎn)品工具中的獨(dú)立引擎。PL/SQL有如下優(yōu)點(diǎn)：高性能且基于事務(wù)處理的語言，能運(yùn)行在任何ORACLE環(huán)境中，支持所有數(shù)據(jù)處理命令。支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時(shí)支持所有ORACLE對(duì)象類型PL/SQL塊可以被命名和存儲(chǔ)在ORACLE服務(wù)器中，同時(shí)也能被其他的PL/SQL程序或SQL命令調(diào)用，任何客戶/服務(wù)器工具都能訪問PL/SQL程序，具有很好的可重用性。是以整個(gè)語句塊發(fā)給服務(wù)器，降低了網(wǎng)絡(luò)擁擠。PL/SQL可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運(yùn)行。-4-PL/SQL結(jié)構(gòu)一個(gè)完整的PL/SQL程序的總體結(jié)構(gòu)如下所示：

DECLARE/*聲明部分：在此聲明PL/SQL用到的變量，類型及游標(biāo)，以及局部的存儲(chǔ)過程和函數(shù)*/BEGIN/**執(zhí)行部分：過程及SQL語句，即程序的主要部分*/EXCEPTION/**異常處理部分：錯(cuò)誤處理*/END;注意:使用dbms_output包輸出信息時(shí)，需將使用setserveroutputon；語句將SQL*Plus環(huán)境變量serveroutput設(shè)置為ON。

-5-PL/SQL塊分類使用PL/SQL進(jìn)行應(yīng)用開發(fā)時(shí)，可以將PL/SQL塊劃分為三部分：匿名塊：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次。子程序：存儲(chǔ)在數(shù)據(jù)庫中的存儲(chǔ)過程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫上建立好后可以在其它程序中調(diào)用它們。觸發(fā)器：當(dāng)數(shù)據(jù)庫發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序-6-匿名塊匿名塊是指沒有名稱的PL/SQL塊，匿名塊即可以內(nèi)嵌到應(yīng)用程序（例如Pro*C/C++）中，也可以在交互環(huán)境（例如SQL*Plus）中使用。示例代碼如下：DECLAREv_countnumber;BEGINSELECTCOUNT(*)intov_countFROMempWHEREdeptno=&deptno;dbms_output.put_line('總?cè)藬?shù)為：'||v_count);END;-7-子程序子程序是指存儲(chǔ)在數(shù)據(jù)庫中或者客戶端的PL/SQL過程、函數(shù)或包。通過將業(yè)務(wù)邏輯集成到PL/SQL子程序，不僅可以簡(jiǎn)化客戶端程序的開發(fā)和維護(hù)，而且還可以提高應(yīng)用程序的性能。示例代碼如下：CREATEORREPLACEPROCEDUREupdate_emp(noNUMBER)ISBEGINUPDATEempSETsal=sal*1.1WHEREempno=no;END;-8-觸發(fā)器觸發(fā)器是指被隱含執(zhí)行的PL/SQL塊。觸發(fā)器與一個(gè)表或數(shù)據(jù)庫事件聯(lián)系在一起的，當(dāng)一個(gè)觸發(fā)器事件發(fā)生時(shí)，定義在表上的PL/SQL語句塊被執(zhí)行。-9-注釋注釋用于說明單行代碼或者多行代碼的作用，從而提高PL/SQL塊的可讀性。當(dāng)執(zhí)行PL/SQL塊時(shí)，PL/SQL編譯器會(huì)忽略注釋。注釋包括單行注釋和多行注釋。單行注釋：放置在某一行上的注釋文本。使用兩個(gè)破折號(hào)（--）可以指定單行注釋。多行注釋：多行注釋是指分布到多行上的注釋文本，并且其主要作用是說明一段代碼的作用。使用/*…*/可以指定多行注釋-10-基本數(shù)據(jù)類型編寫PL/SQL時(shí)，為了臨時(shí)存儲(chǔ)數(shù)據(jù)，需要定義變量和常量。當(dāng)定義變量、常量和參數(shù)時(shí)，需要指定合適的PL/SQL數(shù)據(jù)類型。-11-標(biāo)量變量標(biāo)量變量是指只能存放單個(gè)數(shù)值的變量。當(dāng)定義標(biāo)量變量時(shí)，需要指定標(biāo)量數(shù)據(jù)類型。常用的標(biāo)量數(shù)據(jù)類型有VARCHAR、CHAR、NUMBER、DATE、BOOLEAN、LONG和BINARYINTEGER等。PL/SQL中的標(biāo)識(shí)符定義與SQL的標(biāo)識(shí)符定義的要求相同只能使用A~Z、A~Z、0~9、_、$和#，如果使用其他字符，需用雙引號(hào)引住標(biāo)識(shí)符名不能超過30字符第一個(gè)字符必須為字母標(biāo)識(shí)符不區(qū)分大小寫不能是SQL保留字-12-使用%TYPE定義變量時(shí)，如果其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個(gè)列的數(shù)據(jù)類型相同，這時(shí)可以使用%TYPE，該屬性會(huì)自動(dòng)根據(jù)表列或其他變量的類型和長(zhǎng)度定義新變量。使用%TYPE特性的優(yōu)點(diǎn)在于：所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型及長(zhǎng)度可以不必知道所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實(shí)時(shí)改變提高PL/SQL塊的效率和健壯性-13-記錄類型ORACLE在PL/SQL中除了提供象前面介紹的各種類型外，還提供一種稱為復(fù)合類型的數(shù)據(jù)類型，在PL/SQL中使用復(fù)合變量可以以特定結(jié)構(gòu)存儲(chǔ)多個(gè)數(shù)值，PL/SQL記錄類型是最常用的復(fù)合類型之一。記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來，它主要用于處理單行多列數(shù)據(jù)。記錄類型必須包括至少一個(gè)標(biāo)量型或RECORD數(shù)據(jù)類型的成員，稱為RECORD的域(FIELD)，其作用是存放互不相同但邏輯相關(guān)的信息。當(dāng)使用PL/SQL記錄是，既可以自定義記錄類型和記錄變量，也可以使用%ROWTYPE屬性定義記錄變量。-14-%ROWTYPE記錄類型使用%TYPE可以使變量獲得字段的數(shù)據(jù)類型使用%ROWTYPE可以使變量獲得整個(gè)記錄的數(shù)據(jù)類型。%ROWTYPE屬性用于基于表或視圖定義記錄變量。示例: 通過定義雇員表的記錄類型變量，演示%ROWTYPE的使用。-15-自定義PL/SQL記錄類型自定義記錄類型的語法格式如下：示例： 使用替換變量輸入員工號(hào)，輸出當(dāng)前員工的相關(guān)信息，其中員工的信息使用記錄類型進(jìn)行存儲(chǔ)TYPErecord_typeISRECORD(Field1type[NOTNULL][:=exp1],Field2type[NOTNULL][:=exp2],......Fieldntype[NOTNULL][:=expn]);record_type是新建的記錄類型；Field用于定義記錄成員；type指明Filed的數(shù)據(jù)類型。-16-集合類型ORACLE在PL/SQL中復(fù)合類型有記錄類型和集合類型。常用的集合類型如下：PL/SQL一維表PL/SQL多維表嵌套表可變數(shù)組集合方法-17-PL/SQL一維表PL/SQL表，或者稱為索引表(index-table)，是在PL/SQL中能夠模仿數(shù)組的非永久表。語法格式如下：type_name是創(chuàng)建的PL/SQL表類型的名稱；type用于指定PL/SQL表的數(shù)據(jù)類型；NOTNULL表示不允許引用null元素；key_type用于指定PL/SQL表下標(biāo)的數(shù)據(jù)類型，可以是BINARY_INTEGER、PLS_INTEGER或VARCHAR2。TYPEtype_nameISTABLEOFtype[NOTNULL]INDEXBYkey_type;-18-PL/SQL多維表在定義PL/SQL表類型時(shí)，還可以為其指定數(shù)據(jù)類型為記錄類型，這樣定義的表類型就相當(dāng)于多維數(shù)組，即多維表結(jié)構(gòu)，也稱記錄表結(jié)構(gòu)。語法格式如下：tableName是表名；type用于指定PL/SQL表的數(shù)據(jù)類型；NOTNULL表示不允許引用null元素；key_type用于指定PL/SQL表下標(biāo)的數(shù)據(jù)類型，可以是BINARY_INTEGER、PLS_INTEGER或VARCHAR2。TYPEtype_nameISTABLEOFtableName%ROWTYPE[NOTNULL]INDEXBYkey_type;-19-可變數(shù)組可變數(shù)組（VARRAY數(shù)組）也是一種集合數(shù)據(jù)類型，其可以作為表列的數(shù)據(jù)類型來使用，該集合的下標(biāo)從1開始，并且元素個(gè)數(shù)是有限制的。語法格式如下：type_name是創(chuàng)建的PL/SQL表類型的名稱；type用于指定PL/SQL表的數(shù)據(jù)類型；NOTNULL表示不允許引用null元素。TYPEtype_nameISVARRAY(size)OFtype[NOTNULL];-20-集合方法方法名功能說明EXISTS(n)判斷指定的元素n在當(dāng)前集合中是否存在COUNT返回當(dāng)前集合中元素的總個(gè)數(shù)FIRST返回當(dāng)前集合中第一個(gè)元素的下標(biāo)值LAST返回當(dāng)前集合中最后一個(gè)元素的下標(biāo)值LIMIT返回VARRAY集合變量所允許的最大元素個(gè)數(shù)PRIOR(n)返回當(dāng)前集合中第n個(gè)元素的前一個(gè)元素的值，如果該元素是第一個(gè)元素，則返回nullNEXT(n)返回當(dāng)前集合中第n個(gè)元素的后一個(gè)元素的值，如果該元素是最后一個(gè)元素，則返回nullTRIM(n)用于從集合尾部刪除n個(gè)元素；如果省略n，則從集合變量尾部刪除一個(gè)元素。該方法只適用于嵌套表和VARRAYEXTEND(n,m)用于為集合變量增加n個(gè)與第m個(gè)元素相同的值；如果省略m，則為集合變量增加n個(gè)值為null的元素。該方法只適用于嵌套表和VARRAYDELETE(m,n)用于刪除集合中m~n之間的所有元素；如果省略m，則刪除集合變量中的第n個(gè)元素；如果m和n都省略，則刪除集合變量中的所有元素。該方法只適用于嵌套表和PL/SQL表-21-運(yùn)算符與表達(dá)式PL/SQL中常用的操作符分為以下幾類：算術(shù)運(yùn)算符關(guān)系運(yùn)算符邏輯運(yùn)算符-22-算數(shù)運(yùn)算符常用的算數(shù)運(yùn)算符如下：運(yùn)算符作用+加號(hào)-減號(hào)*乘號(hào)/除號(hào)**乘方:=賦值號(hào)||字符（串）連接符=>關(guān)系號(hào)-23-關(guān)系運(yùn)算符常用的關(guān)系運(yùn)算符如下：運(yùn)算符作用=等于<>,!=,~=,^=不等于<小于>大于<=小于等于>=大于等于-24-邏輯運(yùn)算符常用的邏輯運(yùn)算符如下：運(yùn)算符作用ISNULL是否是空置BETWEENAND取值介于兩者之間IN取值是指定的列表值之一AND邏輯與OR邏輯或NOT邏輯非，取反操作-25-流程控制語句PL/SQL不僅可以嵌入SQL語句，有完善的數(shù)據(jù)類型和運(yùn)算符，還可以使用如下流程控制語句。條件分支語句CASE語句循環(huán)語句GOTO語句-26-條件分支語句PL/SQL中提供了3種條件分支語句：IF-THENIF-THEN-ELSEIF-THEN-ELSIF-27-IF-THEN語句IF-THEN語句用于執(zhí)行簡(jiǎn)單的條件判斷。如果布爾表達(dá)式返回為TRUE，則會(huì)執(zhí)行THEN后的相應(yīng)操作；如果布爾表達(dá)式返回為FALSE或NULL，則退出條件分支語句。其語法結(jié)構(gòu)如下：示例代碼如下：

IF<布爾表達(dá)式>THENPL/SQL和SQL語句ENDIF;DECLAREv_num1NUMBER;v_num2NUMBER;BEGINv_num1:=&n1;v_num2:=&n2;IFv_num1>v_num2THENdbms_output.put_line(v_num1||'>'||v_num2);ENDIF;END;-28-IF-THEN-ELSEIF-THEN-ELSE是二重分支語句。當(dāng)使用二重條件分支時(shí)，如果布爾表達(dá)式返回為TRUE，則會(huì)執(zhí)行THEN后的相應(yīng)操作；如果布爾表達(dá)式返回為FALSE或NULL，則執(zhí)行ELSE后的相應(yīng)操作。其語法結(jié)構(gòu)如下：

示例代碼如下：IF<布爾表達(dá)式>THENPL/SQL和SQL語句ENDIF;DECLAREv_num1NUMBER;v_num2NUMBER;BEGINv_num1:=&n1;v_num2:=&n2;IFv_num1>v_num2THENdbms_output.put_line(v_num1||'>'||v_num2);ELSEdbms_output.put_line(v_num1||'<='||v_num2);ENDIF;END;-29-IF-THEN-ELSIFIF-THEN-ELSIF是多重分支語句。當(dāng)使用多重條件分支時(shí)，如果第一個(gè)布爾表達(dá)式返回為TRUE，則會(huì)執(zhí)行第一個(gè)THEN后的操作；如果第一個(gè)布爾表達(dá)式返回為FALSE或NULL，則會(huì)判斷第二個(gè)布爾表達(dá)式（ELSIF）；如果第二個(gè)布爾表達(dá)式返回為TRUE，則會(huì)執(zhí)行第二個(gè)THEN后的操作；依此類推，如果所有條件都為FALSE或NULL，則會(huì)執(zhí)行ELSE后的操作。其語法結(jié)構(gòu)如下：IF<布爾表達(dá)式>THENPL/SQL和SQL語句ENDIF;-30-CASE語句在ORACLE11g中，開發(fā)人員可以使用CASE語句實(shí)現(xiàn)多重分支語句。相對(duì)于IF-THEN-ELSIF，該語句更加簡(jiǎn)潔，且執(zhí)行效率更高。其語法結(jié)構(gòu)如下：

CASEselectorWHENexpression1THENresult1;WHENexpression2THENresult2;WHENexpressionNTHENresultN;[ELSEresultN+1]END;selector是條件選擇符；expression是指定的條件表達(dá)式-31-簡(jiǎn)單循環(huán)在PL/SQL中，可以使用LOOP語句實(shí)現(xiàn)簡(jiǎn)單的循環(huán)操作。其語法結(jié)構(gòu)如下：LOOPPL/SQL和SQL語句

EXITWHEN<布爾表達(dá)式>/*條件滿足，退出循環(huán)語句*/ENDLOOP;當(dāng)使用LOOP語句進(jìn)行循環(huán)操作時(shí)，無論條件是否滿足，語句塊至少被執(zhí)行一次，并且當(dāng)布爾表達(dá)式為TRUE時(shí)會(huì)退出循環(huán)。-32-WHILE循環(huán)WHILE循環(huán)用于根據(jù)布爾表達(dá)式的返回值確定是否要執(zhí)行循環(huán)體的語句，這種循環(huán)以WHILE…LOOP開始，以ENDLOOP結(jié)束，其語法結(jié)構(gòu)如下：WHILE<布爾表達(dá)式>LOOPPL/SQL和SQL語句;ENDLOOP;在使用WHILE語句實(shí)現(xiàn)循環(huán)時(shí)，當(dāng)布爾表達(dá)式返回為TRUE時(shí)，會(huì)執(zhí)行循環(huán)體的語句；當(dāng)布爾表達(dá)式返回為FALSE或NULL時(shí)，會(huì)退出循環(huán)并執(zhí)行ENDLOOP后的操作。-33-FOR循環(huán)ORACLE還提供了一種FOR循環(huán)結(jié)構(gòu)，使用FOR循環(huán)結(jié)構(gòu)可以不需定義循環(huán)控制變量，ORACLE會(huì)隱含定義循環(huán)控制變量。其語法結(jié)構(gòu)如下：FOR循環(huán)控制變量IN[REVERSE]下限..上限LOOPPL/SQL和SQL語句;ENDLOOP;在使用FOR語句實(shí)現(xiàn)循環(huán)時(shí)，每循環(huán)一次，循環(huán)變量自動(dòng)加1；如果指定關(guān)鍵字REVERSE，循環(huán)變量則自動(dòng)減1；跟在INREVERSE后面的用于標(biāo)注上限和下限