游標和異常處理 oracle_第1頁
游標和異常處理 oracle_第2頁
游標和異常處理 oracle_第3頁
游標和異常處理 oracle_第4頁
游標和異常處理 oracle_第5頁
已閱讀5頁,還剩55頁未讀, 繼續(xù)免費閱讀

下載本文檔

版權說明:本文檔由用戶提供并上傳,收益歸屬內容提供方,若內容存在侵權,請進行舉報或認領

文檔簡介

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的存儲過程是失敗的還是成功的呢?答案是成功的。如果讀者有興趣,不妨進行一下實際測試。附錄資料:不需要的可以自行

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯系上傳者。文件的所有權益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網頁內容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
  • 4. 未經權益所有人同意不得將文件中的內容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網僅提供信息存儲空間,僅對用戶上傳內容的表現方式做保護處理,對用戶上傳分享的文檔內容本身不做任何修改或編輯,并不能對任何下載內容負責。
  • 6. 下載文件中如有侵權或不適當內容,請與我們聯系,我們立即糾正。
  • 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論