萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 自訂函數完整教學:無參數、輸入/輸出參數、結果快取、集合回傳與異常處理

Oracle PL/SQL 函數支援多種參數模式與回傳型態,本文從基本語法到進階特性 (Result Cache、Record/Collection 回傳、異常處理) 完整示範。

1. 無參數函數

CREATE OR REPLACE FUNCTION cur_datetime RETURN VARCHAR2 IS
BEGIN
  RETURN TO_CHAR(sysdate, 'YYYY"年"MM"月"DD"日"HH24"時"MI"分"SS"秒"');
END;
/

2. 輸入參數 (IN 模式,預設)

CREATE OR REPLACE FUNCTION get_sal(p_name VARCHAR2) RETURN NUMBER AS
  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, '員工不存在');
END;
/

3. 輸出參數 (OUT 模式)

CREATE OR REPLACE FUNCTION get_info(p_eno NUMBER, p_title OUT VARCHAR2) RETURN VARCHAR2 AS
  v_name emp.ename%TYPE;
BEGIN
  SELECT ename, job INTO v_name, p_title FROM emp WHERE empno = p_eno;
  RETURN v_name;
END;
/

4. 輸入輸出參數 (IN OUT 模式)

CREATE OR REPLACE FUNCTION get_upd_info(p_eno NUMBER, p_sal_chg IN OUT NUMBER) RETURN VARCHAR2 AS
  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;
/

5. 結果快取函數 (11g+)

CREATE OR REPLACE FUNCTION get_name(p_no NUMBER) RETURN VARCHAR2
RESULT_CACHE RELIES_ON(emp) AS
  v_name emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = p_no;
  RETURN v_name;
END;
/
  • 相同參數再次呼叫直接從快取取回,跨 Session 共享。
  • RELIES_ON(emp):相依表異動時自動失效 (12c 起自動偵測,可省略)。

6. 回傳 Record 型態 (%ROWTYPE)

CREATE OR REPLACE FUNCTION get_info_rec(p_eno NUMBER) RETURN emp%ROWTYPE AS
  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_info_rec(7788); DBMS_OUTPUT.PUT_LINE(v_rec.ename); END;

7. 回傳 Collection 型態

CREATE OR REPLACE TYPE ename_tab_t IS TABLE OF VARCHAR2(10);
/
CREATE OR REPLACE FUNCTION get_names(p_dno NUMBER) RETURN ename_tab_t AS
  v_tab ename_tab_t;
BEGIN
  SELECT ename BULK COLLECT INTO v_tab FROM emp WHERE deptno = p_dno;
  RETURN v_tab;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RAISE_APPLICATION_ERROR(-20099, '部門不存在');
END;
/
-- 呼叫
DECLARE v_tab ename_tab_t;
BEGIN v_tab := get_names(10); FOR i IN 1..v_tab.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_tab(i)); END LOOP; END;

8. 參數傳遞三種方式

  • 位置標記法:get_sal('SCOTT')
  • 名稱標記法:get_sal(p_name => 'SCOTT')
  • 混合標記法:get_upd_info(7788, p_sal_chg => v_amt)

9. 管理與除錯

-- 查看錯誤
SHOW ERRORS;
-- 查無效物件
SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='FUNCTION';
-- 重新編譯
ALTER FUNCTION get_info COMPILE;
-- 查看原始碼
SELECT text FROM user_source WHERE name='GET_INFO';
copyright © 萬盛學電腦網 all rights reserved