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

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

2、去了數(shù)據(jù)在網(wǎng)上的傳輸時間。 適合于客戶環(huán)境適合于客戶環(huán)境由于由于PL/SQL分為數(shù)據(jù)庫分為數(shù)據(jù)庫PL/SQL部分和工具部分和工具PL/SQL。對于客戶端來說，對于客戶端來說，PL/SQL可以嵌套到相應(yīng)的工具中，可以嵌套到相應(yīng)的工具中，客戶端程序可以執(zhí)行本地包含客戶端程序可以執(zhí)行本地包含PL/SQL部分，也可以向部分，也可以向服務(wù)發(fā)服務(wù)發(fā)SQL命令或激活服務(wù)器端的命令或激活服務(wù)器端的PL/SQL程序運行。程序運行。3.2 運行運行PL/SQL程序程序 PL/SQL程序的運行是通過程序的運行是通過Oracle中的一個引擎中的一個引擎來進(jìn)行的。這個引擎可能在來進(jìn)行的。這個引擎可能在Oracle的服務(wù)

3、器端，的服務(wù)器端，也可能在也可能在 Oracle 應(yīng)用開發(fā)的客戶端。引擎執(zhí)行應(yīng)用開發(fā)的客戶端。引擎執(zhí)行PL/SQL中的過程性語句，然后將中的過程性語句，然后將SQL語句發(fā)送語句發(fā)送給數(shù)據(jù)庫服務(wù)器來執(zhí)行，再將結(jié)果返回給執(zhí)行端。給數(shù)據(jù)庫服務(wù)器來執(zhí)行，再將結(jié)果返回給執(zhí)行端。例如，如果應(yīng)用程序需要取得學(xué)生的成績，那例如，如果應(yīng)用程序需要取得學(xué)生的成績，那么可以建立函數(shù)實現(xiàn)該項功能。么可以建立函數(shù)實現(xiàn)該項功能。SQL create function get_grade1(sno char,cno char) 2 return number is 3 V_grade number(3); 4 begin

4、5 select grade 6 into V_grade 7 from sc 8 where stu_no=sno and cou_no=cno; 9 return V_grade; 10 end; 11 /函數(shù)已創(chuàng)建。函數(shù)已創(chuàng)建。SQL var v_grade numberSQL exec :v_grade:=get_grade1(20026101,a02)PL/SQL 過程已成功完成。過程已成功完成。SQL print v_grade3.3 PL/SQL塊結(jié)構(gòu)塊結(jié)構(gòu) PL/SQL程序由三個塊組成，即程序由三個塊組成，即 聲明部分、執(zhí)行部分、異常聲明部分、執(zhí)行部分、異常處理部分。處理部分。

5、PL/SQL塊的結(jié)構(gòu)如下：塊的結(jié)構(gòu)如下：Declare /* 聲明部分聲明部分: 在此在此 聲明聲明PL/SQL用到的變量用到的變量,類型及光標(biāo)類型及光標(biāo) */Begin /* 執(zhí)行部分執(zhí)行部分: 過程及過程及SQL 語句語句 , 即程序的主要部分即程序的主要部分 */Exception /* 執(zhí)行異常部分執(zhí)行異常部分: 錯誤處理錯誤處理 */End;其中其中 執(zhí)行部分是必須的。而執(zhí)行部分是必須的。而END則是則是PL/SQL 塊的塊的結(jié)束標(biāo)記。結(jié)束標(biāo)記。需要注意的是需要注意的是DECLARE，BEGIN，EXCEPTION后后面沒有分號（；），而面沒有分號（；），而END后則必須要帶有分號。

6、后則必須要帶有分號。PL/SQL標(biāo)識符的命名規(guī)則：標(biāo)識符的命名規(guī)則： 標(biāo)識符的最大長度是標(biāo)識符的最大長度是30個字符，包括字母、數(shù)字、個字符，包括字母、數(shù)字、$、_、# ；不可包含；不可包含保留字；要以字來打頭；不能和同一塊中的表中的保留字；要以字來打頭；不能和同一塊中的表中的字段名一樣。字段名一樣?！纠纠?-13-1】只包含執(zhí)行部分的只包含執(zhí)行部分的PL/SQLPL/SQL塊塊SQL set SQL set serveroutputserveroutput on onSQL beginSQL begin 2 2 dbmsdbms_output.put_line(Welcome!);_out

7、put.put_line(Welcome!); 3 end; 3 end; 4 / 4 /Welcome!Welcome!PL/SQL PL/SQL 過程已成功完成。過程已成功完成。 注意：當(dāng)使用注意：當(dāng)使用dbmsdbms_output._output.包輸出數(shù)據(jù)或消息時，必須包輸出數(shù)據(jù)或消息時，必須要將要將SQLSQL* *PlusPlus的環(huán)境變量的環(huán)境變量serveroutputserveroutput 設(shè)置為設(shè)置為on.on.【例【例3-2】包含定義部分和執(zhí)行部分的包含定義部分和執(zhí)行部分的PL/SQL塊塊SQL DECLARE 2 v_sname VARCHAR(10); 3 BEG

8、IN 4 select stu_name INTO v_sname FROM student 5 WHERE stu_no=&no; 6 dbms_output.put_line(學(xué)生姓名：學(xué)生姓名：|v_sname); 7 END; 8 /輸入輸入 no 的值的值: 20026101原值原值 5: WHERE stu_no=&no;新值新值 5: WHERE stu_no=20026101;學(xué)生姓名：李勇學(xué)生姓名：李勇PL/SQL 過程已成功完成。過程已成功完成。注意：該例中當(dāng)執(zhí)行該注意：該例中當(dāng)執(zhí)行該PL/SQL時，會根據(jù)輸入的學(xué)號顯時，會根據(jù)輸入的學(xué)號顯示學(xué)生姓名。為了臨

9、時存放姓名，就必須定義變量。示學(xué)生姓名。為了臨時存放姓名，就必須定義變量。 &no為為SQL*Plus的替代變量。的替代變量。3.4 PL/SQL基本語法基本語法3.4.1 常量與變量常量與變量 定義常量的語法格式：定義常量的語法格式：常量名常量名 constant 類型標(biāo)識符類型標(biāo)識符 not null:=值值; 常量包括后面的變量名都必須以字母開頭，不能常量包括后面的變量名都必須以字母開頭，不能有空格，不能超過有空格，不能超過30個字符長度，同時不能和保個字符長度，同時不能和保留字同名，常（變）量名稱不區(qū)分大小寫，在字留字同名，常（變）量名稱不區(qū)分大小寫，在字母后面可以帶數(shù)字或特殊

10、字符。括號內(nèi)的母后面可以帶數(shù)字或特殊字符。括號內(nèi)的not null為可選參數(shù)，若選用，表明該常（變）量不能為為可選參數(shù)，若選用，表明該常（變）量不能為空值?？罩怠！纠纠?-4】常量定義常量定義 SQL declare 2 pi constant number(9):=3.1415926; 3 begin 4 commit; 5 end; 6 /PL/SQL 過程已成功完成。過程已成功完成。3.4.2 基本數(shù)據(jù)類型變量基本數(shù)據(jù)類型變量 PL/SQL中常用的基本數(shù)據(jù)類型中常用的基本數(shù)據(jù)類型3.4.3 基本數(shù)據(jù)類型變量的定義方法基本數(shù)據(jù)類型變量的定義方法 變量名變量名 類型標(biāo)識符類型標(biāo)識符 not

11、 null:=值值;【例【例3-5】 程序定義了名為程序定義了名為age的數(shù)字型變量，長度為的數(shù)字型變量，長度為3，初始值為初始值為26SQL declare 2 v_age number(3):=26; 3 begin 4 commit; 5 end; 6 /PL/SQL 過程已成功完成。過程已成功完成。3.4.4 復(fù)合數(shù)據(jù)類型變量復(fù)合數(shù)據(jù)類型變量 使用使用%type定義變量定義變量為了讓為了讓PL/SQL中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)中變量的類型和數(shù)據(jù)表中的字段的數(shù)據(jù)類型一致，類型一致，Oracle 9i提供了提供了%type定義方法。這樣當(dāng)定義方法。這樣當(dāng)數(shù)據(jù)表的字段類型修改后，數(shù)據(jù)

12、表的字段類型修改后，PL/SQL程序中相應(yīng)變量的程序中相應(yīng)變量的類型也自動修改。類型也自動修改?！纠纠?-6】該程序定義了名為該程序定義了名為 v_sname的變量，其類型的變量，其類型和和 student據(jù)表中的據(jù)表中的 stu_name字段類型是一致的。字段類型是一致的。SQL Declare 2 v_sname student.stu_name%type; 3 begin 4 commit; 5 end; 6 /PL/SQL 過程已成功完成。過程已成功完成。 自定義記錄類型變量自定義記錄類型變量很多結(jié)構(gòu)化程序設(shè)計語言都提供了記錄類型的數(shù)據(jù)類很多結(jié)構(gòu)化程序設(shè)計語言都提供了記錄類型的數(shù)據(jù)類

13、型，型，在在PL/SQL中，也支持將多個基本數(shù)據(jù)類型捆綁在中，也支持將多個基本數(shù)據(jù)類型捆綁在一起的記錄數(shù)據(jù)類型。一起的記錄數(shù)據(jù)類型?！纠?-7】程序代碼定義了名為程序代碼定義了名為 stu_record_type的記的記錄類型，該記錄類型由字符型的錄類型，該記錄類型由字符型的sno、字符型的字符型的name和整型的和整型的age基本類型變量組成，基本類型變量組成，stu_record是該類是該類型的變量，引用記錄型變量的方法是型的變量，引用記錄型變量的方法是“記錄變量名記錄變量名.基基本類型變量名本類型變量名”。使用使用%rowtype屬性定義記錄變量屬性定義記錄變量使用使用%type可以使

14、變量獲得字段的數(shù)據(jù)類型，使用可以使變量獲得字段的數(shù)據(jù)類型，使用%rowtype可以使變量獲得整個記錄的數(shù)據(jù)類型。該屬可以使變量獲得整個記錄的數(shù)據(jù)類型。該屬性可以基于表或視圖定義記錄變量。為了簡化表或視圖性可以基于表或視圖定義記錄變量。為了簡化表或視圖所有列數(shù)據(jù)的處理，應(yīng)該使用該屬性定義記錄變量。所有列數(shù)據(jù)的處理，應(yīng)該使用該屬性定義記錄變量?！纠纠?-8】執(zhí)行下列執(zhí)行下列PL/SQL程序，程序定義了名為程序，程序定義了名為myrecord的復(fù)合類型變量，與的復(fù)合類型變量，與student表結(jié)構(gòu)相同。表結(jié)構(gòu)相同。SQL DECLARE 2 myrecord student%rowtype; 3

15、BEGIN 4 select * 5 into myrecord 6 from student 7 where stu_no=&no; 8 dbms_output.put_line(姓名姓名:|myrecord.stu_name); 9 dbms_output.put_line(年齡年齡:|myrecord.stu_age); 10 dbms_output.put_line(性別性別:|myrecord.stu_sex); 11 dbms_output.put_line(專業(yè)專業(yè):|myrecord.stu_dept); 12 EXCEPTION 13 WHEN NO_DATA_FOU

16、ND THEN 14 dbms_output.put_line(請輸入正確的學(xué)號請輸入正確的學(xué)號!); 15 END; 16 /3.4.5 PL/SQL集合類型集合類型 索引表索引表(PL/SQL表表)PL/SQL表與其他過程化語言表與其他過程化語言(如如C語言語言)的一維數(shù)組類似。的一維數(shù)組類似。需要注意的是，高級語言數(shù)組的下標(biāo)不能為負(fù)，但需要注意的是，高級語言數(shù)組的下標(biāo)不能為負(fù)，但PL/SQL 表的下標(biāo)可以為負(fù)值；高級語言數(shù)組的元素個表的下標(biāo)可以為負(fù)值；高級語言數(shù)組的元素個數(shù)有限制，而數(shù)有限制，而PL/SQL 表的元素個數(shù)沒有限制，并且其表的元素個數(shù)沒有限制，并且其下標(biāo)沒有上下限?，F(xiàn)下標(biāo)沒

17、有上下限?，F(xiàn)PL/SQL表需要創(chuàng)建一個數(shù)據(jù)類型并表需要創(chuàng)建一個數(shù)據(jù)類型并另外進(jìn)行變量說明。表類型變量和數(shù)據(jù)表是有區(qū)別的，另外進(jìn)行變量說明。表類型變量和數(shù)據(jù)表是有區(qū)別的，定義表類型變量的語法如下：定義表類型變量的語法如下：Type Is Table Of Index by SET SERVEROUTPUT ONSQL Declare 2 Type Array_type is 3 Table Of Number 4 Index by Binary_Integer; 5 My_Array Array_type; 6 Begin 7 For I In 1.10 Loop 8 My_Array(I) :

18、= I*2; 9 End Loop; 10 For I In 1.10 Loop 11 Dbms_Output.Put_line(To_char(My_Array(I); 12 End Loop; 13 End; 14 / 嵌套表嵌套表嵌套表是嵌在一張表中記錄的表。對保存嵌套表的表中的嵌套表是嵌在一張表中記錄的表。對保存嵌套表的表中的每一列都可以創(chuàng)建一張存儲表。嵌套表的每一行都存儲在每一列都可以創(chuàng)建一張存儲表。嵌套表的每一行都存儲在主表外的存儲表中。其格式：主表外的存儲表中。其格式： type 嵌套表名嵌套表名 is table of 元素類型元素類型 not null;嵌套表（嵌套表（Nes

19、ted Table）類似于高級語言中的數(shù)組。需要類似于高級語言中的數(shù)組。需要注意的是，高級語言數(shù)組和嵌套表的下標(biāo)都不能為負(fù)值，注意的是，高級語言數(shù)組和嵌套表的下標(biāo)都不能為負(fù)值，高級語言數(shù)組的元素個數(shù)有限制，而嵌套表的元素個數(shù)沒高級語言數(shù)組的元素個數(shù)有限制，而嵌套表的元素個數(shù)沒有限制。有限制。當(dāng)在表列中使用嵌套表時，必須首先使用當(dāng)在表列中使用嵌套表時，必須首先使用CREATE TYPE語句建立嵌套表類型。該嵌套表類型被存儲在數(shù)據(jù)字典中語句建立嵌套表類型。該嵌套表類型被存儲在數(shù)據(jù)字典中(user_type)。【例【例3-11】為雇員信息建立對象類型為雇員信息建立對象類型emp_obj，而而emp_

20、array是基于是基于emp_obj的嵌套表類型，它可以用于的嵌套表類型，它可以用于存儲多個雇員信息。存儲多個雇員信息。SQL create or replace type emp_obj as object ( 2 name varchar2(10), 3 salary number(6,2), 4 hiredate date ); 5 /類型已創(chuàng)建。類型已創(chuàng)建。SQL create or replace type emp_array is table of emp_obj; 2 /類型已創(chuàng)建。類型已創(chuàng)建。SQL create table department ( 2 depno numbe

21、r(2), 3 dname varchar2(10), 4 employee emp_array 5 ) nested table employee store as employee;表已創(chuàng)建。表已創(chuàng)建。create table 語句中包含有語句中包含有nested table子句，指明將用子句，指明將用來存放嵌套表行的存儲表的名字為來存放嵌套表行的存儲表的名字為employee。而且，對而且，對此存儲表不能直接進(jìn)行訪問，必須通過主表才能訪問引存此存儲表不能直接進(jìn)行訪問，必須通過主表才能訪問引存儲表中的數(shù)據(jù)儲表中的數(shù)據(jù). 存儲表是系統(tǒng)生成的表，它用來存儲嵌套存儲表是系統(tǒng)生成的表，它用來存儲嵌

22、套表中的實際數(shù)據(jù)，這些數(shù)據(jù)不是和表中其他列的數(shù)據(jù)共同表中的實際數(shù)據(jù)，這些數(shù)據(jù)不是和表中其他列的數(shù)據(jù)共同存儲的，而是被單獨存放的。存儲的，而是被單獨存放的。 變長數(shù)組變長數(shù)組(VARRAY)VARRAY也是一種用于處理也是一種用于處理PL/SQL 數(shù)組的數(shù)據(jù)類型，客數(shù)組的數(shù)據(jù)類型，客觀存在也可以作為表列的數(shù)據(jù)類型使用。該數(shù)據(jù)類型與高觀存在也可以作為表列的數(shù)據(jù)類型使用。該數(shù)據(jù)類型與高級語言數(shù)組非常類似，其元素下標(biāo)以級語言數(shù)組非常類似，其元素下標(biāo)以1開始，并且元素的開始，并且元素的最大個數(shù)是有限制的。定義變長數(shù)組的格式：最大個數(shù)是有限制的。定義變長數(shù)組的格式： type 類型名類型名 is varr

23、y（最大尺寸最大尺寸) of 元素類型元素類型 not null; 當(dāng)在當(dāng)在PL/SQL 塊中使用塊中使用varray變量時，必須首先使用其變量時，必須首先使用其構(gòu)造方法來初始化構(gòu)造方法來初始化varray變量，然后才能在變量，然后才能在PL/SQL塊內(nèi)塊內(nèi)引用引用varray元素。下面舉例說明使用元素。下面舉例說明使用VARRAY的方法：的方法：SQL declare 2 type sname_table_type is varray(10) of student.stu_name%TYPE; 3 sname_table sname_table_type:=sname_table_type(

24、lin); 4 begin 5 select stu_name into sname_table(1) from student 6 where stu_no=&no; 7 dbms_output.put_line(學(xué)生姓名學(xué)生姓名:|sname_table(1); 8 end; 9 /輸入輸入 no 的值的值: 20026102原值原值 6: where stu_no=&no;新值新值 6: where stu_no=20026102;學(xué)生姓名學(xué)生姓名:劉晨劉晨PL/SQL 過程已成功完成。過程已成功完成。3.5 PL/SQL 處理流程處理流程 在在PL/SQL程序中，要使程

25、序能按照邏輯進(jìn)行處程序中，要使程序能按照邏輯進(jìn)行處理，除了有些語句是理，除了有些語句是SQL語句外，還必須有能進(jìn)語句外，還必須有能進(jìn)行邏輯控制的語句。行邏輯控制的語句。PL/SQL 也不例外，它不僅也不例外，它不僅可以嵌入可以嵌入SQL語句，而且還支持條件分支語句語句，而且還支持條件分支語句（IF，CASE）、）、循環(huán)語句（循環(huán)語句（LOOP）。）。 格式：格式：IF THENPL/SQL 和和 SQL語句語句;ELSE其它語句其它語句;ELSIF THEN其它語句其它語句;END IF;3.5.1 條件分支語句條件分支語句【例【例 3-12】判斷兩個整數(shù)變量的大小，輸出不同的結(jié)果。判斷兩個整

26、數(shù)變量的大小，輸出不同的結(jié)果。SQL set serveroutput onSQL declare 2 number1 integer:=80; 3 number2 integer:=90; 4 begin 5 if number1=number2 then 6 if number1=number2 then 7 dbms_output.put_line(number1等于等于number2); 8 else 9 dbms_output.put_line(number1小于小于number2); 10 end if; 11 else 12 dbms_output.put_line(number

27、1大于大于number2); 13 end if; 14 end; 15 / 從從Oracle9i開始，不僅可以使用開始，不僅可以使用IF語句，也可以使語句，也可以使用用CASE語句來執(zhí)行多重條件分支操作。使用語句來執(zhí)行多重條件分支操作。使用CASE語句更加簡捷，而且執(zhí)行效率也更好。語句更加簡捷，而且執(zhí)行效率也更好。 在在CASE語句中使用單一選擇符進(jìn)行等值比較語句中使用單一選擇符進(jìn)行等值比較格式：格式： CASE WHEN THEN 語句語句1；WHEN THEN 語句語句1；WHEN THEN 語句語句1；ELSE 語句語句n+1; END CASE;3.5.2 CASE語句語句在在CAS

28、E語句中使用多種條件比較語句中使用多種條件比較 格式：格式： CASE WHEN THEN 語句語句1；WHEN THEN 語句語句1；WHEN THEN 語句語句1；ELSE 語句語句n+1; END CASE; 基本循環(huán)基本循環(huán)Loop要執(zhí)行的語句要執(zhí)行的語句;exit when condition;end loop;當(dāng)使用基本循環(huán)時，無論是否滿足條件，語句至少會當(dāng)使用基本循環(huán)時，無論是否滿足條件，語句至少會被執(zhí)行一次。當(dāng)被執(zhí)行一次。當(dāng)condition為為TURE時，會退出循環(huán)，時，會退出循環(huán)，并執(zhí)行并執(zhí)行END LOOP后的相應(yīng)操作。后的相應(yīng)操作。3.5.3 循環(huán)語句循環(huán)語句【例例 3

29、-15】 為為stu2 表插入表插入5條數(shù)據(jù)條數(shù)據(jù)(2004610120046105)。 SQL create table stu2(sno int);表已創(chuàng)建。表已創(chuàng)建。SQL declare 2 i int:=20048101; 3 begin 4 loop 5 insert into stu2 values(i); 6 exit when i=20048105; 7 i:=i+1; 8 end loop; 9 end; 10 /PL/SQL 過程已成功完成。過程已成功完成。 WHILE 循環(huán)循環(huán)格式：格式：while loop 要執(zhí)行的語句要執(zhí)行的語句;end loop;只有條件為真時，

30、才會執(zhí)行循環(huán)體內(nèi)的語句。只有條件為真時，才會執(zhí)行循環(huán)體內(nèi)的語句。 FOR循環(huán)循環(huán) 格式：格式： FOR 循環(huán)控制變量循環(huán)控制變量 IN REVERSE 下界值下界值 上界值上界值 LOOP statement1; statement2; END LOOP; 當(dāng)使用當(dāng)使用FOR循環(huán)時，每次循環(huán)時循環(huán)控制變量會自循環(huán)時，每次循環(huán)時循環(huán)控制變量會自動增一；如果指定動增一；如果指定REVERSE選項，那么每次循環(huán)時選項，那么每次循環(huán)時循環(huán)控制變量會自動減一。循環(huán)控制變量會自動減一。3.6 異常處理異常處理 一個優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯情一個優(yōu)秀的程序都應(yīng)該能夠正確處理各種出錯情況，并盡可能

31、從錯誤中恢復(fù)。況，并盡可能從錯誤中恢復(fù)。Oracle 提供異常情提供異常情況況(EXCEPTION)和異常處理和異常處理(EXCEPTION HANDLER)來實來實現(xiàn)錯誤處理?，F(xiàn)錯誤處理。 雖然在雖然在PL/SQL編程中，異常處理不是必須的，編程中，異常處理不是必須的，但建議編程人員要養(yǎng)成在但建議編程人員要養(yǎng)成在PL/SQL編程中指定相編程中指定相應(yīng)的異常。應(yīng)的異常。 異常處理是用來處理正常執(zhí)行過程中未預(yù)料的事異常處理是用來處理正常執(zhí)行過程中未預(yù)料的事件，異常處理包括預(yù)定義的錯誤和自定義錯誤。件，異常處理包括預(yù)定義的錯誤和自定義錯誤。PL/SQL程序塊一旦產(chǎn)生異常而沒有指出如何處程序塊一旦產(chǎn)

32、生異常而沒有指出如何處理時理時,程序就會自動終止整個程序運行。程序就會自動終止整個程序運行。EXCEPTION when exception1 then statement1;when exception2 then statement2; .when others then statement;END;其中：異常處理可以按任意次序排列其中：異常處理可以按任意次序排列,但但 Others 必須必須放在最后。放在最后。3.6.1 異常處理概念異常處理概念 兩種類型的異常：用戶定義兩種類型的異常：用戶定義(user_define) 異常和異常和預(yù)定義預(yù)定義 ( predefined )異常異常。

33、當(dāng)使用預(yù)定義異常處理時，應(yīng)該了解當(dāng)使用預(yù)定義異常處理時，應(yīng)該了解PL/SQL 塊塊的常見運行錯誤，并掌握與之相關(guān)的預(yù)定義異常的常見運行錯誤，并掌握與之相關(guān)的預(yù)定義異常處理。處理。3.6.2預(yù)定義的異常處理預(yù)定義的異常處理 可以使用可以使用RAISE_APPLICATION_ERROR 創(chuàng)建自己的錯誤創(chuàng)建自己的錯誤處理。其語法如下：處理。其語法如下：RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors ) ;其中：其中：error_number 是從是從 20,000 到到 20,999 之間的參數(shù)，之間的參數(shù)， error

34、_message 是相應(yīng)的提示信息是相應(yīng)的提示信息( set serveroutput onSQL declare 2 cursor stu_cursor is 3 select stu_no, stu_name from student where stu_dept like 信息信息; 4 v_sname varchar2(10); 5 v_sno char(8); 6 begin 7 if not stu_cursor%ISOPEN then 8 open stu_cursor; 9 end if; 10 loop 11 fetch stu_cursor into v_sno, v_sn

35、ame; 12 exit when stu_cursor%NOTFOUND ; 13 dbms_output.put_line(v_sno|,|v_sname ); 14 end loop; 15 close stu_cursor; 16 end; 17 / 所有的所有的SQL 語句在上下文區(qū)內(nèi)部都是可執(zhí)行的，因語句在上下文區(qū)內(nèi)部都是可執(zhí)行的，因此都有一個游標(biāo)指向上下文區(qū)，此游標(biāo)就是所謂的此都有一個游標(biāo)指向上下文區(qū)，此游標(biāo)就是所謂的SQL游標(biāo)游標(biāo)(SQL cursor)，即隱式游標(biāo)。與顯式游標(biāo)即隱式游標(biāo)。與顯式游標(biāo)不同，不同，SQL 游標(biāo)不被程序打開和關(guān)閉。游標(biāo)不被程序打開和關(guān)閉。當(dāng)一個當(dāng)一個

36、DML語句執(zhí)行時，語句執(zhí)行時，PL/SQL內(nèi)部打開一個游標(biāo)，語內(nèi)部打開一個游標(biāo)，語句的結(jié)果被保存在句的結(jié)果被保存在4個游標(biāo)屬性中。個游標(biāo)屬性中。SQL%FOUNDSQL%NOTFOUNDSQL%ROWCOUNTSQL%ISOPEN游標(biāo)是一塊包含有查詢信息的內(nèi)存空間。在執(zhí)行游標(biāo)是一塊包含有查詢信息的內(nèi)存空間。在執(zhí)行DML語語句，游標(biāo)被打開；當(dāng)語句完成時，游標(biāo)被關(guān)閉。句，游標(biāo)被打開；當(dāng)語句完成時，游標(biāo)被關(guān)閉。3.7.2 隱式游標(biāo)隱式游標(biāo)【例例 3-20】 更新學(xué)生表，如果找到學(xué)號為更新學(xué)生表，如果找到學(xué)號為20028104的學(xué)生更新學(xué)生的年齡，否則往學(xué)生表中插入該學(xué)生的的學(xué)生更新學(xué)生的年齡，否則往

37、學(xué)生表中插入該學(xué)生的記錄。記錄。SQL BEGIN 2 UPDATE student 3 SET stu_age=19 4 WHERE stu_no=20028104; 5 - 如果更新沒有匹配則插入一新行如果更新沒有匹配則插入一新行 6 IF SQL%NOTFOUND THEN 7 INSERT into Student VALUES(20028104,李濱李濱,19,男男,信息信息); 8 END IF; 9 commit; 10 END; 11 / 游標(biāo)游標(biāo)FOR循環(huán)是在循環(huán)是在PL/SQL 塊中使用游標(biāo)最簡單的方式，塊中使用游標(biāo)最簡單的方式，簡化了對游標(biāo)的處理。當(dāng)使用游標(biāo)簡化了對游標(biāo)的

38、處理。當(dāng)使用游標(biāo)FOR循環(huán)時，循環(huán)時，Oracle 會會隱含地打開游標(biāo)、提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)。語法如下：隱含地打開游標(biāo)、提取游標(biāo)數(shù)據(jù)并關(guān)閉游標(biāo)。語法如下： FOR record_name IN cursor_name LOOP Ststement1; Ststement2; END LOOP;其中：其中：record_name是是Oracle 隱含定義的記錄變量名。隱含定義的記錄變量名。當(dāng)使用游標(biāo)開發(fā)當(dāng)使用游標(biāo)開發(fā)PL/SQL 應(yīng)用程序時，為了簡化程序代應(yīng)用程序時，為了簡化程序代碼，建議大家使用游標(biāo)碼，建議大家使用游標(biāo)FOR循環(huán)。循環(huán)。3.7.3 游標(biāo)游標(biāo)FOR循環(huán)循環(huán)【例例 3-21】給課

39、程名為給課程名為數(shù)據(jù)庫原理數(shù)據(jù)庫原理的所有學(xué)生的成績加的所有學(xué)生的成績加5分。分。 SQL declare 2 cursor sc1_cursor 3 is 4 select * 5 from sc for update; 6 begin 7 dbms_output.put_line(課程號課程號 學(xué)號學(xué)號 成績成績); 8 for sc_rec in sc1_cursor loop 9 if sc_rec.cou_no=a01 then 10 dbms_output.put_line(sc_rec.cou_no| |sc_rec.stu_no| |sc_rec.grade); 11 upda

40、te sc 12 set grade=grade+2 13 WHERE CURRENT OF sc1_cursor; 14 end if; 15 end loop; 16 end; 17 / ORACLE編寫的程序一般分為兩類：編寫的程序一般分為兩類：存儲過程：是可以完成一定功能的程序叫存儲過程；存儲過程：是可以完成一定功能的程序叫存儲過程；函數(shù)：是在使用時給出一個或多個值，處理完后返回一個函數(shù)：是在使用時給出一個或多個值，處理完后返回一個或多個結(jié)果的程序叫函數(shù)；或多個結(jié)果的程序叫函數(shù)；這兩類程序都存放在這兩類程序都存放在Oracle數(shù)據(jù)庫字典中。數(shù)據(jù)庫字典中。3.8存儲過程和函數(shù)存儲過程和函

41、數(shù) 與其它的數(shù)據(jù)庫系統(tǒng)一樣，與其它的數(shù)據(jù)庫系統(tǒng)一樣，Oracle的存儲過程是的存儲過程是用用PL/SQL語言編寫的能完成一定處理功能的存儲語言編寫的能完成一定處理功能的存儲在數(shù)據(jù)庫字典中的程序。在數(shù)據(jù)庫字典中的程序。創(chuàng)建過程語法創(chuàng)建過程語法: CREATE OR REPLACE PROCEDURE 過程名過程名 (參數(shù)名參數(shù)名 IN | IN OUT 數(shù)據(jù)類型數(shù)據(jù)類型.) IS | AS PL/SQL塊塊3.8.1 存儲過程存儲過程 建立無參數(shù)的存儲過程建立無參數(shù)的存儲過程【例例 22】以下過程用于輸出當(dāng)前系統(tǒng)日期和時間。】以下過程用于輸出當(dāng)前系統(tǒng)日期和時間。SQL CREATE OR REP

42、LACE PROCEDURE data_time 2 IS 3 BEGIN 4 dbms_output.put_line(systimestamp); 5 END; 6 /過程已創(chuàng)建。過程已創(chuàng)建。建立了存儲過程建立了存儲過程data_time之后，就可以調(diào)用該過程。之后，就可以調(diào)用該過程。在在SQL*Plus環(huán)境中調(diào)用過程有兩種方法環(huán)境中調(diào)用過程有兩種方法：方法一：使用方法一：使用execute命令調(diào)用過程命令調(diào)用過程SQL set serveroutput on; SQL exec data_time;19-7月月 -05 09.08.36.312000000 下午下午 +08:00PL/S

43、QL 過程已成功完成。過程已成功完成。方法二：使用方法二：使用call命令調(diào)用過程命令調(diào)用過程SQL call data_time();20-7月月 -05 09.24.59.902000000 上午上午 +08:00調(diào)用完成。調(diào)用完成。 建立帶有建立帶有IN參數(shù)的存儲過程參數(shù)的存儲過程建立存儲過程時，可以通過使用輸入?yún)?shù)，將應(yīng)用程序建立存儲過程時，可以通過使用輸入?yún)?shù)，將應(yīng)用程序的數(shù)據(jù)傳遞到過程中，的數(shù)據(jù)傳遞到過程中，如果不指定參數(shù)模式，則默認(rèn)是輸入?yún)?shù)，如果不指定參數(shù)模式，則默認(rèn)是輸入?yún)?shù)，可以使用可以使用IN關(guān)鍵字顯示地定義輸入?yún)?shù)。關(guān)鍵字顯示地定義輸入?yún)?shù)。下面以建立為選課表下面以建立

44、為選課表SC插入數(shù)據(jù)的存儲過程插入數(shù)據(jù)的存儲過程add_sc為為例，說明建立帶有輸入?yún)?shù)的過程的方法。例，說明建立帶有輸入?yún)?shù)的過程的方法。【例例 3-23】建立為選課表建立為選課表SC插入數(shù)據(jù)的存儲過程插入數(shù)據(jù)的存儲過程add_scv。 SQL CREATE OR REPLACE PROCEDURE add_scv 2 (v_sno sc.stu_no%TYPE, v_cno sc.cou_no%TYPE, v_grade sc.grade%TYPE) 3 IS 4 e_integrity EXCEPTION; 5 pragma exception_init(e_integrity,-229

45、1); 6 BEGIN 7 insert into sc(stu_no,cou_no,grade) 8 values(v_sno,v_cno,v_grade); 9 EXCEPTION 10 WHEN dup_val_on_index THEN 11 RAISE_APPLICATION_ERROR(-20000,學(xué)號與課程號不能重復(fù)學(xué)號與課程號不能重復(fù)); 12 WHEN e_integrity THEN 13 RAISE_APPLICATION_ERROR(-20001,學(xué)號或課程號不存在學(xué)號或課程號不存在); 14 END;15 / 建立建立OUT參數(shù)的存儲過程參數(shù)的存儲過程過程不僅可以用

46、來執(zhí)行特定操作，而且也可以用于輸出過程不僅可以用來執(zhí)行特定操作，而且也可以用于輸出數(shù)據(jù)，在過程中輸出數(shù)據(jù)是使用數(shù)據(jù)，在過程中輸出數(shù)據(jù)是使用OUT或或IN OUT 參數(shù)來參數(shù)來完成的，當(dāng)定義輸出參數(shù)時，必須提供完成的，當(dāng)定義輸出參數(shù)時，必須提供OUT關(guān)鍵字。關(guān)鍵字?！纠?3-24】 建立用于輸出某學(xué)生某門課的成績的存儲過建立用于輸出某學(xué)生某門課的成績的存儲過程程sc_gradeSQL CREATE OR REPLACE PROCEDURE sc_grade 2 ( v_sno IN sc.stu_no%TYPE, 3 v_cno IN sc.cou_no%TYPE, 4 v_grade OUT

47、 sc.grade%TYPE ) 5 IS 6 BEGIN 7 select grade into v_grade 8 from sc 9 where stu_no=v_sno and cou_no=v_cno; 10 EXCEPTION 11 WHEN no_data_found THEN 12 RAISE_APPLICATION_ERROR(-20000,學(xué)號或課程號不存在學(xué)號或課程號不存在); 13 END; 14 / 建立帶建立帶IN OUT參數(shù)的存儲過程參數(shù)的存儲過程定義過程時，不僅可以指定定義過程時，不僅可以指定IN和和OUT參數(shù)，也可以指定參數(shù)，也可以指定IN OUT參數(shù)。參數(shù)。

48、IN OUT參數(shù)也稱為輸入輸出參數(shù)，當(dāng)使用這種參數(shù)時，參數(shù)也稱為輸入輸出參數(shù)，當(dāng)使用這種參數(shù)時，在調(diào)用過程之前需要通過變量給該參數(shù)傳遞數(shù)據(jù)。在調(diào)在調(diào)用過程之前需要通過變量給該參數(shù)傳遞數(shù)據(jù)。在調(diào)用結(jié)束后，用結(jié)束后，Oracle 會通過該變量將過程結(jié)果傳遞給應(yīng)會通過該變量將過程結(jié)果傳遞給應(yīng)用程序。用程序?！纠?3-25】 將一個將一個7位數(shù)字的電話號碼（如位數(shù)字的電話號碼（如2217889轉(zhuǎn)轉(zhuǎn)換成換成8 位數(shù)字的電話號碼。轉(zhuǎn)換規(guī)則：第一個數(shù)字為位數(shù)字的電話號碼。轉(zhuǎn)換規(guī)則：第一個數(shù)字為2，前面加上前面加上5 ，其余的加上，其余的加上6。SQL CREATE OR REPLACE PROCEDURE

49、 telephone 2 (v_phone_no IN OUT varchar2) 3 IS 4 BEGIN 5 if substr(v_phone_no,1,1)=2 then 6 v_phone_no:=5|v_phone_no; 7 else 8 v_phone_no:=6|v_phone_no; 9 end if; 10 END; Oracle的函數(shù)是一個獨有的對象，它也是由的函數(shù)是一個獨有的對象，它也是由PL/SQL語句編語句編寫而成。與存儲過程不同的是函數(shù)必須返回某些值，而存寫而成。與存儲過程不同的是函數(shù)必須返回某些值，而存儲過程可以不返回任何值。創(chuàng)建函數(shù)的語法如下：儲過程可以不返

50、回任何值。創(chuàng)建函數(shù)的語法如下：CREATE OR REPLACE FUNCTION 函數(shù)名函數(shù)名 (argment in| in out TYPE, argment in | out | in out TYPE,. RETURN datatype IS | AS PL/SQL 塊；塊；3.8.2 函數(shù)函數(shù) 建立無參數(shù)的函數(shù)建立無參數(shù)的函數(shù)當(dāng)建立函數(shù)時，函數(shù)可以帶有參數(shù)，也可以不帶參數(shù)。當(dāng)建立函數(shù)時，函數(shù)可以帶有參數(shù)，也可以不帶參數(shù)?！纠?3-26】建立用于顯示當(dāng)前數(shù)據(jù)庫用戶的函數(shù)。（不帶】建立用于顯示當(dāng)前數(shù)據(jù)庫用戶的函數(shù)。（不帶任何參數(shù)）任何參數(shù)） SQL CREATE OR REPLACE

51、 FUNCTION get_user 2 return varchar2 3 AS 4 v_user varchar2(100); 5 BEGIN 6 select username into v_user from user_users; 7 return v_user; 8 END; 9 /函數(shù)已創(chuàng)建。函數(shù)已創(chuàng)建。 建立帶建立帶IN參數(shù)的函數(shù)參數(shù)的函數(shù)建立函數(shù)時，通過使用輸入?yún)?shù)，可以將應(yīng)用程序的數(shù)建立函數(shù)時，通過使用輸入?yún)?shù)，可以將應(yīng)用程序的數(shù)據(jù)傳遞到函數(shù)中，最終通過執(zhí)行函數(shù)可以將結(jié)果返回到據(jù)傳遞到函數(shù)中，最終通過執(zhí)行函數(shù)可以將結(jié)果返回到應(yīng)用程序中。應(yīng)用程序中。【例例 3-27】創(chuàng)建函數(shù)

52、】創(chuàng)建函數(shù)get_grade，實現(xiàn)輸入學(xué)生的學(xué)號實現(xiàn)輸入學(xué)生的學(xué)號及課程號返回該門課的成績。如果學(xué)號及課程號存在，及課程號返回該門課的成績。如果學(xué)號及課程號存在，顯示該信息。（帶有顯示該信息。（帶有IN參數(shù)）參數(shù)）SQL CREATE OR REPLACE FUNCTION get_grade 2 (v_sno IN varchar2, v_cno IN varchar2) 3 return number 4 AS 5 v_grade sc.grade%TYPE; 6 EGIN 7 select grade into v_grade 8 from sc 9 where stu_no=v_sno

53、 and cou_no=v_cno; 10 return v_grade; 11 EXCEPTION 12 WHEN no_data_found THEN 13 RAISE_APPLICATION_ERROR(-20000,學(xué)號或課程號不存在學(xué)號或課程號不存在); 14 END; 建立帶建立帶OUT參數(shù)的函數(shù)參數(shù)的函數(shù)一般情況下，函數(shù)只有一個返回值，如果希望使用函一般情況下，函數(shù)只有一個返回值，如果希望使用函數(shù)同時返回多個值，則需要使用輸出參數(shù)數(shù)同時返回多個值，則需要使用輸出參數(shù)OUT?！纠?28】 輸入學(xué)生的學(xué)號，建立用于返回學(xué)生的姓輸入學(xué)生的學(xué)號，建立用于返回學(xué)生的姓名及所在的專業(yè)的函數(shù)

54、名及所在的專業(yè)的函數(shù)get_dept。（。（帶有帶有OUT參數(shù)）參數(shù)）SQL CREATE OR REPLACE FUNCTION get_dept 2 (v_sno IN varchar2, v_name OUT varchar2) 3 return varchar2 4 AS 5 v_sdept student.stu_dept%TYPE; 6 BEGIN 7 select stu_name, stu_dept into v_name,v_sdept 8 from student 9 where stu_no=v_sno; 10 return v_sdept; 11 EXCEPTION 1

55、2 WHEN no_data_found THEN 13 RAISE_APPLICATION_ERROR(-20000,學(xué)號不存在學(xué)號不存在); 14 END; 存儲過程、函數(shù)是存儲在數(shù)據(jù)字典中的對象，它們是屬于某存儲過程、函數(shù)是存儲在數(shù)據(jù)字典中的對象，它們是屬于某一數(shù)據(jù)庫用戶的。用戶對其所擁有的對象可以進(jìn)行任何操作，一數(shù)據(jù)庫用戶的。用戶對其所擁有的對象可以進(jìn)行任何操作，其他用戶在被授予了合適的權(quán)限以后也可以訪問這些對象。其他用戶在被授予了合適的權(quán)限以后也可以訪問這些對象。 如果調(diào)試正確的存儲過程沒有進(jìn)行授權(quán)，那就只有建立者本如果調(diào)試正確的存儲過程沒有進(jìn)行授權(quán)，那就只有建立者本人才可以運行。所

56、以作為應(yīng)用系統(tǒng)的一部分的存儲過程也必人才可以運行。所以作為應(yīng)用系統(tǒng)的一部分的存儲過程也必須進(jìn)行授權(quán)才能達(dá)到要求。須進(jìn)行授權(quán)才能達(dá)到要求。 可以用可以用GRANT命令來進(jìn)行存儲命令來進(jìn)行存儲過程的運行授權(quán)。過程的運行授權(quán)。 GRANT語法：語法：GRANT system_privilege | role TO user | role | PUBLICWITH ADMIN OPTIONGRANT object_privilege | ALL column ON schema.objectFROM user | role | PUBLIC WITH GRANT OPTION3.8.3 過程和函數(shù)的安

57、全性過程和函數(shù)的安全性 【例例 29】假定表】假定表student是用戶是用戶scott的私有表，用戶的私有表，用戶personal是開發(fā)者，最終用戶是是開發(fā)者，最終用戶是green?，F(xiàn)要求現(xiàn)要求green只能通過只能通過personal創(chuàng)建的過程創(chuàng)建的過程add_stu存取存取scott的的student表。該過程表。該過程插入學(xué)生的記錄。如何實現(xiàn)？插入學(xué)生的記錄。如何實現(xiàn)？（1）首先）首先在在scott用戶環(huán)境下，為用戶用戶環(huán)境下，為用戶personal授于對授于對student表操作所需的相應(yīng)權(quán)限。表操作所需的相應(yīng)權(quán)限。SQL grant select,insert,update,del

58、ete ON student to personal;授權(quán)成功。授權(quán)成功。注意：如果某個用戶沒有權(quán)限來創(chuàng)建存儲過程，則需要注意：如果某個用戶沒有權(quán)限來創(chuàng)建存儲過程，則需要DBA將創(chuàng)建過程的權(quán)限授予某用戶。由于將創(chuàng)建過程的權(quán)限授予某用戶。由于personal用戶用戶沒有創(chuàng)建存儲過程的權(quán)限，必須沒有創(chuàng)建存儲過程的權(quán)限，必須以以DBA的身份為的身份為personal用戶建立創(chuàng)建存儲過程的權(quán)限。否則用戶建立創(chuàng)建存儲過程的權(quán)限。否則personal用戶沒法創(chuàng)建存儲過程的權(quán)限。用戶沒法創(chuàng)建存儲過程的權(quán)限。（2）personal擁有這些權(quán)限后，就可以建立存儲過程。擁有這些權(quán)限后，就可以建立存儲過程。SQL

59、CREATE OR REPLACE PROCEDURE add_stu 2 (v_sno scott.student.stu_no%TYPE, v_sname scott.student.stu_name%TYPE) 3 IS 4 e_integrity EXCEPTION; 5 pragma exception_init(e_integrity,-2291); 6 BEGIN 7 insert into scott.student(stu_no,stu_name) 8 values(v_sno,v_sname); 9 EXCEPTION 10 WHEN dup_val_on_index TH

60、EN 11 RAISE_APPLICATION_ERROR(-20000,學(xué)號不能重復(fù)學(xué)號不能重復(fù)); 12 WHEN e_integrity THEN 13 RAISE_APPLICATION_ERROR(-20001,學(xué)號不存在學(xué)號不存在); 14 END; 15 /（3）進(jìn)行授權(quán)）進(jìn)行授權(quán)SQL grant execute on add_stu to green;授權(quán)成功。授權(quán)成功。（4）Green用戶就可以對用戶就可以對personal用戶所建的存儲過用戶所建的存儲過程調(diào)用了。示例如下：程調(diào)用了。示例如下：SQL exec personal.add_stu(20026121,李琳李琳);PL/SQL 過程已成功完成。過程已成功完