Oracle數(shù)據(jù)庫實用教程第三章 PL／SQL程序設計

1、第三章 PL/SQL程序設計主要內(nèi)容容3.1PL/SQL的優(yōu)優(yōu)點：3.2運運行行PL/SQL程序3.3PL/SQL塊結結構3.4PL/SQL基本本語法3.5PL/SQL處處理流程程3.6異異常常處理3.7游游標標3.8存存儲儲過程和和函數(shù)3.9觸觸發(fā)發(fā)器3.1PL/SQL的優(yōu)優(yōu)點有利于客客戶/服服務器環(huán)環(huán)境應用用的運行行使用PL/SQL進行行編程，將大量量數(shù)據(jù)處處理的應應用放在在服務器器端來執(zhí)執(zhí)行，省省去了數(shù)數(shù)據(jù)在網(wǎng)網(wǎng)上的傳傳輸時間間。適合于客客戶環(huán)境境由于PL/SQL分為為數(shù)據(jù)庫庫PL/SQL部分和和工具PL/SQL。對于客客戶端來來說，PL/SQL可可以嵌套套到相應應的工具具中，客客戶端程

2、程序可以以執(zhí)行本本地包含含PL/SQL部分，也可以以向服務務發(fā)SQL命令令或激活活服務器器端的PL/SQL程程序運行行。3.2運運行PL/SQL程程序PL/SQL程程序的運運行是通通過Oracle中的的一個引引擎來進進行的。這個引引擎可能能在Oracle的服服務器端端，也可可能在Oracle 應用用開發(fā)的的客戶端端。引擎擎執(zhí)行PL/SQL中中的過程程性語句句，然后后將SQL語句句發(fā)送給給數(shù)據(jù)庫庫服務器器來執(zhí)行行，再將將結果返返回給執(zhí)執(zhí)行端。例如，如如果應用用程序需需要取得得學生的的成績，那么可可以建立立函數(shù)實實現(xiàn)該項項功能。SQL createfunctionget_grade1(sno ch

3、ar,cno char)2returnnumber is3V_gradenumber(3);4begin5selectgrade6intoV_grade7fromsc8where stu_no=snoand cou_no=cno;9returnV_grade;10end;11/函數(shù)已創(chuàng)創(chuàng)建。SQL varv_grade numberSQLexec :v_grade:=get_grade1(20026101,a02)PL/SQL過過程已已成功完完成。SQL print v_grade3.3PL/SQL塊結構構PL/SQL程程序由三三個塊組組成，即即 聲明明部分、執(zhí)行部部分、異異常處理理部分。PL

4、/SQL塊的結結構如下下：Declare/*聲聲明明部分: 在此此聲聲明PL/SQL用到到的變量量,類型型及光標標 */Begin/*執(zhí)執(zhí)行部部分:過過程程及SQL語語句,即即程序的的主要部部分*/Exception/*執(zhí)執(zhí)行異常常部分: 錯誤誤處理*/End;其中執(zhí)執(zhí)行部分分是必須須的。而而END則是PL/SQL塊塊的結結束標記記。需要注意意的是DECLARE，BEGIN，EXCEPTION后面面沒有分分號（；），而而END后則必必須要帶帶有分號號。PL/SQL標標識符的的命名規(guī)規(guī)則：標標識符符的最大大長度是是30個個字符，包括字字母、數(shù)數(shù)字、$、_、#；不可包包含保留留字；要要以字來來打頭

5、；不能和和同一塊塊中的表表中的字字段名一一樣。【例3-1】只包含執(zhí)執(zhí)行部分分的PL/SQL塊SQL setserveroutputonSQLbegin2dbms_output.put_line(Welcome!);3end;4/Welcome!PL/SQL過程已成成功完成成。 注意：當當使用dbms_output.包輸出數(shù)數(shù)據(jù)或消消息時，必須要要將SQL*Plus的環(huán)境變變量serveroutput設置為on.【例3-2】包包含定義義部分和和執(zhí)行部部分的PL/SQL塊塊SQL DECLARE2v_snameVARCHAR(10);3BEGIN4select stu_name INTOv_sna

6、meFROM student5WHEREstu_no=&no;6dbms_output.put_line(學生生姓名：|v_sname);7END;8/輸入no的的值:20026101原值5:WHEREstu_no=&no;新值5:WHEREstu_no=20026101;學生姓名名：李勇勇PL/SQL過過程已已成功完完成。注意：該該例中當當執(zhí)行該該PL/SQL時，會會根據(jù)輸輸入的學學號顯示示學生姓姓名。為為了臨時時存放姓姓名，就就必須定定義變量量。&no為為SQL*Plus的的替代變變量。3.4PL/SQL基本語語法3.4.1常常量與變變量定義常量量的語法法格式：常量名constant類型標

7、識識符notnull:=值;常量包括括后面的的變量名名都必須須以字母母開頭，不能有有空格，不能超超過30個字符符長度，同時不不能和保保留字同同名，常常（變）量名稱稱不區(qū)分分大小寫寫，在字字母后面面可以帶帶數(shù)字或或特殊字字符。括括號內(nèi)的的not null為為可選參參數(shù)，若若選用，表明該該常（變變）量不不能為空空值?！纠?-4】常常量定義義SQL declare2pi constant number(9):=3.1415926;3begin4commit;5end;6/PL/SQL過過程已已成功完完成。3.4.2基本數(shù)據(jù)據(jù)類型變變量PL/SQL中中常用的的基本數(shù)數(shù)據(jù)類型型3.4.3基基本數(shù)數(shù)據(jù)類型

8、型變量的的定義方方法變量名類類型標標識符notnull:=值;【例3-5】程程序定定義了名名為age的數(shù)數(shù)字型變變量，長長度為3，初始始值為26SQL declare2v_agenumber(3):=26;3begin4commit;5end;6/PL/SQL過過程已已成功完完成。3.4.4復復合數(shù)數(shù)據(jù)類型型變量使用%type定義變變量為了讓PL/SQL中中變量的的類型和和數(shù)據(jù)表表中的字字段的數(shù)數(shù)據(jù)類型型一致，Oracle 9i提供了了%type定定義方法法。這樣樣當數(shù)據(jù)據(jù)表的字字段類型型修改后后，PL/SQL程序序中相應應變量的的類型也也自動修修改?！纠?-6】該該程序定定義了名名為v_sn

9、ame的變量量，其類類型和student據(jù)表表中的stu_name字字段類型型是一致致的。SQL Declare2v_snamestudent.stu_name%type;3begin4commit;5end;6/PL/SQL過過程已已成功完完成。自定義記記錄類型型變量很多結構構化程序序設計語語言都提提供了記記錄類型型的數(shù)據(jù)據(jù)類型，在PL/SQL中，也支持持將多個個基本數(shù)數(shù)據(jù)類型型捆綁在在一起的的記錄數(shù)數(shù)據(jù)類型型?！纠?-7】程程序代碼碼定義了了名為stu_record_type的記錄錄類型，該記錄錄類型由由字符型型的sno、字字符型的的name和整整型的age基基本類型型變量組組成，stu_

10、record是該類類型的變變量，引引用記錄錄型變量量的方法法是“記記錄變量量名.基基本類型型變量名名”。使用%rowtype屬性定定義記錄錄變量使用%type可以使使變量獲獲得字段段的數(shù)據(jù)據(jù)類型，使用%rowtype可以以使變量量獲得整整個記錄錄的數(shù)據(jù)據(jù)類型。該屬性性可以基基于表或或視圖定定義記錄錄變量。為了簡簡化表或或視圖所所有列數(shù)數(shù)據(jù)的處處理，應應該使用用該屬性性定義記記錄變量量?！纠?-8】執(zhí)執(zhí)行下列列PL/SQL程序，程序定定義了名名為myrecord的復合合類型變變量，與與student表結結構相同同。SQL DECLARE2myrecordstudent%rowtype;3BEGI

11、N4select *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_FOUND THEN14dbms_output.put_line(請請輸入正正確的

12、學學號!);15END;16/3.4.5PL/SQL集合類類型索引表(PL/SQL表)PL/SQL表表與其他他過程化化語言(如C語語言)的的一維數(shù)數(shù)組類似似。需要要注意的的是，高高級語言言數(shù)組的的下標不不能為負負，但PL/SQL表表的下下標可以以為負值值；高級級語言數(shù)數(shù)組的元元素個數(shù)數(shù)有限制制，而PL/SQL表表的元元素個數(shù)數(shù)沒有限限制，并并且其下下標沒有有上下限限?，F(xiàn)PL/SQL表表需要創(chuàng)創(chuàng)建一個個數(shù)據(jù)類類型并另另外進行行變量說說明。表表類型變變量和數(shù)數(shù)據(jù)表是是有區(qū)別別的，定定義表類類型變量量的語法法如下：TypeIsTableOfIndexby SETSERVEROUTPUT ONSQL

13、Declare2TypeArray_typeis3TableOfNumber4IndexbyBinary_Integer;5My_ArrayArray_type;6Begin7For IIn1.10Loop8My_Array(I) := I*2;9End Loop;10For IIn1.10Loop11Dbms_Output.Put_line(To_char(My_Array(I);12End Loop;13End;14/嵌套表嵌套表是是嵌在一一張表中中記錄的的表。對對保存嵌嵌套表的的表中的的每一列列都可以以創(chuàng)建一一張存儲儲表。嵌嵌套表的的每一行行都存儲儲在主表表外的存存儲表中中。其格格式：t

14、ype嵌嵌套表名名 is table of元元素類型型 notnull;嵌套表（Nested Table）類似似于高級級語言中中的數(shù)組組。需要要注意的的是，高高級語言言數(shù)組和和嵌套表表的下標標都不能能為負值值，高級級語言數(shù)數(shù)組的元元素個數(shù)數(shù)有限制制，而嵌嵌套表的的元素個個數(shù)沒有有限制。當在表列列中使用用嵌套表表時，必必須首先先使用CREATETYPE語句句建立嵌嵌套表類類型。該該嵌套表表類型被被存儲在在數(shù)據(jù)字字典中(user_type)。【例3-11】為雇員員信息建建立對象象類型emp_obj，而emp_array是是基于emp_obj的嵌套套表類型型，它可可以用于于存儲多多個雇員員信息。SQ

15、L createorreplace typeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredate date);5/類型已創(chuàng)創(chuàng)建。SQL createorreplace typeemp_array is table of emp_obj;2/類型已創(chuàng)創(chuàng)建。SQL createtabledepartment (2depno number(2),3dname varchar2(10),4employeeemp_array5)nested table employee store as employee;表已創(chuàng)建建。createt

16、able語語句中中包含有有nested table子句，指明將將用來存存放嵌套套表行的的存儲表表的名字字為employee。而且且，對此此存儲表表不能直直接進行行訪問，必須通通過主表表才能訪訪問引存存儲表中中的數(shù)據(jù)據(jù).存存儲表是是系統(tǒng)生生成的表表，它用用來存儲儲嵌套表表中的實實際數(shù)據(jù)據(jù)，這些些數(shù)據(jù)不不是和表表中其他他列的數(shù)數(shù)據(jù)共同同存儲的的，而是是被單獨獨存放的的。變長數(shù)組組(VARRAY)VARRAY也也是一種種用于處處理PL/SQL數(shù)數(shù)組的數(shù)數(shù)據(jù)類型型，客觀觀存在也也可以作作為表列列的數(shù)據(jù)據(jù)類型使使用。該該數(shù)據(jù)類類型與高高級語言言數(shù)組非非常類似似，其元元素下標標以1開開始，并并且元素素的最

17、大大個數(shù)是是有限制制的。定定義變長長數(shù)組的的格式：type類類型名isvarry（最大尺尺寸)of 元素素類型notnull;當在PL/SQL塊塊中使用用varray變量時時，必須須首先使使用其構構造方法法來初始始化varray變量量，然后后才能在在PL/SQL塊內(nèi)引引用varray元素素。下面面舉例說說明使用用VARRAY的方法法：SQL declare2type sname_table_typeisvarray(10)ofstudent.stu_name%TYPE;3sname_tablesname_table_type:=sname_table_type(lin);4begin5sele

18、ct stu_name intosname_table(1)fromstudent6wherestu_no=&no;7dbms_output.put_line(學生生姓名:|sname_table(1);8end;9/輸入no的的值:20026102原值6:wherestu_no=&no;新值6:wherestu_no=20026102;學生姓名名:劉晨晨PL/SQL過過程已已成功完完成。3.5PL/SQL處理流程程在PL/SQL程序中中，要使使程序能能按照邏邏輯進行行處理，除了有有些語句句是SQL語句句外，還還必須有有能進行行邏輯控控制的語語句。PL/SQL也也不例例外，它它不僅可可以嵌入入

19、SQL語句，而且還還支持條條件分支支語句（IF，CASE）、循環(huán)語語句（LOOP）。格式：IFTHENPL/SQL和和SQL語語句;ELSE其它語句句;ELSIFTHEN其它語句句;ENDIF;3.5.1條條件分支支語句【例3-12】判斷斷兩個整整數(shù)變量量的大小小，輸出出不同的的結果。SQL setserveroutputonSQLdeclare2number1integer:=80;3number2integer:=90;4begin5if number1=number2then6if number1=number2then7dbms_output.put_line(number1等于num

20、ber2);8else9dbms_output.put_line(number1小于number2);10endif;11else12dbms_output.put_line(number1大于于number2);13endif;14end;15/從Oracle9i開開始，不不僅可以以使用IF語句句，也可可以使用用CASE語句句來執(zhí)行行多重條條件分支支操作。使用CASE語句更更加簡捷捷，而且且執(zhí)行效效率也更更好。在CASE語句句中使用用單一選選擇符進進行等值值比較格式：CASEWHENTHEN語語句1；WHENTHEN語語句1；WHENTHEN語語句句1；ELSE語語句n+1;ENDCASE;

21、3.5.2CASE語句句在CASE語句句中使用用多種條條件比較較格式：CASEWHENTHEN語語句句1；WHENTHEN語語句句1；WHENTHEN語語句1；ELSE語語句n+1;ENDCASE;基本循環(huán)環(huán)Loop要執(zhí)行的的語句;exit whencondition;endloop;當使用基基本循環(huán)環(huán)時，無無論是否否滿足條條件，語語句至少少會被執(zhí)執(zhí)行一次次。當condition為為TURE時，會退出出循環(huán)，并執(zhí)行行END LOOP后后的相應應操作。3.5.3循循環(huán)語句句【例3-15】為為stu2表表插入5條數(shù)據(jù)據(jù)(2004610120046105)。SQL createtablestu2(s

22、noint);表已創(chuàng)建建。SQLdeclare2iint:=20048101;3begin4loop5insert intostu2values(i);6exitwhen i=20048105;7i:=i+1;8endloop;9end;10/PL/SQL過過程已已成功完完成。WHILE循循環(huán)格式：whileloop要執(zhí)行的的語句;endloop;只有條件件為真時時，才會會執(zhí)行循循環(huán)體內(nèi)內(nèi)的語句句。FOR循循環(huán)格式：FOR循循環(huán)環(huán)控制變變量IN REVERSE下下界值 上界界值LOOPstatement1;statement2;ENDLOOP;當使用FOR循循環(huán)時，每次循循環(huán)時循循環(huán)控制制變量

23、會會自動增增一；如如果指定定REVERSE選項項，那么么每次循循環(huán)時循循環(huán)控制制變量會會自動減減一。3.6異異常常處理一個優(yōu)秀秀的程序序都應該該能夠正正確處理理各種出出錯情況況，并盡盡可能從從錯誤中中恢復。Oracle 提供供異常情情況(EXCEPTION)和異常處處理(EXCEPTIONHANDLER)來實現(xiàn)錯錯誤處理理。雖然在PL/SQL編編程中，異常處處理不是是必須的的，但建建議編程程人員要要養(yǎng)成在在PL/SQL編程中中指定相相應的異異常。異常處理理是用來來處理正正常執(zhí)行行過程中中未預料料的事件件，異常常處理包包括預定定義的錯錯誤和自自定義錯錯誤。PL/SQL程程序塊一一旦產(chǎn)生生異常而而

24、沒有指指出如何何處理時時,程序序就會自自動終止止整個程程序運行行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;.whenothersthenstatement;END;其中：異異常處理理可以按按任意次次序排列列,但Others 必須須放在最最后。3.6.1異異常處理理概念兩種類型型的異常常：用戶戶定義(user_define) 異常常和預定定義( predefined)異常常。當使用預預定義異異常處理理時，應應該了解解PL/SQL 塊的的常見運運行錯誤誤，并掌掌握與之之相關的的預定義義異常處處理。3.6.

25、2預預定義的的異常處處理可以使用用RAISE_APPLICATION_ERROR創(chuàng)創(chuàng)建自自己的錯錯誤處理理。其語語法如下下：RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors );其中：error_number是是從20,000到到 20,999之之間的的參數(shù)，error_message 是相相應的提提示信息息( setserveroutputonSQL declare2cursorstu_cursoris3selectstu_no,stu_namefromstudentwherestu_deptlike信息;4v_sn

26、amevarchar2(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);14end loop;15closestu_cursor;16end;17/所有的SQL語語句在在上下文文區(qū)內(nèi)部部都是可可執(zhí)行的的，因此此都有一一個游標標指向上上下文區(qū)區(qū)，此游游標就是是所謂的的SQL游

27、標(SQL cursor)，即隱式式游標。與顯式式游標不不同，SQL游游標不不被程序序打開和和關閉。當一個DML語語句執(zhí)行行時，PL/SQL內(nèi)內(nèi)部打開開一個游游標，語語句的結結果被保保存在4個游標標屬性中中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游標是一一塊包含含有查詢詢信息的的內(nèi)存空空間。在在執(zhí)行DML語語句，游游標被打打開；當當語句完完成時，游標被被關閉。3.7.2隱隱式游標標【例3-20】更更新學生生表，如如果找到到學號為為20028104的學學生更新新學生的的年齡，否則往往學生表表中插入入該學生生的記錄錄。SQL BEGIN2UPDATE

28、student3SETstu_age=194WHERE stu_no=20028104;5-如如果更新新沒有匹匹配則插插入一新新行6IF SQL%NOTFOUNDTHEN7INSERTintoStudent VALUES(20028104,李李濱,19,男,信信息);8ENDIF;9commit;10END;11/游標FOR循環(huán)環(huán)是在PL/SQL塊塊中使使用游標標最簡單單的方式式，簡化化了對游游標的處處理。當當使用游游標FOR循環(huán)環(huán)時，Oracle會會隱含含地打開開游標、提取游游標數(shù)據(jù)據(jù)并關閉閉游標。語法如如下：FORrecord_nameIN cursor_nameLOOPStstement

29、1;Ststement2;ENDLOOP;其中：record_name是Oracle隱隱含定定義的記記錄變量量名。當使用游游標開發(fā)發(fā)PL/SQL 應用用程序時時，為了了簡化程程序代碼碼，建議議大家使使用游標標FOR循環(huán)。3.7.3游游標FOR循環(huán)環(huán)【例3-21】給課課程名為為數(shù)據(jù)據(jù)庫原理理的所所有學生生的成績績加5分分。SQL declare2cursorsc1_cursor3is4select*5fromscforupdate;6begin7dbms_output.put_line(課課程號學學號成成績);8forsc_rec in sc1_cursorloop9if sc_rec.cou_

30、no=a01then10dbms_output.put_line(sc_rec.cou_no|sc_rec.stu_no|sc_rec.grade);11updatesc12setgrade=grade+213WHERECURRENTOFsc1_cursor;14endif;15endloop;16end;17/ORACLE編編寫的程程序一般般分為兩兩類：存儲過程程：是可可以完成成一定功功能的程程序叫存存儲過程程；函數(shù)：是是在使用用時給出出一個或或多個值值，處理理完后返返回一個個或多個個結果的的程序叫叫函數(shù)；這兩類程程序都存存放在Oracle數(shù)數(shù)據(jù)庫字字典中。3.8存存儲過程程和函數(shù)數(shù)與其它的

31、的數(shù)據(jù)庫庫系統(tǒng)一一樣，Oracle的的存儲過過程是用用PL/SQL語言編編寫的能能完成一一定處理理功能的的存儲在在數(shù)據(jù)庫庫字典中中的程序序。創(chuàng)建過程程語法:CREATEOR REPLACE PROCEDURE過過程名名 (參參數(shù)名 IN|INOUT 數(shù)數(shù)據(jù)類型型.) IS|ASPL/SQL塊塊3.8.1存存儲過過程建立無參參數(shù)的存存儲過程程【例22】以以下過程程用于輸輸出當前前系統(tǒng)日日期和時時間。SQL CREATEORREPLACE PROCEDUREdata_time2IS3BEGIN4dbms_output.put_line(systimestamp);5END;6/過程已創(chuàng)創(chuàng)建。建立了

32、存存儲過程程data_time之后，就可以以調用該該過程。在SQL*Plus環(huán)境中中調用過過程有兩兩種方法法：方法一：使用execute命令調調用過程程SQL setserveroutputon;SQL execdata_time;19-7月-0509.08.36.312000000 下午午 +08:00PL/SQL過過程已已成功完完成。方法二：使用call命令調調用過程程SQL calldata_time();20-7月-0509.24.59.902000000 上午午 +08:00調用完成成。建立帶有有IN參參數(shù)的存存儲過程程建立存儲儲過程時時，可以以通過使使用輸入入?yún)?shù)，將應用用程序的的

33、數(shù)據(jù)傳傳遞到過過程中，如果不指指定參數(shù)數(shù)模式，則默認認是輸入入?yún)?shù)，可以使用用IN關關鍵字顯顯示地定定義輸入入?yún)?shù)。下面以建建立為選選課表SC插入入數(shù)據(jù)的的存儲過過程add_sc為例例，說明明建立帶帶有輸入入?yún)?shù)的的過程的的方法?！纠?-23】建立為選選課表SC插入入數(shù)據(jù)的的存儲過過程add_scv。SQL CREATEORREPLACE PROCEDUREadd_scv2(v_sno sc.stu_no%TYPE,v_cnosc.cou_no%TYPE,v_gradesc.grade%TYPE)3IS4e_integrity EXCEPTION;5pragmaexception_init(e

34、_integrity,-2291);6BEGIN7insert intosc(stu_no,cou_no,grade)8values(v_sno,v_cno,v_grade);9EXCEPTION10WHENdup_val_on_index THEN11RAISE_APPLICATION_ERROR(-20000,學號與與課程號號不能重重復);12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,學號或或課程號號不存在在);14END;15/建立OUT參數(shù)數(shù)的存儲儲過程過程不僅僅可以用用來執(zhí)行行特定操操作，而而且也可可以用于于輸出數(shù)數(shù)據(jù)，在

35、在過程中中輸出數(shù)數(shù)據(jù)是使使用OUT或INOUT參參數(shù)來來完成的的，當定定義輸出出參數(shù)時時，必須須提供OUT關關鍵字?！纠?-24】建建立用于于輸出某某學生某某門課的的成績的的存儲過過程sc_gradeSQL CREATEORREPLACE PROCEDUREsc_grade2(v_snoINsc.stu_no%TYPE,3v_cnoINsc.cou_no%TYPE,4v_gradeOUTsc.grade%TYPE)5IS6BEGIN7selectgradeinto v_grade8fromsc9where stu_no=v_snoandcou_no=v_cno;10EXCEPTION11WHE

36、Nno_data_foundTHEN12RAISE_APPLICATION_ERROR(-20000,學號號或課程程號不存存在);13END;14/建立帶INOUT參參數(shù)的存存儲過程程定義過程程時，不不僅可以以指定IN和OUT參參數(shù)，也也可以指指定IN OUT參數(shù)數(shù)。INOUT參參數(shù)也稱稱為輸入入輸出參參數(shù)，當當使用這這種參數(shù)數(shù)時，在在調用過過程之前前需要通通過變量量給該參參數(shù)傳遞遞數(shù)據(jù)。在調用用結束后后，Oracle會會通過該該變量將將過程結結果傳遞遞給應用用程序。【例3-25】將將一個7位數(shù)字字的電話話號碼（如2217889轉轉換成8 位數(shù)數(shù)字的電電話號碼碼。轉換換規(guī)則：第一個個數(shù)字為為2

37、，前前面加上上5，其余的的加上6。SQL CREATEORREPLACE PROCEDUREtelephone2(v_phone_noINOUTvarchar2)3IS4BEGIN5if substr(v_phone_no,1,1)=2 then6v_phone_no:=5|v_phone_no;7else8v_phone_no:=6|v_phone_no;9endif;10END;Oracle的的函數(shù)是是一個獨獨有的對對象，它它也是由由PL/SQL語句編編寫而成成。與存存儲過程程不同的的是函數(shù)數(shù)必須返返回某些些值，而而存儲過過程可以以不返回回任何值值。創(chuàng)建建函數(shù)的的語法如如下：CREATEO

38、R REPLACE FUNCTION函函數(shù)名 (argment in|inoutTYPE,argment in|out|inout TYPE,.RETURNdatatype IS| AS PL/SQL塊塊；3.8.2函數(shù)建立無參參數(shù)的函函數(shù)當建立函函數(shù)時，函數(shù)可可以帶有有參數(shù)，也可以以不帶參參數(shù)?！纠?-26】建立立用于顯顯示當前前數(shù)據(jù)庫庫用戶的的函數(shù)。（不帶帶任何參參數(shù)） SQL CREATEORREPLACE FUNCTIONget_user2returnvarchar23AS4v_uservarchar2(100);5BEGIN6selectusernameinto v_userfrom

39、 user_users;7returnv_user;8END;9/函數(shù)已創(chuàng)創(chuàng)建。建立帶IN參數(shù)數(shù)的函數(shù)數(shù)建立函數(shù)數(shù)時，通通過使用用輸入?yún)?shù)，可可以將應應用程序序的數(shù)據(jù)據(jù)傳遞到到函數(shù)中中，最終終通過執(zhí)執(zhí)行函數(shù)數(shù)可以將將結果返返回到應應用程序序中?！纠?-27】創(chuàng)建建函數(shù)get_grade，實現(xiàn)輸輸入學生生的學號號及課程程號返回回該門課課的成績績。如果果學號及及課程號號存在，顯示該該信息。（帶有有IN參參數(shù)）SQL CREATEORREPLACE FUNCTIONget_grade2(v_snoINvarchar2,v_cnoINvarchar2)3returnnumber4AS5v_grade

40、sc.grade%TYPE;6EGIN7selectgradeinto v_grade8fromsc9where stu_no=v_snoandcou_no=v_cno;10returnv_grade;11EXCEPTION12WHEN no_data_foundTHEN13RAISE_APPLICATION_ERROR(-20000,學號號或課程程號不存存在);14END;建立帶OUT參參數(shù)的函函數(shù)一般情況況下，函函數(shù)只有有一個返返回值，如果希希望使用用函數(shù)同同時返回回多個值值，則需需要使用用輸出參參數(shù)OUT?！纠?8】輸輸入學學生的學學號，建建立用于于返回學學生的姓姓名及所所在的專專業(yè)的函

41、函數(shù)get_dept。（帶帶有OUT參數(shù)數(shù)）SQL CREATEORREPLACE FUNCTIONget_dept2(v_snoINvarchar2, v_nameOUTvarchar2)3returnvarchar24AS5v_sdeptstudent.stu_dept%TYPE;6BEGIN7selectstu_name, stu_dept intov_name,v_sdept8from student9wherestu_no=v_sno;10returnv_sdept;11EXCEPTION12WHENno_data_foundTHEN13RAISE_APPLICATION_ERROR

42、(-20000,學學號不存存在);14END;存儲過程程、函數(shù)數(shù)是存儲儲在數(shù)據(jù)據(jù)字典中中的對象象，它們們是屬于于某一數(shù)數(shù)據(jù)庫用用戶的。用戶對對其所擁擁有的對對象可以以進行任任何操作作，其他他用戶在在被授予予了合適適的權限限以后也也可以訪訪問這些些對象。如果調試試正確的的存儲過過程沒有有進行授授權，那那就只有有建立者者本人才才可以運運行。所所以作為為應用系系統(tǒng)的一一部分的的存儲過過程也必必須進行行授權才才能達到到要求。 可以以用GRANT命令來來進行存存儲過程程的運行行授權。GRANT語法法：GRANTsystem_privilege |roleTOuser |role| PUBLICWITHA

43、DMINOPTIONGRANTobject_privilege |ALL columnONschema.objectFROM user|role |PUBLIC WITHGRANTOPTION3.8.3過過程和和函數(shù)的的安全性性【例29】假假定表student是用戶戶scott的的私有表表，用戶戶personal是是開發(fā)者者，最終終用戶是是green。現(xiàn)要求求green只只能通過過personal創(chuàng)創(chuàng)建的過過程add_stu存存取scott的student表表。該過過程插入入學生的的記錄。如何實實現(xiàn)？（1）首首先在scott用戶戶環(huán)境下下，為用用戶personal授于對對student表操操作

44、所需需的相應應權限。SQL grant select,insert,update,deleteONstudent to personal;授權成功功。注意：如如果某個個用戶沒沒有權限限來創(chuàng)建建存儲過過程，則則需要DBA將將創(chuàng)建過過程的權權限授予予某用戶戶。由于于personal用用戶沒有有創(chuàng)建存存儲過程程的權限限，必須須以DBA的身身份為personal用戶戶建立創(chuàng)創(chuàng)建存儲儲過程的的權限。否則personal用戶戶沒法創(chuàng)創(chuàng)建存儲儲過程的的權限。（2）personal擁有有這些權權限后，就可以以建立存存儲過程程。SQL CREATEORREPLACE PROCEDUREadd_stu2(v_sn

45、oscott.student.stu_no%TYPE,v_sname scott.student.stu_name%TYPE)3IS4e_integrityEXCEPTION;5pragmaexception_init(e_integrity,-2291);6BEGIN7insertinto scott.student(stu_no,stu_name)8values(v_sno,v_sname);9EXCEPTION10WHEN dup_val_on_indexTHEN11RAISE_APPLICATION_ERROR(-20000,學號號不能重重復);12WHENe_integrityTHE

46、N13RAISE_APPLICATION_ERROR(-20001,學號號不存在在);14END;15/（3）進進行授權權SQL grant executeonadd_stutogreen;授權成功功。（4）Green用戶戶就可以以對personal用戶所所建的存存儲過程程調用了了。示例例如下：SQL execpersonal.add_stu(20026121,李李琳);PL/SQL過過程已已成功完完成。提高數(shù)據(jù)據(jù)的安全全性與完完整性利用安全全性的權權限來控控制那些些沒有足足夠權限限用戶對對數(shù)據(jù)庫庫的間接接訪問。通過把把相關聯(lián)聯(lián)的表的的操作集集中到一一起，保保證對這這些相關關聯(lián)的表表執(zhí)行一一致

47、的操操作，或或者任何何操作都都不做。改善操作作性能多個用戶戶使用同同一個SQL語語句時，只需做做一次語語法分析析，只在在編譯時時進行語語法分析析，運行行時不再再重做，可以直直接調用用緩存中中的編譯譯代碼。3.8.4過過程和和函數(shù)的的優(yōu)點39觸發(fā)器觸發(fā)器是是許多關關系數(shù)據(jù)據(jù)庫系統(tǒng)統(tǒng)都提供供的一項項技術。在oracle系統(tǒng)統(tǒng)里，觸觸發(fā)器類類似過程程和函數(shù)數(shù)，都有有聲明，執(zhí)行和和異常處處理過程程的PL/SQL塊。觸發(fā)器在在數(shù)據(jù)庫庫里以獨獨立的對對象存儲儲，它與與存儲過過程不同同的是存存儲過程程通過其其它程序序來啟動動運行或或直接啟啟動運行行，而觸觸發(fā)器是是由一個個事件來來啟動運運行，觸觸發(fā)器是是當某

48、個個事件發(fā)發(fā)生時自自動地隱隱式運行行，并且且觸發(fā)器器不能接接收參數(shù)數(shù)。所以以運行觸觸發(fā)器就就叫觸發(fā)發(fā)或點火火（firing）。在Oracle里，觸觸發(fā)器事事件指的的是對數(shù)數(shù)據(jù)庫的的表進行行的INSERT、UPDATE及DELETE操作或對對視圖進進行類似似的操作作。DML觸觸發(fā)器器Oracle可可以在DML語語句進行行觸發(fā)，可以在在DML操作前前或操作作后進行行觸發(fā)，并且可可以對每每個行或或語句操操作上進進行觸發(fā)發(fā)。替代觸發(fā)發(fā)器在Oracle里，不不能直接接對由兩兩個以上上的表建建立的視視圖進行行操作，所以給給出了替替代觸發(fā)發(fā)器。系統(tǒng)觸發(fā)發(fā)器系統(tǒng)觸發(fā)發(fā)器是在在Oracle數(shù)據(jù)庫庫系統(tǒng)的的事件中

49、中進行觸觸發(fā)，如如Oracle系統(tǒng)的的啟動與與關閉等等。管理觸發(fā)發(fā)器Oracle提提供了了顯示觸觸發(fā)器信信息、禁禁止觸發(fā)發(fā)器和和激活觸觸發(fā)器等等功能。3.9.1觸觸發(fā)器器類型創(chuàng)建觸發(fā)發(fā)器的一一般語法法是：CREATEORREPLACETRIGGER觸觸發(fā)發(fā)器名 BEFORE|AFTER eventONtable_reference FOREACH ROWWHENtrigger_conditiontrigger_body;3.9.2DML觸觸發(fā)器建立BEFORE語句句觸發(fā)器器【例3- 30】建建立一個個行級觸觸發(fā)器，當選課課表被刪刪除一條條記錄時時，把被被刪除記記錄寫到到選課表表刪除日日志表中中

50、去。（1）首首先創(chuàng)建建一個日日志表sc.hisSQL createtableSC_his(2Snochar(8),3Cnochar(3),4Gradenumber(3);表已創(chuàng)建建。（2）創(chuàng)創(chuàng)建一個個行級觸觸發(fā)器。SQL createorreplace triggerscott.del_SC2beforedeleteonscott.SCfor eachrow3begin4-將將修修改前數(shù)數(shù)據(jù)插入入到日志志記錄表表 del_emp, 以供供監(jiān)督使使用。5insertintosc_his( sno,cno,grade)6values(:old.stu_no,:old.cou_no, :old.gr

51、ade);6end;8/（3）測測試：SQL deletesc2wherestu_no=20026102andcou_no=a03;已刪除1行行。SQL select* fromsc_his;SNOCNOGRADE-20026102a0389在行級觸觸發(fā)器中中，在列列名前加加上:old標標識符標標識該列列變化前前的值，加上:new標識符符標識變變化后的的值。使用條件件謂詞ORACLE提提供三三個參數(shù)數(shù) INSERTING，UPDATEING，DELETING用用于判判斷觸發(fā)發(fā)了哪些些操作。謂詞行行為如下下：INSERTING：如果觸觸發(fā)語句句是INSERT語語句，則為TRUE,否則則為FALS

52、EUPDATING：如如果觸發(fā)發(fā)語句是是 UPDATE語句句，則為為TRUE,否否則為FALSEDELETING：如如果觸發(fā)發(fā)語句是是 DELETE語語句，則則為TRUE,否則為為FALSE【例3- 31】創(chuàng)創(chuàng)建一個個包含多多個觸發(fā)發(fā)器事件件的觸發(fā)發(fā)器，禁禁止工作作人員在在星期六六及星期期日插入入、刪除除或更改改雇員的的信息。SQL createorreplace triggertri_emp2beforeinsert or updateordeleteonemp3begin4if to_char(sysdate,DY,nls_date_language=AMERICAN) IN (SAT,SUN)then5case6when insertingthen7raise_application_error(-20001,不能能在休息息日增加加雇員);8when updating then9raise_application_error(-20001,不能能