第7章 游標和異常處理

第7章游標和異常處理

第7章游標和異常處理

7.1游標的概念

7.2隱式游標

7.3顯式游標

7.4異常處理

75階段訓練

7.6練習

第7章游標和異常處理

7.1游標的概念

游標是SQL的一個內存工作區(qū)，由系統(tǒng)或用戶以變量

的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中

提取的數(shù)據(jù)塊。在某些情況下，需要把數(shù)據(jù)從存放在磁

盤的表中調到計算機內存中進行處理，最后將處理結果

顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度才會

提高，否則頻繁的磁盤數(shù)據(jù)交換會降低效率。

第7章游標和異常處理

游標有兩種類型：顯式游標和隱式游標。在前述程

序中用到的SELECT…INTO…查詢語句，一次只能從數(shù)據(jù)

庫中提取一行數(shù)據(jù)，對于這種形式的查詢和DML操作，

系統(tǒng)都會使用一個隱式游標。但是如果要提取多行數(shù)據(jù),

就要由程序員定義一個顯式游標，并通過與游標有關的

語句進行處理。顯式游標對應一個返回結果為多行多列

的SELECT語句。

游標一旦打開，數(shù)據(jù)就從數(shù)據(jù)庫中傳送到游標變量

中，然后應用程序再從游標變量中分解出需要的數(shù)據(jù)，

并進行處理。

第7章游標和異常處理

7.2隱式游標

如前所述，DML操作和單行SELECT語句會使用隱

式游標，它們是：

*插入操作：INSERTo

*更新操作：UPDATEo

*刪除操作：DELETEo

*單行查詢操作：SELECT...INTO...o

第7章游標和異常處理

當系統(tǒng)使用一個隱式游標時，可以通過隱式游標

的屬性來了解操作的狀態(tài)和結果，進而控制程序的流

程。隱式游標可以使用名字SQL來訪問，但要注意，

通過SQL游標名總是只能訪問前一個DML操作或單行

SELECT操作的游標屬性。所以通常在剛剛執(zhí)行完操作

之后，立即使用SQL游標名來訪問屬性。游標的屬性

有四種，如表7-1所示。

第7章游標和異常處理

表7-1隱式游標屬性

隱式游標的屬性返回值類型意義

SQL%ROWCOUNT整型代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù)

值為TRUE代表插入、刪除、更新或單行查詢操作

SQL%FOUND布爾型

成功

SQL%NOTFOUND布爾型與SQL%FOUND屬性返回值相反

SQL%ISOPEN布爾型DML執(zhí)行過程中為真，結束后為假

第7章游標和異常處理

【訓練1】使用隱式游標的屬性，判斷對雇員工

資的修改是否成功。

步驟1：輸入和運行以下程序：

SETSERVEROUTPUTON

BEGIN

UPDATEempSETsal=sal+100WHEREempno=1234;

IFSQL%FOUNDTHEN

DBMS_OUTPUT.PUT_LINE（，成功修改雇員工資!，）;

COMMIT;

ELSE

第7章游標和異常處理

DBMSJDUTPUT.PUTJLINEC修改雇員工資失??！，）；

ENDIF;

END;

運行結果為：

修改雇員工資失??！

PL/SQL過程已成功完成。

步驟2：將雇員編號1234改為7788,重新執(zhí)行以上程序：

運行結果為：

成功修改雇員工資！

PL/SQL過程已成功完成。

說明：本例中，通過SQL%FOUND屬性判斷修改是否成

功，并給出相應信息。

第7章游標和異常處理

7.3顯式游標

7.3.1游標的定義和操作

游標的使用分成以下4個步驟。

1.聲明游標

在DECLEAR部分按以下格式聲明游標：

CURSOR游標名［（參數(shù)1數(shù)據(jù)類型［,參數(shù)2數(shù)據(jù)類

型…］）］

ISSELECT語句；

參數(shù)是可選部分，所定義的參數(shù)可以出現(xiàn)在

SELECT語句的WHERE子句中。如果定義了參數(shù)，則必

須在打開游標時傳遞相應的實際參數(shù)。

第7章游標和異常處理

SELECT語句是對表或視圖的查詢語句，甚至也可

以是聯(lián)合查詢?？梢詭HERE條件、ORDERBY或

GROUPBY等子句，但不能使用INTO子句。在

SELECT語句中可以使用在定義游標之前定義的變量。

2.打開游標

在可執(zhí)行部分，按以下格式打開游標：

OPEN游標名［（實際參數(shù)1［,實際參數(shù)2...］）］;

打開游標時，SELECT語句的查詢結果就被傳送到

了游標工作區(qū)。

第7章游標和異常處理

3.提取數(shù)據(jù)

在可執(zhí)行部分，按以下格式將游標工作區(qū)中的數(shù)據(jù)

取到變量中。提取操作必須在打開游標之后進行。

FETCH游標名INTO變量名1[,變量名2...];

或

FETCH游標名INTO記錄變量；

游標打開后有一個指針指向數(shù)據(jù)區(qū)，F(xiàn)ETCH語句一

次返回指針所指的一行數(shù)據(jù)，要返回多行需重復執(zhí)行，

可以使用循環(huán)語句來實現(xiàn)?？刂蒲h(huán)可以通過判斷游標

的屬性來進行。

第7章游標和異常處理

下面對這兩種格式進行說明：

第一種格式中的變量名是用來從游標中接收數(shù)據(jù)

的變量，需要事先定義。變量的個數(shù)和類型應與

SELECT語句中的字段變量的個數(shù)和類型一致。

第二種格式一次將一行數(shù)據(jù)取到記錄變量中，需

要使用％ROWTYPE事先定義記錄變量，這種形式使用

起來比較方便，不必分別定義和使用多個變量。

定義記錄變量的方法如下：

變量名表名|游標名％ROWTYPE；

其中的表必須存在，游標名也必須先定義。

第7章游標和異常處理

4.關閉游標

CLOSE游標名；

顯式游標打開后，必須顯式地關閉。游標一旦關閉，游標

占用的資源就被釋放，游標變成無效，必須重新打開才能使用。

以下是使用顯式游標的一個簡單練習。

【訓練1】用游標提取emp表中7788雇員的名稱和職務。

SETSERVEROUTPUTON

DECLARE

venameVARCHAR2(10);

vjobVARCHAR2(10);

CURSORempcursorIS

SELECTenameJobFROMempWHEREempno=7788;

第7章游標和異常處理

BEGIN

OPENempcursor;

FETCHempcursorINTOv_ename,vjob;

DBMS_OUTPUT.PUT_LINE(v_ename||7||vJob);

CLOSEempcursor;

END;

執(zhí)行結果為：

SCOTT,ANALYST

PL/SQL過程已成功完成。

說明：該程序通過定義游標emp_cursor,提取并顯示雇

員7788的名稱和職務。

作為對以上例子的改進，在以下訓練中采用了記錄變量。

第7章游標和異常處理

【訓練2】用游標提取emp表中7788雇員的姓名、

職務和工資。

SETSERVEROUTPUTON

DECLARE

CURSORempcursorISSELECTename,job,sal

FROMempWHEREempno=7788;

emprecordemp_cursor%ROWTYPE;

BEGIN

第7章游標和異常處理

OPENempcursor;

FETCHempcursorINTOemprecord;

DBMS_OUTPUT.PUT_LINE(emp_record.ename||7

emp_record.job||7||emp_record.sal);

CLOSEempcursor;

END;

第7章游標和異常處理

執(zhí)行結果為：

SCOTT,ANALYST,3000

PL/SQL過程已成功完成。

說明：實例中使用記錄變量來接收數(shù)據(jù)，記錄變

量由游標變量定義，需要出現(xiàn)在游標定義之后。

注意：可通過以下形式獲得記錄變量的內容：

記錄變量名.字段名。

第7章游標和異常處理

【訓練3】顯示工資最高的前3名雇員的名稱和工

資。

SETSERVEROUTPUTON

DECLARE

VenameVARCHAR2(10);

V_salNUMBER(5);

CURSORempcursorISSELECTename,salFROM

empORDERBYsalDESC;

BEGIN

OPENempcursor;

FORIIN1..3LOOP

FETCHempcursorINTOv_ename,v_sal;

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE(v_ename||7||v_sal);

ENDLOOP;

CLOSEempcursor;

END;

執(zhí)行結果為：

KING,5000

SCOTT,3000

FORD,3000

PL/SQL過程已成功完成。

說明：該程序在游標定義中使用了ORDERBY子

句進行排序，并使用循環(huán)語句來提取多行數(shù)據(jù)。

第7章游標和異常處理

7.3.2游標循環(huán)

【訓練1】使用特殊的FOR循環(huán)形式顯示全部雇

員的編號和名稱。

SETSERVEROUTPUTON

DECLARE

CURSORempcursorIS

SELECTempno,enameFROMemp;

BEGIN

第7章游標和異常處理

FOREmprecordINempcursorLOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.emp

no||Emprecord.ename);

ENDLOOP;

END;

第7章游標和異常處理

執(zhí)行結果為：

7369SMITH

7499ALLEN

7521WARD

7566JONES

PL/SQL過程已成功完成。

說明：可以看到該循環(huán)形式非常簡單，隱含了記

錄變量的定義、游標的打開、提取和關閉過程。

Empjecord為隱含定義的記錄變量，循環(huán)的執(zhí)行次數(shù)

與游標取得的數(shù)據(jù)的行數(shù)相一致。

第7章游標和異常處理

【訓練2】另一種形式的游標循環(huán)。

SETSERVEROUTPUTON

BEGIN

FORreIN(SELECTenameFROMEMP)LOOP

DBMSOUTPUT.PUTLINE(re.ename)

ENDLOOP;

END;

第7章游標和異常處理

執(zhí)行結果為：

SMITH

ALLEN

WARD

JONES

說明：該種形式更為簡單，省略了游標的定義,

游標的SELECT查詢語句在循環(huán)中直接出現(xiàn)。

第7章游標和異常處理

7.3.3顯式游標屬性

雖然可以使用前面的形式獲得游標數(shù)據(jù)，但是在

游標定義以后使用它的一些屬性來進行結構控制是一

種更為靈活的方法。顯式游標的屬性如表7-2所示。

一

第7章游標和異常處理

表7-2顯式游標屬性

游標的屬性返回值類型意義

%ROWCOUNT整型獲得FETCH語句返回的數(shù)據(jù)行數(shù)

%FOUND布爾型最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假

%NOTFOUND布爾型與％FOUND屬性返回值相反

%ISOPEN布爾型游標已經(jīng)打開時值為真，否則為假

第7章游標和異常處理

可按照以下形式取得游標的屬性：

游標名％屬性

要判斷游標empcursor是否處于打開狀態(tài)，可以使

用屬性emp_cursor%ISOPEN。如果游標已經(jīng)打開，則返

回值為“真”，否則為“假”。具體可參照以下的訓練。

第7章游標和異常處理

【訓練1】使用游標的屬性練習。

SETSERVEROUTPUTON

DECLARE

VenameVARCHAR2(10);

CURSORempcursorIS

SELECTenameFROMemp;

BEGIN

OPENempcursor;

IFemp_cursor%ISOPENTHEN

第7章游標和異常處理

LOOP

FETCHempcursorINTOvename;

EXITWHENemp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor

%ROWCOUNT)||,-,||v_ename);——

ENDLOOP;

ELSE

DBMS_OUTPUT.PUT_LINE(f用戶信息：游標沒

有打開！力"

ENDIF;

CLOSEempcursor;

END;

第7章游標和異常處理

執(zhí)行結果為：

1-SMITH

2-ALLEN

3-WARD

PL/SQL過程已成功完成。

說明：本例使用emp_cursor%ISOPEN判斷游標是否打開；

使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語句返

回的數(shù)屆亍數(shù)并輸出；使用循環(huán)來獲取數(shù)據(jù)，在循環(huán)體中使

用FETCH語句；使用emp_cursor%NOTFOUND^lJ斷FETCH語

句是否成功執(zhí)行，當FETCH語句失敗時說明數(shù)據(jù)已經(jīng)取完，

退出循環(huán)。

【練習1】去掉OPENemp_cursor;語句，重新執(zhí)行以上程

序。

第7章游標和異常處理

7.3.4游標參數(shù)的傳遞

【訓練1】帶參數(shù)的游標。

SETSERVEROUTPUTON

DECLARE

VempnoNUMBER(5);

VenameVARCHAR2(10);

CURSORemp_cursor(p_deptnoNUMBER,

pjobVARCHAR2)IS

SELECTempno,enameFROMemp

WHEREdeptno=pdeptnoANDjob=pjob;

第7章游標和異常處理

BEGIN

OPENemp_cursor(10,^LERK1);

LOOP

FETCHempcursorINTOv_empno,v_ename;

EXITWHENemp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno||7||v_ename);

ENDLOOP;

END;

第7章游標和異常處理

執(zhí)行結果為：

7934,MILLER

PL/SQL過程已成功完成。

說明：游標emp_cursor定義了兩個參數(shù)：p_deptno代

表部門編號，p」ob代表職務。語句OPENemp_cursor(10,

CLERK，)傳遞了兩個參數(shù)值給游標，即部門為10、職務為

CLERK,所以游標查詢的內容是部門10的職務為CLERK

的雇員。循環(huán)部分用于顯示查詢的內容。

第7章游標和異常處理

【練習1】修改Open語句的參數(shù)：部門號為20、職

務為ANALYST,并重新執(zhí)行。

也可以通過變量向游標傳遞參數(shù)，但變量需要先

于游標定義，并在游標打開之前賦值。對以上例子重

新改動如下：

【訓練2】通過變量傳遞參數(shù)給游標。

SETSERVEROUTPUTON

DECLARE

vempnoNUMBER(5);

venameVARCHAR2(10);

v_deptnoNUMBER(5);

第7章游標和異常處理

vjobVARCHAR2(10);

CURSORempcursorIS

SELECTempno,enameFROMemp

WHEREdeptno=vdeptnoANDjob=vjob;

BEGIN

v_deptno:=10;

v」ob:='CLERK';

OPENempcursor;

LOOP

FETCHempcursorINTOvempno,vename;

EXITWHENemp_cursor%NOTFOUND;

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE(v_empno||7||v_ename);

ENDLOOP;

END;

執(zhí)行結果為：

7934,MILLER

PL/SQL過程已成功完成。

說明：該程序與前一程序實現(xiàn)相同的功能。

第7章游標和異常處理

7.3.5動態(tài)SELECT語句和動態(tài)游標的用法

Oracle支持動態(tài)SELECT語句和動態(tài)游標，動態(tài)的

方法大大擴展了程序設計的能力。

對于查詢結果為一行的SELECT語句，可以用動態(tài)

生成查詢語句字符串的方法，在程序執(zhí)行階段臨時地

生成并執(zhí)行，語法是：

executeimmediate查詢語句字符串into變量1［，變量

2...］；

以下是一個動態(tài)生成SELECT語句的例子。

第7章游標和異常處理

【訓練1】動態(tài)SELECT查詢o

SETSERVEROUTPUTON

DECLARE

strvarchar2(100);

venamevarchar2(10);

begin

str:-selectenamefromscott.empwhereempno=7788';

executeimmediatestrintovename;

dbmsoutput.putline(vename);

END;

第7章游標和異常處理

執(zhí)行結果為：

SCOTT

PL/SQL過程已成功完成。

說明：SELECT…INTO…語句存放在STR字符串中，通

過EXECUTE語句執(zhí)行。

在變量聲明部分定義的游標是靜態(tài)的，不能在程序運

行過程中修改。雖然可以通過參數(shù)傳遞來取得不同的數(shù)據(jù),

但還是有很大的局限性。通過采用動態(tài)游標，可以在程序

運行階段隨時生成一個查詢語句作為游標。要使用動態(tài)游

標需要先定義一個游標類型，然后聲明一個游標變量，游

標對應的查詢語句可以在程序的執(zhí)行過程中動態(tài)地說明。

第7章游標和異常處理

定義游標類型的語句如下：

TYPE游標類型名REFCURSOR;

聲明游標變量的語句如下：

游標變量名游標類型名；

在可執(zhí)行部分可以如下形式打開一個動態(tài)游標:

OPEN游標變量名FOR查詢語句字符串；

第7章游標和異常處理

【訓練2】按名字中包含的字母順序分組顯示雇

員信息。

輸入并運行以下程序：

declare

typecurtypeisrefcursor;

curcurtype;

recscott.emp%rowtype;

strvarchar2(50);

letterchar:='A';

第7章游標和異常處理

begin

loop

str:=’selectenamefromempwhereenamelike

n%,||letter||,%n,;

opencurforstr;

dbms_output.put_line(，包含字母［〔letter『的名字：*);

loop

fetchcurintorec.ename;

exitwhencur%notfbund;

dbms_output.put_line(rec.ename);

第7章游標和異常處理

endloop;

exitwhenletter='Z';

letter:=chr(ascii(letter)+1);

endloop;

end;

運行結果為：

包含字母A的名字：

ALLEN

WARD

MARTIN

..

第7章游標和異常處理

BLAKE

CLARK

ADAMS

JAMES

包含字母B的名字

BLAKE

包含字母C的名字

CLARK

SCOTT

第7章游標和異常處理

說明：使用了二重循環(huán)，在外循環(huán)體中，動態(tài)生

成游標的SELECT語句，然后打開。通過語句

letter:=chr(ascii(letter)+l)可獲得字母表中的下一個字母。

第7章游標和異常處理

7.4異常處理

7.4.1錯誤處理

錯誤處理部分位于程序的可執(zhí)行部分之后，是由

WHEN語句引導的多個分支構成的。錯誤處理的語法

如下：

EXCEPTION

WHEN錯誤1[OR錯誤2]THEN

語句序列1;

WHEN錯誤3[OR錯誤4]THEN

第7章游標和異常處理

語句序列2；

WHENOTHERS

語句序列n；

END;

其中：

錯誤是在標準包中由系統(tǒng)預定義的標準錯誤，或

是由用戶在程序的說明部分自定義的錯誤，參見下一

節(jié)系統(tǒng)預定義的錯誤類型。

語句序列就是不同分支的錯誤處理部分。

'、辿/％

7浙茲-

^4

第7章游標和異常處理

凡是出現(xiàn)在WHEN后面的錯誤都是可以捕捉到的

錯誤，其他未被捕捉到的錯誤，將在WHENOTHERS

部分進行統(tǒng)一處理，OTHENS必須是EXCEPTION部分

的最后一個錯誤處理分支。如要在該分支中進一步判

斷錯誤種類，可以通過使用預定義函數(shù)SQLCODE()和

SQLERRM()來獲得系統(tǒng)錯誤號和錯誤信息。

如果在程序的子塊中發(fā)生了錯誤，但子塊沒有錯

誤處理部分，則錯誤會傳遞到主程序中。

下面是由于查詢編號錯誤而引起系統(tǒng)預定義異常

的例子。

第7章游標和異常處理

【訓練1】查詢編號為1234的雇員名字。

SETSERVEROUTPUTON

DECLARE

vnameVARCHAR2(10);

BEGIN

SELECTename

INTOvname

FROMemp

WHEREempno=1234;

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE（f該雇員名字為：!||

vname）;

EXCEPTION

WHENNODATAFOUNDTHEN

DBMS_OUTPUT.PUT_LINE（，編號錯誤，沒有找

到相應雇員！，）；

WHENOTHERSTHEN

DBMSJDUTPUT.PUTLINEC發(fā)生其他錯誤！，）；

END;

執(zhí)行結果為：

編號錯誤，沒有找到相應雇員！

PL/SQL過程已成功完成。

第7章游標和異常處理

說明：在以上查詢中，因為編號為1234的雇員不

存在，所以將發(fā)生類型為"NO_DATA_

FOUND〃的異常?！癗O_DATA_FOUND〃是系統(tǒng)

預定義的錯誤類型，EXCEPTION部分下的WHEN語句

將捕捉到該異常，并執(zhí)行相應代碼部分。在本例中，

輸出用戶自定義的錯誤信息“編號錯誤，沒有找到相

應雇員！〃。如果發(fā)生其他類型的錯誤，將執(zhí)行

OTHERS條件下的代碼部分，顯示“發(fā)生其他錯誤!〃。

第7章游標和異常處理

【訓練2】由程序代碼顯示系統(tǒng)錯誤。

SETSERVEROUTPUTON

DECLARE

vtempNUMBER(5):=1;

BEGIN

vtemp:=vtemp/0;

EXCEPTION

WHENOTHERSTHEN

第7章游標和異常處理

DBMSJ3UTPUT.PUTLINEC發(fā)生系統(tǒng)錯誤！，)；

DBMS_OUTPUT.PUT_LINE(，錯誤代碼：1|SQLCODE());

DBMS_OUTPUT.PUT_LINE(，錯誤信息：”|SQLERRM());

END;

執(zhí)行結果為：

發(fā)生系統(tǒng)錯誤！

錯誤代碼：？1476

錯誤信息：ORA-01476:除數(shù)為0

PL/SQL過程已成功完成。

第7章游標和異常處理

說明：程序運行中發(fā)生除零錯誤，由WHENOTHERS捕

捉到，執(zhí)行用戶自己的輸出語句顯示錯誤信息，然后正常結

束。在錯誤處理部分使用了預定義函數(shù)SQLCODE()和

SQLERRM()來進一步獲得錯誤的代碼和種類信息。

7.4.2預定義錯誤

Oracle的系統(tǒng)錯誤很多，但只有一部分常見錯誤在標準

包中予以定義。定義的錯誤可以在EXCEPTION部分通過標

準的錯誤名來進行判斷，并進行異常處理。常見的系統(tǒng)預定

義異常如表7-3所示。

第7章游標和異常處理

表7-3系統(tǒng)預定義異常

錯誤名稱錯誤代碼錯誤含義

CURSOR_ALREADY_OPENORA_06511試圖打開已經(jīng)打開的游標

INVALID_CURSORORA_01001試圖使用沒有打開的游標

DUP_VAL_ON_INDEXORA_00001保存重復值到惟一索引約束的列中

ZERO_DIVIDEORA_01476發(fā)生除數(shù)為零的除法錯誤

INVALID_NUMBERORA_01722試圖對無效字符進行數(shù)值轉換

ROWTYPE_MISMATCHORA_06504主變量和游標的類型不兼容

VALUE_ERRORORA_06502轉換、截斷或算術運算發(fā)生錯誤

TOO_MANY_ROWSORA_01422SELECT...INTO…語句返回多于一行的數(shù)據(jù)

NO_DATA_FOUNDORA_01403SELECT...INTO…語句沒有數(shù)據(jù)返回

TIMEOUT_ON_RESOURCEORA_00051等待資源時發(fā)生超時錯誤

TRANSACTION_BACKED_OUTORA_00060由于死鎖，提交失敗

STORAGE_ERRORORA_06500發(fā)生內存錯誤

PROGRAM_ERRORORA_06501發(fā)生PL/SQL內部錯誤

NOT_LOGGED_ONORA_01012試圖操作未連接的數(shù)據(jù)庫

LOG1N_DENIEDORA_01017在連接時提供了無效用戶名或口令

第7章游標和異常處理

比如，如果程序向表的主鍵列插入重復值，則將發(fā)生

DUPVALONJNDEX錯誤。

如果一個系統(tǒng)錯誤沒有在標準包中定義，則需要在說

明部分定義，語法如下：

錯誤名EXCEPTION;

定義后使用PRAGMAEXCEPTIONJNIT來將一個定義

的錯誤同一個特別的Oracle錯誤代碼相關聯(lián)，就可以同系

統(tǒng)預定義的錯誤一樣使用了。語法如下：

PRAGMAEXCEPTION_INIT（錯誤名,-錯誤代碼）;

第7章游標和異常處理

【訓練1】定義新的系統(tǒng)錯誤類型。

SETSERVEROUTPUTON

DECLARE

V_ENAMEVARCHAR2(10);

NULLINSERTERROREXCEPTION;

PRAGMA

EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);

BEGIN

INSERTINTOEMP(EMPNO)VALUES(NULL);

EXCEPTION

第7章游標和異常處理

WHENNULLINSERTERRORTHEN

DBMS_OUTPUT.PUT_LINE（f無法插入NULL值!

'）；

WHENOTHERSTHEN

DBMS_OUTPUT.PUT_LINE（，發(fā)生其他系統(tǒng)錯誤!

'）；

END;

執(zhí)行結果為：

無法插入NULL值！

PL/SQL過程已成功完成。

說明：NULL」NSERT_ERROR是自定義異常，同

不統(tǒng)警1400相關聯(lián)。

第7章游標和異常處理

7.4.3自定義異常

程序設計者可以利用引發(fā)異常的機制來進行程序設計，

自己定義異常類型?？梢栽诼暶鞑糠侄x新的異常類型，

定義的語法是：

錯誤名EXCEPTION;

用戶定義的錯誤不能由系統(tǒng)來觸發(fā)，必須由程序顯式

地觸發(fā)，觸發(fā)的語法是：

RAISE錯誤名；

第7章游標和異常處理

RAISE也可以用來引發(fā)模擬系統(tǒng)錯誤，比如，

RAISEZERO_DIVIDE將引發(fā)模擬的除零錯誤。

使用RAISE_APPLICATION_ERROR函數(shù)也可以弓|

發(fā)異常。該函數(shù)要傳遞兩個參數(shù)，第一個是用戶自定

義的錯誤編號，第二個參數(shù)是用戶自定義的錯誤信息。

使用該函數(shù)引發(fā)的異常的編號應該在20000和20999之

間選擇。

自定義異常處理錯誤的方式同前。

第7章游標和異常處理

【訓練1】插入新雇員，限定插入雇員的編號在

7000?8000之間。

SETSERVEROUTPUTON

DECLARE

newnoNUMBER(IO);

newexcp1EXCEPTION;

new_excp2EXCEPTION;

BEGIN

第7章游標和異常處理

new_no:=6789;

INSERTINTOemp(empno,ename)

VALUES(new_noJ小鄭)

IFnew_no<7000THEN

RAISEnewexcp1;

ENDIF;

IFnew_no>8000THEN

RAISEnew_excp2;

ENDIF;

COMMIT;

EXCEPTION

第7章游標和異常處理

WHENnewexcplTHEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（f>員編號小于7000的下限!

，）；~~

WHENnew_excp2THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（f>員編號超過8000的上限!

'）；

END;

執(zhí)行結果為：

雇員編號小于7000的下限！

PL/SQL過程已成功完成。

第7章游標和異常處理

說明：在此例中，自定義了兩個異常：newexcp1

和new_excp2,分別代表編號小于7000和編號大于8000

的錯誤。在程序中通過判斷編號大小，產生對應的異

常，并在異常處理部分回退插入操作，然后顯示相應

的錯誤信息。

^4

第7章游標和異常處理

【訓練2】使用RAISEAPPLICATIONERROR函

數(shù)引發(fā)系統(tǒng)異常。

SETSERVEROUTPUTON

DECLARE

NewnoNUMBER(IO);

BEGIN

New_no:=6789;

INSERTINTOemp(empno,ename)

VALUES(new_no,'JAMES');

第7章游標和異常處理

IFnew_no<7000THEN

ROLLBACK;

RAISE_APPLICATION_ERROR（-20001,，編號小于

7000的下限！，）;

ENDIF;

IFnew_no>8000THEN

ROLLBACK;

RAISEAPPLICATIONERROR（-20002」編號大于

8000的下限！

ENDIF;

END;

第7章游標和異常處理

執(zhí)行結果為：

DECLARE

*

ERROR位于第1行：

ORA-20001:編號小于7000的下限！

ORA-06512:在line9

說明：在本訓練中，使用

RAISE_APPLICATION_ERROR引發(fā)自定義異常，并以系統(tǒng)

錯誤的方式進行顯示。錯誤編號為20001和20002。

注意：同上一個訓練比較，此種方法不需要事先定義異

常，可直接引發(fā)。

第7章游標和異常處理

可以參考下面的程序片斷將出錯信息記錄到表中，

其中，errors為記錄錯誤信息的表，SQLCODE為發(fā)生

異常的錯誤編號，SQLERRM為發(fā)生異常的錯誤信息。

DECLARE

verrorcodeNUMBER;

v_error_messageVARCHAR2(255);

BEGIN

EXCEPTION

第7章游標和異常處理

WHENOTHERSTHEN

verrorcode:=SQLCODE;

v_error_message:=SQLERRM;

INSERTINTOerrors

VALUES(verrorcode,v_error_message);

END;

【練習1】修改雇員的工資，通過引發(fā)異?？刂菩?/p>

改范圍在600?6000之間。

第7章游標和異常處理

7.5階段訓練

【訓練1】將雇員從一個表復制到另一個表。

步驟1：創(chuàng)建一個結構同EMP表一樣的新表EMP1：

CREATETABLEemplASSELECT*FROM

SCOTT.EMPWHERE1=2;

步驟2：通過指定雇員編號，將雇員由EMP表移動

到EMP1表：

第7章游標和異常處理

SETSERVEROUTPUTON

DECLARE

vempnoNUMBER(5):=7788;

emp_recemp%ROWTYPE;

BEGIN

SELECT*INTOemprecFROMempWHERE

empno=vempno;

DELETEFROMempWHEREempno=vempno;

第7章游標和異常處理

INSERTINTOemplVALUESemp_rec;

IFSQL%FOUNDTHEN

COMMIT;

DBMS_OUTPUT.PUT_LINE（，雇員復制成功!

'）；

ELSE

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（，雇員復制失敗!

'）；

ENDIF;

END;

第7章游標和異常處理

執(zhí)行結果為：

雇員復制成功！

PL/SQL過程已成功完成。

步驟2：顯示復制結果：

SELECTempno,ename,jobFROMempl;

執(zhí)行結果為：

EMPNOENAMEJOB

7788SCOTTANALYST

第7章游標和異常處理

說明：empjrec變量是根據(jù)emp表定義的記錄變量，

SELECT…INTO…語句將整個記錄傳給該變量。INSERT

語句將整個記錄變量插入empl表，如果插入成功

（SQL%FOUND為真），則提交事務，否則回滾撤銷事務。

試修改雇員編號為7902,重新執(zhí)行以上程序。

第7章游標和異常處理

【訓練2】輸出雇員工資，雇員工資用不同高度

的*表示。

輸入并執(zhí)行以下程序：

SETSERVEROUTPUTON

BEGIN

FORreIN(SELECTename,salFROMEMP)LOOP

DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12；

')||rpad('*'je.sal/100,'*'));

ENDLOOP;

END;

第7章游標和異常處理

輸出結果為:

7,

SMITH*T*

KX>

ALLEN*T*

WARD^jxvj^K!^

JONES7,^1，-1，7,7,w?x^^1，7,7,w?x^^f,d1，7,7,^f,^f.

MARTINKI^vf^KIXKI^

BLAKEKix、1，^1，%T^d[,^f,7,^1，

CLARKv!^vj>7,^1，7,7,^1，7,7,^1，KI^K!^

SCOTT^f?7,^1，-1，7,7,w?x^^1，7,7,w?x^^1，—1，7,7,^f,^f.

第7章游標和異常處理

KING

7,%x^7,7,yj^^fxKI^KI^vf^%J>?J^

7,^1，%j^

^T*^r*

7,7,7.^1，7,7?^1，^i>

TURNER<Tw*TM<T**TW*TW

7,7.^1，^j>\f<*

ADAMS^T*^r*^T*

JAMESvf^KJ^

7,^f，7.^1，7,7?^1，^i>.f<*7,7.^1，^f>.f<*7,7^^1，

FORD<Tw*TM<T**TW*T*<Tw

MILLERyf^yj^KI^%I>KJ^v!>

執(zhí)行結果為:

PL/SQL過程已成功完成。

說明：第一個rpad函數(shù)產生對齊效果，第二個rpad函數(shù)

根據(jù)工資額產生不同數(shù)目的*。該程序采用了隱式的簡略游

標循環(huán)形式°

出dL「一

第7章游標和異常處理

【訓練3】編寫程序，格式化輸出部門信息。

輸入并執(zhí)行如下程序：

SETSERVEROUTPUTON

DECLARE

vcountnumber:=0;

CURSORd印tcursorISSELECT*FROMdept;

BEGIN

DBMSOUTPUT.PUTLINEC部門列表，）；

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE（!--------------------------

-——'）；

FORDeptrecordINdeptcursorLOOP

DBMS_OUTPUT.PUT_LINE（1部門編號：f||

Deptrecord.deptno）;

DBMS_OUTPUT.PUT_LINE（1部門名稱：f||

Deptrecord.dname）;

DBMS_OUTPUT.PUT_LINE（,所在城市：］

Deptrecord.loc）;

第7章游標和異常處理

DBMSOUTPUT.PUTLINEC1------

-——')；

v_count:=v_count+1;

ENDLOOP;

DBMSOUTPUT.PUTLINEC共有

[|to_char(v_count)『個部門！，)；

END;

第7章游標和異常處理

輸出結果為：

部門列表

部門編號：10

部門名稱：ACCOUNTING

所在城市：NEWYORK

第7章游標和異常處理

部門編號：20

部門名稱：RESEARCH

所在城市：DALLAS

???

共有4個部門！

PL/SQL過程已成功完成。

說明：該程序中將字段內容垂直排列。V_count變

量記錄循環(huán)次數(shù)，即部門個數(shù)。

^4

第7章游標和異常處理

【訓練4】已知每個部門有一個經(jīng)理，編寫程序，

統(tǒng)計輸出部門名稱、部門總人數(shù)、總工資和部門經(jīng)理。

輸入并執(zhí)行如下程序：

SETSERVEROUTPUTON

DECLARE

vdeptnonumber(8);

vcountnumber(3);

vsumsalnumber(6);

vdnamevarchar2(15);

第7章游標和異常處理

vmanagervarchar2(15);

CURSORlistcursorIS

SELECTdeptno,count(*),sum(sal)FROMemp

groupbydeptno;

BEGIN

OPENlistcursor;

DBMS_OUTPUT.PUT_LINE(f----------部門統(tǒng)計

表--------，)；

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE（，部門名稱總人數(shù)總

工資部門經(jīng)理）；

FETCHlistcursorINTOv_deptno,v_count,v_sumsal;

WHILElist_cursor%foundLOOP

SELECTdnameINTOvdnameFROMdept

WHEREdeptno=vdeptno;

SELECTenameINTOvmanagerFROMemp

WHEREdeptno=v_deptnoandjob='MANAGER';

第7章游標和異常處理

DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_

char(v_count),8)

||rpad(to_char(v_sumsal),9)11vmanager);

FETCHlistcursorINTOv_deptno,v_count,v_sumsal;

ENDLOOP;

DBMSOUTPUT.PUTLINEC1---------------------------

-----------，)；

CLOSElistcursor;

END;

第7章游標和異常處理

輸出結果為：

-------------部門統(tǒng)計表------------

部門名稱總人數(shù)總工資部門經(jīng)理

ACCOUNTING38750CLARK

RESEARC