遊标的概念:
遊标是SQL的一個記憶體工作區，由系統或使用者以變量的形式定義。遊标的作用就是用于臨時存儲從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁盤的表中調到計算機記憶體中進行處理，最後将處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁盤資料交換會降低效率。
遊标有兩種類型：顯式遊标和隐式遊标。在前述程式中用到的SELECT...INTO...查詢語句，一次隻能從資料庫中提取一行資料，對于這種形式的查詢和DML操作，系統都會使用一個隐式遊标。但是如果要提取多行資料，就要由程式員定義一個顯式遊标，并通過與遊标有關的語句進行處理。顯式遊标對應一個傳回結果為多行多列的SELECT語句。
遊标一旦打開，資料就從資料庫中傳送到遊标變量中，然後應用程式再從遊标變量中分解出需要的資料，并進行處理。
隐式遊标
如前所述，DML操作和單行SELECT語句會使用隐式遊标，它們是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 單行查詢操作：SELECT ... INTO ...。
當系統使用一個隐式遊标時，可以通過隐式遊标的屬性來了解操作的狀态和結果，進而控制程式的流程。隐式遊标可以使用名字SQL來通路，但要注意，通過SQL遊标名總是隻能通路前一個DML操作或單行SELECT操作的遊标屬性。是以通常在剛剛執行完操作之後，立即使用SQL遊标名來通路屬性。遊标的屬性有四種，如下所示。
Sql代碼
- 隐式遊标的屬性 傳回值類型 意 義
- SQL%ROWCOUNT 整型 代表DML語句成功執行的資料行數
- SQL%FOUND 布爾型 值為TRUE代表插入、删除、更新或單行查詢操作成功
- SQL%NOTFOUND 布爾型 與SQL%FOUND屬性傳回值相反
- SQL%ISOPEN 布爾型 DML執行過程中為真，結束後為假
【訓練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;
運作結果為：
Sql代碼
- 修改雇員工資失敗！
- PL/SQL 過程已成功完成。
步驟2：将雇員編号1234改為7788，重新執行以上程式：
運作結果為：
Sql代碼
- 成功修改雇員工資！
- PL/SQL 過程已成功完成。
說明：本例中，通過SQL%FOUND屬性判斷修改是否成功，并給出相應資訊。
顯式遊标
遊标的定義和操作
遊标的使用分成以下4個步驟。
1．聲明遊标
在DECLEAR部分按以下格式聲明遊标：
CURSOR 遊标名[(參數1 資料類型[，參數2 資料類型...])]
IS SELECT語句;
參數是可選部分，所定義的參數可以出現在SELECT語句的WHERE子句中。如果定義了參數，則必須在打開遊标時傳遞相應的實際參數。
SELECT語句是對表或視圖的查詢語句，甚至也可以是聯合查詢。可以帶WHERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT語句中可以使用在定義遊标之前定義的變量。
2．打開遊标
在可執行部分，按以下格式打開遊标：
OPEN 遊标名[(實際參數1[，實際參數2...])];
打開遊标時，SELECT語句的查詢結果就被傳送到了遊标工作區。
3．提取資料
在可執行部分，按以下格式将遊标工作區中的資料取到變量中。提取操作必須在打開遊标之後進行。
FETCH 遊标名 INTO 變量名1[，變量名2...];
或
FETCH 遊标名 INTO 記錄變量;
遊标打開後有一個指針指向資料區，FETCH語句一次傳回指針所指的一行資料，要傳回多行需重複執行，可以使用循環語句來實作。控制循環可以通過判斷遊标的屬性來進行。
下面對這兩種格式進行說明：
第一種格式中的變量名是用來從遊标中接收資料的變量，需要事先定義。變量的個數和類型應與SELECT語句中的字段變量的個數和類型一緻。
第二種格式一次将一行資料取到記錄變量中，需要使用%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;
執行結果為:
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;
執行結果為:
Sql代碼
- SCOTT,ANALYST,3000
- PL/SQL 過程已成功完成。
說明：執行個體中使用記錄變量來接收資料，記錄變量由遊标變量定義，需要出現在遊标定義之後。
注意：可通過以下形式獲得記錄變量的内容：
記錄變量名.字段名。
【訓練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;
執行結果為:
Sql代碼
- KING,5000
- SCOTT,3000
- FORD,3000
- PL/SQL 過程已成功完成。
說明：該程式在遊标定義中使用了ORDER BY子句進行排序，并使用循環語句來提取多行資料。
遊标循環
【訓練1】 使用特殊的FOR循環形式顯示全部雇員的編号和名稱。
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;
執行結果為：
Sql代碼
- 7369SMITH
- 7499ALLEN
- 7521WARD
- 7566JONES
- PL/SQL 過程已成功完成。
說明：可以看到該循環形式非常簡單，隐含了記錄變量的定義、遊标的打開、提取和關閉過程。Emp_record為隐含定義的記錄變量，循環的執行次數與遊标取得的資料的行數相一緻。
【訓練2】 另一種形式的遊标循環。
Sql代碼
- SET SERVEROUTPUT ON
- BEGIN
- FOR re IN (SELECT ename FROM EMP) LOOP
- DBMS_OUTPUT.PUT_LINE(re.ename)
- END LOOP;
- END;
執行結果為：
Sql代碼
- SMITH
- ALLEN
- WARD
- JONES
說明：該種形式更為簡單，省略了遊标的定義，遊标的SELECT查詢語句在循環中直接出現。
顯式遊标屬性
雖然可以使用前面的形式獲得遊标資料，但是在遊标定義以後使用它的一些屬性來進行結構控制是一種更為靈活的方法。顯式遊标的屬性如下所示。
Sql代碼
- 遊标的屬性 傳回值類型 意 義
- %ROWCOUNT 整型 獲得FETCH語句傳回的資料行數
- %FOUND 布爾型 最近的FETCH語句傳回一行資料則為真，否則為假
- %NOTFOUND 布爾型 與%FOUND屬性傳回值相反
- %ISOPEN 布爾型 遊标已經打開時值為真，否則為假
可按照以下形式取得遊标的屬性：
遊标名%屬性
要判斷遊标emp_cursor是否處于打開狀态，可以使用屬性emp_cursor%ISOPEN。如果遊标已經打開，則傳回值為“真”，否則為“假”。具體可參照以下的訓練。
【訓練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;
執行結果為：
Sql代碼
- 1-SMITH
- 2-ALLEN
- 3-WARD
- PL/SQL 過程已成功完成。
說明：本例使用emp_cursor%ISOPEN判斷遊标是否打開；使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語句傳回的資料行數并輸出；使用循環來擷取資料，在循環體中使用FETCH語句；使用emp_cursor%NOTFOUND判斷FETCH語句是否成功執行，當FETCH語句失敗時說明資料已經取完，退出循環。
【練習1】去掉OPEN emp_cursor;語句，重新執行以上程式。
遊标參數的傳遞
【訓練1】 帶參數的遊标。
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;
執行結果為：
Sql代碼
- 7934,MILLER
- PL/SQL 過程已成功完成。
說明：遊标emp_cursor定義了兩個參數：p_deptno代表部門編号，p_job代表職務。語句OPEN emp_cursor(10, 'CLERK')傳遞了兩個參數值給遊标，即部門為10、職務為CLERK，是以遊标查詢的内容是部門10的職務為CLERK的雇員。循環部分用于顯示查詢的内容。
【練習1】修改Open語句的參數：部門号為20、職務為ANALYST，并重新執行。
也可以通過變量向遊标傳遞參數，但變量需要先于遊标定義，并在遊标打開之前指派。對以上例子重新改動如下：
【訓練2】 通過變量傳遞參數給遊标。
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;
執行結果為：
Sql代碼
- 7934,MILLER
- PL/SQL 過程已成功完成。
說明：該程式與前一程式實作相同的功能。
動态SELECT語句和動态遊标的用法
Oracle支援動态SELECT語句和動态遊标，動态的方法大大擴充了程式設計的能力。
對于查詢結果為一行的SELECT語句，可以用動态生成查詢語句字元串的方法，在程式執行階段臨時地生成并執行，文法是：
execute immediate 查詢語句字元串 into 變量1[,變量2...];
以下是一個動态生成SELECT語句的例子。
【訓練1】 動态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;
執行結果為：
Sql代碼
- SCOTT
- PL/SQL 過程已成功完成。
說明：SELECT...INTO...語句存放在STR字元串中，通過EXECUTE語句執行。
在變量聲明部分定義的遊标是靜态的，不能在程式運作過程中修改。雖然可以通過參數傳遞來取得不同的資料，但還是有很大的局限性。通過采用動态遊标，可以在程式運作階段随時生成一個查詢語句作為遊标。要使用動态遊标需要先定義一個遊标類型，然後聲明一個遊标變量，遊标對應的查詢語句可以在程式的執行過程中動态地說明。
定義遊标類型 的語句如下：
TYPE 遊标類型名 REF CURSOR;
聲明遊标變量的語句如下：
遊标變量名 遊标類型名;
在可執行部分可以如下形式打開一個動态遊标：
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;
運作結果為：
Sql代碼
- 包含字母A的名字：
- ALLEN
- WARD
- MARTIN
- BLAKE
- CLARK
- ADAMS
- JAMES
- 包含字母B的名字：
- BLAKE
- 包含字母C的名字：
- CLARK
- SCOTT
說明：使用了二重循環，在外循環體中，動态生成遊标的SELECT語句，然後打開。通過語句letter:=chr(ascii(letter)+1)可獲得字母表中的下一個字母。
異常處理
錯誤處理
錯誤處理部分位于程式的可執行部分之後，是由WHEN語句引導的多個分支構成的。錯誤處理的文法如下：
EXCEPTION
WHEN 錯誤1[OR 錯誤2] THEN
語句序列1；
WHEN 錯誤3[OR 錯誤4] THEN
語句序列2；
WHEN OTHERS
語句序列n；
END;
其中：
錯誤是在标準包中由系統預定義的标準錯誤，或是由使用者在程式的說明部分自定義的錯誤，參見下一節系統預定義的錯誤類型。
語句序列就是不同分支的錯誤處理部分。
凡是出現在WHEN後面的錯誤都是可以捕捉到的錯誤，其他未被捕捉到的錯誤，将在WHEN OTHERS部分進行統一處理，OTHENS必須是EXCEPTION部分的最後一個錯誤處理分支。如要在該分支中進一步判斷錯誤種類，可以通過使用預定義函數SQLCODE( )和SQLERRM( )來獲得系統錯誤号和錯誤資訊。
如果在程式的子塊中發生了錯誤，但子塊沒有錯誤處理部分，則錯誤會傳遞到主程式中。
下面是由于查詢編号錯誤而引起系統預定義異常的例子。
【訓練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('發生其他錯誤！');
- END;
執行結果為：
Sql代碼
- 編号錯誤，沒有找到相應雇員！
- PL/SQL 過程已成功完成。
說明：在以上查詢中，因為編号為1234的雇員不存在，是以将發生類型為“NO_DATA_
FOUND”的異常。“NO_DATA_FOUND”是系統預定義的錯誤類型，EXCEPTION部分下的WHEN語句将捕捉到該異常，并執行相應代碼部分。在本例中，輸出使用者自定義的錯誤資訊“編号錯誤，沒有找到相應雇員!”。如果發生其他類型的錯誤，将執行OTHERS條件下的代碼部分，顯示“發生其他錯誤!”。
【訓練2】 由程式代碼顯示系統錯誤。
Sql代碼
- SET SERVEROUTPUT ON
- DECLARE
- v_temp NUMBER(5):=1;
- BEGIN
- v_temp:=v_temp/0;
- EXCEPTION
- WHEN OTHERS THEN
- DBMS_OUTPUT.PUT_LINE('發生系統錯誤！');
- DBMS_OUTPUT.PUT_LINE('錯誤代碼：'|| SQLCODE( ));
- DBMS_OUTPUT.PUT_LINE('錯誤資訊：' ||SQLERRM( ));
- END;
執行結果為：
Sql代碼
- 發生系統錯誤！
- 錯誤代碼：?1476
- 錯誤資訊：ORA-01476: 除數為 0
- PL/SQL 過程已成功完成。
說明：程式運作中發生除零錯誤，由WHEN OTHERS捕捉到，執行使用者自己的輸出語句顯示錯誤資訊，然後正常結束。在錯誤處理部分使用了預定義函數SQLCODE( )和SQLERRM( )來進一步獲得錯誤的代碼和種類資訊。
預定義錯誤
Oracle的系統錯誤很多，但隻有一部分常見錯誤在标準包中予以定義。定義的錯誤可以在EXCEPTION部分通過标準的錯誤名來進行判斷，并進行異常處理。常見的系統預定義異常如下所示。
Sql代碼
- 錯 誤 名 稱 錯誤代碼 錯 誤 含 義
- CURSOR_ALREADY_OPEN ORA_06511 試圖打開已經打開的遊标
- INVALID_CURSOR ORA_01001 試圖使用沒有打開的遊标
- DUP_VAL_ON_INDEX ORA_00001 儲存重複值到惟一索引限制的列中
- ZERO_DIVIDE ORA_01476 發生除數為零的除法錯誤
- INVALID_NUMBER ORA_01722 試圖對無效字元進行數值轉換
- ROWTYPE_MISMATCH ORA_06504 主變量和遊标的類型不相容
- VALUE_ERROR ORA_06502 轉換、截斷或算術運算發生錯誤
- TOO_MANY_ROWS ORA_01422 SELECT…INTO…語句傳回多于一行的資料
- NO_DATA_FOUND ORA_01403 SELECT…INTO…語句沒有資料傳回
- TIMEOUT_ON_RESOURCE ORA_00051 等待資源時發生逾時錯誤
- TRANSACTION_BACKED_OUT ORA_00060 由于死鎖，送出失敗
- STORAGE_ERROR ORA_06500 發生記憶體錯誤
- PROGRAM_ERROR ORA_06501 發生PL/SQL内部錯誤
- NOT_LOGGED_ON ORA_01012 試圖操作未連接配接的資料庫
- LOGIN_DENIED ORA_01017 在連接配接時提供了無效使用者名或密碼
比如，如果程式向表的主鍵列插入重複值，則将發生DUP_VAL_ON_INDEX錯誤。
如果一個系統錯誤沒有在标準包中定義，則需要在說明部分定義，文法如下：
錯誤名 EXCEPTION;
定義後使用PRAGMA EXCEPTION_INIT來将一個定義的錯誤同一個特别的Oracle錯誤代碼相關聯，就可以同系統預定義的錯誤一樣使用了。文法如下：
PRAGMA EXCEPTION_INIT(錯誤名，- 錯誤代碼)；
【訓練1】 定義新的系統錯誤類型。
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('發生其他系統錯誤！');
- END;
執行結果為：
Sql代碼
- 無法插入NULL值！
- PL/SQL 過程已成功完成。
說明：NULL_INSERT_ERROR是自定義異常，同系統錯誤1400相關聯。
自定義異常
程式設計者可以利用引發異常的機制來進行程式設計，自己定義異常類型。可以在聲明部分定義新的異常類型，定義的文法是：
錯誤名 EXCEPTION;
使用者定義的錯誤不能由系統來觸發，必須由程式顯式地觸發，觸發的文法是：
RAISE 錯誤名；
RAISE也可以用來引發模拟系統錯誤，比如，RAISE ZERO_DIVIDE将引發模拟的除零錯誤。
使用RAISE_APPLICATION_ERROR函數也可以引發異常。該函數要傳遞兩個參數，第一個是使用者自定義的錯誤編号，第二個參數是使用者自定義的錯誤資訊。使用該函數引發的異常的編号應該在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;
執行結果為：
雇員編号小于7000的下限！
PL/SQL 過程已成功完成。
說明：在此例中，自定義了兩個異常：new_excp1和new_excp2，分别代表編号小于7000和編号大于8000的錯誤。在程式中通過判斷編号大小，産生對應的異常，并在異常處理部分回退插入操作，然後顯示相應的錯誤資訊。
【訓練2】 使用RAISE_APPLICATION_ERROR函數引發系統異常。
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;
執行結果為：
Sql代碼
- DECLARE
- *
- ERROR 位于第 1 行:
- ORA-20001: 編号小于7000的下限！
- ORA-06512: 在line 9
說明：在本訓練中，使用RAISE_APPLICATION_ERROR引發自定義異常，并以系統錯誤的方式進行顯示。錯誤編号為20001和20002。
注意：同上一個訓練比較，此種方法不需要事先定義異常，可直接引發。
可以參考下面的程式片斷将出錯資訊記錄到表中，其中，errors為記錄錯誤資訊的表，SQLCODE為發生異常的錯誤編号，SQLERRM為發生異常的錯誤資訊。
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】修改雇員的工資，通過引發異常控制修改範圍在600～6000之間。
階段訓練
【訓練1】 将雇員從一個表複制到另一個表。
步驟1：建立一個結構同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;
執行結果為：
雇員複制成功！
PL/SQL 過程已成功完成。
步驟2：顯示複制結果：
SELECT empno,ename,job FROM emp1;
執行結果為：
Sql代碼
- EMPNO ENAME JOB
- ------------- -------------- ----------------
- 7788 SCOTT ANALYST
說明：emp_rec變量是根據emp表定義的記錄變量，SELECT...INTO...語句将整個記錄傳給該變量。INSERT語句将整個記錄變量插入emp1表，如果插入成功(SQL%FOUND為真)，則送出事務，否則復原撤銷事務。試修改雇員編号為7902，重新執行以上程式。
【訓練2】 輸出雇員工資，雇員工資用不同高度的*表示。
輸入并執行以下程式：
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;
輸出結果為：
Sql代碼
- SMITH ********
- ALLEN ****************
- WARD *************
- JONES ******************************
- MARTIN *************
- BLAKE *****************************
- CLARK *****************************
- SCOTT ******************************
- KING **************************************************
- TURNER ***************
- ADAMS ***********
- JAMES **********
- FORD ******************************
- MILLER *************
- 執行結果為：
- PL/SQL 過程已成功完成。
說明：第一個rpad函數産生對齊效果，第二個rpad函數根據工資額産生不同數目的*。該程式采用了隐式的簡略遊标循環形式。
【訓練3】 編寫程式，格式化輸出部門資訊。
輸入并執行如下程式：
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;
輸出結果為：
Sql代碼
- 部門清單
- ------------------------------------
- 部門編号：10
- 部門名稱：ACCOUNTING
- 所在城市：NEW YORK
- ------------------------------------
- 部門編号：20
- 部門名稱：RESEARCH
- 所在城市：DALLAS
- ...
- 共有4個部門！
- PL/SQL 過程已成功完成。
說明：該程式中将字段内容垂直排列。V_count變量記錄循環次數，即部門個數。
【訓練4】 已知每個部門有一個經理，編寫程式，統計輸出部門名稱、部門總人數、總工資和部門經理。
輸入并執行如下程式：
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('----------- 部 門 統 計 表 -----------');
- DBMS_OUTPUT.PUT_LINE('部門名稱 總人數 總工資 部門經理');
- 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;
輸出結果為：
Sql代碼
- -------------------- 部 門 統 計 表 -----------------
- 部門名稱 總人數 總工資 部門經理
- ACCOUNTING 3 8750 CLARK
- RESEARCH 5 10875 JONES
- SALES 6 9400 BLAKE
- -------------------------------------------------------------
- PL/SQL 過程已成功完成。
說明：遊标中使用到了起分組功能的SELECT語句，統計出各部門的總人數和總工資。再根據部門編号和職務找到部門的經理。該程式假定每個部門有一個經理。
【訓練5】 為雇員增加工資，從工資低的雇員開始，為每個人增加原工資的10%，限定所增加的工資總額為800元，顯示增加工資的人數和餘額。
輸入并調試以下程式：
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; --增加工資的人數
- 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('增加工資人數:'||V_NUM||' 剩餘工資：'||V_TOTAL);
- CLOSE emp_cursor;
- COMMIT;
- END;
輸出結果為：
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
- -----------------------------------------------
- 增加工資人數：5 剩餘工資：3
- PL/SQL 過程已成功完成。
【練習1】按部門編号從小到大的順序輸出雇員名字、工資以及工資與平均工資的差。
【練習2】為所有雇員增加工資，工資在1000以内的增加30%，工資在1000～2000之間的增加20%，2000以上的增加10%。
轉載：http://heisetoufa.iteye.com/blog/366483/
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)