游標和異常處理 oracle

1、游標和異常處理游標的概念游標是SQL的一個內存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數據庫中提取的數據塊。在某些情況下，需要把數據從存放在磁盤的表中調到計算機內存中進行處理，最后將處理結果顯示出來或最終寫回數據庫。這樣數據處理的速度才會提高，否則頻繁的磁盤數據交換會降低效率。游標有兩種類型：顯式游標和隱式游標。在前述程序中用到的SELECT.INTO.查詢語句，一次只能從數據庫中提取一行數據，對于這種形式的查詢和DML操作，系統(tǒng)都會使用一個隱式游標。但是如果要提取多行數據，就要由程序員定義一個顯式游標，并通過與游標有關的語句進行處理。顯式游標對應一個返回結果為多行多

2、列的SELECT語句。游標一旦打開，數據就從數據庫中傳送到游標變量中，然后應用程序再從游標變量中分解出需要的數據，并進行處理。隱式游標如前所述，DML操作和單行SELECT語句會使用隱式游標，它們是：* 插入操作：INSERT。* 更新操作：UPDATE。* 刪除操作：DELETE。* 單行查詢操作：SELECT . INTO .。當系統(tǒng)使用一個隱式游標時，可以通過隱式游標的屬性來了解操作的狀態(tài)和結果，進而控制程序的流程。隱式游標可以使用名字SQL來訪問，但要注意，通過SQL游標名總是只能訪問前一個DML操作或單行SELECT操作的游標屬性。所以通常在剛剛執(zhí)行完操作之后，立即使用SQL游標名來

3、訪問屬性。游標的屬性有四種，如下表所示。范例：使用隱式游標的屬性，判斷對雇員工資的修改是否成功。SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(成功修改雇員工資！); COMMIT; ELSEDBMS_OUTPUT.PUT_LINE(修改雇員工資失??！); END IF; END;說明：本例中，通過SQL%FOUND屬性判斷修改是否成功，并給出相應信息。顯式游標游標的定義和操作游標的使用分成以下4個步驟。1聲明游標在DECL

4、EAR部分按以下格式聲明游標：CURSOR 游標名(參數1 數據類型，參數2 數據類型.) IS SELECT語句;參數是可選部分，所定義的參數可以出現在SELECT語句的WHERE子句中。如果定義了參數，則必須在打開游標時傳遞相應的實際參數。SELECT語句是對表或視圖的查詢語句，甚至也可以是聯合查詢?？梢詭HERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT語句中可以使用在定義游標之前定義的變量。2打開游標在可執(zhí)行部分，按以下格式打開游標：OPEN 游標名(實際參數1，實際參數2.);打開游標時，SELECT語句的查詢結果就被傳送到了游標工作區(qū)。

5、3提取數據在可執(zhí)行部分，按以下格式將游標工作區(qū)中的數據取到變量中。提取操作必須在打開游標之后進行。FETCH 游標名 INTO 變量名1，變量名2.;或FETCH 游標名 INTO 記錄變量;游標打開后有一個指針指向數據區(qū)，FETCH語句一次返回指針所指的一行數據，要返回多行需重復執(zhí)行，可以使用循環(huán)語句來實現?？刂蒲h(huán)可以通過判斷游標的屬性來進行。下面對這兩種格式進行說明：第一種格式中的變量名是用來從游標中接收數據的變量，需要事先定義。變量的個數和類型應與SELECT語句中的字段變量的個數和類型一致。第二種格式一次將一行數據取到記錄變量中，需要使用%ROWTYPE事先定義記錄變量，這種形式使用

6、起來比較方便，不必分別定義和使用多個變量。定義記錄變量的方法如下：變量名 表名|游標名%ROWTYPE；其中的表必須存在，游標名也必須先定義。4關閉游標CLOSE 游標名;顯式游標打開后，必須顯式地關閉。游標一旦關閉，游標占用的資源就被釋放，游標變成無效，必須重新打開才能使用。范例：以下是使用顯式游標的一個簡單練習。用游標提取emp表中7788雇員的名稱和職務。(yb1.sql)SET SERVEROUTPUT ONDECLARE v_ename VARCHAR2(10); v_job VARCHAR2(10); CURSOR emp_cursor IS SELECT ename,job FR

7、OM 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;說明：該程序通過定義游標emp_cursor，提取并顯示雇員7788的名稱和職務。作為對以上例子的改進，在以下訓練中采用了記錄變量。范例：用游標提取emp表中7788雇員的姓名、職務和工資。(yb2.sql)SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor IS SELE

8、CT ename,job,sal FROM emp WHERE empno=7788; emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor; FETCH emp_cursor INTO emp_record;DBMS_OUTPUT.PUT_LINE(emp_record.ename|,| emp_record.job|,| to_char(emp_record.sal); CLOSE emp_cursor;END;說明：實例中使用記錄變量來接收數據，記錄變量由游標變量定義，需要出現在游標定義之后。注意：可通過以下形式獲得記錄變量的內容：記錄變量

9、名.字段名。范例： 顯示工資最高的前3名雇員的名稱和工資。（yb3.sql）SET SERVEROUTPUT ONDECLARE 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

10、 emp_cursor;END;說明：該程序在游標定義中使用了ORDER BY子句進行排序，并使用循環(huán)語句來提取多行數據。游標循環(huán)范例：使用特殊的FOR循環(huán)形式顯示全部雇員的編號和名稱。(yb4.sql)SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp;BEGINFOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno| Emp_record.ename);END LOOP;END;說明：可以看到該循環(huán)形式

11、非常簡單，隱含了記錄變量的定義、游標的打開、提取和關閉過程。Emp_record為隱含定義的記錄變量，循環(huán)的執(zhí)行次數與游標取得的數據的行數相一致。范例：另一種形式的游標循環(huán)。（yb5.sql）SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename); END LOOP;END;說明：該種形式更為簡單，省略了游標的定義，游標的SELECT查詢語句在循環(huán)中直接出現。顯式游標屬性雖然可以使用前面的形式獲得游標數據，但是在游標定義以后使用它的一些屬性來進行結構控制是一種

12、更為靈活的方法。顯式游標的屬性如下表所示。 可按照以下形式取得游標的屬性：游標名%屬性要判斷游標emp_cursor是否處于打開狀態(tài)，可以使用屬性emp_cursor%ISOPEN。如果游標已經打開，則返回值為“真”，否則為“假”。具體可參照以下的訓練。范例： 使用游標的屬性練習。(yb6.sql)SET SERVEROUTPUT ONDECLARE v_ename VARCHAR2(10); CURSOR emp_cursor IS SELECT ename FROM emp;BEGIN OPEN emp_cursor; IF emp_cursor%ISOPEN THENLOOP FETCH

13、 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;說明：本例使用emp_cursor%ISOPEN判斷游標是否打開；使用emp_cursor%ROWCOUNT獲得到目前為止FETCH語句返回的數據行數并輸出；使用循環(huán)來獲取數據，在循環(huán)體中使用FETCH

14、語句；使用emp_cursor%NOTFOUND判斷FETCH語句是否成功執(zhí)行，當FETCH語句失敗時說明數據已經取完，退出循環(huán)。練習:去掉OPEN emp_cursor;語句，重新執(zhí)行以上程序。游標參數的傳遞(了解)范例：帶參數的游標。（yb7.sql）SET SERVEROUTPUT ONDECLARE v_empno NUMBER(5); v_ename VARCHAR2(10); CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECTempno, ename FROM emp WHEREdeptno = p_deptno

15、 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;說明：游標emp_cursor定義了兩個參數：p_deptno代表部門編號，p_job代表職務。語句OPEN emp_cursor(10, CLERK)傳遞了兩個參數值給游標，即部門為10、職務為CLERK，所以游標查詢的內容是部門10的

16、職務為CLERK的雇員。循環(huán)部分用于顯示查詢的內容。練習:修改Open語句的參數：部門號為20、職務為ANALYST，并重新執(zhí)行。也可以通過變量向游標傳遞參數，但變量需要先于游標定義，并在游標打開之前賦值。對以上例子重新改動如下：范例： 通過變量傳遞參數給游標。(yb8.sql)SET SERVEROUTPUT ONDECLARE 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 WHE

17、REdeptno = 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;說明：該程序與前一程序實現相同的功能。利用游標刪除和修改數據的時候要注意：UPDATE 表名 SET WHERE CURRENT OF 游標名;DELETE 表名 WHE

18、RE CURRENT OF 游標名;范例：定義游標emp_cur。通過使用游標，根據職務調整雇員的工資(yb9.sql)SET SERVEROUTPUT ONDECLARE v_job emp.job%TYPE; CURSOR emp_cur IS SELECT job FROM emp FOR UPDATE;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_job; EXIT WHEN emp_cur%NOTFOUND; CASE WHEN v_job=CLERK THEN update emp set sal=sal+50 where curren

19、t of emp_cur; WHEN v_job=SALESMAN OR v_job=ANALYST THEN update emp set sal=sal+40 where current of emp_cur; ELSE update emp set sal=sal+10 where current of emp_cur;END CASE; END LOOP;COMMIT;END;/范例：用游標For循環(huán)，實現打印某一職務（輸入一職務）的雇員的雇員編號和雇員姓名。（yb10.sql）可參考yb4.sqlDECLAREv_job emp.job%TYPE; CURSOR emp_cursor

20、 IS SELECT empno, ename FROM emp WHERE job=v_job;BEGINv_job:=&v_job;FOR emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno| Emp_record.ename);END LOOP;END;異常處理錯誤處理錯誤處理部分位于程序的可執(zhí)行部分之后，是由WHEN語句引導的多個分支構成的。錯誤處理的語法如下：EXCEPTIONWHEN 錯誤1OR 錯誤2 THEN語句序列1；WHEN 錯誤3OR 錯誤4 THEN語句序列2；WHEN OTHERS語句

21、序列n；END; 其中：錯誤是在標準包中由系統(tǒng)預定義的標準錯誤，或是由用戶在程序的說明部分自定義的錯誤，參見下一節(jié)系統(tǒng)預定義的錯誤類型。語句序列就是不同分支的錯誤處理部分。凡是出現在WHEN后面的錯誤都是可以捕捉到的錯誤，其他未被捕捉到的錯誤，將在WHEN OTHERS部分進行統(tǒng)一處理，OTHERS必須是EXCEPTION部分的最后一個錯誤處理分支。如要在該分支中進一步判斷錯誤種類，可以通過使用預定義函數SQLCODE( )和SQLERRM( )來獲得系統(tǒng)錯誤號和錯誤信息。如果在程序的子塊中發(fā)生了錯誤，但子塊沒有錯誤處理部分，則錯誤會傳遞到主程序中。下面是由于查詢編號錯誤而引起系統(tǒng)預定義異常的

22、例子。范例：查詢編號為1234的雇員名字。SET SERVEROUTPUT ONDECLAREv_name VARCHAR2(10);BEGIN SELECTename INTOv_name FROMemp WHEREempno = 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;說明：在以上查詢中，因為編號為12

23、34的雇員不存在，所以將發(fā)生類型為“NO_DATA_FOUND”的異常?！癗O_DATA_FOUND”是系統(tǒng)預定義的錯誤類型，EXCEPTION部分下的WHEN語句將捕捉到該異常，并執(zhí)行相應代碼部分。在本例中，輸出用戶自定義的錯誤信息“編號錯誤，沒有找到相應雇員!”。如果發(fā)生其他類型的錯誤，將執(zhí)行OTHERS條件下的代碼部分，顯示“發(fā)生其他錯誤!”范例：由程序代碼顯示系統(tǒng)錯誤。SET SERVEROUTPUT ONDECLAREv_temp NUMBER(5):=1;BEGINv_temp:=v_temp/0;EXCEPTION WHEN OTHERS THENDBMS_OUTPUT.PUT_

24、LINE(發(fā)生系統(tǒng)錯誤！); DBMS_OUTPUT.PUT_LINE(錯誤代碼：| SQLCODE( ); DBMS_OUTPUT.PUT_LINE(錯誤信息： |SQLERRM( );END;說明：程序運行中發(fā)生除零錯誤，由WHEN OTHERS捕捉到，執(zhí)行用戶自己的輸出語句顯示錯誤信息，然后正常結束。在錯誤處理部分使用了預定義函數SQLCODE( )和SQLERRM( )來進一步獲得錯誤的代碼和種類信息。預定義錯誤（了解）Oracle的系統(tǒng)錯誤很多，但只有一部分常見錯誤在標準包中予以定義。定義的錯誤可以在EXCEPTION部分通過標準的錯誤名來進行判斷，并進行異常處理。常見的系統(tǒng)預定義異

25、常如下表所示。比如，如果程序向表的主鍵列插入重復值，則將發(fā)生DUP_VAL_ON_INDEX錯誤。如果一個系統(tǒng)錯誤沒有在標準包中定義，則需要在說明部分定義，語法如下：錯誤名 EXCEPTION;定義后使用PRAGMA EXCEPTION_INIT來將一個定義的錯誤同一個特別的Oracle錯誤代碼相關聯，就可以同系統(tǒng)預定義的錯誤一樣使用了。語法如下：PRAGMA EXCEPTION_INIT(錯誤名，- 錯誤代碼)；范例：定義新的系統(tǒng)錯誤類型。SET SERVEROUTPUT ONDECLAREV_ENAME VARCHAR2(10);NULL_INSERT_ERROR EXCEPTION;PR

26、AGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);BEGININSERT INTO EMP(EMPNO) VALUES(NULL);EXCEPTIONWHEN NULL_INSERT_ERROR THEN DBMS_OUTPUT.PUT_LINE(無法插入NULL值！); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(發(fā)生其他系統(tǒng)錯誤！);END;執(zhí)行結果為：無法插入NULL值！PL/SQL 過程已成功完成。說明：NULL_INSERT_ERROR是自定義異常，同系統(tǒng)錯誤1400相關聯。自定義異常程序設計者可以利用引發(fā)異常的

27、機制來進行程序設計，自己定義異常類型?？梢栽诼暶鞑糠侄x新的異常類型，定義的語法是：錯誤名 EXCEPTION;用戶定義的錯誤不能由系統(tǒng)來觸發(fā)，必須由程序顯式地觸發(fā)，觸發(fā)的語法是：RAISE 錯誤名；RAISE也可以用來引發(fā)模擬系統(tǒng)錯誤，比如，RAISE ZERO_DIVIDE將引發(fā)模擬的除零錯誤。使用RAISE_APPLICATION_ERROR函數也可以引發(fā)異常。該函數要傳遞兩個參數，第一個是用戶自定義的錯誤編號，第二個參數是用戶自定義的錯誤信息。使用該函數引發(fā)的異常的編號應該在20 000和20 999之間選擇。自定義異常處理錯誤的方式同前。范例：插入新雇員，限定插入雇員的編號在7000

28、8000之間。SET SERVEROUTPUT ONDECLAREnew_no NUMBER(10);new_excp1 EXCEPTION;new_excp2 EXCEPTION;BEGINnew_no:=6789;INSERT INTOemp(empno,ename) VALUES(new_no, 小鄭); IF new_no8000 THEN RAISE new_excp2; END IF; COMMIT;EXCEPTIONWHEN new_excp1 THENROLLBACK;DBMS_OUTPUT.PUT_LINE(雇員編號小于7000的下限！); WHEN new_excp2 TH

29、EN ROLLBACK; DBMS_OUTPUT.PUT_LINE(雇員編號超過8000的上限！);END;執(zhí)行結果為：雇員編號小于7000的下限！PL/SQL 過程已成功完成。說明：在此例中，自定義了兩個異常：new_excp1和new_excp2，分別代表編號小于7000和編號大于8000的錯誤。在程序中通過判斷編號大小，產生對應的異常，并在異常處理部分回退插入操作，然后顯示相應的錯誤信息。范例：使用RAISE_APPLICATION_ERROR函數引發(fā)系統(tǒng)異常。SET SERVEROUTPUT ONDECLARENew_no NUMBER(10);BEGIN New_no:=6789;

30、INSERT INTOemp(empno,ename) VALUES(new_no, JAMES);IF new_no8000 THEN ROLLBACK; RAISE_APPLICATION_ERROR (-20002, 編號大于8000的下限！); END IF;END;執(zhí)行結果為：DECLARE*ERROR 位于第 1 行:ORA-20001: 編號小于7000的下限！ORA-06512: 在line 9說明：在本訓練中，使用RAISE_APPLICATION_ERROR引發(fā)自定義異常，并以系統(tǒng)錯誤的方式進行顯示。錯誤編號為20001和20002。注意：同上一個訓練比較，此種方法不需要事

31、先定義異常，可直接引發(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;練習

32、：修改雇員的工資，通過引發(fā)異常控制修改范圍在6006000之間。存儲過程、函數和包認識存儲過程和函數存儲過程和函數也是一種PL/SQL塊，是存入數據庫的PL/SQL塊。但存儲過程和函數不同于已經介紹過的PL/SQL程序，我們通常把PL/SQL程序稱為無名塊，而存儲過程和函數是以命名的方式存儲于數據庫中的。和PL/SQL程序相比，存儲過程有很多優(yōu)點，具體歸納如下：存儲過程和函數以命名的數據庫對象形式存儲于數據庫當中。存儲在數據庫中的優(yōu)點是很明顯的，因為代碼不保存在本地，用戶可以在任何客戶機上登錄到數據庫，并調用或修改代碼。存儲過程和函數可由數據庫提供安全保證，要想使用存儲過程和函數，需要有存儲過

33、程和函數的所有者的授權，只有被授權的用戶或創(chuàng)建者本身才能執(zhí)行存儲過程或調用函數。存儲過程和函數的信息是寫入數據字典的，所以存儲過程可以看作是一個公用模塊，用戶編寫的PL/SQL程序或其他存儲過程都可以調用它(但存儲過程和函數不能調用PL/SQL程序)。一個重復使用的功能，可以設計成為存儲過程，比如：顯示一張工資統(tǒng)計表，可以設計成為存儲過程；一個經常調用的計算，可以設計成為存儲函數；根據雇員編號返回雇員的姓名，可以設計成存儲函數。像其他高級語言的過程和函數一樣，可以傳遞參數給存儲過程或函數，參數的傳遞也有多種方式。存儲過程可以有返回值，也可以沒有返回值，存儲過程的返回值必須通過參數帶回；函數有一

34、定的數據類型，像其他的標準函數一樣，我們可以通過對函數名的調用返回函數值。存儲過程和函數需要進行編譯，以排除語法錯誤，只有編譯通過才能調用。創(chuàng)建和刪除存儲過程創(chuàng)建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權限。該權限可由系統(tǒng)管理員授予。創(chuàng)建一個存儲過程的基本語句如下：CREATE OR REPLACE PROCEDURE 存儲過程名(參數IN|OUT|IN OUT 數據類型.)AS|IS說明部分BEGIN可執(zhí)行部分EXCEPTION 錯誤處理部分END 過程名;其中：可選關鍵字OR REPLACE 表示如果存儲過程已經存在，則用新的存儲過程

35、覆蓋，通常用于存儲過程的重建。參數部分用于定義多個參數(如果沒有參數，就可以省略)。參數有三種形式：IN、OUT和IN OUT。如果沒有指明參數的形式，則默認為IN。關鍵字AS也可以寫成IS，后跟過程的說明部分，可以在此定義過程的局部變量。編寫存儲過程可以使用任何文本編輯器或直接在SQL*Plus環(huán)境下進行，編寫好的存儲過程必須要在SQL*Plus環(huán)境下進行編譯，生成編譯代碼，原代碼和編譯代碼在編譯過程中都會被存入數據庫。編譯成功的存儲過程就可以在Oracle環(huán)境下進行調用了。一個存儲過程在不需要時可以刪除。刪除存儲過程的人是過程的創(chuàng)建者或者擁有DROP ANY PROCEDURE系統(tǒng)權限的人

36、。刪除存儲過程的語法如下：DROP PROCEDURE 存儲過程名；如果要重新編譯一個存儲過程，則只能是過程的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權限的人。語法如下：ALTER PROCEDURE 存儲過程名 COMPILE；執(zhí)行(或調用)存儲過程的人是過程的創(chuàng)建者或是擁有EXECUTE ANY PROCEDURE系統(tǒng)權限的人或是被擁有者授予EXECUTE權限的人。執(zhí)行的方法如下：方法1：EXECUTE 模式名.存儲過程名(參數.);方法2：BEGIN模式名.存儲過程名(參數.);END;傳遞的參數必須與定義的參數類型、個數和順序一致(如果參數定義了默認值，則調用時可以省略

37、參數)。參數可以是變量、常量或表達式，用法參見下一節(jié)。如果是調用本賬戶下的存儲過程，則模式名可以省略。要調用其他賬戶編寫的存儲過程，則模式名必須要添加。以下是一個生成和調用簡單存儲過程的訓練。注意要事先授予創(chuàng)建存儲過程的權限。范例：創(chuàng)建一個顯示雇員總人數的存儲過程。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入區(qū)中，輸入以下存儲過程：CREATE OR REPLACE PROCEDURE EMP_COUNTASV_TOTAL NUMBER(10);BEGIN SELECT COUNT(*) INTO V_TOTAL FROM EMP; DBMS_OUTPUT.PUT_LINE(雇員

38、總人數為：|V_TOTAL);END;步驟3：/執(zhí)行是進行編譯。如果存在錯誤，就會顯示:警告: 創(chuàng)建的過程帶有編譯錯誤。如果存在錯誤，對腳本進行修改，直到沒有錯誤產生。如果要想查看編譯的錯誤：show errors procedure emp_count;如果編譯結果正確，將顯示：過程已創(chuàng)建。步驟4：調用存儲過程，在輸入區(qū)中輸入以下語句并執(zhí)行：EXECUTE EMP_COUNT;顯示結果為：雇員總人數為：14PL/SQL 過程已成功完成。說明：在該訓練中，V_TOTAL變量是存儲過程定義的局部變量，用于接收查詢到的雇員總人數。注意：在SQL*Plus中輸入存儲過程，按“執(zhí)行”按鈕是進行編譯，不

39、是執(zhí)行存儲過程。如果在存儲過程中引用了其他用戶的對象，比如表，則必須有其他用戶授予的對象訪問權限。一個存儲過程一旦編譯成功，就可以由其他用戶或程序來引用。但存儲過程或函數的所有者必須授予其他用戶執(zhí)行該過程的權限。存儲過程沒有參數，在調用時，直接寫過程名即可。注意：用EXECUTE調用存儲過程只有兩種情況：一種是存儲過程不帶參數一種是存儲過程不帶輸出參數，帶輸入參數不是變量帶入是直接帶入范例：在PL/SQL程序中調用存儲過程。步驟1：登錄SCOTT賬戶。步驟2：授權STUDENT賬戶使用該存儲過程，即在SQL*Plus輸入區(qū)中，輸入以下的命令：GRANT EXECUTE ON EMP_COUNT

40、 TO STUDENT授權成功。步驟3：登錄STUDENT賬戶，在SQL*Plus輸入區(qū)中輸入以下程序：SET SERVEROUTPUT ONBEGINSCOTT.EMP_COUNT;END;步驟4：執(zhí)行以上程序，結果為：雇員總人數為：14PL/SQL 過程已成功完成。 說明：在本例中，存儲過程是由SCOTT賬戶創(chuàng)建的，STUDEN賬戶獲得SCOTT賬戶的授權后，才能調用該存儲過程。 注意：在程序中調用存儲過程，使用了第二種語法。范例：編寫顯示雇員信息的存儲過程EMP_LIST，并引用EMP_COUNT存儲過程。步驟1：在SQL*Plus輸入區(qū)中輸入并編譯以下存儲過程：CREATE OR RE

41、PLACE PROCEDURE EMP_LISTAS CURSOR emp_cursor IS SELECT empno,ename FROM emp;BEGINFOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename); END LOOP; EMP_COUNT;END;執(zhí)行結果：過程已創(chuàng)建。步驟2：調用存儲過程，在輸入區(qū)中輸入以下語句并執(zhí)行：EXECUTE EMP_LIST說明：以上的EMP_LIST存儲過程中定義并使用了游標，用來循環(huán)顯示所有雇員的信息。然后調用已經成功編

42、譯的存儲過程EMP_COUNT，用來附加顯示雇員總人數。通過EXECUTE命令來執(zhí)行EMP_LIST存儲過程。練習：編寫顯示部門信息的存儲過程DEPT_LIST，要求統(tǒng)計出部門個數。參數傳遞參數的作用是向存儲過程傳遞數據，或從存儲過程獲得返回結果。正確的使用參數可以大大增加存儲過程的靈活性和通用性。參數的類型有三種，如表8-1所示。參數的定義形式和作用如下：參數名 IN 數據類型 DEFAULT 值；定義一個輸入參數變量，用于傳遞參數給存儲過程。在調用存儲過程時，主程序的實際參數可以是常量、有值變量或表達式等。DEFAULT 關鍵字為可選項，用來設定參數的默認值。如果在調用存儲過程時不指明參數

43、，則參數變量取默認值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進行賦值。參數名 OUT 數據類型；定義一個輸出參數變量，用于從存儲過程獲取數據，即變量從存儲過程中返回值給主程序。在調用存儲過程時，主程序的實際參數只能是一個變量，而不能是常量或表達式。在存儲過程中，參數變量只能被賦值而不能將其用于賦值，在存儲過程中必須給輸出變量至少賦值一次。參數名 IN OUT 數據類型 DEFAULT 值；定義一個輸入、輸出參數變量，兼有以上兩者的功能。在調用存儲過程時，主程序的實際參數只能是一個變量，而不能是常量或表達式。DEFAULT 關鍵字為可選項，用來設定參數的默認值。在存儲過程中，變量接

44、收主程序傳遞的值，同時可以參加賦值運算，也可以對其進行賦值。在存儲過程中必須給變量至少賦值一次。注意：如果省略IN、OUT或IN OUT，則默認模式是IN。參數的定義只能給出類型，無需定義長度。范例：編寫給雇員增加工資的存儲過程CHANGE_SALARY，通過IN類型的參數傳遞要增加工資的雇員編號和增加的工資額。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入區(qū)中輸入以下存儲過程并執(zhí)行：CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)A

45、S V_ENAME VARCHAR2(10);V_SAL NUMBER(5);BEGIN SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO; UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO; DBMS_OUTPUT.PUT_LINE(雇員|V_ENAME|的工資被改為|TO_CHAR(V_SAL+P_RAISE); COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(發(fā)生錯誤，修改失??！); ROLLBA

46、CK;END;執(zhí)行結果為：過程已創(chuàng)建。步驟3：調用存儲過程，在輸入區(qū)中輸入以下語句并執(zhí)行：EXECUTE CHANGE_SALARY(7788,80)顯示結果為：雇員SCOTT的工資被改為3080 說明：從執(zhí)行結果可以看到，雇員SCOTT的工資已由原來的3000改為3080。參數的值由調用者傳遞，傳遞的參數的個數、類型和順序應該和定義的一致。如果順序不一致，可以采用以下調用方法。如上例，執(zhí)行語句可以改為：EXECUTE CHANGE_SALARY(P_RAISE=80,P_EMPNO=7788);可以看出傳遞參數的順序發(fā)生了變化，并且明確指出了參數名和要傳遞的值，=運算符左側是參數名，右側是參

47、數表達式，這種賦值方法的意義較清楚。練習：創(chuàng)建插入雇員的存儲過程INSERT_EMP，并將雇員編號等作為參數。在設計存儲過程的時候，也可以為參數設定默認值，這樣調用者就可以不傳遞或少傳遞參數了。練習：調用存儲過程CHANGE_SALARY，不傳遞參數，使用默認參數值。在SQL*Plus輸入區(qū)中輸入以下命令并執(zhí)行：EXECUTE CHANGE_SALARY顯示結果為：雇員SCOTT的工資被改為3090 說明：在存儲過程的調用中沒有傳遞參數，而是采用了默認值7788和10，即默認雇員號為7788，增加的工資為10。范例：使用OUT類型的參數返回存儲過程的結果。步驟1：登錄SCOTT賬戶。步驟2：在

48、SQL*Plus輸入區(qū)中輸入并編譯以下存儲過程：CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)ASBEGINSELECT COUNT(*) INTO P_TOTAL FROM EMP;END;執(zhí)行結果為：過程已創(chuàng)建。步驟3：輸入以下程序并執(zhí)行：DECLAREV_EMPCOUNT NUMBER;BEGINEMP_COUNT(V_EMPCOUNT);DBMS_OUTPUT.PUT_LINE(雇員總人數為：|V_EMPCOUNT);END;顯示結果為：雇員總人數為：14PL/SQL 過程已成功完成。說明：在存儲過程中定義了OUT類型

49、的參數P_TOTAL，在主程序調用該存儲過程時，傳遞了參數V_EMPCOUNT。在存儲過程中的SELECT.INTO.語句中對P_TOTAL進行賦值，賦值結果由V_EMPCOUNT變量帶回給主程序并顯示。以上程序要覆蓋同名的EMP_COUNT存儲過程，如果不使用OR REPLACE選項，就會出現以下錯誤：ERROR 位于第 1 行:ORA-00955: 名稱已由現有對象使用。練習：創(chuàng)建存儲過程，使用OUT類型參數獲得雇員經理名。創(chuàng)建和刪除存儲函數創(chuàng)建函數，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權限。該權限可由系統(tǒng)管理員授予。創(chuàng)建存儲函數的語法和創(chuàng)

50、建存儲過程的類似，即CREATE OR REPLACE FUNCTION 函數名(參數IN 數據類型.) RETURN 數據類型AS|IS說明部分BEGIN可執(zhí)行部分RETURN (表達式)EXCEPTION 錯誤處理部分END 函數名;其中，參數是可選的，但只能是IN類型(IN關鍵字可以省略)。在定義部分的RETURN 數據類型，用來表示函數的數據類型，也就是返回值的類型，此部分不可省略。在可執(zhí)行部分的RETURN(表達式)，用來生成函數的返回值，其表達式的類型應該和定義部分說明的函數返回值的數據類型一致。在函數的執(zhí)行部分可以有多個RETURN語句，但只有一個RETURN語句會被執(zhí)行，一旦執(zhí)

51、行了RETURN語句，則函數結束并返回調用環(huán)境。一個存儲函數在不需要時可以刪除，但刪除的人應是函數的創(chuàng)建者或者是擁有DROP ANY PROCEDURE系統(tǒng)權限的人。其語法如下：DROP FUNCTION 函數名；重新編譯一個存儲函數時，編譯的人應是函數的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權限的人。重新編譯一個存儲函數的語法如下：ALTER PROCEDURE 函數名 COMPILE；函數的調用者應是函數的創(chuàng)建者或擁有EXECUTE ANY PROCEDURE系統(tǒng)權限的人，或是被函數的擁有者授予了函數執(zhí)行權限的賬戶。函數的引用和存儲過程不同，函數要出現在程序體中，可以參加

52、表達式的運算或單獨出現在表達式中，其形式如下：變量名:=函數名(.)范例：創(chuàng)建一個通過雇員編號返回雇員名稱的函數GET_EMP_NAME。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入區(qū)中輸入以下存儲函數并編譯：CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)RETURN VARCHAR2AS V_ENAME VARCHAR2(10);BEGIN SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;RETURN(V_ENAME);EXCE

53、PTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(沒有該編號雇員！); RETURN (NULL); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(有重復雇員編號！); RETURN (NULL); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(發(fā)生其他錯誤！); RETURN (NULL);END;步驟3：調用該存儲函數，輸入并執(zhí)行以下程序：BEGIN DBMS_OUTPUT.PUT_LINE(雇員7369的名稱是：| GET_EMP_NAME(7369); DBMS

54、_OUTPUT.PUT_LINE(雇員7839的名稱是：| GET_EMP_NAME(7839);END;顯示結果為：雇員7369的名稱是：SMITH雇員7839的名稱是：KINGPL/SQL 過程已成功完成。說明：函數的調用直接出現在程序的DBMS_OUTPUT.PUT_LINE語句中，作為字符串表達式的一部分。如果輸入了錯誤的雇員編號，就會在函數的錯誤處理部分輸出錯誤信息。試修改雇員編號，重新運行調用部分。練習：創(chuàng)建一個通過部門編號返回部門名稱的存儲函數GET_DEPT_NAME。練習：將函數的執(zhí)行權限授予STUDENT賬戶，然后登錄STUDENT賬戶調用。存儲過程和函數的查看可以通過對數

55、據字典的訪問來查詢存儲過程或函數的有關信息，如果要查詢當前用戶的存儲過程或函數的源代碼，可以通過對USER_SOURCE數據字典視圖的查詢得到。USER_SOURCE的結構如下：DESCRIBE USER_SOURCE結果為：名稱 是否為空? 類型 - - - NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000)說明：里面按行存放著過程或函數的腳本，NAME是過程或函數名，TYPE 代表類型(PROCEDURE或FUNCTION)，LINE是行號，TEXT 為腳本。范例：查詢過程EMP_COUNT的腳本。在SQL

56、*Plus中輸入并執(zhí)行如下查詢：select TEXT from user_source WHERE NAME=EMP_COUNT;結果為：TEXT-PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)ASBEGIN SELECT COUNT(*) INTO P_TOTAL FROM EMP;END;范例：查詢過程GET_EMP_NAME的參數。在SQL*Plus中輸入并執(zhí)行如下查詢：DESCRIBE GET_EMP_NAME結果為：FUNCTION GET_EMP_NAME RETURNS VARCHAR2參數名稱 類型 輸入/輸出默認值？- - - - P_EMP

57、NO NUMBER(4) IN DEFAULT范例：在發(fā)生編譯錯誤時，顯示錯誤。SHOW ERRORS說明：查詢一個存儲過程或函數是否是有效狀態(tài)(即編譯成功)，可以使用數據字典USER_OBJECTS的STATUS列。范例：查詢EMP_LIST存儲過程是否可用：SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=EMP_LIST;結果為：STATUS-VALID說明：VALID表示該存儲過程有效(即通過編譯)，INVALID表示存儲過程無效或需要重新編譯。當Oracle調用一個無效的存儲過程或函數時，首先試圖對其進行編譯，如果編譯成功則將狀態(tài)置成

58、VALID并執(zhí)行，否則給出錯誤信息。當一個存儲過程編譯成功，狀態(tài)變?yōu)閂ALID，會不會在某些情況下變成INVALID。結論是完全可能的。比如一個存儲過程中包含對表的查詢，如果表被修改或刪除，存儲過程就會變成無效INVALID。所以要注意存儲過程和函數對其他對象的依賴關系。如果要檢查存儲過程或函數的依賴性，可以通過查詢數據字典USER_DENPENDENCIES來確定，該表結構如下：DESCRIBE USER_DEPENDENCIES;結果：名稱 是否為空? 類型 - - - NAME NOT NULLVARCHAR2(30) TYPE VARCHAR2(12)REFERENCED_OWNER

59、VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12)REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4)說明：NAME為實體名，TYPE為實體類型，REFERENCED_OWNER為涉及到的實體擁有者賬戶，REFERENCED_NAME為涉及到的實體名，REFERENCED_TYPE 為涉及到的實體類型。范例： 查詢EMP_LIST存儲過程的依賴性。SELECT REFERENCED_NAME,REFER

60、ENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME=EMP_LIST;說明：可以看出存儲過程EMP_LIST依賴一些系統(tǒng)包、EMP表和EMP_COUNT存儲過程。如果刪除了EMP表或EMP_COUNT存儲過程，EMP_LIST將變成無效。還有一種情況需要我們注意：如果一個用戶A被授予執(zhí)行屬于用戶B的一個存儲過程的權限，在用戶B的存儲過程中，訪問到用戶C的表，用戶B被授予訪問用戶C的表的權限，但用戶A沒有被授予訪問用戶C表的權限，那么用戶A調用用戶B的存儲過程是失敗的還是成功的呢？答案是成功的。如果讀者有興趣，不妨進行一下實際測試。附錄資料：不需要的可以自行