第7章 游標(biāo)和異常處理

第7章游標(biāo)和異常處理

第7章游標(biāo)和異常處理

7.1游標(biāo)的概念

7.2隱式游標(biāo)

7.3顯式游標(biāo)

7.4異常處理

75階段訓(xùn)練

7.6練習(xí)

第7章游標(biāo)和異常處理

7.1游標(biāo)的概念

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

的形式定義。游標(biāo)的作用就是用于臨時(shí)存儲(chǔ)從數(shù)據(jù)庫(kù)中

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

盤的表中調(diào)到計(jì)算機(jī)內(nèi)存中進(jìn)行處理，最后將處理結(jié)果

顯示出來(lái)或最終寫回?cái)?shù)據(jù)庫(kù)。這樣數(shù)據(jù)處理的速度才會(huì)

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

第7章游標(biāo)和異常處理

游標(biāo)有兩種類型：顯式游標(biāo)和隱式游標(biāo)。在前述程

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

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

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

就要由程序員定義一個(gè)顯式游標(biāo)，并通過與游標(biāo)有關(guān)的

語(yǔ)句進(jìn)行處理。顯式游標(biāo)對(duì)應(yīng)一個(gè)返回結(jié)果為多行多列

的SELECT語(yǔ)句。

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

中，然后應(yīng)用程序再?gòu)挠螛?biāo)變量中分解出需要的數(shù)據(jù)，

并進(jìn)行處理。

第7章游標(biāo)和異常處理

7.2隱式游標(biāo)

如前所述，DML操作和單行SELECT語(yǔ)句會(huì)使用隱

式游標(biāo)，它們是：

*插入操作：INSERTo

*更新操作：UPDATEo

*刪除操作：DELETEo

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

第7章游標(biāo)和異常處理

當(dāng)系統(tǒng)使用一個(gè)隱式游標(biāo)時(shí)，可以通過隱式游標(biāo)

的屬性來(lái)了解操作的狀態(tài)和結(jié)果，進(jìn)而控制程序的流

程。隱式游標(biāo)可以使用名字SQL來(lái)訪問，但要注意，

通過SQL游標(biāo)名總是只能訪問前一個(gè)DML操作或單行

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

之后，立即使用SQL游標(biāo)名來(lái)訪問屬性。游標(biāo)的屬性

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

第7章游標(biāo)和異常處理

表7-1隱式游標(biāo)屬性

隱式游標(biāo)的屬性返回值類型意義

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

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

SQL%FOUND布爾型

成功

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

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

第7章游標(biāo)和異常處理

【訓(xùn)練1】使用隱式游標(biāo)的屬性，判斷對(duì)雇員工

資的修改是否成功。

步驟1：輸入和運(yùn)行以下程序：

SETSERVEROUTPUTON

BEGIN

UPDATEempSETsal=sal+100WHEREempno=1234;

IFSQL%FOUNDTHEN

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

COMMIT;

ELSE

第7章游標(biāo)和異常處理

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

ENDIF;

END;

運(yùn)行結(jié)果為：

修改雇員工資失??！

PL/SQL過程已成功完成。

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

運(yùn)行結(jié)果為：

成功修改雇員工資！

PL/SQL過程已成功完成。

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

功，并給出相應(yīng)信息。

第7章游標(biāo)和異常處理

7.3顯式游標(biāo)

7.3.1游標(biāo)的定義和操作

游標(biāo)的使用分成以下4個(gè)步驟。

1.聲明游標(biāo)

在DECLEAR部分按以下格式聲明游標(biāo)：

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

型…］）］

ISSELECT語(yǔ)句；

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

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

須在打開游標(biāo)時(shí)傳遞相應(yīng)的實(shí)際參數(shù)。

第7章游標(biāo)和異常處理

SELECT語(yǔ)句是對(duì)表或視圖的查詢語(yǔ)句，甚至也可

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

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

SELECT語(yǔ)句中可以使用在定義游標(biāo)之前定義的變量。

2.打開游標(biāo)

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

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

打開游標(biāo)時(shí)，SELECT語(yǔ)句的查詢結(jié)果就被傳送到

了游標(biāo)工作區(qū)。

第7章游標(biāo)和異常處理

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

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

取到變量中。提取操作必須在打開游標(biāo)之后進(jìn)行。

FETCH游標(biāo)名INTO變量名1[,變量名2...];

或

FETCH游標(biāo)名INTO記錄變量；

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

次返回指針?biāo)傅囊恍袛?shù)據(jù)，要返回多行需重復(fù)執(zhí)行，

可以使用循環(huán)語(yǔ)句來(lái)實(shí)現(xiàn)。控制循環(huán)可以通過判斷游標(biāo)

的屬性來(lái)進(jìn)行。

第7章游標(biāo)和異常處理

下面對(duì)這兩種格式進(jìn)行說(shuō)明：

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

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

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

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

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

起來(lái)比較方便，不必分別定義和使用多個(gè)變量。

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

變量名表名|游標(biāo)名％ROWTYPE；

其中的表必須存在，游標(biāo)名也必須先定義。

第7章游標(biāo)和異常處理

4.關(guān)閉游標(biāo)

CLOSE游標(biāo)名；

顯式游標(biāo)打開后，必須顯式地關(guān)閉。游標(biāo)一旦關(guān)閉，游標(biāo)

占用的資源就被釋放，游標(biāo)變成無(wú)效，必須重新打開才能使用。

以下是使用顯式游標(biāo)的一個(gè)簡(jiǎn)單練習(xí)。

【訓(xùn)練1】用游標(biāo)提取emp表中7788雇員的名稱和職務(wù)。

SETSERVEROUTPUTON

DECLARE

venameVARCHAR2(10);

vjobVARCHAR2(10);

CURSORempcursorIS

SELECTenameJobFROMempWHEREempno=7788;

第7章游標(biāo)和異常處理

BEGIN

OPENempcursor;

FETCHempcursorINTOv_ename,vjob;

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

CLOSEempcursor;

END;

執(zhí)行結(jié)果為：

SCOTT,ANALYST

PL/SQL過程已成功完成。

說(shuō)明：該程序通過定義游標(biāo)emp_cursor,提取并顯示雇

員7788的名稱和職務(wù)。

作為對(duì)以上例子的改進(jìn)，在以下訓(xùn)練中采用了記錄變量。

第7章游標(biāo)和異常處理

【訓(xùn)練2】用游標(biāo)提取emp表中7788雇員的姓名、

職務(wù)和工資。

SETSERVEROUTPUTON

DECLARE

CURSORempcursorISSELECTename,job,sal

FROMempWHEREempno=7788;

emprecordemp_cursor%ROWTYPE;

BEGIN

第7章游標(biāo)和異常處理

OPENempcursor;

FETCHempcursorINTOemprecord;

DBMS_OUTPUT.PUT_LINE(emp_record.ename||7

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

CLOSEempcursor;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

SCOTT,ANALYST,3000

PL/SQL過程已成功完成。

說(shuō)明：實(shí)例中使用記錄變量來(lái)接收數(shù)據(jù)，記錄變

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

注意：可通過以下形式獲得記錄變量的內(nèi)容：

記錄變量名.字段名。

第7章游標(biāo)和異常處理

【訓(xùn)練3】顯示工資最高的前3名雇員的名稱和工

資。

SETSERVEROUTPUTON

DECLARE

VenameVARCHAR2(10);

V_salNUMBER(5);

CURSORempcursorISSELECTename,salFROM

empORDERBYsalDESC;

BEGIN

OPENempcursor;

FORIIN1..3LOOP

FETCHempcursorINTOv_ename,v_sal;

第7章游標(biāo)和異常處理

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

ENDLOOP;

CLOSEempcursor;

END;

執(zhí)行結(jié)果為：

KING,5000

SCOTT,3000

FORD,3000

PL/SQL過程已成功完成。

說(shuō)明：該程序在游標(biāo)定義中使用了ORDERBY子

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

第7章游標(biāo)和異常處理

7.3.2游標(biāo)循環(huán)

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

員的編號(hào)和名稱。

SETSERVEROUTPUTON

DECLARE

CURSORempcursorIS

SELECTempno,enameFROMemp;

BEGIN

第7章游標(biāo)和異常處理

FOREmprecordINempcursorLOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.emp

no||Emprecord.ename);

ENDLOOP;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

7369SMITH

7499ALLEN

7521WARD

7566JONES

PL/SQL過程已成功完成。

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

錄變量的定義、游標(biāo)的打開、提取和關(guān)閉過程。

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

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

第7章游標(biāo)和異常處理

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

SETSERVEROUTPUTON

BEGIN

FORreIN(SELECTenameFROMEMP)LOOP

DBMSOUTPUT.PUTLINE(re.ename)

ENDLOOP;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

SMITH

ALLEN

WARD

JONES

說(shuō)明：該種形式更為簡(jiǎn)單，省略了游標(biāo)的定義,

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

第7章游標(biāo)和異常處理

7.3.3顯式游標(biāo)屬性

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

游標(biāo)定義以后使用它的一些屬性來(lái)進(jìn)行結(jié)構(gòu)控制是一

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

一

第7章游標(biāo)和異常處理

表7-2顯式游標(biāo)屬性

游標(biāo)的屬性返回值類型意義

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

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

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

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

第7章游標(biāo)和異常處理

可按照以下形式取得游標(biāo)的屬性：

游標(biāo)名％屬性

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

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

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

第7章游標(biāo)和異常處理

【訓(xùn)練1】使用游標(biāo)的屬性練習(xí)。

SETSERVEROUTPUTON

DECLARE

VenameVARCHAR2(10);

CURSORempcursorIS

SELECTenameFROMemp;

BEGIN

OPENempcursor;

IFemp_cursor%ISOPENTHEN

第7章游標(biāo)和異常處理

LOOP

FETCHempcursorINTOvename;

EXITWHENemp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor

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

ENDLOOP;

ELSE

DBMS_OUTPUT.PUT_LINE(f用戶信息：游標(biāo)沒

有打開！力"

ENDIF;

CLOSEempcursor;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

1-SMITH

2-ALLEN

3-WARD

PL/SQL過程已成功完成。

說(shuō)明：本例使用emp_cursor%ISOPEN判斷游標(biāo)是否打開；

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

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

用FETCH語(yǔ)句；使用emp_cursor%NOTFOUND^lJ斷FETCH語(yǔ)

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

退出循環(huán)。

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

序。

第7章游標(biāo)和異常處理

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

【訓(xùn)練1】帶參數(shù)的游標(biāo)。

SETSERVEROUTPUTON

DECLARE

VempnoNUMBER(5);

VenameVARCHAR2(10);

CURSORemp_cursor(p_deptnoNUMBER,

pjobVARCHAR2)IS

SELECTempno,enameFROMemp

WHEREdeptno=pdeptnoANDjob=pjob;

第7章游標(biāo)和異常處理

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章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

7934,MILLER

PL/SQL過程已成功完成。

說(shuō)明：游標(biāo)emp_cursor定義了兩個(gè)參數(shù)：p_deptno代

表部門編號(hào)，p」ob代表職務(wù)。語(yǔ)句OPENemp_cursor(10,

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

CLERK,所以游標(biāo)查詢的內(nèi)容是部門10的職務(wù)為CLERK

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

第7章游標(biāo)和異常處理

【練習(xí)1】修改Open語(yǔ)句的參數(shù)：部門號(hào)為20、職

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

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

于游標(biāo)定義，并在游標(biāo)打開之前賦值。對(duì)以上例子重

新改動(dòng)如下：

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

SETSERVEROUTPUTON

DECLARE

vempnoNUMBER(5);

venameVARCHAR2(10);

v_deptnoNUMBER(5);

第7章游標(biāo)和異常處理

vjobVARCHAR2(10);

CURSORempcursorIS

SELECTempno,enameFROMemp

WHEREdeptno=vdeptnoANDjob=vjob;

BEGIN

v_deptno:=10;

v」ob:='CLERK';

OPENempcursor;

LOOP

FETCHempcursorINTOvempno,vename;

EXITWHENemp_cursor%NOTFOUND;

第7章游標(biāo)和異常處理

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

ENDLOOP;

END;

執(zhí)行結(jié)果為：

7934,MILLER

PL/SQL過程已成功完成。

說(shuō)明：該程序與前一程序?qū)崿F(xiàn)相同的功能。

第7章游標(biāo)和異常處理

7.3.5動(dòng)態(tài)SELECT語(yǔ)句和動(dòng)態(tài)游標(biāo)的用法

Oracle支持動(dòng)態(tài)SELECT語(yǔ)句和動(dòng)態(tài)游標(biāo)，動(dòng)態(tài)的

方法大大擴(kuò)展了程序設(shè)計(jì)的能力。

對(duì)于查詢結(jié)果為一行的SELECT語(yǔ)句，可以用動(dòng)態(tài)

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

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

executeimmediate查詢語(yǔ)句字符串into變量1［，變量

2...］；

以下是一個(gè)動(dòng)態(tài)生成SELECT語(yǔ)句的例子。

第7章游標(biāo)和異常處理

【訓(xùn)練1】動(dòng)態(tài)SELECT查詢o

SETSERVEROUTPUTON

DECLARE

strvarchar2(100);

venamevarchar2(10);

begin

str:-selectenamefromscott.empwhereempno=7788';

executeimmediatestrintovename;

dbmsoutput.putline(vename);

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

SCOTT

PL/SQL過程已成功完成。

說(shuō)明：SELECT…INTO…語(yǔ)句存放在STR字符串中，通

過EXECUTE語(yǔ)句執(zhí)行。

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

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

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

運(yùn)行階段隨時(shí)生成一個(gè)查詢語(yǔ)句作為游標(biāo)。要使用動(dòng)態(tài)游

標(biāo)需要先定義一個(gè)游標(biāo)類型，然后聲明一個(gè)游標(biāo)變量，游

標(biāo)對(duì)應(yīng)的查詢語(yǔ)句可以在程序的執(zhí)行過程中動(dòng)態(tài)地說(shuō)明。

第7章游標(biāo)和異常處理

定義游標(biāo)類型的語(yǔ)句如下：

TYPE游標(biāo)類型名REFCURSOR;

聲明游標(biāo)變量的語(yǔ)句如下：

游標(biāo)變量名游標(biāo)類型名；

在可執(zhí)行部分可以如下形式打開一個(gè)動(dòng)態(tài)游標(biāo):

OPEN游標(biāo)變量名FOR查詢語(yǔ)句字符串；

第7章游標(biāo)和異常處理

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

員信息。

輸入并運(yùn)行以下程序：

declare

typecurtypeisrefcursor;

curcurtype;

recscott.emp%rowtype;

strvarchar2(50);

letterchar:='A';

第7章游標(biāo)和異常處理

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章游標(biāo)和異常處理

endloop;

exitwhenletter='Z';

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

endloop;

end;

運(yùn)行結(jié)果為：

包含字母A的名字：

ALLEN

WARD

MARTIN

..

第7章游標(biāo)和異常處理

BLAKE

CLARK

ADAMS

JAMES

包含字母B的名字

BLAKE

包含字母C的名字

CLARK

SCOTT

第7章游標(biāo)和異常處理

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

成游標(biāo)的SELECT語(yǔ)句，然后打開。通過語(yǔ)句

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

第7章游標(biāo)和異常處理

7.4異常處理

7.4.1錯(cuò)誤處理

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

WHEN語(yǔ)句引導(dǎo)的多個(gè)分支構(gòu)成的。錯(cuò)誤處理的語(yǔ)法

如下：

EXCEPTION

WHEN錯(cuò)誤1[OR錯(cuò)誤2]THEN

語(yǔ)句序列1;

WHEN錯(cuò)誤3[OR錯(cuò)誤4]THEN

第7章游標(biāo)和異常處理

語(yǔ)句序列2；

WHENOTHERS

語(yǔ)句序列n；

END;

其中：

錯(cuò)誤是在標(biāo)準(zhǔn)包中由系統(tǒng)預(yù)定義的標(biāo)準(zhǔn)錯(cuò)誤，或

是由用戶在程序的說(shuō)明部分自定義的錯(cuò)誤，參見下一

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

語(yǔ)句序列就是不同分支的錯(cuò)誤處理部分。

'、辿/％

7浙茲-

^4

第7章游標(biāo)和異常處理

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

錯(cuò)誤，其他未被捕捉到的錯(cuò)誤，將在WHENOTHERS

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

的最后一個(gè)錯(cuò)誤處理分支。如要在該分支中進(jìn)一步判

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

SQLERRM()來(lái)獲得系統(tǒng)錯(cuò)誤號(hào)和錯(cuò)誤信息。

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

誤處理部分，則錯(cuò)誤會(huì)傳遞到主程序中。

下面是由于查詢編號(hào)錯(cuò)誤而引起系統(tǒng)預(yù)定義異常

的例子。

第7章游標(biāo)和異常處理

【訓(xùn)練1】查詢編號(hào)為1234的雇員名字。

SETSERVEROUTPUTON

DECLARE

vnameVARCHAR2(10);

BEGIN

SELECTename

INTOvname

FROMemp

WHEREempno=1234;

第7章游標(biāo)和異常處理

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

vname）;

EXCEPTION

WHENNODATAFOUNDTHEN

DBMS_OUTPUT.PUT_LINE（，編號(hào)錯(cuò)誤，沒有找

到相應(yīng)雇員！，）；

WHENOTHERSTHEN

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

END;

執(zhí)行結(jié)果為：

編號(hào)錯(cuò)誤，沒有找到相應(yīng)雇員！

PL/SQL過程已成功完成。

第7章游標(biāo)和異常處理

說(shuō)明：在以上查詢中，因?yàn)榫幪?hào)為1234的雇員不

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

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

預(yù)定義的錯(cuò)誤類型，EXCEPTION部分下的WHEN語(yǔ)句

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

輸出用戶自定義的錯(cuò)誤信息“編號(hào)錯(cuò)誤，沒有找到相

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

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

第7章游標(biāo)和異常處理

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

SETSERVEROUTPUTON

DECLARE

vtempNUMBER(5):=1;

BEGIN

vtemp:=vtemp/0;

EXCEPTION

WHENOTHERSTHEN

第7章游標(biāo)和異常處理

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

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

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

END;

執(zhí)行結(jié)果為：

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

錯(cuò)誤代碼：？1476

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

PL/SQL過程已成功完成。

第7章游標(biāo)和異常處理

說(shuō)明：程序運(yùn)行中發(fā)生除零錯(cuò)誤，由WHENOTHERS捕

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

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

SQLERRM()來(lái)進(jìn)一步獲得錯(cuò)誤的代碼和種類信息。

7.4.2預(yù)定義錯(cuò)誤

Oracle的系統(tǒng)錯(cuò)誤很多，但只有一部分常見錯(cuò)誤在標(biāo)準(zhǔn)

包中予以定義。定義的錯(cuò)誤可以在EXCEPTION部分通過標(biāo)

準(zhǔn)的錯(cuò)誤名來(lái)進(jìn)行判斷，并進(jìn)行異常處理。常見的系統(tǒng)預(yù)定

義異常如表7-3所示。

第7章游標(biāo)和異常處理

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

錯(cuò)誤名稱錯(cuò)誤代碼錯(cuò)誤含義

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

INVALID_CURSORORA_01001試圖使用沒有打開的游標(biāo)

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

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

INVALID_NUMBERORA_01722試圖對(duì)無(wú)效字符進(jìn)行數(shù)值轉(zhuǎn)換

ROWTYPE_MISMATCHORA_06504主變量和游標(biāo)的類型不兼容

VALUE_ERRORORA_06502轉(zhuǎn)換、截?cái)嗷蛩阈g(shù)運(yùn)算發(fā)生錯(cuò)誤

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

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

TIMEOUT_ON_RESOURCEORA_00051等待資源時(shí)發(fā)生超時(shí)錯(cuò)誤

TRANSACTION_BACKED_OUTORA_00060由于死鎖，提交失敗

STORAGE_ERRORORA_06500發(fā)生內(nèi)存錯(cuò)誤

PROGRAM_ERRORORA_06501發(fā)生PL/SQL內(nèi)部錯(cuò)誤

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

LOG1N_DENIEDORA_01017在連接時(shí)提供了無(wú)效用戶名或口令

第7章游標(biāo)和異常處理

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

DUPVALONJNDEX錯(cuò)誤。

如果一個(gè)系統(tǒng)錯(cuò)誤沒有在標(biāo)準(zhǔn)包中定義，則需要在說(shuō)

明部分定義，語(yǔ)法如下：

錯(cuò)誤名EXCEPTION;

定義后使用PRAGMAEXCEPTIONJNIT來(lái)將一個(gè)定義

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

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

PRAGMAEXCEPTION_INIT（錯(cuò)誤名,-錯(cuò)誤代碼）;

第7章游標(biāo)和異常處理

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

SETSERVEROUTPUTON

DECLARE

V_ENAMEVARCHAR2(10);

NULLINSERTERROREXCEPTION;

PRAGMA

EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);

BEGIN

INSERTINTOEMP(EMPNO)VALUES(NULL);

EXCEPTION

第7章游標(biāo)和異常處理

WHENNULLINSERTERRORTHEN

DBMS_OUTPUT.PUT_LINE（f無(wú)法插入NULL值!

'）；

WHENOTHERSTHEN

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

'）；

END;

執(zhí)行結(jié)果為：

無(wú)法插入NULL值！

PL/SQL過程已成功完成。

說(shuō)明：NULL」NSERT_ERROR是自定義異常，同

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

第7章游標(biāo)和異常處理

7.4.3自定義異常

程序設(shè)計(jì)者可以利用引發(fā)異常的機(jī)制來(lái)進(jìn)行程序設(shè)計(jì)，

自己定義異常類型。可以在聲明部分定義新的異常類型，

定義的語(yǔ)法是：

錯(cuò)誤名EXCEPTION;

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

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

RAISE錯(cuò)誤名；

第7章游標(biāo)和異常處理

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

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

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

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

義的錯(cuò)誤編號(hào)，第二個(gè)參數(shù)是用戶自定義的錯(cuò)誤信息。

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

間選擇。

自定義異常處理錯(cuò)誤的方式同前。

第7章游標(biāo)和異常處理

【訓(xùn)練1】插入新雇員，限定插入雇員的編號(hào)在

7000?8000之間。

SETSERVEROUTPUTON

DECLARE

newnoNUMBER(IO);

newexcp1EXCEPTION;

new_excp2EXCEPTION;

BEGIN

第7章游標(biāo)和異常處理

new_no:=6789;

INSERTINTOemp(empno,ename)

VALUES(new_noJ小鄭)

IFnew_no<7000THEN

RAISEnewexcp1;

ENDIF;

IFnew_no>8000THEN

RAISEnew_excp2;

ENDIF;

COMMIT;

EXCEPTION

第7章游標(biāo)和異常處理

WHENnewexcplTHEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（f>員編號(hào)小于7000的下限!

，）；~~

WHENnew_excp2THEN

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（f>員編號(hào)超過8000的上限!

'）；

END;

執(zhí)行結(jié)果為：

雇員編號(hào)小于7000的下限！

PL/SQL過程已成功完成。

第7章游標(biāo)和異常處理

說(shuō)明：在此例中，自定義了兩個(gè)異常：newexcp1

和new_excp2,分別代表編號(hào)小于7000和編號(hào)大于8000

的錯(cuò)誤。在程序中通過判斷編號(hào)大小，產(chǎn)生對(duì)應(yīng)的異

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

的錯(cuò)誤信息。

^4

第7章游標(biāo)和異常處理

【訓(xùn)練2】使用RAISEAPPLICATIONERROR函

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

SETSERVEROUTPUTON

DECLARE

NewnoNUMBER(IO);

BEGIN

New_no:=6789;

INSERTINTOemp(empno,ename)

VALUES(new_no,'JAMES');

第7章游標(biāo)和異常處理

IFnew_no<7000THEN

ROLLBACK;

RAISE_APPLICATION_ERROR（-20001,，編號(hào)小于

7000的下限！，）;

ENDIF;

IFnew_no>8000THEN

ROLLBACK;

RAISEAPPLICATIONERROR（-20002」編號(hào)大于

8000的下限！

ENDIF;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

DECLARE

*

ERROR位于第1行：

ORA-20001:編號(hào)小于7000的下限！

ORA-06512:在line9

說(shuō)明：在本訓(xùn)練中，使用

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

錯(cuò)誤的方式進(jìn)行顯示。錯(cuò)誤編號(hào)為20001和20002。

注意：同上一個(gè)訓(xùn)練比較，此種方法不需要事先定義異

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

第7章游標(biāo)和異常處理

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

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

異常的錯(cuò)誤編號(hào)，SQLERRM為發(fā)生異常的錯(cuò)誤信息。

DECLARE

verrorcodeNUMBER;

v_error_messageVARCHAR2(255);

BEGIN

EXCEPTION

第7章游標(biāo)和異常處理

WHENOTHERSTHEN

verrorcode:=SQLCODE;

v_error_message:=SQLERRM;

INSERTINTOerrors

VALUES(verrorcode,v_error_message);

END;

【練習(xí)1】修改雇員的工資，通過引發(fā)異常控制修

改范圍在600?6000之間。

第7章游標(biāo)和異常處理

7.5階段訓(xùn)練

【訓(xùn)練1】將雇員從一個(gè)表復(fù)制到另一個(gè)表。

步驟1：創(chuàng)建一個(gè)結(jié)構(gòu)同EMP表一樣的新表EMP1：

CREATETABLEemplASSELECT*FROM

SCOTT.EMPWHERE1=2;

步驟2：通過指定雇員編號(hào)，將雇員由EMP表移動(dòng)

到EMP1表：

第7章游標(biāo)和異常處理

SETSERVEROUTPUTON

DECLARE

vempnoNUMBER(5):=7788;

emp_recemp%ROWTYPE;

BEGIN

SELECT*INTOemprecFROMempWHERE

empno=vempno;

DELETEFROMempWHEREempno=vempno;

第7章游標(biāo)和異常處理

INSERTINTOemplVALUESemp_rec;

IFSQL%FOUNDTHEN

COMMIT;

DBMS_OUTPUT.PUT_LINE（，雇員復(fù)制成功!

'）；

ELSE

ROLLBACK;

DBMS_OUTPUT.PUT_LINE（，雇員復(fù)制失敗!

'）；

ENDIF;

END;

第7章游標(biāo)和異常處理

執(zhí)行結(jié)果為：

雇員復(fù)制成功！

PL/SQL過程已成功完成。

步驟2：顯示復(fù)制結(jié)果：

SELECTempno,ename,jobFROMempl;

執(zhí)行結(jié)果為：

EMPNOENAMEJOB

7788SCOTTANALYST

第7章游標(biāo)和異常處理

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

SELECT…INTO…語(yǔ)句將整個(gè)記錄傳給該變量。INSERT

語(yǔ)句將整個(gè)記錄變量插入empl表，如果插入成功

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

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

第7章游標(biāo)和異常處理

【訓(xùn)練2】輸出雇員工資，雇員工資用不同高度

的*表示。

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

SETSERVEROUTPUTON

BEGIN

FORreIN(SELECTename,salFROMEMP)LOOP

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

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

ENDLOOP;

END;

第7章游標(biāo)和異常處理

輸出結(jié)果為:

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章游標(biāo)和異常處理

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í)行結(jié)果為:

PL/SQL過程已成功完成。

說(shuō)明：第一個(gè)rpad函數(shù)產(chǎn)生對(duì)齊效果，第二個(gè)rpad函數(shù)

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

標(biāo)循環(huán)形式°

出dL「一

第7章游標(biāo)和異常處理

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

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

SETSERVEROUTPUTON

DECLARE

vcountnumber:=0;

CURSORd印tcursorISSELECT*FROMdept;

BEGIN

DBMSOUTPUT.PUTLINEC部門列表，）；

第7章游標(biāo)和異常處理

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

-——'）；

FORDeptrecordINdeptcursorLOOP

DBMS_OUTPUT.PUT_LINE（1部門編號(hào)：f||

Deptrecord.deptno）;

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

Deptrecord.dname）;

DBMS_OUTPUT.PUT_LINE（,所在城市：］

Deptrecord.loc）;

第7章游標(biāo)和異常處理

DBMSOUTPUT.PUTLINEC1------

-——')；

v_count:=v_count+1;

ENDLOOP;

DBMSOUTPUT.PUTLINEC共有

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

END;

第7章游標(biāo)和異常處理

輸出結(jié)果為：

部門列表

部門編號(hào)：10

部門名稱：ACCOUNTING

所在城市：NEWYORK

第7章游標(biāo)和異常處理

部門編號(hào)：20

部門名稱：RESEARCH

所在城市：DALLAS

???

共有4個(gè)部門！

PL/SQL過程已成功完成。

說(shuō)明：該程序中將字段內(nèi)容垂直排列。V_count變

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

^4

第7章游標(biāo)和異常處理

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

統(tǒng)計(jì)輸出部門名稱、部門總?cè)藬?shù)、總工資和部門經(jīng)理。

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

SETSERVEROUTPUTON

DECLARE

vdeptnonumber(8);

vcountnumber(3);

vsumsalnumber(6);

vdnamevarchar2(15);

第7章游標(biāo)和異常處理

vmanagervarchar2(15);

CURSORlistcursorIS

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

groupbydeptno;

BEGIN

OPENlistcursor;

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

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

第7章游標(biāo)和異常處理

DBMS_OUTPUT.PUT_LINE（，部門名稱總?cè)藬?shù)總

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

FETCHlistcursorINTOv_deptno,v_count,v_sumsal;

WHILElist_cursor%foundLOOP

SELECTdnameINTOvdnameFROMdept

WHEREdeptno=vdeptno;

SELECTenameINTOvmanagerFROMemp

WHEREdeptno=v_deptnoandjob='MANAGER';

第7章游標(biāo)和異常處理

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章游標(biāo)和異常處理

輸出結(jié)果為：

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

部門名稱總?cè)藬?shù)總工資部門經(jīng)理

ACCOUNTING38750CLARK

RESEARC