PL/SQL 函數是業務邏輯封裝、SQL 擴展、代碼復用的核心單元。本文系統覆蓋無參、IN、OUT、IN OUT、RESULT_CACHE、返回記錄類型 (%ROWTYPE)、返回集合類型、異常處理、編譯狀態管理、源碼查看 9 大類函數創建與調用實例，附性能優化與安全建議。
CREATE OR REPLACE FUNCTION func_name
(param1 IN type1, param2 OUT type2, param3 IN OUT type3)
RETURN return_type
[DETERMINISTIC] [PARALLEL_ENABLE] [RESULT_CACHE [RELIES_ON (table)]]
[AUTHID DEFINER | CURRENT_USER]
IS
-- 變量聲明
BEGIN
-- 執行體
RETURN value;
EXCEPTION
WHEN exception_name THEN
-- 異常處理
RAISE_APPLICATION_ERROR(-20001, 'Custom message');
END;
/
CREATE OR REPLACE FUNCTION cur_datetime RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24"時"MI"分"SS"秒"');
END;
/
-- 調用
BEGIN DBMS_OUTPUT.PUT_LINE(cur_datetime); END;
/
SELECT cur_datetime FROM dual;CREATE OR REPLACE FUNCTION get_sal(p_name VARCHAR2) RETURN NUMBER IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE UPPER(ename) = UPPER(p_name);
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '員工不存在: ' || p_name);
END;
/
-- 調用
BEGIN DBMS_OUTPUT.PUT_LINE('工資:' || get_sal('SCOTT')); END;
/
VAR salary NUMBER
EXEC :salary := get_sal(name => 'SCOTT') -- 命名參數傳遞
SELECT get_sal('SCOTT') FROM dual;CREATE OR REPLACE FUNCTION get_info(p_eno NUMBER, p_title OUT VARCHAR2) RETURN VARCHAR2 IS
v_name emp.ename%TYPE;
BEGIN
SELECT ename, job INTO v_name, p_title FROM emp WHERE empno = p_eno;
RETURN v_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '員工編號不存在: ' || p_eno);
END;
/
-- 調用 (PL/SQL 塊)
DECLARE v_name VARCHAR2(20); v_job VARCHAR2(20);
BEGIN
v_name := get_info(7788, v_job);
DBMS_OUTPUT.PUT_LINE('姓名:' || v_name || ', 崗位:' || v_job);
END;
/CREATE OR REPLACE FUNCTION get_upd_info(p_eno NUMBER, p_sal_chg IN OUT NUMBER) RETURN VARCHAR2 IS
v_name emp.ename%TYPE;
BEGIN
UPDATE emp SET sal = sal + p_sal_chg WHERE empno = p_eno
RETURNING ename, sal INTO v_name, p_sal_chg;
RETURN v_name;
END;
/
-- 調用
VAR sal_chg NUMBER
EXEC :sal_chg := 200
VAR name VARCHAR2(20)
EXEC :name := get_upd_info(7788, :sal_chg)
PRINT name sal_chgCREATE OR REPLACE FUNCTION get_name(p_no VARCHAR2) RETURN NUMBER
RESULT_CACHE RELIES_ON(emp) IS
v_name emp.ename%TYPE;
BEGIN
SELECT ename INTO v_name FROM emp WHERE empno = p_no;
RETURN v_name;
END;
/
-- 同一會話/跨會話重複調用相同參數，直接返回緩存結果，不再執行 SQL
SELECT get_name('7788') FROM dual;RELIES_ON(emp)：指定依賴表，表數據變更自動失效緩存。CREATE OR REPLACE FUNCTION get_emp_rec(p_eno NUMBER) RETURN emp%ROWTYPE IS
v_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_rec FROM emp WHERE empno = p_eno;
RETURN v_rec;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, '員工不存在');
END;
/
-- 調用
DECLARE v_rec emp%ROWTYPE;
BEGIN
v_rec := get_emp_rec(7788);
DBMS_OUTPUT.PUT_LINE('姓名:' || v_rec.ename || ', 部門:' || v_rec.deptno);
END;
/CREATE OR REPLACE TYPE ename_tab_type IS TABLE OF VARCHAR2(20);
/
CREATE OR REPLACE FUNCTION get_dept_names(p_dno NUMBER) RETURN ename_tab_type IS
v_names ename_tab_type;
BEGIN
SELECT ename BULK COLLECT INTO v_names FROM emp WHERE deptno = p_dno;
RETURN v_names;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20099, '部門不存在');
END;
/
-- 調用
DECLARE v_names ename_tab_type;
BEGIN
v_names := get_dept_names(20);
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('姓名: ' || v_names(i));
END LOOP;
END;
/RAISE_APPLICATION_ERROR(-20xxx, 'msg')，錯誤碼範圍 -20000 ~ -20999。NO_DATA_FOUND, TOO_MANY_ROWS, VALUE_ERROR) 統一捕獲轉業務異常。WHEN OTHERS THEN NULL 吞噬異常，至少記錄日志 DBMS_OUTPUT / LOG_TABLE。-- 查看無效對象
SELECT object_name FROM user_objects WHERE status = 'INVALID' AND object_type = 'FUNCTION';
-- 重新編譯
ALTER FUNCTION get_info COMPILE;
ALTER FUNCTION get_info COMPILE BODY; -- 僅編譯 Body
-- 查看源碼
SELECT text FROM user_source WHERE name = 'GET_INFO' ORDER BY line;
-- 刪除函數
DROP FUNCTION get_name;| 方式 | 語法 | 優點 | 缺點 |
|---|---|---|---|
| 位置傳遞 | func(a, b, c) | 簡潔 | 參數順序耦合，可讀性差 |
| 命名傳遞 | func(p1=>a, p2=>b) | 順序無關，自文檔化 | 稍長 |
| 混合傳遞 | func(a, p2=>b) | 靈活 | 命名參數必須在最後 |
SELECT DETERMINISTIC_FUNC(x) FROM t）。USING) + DBMS_ASSERT。GRANT EXECUTE ON func TO role，避免 GRANT EXECUTE ANY PROCEDURE。將函數設計原則（單一職責、無副作用、參數模式精確、異常轉業務碼、緩存策略顯式）納入代碼審查清單，構建高性能、可維護、安全的 PL/SQL 函數庫。
