存儲(chǔ)過(guò)程與自定義函數(shù)

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

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

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

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

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

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

7、庫(kù)中并可由任意匿名程序塊調(diào)用 通?？梢越邮軈?shù) 在參數(shù)聲明中的數(shù)據(jù)類(lèi)型區(qū)分符應(yīng)為無(wú)限制的,函數(shù)（續(xù)）,具有兩個(gè)部分 說(shuō)明以關(guān)鍵字 FUNCTION 開(kāi)始，以 RETURN 子句結(jié)束 主體以關(guān)鍵字 IS 開(kāi)始，以關(guān)鍵字 END 結(jié)束，后面可以跟可選函數(shù)名稱(chēng),函數(shù)（續(xù)）,CREATE [OR REPLACE] FUNCTION [(參數(shù)1, … 參數(shù)N) ] RETURN datatype IS|AS[局部聲明]BEGIN

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

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

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

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

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