第7章PL/SQL編程PL/SQL編程7.1PL/SQL語言基礎(chǔ)7.2PL/SQL程序結(jié)構(gòu)7.3異常處理7.1PL/SQL語言基礎(chǔ)7.1.1PL/SQL語言優(yōu)點PL/SQL是一種高效的事務(wù)處理語言，它具備以下優(yōu)點：PL/SQL是一種高性能的基于事務(wù)處理的語言，能運行在任何Oracle環(huán)境中，支持所有數(shù)據(jù)處理命令。通過使用PL/SQL程序單元處理SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素。PL/SQL支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時支持所有Oracle對象類型。PL/SQL塊可以被命名并存儲在Oracle服務(wù)器中，同時也能被其他PL/SQL程序或SQL命令調(diào)用，任何客戶/服務(wù)器工具都能訪問PL/SQL程序，具有很好的可重用性。可以使用Oracle數(shù)據(jù)工具管理PL/SQL程序的安全性?？梢允跈?quán)或撤銷數(shù)據(jù)庫其他用戶訪問PL/SQL程序權(quán)限。7.1PL/SQL語言基礎(chǔ)PL/SQL代碼可以使用任何ASCII文本編輯器編寫，所以對任何能夠運行Oracle的操作系統(tǒng)都是非常便利的。對于SQL，Oracle必須在同一時間處理每一條SQL語句，在網(wǎng)絡(luò)環(huán)境下這就意味著每一個獨立的調(diào)用都必須被Oracle服務(wù)器處理，從而占用大量的服務(wù)器時間，導(dǎo)致網(wǎng)絡(luò)擁擠。而PL/SQL以整個語句塊形式發(fā)給服務(wù)器，可以減少網(wǎng)絡(luò)擁擠。7.1PL/SQL語言基礎(chǔ)7.1.2PL/SQL的基本結(jié)構(gòu)一個PL/SQL程序包含一個或多個邏輯塊，每個塊都可以劃分為三個部分：聲明部分（用DECLARE開頭）、執(zhí)行部分（以BEGIN開頭）和異常處理部分（以EXCEPTION開頭）

1、PL/SQL基本結(jié)構(gòu)如下：

DECLARE

聲明部分

BEGIN

執(zhí)行部分

EXCEPTION

異常處理部分

END；7.1PL/SQL語言基礎(chǔ)其中：聲明部分：定義變量和常量的數(shù)據(jù)類型和初始值，以及程序中要使用的函數(shù)、游標、異常處理名稱等。如果沒有需要聲明的內(nèi)容，可以省略這一部分。PL/SQL中要使用的所有定義都必須在這一部分進行集中定義。執(zhí)行部分：此部分是PL/SQL塊中的指令部分，所有的可執(zhí)行語句都放在這一部分，包括對數(shù)據(jù)庫的操作語句和各種流程控制語句，也可以嵌套其他的PL/SQL塊。執(zhí)行部分至少包含一條可執(zhí)行語句。異常處理部分：包含在執(zhí)行部分中，以關(guān)鍵字EXCEPTION開始，到END結(jié)束。當程序檢測到錯誤而產(chǎn)生異常時，就轉(zhuǎn)到由EXCEPTION標識的部分執(zhí)行異常處理程序。這部分是可選的，利用異常處理可以提高PL/SQL程序的健壯性。7.1PL/SQL語言基礎(chǔ)2、

PL/SQL的特殊結(jié)構(gòu)

<<BLOCK1>>/*以名字標識PL/SQL塊*/DECLARE

聲明部分BEGIN

執(zhí)行部分EXCEPTION

異常處理部分END<<BLOCK1>>；DECLARE

主塊聲明部分BEGIN

主塊執(zhí)行部分

DECLARE

子塊聲明部分

BEGIN

子塊執(zhí)行部分

EXCEPTION

子塊異常處理部分

END；EXCEPTION

主塊異常處理部分END；帶命名塊的PL/SQL塊嵌套結(jié)構(gòu)的PL/SQL塊7.1PL/SQL語言基礎(chǔ)7.1.3PL/SQL的字符集1、合法字符，包括：大寫字母A-Z和小寫字母a-z數(shù)字0-9符號()、+、-、*、/、<、>、=、！、~、^、;、:、.、’、@、”、#、$、_、{}、[]、?制表符、空格符、回車符等非顯示的間空符號2、操作符，包括：算術(shù)操作符關(guān)系操作符邏輯操作符3、其他字符7.1PL/SQL語言基礎(chǔ)7.1.4PL/SQL基本語法1、常量與變量的聲明，語法格式如下：

<常（變）量名>[CONSTANT]<數(shù)據(jù)類型>[NOTNULL][(寬度):=<初始值>]

【例7.1】幾個聲明的例子①聲明一個長度為10B的變量count，初值為1，類型為VARCHAR２。

cvarchar2(10):='1';②聲明一個NUMBER數(shù)據(jù)類型的常量c_limit，并賦予初值5000.00。

c_limitconstantnumber(8.2):=5000.00;③聲明一個NUMBER數(shù)據(jù)類型的常量c_area，并用表達式給它賦初值。

c_areaconstantnumber(8.5):=3.14159*3**2；7.1PL/SQL語言基礎(chǔ)2、作用域變量的作用域指變量的有效使用范圍，它從變量聲明開始，直到當前程序塊結(jié)束，只有在其作用域范圍內(nèi)，程序才能使用該變量，否則將導(dǎo)致編譯錯誤。在同一程序塊中，不允許聲明兩個同名變量，但在不同程序塊內(nèi)可以聲明兩個同名變量。在程序塊內(nèi)聲明的變量稱為局部變量，在程序塊外聲明的變量稱為全局變量。引用程序塊內(nèi)的局部變量不需要加限定詞，而引用塊外的全局變量需要加限定詞（即父塊的名稱）。子塊中可以引用父塊的變量，但是父塊中不能引用子塊的變量。如果父塊與子塊具有相同的變量名，在子塊中引用這個變量時若是不加限定詞則引用的是子塊內(nèi)的局部變量。若兩個塊不是父子關(guān)系，則他們之間的變量不能相互引用。7.1PL/SQL語言基礎(chǔ)（3）變量的屬性性變量有名字和和數(shù)據(jù)類型兩兩個屬性。變變量名用于標標識該變量，，變量的數(shù)據(jù)據(jù)類型確定了了該變量存放放值的格式及及允許的運算算。在PL/SQL中，使用“%”表示屬性提示示符，后面緊緊跟屬性名。。%TYPE和%ROWTYPE是PL/SQL中兩個特殊的的屬性，常用用來定義變量量，使被定義義變量的數(shù)據(jù)據(jù)類型與一個個已定義變量量（或參照表表中的記錄））的數(shù)據(jù)類型型相一致。%TYPE使用%TYPE屬性定義變量量時，被定義義變量的數(shù)據(jù)據(jù)類型與一個個已經(jīng)定義了了的變量的數(shù)數(shù)據(jù)類型相一一致，當被參參照的變量數(shù)數(shù)據(jù)類型改變變后，新定義義變量的數(shù)據(jù)據(jù)類型也隨之之改變。當無無法準確知道道被參照變量量的數(shù)據(jù)類型型時，只能采采用這種定義義方法。定義義格式為：<新變量名><被參照表.被參照列>%TYPE7.1PL/SQL語言基礎(chǔ)【例7.1】聲明三個變量量“v_班級名稱”，，“v_班級人數(shù)”，，“v_班主任”，其其數(shù)據(jù)類型分分別對應(yīng)“班班級表”中相相應(yīng)字段的數(shù)數(shù)據(jù)類型。根根據(jù)輸入的班班級代碼，輸輸出對應(yīng)的班班級名稱、人人數(shù)和班主任任信息。代碼碼如下：DECLAREv_班級名稱班級表.班級名稱%TYPE;v_班級人數(shù)班班級表.班級人數(shù)%TYPE;v_班主任班級級表.班主任%TYPE;BEGINSELECT班級名稱,班級人數(shù),班主任intov_班級名稱,v_班級人數(shù),v_班主任FROM班級表WHERE班級代碼='&班級代碼';DBMS_OUTPUT.PUT_LINE('班級名稱：'||v_班級名稱);DBMS_OUTPUT.PUT_LINE('班級人數(shù)：'||v_班級人數(shù));DBMS_OUTPUT.PUT_LINE('班主任：'||v_班主任);END;語句：Setserverouton7.1PL/SQL語言基礎(chǔ)%ROWTYPE使用%ROWTYPE屬性能夠定義義記錄變量，，使得被定義義的記錄成員員個數(shù)、名稱稱、數(shù)據(jù)類型型與已定義的的表或視圖中中列的個數(shù)、、名稱和數(shù)據(jù)據(jù)類型完全相相同。這種定定義方式，可可以定義一個個表，當被參參照表中列及及數(shù)據(jù)類型改改變時，新定定義表中的列列及數(shù)據(jù)類型型自動改變。。當一個表有較較多列時，使使用%ROWTYPE定義記錄，比比使用%TYPE要方便，并且且不容易出錯錯。定義格式式為：<新表名><被參照表>%ROWTYPE7.1PL/SQL語言基礎(chǔ)【例7.2】聲明一個變量量“v_班級”，與班班級表具有相相同的數(shù)據(jù)類類型。根據(jù)輸輸入的班級代代碼，輸出對對應(yīng)的班級名名稱、人數(shù)和和班主任信息息。代碼如下下：DECLAREv_班級班級表表%ROWTYPE;BEGINSELECT*intov_班級FROM班級表WHERE班級代碼='&班級代碼';DBMS_OUTPUT.PUT_LINE('班級名稱：'||v_班級.班級名稱);DBMS_OUTPUT.PUT_LINE('班級人數(shù)：'||v_班級.班級人數(shù));DBMS_OUTPUT.PUT_LINE('班主任：'||v_班級.班主任);END;7.1PL/SQL語言基礎(chǔ)3、數(shù)據(jù)類型標量類型LOB型數(shù)據(jù)類型定義義的運算符7.1PL/SQL語言基礎(chǔ)【例7.3】】聲明一個TIMESTAMP類型型的變量，并并為它賦值。。代碼如下：：SETSERVEROUTONDECLAREcheckoutTIMESTAMP(2);BEGINcheckout:='10-6月月-07075';DBMS_OUTPUT.PUT_LINE(checkout);END;7.2PL/SQL程序結(jié)構(gòu)7.2.1選擇結(jié)構(gòu)選擇結(jié)構(gòu)又稱稱條件控制，，通過先測試試一個條件，，根據(jù)測試結(jié)結(jié)果選擇運行行不同的語句句段。選擇結(jié)結(jié)構(gòu)允許嵌套套執(zhí)行。選擇結(jié)構(gòu)常用用到的4種語法格式：：IF…THEN…ENDIF結(jié)構(gòu)IF……THEN…ELSE……ENDIF結(jié)構(gòu)IF……THEN…ELSIF…ENDIF結(jié)構(gòu)CASE結(jié)構(gòu)7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.4】查查找““ASP.NET程程序設(shè)設(shè)計””課程程，若若是““備注注”信信息為為“C#””，將將其改改為““J#”，，否則則將““備注注”信信息置置空。。7.2PL/SQL程序結(jié)結(jié)構(gòu)DECLAREv_課課程課課程表表%ROWTYPE;BEGINSELECT*INTOv_課課程FROM課課程程表WHERE課課程名名='ASP.NET程程序設(shè)設(shè)計';IFv_課程程.備備注='C#'THENUPDATE課課程程表SET備備注='J#'WHERE課課程名名=v_課課程.課程程名;ELSEUPDATE課課程程表SET備備注=''WHERE課課程名名=v_課課程.課程程名;ENDIF;DBMS_OUTPUT.PUT_LINE('更更新完完成!');END;試一試試：數(shù)數(shù)據(jù)被被修改改了嗎嗎？為為什么么？作業(yè)一一、、用你你熟悉悉的方方法完完成成該問問題，，比比較兩兩種方方法。。7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.5】學(xué)生成成績按按照分分數(shù)段段分為為“優(yōu)優(yōu)秀””、““良好好”、、“合合格””、““不及及格””4種等級級，根根據(jù)等等級的的不同同，可可以輸輸出對對應(yīng)的的分數(shù)數(shù)段，，代碼碼如下下：DECLAREv_resultVARCHAR2(20):='良好';BEGINIFv_result='優(yōu)秀'THENDBMS_OUTPUT.PUT_LINE('90~100');ELSIFv_result='良好'THENDBMS_OUTPUT.PUT_LINE('80~89');ELSIFv_result='合格'THENDBMS_OUTPUT.PUT_LINE('60~79');ELSIFv_result='不及格格'THENDBMS_OUTPUT.PUT_LINE('<60');ELSEDBMS_OUTPUT.PUT_LINE('不存在在該等等級');ENDIF;END;7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.6】從“成成績表表”中中根據(jù)據(jù)學(xué)生生的學(xué)學(xué)號和和課程程號查查找學(xué)學(xué)生某某門課課程的的成績績，并并輸出出成績績的等等級。。采用用CASE語法格格式：：7.2PL/SQL程序結(jié)結(jié)構(gòu)DECLAREv_scoreNUMBER(3);BEGINSELECT成績intov_scoreFROM成績表表WHERE學(xué)號=&學(xué)號and課程號號=&課程號號;CASEWHEN(v_score>=90andv_score<=100)THENDBMS_OUTPUT.PUT_LINE('成績：：'||v_score||'優(yōu)秀');WHEN(v_score>=80andv_score<90)THENDBMS_OUTPUT.PUT_LINE('成績：：'||v_score||'良好');WHEN(v_score>=60andv_score<80)THENDBMS_OUTPUT.PUT_LINE('成績：：'||v_score||'合格');WHEN(v_score>=0andv_score<60)THENDBMS_OUTPUT.PUT_LINE('成績：：'||v_score||'不及格格');ELSEDBMS_OUTPUT.PUT_LINE('成績：：'||v_score||'成績異異常');ENDCASE;END;7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.7】使用CASE語句完完成例例7.5程序。。要求求使用用CASE語句第第二種種語法法格式式實現(xiàn)現(xiàn)，代代碼如如下：：DECLAREv_resultVARCHAR2(20):='良好';BEGINCASEv_resultWHEN'優(yōu)秀'THENDBMS_OUTPUT.PUT_LINE('90~100');WHEN'良好'THENDBMS_OUTPUT.PUT_LINE('80~89');WHEN'合格'THENDBMS_OUTPUT.PUT_LINE('60~79');WHEN'不及格格'THENDBMS_OUTPUT.PUT_LINE('<60');ELSEDBMS_OUTPUT.PUT_LINE('不存在在該等等級');ENDCASE;END;7.2PL/SQL程序結(jié)結(jié)構(gòu)7.2.2循環(huán)結(jié)結(jié)構(gòu)循環(huán)結(jié)結(jié)構(gòu)可可以對對一段段語句句反復(fù)復(fù)執(zhí)行行，直直到滿滿足某某一條條件件，跳跳出循循環(huán)。循環(huán)結(jié)結(jié)構(gòu)常常用到到的3種語法法格式式：LOOP……ENDLOOP結(jié)構(gòu)WHILE…LOOP……ENDLOOP結(jié)構(gòu)FOR…LOOP……ENDLOOP結(jié)構(gòu)7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.8】利用LOOP循環(huán)和和EXIT語句計計算1至100的自然然數(shù)之之和。。代碼碼如下下：DECLAREv_iNUMBER:=1;v_sNUMBER:=0;BEGINLOOPEXITWHENv_i>100;v_s:=v_s+v_i;v_i:=v_i+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然數(shù)數(shù)之和和：'||v_s);END;7.2PL/SQL程序結(jié)結(jié)構(gòu)【例7.9】利用WHILE循環(huán)計計算1至100的自然然數(shù)之之和。。代碼碼如下下：DECLAREv_iNUMBER:=0;v_sNUMBER:=0;BEGINWHILEv_i<=100LOOPv_s:=v_s+v_i;v_i:=v_i+1;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然數(shù)之和和：'||v_s);END;7.2PL/SQL程序結(jié)構(gòu)【例7.10】利用用FOR循循環(huán)語句計計算1至100的自自然數(shù)之和和。代碼如如下：DECLAREv_sNUMBER:=0;BEGINFORv_iIN1..100LOOPv_s:=v_s+v_i;ENDLOOP;DBMS_OUTPUT.PUT_LINE('1~100自然然數(shù)之和：：'||v_s);END;思考題：如如何利用循循環(huán)語句生生成數(shù)據(jù)？？7.2PL/SQL程序結(jié)構(gòu)7.2.3順序結(jié)構(gòu)順序結(jié)構(gòu)是是一種最簡簡單的控制制結(jié)構(gòu)，程程序按語句句順序依次次執(zhí)行。有有時為了打打破執(zhí)行順順序，將當當前程序跳跳轉(zhuǎn)到另一一個地方繼繼續(xù)運行，，需要借助助GOTO語句。GOTO語句是一種種無條件跳跳轉(zhuǎn)語句，，能夠?qū)⒄Z語句無條件件地跳轉(zhuǎn)到到一個用標標簽指定的的語句，其其目的地必必須與GOTO語句在同一一程序塊中中或更高層層次（比如如父塊中））中。但過過多的使用用GOTO語句會導(dǎo)致致程序復(fù)雜雜，破壞程程序的結(jié)構(gòu)構(gòu)。因此，，GOTO語句一般只只用在從一一個PL/SQL塊中跳轉(zhuǎn)到到異常處理理語句的前前面7.2PL/SQL程序結(jié)構(gòu)【例7.11】】利用GOTO語句計算1至100的自然數(shù)之之和。代碼碼如下：DECLAREv_iNUMBER:=0;v_sNUMBER:=0;BEGIN<<label_1>>v_i:=v_i+1;IFv_i<=100THENv_s:=v_s+v_i;GOTOlabel_1;ENDIF;DBMS_OUTPUT.PUT_LINE('1~100自然數(shù)之和和：'||v_s);END;7.2PL/SQL程序結(jié)構(gòu)7.2.4NULL結(jié)構(gòu)NULL語句是一個個空操作的的執(zhí)行語句句，通過執(zhí)執(zhí)行NULL語句，可以以直接將控控制傳遞到到下一條語語句，以提提高PL/SQL塊的可讀性性?！纠?.12】】根據(jù)輸入的的學(xué)生學(xué)號號和課程號號，從“成成績表”中中查找學(xué)生生的課程成成績，如果果成績低于于60分，將“是是否補考””字段的值值修改為““是”，否否則不執(zhí)行行任何操作作。7.2PL/SQL程序結(jié)構(gòu)DECLAREv_學(xué)號成成績表.學(xué)號%TYPE;v_課程號號成績表表.課程號號%TYPE;v_成績成成績表.成績%TYPE;BEGINv_學(xué)號:='&學(xué)號';v_課程號號:='&課程號號';SELECT成績績INTOv_成績FROM成成績表WHERE學(xué)號=v_學(xué)學(xué)號AND課程程號=v_課程號;IFv_成績<60THENUPDATE成績績表SET是否否補考='是'WHERE學(xué)號=v_學(xué)學(xué)號AND課程程號=v_課程號號;DBMS_OUTPUT.PUT_LINE(v_學(xué)號號||'號號學(xué)生'||v_課程號||'號課課程'||'成績?yōu)闉椋?||v_成績||'需要要補考');ELSENULL;ENDIF;END;思考：如何對表中中所有數(shù)據(jù)據(jù)進行判斷斷并設(shè)置補補考信息？？7.3異常處理注意：commit語句7.3異常處理在語句執(zhí)行行過程中，，因為各種種原因是的的語句不能能正常執(zhí)行行時，就有有可能造成成更大錯誤誤或整個系系統(tǒng)的崩潰潰，所以應(yīng)應(yīng)該采取必必要的措施施來防止這這種情況的的發(fā)生。PL/SQL提供了異常常這一處理理錯誤情況況的方法。。在PL/SQL代碼部分執(zhí)執(zhí)行的過程程中無論何何時發(fā)生錯錯誤，控制制將自動轉(zhuǎn)轉(zhuǎn)向執(zhí)行異異常部分。。PL/SQL有三種異常常：系統(tǒng)預(yù)定義義異常、非非預(yù)定義異異常和用戶戶自定義異異常。7.3異常處理7.3.1系統(tǒng)預(yù)定義義異常系統(tǒng)預(yù)定義義異常處理理是針對PL/SQL程序編譯、、執(zhí)行過程程中發(fā)生的的問題進行行處理的程程序，當一一段PL/SQL程序違反Oracle規(guī)則或是超超出系統(tǒng)特特定限制時時就會引發(fā)發(fā)異常。例例如，如果果SELECTINTO語句沒有返返回記錄，，或者在已已經(jīng)達到結(jié)結(jié)果集末尾尾的情況下下，Oracle將激活預(yù)定定義異常NO_DATA_FOUND。系統(tǒng)中預(yù)定定義的異常常如表7-9所示。7.3異常處理系統(tǒng)預(yù)定義義異常的拋拋出和處理理的語法格格式如下：：EXCEPTIONWHEN異常名1[OR異常名2…]THEN異常處理段段1；[WHEN異常名3[OR異常名4…]THEN異常處理段段2；[WHENOTHERSTHEN異常處理段段3；]]7.3異常處理【例7.13】根據(jù)據(jù)輸入的學(xué)學(xué)生姓名，，在“學(xué)生生表”中查查找并輸出出其相關(guān)信信息，如果果不存在該該學(xué)生，進進行異常處處理。代碼碼如下：7.3異常處理DECLAREv_學(xué)號學(xué)學(xué)生表.學(xué)號%TYPE;v_性別學(xué)學(xué)生表.性別%TYPE;v_入學(xué)時時間學(xué)生生表.入學(xué)學(xué)時間%TYPE;BEGINSELECT學(xué)號號,性別,入學(xué)時間間INTOv_學(xué)號,v_性別,v_入學(xué)學(xué)時間FROM學(xué)學(xué)生表WHERE姓名='&姓名';DBMS_OUTPUT.PUT_LINE('學(xué)號：：'||v_學(xué)號);DBMS_OUTPUT.PUT_LINE('性別：：'||v_性別);DBMS_OUTPUT.PUT_LINE('入學(xué)時時間：'||v_入入學(xué)時間);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('沒有該該學(xué)生!');END;作業(yè)二、用用%ROWTYPE完成上述問問題。7.3異常處理7.3.2非預(yù)定義異異常預(yù)定義異常常只能用于于處理21種Oracle系統(tǒng)異常，，而PL/SQL塊運行時可可能還會遭遭遇其他Oracle異常，為了了提高PL/SQL程序塊的健健壯性，系系統(tǒng)提供了了非預(yù)定義義異常，用用于處理與與預(yù)定義異異常無關(guān)的的Oracle異常。非預(yù)定義異異常處理步步驟如下：：在聲明部分分，用EXCEPTION類型定義異異常的名稱稱。在聲明部分分，使用EXCEPTION_INIT編譯指令建建立該異常常名稱與某某個Oracle異常的關(guān)聯(lián)聯(lián)。當該Oracle異常發(fā)生時時，就會自自動進行處處理。在異常處理理部分，處處理異常。。7.3異常處理EXCEPTION_INIT的語法格式式為：PRAGMAEXCEPTION_INIT（exception_name,oracle_error_code）其中：exception_name是定義的異異常名稱；；oracle_error_code是Oracle的異常代碼碼。7.3異常處理【例7.14】“學(xué)學(xué)生表”中中學(xué)號字段段不允許為為空，當插插入一個空空值時，系系統(tǒng)會產(chǎn)生生錯誤代碼碼為-1400（-01400）的異常，對對這個錯誤誤進行異常常處理的代代碼如下：：DECLAREe_null_errorEXC