1、本章主要介紹了oracle 創(chuàng)建,刪除存儲過程,參數(shù)傳遞,創(chuàng)建,刪除存儲函數(shù),存儲過程和函數(shù)的查看,包,系統(tǒng)包等。認(rèn)識存儲過程和函數(shù)存儲過程和函數(shù)也是一種PL/SQL塊，是存入數(shù)據(jù)庫的PL/SQL塊。但存儲過程和函數(shù)不同于已經(jīng)介紹過的PL/SQL程序，我們通常把PL/SQL程序稱為無名塊，而存儲過程和函數(shù)是以命名的方式存儲于數(shù)據(jù)庫中的。和PL/SQL程序相比，存儲過程有很多優(yōu)點，具體歸納如下：* 存儲過程和函數(shù)以命名的數(shù)據(jù)庫對象形式存儲于數(shù)據(jù)庫當(dāng)中。存儲在數(shù)據(jù)庫中的優(yōu)點是很明顯的，因為代碼不保存在本地，用戶可以在任何客戶機(jī)上登錄到數(shù)據(jù)庫，并調(diào)用或修改代碼。* 存儲過程和函數(shù)可由數(shù)據(jù)庫提供安全保

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

3、回值，存儲過程的返回值必須通過參數(shù)帶回；函數(shù)有一定的數(shù)據(jù)類型，像其他的標(biāo)準(zhǔn)函數(shù)一樣，我們可以通過對函數(shù)名的調(diào)用返回函數(shù)值。 存儲過程和函數(shù)需要進(jìn)行編譯，以排除語法錯誤，只有編譯通過才能調(diào)用。創(chuàng)建和刪除存儲過程創(chuàng)建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限。該權(quán)限可由系統(tǒng)管理員授予。創(chuàng)建一個存儲過程的基本語句如下：CREATE OR REPLACE PROCEDURE 存儲過程名(參數(shù)IN|OUT|IN OUT 數(shù)據(jù)類型.)AS|IS說明部分BEGIN可執(zhí)行部分EXCEPTION錯誤處理部分END 過程名;其中：可選關(guān)鍵字OR REPLA

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

5、DROP ANY PROCEDURE系統(tǒng)權(quán)限的人。刪除存儲過程的語法如下：DROP PROCEDURE 存儲過程名；如果要重新編譯一個存儲過程，則只能是過程的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人。語法如下：ALTER PROCEDURE 存儲過程名 COMPILE；執(zhí)行(或調(diào)用)存儲過程的人是過程的創(chuàng)建者或是擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人或是被擁有者授予EXECUTE權(quán)限的人。執(zhí)行的方法如下：方法1：EXECUTE 模式名.存儲過程名(參數(shù).);方法2：BEGIN模式名.存儲過程名(參數(shù).);END;傳遞的參數(shù)必須與定義的參數(shù)類型、個數(shù)和

6、順序一致(如果參數(shù)定義了默認(rèn)值，則調(diào)用時可以省略參數(shù))。參數(shù)可以是變量、常量或表達(dá)式，用法參見下一節(jié)。如果是調(diào)用本賬戶下的存儲過程，則模式名可以省略。要調(diào)用其他賬戶編寫的存儲過程，則模式名必須要添加。以下是一個生成和調(diào)用簡單存儲過程的訓(xùn)練。注意要事先授予創(chuàng)建存儲過程的權(quán)限。【訓(xùn)練1】 創(chuàng)建一個顯示雇員總?cè)藬?shù)的存儲過程。步驟1：登錄SCOTT賬戶(或?qū)W生個人賬戶)。步驟2：在SQL*Plus輸入?yún)^(qū)中，輸入以下存儲過程：復(fù)制代碼代碼如下:CREATE OR REPLACE PROCEDURE EMP_COUNTASV_TOTAL NUMBER(10);BEGINSELECT COUNT(*) IN

7、TO V_TOTAL FROM EMP;DBMS_OUTPUT.PUT_LINE(雇員總?cè)藬?shù)為：|V_TOTAL);END;步驟3：按“執(zhí)行”按鈕進(jìn)行編譯。如果存在錯誤，就會顯示:警告: 創(chuàng)建的過程帶有編譯錯誤。如果存在錯誤，對腳本進(jìn)行修改，直到?jīng)]有錯誤產(chǎn)生。如果編譯結(jié)果正確，將顯示：復(fù)制代碼代碼如下:過程已創(chuàng)建。步驟4：調(diào)用存儲過程，在輸入?yún)^(qū)中輸入以下語句并執(zhí)行：復(fù)制代碼代碼如下:EXECUTE EMP_COUNT;顯示結(jié)果為：復(fù)制代碼代碼如下:雇員總?cè)藬?shù)為：14PL/SQL 過程已成功完成。說明：在該訓(xùn)練中，V_TOTAL變量是存儲過程定義的局部變量，用于接收查詢到的雇員總?cè)藬?shù)。注意：在S

8、QL*Plus中輸入存儲過程，按“執(zhí)行”按鈕是進(jìn)行編譯，不是執(zhí)行存儲過程。 如果在存儲過程中引用了其他用戶的對象，比如表，則必須有其他用戶授予的對象訪問權(quán)限。一個存儲過程一旦編譯成功，就可以由其他用戶或程序來引用。但存儲過程或函數(shù)的所有者必須授予其他用戶執(zhí)行該過程的權(quán)限。存儲過程沒有參數(shù)，在調(diào)用時，直接寫過程名即可。【訓(xùn)練2】 在PL/SQL程序中調(diào)用存儲過程。步驟1：登錄SCOTT賬戶。步驟2：授權(quán)STUDENT賬戶使用該存儲過程，即在SQL*Plus輸入?yún)^(qū)中，輸入以下的命令：復(fù)制代碼代碼如下:GRANT EXECUTE ON EMP_COUNT TO STUDENT授權(quán)成功。步驟3：登錄S

9、TUDENT賬戶，在SQL*Plus輸入?yún)^(qū)中輸入以下程序：復(fù)制代碼代碼如下:SET SERVEROUTPUT ONBEGINSCOTT.EMP_COUNT;END;步驟4：執(zhí)行以上程序，結(jié)果為：復(fù)制代碼代碼如下:雇員總?cè)藬?shù)為：14PL/SQL 過程已成功完成。說明：在本例中，存儲過程是由SCOTT賬戶創(chuàng)建的，STUDEN賬戶獲得SCOTT賬戶的授權(quán)后，才能調(diào)用該存儲過程。 注意：在程序中調(diào)用存儲過程，使用了第二種語法。【訓(xùn)練3】 編寫顯示雇員信息的存儲過程EMP_LIST，并引用EMP_COUNT存儲過程。步驟1：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲過程：復(fù)制代碼代碼如下:CREATE

10、 OR REPLACE PROCEDURE EMP_LISTAS CURSOR emp_cursor IS SELECT empno,ename FROM emp;BEGINFOR Emp_record IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);END LOOP;EMP_COUNT;END;執(zhí)行結(jié)果：過程已創(chuàng)建。步驟2：調(diào)用存儲過程，在輸入?yún)^(qū)中輸入以下語句并執(zhí)行：復(fù)制代碼代碼如下:EXECUTE EMP_LIST顯示結(jié)果為：復(fù)制代碼代碼如下:7369SMITH7499ALLEN7521WA

11、RD7566JONES 執(zhí)行結(jié)果：雇員總?cè)藬?shù)為：14PL/SQL 過程已成功完成。說明：以上的EMP_LIST存儲過程中定義并使用了游標(biāo)，用來循環(huán)顯示所有雇員的信息。然后調(diào)用已經(jīng)成功編譯的存儲過程EMP_COUNT，用來附加顯示雇員總?cè)藬?shù)。通過EXECUTE命令來執(zhí)行EMP_LIST存儲過程。【練習(xí)1】編寫顯示部門信息的存儲過程DEPT_LIST，要求統(tǒng)計出部門個數(shù)。參數(shù)傳遞參數(shù)的作用是向存儲過程傳遞數(shù)據(jù)，或從存儲過程獲得返回結(jié)果。正確的使用參數(shù)可以大大增加存儲過程的靈活性和通用性。參數(shù)的類型有三種，如下所示。復(fù)制代碼代碼如下:IN定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程OUT定義一個輸出

12、參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)IN OUT定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能參數(shù)的定義形式和作用如下：參數(shù)名 IN 數(shù)據(jù)類型 DEFAULT 值；定義一個輸入?yún)?shù)變量，用于傳遞參數(shù)給存儲過程。在調(diào)用存儲過程時，主程序的實際參數(shù)可以是常量、有值變量或表達(dá)式等。DEFAULT 關(guān)鍵字為可選項，用來設(shè)定參數(shù)的默認(rèn)值。如果在調(diào)用存儲過程時不指明參數(shù)，則參數(shù)變量取默認(rèn)值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進(jìn)行賦值。參數(shù)名 OUT 數(shù)據(jù)類型；定義一個輸出參數(shù)變量，用于從存儲過程獲取數(shù)據(jù)，即變量從存儲過程中返回值給主程序。在調(diào)用存儲過程時，主程序的實際參數(shù)只能是一個變量，而不

13、能是常量或表達(dá)式。在存儲過程中，參數(shù)變量只能被賦值而不能將其用于賦值，在存儲過程中必須給輸出變量至少賦值一次。參數(shù)名 IN OUT 數(shù)據(jù)類型 DEFAULT 值；定義一個輸入、輸出參數(shù)變量，兼有以上兩者的功能。在調(diào)用存儲過程時，主程序的實際參數(shù)只能是一個變量，而不能是常量或表達(dá)式。DEFAULT 關(guān)鍵字為可選項，用來設(shè)定參數(shù)的默認(rèn)值。在存儲過程中，變量接收主程序傳遞的值，同時可以參加賦值運(yùn)算，也可以對其進(jìn)行賦值。在存儲過程中必須給變量至少賦值一次。如果省略IN、OUT或IN OUT，則默認(rèn)模式是IN。【訓(xùn)練1】 編寫給雇員增加工資的存儲過程CHANGE_SALARY，通過IN類型的參數(shù)傳遞要增

14、加工資的雇員編號和增加的工資額。步驟1：登錄SCOTT賬戶。 步驟2：在SQL*Plus輸入?yún)^(qū)中輸入以下存儲過程并執(zhí)行：復(fù)制代碼代碼如下:CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)AS V_ENAME VARCHAR2(10);V_SAL NUMBER(5);BEGINSELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO; UPDATE EMP SET SAL=SA

15、L+P_RAISE WHERE EMPNO=P_EMPNO; DBMS_OUTPUT.PUT_LINE(雇員|V_ENAME|的工資被改為|TO_CHAR(V_SAL+P_RAISE);COMMIT;EXCEPTION WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(發(fā)生錯誤，修改失敗！);ROLLBACK;END;執(zhí)行結(jié)果為：過程已創(chuàng)建。步驟3：調(diào)用存儲過程，在輸入?yún)^(qū)中輸入以下語句并執(zhí)行：復(fù)制代碼代碼如下:EXECUTE CHANGE_SALARY(7788,80)顯示結(jié)果為：雇員SCOTT的工資被改為3080說明：從執(zhí)行結(jié)果可以看到，雇員SCOTT的工資已由原來的

16、3000改為3080。參數(shù)的值由調(diào)用者傳遞，傳遞的參數(shù)的個數(shù)、類型和順序應(yīng)該和定義的一致。如果順序不一致，可以采用以下調(diào)用方法。如上例，執(zhí)行語句可以改為：EXECUTE CHANGE_SALARY(P_RAISE=80,P_EMPNO=7788); 可以看出傳遞參數(shù)的順序發(fā)生了變化，并且明確指出了參數(shù)名和要傳遞的值，=運(yùn)算符左側(cè)是參數(shù)名，右側(cè)是參數(shù)表達(dá)式，這種賦值方法的意義較清楚。【練習(xí)1】創(chuàng)建插入雇員的存儲過程INSERT_EMP，并將雇員編號等作為參數(shù)。在設(shè)計存儲過程的時候，也可以為參數(shù)設(shè)定默認(rèn)值，這樣調(diào)用者就可以不傳遞或少傳遞參數(shù)了。【訓(xùn)練2】 調(diào)用存儲過程CHANGE_SALARY，不

17、傳遞參數(shù)，使用默認(rèn)參數(shù)值。在SQL*Plus輸入?yún)^(qū)中輸入以下命令并執(zhí)行：復(fù)制代碼代碼如下:EXECUTE CHANGE_SALARY顯示結(jié)果為：雇員SCOTT的工資被改為3090說明：在存儲過程的調(diào)用中沒有傳遞參數(shù)，而是采用了默認(rèn)值7788和10，即默認(rèn)雇員號為7788，增加的工資為10。【訓(xùn)練3】 使用OUT類型的參數(shù)返回存儲過程的結(jié)果。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲過程：復(fù)制代碼代碼如下:CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)ASBEGINSELECT COUNT(

18、*) INTO P_TOTAL FROM EMP;END;執(zhí)行結(jié)果為：1.過程已創(chuàng)建。步驟3：輸入以下程序并執(zhí)行：復(fù)制代碼代碼如下:DECLAREV_EMPCOUNT NUMBER;BEGINEMP_COUNT(V_EMPCOUNT);DBMS_OUTPUT.PUT_LINE(雇員總?cè)藬?shù)為：|V_EMPCOUNT);END;顯示結(jié)果為：復(fù)制代碼代碼如下:雇員總?cè)藬?shù)為：14PL/SQL 過程已成功完成。說明：在存儲過程中定義了OUT類型的參數(shù)P_TOTAL，在主程序調(diào)用該存儲過程時，傳遞了參數(shù)V_EMPCOUNT。在存儲過程中的SELECT.INTO.語句中對P_TOTAL進(jìn)行賦值，賦值結(jié)果由V

19、_EMPCOUNT變量帶回給主程序并顯示。以上程序要覆蓋同名的EMP_COUNT存儲過程，如果不使用OR REPLACE選項，就會出現(xiàn)以下錯誤：復(fù)制代碼代碼如下:ERROR 位于第 1 行:ORA-00955: 名稱已由現(xiàn)有對象使用。【練習(xí)2】創(chuàng)建存儲過程，使用OUT類型參數(shù)獲得雇員經(jīng)理名。【訓(xùn)練4】 使用IN OUT類型的參數(shù)，給電話號碼增加區(qū)碼。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入?yún)^(qū)中輸入并編譯以下存儲過程：復(fù)制代碼代碼如下:CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)ASBEG

20、IN P_HPONE_NUM:=0755-|P_HPONE_NUM;END;執(zhí)行結(jié)果為：過程已創(chuàng)建。步驟3：輸入以下程序并執(zhí)行：復(fù)制代碼代碼如下:SET SERVEROUTPUT ONDECLAREV_PHONE_NUM VARCHAR2(15);BEGINV_PHONE_NUM:=26731092;ADD_REGION(V_PHONE_NUM);DBMS_OUTPUT.PUT_LINE(新的電話號碼：|V_PHONE_NUM);END;顯示結(jié)果為：復(fù)制代碼代碼如下:新的電話號碼L/SQL 過程已成功完成。說明：變量V_HPONE_NUM既用來向存儲過程傳遞舊電話

21、號碼，也用來向主程序返回新號碼。新的號碼在原來基礎(chǔ)上增加了區(qū)號0755和-。創(chuàng)建和刪除存儲函數(shù) 創(chuàng)建函數(shù)，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系統(tǒng)權(quán)限。該權(quán)限可由系統(tǒng)管理員授予。創(chuàng)建存儲函數(shù)的語法和創(chuàng)建存儲過程的類似，即CREATE OR REPLACE FUNCTION 函數(shù)名(參數(shù)IN 數(shù)據(jù)類型.)RETURN 數(shù)據(jù)類型AS|IS說明部分BEGIN可執(zhí)行部分RETURN (表達(dá)式)EXCEPTION 錯誤處理部分END 函數(shù)名;其中，參數(shù)是可選的，但只能是IN類型(IN關(guān)鍵字可以省略)。在定義部分的RETURN 數(shù)據(jù)類型，用來表示函數(shù)的數(shù)據(jù)類型

22、，也就是返回值的類型，此部分不可省略。在可執(zhí)行部分的RETURN(表達(dá)式)，用來生成函數(shù)的返回值，其表達(dá)式的類型應(yīng)該和定義部分說明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個RETURN語句，但只有一個RETURN語句會被執(zhí)行，一旦執(zhí)行了RETURN語句，則函數(shù)結(jié)束并返回調(diào)用環(huán)境。一個存儲函數(shù)在不需要時可以刪除，但刪除的人應(yīng)是函數(shù)的創(chuàng)建者或者是擁有DROP ANY PROCEDURE系統(tǒng)權(quán)限的人。其語法如下：DROP FUNCTION 函數(shù)名；重新編譯一個存儲函數(shù)時，編譯的人應(yīng)是函數(shù)的創(chuàng)建者或者擁有ALTER ANY PROCEDURE系統(tǒng)權(quán)限的人。重新編譯一個存儲函數(shù)的語法如下：A

23、LTER PROCEDURE 函數(shù)名 COMPILE；函數(shù)的調(diào)用者應(yīng)是函數(shù)的創(chuàng)建者或擁有EXECUTE ANY PROCEDURE系統(tǒng)權(quán)限的人，或是被函數(shù)的擁有者授予了函數(shù)執(zhí)行權(quán)限的賬戶。函數(shù)的引用和存儲過程不同，函數(shù)要出現(xiàn)在程序體中，可以參加表達(dá)式的運(yùn)算或單獨出現(xiàn)在表達(dá)式中，其形式如下：變量名:=函數(shù)名(.)【訓(xùn)練1】 創(chuàng)建一個通過雇員編號返回雇員名稱的函數(shù)GET_EMP_NAME。步驟1：登錄SCOTT賬戶。步驟2：在SQL*Plus輸入?yún)^(qū)中輸入以下存儲函數(shù)并編譯：復(fù)制代碼代碼如下:CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER

24、 DEFAULT 7788)RETURN VARCHAR2AS V_ENAME VARCHAR2(10);BEGINELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;RETURN(V_ENAME);EXCEPTIONWHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(沒有該編號雇員！); RETURN (NULL);WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(有重復(fù)雇員編號！); RETURN (NULL);WHEN OTHERS THEN DBMS_O

25、UTPUT.PUT_LINE(發(fā)生其他錯誤！); RETURN (NULL);END;步驟3：調(diào)用該存儲函數(shù)，輸入并執(zhí)行以下程序：復(fù)制代碼代碼如下:BEGIN DBMS_OUTPUT.PUT_LINE(雇員7369的名稱是：| GET_EMP_NAME(7369); DBMS_OUTPUT.PUT_LINE(雇員7839的名稱是：| GET_EMP_NAME(7839);END;顯示結(jié)果為：復(fù)制代碼代碼如下:雇員7369的名稱是：SMITH雇員7839的名稱是：KINGPL/SQL 過程已成功完成。說明：函數(shù)的調(diào)用直接出現(xiàn)在程序的DBMS_OUTPUT.PUT_LINE語句中，作為字符串表達(dá)式

26、的一部分。如果輸入了錯誤的雇員編號，就會在函數(shù)的錯誤處理部分輸出錯誤信息。試修改雇員編號，重新運(yùn)行調(diào)用部分。【練習(xí)1】創(chuàng)建一個通過部門編號返回部門名稱的存儲函數(shù)GET_DEPT_NAME。 【練習(xí)2】將函數(shù)的執(zhí)行權(quán)限授予STUDENT賬戶，然后登錄STUDENT賬戶調(diào)用。存儲過程和函數(shù)的查看可以通過對數(shù)據(jù)字典的訪問來查詢存儲過程或函數(shù)的有關(guān)信息，如果要查詢當(dāng)前用戶的存儲過程或函數(shù)的源代碼，可以通過對USER_SOURCE數(shù)據(jù)字典視圖的查詢得到。USER_SOURCE的結(jié)構(gòu)如下：復(fù)制代碼代碼如下:DESCRIBE USER_SOURCE結(jié)果為：復(fù)制代碼代碼如下:名稱 是否為空? 類型- - -N

27、AME VARCHAR2(30)TYPE VARCHAR2(12)LINE NUMBERTEXT VARCHAR2(4000)說明：里面按行存放著過程或函數(shù)的腳本，NAME是過程或函數(shù)名，TYPE 代表類型(PROCEDURE或FUNCTION)，LINE是行號，TEXT 為腳本。【訓(xùn)練1】 查詢過程EMP_COUNT的腳本。在SQL*Plus中輸入并執(zhí)行如下查詢：復(fù)制代碼代碼如下:select TEXT from user_source WHERE NAME=EMP_COUNT;結(jié)果為：復(fù)制代碼代碼如下:TEXT-PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)

28、ASBEGINSELECT COUNT(*) INTO P_TOTAL FROM EMP;END;【訓(xùn)練2】 查詢過程GET_EMP_NAME的參數(shù)。在SQL*Plus中輸入并執(zhí)行如下查詢：復(fù)制代碼代碼如下:DESCRIBE GET_EMP_NAME結(jié)果為：復(fù)制代碼代碼如下:FUNCTION GET_EMP_NAME RETURNS VARCHAR2參數(shù)名稱 類型 輸入/輸出默認(rèn)值？- - - -P_EMPNO NUMBER(4) IN DEFAULT【訓(xùn)練3】 在發(fā)生編譯錯誤時，顯示錯誤。復(fù)制代碼代碼如下:SHOW ERRORS以下是一段編譯錯誤顯示：復(fù)制代碼代碼如下:LINE/COL ER

29、ROR- -4/2 PL/SQL: SQL Statement ignored4/36 PLS-00201: 必須說明標(biāo)識符 EMPP說明：查詢一個存儲過程或函數(shù)是否是有效狀態(tài)(即編譯成功)，可以使用數(shù)據(jù)字典USER_OBJECTS的STATUS列。【訓(xùn)練4】 查詢EMP_LIST存儲過程是否可用：復(fù)制代碼代碼如下:SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=EMP_LIST;結(jié)果為：復(fù)制代碼代碼如下:STATUS-VALID說明：VALID表示該存儲過程有效(即通過編譯)，INVALID表示存儲過程無效或需要重新編譯。當(dāng)Oracle調(diào)用

30、一個無效的存儲過程或函數(shù)時，首先試圖對其進(jìn)行編譯，如果編譯成功則將狀態(tài)置成VALID并執(zhí)行，否則給出錯誤信息。當(dāng)一個存儲過程編譯成功，狀態(tài)變?yōu)閂ALID，會不會在某些情況下變成INVALID。結(jié)論是完全可能的。比如一個存儲過程中包含對表的查詢，如果表被修改或刪除，存儲過程就會變成無效INVALID。所以要注意存儲過程和函數(shù)對其他對象的依賴關(guān)系。如果要檢查存儲過程或函數(shù)的依賴性，可以通過查詢數(shù)據(jù)字典USER_DENPENDENCIES來確定，該表結(jié)構(gòu)如下：復(fù)制代碼代碼如下:DESCRIBE USER_DEPENDENCIES;結(jié)果：復(fù)制代碼代碼如下:名稱 是否為空? 類型- - - NAME N

31、OT NULLVARCHAR2(30) TYPE VARCHAR2(12)REFERENCED_OWNER 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 為涉及到的實體類型。

32、【訓(xùn)練5】 查詢EMP_LIST存儲過程的依賴性。復(fù)制代碼代碼如下:SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME=EMP_LIST;執(zhí)行結(jié)果：復(fù)制代碼代碼如下:REFERENCED_NAME REFERENCED_TYPE- -STANDARD PACKAGESYS_STUB_FOR_PURITY_ANALYSIS PACKAGEDBMS_OUTPUT PACKAGEDBMS_OUTPUT SYNONYMDBMS_OUTPUT NON-EXISTENTEMP TABLEEMP_COUNT PRO

33、CEDURE說明：可以看出存儲過程EMP_LIST依賴一些系統(tǒng)包、EMP表和EMP_COUNT存儲過程。如果刪除了EMP表或EMP_COUNT存儲過程，EMP_LIST將變成無效。還有一種情況需要我們注意：如果一個用戶A被授予執(zhí)行屬于用戶B的一個存儲過程的權(quán)限，在用戶B的存儲過程中，訪問到用戶C的表，用戶B被授予訪問用戶C的表的權(quán)限，但用戶A沒有被授予訪問用戶C表的權(quán)限，那么用戶A調(diào)用用戶B的存儲過程是失敗的還是成功的呢？答案是成功的。如果讀者有興趣，不妨進(jìn)行一下實際測試。包包的概念和組成包是用來存儲相關(guān)程序結(jié)構(gòu)的對象，它存儲于數(shù)據(jù)字典中。包由兩個分離的部分組成：包頭(PACKAGE)和包體(

34、PACKAGE BODY)。包頭是包的說明部分，是對外的操作接口，對應(yīng)用是可見的；包體是包的代碼和實現(xiàn)部分，對應(yīng)用來說是不可見的黑盒。包中可以包含的程序結(jié)構(gòu)如下所示。復(fù)制代碼代碼如下:過程(PROCUDURE)帶參數(shù)的命名的程序模塊函數(shù)(FUNCTION)帶參數(shù)、具有返回值的命名的程序模塊變量(VARIABLE)存儲變化的量的存儲單元常量(CONSTANT)存儲不變的量的存儲單元游標(biāo)(CURSOR)用戶定義的數(shù)據(jù)操作緩存區(qū)，在可執(zhí)行部分使用類型(TYPE)用戶定義的新的結(jié)構(gòu)類型異常(EXCEPTION)在標(biāo)準(zhǔn)包中定義或由用戶自定義，用于處理程序錯誤說明部分可以出現(xiàn)在包的三個不同的部分：出現(xiàn)在包

35、頭中的稱為公有元素，出現(xiàn)在包體中的稱為私有元素，出現(xiàn)在包體的過程(或函數(shù))中的稱為局部變量。它們的性質(zhì)有所不同，如下所示。復(fù)制代碼代碼如下:公有元素(PUBLIC)在包頭中說明，在包體中具體定義在包外可見并可以訪問，對整個應(yīng)用的全過程有效私有元素(PRIVATE)在包體的說明部分說明只能被包內(nèi)部的其他部分訪問局部變量(LOCAL)在過程或函數(shù)的說明部分說明只能在定義變量的過程或函數(shù)中使用在包體中出現(xiàn)的過程或函數(shù)，如果需要對外公用，就必須在包頭中說明，包頭中的說明應(yīng)該和包體中的說明一致。包有以下優(yōu)點：* 包可以方便地將存儲過程和函數(shù)組織到一起，每個包又是相互獨立的。在不同的包中，過程、函數(shù)都可以

36、重名，這解決了在同一個用戶環(huán)境中命名的沖突問題。* 包增強(qiáng)了對存儲過程和函數(shù)的安全管理，對整個包的訪問權(quán)只需一次授予。 * 在同一個會話中，公用變量的值將被保留，直到會話結(jié)束。* 區(qū)分了公有過程和私有過程，包體的私有過程增加了過程和函數(shù)的保密性。* 包在被首次調(diào)用時，就作為一個整體被全部調(diào)入內(nèi)存，減少了多次訪問過程或函數(shù)的I/O次數(shù)。創(chuàng)建包和包體包由包頭和包體兩部分組成，包的創(chuàng)建應(yīng)該先創(chuàng)建包頭部分，然后創(chuàng)建包體部分。創(chuàng)建、刪除和編譯包的權(quán)限同創(chuàng)建、刪除和編譯存儲過程的權(quán)限相同。創(chuàng)建包頭的簡要語句如下：CREATE OR REPLACE PACKAGE 包名IS|AS公有變量定義公有類型定義公有

37、游標(biāo)定義公有異常定義函數(shù)說明過程說明END;創(chuàng)建包體的簡要語法如下：CREATE OR REPLACE PACKAGE BODY 包名IS|AS私有變量定義私有類型定義私有游標(biāo)定義私有異常定義函數(shù)定義過程定義END;包的其他操作命令包括：刪除包頭：DROP PACKAGE 包頭名刪除包體：DROP PACKAGE BODY 包體名重新編譯包頭：ALTER PACKAGE 包名 COMPILE PACKAGE重新編譯包體：ALTER PACKAGE 包名 COMPILE PACKAGE BODY在包頭中說明的對象可以在包外調(diào)用，調(diào)用的方法和調(diào)用單獨的過程或函數(shù)的方法基本相同，惟一的區(qū)別就是要在調(diào)

38、用的過程或函數(shù)名前加上包的名字(中間用“.”分隔)。但要注意，不同的會話將單獨對包的公用變量進(jìn)行初始化，所以不同的會話對包的調(diào)用屬于不同的應(yīng)用。系統(tǒng)包Oracle預(yù)定義了很多標(biāo)準(zhǔn)的系統(tǒng)包，這些包可以在應(yīng)用中直接使用，比如在訓(xùn)練中我們使用的DBMS_OUTPUT包，就是系統(tǒng)包。PUT_LINE是該包的一個函數(shù)。常用系統(tǒng)包下所示。復(fù)制代碼代碼如下:DBMS_OUTPUT在SQL*Plus環(huán)境下輸出信息DBMS_DDL編譯過程函數(shù)和包DBMS_SESSION改變用戶的會話，初始化包等DBMS_TRANSACTION控制數(shù)據(jù)庫事務(wù)DBMS_MAIL連接Oracle*MailDBMS_LOCK進(jìn)行復(fù)雜的

39、鎖機(jī)制管理DBMS_ALERT識別數(shù)據(jù)庫事件告警DBMS_PIPE通過管道在會話間傳遞信息DBMS_JOB管理Oracle的作業(yè)DBMS_LOB操縱大對象DBMS_SQL執(zhí)行動態(tài)SQL語句包的應(yīng)用在SQL*Plus環(huán)境下，包和包體可以分別編譯，也可以一起編譯。如果分別編譯，則要先編譯包頭，后編譯包體。如果在一起編譯，則包頭寫在前，包體在后，中間用“/”分隔。可以將已經(jīng)存在的存儲過程或函數(shù)添加到包中，方法是去掉過程或函數(shù)創(chuàng)建語句的CREATE OR REPLACE部分，將存儲過程或函數(shù)復(fù)制到包體中 ，然后重新編譯即可。 如果需要將私有過程或函數(shù)變成共有過程或函數(shù)的話，將過程或函數(shù)說明部分復(fù)制到包

40、頭說明部分，然后重新編譯就可以了。【訓(xùn)練1】 創(chuàng)建管理雇員信息的包EMPLOYE，它具有從EMP表獲得雇員信息，修改雇員名稱，修改雇員工資和寫回EMP表的功能。步驟1：登錄SCOTT賬戶，輸入以下代碼并編譯：復(fù)制代碼代碼如下:CREATE OR REPLACE PACKAGE EMPLOYE -包頭部分ISPROCEDURE SHOW_DETAIL;PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER);PROCEDURE SAVE_EMPLOYE;PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2);PROCEDURE CHANGE_SAL(

41、P_NEWSAL NUMBER);END EMPLOYE;/CREATE OR REPLACE PACKAGE BODY EMPLOYE -包體部分ISEMPLOYE EMP%ROWTYPE;- 顯示雇員信息 -PROCEDURE SHOW_DETAILASBEGINDBMS_OUTPUT.PUT_LINE(- 雇員信息 -); DBMS_OUTPUT.PUT_LINE(雇員編號：|EMPLOYE.EMPNO); DBMS_OUTPUT.PUT_LINE(雇員名稱：|EMPLOYE.ENAME); DBMS_OUTPUT.PUT_LINE(雇員職務(wù)：|EMPLOYE.JOB); DBMS_OU

42、TPUT.PUT_LINE(雇員工資：|EMPLOYE.SAL); DBMS_OUTPUT.PUT_LINE(部門編號：|EMPLOYE.DEPTNO);END SHOW_DETAIL;- 從EMP表取得一個雇員 - PROCEDURE GET_EMPLOYE(P_EMPNO NUMBER)ASBEGIN SELECT * INTO EMPLOYE FROM EMP WHERE EMPNO=P_EMPNO; DBMS_OUTPUT.PUT_LINE(獲取雇員|EMPLOYE.ENAME|信息成功); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE

43、(獲取雇員信息發(fā)生錯誤！);END GET_EMPLOYE;- 保存雇員到EMP表 -PROCEDURE SAVE_EMPLOYEASBEGIN UPDATE EMP SET ENAME=EMPLOYE.ENAME, SAL=EMPLOYE.SAL WHERE EMPNO=EMPLOYE.EMPNO; DBMS_OUTPUT.PUT_LINE(雇員信息保存完成！);END SAVE_EMPLOYE;- 修改雇員名稱 -PROCEDURE CHANGE_NAME(P_NEWNAME VARCHAR2) ASBEGIN EMPLOYE.ENAME:=P_NEWNAME; DBMS_OUTPUT.P

44、UT_LINE(修改名稱完成！);END CHANGE_NAME;- 修改雇員工資 -PROCEDURE CHANGE_SAL(P_NEWSAL NUMBER)ASBEGIN EMPLOYE.SAL:=P_NEWSAL; DBMS_OUTPUT.PUT_LINE(修改工資完成！);END CHANGE_SAL;END EMPLOYE;步驟2：獲取雇員7788的信息:復(fù)制代碼代碼如下:SET SERVEROUTPUT ONEXECUTE EMPLOYE.GET_EMPLOYE(7788);結(jié)果為：復(fù)制代碼代碼如下:獲取雇員SCOTT信息成功PL/SQL 過程已成功完成。步驟3：顯示雇員信息：復(fù)制

45、代碼代碼如下:EXECUTE EMPLOYE.SHOW_DETAIL;結(jié)果為：復(fù)制代碼代碼如下:- 雇員信息 -雇員編號：7788雇員名稱：SCOTT雇員職務(wù)：ANALYST雇員工資：3000部門編號：20PL/SQL 過程已成功完成。步驟4：修改雇員工資：復(fù)制代碼代碼如下:EXECUTE EMPLOYE.CHANGE_SAL(3800);結(jié)果為：復(fù)制代碼代碼如下:修改工資完成！PL/SQL 過程已成功完成。步驟5：將修改的雇員信息存入EMP表復(fù)制代碼代碼如下:EXECUTE EMPLOYE.SAVE_EMPLOYE;結(jié)果為：復(fù)制代碼代碼如下:雇員信息保存完成！PL/SQL 過程已成功完成。說

46、明：該包完成將EMP表中的某個雇員的信息取入內(nèi)存記錄變量，在記錄變量中進(jìn)行修改編輯，在確認(rèn)顯示信息正確后寫回EMP表的功能。記錄變量EMPLOYE用來存儲取得的雇員信息，定義為私有變量，只能被包的內(nèi)部模塊訪問。 【練習(xí)1】為包增加修改雇員職務(wù)和部門編號的功能。階段訓(xùn)練下面的訓(xùn)練通過定義和創(chuàng)建完整的包EMP_PK并綜合運(yùn)用本章的知識，完成對雇員表的插入、刪除等功能，包中的主要元素解釋如下所示。復(fù)制代碼代碼如下:程序結(jié)構(gòu)類 型說 明V_EMP_COUNT公有變量跟蹤雇員的總?cè)藬?shù)變化，插入、刪除雇員的同時修改該變量的值INIT公有過程對包進(jìn)行初始化，初始化雇員人數(shù)和工資修改的上、下限LIST_EMP

47、公有過程顯示雇員列表INSERT_EMP公有過程通過編號插入新雇員DELETE_EMP公有過程通過編號刪除雇員CHANGE_EMP_SAL公有過程通過編號修改雇員工資V_MESSAGE私有變量存放準(zhǔn)備輸出的信息C_MAX_SAL私有變量對工資修改的上限C_MIN_SAL私有變量對工資修改的下限SHOW_MESSAGE私有過程顯示私有變量V_MESSAGE中的信息EXIST_EMP私有函數(shù)判斷某個編號的雇員是否存在，該函數(shù)被INSERT_EMP、DELETE_EMP和CHANGE_EMP_SAL等過程調(diào)用【訓(xùn)練1】 完整的雇員包EMP_PK的創(chuàng)建和應(yīng)用。步驟1：在SQL*Plus中登錄SCOTT賬戶，輸入以下包頭和包體部分，按“執(zhí)行”按鈕編譯：復(fù)制代碼代碼如下:CREATE OR REPLACE PACKAGE EMP_PK-包頭部分ISV_EMP_COUNT NUMBER(5);