【正文】 大型數(shù)據(jù)庫技術(shù) 南陽理工學(xué)院軟件學(xué)院 .數(shù)據(jù)庫技術(shù)教研室 2021年 9月 Oracle10g管理、 應(yīng)用與開發(fā) 第 8章 過程、函數(shù)和程序包 軟件學(xué)院 劉楊濤 Email: Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 2 過程、函數(shù)和包序 ?存儲(chǔ)過程 ?函數(shù) ?局部子程序 ?程序包 ?依賴性 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 3 存儲(chǔ)子程序是 被命名的 PL/SQL塊，以編譯的形式存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，可以在應(yīng)用程序中進(jìn)行調(diào)用，是 PL/SQL程序模塊化的一種體現(xiàn)。 PL/SQL中的存儲(chǔ)子程序包括存儲(chǔ)過程和 (存儲(chǔ) )函數(shù)兩種。存儲(chǔ)子程序是以獨(dú)立對(duì)象的形式存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，因此是一種 全局結(jié)構(gòu) ，與之對(duì)應(yīng)的是局部子程序，即嵌套在PL/SQL塊中的局部過程和函數(shù)，其存儲(chǔ)位置取決于其所在的父塊的位置。 存儲(chǔ)子程序 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 4 1. 存儲(chǔ)過程 ?存儲(chǔ)過程的創(chuàng)建 ?存儲(chǔ)過程的調(diào)用 ?存儲(chǔ)過程的管理 ?局部變量和子過程 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 5 （ 1）存儲(chǔ)過程的創(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]。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 6 ?參數(shù)說明 ?參數(shù)的模式 (mode) ?IN(默認(rèn) 參數(shù)模式 )表示當(dāng)過程被調(diào)用時(shí)，實(shí)參被傳遞給形參；在過程內(nèi)，形參起 常量作用 ， 只能讀 該參數(shù)，而不能修改該參數(shù)；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時(shí)，實(shí)參沒有被改變。 IN模式參數(shù)可以是 常量 或 表達(dá)式 。 ?OUT表示當(dāng)過程被調(diào)用時(shí)，實(shí)參值被忽略；在過程內(nèi)，形參起未初始化的 PL/SQL變量的作用，初始值為 NULL，可以進(jìn)行 讀 /寫操作 ；當(dāng)子程序調(diào)用結(jié)束后返回調(diào)用環(huán)境時(shí)，形參值被賦給實(shí)參。 OUT模式參數(shù) 只能是變量 ，不能是常量或表達(dá)式。 ?IN OUT表示當(dāng)過程被調(diào)用時(shí)，實(shí)參值被傳遞給形參；在過程內(nèi)，形參起已初始化的 PL/SQL變量的作用， 可讀可寫 ；當(dāng)子程序調(diào)用結(jié)束返回調(diào)用環(huán)境時(shí)，形參值被賦給實(shí)參。 IN OUT模式參數(shù) 只能是變量 ，不能是常量或表達(dá)式。 （ 1）存儲(chǔ)過程的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 7 ?參數(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ǔ)過程被調(diào)用時(shí)如果沒有給該參數(shù)傳遞值，則采用默認(rèn)值。需要 注意 ， 有默認(rèn)值的 參數(shù)應(yīng)該放 在參數(shù)列表的最后 。 （ 1）存儲(chǔ)過程的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 8 例如，創(chuàng)建一個(gè)存儲(chǔ)過程，以部門號(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。 （ 1）存儲(chǔ)過程的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 9 通常，存儲(chǔ)過程不需要返回值，如果需要返回一個(gè)值可以通過函數(shù)調(diào)用實(shí)現(xiàn)。但是，如果希望 返回多個(gè)值 ，可以 使用 OUT或 IN OUT模式 參數(shù)來實(shí)現(xiàn)。 （ 1）存儲(chǔ)過程的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 10 例如，創(chuàng)建一個(gè)存儲(chǔ)過程，以部門號(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。 （ 1）存儲(chǔ)過程的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 11 （ 2）存儲(chǔ)過程的調(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ǔ)過程可以作為一個(gè)獨(dú)立的表達(dá)式被調(diào)用。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 12 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。 （ 2）存儲(chǔ)過程的調(diào)用 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 13 （ 3）存儲(chǔ)過程的管理 ?修改存儲(chǔ)過程 ?CREATE OR REPLACE PROCEDURE procedure_name ?查看存儲(chǔ)過程及其源代碼 ?查詢數(shù)據(jù)字典視圖 USER_SOURCE ?SELECT name,text FROM user_source ?WHERE type=39。PROCEDURE39。 ?重新編譯存儲(chǔ)過程 ?ALTER PROCEDURE…COMPILE ?ALTER PROCEDURE show_emp COMPILE。 ?刪除存儲(chǔ)過程 ?DROP PROCEDURE ?DROP PROCEDURE show_emp。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 14 2. 函數(shù) ?函數(shù)的創(chuàng)建 ?函數(shù)的調(diào)用 ?函數(shù)的管理 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 15 （ 1）函數(shù)的創(chuàng)建 ?基本語法為 CREATE [OR REPLACE] FUNCTION function_name (parameter1_name [mode] datatyp