PLSQL程序設(shè)計經(jīng)典教程課件

第三章PL/SQL程序設(shè)計第三章PL/SQL程序設(shè)計1主要內(nèi)容3.1PL/SQL的優(yōu)點：3.2運行PL/SQL程序3.3PL/SQL塊結(jié)構(gòu)3.4PL/SQL基本語法3.5PL/SQL處理流程3.6異常處理3.7游標(biāo)3.8存儲過程和函數(shù)3.9觸發(fā)器主要內(nèi)容3.1PL/SQL的優(yōu)點：2

3.1PL/SQL的優(yōu)點有利于客戶/服務(wù)器環(huán)境應(yīng)用的運行

使用PL/SQL進行編程，將大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來執(zhí)行，省去了數(shù)據(jù)在網(wǎng)上的傳輸時間。適合于客戶環(huán)境由于PL/SQL分為數(shù)據(jù)庫PL/SQL部分和工具PL/SQL。對于客戶端來說，PL/SQL可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運行。3.1PL/SQL的優(yōu)點有利于客戶/服務(wù)器環(huán)境應(yīng)用的運33.2運行PL/SQL程序PL/SQL程序的運行是通過Oracle中的一個引擎來進行的。這個引擎可能在Oracle的服務(wù)器端，也可能在Oracle應(yīng)用開發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過程性語句，然后將SQL語句發(fā)送給數(shù)據(jù)庫服務(wù)器來執(zhí)行，再將結(jié)果返回給執(zhí)行端。例如，如果應(yīng)用程序需要取得學(xué)生的成績，那么可以建立函數(shù)實現(xiàn)該項功能。3.2運行PL/SQL程序PL/SQL程序的運行是通過Or4SQL>createfunctionget_grade1(snochar,cnochar)2returnnumberis3V_gradenumber(3);4begin5selectgrade6intoV_grade7fromsc8wherestu_no=snoandcou_no=cno;9returnV_grade;10end;11/函數(shù)已創(chuàng)建。SQL>varv_gradenumberSQL>exec:v_grade:=get_grade1('20026101','a02')PL/SQL過程已成功完成。SQL>printv_gradeSQL>createfunctionget_gra53.3PL/SQL塊結(jié)構(gòu)PL/SQL程序由三個塊組成，即聲明部分、執(zhí)行部分、異常處理部分。PL/SQL塊的結(jié)構(gòu)如下：Declare

/*聲明部分:在此聲明PL/SQL用到的變量,類型及光標(biāo)*/Begin

/*執(zhí)行部分:過程及SQL語句,即程序的主要部分*/Exception

/*執(zhí)行異常部分:錯誤處理*/End;3.3PL/SQL塊結(jié)構(gòu)PL/SQL程序由三個塊組成，即6其中執(zhí)行部分是必須的。而END則是PL/SQL塊的結(jié)束標(biāo)記。需要注意的是DECLARE，BEGIN，EXCEPTION后面沒有分號（；），而END后則必須要帶有分號。PL/SQL標(biāo)識符的命名規(guī)則：標(biāo)識符的最大長度是30個字符，包括字母、數(shù)字、$、_、#；不可包含保留字；要以字來打頭；不能和同一塊中的表中的字段名一樣。其中執(zhí)行部分是必須的。而END則是PL/SQL塊的結(jié)束標(biāo)7【例3-1】只包含執(zhí)行部分的PL/SQL塊SQL>setserveroutputonSQL>begin2dbms_output.put_line('Welcome!');3end;4/Welcome!PL/SQL過程已成功完成。

注意：當(dāng)使用dbms_output.包輸出數(shù)據(jù)或消息時，必須要將SQL*Plus的環(huán)境變量serveroutput設(shè)置為on.【例3-1】只包含執(zhí)行部分的PL/SQL塊8【例3-2】包含定義部分和執(zhí)行部分的PL/SQL塊SQL>DECLARE2v_snameVARCHAR(10);3BEGIN4selectstu_nameINTOv_snameFROMstudent5WHEREstu_no=&no;6dbms_output.put_line('學(xué)生姓名：'||v_sname);7END;8/輸入no的值:20026101原值5:WHEREstu_no=&no;新值5:WHEREstu_no=20026101;學(xué)生姓名：李勇PL/SQL過程已成功完成。注意：該例中當(dāng)執(zhí)行該PL/SQL時，會根據(jù)輸入的學(xué)號顯示學(xué)生姓名。為了臨時存放姓名，就必須定義變量。&no為SQL*Plus的替代變量?！纠?-2】包含定義部分和執(zhí)行部分的PL/SQL塊93.4PL/SQL基本語法3.4PL/SQL基本語法103.4.1常量與變量定義常量的語法格式：常量名constant類型標(biāo)識符[notnull]:=值;常量包括后面的變量名都必須以字母開頭，不能有空格，不能超過30個字符長度，同時不能和保留字同名，常（變）量名稱不區(qū)分大小寫，在字母后面可以帶數(shù)字或特殊字符。括號內(nèi)的notnull為可選參數(shù)，若選用，表明該常（變）量不能為空值。3.4.1常量與變量定義常量的語法格式：11【例3-4】常量定義SQL>declare2piconstantnumber(9):=3.1415926;3begin4commit;5end;6/PL/SQL過程已成功完成?！纠?-4】常量定義123.4.2基本數(shù)據(jù)類型變量PL/SQL中常用的基本數(shù)據(jù)類型3.4.2基本數(shù)據(jù)類型變量PL/SQL中常用的基本數(shù)據(jù)類133.4.3基本數(shù)據(jù)類型變量的定義方法變量名類型標(biāo)識符[notnull]:=值;【例3-5】程序定義了名為age的數(shù)字型變量，長度為3，初始值為26SQL>declare2v_agenumber(3):=26;3begin4commit;5end;6/PL/SQL過程已成功完成。3.4.3基本數(shù)據(jù)類型變量的定義方法變量名類型標(biāo)識符143.4.4復(fù)合數(shù)據(jù)類型變量使用%type定義變量為了讓PL/SQL中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)類型一致，Oracle9i提供了%type定義方法。這樣當(dāng)數(shù)據(jù)表的字段類型修改后，PL/SQL程序中相應(yīng)變量的類型也自動修改。3.4.4復(fù)合數(shù)據(jù)類型變量使用%type定義變量15【例3-6】該程序定義了名為v_sname的變量，其類型和student據(jù)表中的stu_name字段類型是一致的。SQL>Declare2v_snamestudent.stu_name%type;3begin4commit;5end;6/PL/SQL過程已成功完成。【例3-6】該程序定義了名為v_sname的變量，其類型和16自定義記錄類型變量很多結(jié)構(gòu)化程序設(shè)計語言都提供了記錄類型的數(shù)據(jù)類型，在PL/SQL中，也支持將多個基本數(shù)據(jù)類型捆綁在一起的記錄數(shù)據(jù)類型?！纠?-7】程序代碼定義了名為stu_record_type的記錄類型，該記錄類型由字符型的sno、字符型的name和整型的age基本類型變量組成，stu_record是該類型的變量，引用記錄型變量的方法是“記錄變量名.基本類型變量名”。自定義記錄類型變量17使用%rowtype屬性定義記錄變量使用%type可以使變量獲得字段的數(shù)據(jù)類型，使用%rowtype可以使變量獲得整個記錄的數(shù)據(jù)類型。該屬性可以基于表或視圖定義記錄變量。為了簡化表或視圖所有列數(shù)據(jù)的處理，應(yīng)該使用該屬性定義記錄變量?！纠?-8】執(zhí)行下列PL/SQL程序，程序定義了名為myrecord的復(fù)合類型變量，與student表結(jié)構(gòu)相同。使用%rowtype屬性定義記錄變量18SQL>DECLARE2myrecordstudent%rowtype;3BEGIN4select*5intomyrecord6fromstudent7wherestu_no=&no;8dbms_output.put_line('姓名:'||myrecord.stu_name);9dbms_output.put_line('年齡:'||myrecord.stu_age);10dbms_output.put_line('性別:'||myrecord.stu_sex);11dbms_output.put_line('專業(yè):'||myrecord.stu_dept);12EXCEPTION13WHENNO_DATA_FOUNDTHEN14dbms_output.put_line('請輸入正確的學(xué)號!');15END;16/SQL>DECLARE193.4.5PL/SQL集合類型索引表(PL/SQL表)PL/SQL表與其他過程化語言(如C語言)的一維數(shù)組類似。需要注意的是，高級語言數(shù)組的下標(biāo)不能為負，但PL/SQL表的下標(biāo)可以為負值；高級語言數(shù)組的元素個數(shù)有限制，而PL/SQL表的元素個數(shù)沒有限制，并且其下標(biāo)沒有上下限?，F(xiàn)PL/SQL表需要創(chuàng)建一個數(shù)據(jù)類型并另外進行變量說明。表類型變量和數(shù)據(jù)表是有區(qū)別的，定義表類型變量的語法如下：Type<用戶自定義的類型名稱>IsTableOf<索引表元素數(shù)據(jù)類型>Indexby<索引表元素下標(biāo)的數(shù)據(jù)類型;3.4.5PL/SQL集合類型索引表(PL/SQL表)20【例3-9】在索引表中使用數(shù)據(jù)類型Binary_integerSQL>SETSERVEROUTPUTONSQL>Declare2 TypeArray_typeis3 TableOfNumber4 IndexbyBinary_Integer;5 My_ArrayArray_type;6Begin7ForIIn1..10Loop8My_Array(I):=I*2;9EndLoop;10ForIIn1..10Loop11Dbms_Output.Put_line(To_char(My_Array(I)));12EndLoop;13End;14/【例3-9】在索引表中使用數(shù)據(jù)類型Binary_integ21嵌套表嵌套表是嵌在一張表中記錄的表。對保存嵌套表的表中的每一列都可以創(chuàng)建一張存儲表。嵌套表的每一行都存儲在主表外的存儲表中。其格式： type嵌套表名istableof元素類型[notnull];嵌套表（NestedTable）類似于高級語言中的數(shù)組。需要注意的是，高級語言數(shù)組和嵌套表的下標(biāo)都不能為負值，高級語言數(shù)組的元素個數(shù)有限制，而嵌套表的元素個數(shù)沒有限制。當(dāng)在表列中使用嵌套表時，必須首先使用CREATETYPE語句建立嵌套表類型。該嵌套表類型被存儲在數(shù)據(jù)字典中(user_type)。嵌套表22【例3-11】為雇員信息建立對象類型emp_obj，而emp_array是基于emp_obj的嵌套表類型，它可以用于存儲多個雇員信息。SQL>createorreplacetypeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredatedate);5/類型已創(chuàng)建。SQL>createorreplacetypeemp_arrayistableofemp_obj;2/類型已創(chuàng)建?！纠?-11】為雇員信息建立對象類型emp_obj，而emp23SQL>createtabledepartment(2depnonumber(2),3dnamevarchar2(10),4employeeemp_array5)nestedtableemployeestoreasemployee;表已創(chuàng)建。createtable語句中包含有nestedtable子句，指明將用來存放嵌套表行的存儲表的名字為employee。而且，對此存儲表不能直接進行訪問，必須通過主表才能訪問引存儲表中的數(shù)據(jù).存儲表是系統(tǒng)生成的表，它用來存儲嵌套表中的實際數(shù)據(jù)，這些數(shù)據(jù)不是和表中其他列的數(shù)據(jù)共同存儲的，而是被單獨存放的。SQL>createtabledepartment(24變長數(shù)組(VARRAY)VARRAY也是一種用于處理PL/SQL數(shù)組的數(shù)據(jù)類型，客觀存在也可以作為表列的數(shù)據(jù)類型使用。該數(shù)據(jù)類型與高級語言數(shù)組非常類似，其元素下標(biāo)以1開始，并且元素的最大個數(shù)是有限制的。定義變長數(shù)組的格式：

type類型名isvarry（最大尺寸)of元素類型[notnull];

當(dāng)在PL/SQL塊中使用varray變量時，必須首先使用其構(gòu)造方法來初始化varray變量，然后才能在PL/SQL塊內(nèi)引用varray元素。下面舉例說明使用VARRAY的方法：變長數(shù)組(VARRAY)25SQL>declare2typesname_table_typeisvarray(10)ofstudent.stu_name%TYPE;3sname_tablesname_table_type:=sname_table_type('lin');4begin5selectstu_nameintosname_table(1)fromstudent6wherestu_no=&no;7dbms_output.put_line('學(xué)生姓名:'||sname_table(1));8end;9/輸入no的值:20026102原值6:wherestu_no=&no;新值6:wherestu_no=20026102;學(xué)生姓名:劉晨PL/SQL過程已成功完成。PLSQL程序設(shè)計經(jīng)典教程課件263.5PL/SQL處理流程在PL/SQL程序中，要使程序能按照邏輯進行處理，除了有些語句是SQL語句外，還必須有能進行邏輯控制的語句。PL/SQL也不例外，它不僅可以嵌入SQL語句，而且還支持條件分支語句（IF，CASE）、循環(huán)語句（LOOP）。3.5PL/SQL處理流程在PL/SQL程序中，要使程序27格式：IF<布爾表達式>THEN PL/SQL和SQL語句;[ELSE 其它語句;][ELSIF<其它布爾表達式>THEN 其它語句;ENDIF;3.5.1條件分支語句格式：3.5.1條件分支語句28【例3-12】判斷兩個整數(shù)變量的大小，輸出不同的結(jié)果。SQL>setserveroutputonSQL>declare2number1integer:=80;3number2integer:=90;4begin5ifnumber1<=number2then6ifnumber1=number2then7dbms_output.put_line('number1等于number2');8else9dbms_output.put_line('number1小于number2');10endif;11else12dbms_output.put_line('number1大于number2');13endif;14end;15/PLSQL程序設(shè)計經(jīng)典教程課件29從Oracle9i開始，不僅可以使用IF語句，也可以使用CASE語句來執(zhí)行多重條件分支操作。使用CASE語句更加簡捷，而且執(zhí)行效率也更好。在CASE語句中使用單一選擇符進行等值比較格式：CASE<條件選擇符> WHEN<條件值的表達式1>THEN語句1； WHEN<條件值的表達式2>THEN語句1； …… WHEN<條件值的表達式n>THEN語句1； [ELSE語句n+1;]ENDCASE;3.5.2CASE語句從Oracle9i開始，不僅可以使用IF語句，也可以使用CA30在CASE語句中使用多種條件比較格式：CASE WHEN<條件表達式1>THEN語句1； WHEN<條件表達式2>THEN語句1； …… WHEN<條件表達式n>THEN語句1； [ELSE語句n+1;]ENDCASE;在CASE語句中使用多種條件比較31基本循環(huán)Loop 要執(zhí)行的語句; exit[whencondition];endloop;當(dāng)使用基本循環(huán)時，無論是否滿足條件，語句至少會被執(zhí)行一次。當(dāng)condition為TURE時，會退出循環(huán)，并執(zhí)行ENDLOOP后的相應(yīng)操作。3.5.3循環(huán)語句基本循環(huán)3.5.3循環(huán)語句32【例3-15】為stu2表插入5條數(shù)據(jù)(20046101…20046105)。SQL>createtablestu2(snoint);表已創(chuàng)建。SQL>declare2iint:=20048101;3begin4loop5insertintostu2values(i);6exitwheni=20048105;7i:=i+1;8endloop;9end;10/PL/SQL過程已成功完成?！纠?-15】為stu2表插入5條數(shù)據(jù)(200461033WHILE循環(huán)格式：while<布爾表達式>loop要執(zhí)行的語句;endloop;只有條件為真時，才會執(zhí)行循環(huán)體內(nèi)的語句。

WHILE循環(huán)34FOR循環(huán)

格式：

FOR循環(huán)控制變量IN[REVERSE]下界值…上界值LOOP

statement1;statement2;ENDLOOP;

當(dāng)使用FOR循環(huán)時，每次循環(huán)時循環(huán)控制變量會自動增一；如果指定REVERSE選項，那么每次循環(huán)時循環(huán)控制變量會自動減一。FOR循環(huán)353.6異常處理一個優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯情況，并盡可能從錯誤中恢復(fù)。Oracle提供異常情況(EXCEPTION)和異常處理(EXCEPTIONHANDLER)來實現(xiàn)錯誤處理。雖然在PL/SQL編程中，異常處理不是必須的，但建議編程人員要養(yǎng)成在PL/SQL編程中指定相應(yīng)的異常。3.6異常處理一個優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯情36異常處理是用來處理正常執(zhí)行過程中未預(yù)料的事件，異常處理包括預(yù)定義的錯誤和自定義錯誤。PL/SQL程序塊一旦產(chǎn)生異常而沒有指出如何處理時,程序就會自動終止整個程序運行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;……..whenothersthenstatement;END;其中：異常處理可以按任意次序排列,但Others必須放在最后。3.6.1異常處理概念異常處理是用來處理正常執(zhí)行過程中未預(yù)料的事件，異常處理包括預(yù)37兩種類型的異常：用戶定義(user_define)異常和預(yù)定義(predefined)異常。當(dāng)使用預(yù)定義異常處理時，應(yīng)該了解PL/SQL塊的常見運行錯誤，并掌握與之相關(guān)的預(yù)定義異常處理。3.6.2預(yù)定義的異常處理兩種類型的異常：用戶定義(user_define)異常和預(yù)38可以使用RAISE_APPLICATION_ERROR創(chuàng)建自己的錯誤處理。其語法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);其中：error_number是從–20,000到–20,999之間的參數(shù)，error_message是相應(yīng)的提示信息(<2048字節(jié))，keep_errors為可選，如果keep_errors=TRUE,則新錯誤將被添加到已經(jīng)引發(fā)的錯誤列表中。如果keep_errors=FALSE(缺省),則新錯誤將替換當(dāng)前的錯誤列表。3.6.3用戶定義的異常處理可以使用RAISE_APPLICATION_ERROR創(chuàng)建393.7游標(biāo)在PL/SQL中處理查詢語句和數(shù)據(jù)操縱語句時，Oracle必須分配一片叫上下文(contextarea)的區(qū)域來處理所必需的信息，其中包括：要處理的行的數(shù)目；一個指針，指向語句被分析以后的表示形式；查詢的活動集(activeset)。游標(biāo)是一個指向上下文的句柄(handle)或指針。通過游標(biāo)，PL/SQL可以控制上下文區(qū)和處理語句時上下文區(qū)會發(fā)生的事情。3.7游標(biāo)在PL/SQL中處理查詢語句和數(shù)據(jù)操縱語句時，40在PL/SQL程序中定義的游標(biāo)稱作顯式游標(biāo)。顯式游標(biāo)包括:定義游標(biāo)打開游標(biāo)提取數(shù)據(jù)（也稱為推進游標(biāo)）關(guān)閉游標(biāo)3.7.1顯式游標(biāo)在PL/SQL程序中定義的游標(biāo)稱作顯式游標(biāo)。顯式游標(biāo)包括:341定義游標(biāo)cursor游標(biāo)名[（參數(shù)名1數(shù)據(jù)類型[,參數(shù)2數(shù)據(jù)類型…]）]is查詢語句;其中：游標(biāo)名是一個沒有定義的PL/SQL變量，用戶不能給游標(biāo)名賦值。參數(shù)名是一個在SELECT語句的WHERE條件中要用到的參數(shù)。SELECT語句是一個不含INTO子句的SELECT語句，允許帶WHERE、ORDERBY、GROUPBY等子句。定義游標(biāo)42打開游標(biāo)打開游標(biāo)是在SELECT語句的所有輸入?yún)?shù)都接受值之后，執(zhí)行游標(biāo)所對應(yīng)的SELECT語句，將其查詢結(jié)果放入工作區(qū)，指針指向工作區(qū)的首部。假如在打開游標(biāo)時查詢沒有返回行，PL/SQL沒有觸發(fā)錯誤，可以在取數(shù)據(jù)（FETCH）之后檢測游標(biāo)的狀態(tài)。open游標(biāo)名稱[（實際參數(shù)值1[實際參數(shù)值2…]）];打開游標(biāo)43提取數(shù)據(jù)在打開游標(biāo)之后，SELECT語句的結(jié)果被臨時存放到游標(biāo)結(jié)果集中，為了處理結(jié)果的數(shù)據(jù)，需要使用FETCH語句提取游標(biāo)數(shù)據(jù)。格式一：fetch游標(biāo)名稱into變量列表;格式二：

fetch游標(biāo)名稱bulkcollectinto集合變量表[LIMTrows];關(guān)閉游標(biāo)close游標(biāo)名稱;提取數(shù)據(jù)44【例3-16】顯示信息專業(yè)的所有學(xué)生學(xué)號及姓名。（使用FETCH….INTO語句）SQL>setserveroutputonSQL>declare2cursorstu_cursoris3selectstu_no,stu_namefromstudentwherestu_deptlike'信息';4v_snamevarchar2(10);5v_snochar(8);6begin7ifnotstu_cursor%ISOPENthen8openstu_cursor;9endif;10loop11fetchstu_cursorintov_sno,v_sname;12exitwhenstu_cursor%NOTFOUND;13dbms_output.put_line(v_sno||','||v_sname);14endloop;15closestu_cursor;16end;17/【例3-16】顯示信息專業(yè)的所有學(xué)生學(xué)號及姓名。（使用F45所有的SQL語句在上下文區(qū)內(nèi)部都是可執(zhí)行的，因此都有一個游標(biāo)指向上下文區(qū)，此游標(biāo)就是所謂的SQL游標(biāo)(SQLcursor)，即隱式游標(biāo)。與顯式游標(biāo)不同，SQL游標(biāo)不被程序打開和關(guān)閉。當(dāng)一個DML語句執(zhí)行時，PL/SQL內(nèi)部打開一個游標(biāo)，語句的結(jié)果被保存在4個游標(biāo)屬性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游標(biāo)是一塊包含有查詢信息的內(nèi)存空間。在執(zhí)行DML語句，游標(biāo)被打開；當(dāng)語句完成時，游標(biāo)被關(guān)閉。3.7.2隱式游標(biāo)所有的SQL語句在上下文區(qū)內(nèi)部都是可執(zhí)行的，因此都有一個游46【例3-20】更新學(xué)生表，如果找到學(xué)號為’20028104’的學(xué)生更新學(xué)生的年齡，否則往學(xué)生表中插入該學(xué)生的記錄。SQL>BEGIN2UPDATEstudent3SETstu_age=194WHEREstu_no='20028104';5--如果更新沒有匹配則插入一新行6IFSQL%NOTFOUNDTHEN7INSERTintoStudentVALUES('20028104','李濱',19,'男','信息');8ENDIF;9commit;10END;11/【例3-20】更新學(xué)生表，如果找到學(xué)號為’200281047游標(biāo)FOR循環(huán)是在PL/SQL塊中使用游標(biāo)最簡單的方式，簡化了對游標(biāo)的處理。當(dāng)使用游標(biāo)FOR循環(huán)時，Oracle會隱含地打開游標(biāo)、提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)。語法如下：

FORrecord_nameINcursor_nameLOOPStstement1;Ststement2;ENDLOOP;其中：record_name是Oracle隱含定義的記錄變量名。當(dāng)使用游標(biāo)開發(fā)PL/SQL應(yīng)用程序時，為了簡化程序代碼，建議大家使用游標(biāo)FOR循環(huán)。3.7.3游標(biāo)FOR循環(huán)游標(biāo)FOR循環(huán)是在PL/SQL塊中使用游標(biāo)最簡單的方式，簡48【例3-21】給課程名為‘?dāng)?shù)據(jù)庫原理’的所有學(xué)生的成績加5分。SQL>declare2cursorsc1_cursor3is4select*5fromscforupdate;6begin7dbms_output.put_line('課程號學(xué)號成績');8forsc_recinsc1_cursorloop9ifsc_rec.cou_no='a01'then10dbms_output.put_line(sc_rec.cou_no||''||sc_rec.stu_no||''||sc_rec.grade);11updatesc12setgrade=grade+213WHERECURRENTOFsc1_cursor;14endif;15endloop;16end;17/【例3-21】給課程名為‘?dāng)?shù)據(jù)庫原理’的所有學(xué)生的成績加549ORACLE編寫的程序一般分為兩類：存儲過程：是可以完成一定功能的程序叫存儲過程；函數(shù)：是在使用時給出一個或多個值，處理完后返回一個或多個結(jié)果的程序叫函數(shù)；這兩類程序都存放在Oracle數(shù)據(jù)庫字典中。3.8存儲過程和函數(shù)ORACLE編寫的程序一般分為兩類：3.8存儲過程和函數(shù)50與其它的數(shù)據(jù)庫系統(tǒng)一樣，Oracle的存儲過程是用PL/SQL語言編寫的能完成一定處理功能的存儲在數(shù)據(jù)庫字典中的程序。創(chuàng)建過程語法:

CREATE[ORREPLACE]PROCEDURE過程名[(參數(shù)名[{IN|INOUT}]數(shù)據(jù)類型….)]{IS|AS}PL/SQL塊3.8.1存儲過程與其它的數(shù)據(jù)庫系統(tǒng)一樣，Oracle的存儲過程是用PL/SQ51建立無參數(shù)的存儲過程【例22】以下過程用于輸出當(dāng)前系統(tǒng)日期和時間。SQL>CREATEORREPLACEPROCEDUREdata_time2IS3BEGIN4dbms_output.put_line(systimestamp);5END;6/過程已創(chuàng)建。建立無參數(shù)的存儲過程52建立了存儲過程data_time之后，就可以調(diào)用該過程。在SQL*Plus環(huán)境中調(diào)用過程有兩種方法：方法一：使用execute命令調(diào)用過程SQL>setserveroutputon;SQL>execdata_time;19-7月-0509.08.36.312000000下午+08:00PL/SQL過程已成功完成。方法二：使用call命令調(diào)用過程SQL>calldata_time();20-7月-05002000000上午+08:00調(diào)用完成。建立了存儲過程data_time之后，就可以調(diào)用該過程。在S53建立帶有IN參數(shù)的存儲過程建立存儲過程時，可以通過使用輸入?yún)?shù)，將應(yīng)用程序的數(shù)據(jù)傳遞到過程中，如果不指定參數(shù)模式，則默認(rèn)是輸入?yún)?shù)，可以使用IN關(guān)鍵字顯示地定義輸入?yún)?shù)。下面以建立為選課表SC插入數(shù)據(jù)的存儲過程add_sc為例，說明建立帶有輸入?yún)?shù)的過程的方法。建立帶有IN參數(shù)的存儲過程54【例3-23】建立為選課表SC插入數(shù)據(jù)的存儲過程add_scv。

SQL>CREATEORREPLACEPROCEDUREadd_scv2(v_snosc.stu_no%TYPE,v_cnosc.cou_no%TYPE,v_gradesc.grade%TYPE)3IS4e_integrityEXCEPTION;5pragmaexception_init(e_integrity,-2291);6BEGIN7insertintosc(stu_no,cou_no,grade)8values(v_sno,v_cno,v_grade);9EXCEPTION10WHENdup_val_on_indexTHEN11RAISE_APPLICATION_ERROR(-20000,'學(xué)號與課程號不能重復(fù)');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'學(xué)號或課程號不存在');14END;15/【例3-23】建立為選課表SC插入數(shù)據(jù)的存儲過程add_s55建立OUT參數(shù)的存儲過程過程不僅可以用來執(zhí)行特定操作，而且也可以用于輸出數(shù)據(jù)，在過程中輸出數(shù)據(jù)是使用OUT或INOUT參數(shù)來完成的，當(dāng)定義輸出參數(shù)時，必須提供OUT關(guān)鍵字。建立OUT參數(shù)的存儲過程56【例3-24】建立用于輸出某學(xué)生某門課的成績的存儲過程sc_gradeSQL>CREATEORREPLACEPROCEDUREsc_grade2(v_snoINsc.stu_no%TYPE,3v_cnoINsc.cou_no%TYPE,4v_gradeOUTsc.grade%TYPE)5IS6BEGIN7selectgradeintov_grade8fromsc9wherestu_no=v_snoandcou_no=v_cno;10EXCEPTION11WHENno_data_foundTHEN12RAISE_APPLICATION_ERROR(-20000,'學(xué)號或課程號不存在');13END;14/【例3-24】建立用于輸出某學(xué)生某門課的成績的存儲過程s57建立帶INOUT參數(shù)的存儲過程定義過程時，不僅可以指定IN和OUT參數(shù)，也可以指定INOUT參數(shù)。INOUT參數(shù)也稱為輸入輸出參數(shù)，當(dāng)使用這種參數(shù)時，在調(diào)用過程之前需要通過變量給該參數(shù)傳遞數(shù)據(jù)。在調(diào)用結(jié)束后，Oracle會通過該變量將過程結(jié)果傳遞給應(yīng)用程序。建立帶INOUT參數(shù)的存儲過程58【例3-25】將一個7位數(shù)字的電話號碼（如2217889轉(zhuǎn)換成8位數(shù)字的電話號碼。轉(zhuǎn)換規(guī)則：第一個數(shù)字為2，前面加上5，其余的加上6。SQL>CREATEORREPLACEPROCEDUREtelephone2(v_phone_noINOUTvarchar2)3IS4BEGIN5ifsubstr(v_phone_no,1,1)='2'then6v_phone_no:='5'||v_phone_no;7else8v_phone_no:='6'||v_phone_no;9endif;10END;【例3-25】將一個7位數(shù)字的電話號碼（如221788959Oracle的函數(shù)是一個獨有的對象，它也是由PL/SQL語句編寫而成。與存儲過程不同的是函數(shù)必須返回某些值，而存儲過程可以不返回任何值。創(chuàng)建函數(shù)的語法如下：CREATE[ORREPLACE]FUNCTION函數(shù)名[(argment[{in|inout}]TYPE,argment[{in|out|inout}]TYPE,….]RETURNdatatype{IS|AS}PL/SQL塊；3.8.2函數(shù)Oracle的函數(shù)是一個獨有的對象，它也是由PL/SQL語句60建立無參數(shù)的函數(shù)當(dāng)建立函數(shù)時，函數(shù)可以帶有參數(shù)，也可以不帶參數(shù)。【例3-26】建立用于顯示當(dāng)前數(shù)據(jù)庫用戶的函數(shù)。（不帶任何參數(shù)）

SQL>CREATEORREPLACEFUNCTIONget_user2returnvarchar23AS4v_uservarchar2(100);5BEGIN6selectusernameintov_userfromuser_users;7returnv_user;8END;9/函數(shù)已創(chuàng)建。建立無參數(shù)的函數(shù)61建立帶IN參數(shù)的函數(shù)建立函數(shù)時，通過使用輸入?yún)?shù)，可以將應(yīng)用程序的數(shù)據(jù)傳遞到函數(shù)中，最終通過執(zhí)行函數(shù)可以將結(jié)果返回到應(yīng)用程序中?！纠?-27】創(chuàng)建函數(shù)get_grade，實現(xiàn)輸入學(xué)生的學(xué)號及課程號返回該門課的成績。如果學(xué)號及課程號存在，顯示該信息。（帶有IN參數(shù)）建立帶IN參數(shù)的函數(shù)62SQL>CREATEORREPLACEFUNCTIONget_grade2(v_snoINvarchar2,v_cnoINvarchar2)3returnnumber4AS5v_gradesc.grade%TYPE;6EGIN7selectgradeintov_grade8fromsc9wherestu_no=v_snoandcou_no=v_cno;10returnv_grade;11EXCEPTION12WHENno_data_foundTHEN13RAISE_APPLICATION_ERROR(-20000,'學(xué)號或課程號不存在');14END;SQL>CREATEORREPLACEFUNCTIO63建立帶OUT參數(shù)的函數(shù)一般情況下，函數(shù)只有一個返回值，如果希望使用函數(shù)同時返回多個值，則需要使用輸出參數(shù)OUT?！纠?8】輸入學(xué)生的學(xué)號，建立用于返回學(xué)生的姓名及所在的專業(yè)的函數(shù)get_dept。（帶有OUT參數(shù)）建立帶OUT參數(shù)的函數(shù)64SQL>CREATEORREPLACEFUNCTIONget_dept2(v_snoINvarchar2,v_nameOUTvarchar2)3returnvarchar24AS5v_sdeptstudent.stu_dept%TYPE;6BEGIN7selectstu_name,stu_deptintov_name,v_sdept8fromstudent9wherestu_no=v_sno;10returnv_sdept;11EXCEPTION12WHENno_data_foundTHEN13RAISE_APPLICATION_ERROR(-20000,'學(xué)號不存在');14END;PLSQL程序設(shè)計經(jīng)典教程課件65存儲過程、函數(shù)是存儲在數(shù)據(jù)字典中的對象，它們是屬于某一數(shù)據(jù)庫用戶的。用戶對其所擁有的對象可以進行任何操作，其他用戶在被授予了合適的權(quán)限以后也可以訪問這些對象。如果調(diào)試正確的存儲過程沒有進行授權(quán)，那就只有建立者本人才可以運行。所以作為應(yīng)用系統(tǒng)的一部分的存儲過程也必須進行授權(quán)才能達到要求?？梢杂肎RANT命令來進行存儲過程的運行授權(quán)。GRANT語法：GRANTsystem_privilege|roleTOuser|role|PUBLIC[WITHADMINOPTION]GRANTobject_privilege|ALLcolumnONschema.objectFROMuser|role|PUBLICWITHGRANTOPTION3.8.3過程和函數(shù)的安全性存儲過程、函數(shù)是存儲在數(shù)據(jù)字典中的對象，它們是屬于某一數(shù)據(jù)庫66【例29】假定表student是用戶scott的私有表，用戶personal是開發(fā)者，最終用戶是green?，F(xiàn)要求green只能通過personal創(chuàng)建的過程add_stu存取scott的student表。該過程插入學(xué)生的記錄。如何實現(xiàn)？（1）首先在scott用戶環(huán)境下，為用戶personal授于對student表操作所需的相應(yīng)權(quán)限。SQL>grantselect,insert,update,deleteONstudenttopersonal;授權(quán)成功?！纠?9】假定表student是用戶scott的私有表，用67注意：如果某個用戶沒有權(quán)限來創(chuàng)建存儲過程，則需要DBA將創(chuàng)建過程的權(quán)限授予某用戶。由于personal用戶沒有創(chuàng)建存儲過程的權(quán)限，必須以DBA的身份為personal用戶建立創(chuàng)建存儲過程的權(quán)限。否則personal用戶沒法創(chuàng)建存儲過程的權(quán)限。注意：如果某個用戶沒有權(quán)限來創(chuàng)建存儲過程，則需要DBA將創(chuàng)建68（2）personal擁有這些權(quán)限后，就可以建立存儲過程。SQL>CREATEORREPLACEPROCEDUREadd_stu2(v_snoscott.student.stu_no%TYPE,v_snamescott.student.stu_name%TYPE)3IS4e_integrityEXCEPTION;5pragmaexception_init(e_integrity,-2291);6BEGIN7insertintoscott.student(stu_no,stu_name)8values(v_sno,v_sname);9EXCEPTION10WHENdup_val_on_indexTHEN11RAISE_APPLICATION_ERROR(-20000,'學(xué)號不能重復(fù)');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'學(xué)號不存在');14END;15/（2）personal擁有這些權(quán)限后，就可以建立存儲過程。69（3）進行授權(quán)SQL>grantexecuteonadd_stutogreen;授權(quán)成功。（4）Green用戶就可以對personal用戶所建的存儲過程調(diào)用了。示例如下：SQL>execpersonal.add_stu('20026121','李琳');PL/SQL過程已成功完成。（3）進行授權(quán)70提高數(shù)據(jù)的安全性與完整性利用安全性的權(quán)限來控制那些沒有足夠權(quán)限用戶對數(shù)據(jù)庫的間接訪問。通過把相關(guān)聯(lián)的表的操作集中到一起，保證對這些相關(guān)聯(lián)的表執(zhí)行一致的操作，或者任何操作都不做。改善操作性能多個用戶使用同一個SQL語句時，只需做一次語法分析，只在編譯時進行語法分析，運行時不再重做，可以直接調(diào)用緩存中的編譯代碼。3.8.4過程和函數(shù)的優(yōu)點提高數(shù)據(jù)的安全性與完整性3.8.4過程和函數(shù)的優(yōu)點713．9觸發(fā)器觸發(fā)器是許多關(guān)系數(shù)據(jù)庫系統(tǒng)都提供的一項技術(shù)。在oracle系統(tǒng)里，觸發(fā)器類似過程和函數(shù)，都有聲明，執(zhí)行和異常處理過程的PL/SQL塊。觸發(fā)器在數(shù)據(jù)庫里以獨立的對象存儲，它與存儲過程不同的是存儲過程通過其它程序來啟動運行或直接啟動運行，而觸發(fā)器是由一個事件來啟動運行，觸發(fā)器是當(dāng)某個事件發(fā)生時自動地隱式運行，并且觸發(fā)器不能接收參數(shù)。所以運行觸發(fā)器就叫觸發(fā)或點火（firing）。在Oracle里，觸發(fā)器事件指的是對數(shù)據(jù)庫的表進行的INSERT、UPDATE及DELETE操作或?qū)σ晥D進行類似的操作。3．9觸發(fā)器觸發(fā)器是許多關(guān)系數(shù)據(jù)庫系統(tǒng)都提供的一項技術(shù)。在72DML觸發(fā)器Oracle可以在DML語句進行觸發(fā)，可以在DML操作前或操作后進行觸發(fā)，并且可以對每個行或語句操作上進行觸發(fā)。替代觸發(fā)器在Oracle里，不能直接對由兩個以上的表建立的視圖進行操作，所以給出了替代觸發(fā)器。系統(tǒng)觸發(fā)器系統(tǒng)觸發(fā)器是在Oracle數(shù)據(jù)庫系統(tǒng)的事件中進行觸發(fā)，如Oracle系統(tǒng)的啟動與關(guān)閉等。管理觸發(fā)器Oracle提供了顯示觸發(fā)器信息、禁止觸發(fā)器和激活觸發(fā)器等功能。3.9.1觸發(fā)器類型DML觸發(fā)器3.9.1觸發(fā)器類型73創(chuàng)建觸發(fā)器的一般語法是：CREATE[ORREPLACE]TRIGGER觸發(fā)器名[BEFORE|AFTER]eventONtable_reference[FOREACHROW[WHENtrigger_condition]]trigger_body;3.9.2DML觸發(fā)器創(chuàng)建觸發(fā)器的一般語法是：3.9.2DML觸發(fā)器74建立BEFORE語句觸發(fā)器【例3-30】建立一個行級觸發(fā)器，當(dāng)選課表被刪除一條記錄時，把被刪除記錄寫到選課表刪除日志表中去。建立BEFORE語句觸發(fā)器75（1）首先創(chuàng)建一個日志表sc.hisSQL>createtableSC_his(2Snochar(8),3Cnochar(3),4Gradenumber(3));表已創(chuàng)建。（2）創(chuàng)建一個行級觸發(fā)器。SQL>createorreplacetriggerscott.del_SC2beforedeleteonscott.SCforeachrow3begin4--將修改前數(shù)據(jù)插入到日志記錄表del_emp,以供監(jiān)督使用。5insertintosc_his(sno,cno,grade)6values(:old.stu_no,:old.cou_no,:old.grade);6end;8/（1）首先創(chuàng)建一個日志表sc.his76（3）測試：SQL>deletesc2wherestu_no='20026102'andcou_no='a03';已刪除1行。SQL>select*fromsc_his;SNOCNOGRADE---------------------------------20026102a0389在行級觸發(fā)器中，在列名前加上:old標(biāo)識符標(biāo)識該列變化前的值，加上:new標(biāo)識符標(biāo)識變化后的值。（3）測試：77使用條件謂詞ORACLE提供三個參數(shù)INSERTING，UPDATEING，DELETING用于判斷觸發(fā)了哪些操作。謂詞行為如下：INSERTING：如果觸發(fā)語句是INSERT語句，則為TRUE,否則為FALSEUPDATING：如果觸發(fā)語句是UPDATE語句，則為TRUE,否則為FALSEDELETING：如果觸發(fā)語句是DELETE語句，則為TRUE,否則為FALSE【例3-31】創(chuàng)建一個包含多個觸發(fā)器事件的觸發(fā)器，禁止工作人員在星期六及星期日插入、刪除或更改雇員的信息。使用條件謂詞78SQL>createorreplacetriggertri_emp2beforeinsertorupdateordeleteonemp3begin4ifto_char(sysdate,'DY','nls_date_language=AMERICAN')IN('SAT','SUN')then5case6wh