【正文】 INTO...語句中對(duì) P_TOTAL 進(jìn)行賦值，賦值結(jié)果由 V_EMPCOUNT 變量帶回給主程序并顯示。以上程序要覆蓋同名的 EMP_COUNT 存儲(chǔ)過程，如果不使用 OR REPLACE 選項(xiàng)，就會(huì)出現(xiàn)以18 / 21下錯(cuò)誤：ERROR 位于第 1 行:ORA00955: 名稱已由現(xiàn)有對(duì)象使用。練習(xí)：創(chuàng)建存儲(chǔ)過程，使用 OUT 類型參數(shù)獲得雇員經(jīng)理名。創(chuàng)建和刪除存儲(chǔ)函數(shù) 創(chuàng)建函數(shù)，需要有 CREATE PROCEDURE 或 CREATE ANY PROCEDURE 的系統(tǒng)權(quán)限。該權(quán)限可由系統(tǒng)管理員授予。創(chuàng)建存儲(chǔ)函數(shù)的語法和創(chuàng)建存儲(chǔ)過程的類似，即CREATE [OR REPLACE] FUNCTION 函數(shù)名[(參數(shù)[IN] 數(shù)據(jù)類型...)] RETURN 數(shù)據(jù)類型{AS|IS}[說明部分]BEGIN可執(zhí)行部分RETURN (表達(dá)式)[EXCEPTION 錯(cuò)誤處理部分]END [函數(shù)名]。其中，參數(shù)是可選的，但只能是 IN 類型(IN 關(guān)鍵字可以省略)。在定義部分的 RETURN 數(shù)據(jù)類型，用來表示函數(shù)的數(shù)據(jù)類型，也就是返回值的類型，此部分不可省略。在可執(zhí)行部分的 RETURN(表達(dá)式)，用來生成函數(shù)的返回值，其表達(dá)式的類型應(yīng)該和定義部分說明的函數(shù)返回值的數(shù)據(jù)類型一致。在函數(shù)的執(zhí)行部分可以有多個(gè) RETURN 語句，但只有一個(gè) RETURN 語句會(huì)被執(zhí)行，一旦執(zhí)行了 RETURN 語句，則函數(shù)結(jié)束并返回調(diào)用環(huán)境。一個(gè)存儲(chǔ)函數(shù)在不需要時(shí)可以刪除，但刪除的人應(yīng)是函數(shù)的創(chuàng)建者或者是擁有DROP ANY PROCEDURE 系統(tǒng)權(quán)限的人。其語法如下：DROP FUNCTION 函數(shù)名；重新編譯一個(gè)存儲(chǔ)函數(shù)時(shí)，編譯的人應(yīng)是函數(shù)的創(chuàng)建者或者擁有 ALTER ANY PROCEDURE 系統(tǒng)權(quán)限的人。重新編譯一個(gè)存儲(chǔ)函數(shù)的語法如下：ALTER PROCEDURE 函數(shù)名 COMPILE；函數(shù)的調(diào)用者應(yīng)是函數(shù)的創(chuàng)建者或擁有 EXECUTE ANY PROCEDURE 系統(tǒng)權(quán)限的人，或是被函數(shù)的擁有者授予了函數(shù)執(zhí)行權(quán)限的賬戶。函數(shù)的引用和存儲(chǔ)過程不同，函數(shù)要出現(xiàn)在程序體中，可以參加表達(dá)式的運(yùn)算或單獨(dú)出現(xiàn)在表達(dá)式中，其形式如下：變量名:=函數(shù)名(...)范例：創(chuàng)建一個(gè)通過雇員編號(hào)返回雇員名稱的函數(shù) GET_EMP_NAME。步驟 1：登錄 SCOTT 賬戶。步驟 2：在 SQL*Plus 輸入?yún)^(qū)中輸入以下存儲(chǔ)函數(shù)并編譯：CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)RETURN VARCHAR2AS V_ENAME VARCHAR2(10)。BEGIN19 / 21 SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO。RETURN(V_ENAME)。EXCEPTION WHEN NO_DATA_FOUND THEN (39。沒有該編號(hào)雇員！39。)。 RETURN (NULL)。 WHEN TOO_MANY_ROWS THEN (39。有重復(fù)雇員編號(hào)！39。)。 RETURN (NULL)。 WHEN OTHERS THEN (39。發(fā)生其他錯(cuò)誤！39。)。 RETURN (NULL)。END。步驟 3：調(diào)用該存儲(chǔ)函數(shù)，輸入并執(zhí)行以下程序：BEGIN (39。雇員 7369 的名稱是： 39。|| GET_EMP_NAME(7369))。 (39。雇員 7839 的名稱是： 39。|| GET_EMP_NAME(7839))。END。顯示結(jié)果為：雇員 7369 的名稱是：SMITH雇員 7839 的名稱是：KINGPL/SQL 過程已成功完成。說明：函數(shù)的調(diào)用直接出現(xiàn)在程序的 語句中，作為字符串表達(dá)式的一部分。如果輸入了錯(cuò)誤的雇員編號(hào)，就會(huì)在函數(shù)的錯(cuò)誤處理部分輸出錯(cuò)誤信息。試修改雇員編號(hào)，重新運(yùn)行調(diào)用部分。練習(xí)：創(chuàng)建一個(gè)通過部門編號(hào)返回部門名稱的存儲(chǔ)函數(shù) GET_DEPT_NAME。練習(xí)：將函數(shù)的執(zhí)行權(quán)限授予 STUDENT 賬戶，然后登錄 STUDENT 賬戶調(diào)用。存儲(chǔ)過程和函數(shù)的查看可以通過對(duì)數(shù)據(jù)字典的訪問來查詢存儲(chǔ)過程或函數(shù)的有關(guān)信息，如果要查詢當(dāng)前用戶的存儲(chǔ)過程或函數(shù)的源代碼，可以通過對(duì) USER_SOURCE 數(shù)據(jù)字典視圖的查詢得到。USER_SOURCE 的結(jié)構(gòu)如下：DESCRIBE USER_SOURCE結(jié)果為：名稱 是否為空? 類型 NAME VARCHAR2(30) TYPE VARCHAR2(12) LINE NUMBER TEXT VARCHAR2(4000) 說明：里面按行存放著過程或函數(shù)的腳本，NAME 是過程或函數(shù)名，TYPE 代表類型(PROCEDURE 或 FUNCTION)，LINE 是行號(hào)，TEXT 為腳本。20 / 21范例：查詢過程 EMP_COUNT 的腳本。在 SQL*Plus 中輸入并執(zhí)行如下查詢：select TEXT from user_source WHERE NAME=39。EMP_COUNT39。結(jié)果為：TEXTPROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)ASBEGIN SELECT COUNT(*) INTO P_TOTAL FROM EMP。END。范例：查詢過程 GET_EMP_NAME 的參數(shù)。在 SQL*Plus 中輸入并執(zhí)行如下查詢：DESCRIBE GET_EMP_NAME結(jié)果為：FUNCTION GET_EMP_NAME RETURNS VARCHAR2參數(shù)名稱 類型 輸入/輸出默認(rèn)值？ P_EMPNO NUMBER(4) IN DEFAULT范例：在發(fā)生編譯錯(cuò)誤時(shí)，顯示錯(cuò)誤。SHOW ERRORS 說明：查詢一個(gè)存儲(chǔ)過程或函數(shù)是否是有效狀態(tài)(即編譯成功 )，可以使用數(shù)據(jù)字典USER_OBJECTS 的 STATUS 列。范例：查詢 EMP_LIST 存儲(chǔ)過程是否可用：SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME=39。EMP_LIST39。結(jié)果為：STATUSVALID說明：VALID 表示該存儲(chǔ)過程有效 (即通過編譯)， INVALID 表示存儲(chǔ)過程無效或需要重新編譯。當(dāng) Oracle 調(diào)用一個(gè)無效的存儲(chǔ)過程或函數(shù)時(shí)，首先試圖對(duì)其進(jìn)行編譯，如果編譯成功則將狀態(tài)置成 VALID 并執(zhí)行，否則給出錯(cuò)誤信息。當(dāng)一個(gè)存儲(chǔ)過程編譯成功，狀態(tài)變?yōu)?VALID，會(huì)不會(huì)在某些情況下變成 INVALID。結(jié)論是完全可能的。比如一個(gè)存儲(chǔ)過程中包含對(duì)表的查詢，如果表被修改或刪除，存儲(chǔ)過程就會(huì)變成無效 INVALID。所以要注意存儲(chǔ)過程和函數(shù)對(duì)其他對(duì)象的依賴關(guān)系。如果要檢查存儲(chǔ)過程或函數(shù)的依賴性，可以通過查詢數(shù)據(jù)字典USER_DENPENDENCIES 來確定，該表結(jié)構(gòu)如下：DESCRIBE USER_DEPENDENCIES。結(jié)果：名稱 是否為空? 類型 NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12)21 / 21REFERENCED_OWNER VARCHAR2(30) REFERENCED_NAME VARCHAR2(64) REFERENCED_TYPE VARCHAR2(12)REFERENCED_LINK_NAME VARCHAR2(128) SCHEMAID NUMBER DEPENDENCY_TYPE VARCHAR2(4) 說明：NAME 為實(shí)體名，TYPE 為實(shí)體類型，REFERENCED_OWNER 為涉及到的實(shí)體擁有者賬戶，REFERENCED_NAME 為涉及到的實(shí)體名，REFERENCED_TYPE 為涉及到的實(shí)體類型。范例： 查詢 EMP_LIST 存儲(chǔ)過程的依賴性。SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME=39。EMP_LIST39。 說明：可以看出存儲(chǔ)過程 EMP_LIST 依賴一些系統(tǒng)包、EMP 表和 EMP_COUNT 存儲(chǔ)過程。如果刪除了 EMP 表或 EMP_COUNT 存儲(chǔ)過程，EMP_LIST 將變成無效。還有一種情況需要我們注意：如果一個(gè)用戶 A 被授予執(zhí)行屬于用戶 B 的一個(gè)存儲(chǔ)過程的權(quán)限，在用戶 B 的存儲(chǔ)過程中，訪問到用戶 C 的表，用戶 B 被授予訪問用戶 C 的表的權(quán)限，但用戶 A 沒有被授予訪問用戶 C 表的權(quán)限，那么用戶 A 調(diào)用用戶 B 的存儲(chǔ)過程是失敗的還是成功的呢？答案是成功的。如果讀者有興趣，不妨進(jìn)行一下實(shí)際測(cè)試