【正文】 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 1 第 15章 PL/SQL程序設(shè)計(jì) Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 2 本章內(nèi)容 ? 存儲(chǔ)過(guò)程 ? 函數(shù) ? 局部子程序 ? 包 ? 觸發(fā)器 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 3 本章要求 ?掌握 PL/SQL功能模塊的應(yīng)用 ?存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器的創(chuàng)建 ?存儲(chǔ)過(guò)程、函數(shù)、包、觸發(fā)器的維護(hù) Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 4 存儲(chǔ) 子程序 ?存儲(chǔ)過(guò)程 ?函數(shù) ?局部子程序 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 ?存儲(chǔ)過(guò)程的創(chuàng)建 ?存儲(chǔ)過(guò)程的調(diào)用 ?存儲(chǔ)過(guò)程的管理 5 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 6 （ 1）存儲(chǔ)過(guò)程的創(chuàng)建 ? 基本語(yǔ)法 ? CREATE [OR REPLACE] PROCEDURE procedure_name ? (parameter1_name [mode] datatype ? [DEFAULT|:=value] ? [, parameter2_name [mode] datatype ? [DEFAULT|:=value],… ]) ? AS|IS ? /*Declarative section is here */ ? BEGIN ? /*Executable section is here*/ ? EXCEPTION ? /*Exception section is here*/ ? END[procedure_name]。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 7 ? 參數(shù)說(shuō)明 ? 參數(shù)的模式 ? IN（默認(rèn)參數(shù)模式）表示當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被傳遞給形參；在過(guò)程內(nèi)，形參起常量作用，只能讀該參數(shù)，而不能修改該參數(shù)；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時(shí)，實(shí)參沒(méi)有被改變。IN模式參數(shù)可以是常量或表達(dá)式。 ? OUT表示當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被忽略；在過(guò)程內(nèi)，形參起未初始化的 PL/SQL變量的作用，初始值為 NULL，可以進(jìn)行讀 /寫(xiě)操作；當(dāng)子程序調(diào)用結(jié)束后返回調(diào)用環(huán)境時(shí)，形參值被賦給實(shí)參。 OUT模式參數(shù)只能是變量，不能是常量或表達(dá)式。 ? IN OUT表示當(dāng)過(guò)程被調(diào)用時(shí)，實(shí)參值被傳遞給形參；在過(guò)程內(nèi)，形參起已初始化的 PL/SQL變量的作用，可讀可寫(xiě)；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時(shí)，形參值被賦給實(shí)參。 IN OUT模式參數(shù)只能是變量，不能是常量或表達(dá)式。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 8 ? 參數(shù)的限制 ? 在聲明形參時(shí)，不能定義形參的長(zhǎng)度或精度、刻度，它們是作為參數(shù)傳遞機(jī)制的一部分被傳遞的，是由實(shí)參決定的。 ? 參數(shù)傳遞方式 ? 當(dāng)子程序被調(diào)用時(shí)，實(shí)參與形參之間值的傳遞方式取決于參數(shù)的模式。 IN參數(shù)為引用傳遞，即實(shí)參的指針被傳遞給形參；OUT， IN OUT參數(shù)為值傳遞，即實(shí)參的值被復(fù)制給形參。 ? 參數(shù)默認(rèn)值 ? 可以為參數(shù)設(shè)置默認(rèn)值，這樣存儲(chǔ)過(guò)程被調(diào)用時(shí)如果沒(méi)有給該參數(shù)傳遞值，則采用默認(rèn)值。需要注意，有默認(rèn)值的參數(shù)應(yīng)該放在參數(shù)列表的最后。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 9 ? 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以部門號(hào)為參數(shù)，查詢?cè)摬块T的平均工資，并輸出該部門中比平均工資高的員工號(hào)、員工名。 ? CREATE OR REPLACE PROCEDURE show_emp( ? p_deptno %TYPE) ? AS ? v_sal %TYPE。 ? BEGIN ? SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno。 ? (p_deptno||39。 39。||39。average salary is:39。|| ? v_sal)。 ? FOR v_emp IN (SELECT * FROM emp WHERE ? deptno=p_deptno AND salv_sal) LOOP ? (||39。 39。|| ? )。 ? END LOOP。 ? EXCEPTION ? WHEN NO_DATA_FOUND THEN ? (39。The department doesn’’t exists!39。)。 ? END show_emp。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 10 ? 通常，存儲(chǔ)過(guò)程不需要返回值，如果需要返回一個(gè)值可以通過(guò)函數(shù)調(diào)用實(shí)現(xiàn)。但是，如果希望返回多個(gè)值，可以使用 OUT或 IN OUT模式參數(shù)來(lái)實(shí)現(xiàn)。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 11 ? 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，以部門號(hào)為參數(shù)，返回該部門的人數(shù)和最高工資。 ? CREATE OR REPLACE PROCEDURE return_deptinfo( ? p_deptno %TYPE, ? p_avgsal OUT %TYPE, ? p_count OUT %TYPE) ? AS ? BEGIN ? SELECT avg(sal),count(*) INTO p_avgsal,p_count ? FROM emp ? WHERE deptno=p_deptno。 ? EXCEPTION ? WHEN NO_DATA_FOUND THEN ? (39。The department don’’t exists!39。)。 ? END return_deptinfo。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 12 （ 2）存儲(chǔ)過(guò)程的調(diào)用 ? 在 SQL*PLUS中調(diào)用 ? EXEC procedure_name(parameter_list) ? EXECUTE show_emp(10) ? 在 PL/SQL塊中調(diào)用 ? BEGIN procedure_name(parameter_list)。 ? END； ? 注意 ? 在 PL/SQL程序中，存儲(chǔ)過(guò)程可以作為一個(gè)獨(dú)立的表達(dá)式被調(diào)用。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 13 ? DECLARE ? v_avgsal %TYPE。 ? v_count NUMBER。 ? BEGIN ? show_emp(20)。 ? return_deptinfo(10,v_avgsal,v_count)。 ? (v_avgsal||39。 39。|| ? v_count)。 ? END。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 14 （ 3）存儲(chǔ)過(guò)程的管理 ? 修改存儲(chǔ)過(guò)程 ? CREATE OR REPLACE PROCEDURE procedure_name ? 查看存儲(chǔ)過(guò)程及其源代碼 ? 查詢數(shù)據(jù)字典視圖 USER_SOURCE ? SELECT name,text FROM user_source WHERE type=39。PROCEDURE39。 ? 重新編譯存儲(chǔ)過(guò)程 ? ALTER PROCEDURE…COMPILE ? ALTER PROCEDURE show_emp COMPILE。 ? 刪除存儲(chǔ)過(guò)程 ? DROP PROCEDURE ? DROP PROCEDURE show_emp。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 15 ?函數(shù)的創(chuàng)建 ?函數(shù)的調(diào)用 ?函數(shù)的管理 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 16 （ 1）函數(shù)的創(chuàng)建 ? 基本語(yǔ)法為 ? CREATE [OR REPLACE] FUNCTION function_name ? (parameter1_name [mode] datatype ? [DEFAULT|:=value] ? [, parameter2_name [mode] datatype ? [DEFAULT|:=value],… ]) ? RETURN return_datatype ? AS|IS ? /*Declarative section is here */ ? BEGIN ? /*Executable section is here*/ ? EXCEPTION ? /*Exception section is here*/ ? END [function_name]。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 17 ? 注意 ? 在函數(shù)定義的頭部，參數(shù)列表之后，必須包含一個(gè)RETURN語(yǔ)句來(lái)指明函數(shù)返回值的類型，但不能約束返回值的長(zhǎng)度、精度、刻度等。如果使用 %TYPE，則可以隱含地包括長(zhǎng)度、精度、刻度等約束信息； ? 在函數(shù)體的定義中，必須至少包含一個(gè) RETURN 語(yǔ)句，來(lái)指明函數(shù)返回值。也可以有多個(gè) RETURN語(yǔ)句，但最終只有一個(gè) RETURN語(yǔ)句被執(zhí)行。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 18 ? 創(chuàng)建一個(gè)以部門號(hào)為參數(shù)，返回該部門最高工資的函數(shù)。 ? CREATE OR REPLACE FUNCTION return_maxsal ? (p_deptno %TYPE) ? RETURN %TYPE ? AS ? v_maxsal %TYPE。 ? BEGIN ? SELECT max(sal) INTO v_maxsal FROM emp ? WHERE deptno=p_deptno。 ? RETURN v_maxsal。 ? EXCEPTION ? WHEN NO_DATA_FOUND THEN ? (39。The deptno is invalid!39。)。 ? END return_maxsal。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 19 ? 如果需要函數(shù)返回多個(gè)值，可以使用 OUT或 IN OUT模式參數(shù)。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 20 ? 創(chuàng)建一個(gè)函數(shù)，以部門號(hào)為參數(shù)，返回部門名、部門人數(shù)及部門平均工資 。 ? CREATE OR REPLACE FUNCTION ret_deptinfo( ? p_deptno %TYPE, ? p_num OUT NUMBER, ? p_avg OUT NUMBER) ? RETURN %TYPE ? AS ? v_dname %TYPE。 ? BEGIN ? SELECT dname INTO v_dname FROM dept ? WHERE deptno=p_deptno。 ? SELECT count(*),avg(sal) INTO p_num,p_avg ? FROM emp WHERE deptno=p_deptno。 ? RETURN v_dname。 ? END ret_maxsal。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 21 （ 2）函數(shù)的調(diào)用 ? 在 SQL語(yǔ)句中調(diào)用函數(shù) ? 在 PL/SQL中調(diào)用函數(shù) ? 注意 ? 函數(shù)只能作為表達(dá)式的一部分被調(diào)用。 ? 示例 ? 通過(guò) return_maxsal函數(shù)的調(diào)用，輸出各個(gè)部門的最高工資；通過(guò) ret_deptinfo函數(shù)調(diào)用，輸出各個(gè)部門名、部門人數(shù)及平均工資。 Oracle10g 數(shù)據(jù)庫(kù)基礎(chǔ)教程 22 ? DECLARE ? v_maxsal %TYPE。 ? v_avgsal %TYPE。 ? v_num NUMBER。 ? v_dname %TYPE。 ? BEGIN ? FOR v_dept IN (SELECT DISTINCT de