【正文】 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 1 第 15章 PL/SQL程序設(shè)計 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 2 本章內(nèi)容 ? 存儲過程 ? 函數(shù) ? 局部子程序 ? 包 ? 觸發(fā)器 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 3 本章要求 ?掌握 PL/SQL功能模塊的應(yīng)用 ?存儲過程、函數(shù)、包、觸發(fā)器的創(chuàng)建 ?存儲過程、函數(shù)、包、觸發(fā)器的維護 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 4 存儲 子程序 ?存儲過程 ?函數(shù) ?局部子程序 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 ?存儲過程的創(chuàng)建 ?存儲過程的調(diào)用 ?存儲過程的管理 5 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 6 （ 1）存儲過程的創(chuàng)建 ? 基本語法 ? 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ù)庫基礎(chǔ)教程 7 ? 參數(shù)說明 ? 參數(shù)的模式 ? IN（默認參數(shù)模式）表示當過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起常量作用，只能讀該參數(shù)，而不能修改該參數(shù)；當子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時，實參沒有被改變。IN模式參數(shù)可以是常量或表達式。 ? OUT表示當過程被調(diào)用時，實參值被忽略；在過程內(nèi)，形參起未初始化的 PL/SQL變量的作用，初始值為 NULL，可以進行讀 /寫操作；當子程序調(diào)用結(jié)束后返回調(diào)用環(huán)境時，形參值被賦給實參。 OUT模式參數(shù)只能是變量，不能是常量或表達式。 ? IN OUT表示當過程被調(diào)用時，實參值被傳遞給形參；在過程內(nèi)，形參起已初始化的 PL/SQL變量的作用，可讀可寫；當子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時，形參值被賦給實參。 IN OUT模式參數(shù)只能是變量，不能是常量或表達式。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 8 ? 參數(shù)的限制 ? 在聲明形參時，不能定義形參的長度或精度、刻度，它們是作為參數(shù)傳遞機制的一部分被傳遞的，是由實參決定的。 ? 參數(shù)傳遞方式 ? 當子程序被調(diào)用時，實參與形參之間值的傳遞方式取決于參數(shù)的模式。 IN參數(shù)為引用傳遞，即實參的指針被傳遞給形參；OUT， IN OUT參數(shù)為值傳遞，即實參的值被復(fù)制給形參。 ? 參數(shù)默認值 ? 可以為參數(shù)設(shè)置默認值，這樣存儲過程被調(diào)用時如果沒有給該參數(shù)傳遞值，則采用默認值。需要注意，有默認值的參數(shù)應(yīng)該放在參數(shù)列表的最后。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 9 ? 創(chuàng)建一個存儲過程，以部門號為參數(shù)，查詢該部門的平均工資，并輸出該部門中比平均工資高的員工號、員工名。 ? 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ù)庫基礎(chǔ)教程 10 ? 通常，存儲過程不需要返回值，如果需要返回一個值可以通過函數(shù)調(diào)用實現(xiàn)。但是，如果希望返回多個值，可以使用 OUT或 IN OUT模式參數(shù)來實現(xiàn)。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 11 ? 創(chuàng)建一個存儲過程，以部門號為參數(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ù)庫基礎(chǔ)教程 12 （ 2）存儲過程的調(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程序中，存儲過程可以作為一個獨立的表達式被調(diào)用。 Oracle10g 數(shù)據(jù)庫基礎(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ù)庫基礎(chǔ)教程 14 （ 3）存儲過程的管理 ? 修改存儲過程 ? CREATE OR REPLACE PROCEDURE procedure_name ? 查看存儲過程及其源代碼 ? 查詢數(shù)據(jù)字典視圖 USER_SOURCE ? SELECT name,text FROM user_source WHERE type=39。PROCEDURE39。 ? 重新編譯存儲過程 ? ALTER PROCEDURE…COMPILE ? ALTER PROCEDURE show_emp COMPILE。 ? 刪除存儲過程 ? DROP PROCEDURE ? DROP PROCEDURE show_emp。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 15 ?函數(shù)的創(chuàng)建 ?函數(shù)的調(diào)用 ?函數(shù)的管理 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 16 （ 1）函數(shù)的創(chuàng)建 ? 基本語法為 ? 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ù)庫基礎(chǔ)教程 17 ? 注意 ? 在函數(shù)定義的頭部，參數(shù)列表之后，必須包含一個RETURN語句來指明函數(shù)返回值的類型，但不能約束返回值的長度、精度、刻度等。如果使用 %TYPE，則可以隱含地包括長度、精度、刻度等約束信息； ? 在函數(shù)體的定義中，必須至少包含一個 RETURN 語句，來指明函數(shù)返回值。也可以有多個 RETURN語句，但最終只有一個 RETURN語句被執(zhí)行。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 18 ? 創(chuàng)建一個以部門號為參數(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ù)庫基礎(chǔ)教程 19 ? 如果需要函數(shù)返回多個值，可以使用 OUT或 IN OUT模式參數(shù)。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 20 ? 創(chuàng)建一個函數(shù)，以部門號為參數(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ù)庫基礎(chǔ)教程 21 （ 2）函數(shù)的調(diào)用 ? 在 SQL語句中調(diào)用函數(shù) ? 在 PL/SQL中調(diào)用函數(shù) ? 注意 ? 函數(shù)只能作為表達式的一部分被調(diào)用。 ? 示例 ? 通過 return_maxsal函數(shù)的調(diào)用，輸出各個部門的最高工資；通過 ret_deptinfo函數(shù)調(diào)用，輸出各個部門名、部門人數(shù)及平均工資。 Oracle10g 數(shù)據(jù)庫基礎(chǔ)教程 22 ? DECLARE ? v_maxsal %TYPE。 ? v_avgsal %TYPE。 ? v_num NUMBER。 ? v_dname %TYPE。 ? BEGIN ? FOR v_dept IN (SELECT DISTINCT de