【正文】 e [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]。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 16 ?注意 ?在 函數(shù)定義的頭部 ，參數(shù)列表之后， 必須包含 一個 RETURN語句 來指明函數(shù)返回值的類型，但 不能約束 返回值的 長度 、 精度 、 刻度 等。如果使用 %TYPE，則可以隱含地包括長度、精度、刻度等約束信息； ?在 函數(shù)體的定義 中， 必須至少 包含 一個 RETURN 語句 ，來指明函數(shù)返回值。也可以有多個 RETURN語句，但最終只有一個 RETURN語句被執(zhí)行。 （ 1）函數(shù)的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 17 例如，創(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。 （ 1）函數(shù)的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 18 如果需要函數(shù)返回多個值，可以使用 OUT或 IN OUT模式參數(shù)。 例如，創(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_deptinfo。 （ 1）函數(shù)的創(chuàng)建 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 19 （ 2）函數(shù)的調(diào)用 ?在 SQL語句中調(diào)用函數(shù) ?在 PL/SQL中調(diào)用函數(shù) ?注意 ?函數(shù) 只能 作為 表達(dá)式的一部分 被調(diào)用。 ?示例 ?通過 return_maxsal函數(shù)的調(diào)用，輸出各個部門的最高工資；通過 ret_deptinfo函數(shù)調(diào)用，輸出各個部門名、部門人數(shù)及平均工資。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 20 DECLARE v_maxsal %TYPE。 v_avgsal %TYPE。 v_num NUMBER。 v_dname %TYPE。 BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_maxsal:=return_maxsal()。 v_dname:=ret_deptinfo(,v_num,v_avgsal)。 (v_dname||39。 39。||v_maxsal||39。 39。|| v_avgsal||39。 39。||v_num)。 END LOOP。 END。 （ 2）函數(shù)的調(diào)用 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 21 ?函數(shù)可以在 SQL語句的以下部分調(diào)用： ?SELECT語句的目標(biāo)列； ?WHERE和 HAVING子句； ?CONNECT BY， START WITH， ORDER BY， GROUP BY子句； ?INSERT語句的 VALUES子句中； ?UPDATE語句的 SET子句中。 （ 2）函數(shù)的調(diào)用 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 22 ?如果要在 SQL中調(diào)用函數(shù)，那么函數(shù)必須符合下列限制和要求： ?在 SELECT語句中的函數(shù)不能修改 (INSERT， UPDATE，DELETE)調(diào)用函數(shù)的 SQL語句中使用的表； ?函數(shù)在一個遠(yuǎn)程或并行操作中使用時，不能讀 /寫封裝變量； ?函數(shù)必須是一個存儲數(shù)據(jù)庫對象 (或存儲在包中 )； ?函數(shù)的參數(shù)只能使用 IN模式； ?形式參數(shù)類型必須使用數(shù)據(jù)庫數(shù)據(jù)類型； ?返回的數(shù)據(jù)類型必須是數(shù)據(jù)庫數(shù)據(jù)類型； （ 2）函數(shù)的調(diào)用 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 23 （ 3）函數(shù)的管理 ?函數(shù)的修改 CREATE OR REPLACE FUNCTION function_name ?查看函數(shù)及其源代碼 ?查詢數(shù)據(jù)字典視圖 USER_SOURCE SELECT name,text FROM user_source WHERE type=39。FUNCTION39。 ?函數(shù)重編譯 ALTER FUNCTION…COMPILE ALTER FUNCTION ret_maxsal COMPILE。 ?刪除函數(shù) DROP FUNCTION DROP FUNCTION ret_maxsal。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 24 3. 局部子程序 ?局部子程序 ?嵌套在其他 PL/SQL塊中的子程序。 ?只能在其定義的塊內(nèi)部被調(diào)用，而不能在其父塊外被調(diào)用。 ?使用局部子程序時需要注意： ?局部子程序只在當(dāng)前語句塊內(nèi)有效； ?局部子程序必須在 PL/SQL塊聲明部分的最后進(jìn)行定義； ?局部子程序必須在使用之前聲明，如果是子程序間相互引用，則需要采用預(yù)先聲明； ?局部子程序可以重載。 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 25 例如，在一個塊內(nèi)部定義一個函數(shù)和一個過程。函數(shù)以部門號為參數(shù)返回該部門的平均工資；過程以部門號為參數(shù)，輸出該部門中工資低于部門平均工資的員工的員工號、員工名。 DECLARE v_deptno %TYPE。 v_avgsal %TYPE。 FUNCTION return_avgsal(p_deptno %TYPE) RETURN %TYPE AS v_sal %TYPE。 BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno。 RETURN v_sal。 END return_avgsal。 3. 局部子程序 Oracle 10g 數(shù)據(jù)庫管理、應(yīng)用與開發(fā) 26 PROCEDURE show_emp(p_deptno %TYPE) AS CURSOR c_emp IS SELECT * FROM emp WHERE deptno=p_deptno。 BEGIN FOR v_emp IN c_emp LOOP IF return_avgsal() THEN (||39。 39。|| )。 END IF。 END LOOP。 END show_emp。 BEGIN v_deptno:=amp。x。 v_avgsal:=return_avgsal(v_deptno)。 show_e