Oracle數(shù)據(jù)庫(kù)實(shí)用教程第三章 L／SQL程序設(shè)計(jì)

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

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

使用PL/SQL進(jìn)行編程，將大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端來執(zhí)行，省去了數(shù)據(jù)在網(wǎng)上的傳輸時(shí)間。適合于客戶環(huán)境由于PL/SQL分為數(shù)據(jù)庫(kù)PL/SQL部分和工具PL/SQL。對(duì)于客戶端來說，PL/SQL可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向服務(wù)發(fā)SQL命令或激活服務(wù)器端的PL/SQL程序運(yùn)行。3.2運(yùn)行PL/SQL程序PL/SQL程序的運(yùn)行是通過Oracle中的一個(gè)引擎來進(jìn)行的。這個(gè)引擎可能在Oracle的服務(wù)器端，也可能在Oracle應(yīng)用開發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過程性語(yǔ)句，然后將SQL語(yǔ)句發(fā)送給數(shù)據(jù)庫(kù)服務(wù)器來執(zhí)行，再將結(jié)果返回給執(zhí)行端。例如，如果應(yīng)用程序需要取得學(xué)生的成績(jī)，那么可以建立函數(shù)實(shí)現(xiàn)該項(xiàng)功能。SQL>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_grade3.3PL/SQL塊結(jié)構(gòu)PL/SQL程序由三個(gè)塊組成，即聲明部分、執(zhí)行部分、異常處理部分。PL/SQL塊的結(jié)構(gòu)如下：Declare

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

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

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

注意：當(dāng)使用dbms_output.包輸出數(shù)據(jù)或消息時(shí)，必須要將SQL*Plus的環(huán)境變量serveroutput設(shè)置為on.【例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í)行該P(yáng)L/SQL時(shí)，會(huì)根據(jù)輸入的學(xué)號(hào)顯示學(xué)生姓名。為了臨時(shí)存放姓名，就必須定義變量。&no為SQL*Plus的替代變量。3.4PL/SQL基本語(yǔ)法3.4.1常常量與變變量定義常量量的語(yǔ)法法格式：：常量名constant類類型標(biāo)標(biāo)識(shí)符[notnull]:=值;常量包括括后面的的變量名名都必須須以字母母開頭，，不能有有空格，，不能超超過30個(gè)字符符長(zhǎng)度，，同時(shí)不不能和保保留字同同名，常常（變））量名稱稱不區(qū)分分大小寫寫，在字字母后面面可以帶帶數(shù)字或或特殊字字符。括括號(hào)內(nèi)的的notnull為為可選參參數(shù)，若若選用，，表明該該常（變變）量不不能為空空值。【例3-4】常常量定義義SQL>declare2piconstantnumber(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ù)類型型變量的的定義方方法變量名類類型標(biāo)標(biāo)識(shí)符[notnull]:=值;【例3-5】程程序定定義了名名為age的數(shù)數(shù)字型變變量，長(zhǎng)長(zhǎng)度為3，初始始值為26SQL>declare2v_agenumber(3):=26;3begin4commit;5end;6/PL/SQL過程程已成功完完成。3.4.4復(fù)合合數(shù)據(jù)類型型變量使用%type定義義變量為了讓PL/SQL中變量的的類型和數(shù)數(shù)據(jù)表中的的字段的數(shù)數(shù)據(jù)類型一一致，Oracle9i提提供了%type定定義方法。。這樣當(dāng)數(shù)數(shù)據(jù)表的字字段類型修修改后，PL/SQL程序中中相應(yīng)變量量的類型也也自動(dòng)修改改。【例3-6】該程序序定義了名名為v_sname的變量量，其類型型和student據(jù)表中中的stu_name字段段類型是一一致的。SQL>Declare2v_snamestudent.stu_name%type;3begin4commit;5end;6/PL/SQL過程程已成功完完成。自定義記錄錄類型變量量很多結(jié)構(gòu)化化程序設(shè)計(jì)計(jì)語(yǔ)言都提提供了記錄錄類型的數(shù)數(shù)據(jù)類型，，在PL/SQL中中，也支持持將多個(gè)基基本數(shù)據(jù)類類型捆綁在在一起的記記錄數(shù)據(jù)類類型?！纠?-7】程序代代碼定義了了名為stu_record_type的記記錄類型，，該記錄類類型由字符符型的sno、字符符型的name和整整型的age基本類類型變量組組成，stu_record是該類型型的變量，，引用記錄錄型變量的的方法是““記錄變量量名.基本本類型變量量名”。使用%rowtype屬性定定義記錄變變量使用%type可以以使變量獲獲得字段的的數(shù)據(jù)類型型，使用%rowtype可可以使變量量獲得整個(gè)個(gè)記錄的數(shù)數(shù)據(jù)類型。。該屬性可可以基于表表或視圖定定義記錄變變量。為了了簡(jiǎn)化表或或視圖所有有列數(shù)據(jù)的的處理，應(yīng)應(yīng)該使用該該屬性定義義記錄變量量?！纠?-8】執(zhí)行下下列PL/SQL程程序，程序序定義了名名為myrecord的復(fù)合合類型變量量，與student表結(jié)構(gòu)構(gòu)相同。SQL>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('請(qǐng)輸入正正確的學(xué)號(hào)號(hào)!');15END;16/3.4.5PL/SQL集合類型型索引表(PL/SQL表)PL/SQL表與其其他過程化化語(yǔ)言(如如C語(yǔ)言)的一維數(shù)數(shù)組類似。。需要注意意的是，高高級(jí)語(yǔ)言數(shù)數(shù)組的下標(biāo)標(biāo)不能為負(fù)負(fù)，但PL/SQL表的下下標(biāo)可以為為負(fù)值；高高級(jí)語(yǔ)言數(shù)數(shù)組的元素素個(gè)數(shù)有限限制，而PL/SQL表的的元素個(gè)數(shù)數(shù)沒有限制制，并且其其下標(biāo)沒有有上下限?！，F(xiàn)PL/SQL表表需要?jiǎng)?chuàng)建建一個(gè)數(shù)據(jù)據(jù)類型并另另外進(jìn)行變變量說明。。表類型變變量和數(shù)據(jù)據(jù)表是有區(qū)區(qū)別的，定定義表類型型變量的語(yǔ)語(yǔ)法如下：：Type<用戶戶自定義的的類型名稱稱>IsTableOf<索引引表元素?cái)?shù)數(shù)據(jù)類型>Indexby<索引引表元素下下標(biāo)的數(shù)據(jù)據(jù)類型;【例3-9】在索索引表中使使用數(shù)據(jù)類類型Binary_integerSQL>SETSERVEROUTPUTONSQL>Declare2TypeArray_typeis3TableOfNumber4IndexbyBinary_Integer;5My_ArrayArray_type;6Begin7ForIIn1..10Loop8My_Array(I):=I*2;9EndLoop;10ForIIn1..10Loop11Dbms_Output.Put_line(To_char(My_Array(I)));12EndLoop;13End;14/嵌套表嵌套表是嵌嵌在一張表表中記錄的的表。對(duì)保保存嵌套表表的表中的的每一列都都可以創(chuàng)建建一張存儲(chǔ)儲(chǔ)表。嵌套套表的每一一行都存儲(chǔ)儲(chǔ)在主表外外的存儲(chǔ)表表中。其格格式：type嵌嵌套表表名istableof元元素類型[notnull];嵌套表（NestedTable））類似于高高級(jí)語(yǔ)言中中的數(shù)組。。需要注意意的是，高高級(jí)語(yǔ)言數(shù)數(shù)組和嵌套套表的下標(biāo)標(biāo)都不能為為負(fù)值，高高級(jí)語(yǔ)言數(shù)數(shù)組的元素素個(gè)數(shù)有限限制，而嵌嵌套表的元元素個(gè)數(shù)沒沒有限制。。當(dāng)在表列中中使用嵌套套表時(shí)，必必須首先使使用CREATETYPE語(yǔ)句建立立嵌套表類類型。該嵌嵌套表類型型被存儲(chǔ)在在數(shù)據(jù)字典典中(user_type)?！纠?-11】為雇雇員信息建建立對(duì)象類類型emp_obj，而emp_array是是基于emp_obj的嵌套套表類型，，它可以用用于存儲(chǔ)多多個(gè)雇員信信息。SQL>createorreplacetypeemp_objasobject(2namevarchar2(10),3salarynumber(6,2),4hiredatedate);5/類型已創(chuàng)建建。SQL>createorreplacetypeemp_arrayistableofemp_obj;2/類型已創(chuàng)建建。SQL>createtabledepartment(2depnonumber(2),3dnamevarchar2(10),4employeeemp_array5)nestedtableemployeestoreasemployee;表已創(chuàng)創(chuàng)建。。createtable語(yǔ)語(yǔ)句句中包包含有有nestedtable子句句，指指明將將用來來存放放嵌套套表行行的存存儲(chǔ)表表的名名字為為employee。而而且，，對(duì)此此存儲(chǔ)儲(chǔ)表不不能直直接進(jìn)進(jìn)行訪訪問，，必須須通過過主表表才能能訪問問引存存儲(chǔ)表表中的的數(shù)據(jù)據(jù).存存儲(chǔ)儲(chǔ)表是是系統(tǒng)統(tǒng)生成成的表表，它它用來來存儲(chǔ)儲(chǔ)嵌套套表中中的實(shí)實(shí)際數(shù)數(shù)據(jù)，，這些些數(shù)據(jù)據(jù)不是是和表表中其其他列列的數(shù)數(shù)據(jù)共共同存存儲(chǔ)的的，而而是被被單獨(dú)獨(dú)存放放的。。變長(zhǎng)數(shù)數(shù)組(VARRAY)VARRAY也也是一一種用用于處處理PL/SQL數(shù)數(shù)組組的數(shù)數(shù)據(jù)類類型，，客觀觀存在在也可可以作作為表表列的的數(shù)據(jù)據(jù)類型型使用用。該該數(shù)據(jù)據(jù)類型型與高高級(jí)語(yǔ)語(yǔ)言數(shù)數(shù)組非非常類類似，，其元元素下下標(biāo)以以1開開始，，并且且元素素的最最大個(gè)個(gè)數(shù)是是有限限制的的。定定義變變長(zhǎng)數(shù)數(shù)組的的格式式：type類類型名名isvarry（（最大大尺寸寸)of元元素素類型型[notnull];當(dāng)在PL/SQL塊塊中中使用用varray變量量時(shí)，，必須須首先先使用用其構(gòu)構(gòu)造方方法來來初始始化varray變變量，，然后后才能能在PL/SQL塊塊內(nèi)引引用varray元元素。。下面面舉例例說明明使用用VARRAY的方方法：：SQL>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é)學(xué)生姓姓名:'||sname_table(1));8end;9/輸入no的的值:20026102原值6:wherestu_no=&no;新值6:wherestu_no=20026102;學(xué)生姓姓名:劉晨晨PL/SQL過過程程已成成功完完成。。3.5PL/SQL處處理理流程程在PL/SQL程序序中，，要使使程序序能按按照邏邏輯進(jìn)進(jìn)行處處理，，除了了有些些語(yǔ)句句是SQL語(yǔ)句句外，，還必必須有有能進(jìn)進(jìn)行邏邏輯控控制的的語(yǔ)句句。PL/SQL也也不不例外外，它它不僅僅可以以嵌入入SQL語(yǔ)語(yǔ)句，，而且且還支支持條條件分分支語(yǔ)語(yǔ)句（（IF，CASE））、循循環(huán)語(yǔ)語(yǔ)句（（LOOP）。。格式：IF<布爾爾表達(dá)式式>THENPL/SQL和和SQL語(yǔ)語(yǔ)句;[ELSE其它語(yǔ)句句;][ELSIF<其其它布爾爾表達(dá)式式>THEN其它語(yǔ)句句;ENDIF;3.5.1條條件分支支語(yǔ)句【例3-12】判斷斷兩個(gè)整整數(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/從Oracle9i開開始，不不僅可以以使用IF語(yǔ)句句，也可可以使用用CASE語(yǔ)句句來執(zhí)行行多重條條件分支支操作。。使用CASE語(yǔ)句更更加簡(jiǎn)捷捷，而且且執(zhí)行效效率也更更好。在CASE語(yǔ)句句中使用用單一選選擇符進(jìn)進(jìn)行等值值比較格式：CASE<條件選選擇符>WHEN<條件件值的表表達(dá)式1>THEN語(yǔ)語(yǔ)句1；WHEN<條件件值的表表達(dá)式2>THEN語(yǔ)語(yǔ)句1；……WHEN<條件件值的表表達(dá)式n>THEN語(yǔ)語(yǔ)句句1；[ELSE語(yǔ)語(yǔ)句n+1;]ENDCASE;3.5.2CASE語(yǔ)句句在CASE語(yǔ)句句中使用用多種條條件比較較格式：CASEWHEN<條件件表達(dá)式式1>THEN語(yǔ)語(yǔ)句句1；WHEN<條件件表達(dá)式式2>THEN語(yǔ)語(yǔ)句句1；……WHEN<條件件表達(dá)式式n>THEN語(yǔ)語(yǔ)句1；；[ELSE語(yǔ)語(yǔ)句n+1;]ENDCASE;基本循環(huán)Loop要執(zhí)行的語(yǔ)句句;exit[whencondition];endloop;當(dāng)使用基本循循環(huán)時(shí)，無論論是否滿足條條件，語(yǔ)句至至少會(huì)被執(zhí)行行一次。當(dāng)condition為TURE時(shí)，，會(huì)退出循環(huán)環(huán)，并執(zhí)行ENDLOOP后的相相應(yīng)操作。3.5.3循循環(huán)語(yǔ)句【例3-15】為stu2表表插入5條數(shù)數(shù)據(jù)(20046101…20046105)。SQL>createtablestu2(snoint);表已創(chuàng)建。SQL>declare2iint:=20048101;3begin4loop5insertintostu2values(i);6exitwheni=20048105;7i:=i+1;8endloop;9end;10/PL/SQL過程已成成功完成。WHILE循循環(huán)格式：while<布爾表表達(dá)式>loop要執(zhí)行的語(yǔ)句句;endloop;只有條件為真真時(shí)，才會(huì)執(zhí)執(zhí)行循環(huán)體內(nèi)內(nèi)的語(yǔ)句。FOR循環(huán)格式：FOR循循環(huán)控制變量量IN[REVERSE]下界值……上界值LOOPstatement1;statement2;ENDLOOP;當(dāng)使用FOR循環(huán)時(shí)，每每次循環(huán)時(shí)循循環(huán)控制變量量會(huì)自動(dòng)增一一；如果指定定REVERSE選項(xiàng)，，那么每次循循環(huán)時(shí)循環(huán)控控制變量會(huì)自自動(dòng)減一。3.6異異常處理一個(gè)優(yōu)秀的程程序都應(yīng)該能能夠正確處理理各種出錯(cuò)情情況，并盡可可能從錯(cuò)誤中中恢復(fù)。Oracle提提供異常情情況(EXCEPTION)和異常處理(EXCEPTIONHANDLER)來實(shí)現(xiàn)錯(cuò)誤處處理。雖然在PL/SQL編程程中，異常處處理不是必須須的，但建議議編程人員要要養(yǎng)成在PL/SQL編編程中指定相相應(yīng)的異常。。異常處理是用用來處理正常常執(zhí)行過程中中未預(yù)料的事事件，異常處處理包括預(yù)定定義的錯(cuò)誤和和自定義錯(cuò)誤誤。PL/SQL程序塊塊一旦產(chǎn)生異異常而沒有指指出如何處理理時(shí),程序就就會(huì)自動(dòng)終止止整個(gè)程序運(yùn)運(yùn)行。EXCEPTIONwhenexception1thenstatement1;whenexception2thenstatement2;……..whenothersthenstatement;END;其中：異常處處理可以按任任意次序排列列,但Others必必須放在最最后。3.6.1異異常處理概概念兩種類型的異異常：用戶定定義(user_define)異異常和預(yù)定定義(predefined)異常。當(dāng)使用預(yù)定義義異常處理時(shí)時(shí)，應(yīng)該了解解PL/SQL塊的常常見運(yùn)行錯(cuò)誤誤，并掌握與與之相關(guān)的預(yù)預(yù)定義異常處處理。3.6.2預(yù)預(yù)定義的異異常處理可以使用RAISE_APPLICATION_ERROR創(chuàng)建自自己的錯(cuò)誤處處理。其語(yǔ)法法如下：RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);其中：error_number是從––20,000到––20,999之間間的參數(shù)，error_message是相應(yīng)應(yīng)的提示信信息(<2048字節(jié))，keep_errors為為可選，如如果keep_errors=TRUE,則新錯(cuò)誤誤將被添加加到已經(jīng)引引發(fā)的錯(cuò)誤誤列表中。。如果keep_errors=FALSE(缺省),則新錯(cuò)誤誤將替換當(dāng)當(dāng)前的錯(cuò)誤誤列表。3.6.3用戶定定義的異常常處理3.7游游標(biāo)在PL/SQL中中處理查詢?cè)冋Z(yǔ)句和數(shù)數(shù)據(jù)操縱語(yǔ)語(yǔ)句時(shí)，Oracle必須須分配一片片叫上下文文(contextarea)的區(qū)域來來處理所必必需的信息息，其中包包括：要處理的行行的數(shù)目；；一個(gè)指針，，指向語(yǔ)句句被分析以以后的表示示形式；查詢的活動(dòng)動(dòng)集(activeset)。游標(biāo)是一個(gè)個(gè)指向上下下文的句柄柄(handle)或指針針。通過游游標(biāo)，PL/SQL可以控控制上下文文區(qū)和處理理語(yǔ)句時(shí)上上下文區(qū)會(huì)會(huì)發(fā)生的事事情。在PL/SQL程序序中定義的的游標(biāo)稱作作顯式游標(biāo)標(biāo)。顯式游游標(biāo)包括:定義游標(biāo)打開游標(biāo)提取數(shù)據(jù)（（也稱為推推進(jìn)游標(biāo)））關(guān)閉游標(biāo)3.7.1顯式游游標(biāo)定義游標(biāo)cursor游游標(biāo)名[（（參數(shù)名1數(shù)據(jù)類類型[,參參數(shù)2數(shù)數(shù)據(jù)類類型…]））]is查查詢語(yǔ)句;其中：游標(biāo)標(biāo)名是一個(gè)個(gè)沒有定義義的PL/SQL變變量，用用戶不能給給游標(biāo)名賦賦值。參數(shù)名是一一個(gè)在SELECT語(yǔ)句的WHERE條件中要要用到的參參數(shù)。SELECT語(yǔ)句是是一個(gè)不含含INTO子句的SELECT語(yǔ)句，，允許帶WHERE、ORDERBY、GROUPBY等子子句。打開游標(biāo)標(biāo)打開游標(biāo)標(biāo)是在SELECT語(yǔ)語(yǔ)句的所所有輸入入?yún)?shù)都都接受值值之后，，執(zhí)行游游標(biāo)所對(duì)對(duì)應(yīng)的SELECT語(yǔ)語(yǔ)句，將將其查詢?cè)兘Y(jié)果放放入工作作區(qū)，指指針指向向工作區(qū)區(qū)的首部部。假如如在打開開游標(biāo)時(shí)時(shí)查詢沒沒有返回回行，PL/SQL沒沒有觸觸發(fā)錯(cuò)誤誤，可以以在取數(shù)數(shù)據(jù)（FETCH）之之后檢測(cè)測(cè)游標(biāo)的的狀態(tài)。。open游游標(biāo)名稱稱[（實(shí)實(shí)際參數(shù)數(shù)值1[實(shí)際參參數(shù)值2…]））];提取數(shù)據(jù)在打開游標(biāo)之之后，SELECT語(yǔ)句句的結(jié)果被臨臨時(shí)存放到游游標(biāo)結(jié)果集中中，為了處理理結(jié)果的數(shù)據(jù)據(jù)，需要使用用FETCH語(yǔ)句提取游游標(biāo)數(shù)據(jù)。格式一：fetch游游標(biāo)名稱稱into變量量列表;格式二：fetch游游標(biāo)名稱稱bulkcollectinto集合變變量表[LIMTrows];關(guān)閉游標(biāo)close游游標(biāo)名稱稱;【例3-16】顯示示信息專業(yè)的的所有學(xué)生學(xué)學(xué)號(hào)及姓名。。（使用FETCH….INTO語(yǔ)語(yǔ)句）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/所有的的SQL語(yǔ)語(yǔ)句句在上上下文文區(qū)內(nèi)內(nèi)部都都是可可執(zhí)行行的，，因此此都有有一個(gè)個(gè)游標(biāo)標(biāo)指向向上下下文區(qū)區(qū)，此此游標(biāo)標(biāo)就是是所謂謂的SQL游標(biāo)標(biāo)(SQLcursor)，即即隱式式游標(biāo)標(biāo)。與與顯式式游標(biāo)標(biāo)不同同，SQL游游標(biāo)不不被程程序打打開和和關(guān)閉閉。當(dāng)一個(gè)個(gè)DML語(yǔ)語(yǔ)句執(zhí)執(zhí)行時(shí)時(shí)，PL/SQL內(nèi)內(nèi)部打打開一一個(gè)游游標(biāo)，，語(yǔ)句句的結(jié)結(jié)果被被保存存在4個(gè)游游標(biāo)屬屬性中中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游標(biāo)是是一塊塊包含含有查查詢信信息的的內(nèi)存存空間間。在在執(zhí)行行DML語(yǔ)語(yǔ)句，，游標(biāo)標(biāo)被打打開；；當(dāng)語(yǔ)語(yǔ)句完完成時(shí)時(shí)，游游標(biāo)被被關(guān)閉閉。3.7.2隱隱式游游標(biāo)【例3-20】更更新新學(xué)生生表，，如果果找到到學(xué)號(hào)號(hào)為’’20028104’的的學(xué)生生更新新學(xué)生生的年年齡，，否則則往學(xué)學(xué)生表表中插插入該該學(xué)生生的記記錄。。SQL>BEGIN2UPDATEstudent3SETstu_age=194WHEREstu_no='20028104';5--如果果更新沒有有匹配則插插入一新行行6IFSQL%NOTFOUNDTHEN7INSERTintoStudentVALUES('20028104','李濱濱',19,'男','信息息');8ENDIF;9commit;10END;11/游標(biāo)FOR循環(huán)是在在PL/SQL塊塊中使用游游標(biāo)最簡(jiǎn)單單的方式，，簡(jiǎn)化了對(duì)對(duì)游標(biāo)的處處理。當(dāng)使使用游標(biāo)FOR循環(huán)環(huán)時(shí)，Oracle會(huì)隱含含地打開游游標(biāo)、提取取游標(biāo)數(shù)據(jù)據(jù)并關(guān)閉游游標(biāo)。語(yǔ)法法如下：FORrecord_nameINcursor_nameLOOPStstement1;Ststement2;ENDLOOP;其中：record_name是Oracle隱含定定義的記錄錄變量名。。當(dāng)使用游標(biāo)標(biāo)開發(fā)PL/SQL應(yīng)用程程序時(shí)，為為了簡(jiǎn)化程程序代碼，，建議大家家使用游標(biāo)標(biāo)FOR循循環(huán)。3.7.3游標(biāo)FOR循環(huán)環(huán)【例3-21】給給課程名為為‘?dāng)?shù)據(jù)庫(kù)庫(kù)原理’的的所有學(xué)生生的成績(jī)加加5分。SQL>declare2cursorsc1_cursor3is4select*5fromscforupdate;6begin7dbms_output.put_line('課程程號(hào)學(xué)學(xué)號(hào)成成績(jī)績(jī)');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/ORACLE編寫的的程序一般般分為兩類類：存儲(chǔ)過程：：是可以完完成一定功功能的程序序叫存儲(chǔ)過過程；函數(shù)：是在在使用時(shí)給給出一個(gè)或或多個(gè)值，，處理完后后返回一個(gè)個(gè)或多個(gè)結(jié)結(jié)果的程序序叫函數(shù)；；這兩類程序序都存放在在Oracle數(shù)據(jù)據(jù)庫(kù)字典中中。3.8存儲(chǔ)儲(chǔ)過程和函函數(shù)與其它的數(shù)數(shù)據(jù)庫(kù)系統(tǒng)統(tǒng)一樣，Oracle的存儲(chǔ)儲(chǔ)過程是用用PL/SQL語(yǔ)言言編寫的能能完成一定定處理功能能的存儲(chǔ)在在數(shù)據(jù)庫(kù)字字典中的程程序。創(chuàng)建過程語(yǔ)語(yǔ)法:CREATE[ORREPLACE]PROCEDURE過程程名[(參數(shù)數(shù)名[{IN|INOUT}]數(shù)數(shù)據(jù)類類型….)]{IS|AS}PL/SQL塊3.8.1存儲(chǔ)儲(chǔ)過程建立無參數(shù)數(shù)的存儲(chǔ)過過程【例22】以下過過程用于輸輸出當(dāng)前系系統(tǒng)日期和和時(shí)間。SQL>CREATEORREPLACEPROCEDUREdata_time2IS3BEGIN4dbms_output.put_line(systimestamp);5END;6/過程已創(chuàng)建建。建立了存儲(chǔ)儲(chǔ)過程data_time之之后，就可可以調(diào)用該該過程。在在SQL*Plus環(huán)境中調(diào)調(diào)用過程有有兩種方法法：方法一：使使用execute命令調(diào)用用過程SQL>setserveroutputon;SQL>execdata_time;19-7月月-0509.08.36.312000000下下午+08:00PL/SQL過程程已成功完完成。方法二：使使用call命令調(diào)調(diào)用過程SQL>calldata_time();20-7月月-05002000000上上午+08:00調(diào)用完成。。建立帶有IN參數(shù)的的存儲(chǔ)過程程建立存儲(chǔ)過過程時(shí)，可可以通過使使用輸入?yún)?shù)，將應(yīng)應(yīng)用程序的的數(shù)據(jù)傳遞遞到過程中中，如果不指定定參數(shù)模式式，則默認(rèn)認(rèn)是輸入?yún)?shù)，可以使用IN關(guān)鍵字字顯示地定定義輸入?yún)?shù)。下面以建立立為選課表表SC插入入數(shù)據(jù)的存存儲(chǔ)過程add_sc為例，，說明建立立帶有輸入入?yún)?shù)的過過程的方法法?！纠?-23】建立為選課課表SC插插入數(shù)據(jù)的的存儲(chǔ)過程程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é)號(hào)號(hào)與課程號(hào)號(hào)不能重復(fù)復(fù)');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'學(xué)號(hào)號(hào)或課程號(hào)號(hào)不存在');14END;15/建立OUT參數(shù)的存存儲(chǔ)過程過程不僅可可以用來執(zhí)執(zhí)行特定操操作，而且且也可以用用于輸出數(shù)數(shù)據(jù)，在過過程中輸出出數(shù)據(jù)是使使用OUT或INOUT參參數(shù)來完完成的，當(dāng)當(dāng)定義輸出出參數(shù)時(shí)，，必須提供供OUT關(guān)關(guān)鍵字?！纠?-24】建建立用于于輸出某學(xué)學(xué)生某門課課的成績(jī)的的存儲(chǔ)過程程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é)學(xué)號(hào)或課程程號(hào)不存在在');13END;14/建立帶INOUT參數(shù)的存存儲(chǔ)過程定義過程時(shí)時(shí)，不僅可可以指定IN和OUT參數(shù)，，也可以指指定INOUT參參數(shù)。INOUT參數(shù)也也稱為輸入入輸出參數(shù)數(shù)，當(dāng)使用用這種參數(shù)數(shù)時(shí)，在調(diào)調(diào)用過程之之前需要通通過變量給給該參數(shù)傳傳遞數(shù)據(jù)。。在調(diào)用結(jié)結(jié)束后，Oracle會(huì)通通過該變量量將過程結(jié)結(jié)果傳遞給給應(yīng)用程序序?！纠?-25】將將一個(gè)7位數(shù)字的的電話號(hào)碼碼（如2217889轉(zhuǎn)換成成8位數(shù)數(shù)字的電話話號(hào)碼。轉(zhuǎn)轉(zhuǎn)換規(guī)則：：第一個(gè)數(shù)數(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;Oracle的函數(shù)數(shù)是一個(gè)獨(dú)獨(dú)有的對(duì)象象，它也是是由PL/SQL語(yǔ)語(yǔ)句編寫而而成。與存存儲(chǔ)過程不不同的是函函數(shù)必須返返回某些值值，而存儲(chǔ)儲(chǔ)過程可以以不返回任任何值。創(chuàng)創(chuàng)建函數(shù)的的語(yǔ)法如下下：CREATE[ORREPLACE]FUNCTION函函數(shù)名名[(argment[{in|inout}]TYPE,argment[{in|out|inout}]TYPE,…….]RETURNdatatype{IS|AS}PL/SQL塊；；3.8.2函數(shù)建立無參數(shù)數(shù)的函數(shù)當(dāng)建立函數(shù)數(shù)時(shí)，函數(shù)數(shù)可以帶有有參數(shù)，也也可以不帶帶參數(shù)?！纠?-26】建建立用于顯顯示當(dāng)前數(shù)數(shù)據(jù)庫(kù)用戶戶的函數(shù)。。（不帶任任何參數(shù)））SQL>CREATEORREPLACEFUNCTIONget_user2returnvarchar23AS4v_uservarchar2(100);5BEGIN6selectusernameintov_userfromuser_users;7returnv_user;8END;9/函數(shù)已創(chuàng)建建。建立帶IN參參數(shù)的函數(shù)建立函數(shù)時(shí)，，通過使用輸輸入?yún)?shù)，可可以將應(yīng)用程程序的數(shù)據(jù)傳傳遞到函數(shù)中中，最終通過過執(zhí)行函數(shù)可可以將結(jié)果返返回到應(yīng)用程程序中。【例3-27】創(chuàng)建函函數(shù)get_grade，實(shí)現(xiàn)輸入入學(xué)生的學(xué)號(hào)號(hào)及課程號(hào)返返回該門課的的成績(jī)。如果果學(xué)號(hào)及課程程號(hào)存在，顯顯示該信息。。（帶有IN參數(shù)）SQL>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é)號(hào)或課課程號(hào)不存在在');14END;建立帶OUT參數(shù)的函數(shù)數(shù)一般情況下，，函數(shù)只有一一個(gè)返回值，，如果希望使使用函數(shù)同時(shí)時(shí)返回多個(gè)值值，則需要使使用輸出參數(shù)數(shù)OUT?！纠?8】】輸入學(xué)生生的學(xué)號(hào)，建建立用于返回回學(xué)生的姓名名及所在的專專業(yè)的函數(shù)get_dept。（帶帶有OUT參參數(shù)）SQL>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é)號(hào)不存存在');14END;存儲(chǔ)過程、函函數(shù)是存儲(chǔ)在在數(shù)據(jù)字典中中的對(duì)象，它它們是屬于某某一數(shù)據(jù)庫(kù)用用戶的。用戶戶對(duì)其所擁有有的對(duì)象可以以進(jìn)行任何操操作，其他用用戶在被授予予了合適的權(quán)權(quán)限以后也可可以訪問這些些對(duì)象。如果調(diào)試正確確的存儲(chǔ)過程程沒有進(jìn)行授授權(quán)，那就只只有建立者本本人才可以運(yùn)運(yùn)行。所以作作為應(yīng)用系統(tǒng)統(tǒng)的一部分的的存儲(chǔ)過程也也必須進(jìn)行授授權(quán)才能達(dá)到到要求?？煽梢杂肎RANT命令來來進(jìn)行存儲(chǔ)過過程的運(yùn)行授授權(quán)。GRANT語(yǔ)語(yǔ)法：GRANTsystem_privilege|roleTOuser|role|PUBLIC[WITHADMINOPTION]GRANTobject_privilege|ALLcolumnONschema.objectFROMuser|role|PUBLICWITHGRANTOPTION3.8.3過過程和函函數(shù)的安全性性【例29】】假定表student是用戶scott的私私有表，用戶戶personal是開開發(fā)者，最終終用戶是green?，F(xiàn)現(xiàn)要求green只能通通過personal創(chuàng)創(chuàng)建的過程add_stu存取scott的student表。該過過程插入學(xué)生生的記錄。如如何實(shí)現(xiàn)？（1）首先在在scott用戶環(huán)境下下，為用戶personal授于對(duì)對(duì)student表操作作所需的相應(yīng)應(yīng)權(quán)限。SQL>grantselect,insert,update,deleteONstudenttopersonal;授權(quán)成功。注意：如果某某個(gè)用戶沒有有權(quán)限來創(chuàng)建建存儲(chǔ)過程，，則需要DBA將創(chuàng)建過過程的權(quán)限授授予某用戶。。由于personal用戶沒有創(chuàng)創(chuàng)建存儲(chǔ)過程程的權(quán)限，必必須以DBA的身份為personal用戶建建立創(chuàng)建存儲(chǔ)儲(chǔ)過程的權(quán)限限。否則personal用戶沒法法創(chuàng)建存儲(chǔ)過過程的權(quán)限。。（2）personal擁有這些權(quán)權(quán)限后，就可可以建立存儲(chǔ)儲(chǔ)過程。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é)號(hào)號(hào)不能重復(fù)');12WHENe_integrityTHEN13RAISE_APPLICATION_ERROR(-20001,'學(xué)號(hào)號(hào)不存在');14END;15/（3）進(jìn)行授授權(quán)SQL>grantexecuteonadd_stutogreen;授權(quán)成功。（4）Green用戶就就可以對(duì)personal用戶所建建的存儲(chǔ)過程程調(diào)用了。示示例如下：SQL>execpersonal.add_stu('20026121','李琳琳');PL/SQL過程已成成功完成。提高數(shù)據(jù)的安安全性與完整整性利用安全性的的權(quán)限來控制制那些沒有足足夠權(quán)限用戶戶對(duì)數(shù)據(jù)庫(kù)的的間接訪問。。通過把相關(guān)關(guān)聯(lián)的表的操操作集中到一一起，保證對(duì)對(duì)這些相關(guān)聯(lián)聯(lián)的表執(zhí)行一一致的操作，，或者任何操操作都不做。。改善操作性能能多個(gè)用戶使用用同一個(gè)SQL語(yǔ)句時(shí)，，只需做一次次語(yǔ)法分析，，只在編譯時(shí)時(shí)進(jìn)行語(yǔ)法分分析，運(yùn)行時(shí)時(shí)不再重做，，可以直接調(diào)調(diào)用緩存中的的編譯代碼。。3.8.4過過程和函函數(shù)的優(yōu)點(diǎn)3．9觸發(fā)發(fā)器觸發(fā)器是許多多關(guān)系數(shù)據(jù)庫(kù)庫(kù)系統(tǒng)都提供供的一項(xiàng)技術(shù)術(shù)。在oracle系統(tǒng)統(tǒng)里，觸發(fā)器器類似過程和和函數(shù)，都有有聲明，執(zhí)行行和異常處理理過程的PL/SQL塊塊。觸發(fā)器在數(shù)據(jù)據(jù)庫(kù)里以獨(dú)立立的對(duì)象存儲(chǔ)儲(chǔ)，它與存儲(chǔ)儲(chǔ)過程不同的的是存儲(chǔ)過程程通過其它程程序來啟動(dòng)運(yùn)運(yùn)行或直接啟啟動(dòng)運(yùn)行，而而觸發(fā)器是由由一個(gè)事件來來啟動(dòng)運(yùn)行，，觸發(fā)器是當(dāng)當(dāng)某個(gè)事件發(fā)發(fā)生時(shí)自動(dòng)地地隱式運(yùn)行，，并且觸發(fā)器器不能接收參參數(shù)。所以運(yùn)運(yùn)行觸發(fā)器就就叫觸發(fā)或點(diǎn)點(diǎn)火（firing）。。在Oracle里，，觸發(fā)發(fā)器事事件指指的是是對(duì)數(shù)數(shù)據(jù)庫(kù)庫(kù)的表表進(jìn)行行的INSERT、、UPDATE及DELETE操作或或?qū)σ曇晥D進(jìn)進(jìn)行類類似的的操作作。DML觸觸發(fā)器器Oracle可可以在在DML語(yǔ)語(yǔ)句進(jìn)進(jìn)行觸觸發(fā)，，可以以在DML操作作前或或操作作后進(jìn)進(jìn)行觸觸發(fā)，，并且且可以以對(duì)每每個(gè)行行或語(yǔ)語(yǔ)句操操作上上進(jìn)行行觸發(fā)發(fā)。替代觸觸發(fā)器器在Oracle里，，不能能直接接對(duì)由由兩個(gè)個(gè)以上上的表表建立立的視視圖進(jìn)進(jìn)行操操作，，所以以給出出了替替代觸觸發(fā)器器。系統(tǒng)觸觸發(fā)器器系統(tǒng)觸觸發(fā)器器是在在Oracle數(shù)據(jù)據(jù)庫(kù)系系統(tǒng)的的事件件中進(jìn)進(jìn)行觸觸發(fā)，，如Oracle系系統(tǒng)的的啟動(dòng)動(dòng)與關(guān)關(guān)閉等等。管理觸觸發(fā)器器Oracle提提供供了顯顯示觸觸發(fā)器器信息息、禁禁止觸觸發(fā)器器和和激活活觸發(fā)發(fā)器等等功能能。3.9.1觸觸發(fā)發(fā)器類類型創(chuàng)建觸觸發(fā)器器的一一般語(yǔ)語(yǔ)法是是：CREATE[ORREPLACE]TRIGGER觸觸發(fā)發(fā)器名名[BEFORE|AFTER]eventONtable_reference[FOREACHROW[WHENtrigger_condition]]trigger_body;3.9.2DML觸發(fā)發(fā)器建立BEFORE語(yǔ)語(yǔ)句觸觸發(fā)器器【例3-30】建建立立一個(gè)個(gè)行級(jí)級(jí)觸發(fā)發(fā)器，，當(dāng)選選課表表被刪刪除一一條記記錄時(shí)時(shí)，把把被刪刪除記記錄寫寫到選選課表表刪除除日志志表中中去。。（1））首先先創(chuàng)建建一個(gè)個(gè)日志志表sc.hisSQL>createtableSC_his(2Snochar(8),3Cnochar(3),4Gradenumber(3));表已創(chuàng)創(chuàng)建。。（2））創(chuàng)建建一個(gè)個(gè)行級(jí)級(jí)觸發(fā)發(fā)器。。SQL>createorreplacetriggerscott.del_SC2beforedeleteonscott.SCforeachrow3begin4--將將修改改前數(shù)據(jù)插插入到日志志記錄表del_emp,以供監(jiān)監(jiān)督使用。。5insertintosc_his(sno,cno,grade)6values(:old.stu_no,:old.cou_no,:old.grade);6end;8/（3）測(cè)試試：SQL>deletesc2wherestu_no='20026102'andcou_no='a03';已刪除1行。SQL>select*fromsc_his;SNOCNOGRADE---------------------------------20026102a0389在行級(jí)觸發(fā)發(fā)器中，在在列名前加加上:old標(biāo)識(shí)符符標(biāo)識(shí)該列列變化前的的值，加上上:new標(biāo)識(shí)符標(biāo)標(biāo)識(shí)變化后后的值。使用條件謂謂詞ORACLE提供供三個(gè)參數(shù)數(shù)INSERTING，UPDATEING，DELETING用于于判斷觸發(fā)發(fā)了哪些操操作。謂詞詞行為如下下：INSERTING：如果觸觸發(fā)語(yǔ)句是是INSERT語(yǔ)語(yǔ)句，則則為TRUE,否則則為FALSEUPDATING：：如果觸發(fā)發(fā)語(yǔ)句是UPDATE語(yǔ)句句，則為TRUE,否則為FALSEDELETING：：如果觸發(fā)發(fā)語(yǔ)句是DELETE語(yǔ)語(yǔ)句，則為為TRUE,否則為為FALSE【例3-31】創(chuàng)創(chuàng)建一個(gè)個(gè)包含多個(gè)個(gè)觸發(fā)器事事件的觸發(fā)發(fā)器，禁止止工作人員員在星期六六及星期日日插入、刪刪除或更改改雇員的信信息。SQL>createorreplacetriggertri_emp2beforeinsertorupdateordeleteonemp3begin4ifto_char(sysdate,'DY','nls_date_language=AMERICAN')IN('SAT','SUN')then5case6wheninsertingthen7raise_application_error(-20001,'不能在休休息日增加雇雇員');8whenupdatingthen9raise_application_error(-20001,'不