Oracle PL/SQL 函數支援多種參數模式與回傳型態,本文從基本語法到進階特性 (Result Cache、Record/Collection 回傳、異常處理) 完整示範。
CREATE OR REPLACE FUNCTION cur_datetime RETURN VARCHAR2 IS BEGIN RETURN TO_CHAR(sysdate, 'YYYY"年"MM"月"DD"日"HH24"時"MI"分"SS"秒"'); END; /
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;
/
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; /
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; /
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; /
RELIES_ON(emp):相依表異動時自動失效 (12c 起自動偵測,可省略)。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;
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;
get_sal('SCOTT')get_sal(p_name => 'SCOTT')get_upd_info(7788, p_sal_chg => v_amt)-- 查看錯誤 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';