Oracle數(shù)據(jù)庫教程 第章

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

1、PL/SQL基本結構如下：

DECLARE

聲明部分

BEGIN

執(zhí)行部分

EXCEPTION

異常處理部分

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

PL/SQL的特殊結構

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

聲明部分BEGIN

執(zhí)行部分EXCEPTION

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

主塊聲明部分BEGIN

主塊執(zhí)行部分

DECLARE

子塊聲明部分

BEGIN

子塊執(zhí)行部分

EXCEPTION

子塊異常處理部分

END；EXCEPTION

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