游標的概念:
游標是SQL的一個內(nèi)存工作區(qū),,由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊,。在某些情況下,,需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計算機內(nèi)存中進行處理,最后將處理結(jié)果顯示出來或最終寫回數(shù)據(jù)庫,。這樣數(shù)據(jù)處理的速度才會提高,,否則頻繁的磁盤數(shù)據(jù)交換會降低效率。
游標有兩種類型:顯式游標和隱式游標,。在前述程序中用到的SELECT...INTO...查詢語句,,一次只能從數(shù)據(jù)庫中提取一行數(shù)據(jù),對于這種形式的查詢和DML操作,,系統(tǒng)都會使用一個隱式游標,。但是如果要提取多行數(shù)據(jù),就要由程序員定義一個顯式游標,,并通過與游標有關的語句進行處理,。顯式游標對應一個返回結(jié)果為多行多列的SELECT語句。
游標一旦打開,,數(shù)據(jù)就從數(shù)據(jù)庫中傳送到游標變量中,,然后應用程序再從游標變量中分解出需要的數(shù)據(jù),并進行處理,。
隱式游標
如前所述,,DML操作和單行SELECT語句會使用隱式游標,它們是:
* 插入操作:INSERT,。
* 更新操作:UPDATE,。
* 刪除操作:DELETE。
* 單行查詢操作:SELECT ... INTO ...,。
當系統(tǒng)使用一個隱式游標時,,可以通過隱式游標的屬性來了解操作的狀態(tài)和結(jié)果,進而控制程序的流程,。隱式游標可以使用名字SQL來訪問,,但要注意,通過SQL游標名總是只能訪問前一個DML操作或單行SELECT操作的游標屬性,。所以通常在剛剛執(zhí)行完操作之后,,立即使用SQL游標名來訪問屬性。游標的屬性有四種,如下所示,。
Sql代碼
- 隱式游標的屬性 返回值類型 意 義
- SQL%ROWCOUNT 整型 代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù)
- SQL%FOUND 布爾型 值為TRUE代表插入、刪除,、更新或單行查詢操作成功
- SQL%NOTFOUND 布爾型 與SQL%FOUND屬性返回值相反
- SQL%ISOPEN 布爾型 DML執(zhí)行過程中為真,,結(jié)束后為假
【訓練1】 使用隱式游標的屬性,判斷對雇員工資的修改是否成功,。
步驟1:輸入和運行以下程序:
Sql代碼
- SET SERVEROUTPUT ON
- BEGIN
- UPDATE emp SET sal=sal+100 WHERE empno=1234;
- IF SQL%FOUND THEN
- DBMS_OUTPUT.PUT_LINE('成功修改雇員工資,!');
- COMMIT;
- ELSE
- DBMS_OUTPUT.PUT_LINE('修改雇員工資失敗,!');
- END IF;
- END;
運行結(jié)果為:
Sql代碼
- 修改雇員工資失?。?
- PL/SQL 過程已成功完成,。
步驟2:將雇員編號1234改為7788,,重新執(zhí)行以上程序:
運行結(jié)果為:
Sql代碼
- 成功修改雇員工資!
- PL/SQL 過程已成功完成,。
說明:本例中,,通過SQL%FOUND屬性判斷修改是否成功,并給出相應信息,。
顯式游標
游標的定義和操作
游標的使用分成以下4個步驟,。
1.聲明游標
在DECLEAR部分按以下格式聲明游標:
CURSOR 游標名[(參數(shù)1 數(shù)據(jù)類型[,參數(shù)2 數(shù)據(jù)類型...])]
IS SELECT語句;
參數(shù)是可選部分,,所定義的參數(shù)可以出現(xiàn)在SELECT語句的WHERE子句中,。如果定義了參數(shù),則必須在打開游標時傳遞相應的實際參數(shù),。
SELECT語句是對表或視圖的查詢語句,,甚至也可以是聯(lián)合查詢??梢詭HERE條件,、ORDER BY或GROUP BY等子句,但不能使用INTO子句,。在SELECT語句中可以使用在定義游標之前定義的變量,。
2.打開游標
在可執(zhí)行部分,按以下格式打開游標:
OPEN 游標名[(實際參數(shù)1[,,實際參數(shù)2...])];
打開游標時,,SELECT語句的查詢結(jié)果就被傳送到了游標工作區(qū)。
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)可以通過判斷游標的屬性來進行。
下面對這兩種格式進行說明:
第一種格式中的變量名是用來從游標中接收數(shù)據(jù)的變量,,需要事先定義,。變量的個數(shù)和類型應與SELECT語句中的字段變量的個數(shù)和類型一致。
第二種格式一次將一行數(shù)據(jù)取到記錄變量中,,需要使用%ROWTYPE事先定義記錄變量,,這種形式使用起來比較方便,不必分別定義和使用多個變量,。
定義記錄變量的方法如下:
變量名 表名|游標名%ROWTYPE,;
其中的表必須存在,游標名也必須先定義,。
4.關閉游標
CLOSE 游標名;
顯式游標打開后,,必須顯式地關閉。游標一旦關閉,,游標占用的資源就被釋放,,游標變成無效,必須重新打開才能使用,。
以下是使用顯式游標的一個簡單練習,。
【訓練1】 用游標提取emp表中7788雇員的名稱和職務。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_ename VARCHAR2(10);
- v_job VARCHAR2(10);
- CURSOR emp_cursor IS
- SELECT ename,job FROM emp WHERE empno=7788;
- BEGIN
- OPEN emp_cursor;
- FETCH emp_cursor INTO v_ename,v_job;
- DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
- CLOSE emp_cursor;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- SCOTT,ANALYST
- PL/SQL 過程已成功完成,。
說明:該程序通過定義游標emp_cursor,,提取并顯示雇員7788的名稱和職務。
作為對以上例子的改進,,在以下訓練中采用了記錄變量,。
【訓練2】 用游標提取emp表中7788雇員的姓名、職務和工資,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;
- emp_record emp_cursor%ROWTYPE;
- BEGIN
- OPEN emp_cursor;
- FETCH emp_cursor INTO emp_record;
- DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);
- CLOSE emp_cursor;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- SCOTT,ANALYST,3000
- PL/SQL 過程已成功完成,。
說明:實例中使用記錄變量來接收數(shù)據(jù),記錄變量由游標變量定義,,需要出現(xiàn)在游標定義之后,。
注意:可通過以下形式獲得記錄變量的內(nèi)容:
記錄變量名.字段名。
【訓練3】 顯示工資最高的前3名雇員的名稱和工資,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- V_ename VARCHAR2(10);
- V_sal NUMBER(5);
- CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;
- BEGIN
- OPEN emp_cursor;
- FOR I IN 1..3 LOOP
- FETCH emp_cursor INTO v_ename,v_sal;
- DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
- END LOOP;
- CLOSE emp_cursor;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- KING,5000
- SCOTT,3000
- FORD,3000
- PL/SQL 過程已成功完成,。
說明:該程序在游標定義中使用了ORDER BY子句進行排序,并使用循環(huán)語句來提取多行數(shù)據(jù)。
游標循環(huán)
【訓練1】 使用特殊的FOR循環(huán)形式顯示全部雇員的編號和名稱,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- CURSOR emp_cursor IS
- SELECT empno, ename FROM emp;
- BEGIN
- FOR Emp_record IN emp_cursor LOOP
- DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
- END LOOP;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 7369SMITH
- 7499ALLEN
- 7521WARD
- 7566JONES
- PL/SQL 過程已成功完成,。
說明:可以看到該循環(huán)形式非常簡單,隱含了記錄變量的定義,、游標的打開,、提取和關閉過程。Emp_record為隱含定義的記錄變量,,循環(huán)的執(zhí)行次數(shù)與游標取得的數(shù)據(jù)的行數(shù)相一致。
【訓練2】 另一種形式的游標循環(huán),。
Sql代碼
- SET SERVEROUTPUT ON
- BEGIN
- FOR re IN (SELECT ename FROM EMP) LOOP
- DBMS_OUTPUT.PUT_LINE(re.ename)
- END LOOP;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- SMITH
- ALLEN
- WARD
- JONES
說明:該種形式更為簡單,,省略了游標的定義,游標的SELECT查詢語句在循環(huán)中直接出現(xiàn),。
顯式游標屬性
雖然可以使用前面的形式獲得游標數(shù)據(jù),,但是在游標定義以后使用它的一些屬性來進行結(jié)構(gòu)控制是一種更為靈活的方法。顯式游標的屬性如下所示,。
Sql代碼
- 游標的屬性 返回值類型 意 義
- %ROWCOUNT 整型 獲得FETCH語句返回的數(shù)據(jù)行數(shù)
- %FOUND 布爾型 最近的FETCH語句返回一行數(shù)據(jù)則為真,,否則為假
- %NOTFOUND 布爾型 與%FOUND屬性返回值相反
- %ISOPEN 布爾型 游標已經(jīng)打開時值為真,否則為假
可按照以下形式取得游標的屬性:
游標名%屬性
要判斷游標emp_cursor是否處于打開狀態(tài),,可以使用屬性emp_cursor%ISOPEN,。如果游標已經(jīng)打開,則返回值為“真”,,否則為“假”,。具體可參照以下的訓練。
【訓練1】 使用游標的屬性練習,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- V_ename VARCHAR2(10);
- CURSOR emp_cursor IS
- SELECT ename FROM emp;
- BEGIN
- OPEN emp_cursor;
- IF emp_cursor%ISOPEN THEN
- LOOP
- FETCH emp_cursor INTO v_ename;
- EXIT WHEN emp_cursor%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
- END LOOP;
- ELSE
- DBMS_OUTPUT.PUT_LINE('用戶信息:游標沒有打開,!');
- END IF;
- CLOSE emp_cursor;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 1-SMITH
- 2-ALLEN
- 3-WARD
- PL/SQL 過程已成功完成。
說明:本例使用emp_cursor%ISOPEN判斷游標是否打開,;使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語句返回的數(shù)據(jù)行數(shù)并輸出,;使用循環(huán)來獲取數(shù)據(jù),在循環(huán)體中使用FETCH語句,;使用emp_cursor%NOTFOUND判斷FETCH語句是否成功執(zhí)行,,當FETCH語句失敗時說明數(shù)據(jù)已經(jīng)取完,退出循環(huán),。
【練習1】去掉OPEN emp_cursor;語句,,重新執(zhí)行以上程序。
游標參數(shù)的傳遞
【訓練1】 帶參數(shù)的游標,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- V_empno NUMBER(5);
- V_ename VARCHAR2(10);
- CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS
- SELECT empno, ename FROM emp
- WHERE deptno = p_deptno AND job = p_job;
- BEGIN
- OPEN emp_cursor(10, 'CLERK');
- LOOP
- FETCH emp_cursor INTO v_empno,v_ename;
- EXIT WHEN emp_cursor%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
- END LOOP;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 7934,MILLER
- PL/SQL 過程已成功完成,。
說明:游標emp_cursor定義了兩個參數(shù):p_deptno代表部門編號,p_job代表職務。語句OPEN emp_cursor(10, 'CLERK')傳遞了兩個參數(shù)值給游標,,即部門為10,、職務為CLERK,所以游標查詢的內(nèi)容是部門10的職務為CLERK的雇員,。循環(huán)部分用于顯示查詢的內(nèi)容,。
【練習1】修改Open語句的參數(shù):部門號為20、職務為ANALYST,,并重新執(zhí)行,。
也可以通過變量向游標傳遞參數(shù),但變量需要先于游標定義,,并在游標打開之前賦值,。對以上例子重新改動如下:
【訓練2】 通過變量傳遞參數(shù)給游標。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_empno NUMBER(5);
- v_ename VARCHAR2(10);
- v_deptno NUMBER(5);
- v_job VARCHAR2(10);
- CURSOR emp_cursor IS
- SELECT empno, ename FROM emp
- WHERE deptno = v_deptno AND job = v_job;
- BEGIN
- v_deptno:=10;
- v_job:='CLERK';
- OPEN emp_cursor;
- LOOP
- FETCH emp_cursor INTO v_empno,v_ename;
- EXIT WHEN emp_cursor%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
- END LOOP;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 7934,MILLER
- PL/SQL 過程已成功完成,。
說明:該程序與前一程序?qū)崿F(xiàn)相同的功能,。
動態(tài)SELECT語句和動態(tài)游標的用法
Oracle支持動態(tài)SELECT語句和動態(tài)游標,動態(tài)的方法大大擴展了程序設計的能力,。
對于查詢結(jié)果為一行的SELECT語句,,可以用動態(tài)生成查詢語句字符串的方法,在程序執(zhí)行階段臨時地生成并執(zhí)行,,語法是:
execute immediate 查詢語句字符串 into 變量1[,變量2...];
以下是一個動態(tài)生成SELECT語句的例子,。
【訓練1】 動態(tài)SELECT查詢。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- str varchar2(100);
- v_ename varchar2(10);
- begin
- str:='select ename from scott.emp where empno=7788';
- execute immediate str into v_ename;
- dbms_output.put_line(v_ename);
- END;
執(zhí)行結(jié)果為:
Sql代碼
- SCOTT
- PL/SQL 過程已成功完成,。
說明:SELECT...INTO...語句存放在STR字符串中,,通過EXECUTE語句執(zhí)行。
在變量聲明部分定義的游標是靜態(tài)的,,不能在程序運行過程中修改,。雖然可以通過參數(shù)傳遞來取得不同的數(shù)據(jù),但還是有很大的局限性,。通過采用動態(tài)游標,,可以在程序運行階段隨時生成一個查詢語句作為游標。要使用動態(tài)游標需要先定義一個游標類型,,然后聲明一個游標變量,,游標對應的查詢語句可以在程序的執(zhí)行過程中動態(tài)地說明。
定義游標類型的語句如下:
TYPE 游標類型名 REF CURSOR;
聲明游標變量的語句如下:
游標變量名 游標類型名;
在可執(zhí)行部分可以如下形式打開一個動態(tài)游標:
OPEN 游標變量名 FOR 查詢語句字符串;
【訓練2】 按名字中包含的字母順序分組顯示雇員信息,。
輸入并運行以下程序:
Sql代碼
- declare
- type cur_type is ref cursor;
- cur cur_type;
- rec scott.emp%rowtype;
- str varchar2(50);
- letter char:= 'A';
- begin
- loop
- str:= 'select ename from emp where ename like ''%'||letter||'%''';
- open cur for str;
- dbms_output.put_line('包含字母'||letter||'的名字:');
- loop
- fetch cur into rec.ename;
- exit when cur%notfound;
- dbms_output.put_line(rec.ename);
- end loop;
- exit when letter='Z';
- letter:=chr(ascii(letter)+1);
- end loop;
- end;
運行結(jié)果為:
Sql代碼
- 包含字母A的名字:
- ALLEN
- WARD
- MARTIN
- BLAKE
- CLARK
- ADAMS
- JAMES
- 包含字母B的名字:
- BLAKE
- 包含字母C的名字:
- CLARK
- SCOTT
說明:使用了二重循環(huán),,在外循環(huán)體中,動態(tài)生成游標的SELECT語句,,然后打開,。通過語句letter:=chr(ascii(letter)+1)可獲得字母表中的下一個字母,。
異常處理
錯誤處理
錯誤處理部分位于程序的可執(zhí)行部分之后,是由WHEN語句引導的多個分支構(gòu)成的,。錯誤處理的語法如下:
EXCEPTION
WHEN 錯誤1[OR 錯誤2] THEN
語句序列1,;
WHEN 錯誤3[OR 錯誤4] THEN
語句序列2;
WHEN OTHERS
語句序列n,;
END;
其中:
錯誤是在標準包中由系統(tǒng)預定義的標準錯誤,,或是由用戶在程序的說明部分自定義的錯誤,參見下一節(jié)系統(tǒng)預定義的錯誤類型,。
語句序列就是不同分支的錯誤處理部分,。
凡是出現(xiàn)在WHEN后面的錯誤都是可以捕捉到的錯誤,其他未被捕捉到的錯誤,,將在WHEN OTHERS部分進行統(tǒng)一處理,,OTHENS必須是EXCEPTION部分的最后一個錯誤處理分支。如要在該分支中進一步判斷錯誤種類,,可以通過使用預定義函數(shù)SQLCODE( )和SQLERRM( )來獲得系統(tǒng)錯誤號和錯誤信息。
如果在程序的子塊中發(fā)生了錯誤,,但子塊沒有錯誤處理部分,,則錯誤會傳遞到主程序中。
下面是由于查詢編號錯誤而引起系統(tǒng)預定義異常的例子,。
【訓練1】 查詢編號為1234的雇員名字,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_name VARCHAR2(10);
- BEGIN
- SELECT ename
- INTO v_name
- FROM emp
- WHERE empno = 1234;
- DBMS_OUTPUT.PUT_LINE('該雇員名字為:'|| v_name);
- EXCEPTION
- WHEN NO_DATA_FOUND THEN
- DBMS_OUTPUT.PUT_LINE('編號錯誤,沒有找到相應雇員,!');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('發(fā)生其他錯誤,!');
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 編號錯誤,沒有找到相應雇員,!
- PL/SQL 過程已成功完成,。
說明:在以上查詢中,因為編號為1234的雇員不存在,,所以將發(fā)生類型為“NO_DATA_
FOUND”的異常,。“NO_DATA_FOUND”是系統(tǒng)預定義的錯誤類型,,EXCEPTION部分下的WHEN語句將捕捉到該異常,,并執(zhí)行相應代碼部分。在本例中,,輸出用戶自定義的錯誤信息“編號錯誤,,沒有找到相應雇員!”。如果發(fā)生其他類型的錯誤,,將執(zhí)行OTHERS條件下的代碼部分,,顯示“發(fā)生其他錯誤!”,。
【訓練2】 由程序代碼顯示系統(tǒng)錯誤。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_temp NUMBER(5):=1;
- BEGIN
- v_temp:=v_temp/0;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('發(fā)生系統(tǒng)錯誤,!');
- DBMS_OUTPUT.PUT_LINE('錯誤代碼:'|| SQLCODE( ));
- DBMS_OUTPUT.PUT_LINE('錯誤信息:' ||SQLERRM( ));
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 發(fā)生系統(tǒng)錯誤,!
- 錯誤代碼:?1476
- 錯誤信息:ORA-01476: 除數(shù)為 0
- PL/SQL 過程已成功完成。
說明:程序運行中發(fā)生除零錯誤,,由WHEN OTHERS捕捉到,,執(zhí)行用戶自己的輸出語句顯示錯誤信息,然后正常結(jié)束,。在錯誤處理部分使用了預定義函數(shù)SQLCODE( )和SQLERRM( )來進一步獲得錯誤的代碼和種類信息,。
預定義錯誤
Oracle的系統(tǒng)錯誤很多,但只有一部分常見錯誤在標準包中予以定義,。定義的錯誤可以在EXCEPTION部分通過標準的錯誤名來進行判斷,,并進行異常處理。常見的系統(tǒng)預定義異常如下所示,。
Sql代碼
- 錯 誤 名 稱 錯誤代碼 錯 誤 含 義
- CURSOR_ALREADY_OPEN ORA_06511 試圖打開已經(jīng)打開的游標
- INVALID_CURSOR ORA_01001 試圖使用沒有打開的游標
- DUP_VAL_ON_INDEX ORA_00001 保存重復值到惟一索引約束的列中
- ZERO_DIVIDE ORA_01476 發(fā)生除數(shù)為零的除法錯誤
- INVALID_NUMBER ORA_01722 試圖對無效字符進行數(shù)值轉(zhuǎn)換
- ROWTYPE_MISMATCH ORA_06504 主變量和游標的類型不兼容
- VALUE_ERROR ORA_06502 轉(zhuǎn)換,、截斷或算術運算發(fā)生錯誤
- TOO_MANY_ROWS ORA_01422 SELECT…INTO…語句返回多于一行的數(shù)據(jù)
- NO_DATA_FOUND ORA_01403 SELECT…INTO…語句沒有數(shù)據(jù)返回
- TIMEOUT_ON_RESOURCE ORA_00051 等待資源時發(fā)生超時錯誤
- TRANSACTION_BACKED_OUT ORA_00060 由于死鎖,提交失敗
- STORAGE_ERROR ORA_06500 發(fā)生內(nèi)存錯誤
- PROGRAM_ERROR ORA_06501 發(fā)生PL/SQL內(nèi)部錯誤
- NOT_LOGGED_ON ORA_01012 試圖操作未連接的數(shù)據(jù)庫
- LOGIN_DENIED ORA_01017 在連接時提供了無效用戶名或口令
比如,,如果程序向表的主鍵列插入重復值,,則將發(fā)生DUP_VAL_ON_INDEX錯誤。
如果一個系統(tǒng)錯誤沒有在標準包中定義,,則需要在說明部分定義,,語法如下:
錯誤名 EXCEPTION;
定義后使用PRAGMA EXCEPTION_INIT來將一個定義的錯誤同一個特別的Oracle錯誤代碼相關聯(lián),就可以同系統(tǒng)預定義的錯誤一樣使用了,。語法如下:
PRAGMA EXCEPTION_INIT(錯誤名,,- 錯誤代碼);
【訓練1】 定義新的系統(tǒng)錯誤類型,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- V_ENAME VARCHAR2(10);
- NULL_INSERT_ERROR EXCEPTION;
- PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);
- BEGIN
- INSERT INTO EMP(EMPNO) VALUES(NULL);
- EXCEPTION
- WHEN NULL_INSERT_ERROR THEN
- DBMS_OUTPUT.PUT_LINE('無法插入NULL值,!');
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('發(fā)生其他系統(tǒng)錯誤!');
- END;
執(zhí)行結(jié)果為:
Sql代碼
- 無法插入NULL值,!
- PL/SQL 過程已成功完成,。
說明:NULL_INSERT_ERROR是自定義異常,同系統(tǒng)錯誤1400相關聯(lián),。
自定義異常
程序設計者可以利用引發(fā)異常的機制來進行程序設計,,自己定義異常類型??梢栽诼暶鞑糠侄x新的異常類型,,定義的語法是:
錯誤名 EXCEPTION;
用戶定義的錯誤不能由系統(tǒng)來觸發(fā),必須由程序顯式地觸發(fā),,觸發(fā)的語法是:
RAISE 錯誤名,;
RAISE也可以用來引發(fā)模擬系統(tǒng)錯誤,,比如,RAISE ZERO_DIVIDE將引發(fā)模擬的除零錯誤,。
使用RAISE_APPLICATION_ERROR函數(shù)也可以引發(fā)異常,。該函數(shù)要傳遞兩個參數(shù),第一個是用戶自定義的錯誤編號,,第二個參數(shù)是用戶自定義的錯誤信息,。使用該函數(shù)引發(fā)的異常的編號應該在20 000和20 999之間選擇。
自定義異常處理錯誤的方式同前,。
【訓練1】 插入新雇員,,限定插入雇員的編號在7000~8000之間。
Java代碼
- SET SERVEROUTPUT ON
- DECLARE
- new_no NUMBER(10);
- new_excp1 EXCEPTION;
- new_excp2 EXCEPTION;
- BEGIN
- new_no:=6789;
- INSERT INTO emp(empno,ename)
- VALUES(new_no, '小鄭');
- IF new_no<7000 THEN
- RAISE new_excp1;
- END IF;
- IF new_no>8000 THEN
- RAISE new_excp2;
- END IF;
- COMMIT;
- EXCEPTION
- WHEN new_excp1 THEN
- ROLLBACK;
- DBMS_OUTPUT.PUT_LINE('雇員編號小于7000的下限,!');
- WHEN new_excp2 THEN
- ROLLBACK;
- DBMS_OUTPUT.PUT_LINE('雇員編號超過8000的上限,!');
- END;
執(zhí)行結(jié)果為:
雇員編號小于7000的下限!
PL/SQL 過程已成功完成,。
說明:在此例中,,自定義了兩個異常:new_excp1和new_excp2,分別代表編號小于7000和編號大于8000的錯誤,。在程序中通過判斷編號大小,,產(chǎn)生對應的異常,并在異常處理部分回退插入操作,,然后顯示相應的錯誤信息。
【訓練2】 使用RAISE_APPLICATION_ERROR函數(shù)引發(fā)系統(tǒng)異常,。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- New_no NUMBER(10);
- BEGIN
- New_no:=6789;
- INSERT INTO emp(empno,ename)
- VALUES(new_no, 'JAMES');
- IF new_no<7000 THEN
- ROLLBACK;
- RAISE_APPLICATION_ERROR(-20001, '編號小于7000的下限,!');
- END IF;
- IF new_no>8000 THEN
- ROLLBACK;
- RAISE_APPLICATION_ERROR (-20002, '編號大于8000的下限!');
- END IF;
- END;
執(zhí)行結(jié)果為:
Sql代碼
- DECLARE
- *
- ERROR 位于第 1 行:
- ORA-20001: 編號小于7000的下限,!
- ORA-06512: 在line 9
說明:在本訓練中,,使用RAISE_APPLICATION_ERROR引發(fā)自定義異常,并以系統(tǒng)錯誤的方式進行顯示,。錯誤編號為20001和20002,。
注意:同上一個訓練比較,此種方法不需要事先定義異常,,可直接引發(fā),。
可以參考下面的程序片斷將出錯信息記錄到表中,其中,,errors為記錄錯誤信息的表,,SQLCODE為發(fā)生異常的錯誤編號,SQLERRM為發(fā)生異常的錯誤信息,。
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors
VALUES(v_error_code, v_error_message);
END;
【練習1】修改雇員的工資,,通過引發(fā)異??刂菩薷姆秶?00~6000之間。
階段訓練
【訓練1】 將雇員從一個表復制到另一個表,。
步驟1:創(chuàng)建一個結(jié)構(gòu)同EMP表一樣的新表EMP1:
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步驟2:通過指定雇員編號,,將雇員由EMP表移動到EMP1表:
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_empno NUMBER(5):=7788;
- emp_rec emp%ROWTYPE;
- BEGIN
- SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;
- DELETE FROM emp WHERE empno=v_empno;
- INSERT INTO emp1 VALUES emp_rec;
- IF SQL%FOUND THEN
- COMMIT;
- DBMS_OUTPUT.PUT_LINE('雇員復制成功!');
- ELSE
- ROLLBACK;
- DBMS_OUTPUT.PUT_LINE('雇員復制失??!');
- END IF;
- END;
執(zhí)行結(jié)果為:
雇員復制成功!
PL/SQL 過程已成功完成,。
步驟2:顯示復制結(jié)果:
SELECT empno,ename,job FROM emp1;
執(zhí)行結(jié)果為:
Sql代碼
- EMPNO ENAME JOB
- ------------- -------------- ----------------
- 7788 SCOTT ANALYST
說明:emp_rec變量是根據(jù)emp表定義的記錄變量,,SELECT...INTO...語句將整個記錄傳給該變量。INSERT語句將整個記錄變量插入emp1表,,如果插入成功(SQL%FOUND為真),,則提交事務,否則回滾撤銷事務,。試修改雇員編號為7902,,重新執(zhí)行以上程序。
【訓練2】 輸出雇員工資,,雇員工資用不同高度的*表示,。
輸入并執(zhí)行以下程序:
Sql代碼
- SET SERVEROUTPUT ON
- BEGIN
- FOR re IN (SELECT ename,sal FROM EMP) LOOP
- DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));
- END LOOP;
- END;
輸出結(jié)果為:
Sql代碼
- SMITH ********
- ALLEN ****************
- WARD *************
- JONES ******************************
- MARTIN *************
- BLAKE *****************************
- CLARK *****************************
- SCOTT ******************************
- KING **************************************************
- TURNER ***************
- ADAMS ***********
- JAMES **********
- FORD ******************************
- MILLER *************
- 執(zhí)行結(jié)果為:
- PL/SQL 過程已成功完成。
說明:第一個rpad函數(shù)產(chǎn)生對齊效果,,第二個rpad函數(shù)根據(jù)工資額產(chǎn)生不同數(shù)目的*,。該程序采用了隱式的簡略游標循環(huán)形式。
【訓練3】 編寫程序,,格式化輸出部門信息,。
輸入并執(zhí)行如下程序:
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_count number:=0;
- CURSOR dept_cursor IS SELECT * FROM dept;
- BEGIN
- DBMS_OUTPUT.PUT_LINE('部門列表');
- DBMS_OUTPUT.PUT_LINE('---------------------------------');
- FOR Dept_record IN dept_cursor LOOP
- DBMS_OUTPUT.PUT_LINE('部門編號:'|| Dept_record.deptno);
- DBMS_OUTPUT.PUT_LINE('部門名稱:'|| Dept_record.dname);
- DBMS_OUTPUT.PUT_LINE('所在城市:'|| Dept_record.loc);
- DBMS_OUTPUT.PUT_LINE('---------------------------------');
- v_count:= v_count+1;
- END LOOP;
- DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'個部門!');
- END;
輸出結(jié)果為:
Sql代碼
- 部門列表
- ------------------------------------
- 部門編號:10
- 部門名稱:ACCOUNTING
- 所在城市:NEW YORK
- ------------------------------------
- 部門編號:20
- 部門名稱:RESEARCH
- 所在城市:DALLAS
- ...
- 共有4個部門,!
- PL/SQL 過程已成功完成,。
說明:該程序中將字段內(nèi)容垂直排列。V_count變量記錄循環(huán)次數(shù),,即部門個數(shù),。
【訓練4】 已知每個部門有一個經(jīng)理,編寫程序,,統(tǒng)計輸出部門名稱,、部門總?cè)藬?shù)、總工資和部門經(jīng)理,。
輸入并執(zhí)行如下程序:
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_deptno number(8);
- v_count number(3);
- v_sumsal number(6);
- v_dname varchar2(15);
- v_manager varchar2(15);
- CURSOR list_cursor IS
- SELECT deptno,count(*),sum(sal) FROM emp group by deptno;
- BEGIN
- OPEN list_cursor;
- DBMS_OUTPUT.PUT_LINE('----------- 部 門 統(tǒng) 計 表 -----------');
- DBMS_OUTPUT.PUT_LINE('部門名稱 總?cè)藬?shù) 總工資 部門經(jīng)理');
- FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
- WHILE list_cursor%found LOOP
- SELECT dname INTO v_dname FROM dept
- WHERE deptno=v_deptno;
- SELECT ename INTO v_manager FROM emp
- WHERE deptno=v_deptno and job='MANAGER';
- DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)
- ||rpad(to_char(v_sumsal),9)||v_manager);
- FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
- END LOOP;
- DBMS_OUTPUT.PUT_LINE('--------------------------------------');
- CLOSE list_cursor;
- END;
輸出結(jié)果為:
Sql代碼
- -------------------- 部 門 統(tǒng) 計 表 -----------------
- 部門名稱 總?cè)藬?shù) 總工資 部門經(jīng)理
- ACCOUNTING 3 8750 CLARK
- RESEARCH 5 10875 JONES
- SALES 6 9400 BLAKE
- -------------------------------------------------------------
- PL/SQL 過程已成功完成,。
說明:游標中使用到了起分組功能的SELECT語句,統(tǒng)計出各部門的總?cè)藬?shù)和總工資,。再根據(jù)部門編號和職務找到部門的經(jīng)理,。該程序假定每個部門有一個經(jīng)理,。
【訓練5】 為雇員增加工資,從工資低的雇員開始,,為每個人增加原工資的10%,,限定所增加的工資總額為800元,顯示增加工資的人數(shù)和余額,。
輸入并調(diào)試以下程序:
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- V_NAME CHAR(10);
- V_EMPNO NUMBER(5);
- V_SAL NUMBER(8);
- V_SAL1 NUMBER(8);
- V_TOTAL NUMBER(8) := 800; --增加工資的總額
- V_NUM NUMBER(5):=0; --增加工資的人數(shù)
- CURSOR emp_cursor IS
- SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;
- BEGIN
- OPEN emp_cursor;
- DBMS_OUTPUT.PUT_LINE('姓名 原工資 新工資');
- DBMS_OUTPUT.PUT_LINE('---------------------------');
- LOOP
- FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;
- EXIT WHEN emp_cursor%NOTFOUND;
- V_SAL1:= V_SAL*0.1;
- IF V_TOTAL>V_SAL1 THEN
- V_TOTAL := V_TOTAL - V_SAL1;
- V_NUM:=V_NUM+1;
- DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||
- TO_CHAR(V_SAL+V_SAL1,'99999'));
- UPDATE EMP SET SAL=SAL+V_SAL1
- WHERE EMPNO=V_EMPNO;
- ELSE
- DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));
- END IF;
- END LOOP;
- DBMS_OUTPUT.PUT_LINE('---------------------------');
- DBMS_OUTPUT.PUT_LINE('增加工資人數(shù):'||V_NUM||' 剩余工資:'||V_TOTAL);
- CLOSE emp_cursor;
- COMMIT;
- END;
輸出結(jié)果為:
Sql代碼
- 姓名 原工資 新工資
- ---------------------------------------------
- SMITH 1289 1418
- JAMES 1531 1684
- MARTIN 1664 1830
- MILLER 1730 1903
- ALLEN 1760 1936
- ADAMS 1771 1771
- TURNER 1815 1815
- WARD 1830 1830
- BLAKE 2850 2850
- CLARK 2850 2850
- JONES 2975 2975
- FORD 3000 3000
- KING 5000 5000
- -----------------------------------------------
- 增加工資人數(shù):5 剩余工資:3
- PL/SQL 過程已成功完成,。
【練習1】按部門編號從小到大的順序輸出雇員名字、工資以及工資與平均工資的差,。
【練習2】為所有雇員增加工資,,工資在1000以內(nèi)的增加30%,工資在1000~2000之間的增加20%,,2000以上的增加10%,。
轉(zhuǎn)至http://www.cnblogs.com/adslg/archive/2010/08/17/1801469.html
|