存儲過程與自定義函數(shù)

1、存儲過程與自定義函數(shù),www.csst.com.cn,1,本章目標,存儲程序的應用函數(shù)的應用RETURN 語句 過程和函數(shù)的參數(shù)模式 查看存儲子程序的錯誤 存儲子程序的優(yōu)點,存儲過程,執(zhí)行特定操作的子程序存儲于數(shù)據(jù)庫中并可由任意匿名塊調用能夠接受參數(shù)在參數(shù)聲明中的數(shù)據(jù)類型區(qū)分符應為無限制的,存儲過程（續(xù)）,具有兩個部分 說明以關鍵字 PROCEDURE 開始，以過程名或參數(shù)列表結束 主體以關鍵字 IS 開始，以關

2、鍵字 END 結束，后面可以跟可選過程名稱,存儲過程（續(xù)）,CREATE [OR REPLACE] PROCEDURE [(參數(shù)1, … 參數(shù)N) ] IS|AS[局部聲明]BEGIN可執(zhí)行語句；EXCEPTION [例外處理程序]；END [];參數(shù)代表 變量名 [IN|OUT|IN OUT] 數(shù)據(jù)類型 [{:= | DEFAULT} 值],存儲過程（續(xù)）,示例 CREATE PROCEDURE branch_

3、sum(p_brnch branch.branch_code%TYPE) ISdeclare variables;BEGIN可執(zhí)行語句； EXCEPTIONWHEN NO_DATA_FOUND THEN語句 ;END branch_sum;,存儲過程（續(xù)）,創(chuàng)建過程時， Oracle 自動執(zhí)行下列步驟 編譯過程存儲所編譯的代碼在數(shù)據(jù)庫中存儲過程PL/SQL 編譯程序用于編譯代碼 如果發(fā)生錯誤，也可創(chuàng)建過程，但是無

4、效,存儲過程（續(xù)）,可以使用 SHOW ERRORS 命令或下列命令查看編譯錯誤 SELECT * FROM USER_ERRORS;Oracle 將編譯過程裝入 SGA 中 其他用戶也可以執(zhí)行存儲在 SGA 中的相同過程,存儲過程（續(xù)）,Oracle 分三個步驟執(zhí)行過程驗證用戶訪問如果為非法用戶，則拒絕訪問驗證過程有效性如果為非法過程，則不執(zhí)行執(zhí)行過程可以通過下列方式檢查過程的有效性 SELECT OBJECT_N

5、AME, OBJECT_TYPE, STATUSFROM USER_OBJECTSWHERE OBJECT_TYPE = ‘PROCEDURE’;,存儲過程的優(yōu)點,增強了數(shù)據(jù)安全性 可以授權用戶訪問能執(zhí)行表的存儲過程，但不授權他們訪問表自身提高了數(shù)據(jù)庫性能 通過網絡發(fā)送的信息有所減少 編譯不需要執(zhí)行代碼 過程存在于共享池中，所以不需要進行磁盤檢索,存儲過程的優(yōu)點（續(xù)）,節(jié)省內存 只需要在內存中裝入過程的一個拷貝便可供多個

6、用戶執(zhí)行 提高了開發(fā)的工作效率 通過編寫單一的過程可以避免冗余程序代碼，并且提高了生產效率 完整性 只需要測試一次，以保證它能夠返回正確的結果,調用存儲過程,可以作為 PL/SQL 語句調用過程 示例branch_sum(‘NYK’);可以從 SQL*Plus 中調用單獨的過程 示例SQL> EXECUTE branch_sum(‘NYK’);,函數(shù),返回值的子程序 具有一個 RETURN 子句 存儲于數(shù)據(jù)

7、庫中并可由任意匿名程序塊調用 通常可以接受參數(shù) 在參數(shù)聲明中的數(shù)據(jù)類型區(qū)分符應為無限制的,函數(shù)（續(xù)）,具有兩個部分 說明以關鍵字 FUNCTION 開始，以 RETURN 子句結束 主體以關鍵字 IS 開始，以關鍵字 END 結束，后面可以跟可選函數(shù)名稱,函數(shù)（續(xù)）,CREATE [OR REPLACE] FUNCTION [(參數(shù)1, … 參數(shù)N) ] RETURN datatype IS|AS[局部聲明]BEGIN

8、可執(zhí)行語句 ;[EXCEPTION例外處理程序]END [];參數(shù)代表的是變量名 [IN|OUT|IN OUT] 數(shù)據(jù)類型 [{:= | DEFAULT} 值,函數(shù)（續(xù)）,示例 CREATE FUNCTION day_fn(mday NUMBER) RETURN CHAR ISdisp_day CHAR(15);BEGIN可執(zhí)行語句 ;RETURN disp_day;EXCEPTION 語句 ;END

9、day_fn;,調用函數(shù),作為 PL/SQL 語句調用函數(shù) 示例chardays := day_fn(3);作為表達式的一部分調用函數(shù) 示例IF day_fn(3) = ‘TUESDAY’ THEN語句 ;END IF;,過程和函數(shù)的參數(shù)模式,用于定義正式參數(shù)的行為 可以與其他子程序一起使用 三個參數(shù)模式 IN (默認)OUTIN OUT避免在函數(shù)中使用 OUT 和 IN OUT 模型,過程和函數(shù)的參數(shù)模式（

10、續(xù)）,IN允許將值傳送到被調用的子程序 在子程序內部，它的作用如同常數(shù) 實際對應參數(shù)可以是一個常數(shù)、數(shù)據(jù)量、初始化變量或表達式 可以被初始化為默認值,過程和函數(shù)的參數(shù)模式（續(xù)）,OUT允許值返回到子程序的調用者 在子程序的內部，它的作用如同未初始化的變量 實際的對應參數(shù)應為一個變量；不能為一個常量或一個表達式 其值不能對另一個變量賦值，也不能對其自身再次賦值,過程和函數(shù)的參數(shù)模式（續(xù)）,IN OUT允許傳送初始值，并將

11、更新的值返回給調用者 在子程序內部，它的作用如同未初始化的變量 實際的對應參數(shù)應為一個變量；不能為一個常量或一個表達式 它可以被賦值，它的值也可以對另一個變量賦值,調試存儲子程序,可以使用 DBMS_OUTPUT 提供的數(shù)據(jù)包調試存儲子程序 可以使用 PUT 和 PUT_LINE 語句將變量和表達式的值輸出到顯示器 還可以使用 SHOW ERRORS 命令查看編譯錯誤 SHOW ERRORS [PROCEDURE | F

12、UNCTION | PACKAGE] ;,存儲子程序的優(yōu)點,更高的工作效率 通過采用存儲子程序的庫設計應用程序，可以避免冗余編碼，從而提高工作效率 更佳的性能 通過使用子程序可以減少從到 Oracle的調用，因此提高了應用程序的性能,存儲子程序的優(yōu)點（續(xù)）,節(jié)省內存 只需要將子程序的一個拷貝裝入到內存中就可以供多個用戶執(zhí)行，這樣應用程序就可以需要更少的內存 應用程序完整性 通過采用存儲子程序的庫來開發(fā)所有應用程序，降低了編碼