函數基本語法
CREATE OR REPLACE FUNCTION function_name (
arg1 [IN] datatype1,
arg2 [OUT] datatype2,
arg3 [IN OUT] datatype3
) RETURN return_datatype
IS
-- 宣告區
BEGIN
-- 執行區
RETURN return_value;
EXCEPTION
WHEN OTHERS THEN
-- 例外處理
RAISE;
END function_name;
四種參數模式實戰
1. 無參數函數
CREATE OR REPLACE FUNCTION get_current_user RETURN VARCHAR2 IS
v_user VARCHAR2(50);
BEGIN
SELECT user INTO v_user FROM dual;
RETURN v_user;
END;
/
-- 呼叫
SELECT get_current_user FROM dual;
-- 或 SQL*Plus
VAR v_name VARCHAR2(50);
EXEC :v_name := get_current_user;
2. IN 參數（唯讀輸入）
CREATE OR REPLACE FUNCTION get_sal(p_ename IN VARCHAR2) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE ename = p_ename;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/
VAR v_sal NUMBER;
EXEC :v_sal := get_sal('SCOTT');
3. OUT 參數（唯寫輸出）
CREATE OR REPLACE FUNCTION get_emp_info(
p_ename IN VARCHAR2,
p_job OUT VARCHAR2
) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT sal, job INTO v_sal, p_job FROM emp WHERE ename = p_ename;
RETURN v_sal;
END;
/
VAR v_job VARCHAR2(20);
VAR v_sal NUMBER;
EXEC :v_sal := get_emp_info('SCOTT', :v_job);
PRINT v_job;
4. IN OUT 參數（雙向傳遞）
CREATE OR REPLACE FUNCTION div_rem(
p_num1 IN NUMBER,
p_num2 IN OUT NUMBER
) RETURN NUMBER IS
v_quot NUMBER;
v_rem NUMBER;
BEGIN
v_quot := TRUNC(p_num1 / p_num2);
v_rem := MOD(p_num1, p_num2);
p_num2 := v_rem; -- 回傳餘數
RETURN v_quot; -- 回傳商數
EXCEPTION
WHEN ZERO_DIVIDE THEN
RAISE_APPLICATION_ERROR(-20000, '除數不能為零');
END;
/
VAR v_num2 NUMBER;
EXEC :v_num2 := 30;
VAR v_res NUMBER;
EXEC :v_res := div_rem(100, :v_num2);
PRINT v_num2; -- 餘數 10
PRINT v_res; -- 商數 3
函數調用限制（SQL 語句中呼叫）
| 限制 | 說明 |
|---|
| 僅限預存函數 | 不可呼叫用戶端/匿名區塊函數 |
| 只能有 IN 參數 | OUT/IN OUT 參數會報錯 |
| 不可使用 PL/SQL 專有型別 | BOOLEAN、RECORD、TABLE、VARRAY 等 |
| 不可包含 DML | 函數內不得有 INSERT/UPDATE/DELETE（除非定義為 AUTONOMOUS_TRANSACTION） |
| 不可修改被查詢的表 | 避免 Mutating Table 錯誤 |
常用管理指令
-- 查看原始碼
SELECT text FROM user_source WHERE name = 'GET_SAL' ORDER BY line;
-- 刪除函數
DROP FUNCTION get_sal;
-- 查看引用相依
SELECT * FROM user_dependencies WHERE referenced_name = 'GET_SAL';
實務範例：業務通用函數
1. 任務逾期狀態判斷
CREATE OR REPLACE FUNCTION get_urgent_state(
p_task_id VARCHAR2,
p_send_time DATE,
p_flag VARCHAR2 -- 'String' 或 'Img'
) RETURN VARCHAR2 IS
v_expire DATE;
v_now DATE := NVL(p_send_time, SYSDATE);
BEGIN
SELECT MAX(expiretime) INTO v_expire FROM t_wf_supervise WHERE taskid = p_task_id;
IF v_expire IS NULL THEN
RETURN CASE p_flag WHEN 'String' THEN '正常' ELSE 'cb_execute.gif' END;
END IF;
IF v_expire < v_now THEN
RETURN CASE p_flag WHEN 'String' THEN '超期' ELSE 'cb_limit.gif' END;
ELSIF v_expire - v_now < 3 THEN
RETURN CASE p_flag WHEN 'String' THEN '預警' ELSE 'cb_warning.gif' END;
ELSE
RETURN CASE p_flag WHEN 'String' THEN '正常' ELSE 'cb_execute.gif' END;
END IF;
END;
2. 動態 SQL 查詢前置節點名稱
CREATE OR REPLACE FUNCTION get_pre_node(p_pre_task_id VARCHAR2) RETURN VARCHAR2 IS
v_name VARCHAR2(50);
v_sql VARCHAR2(200);
BEGIN
IF p_pre_task_id IS NULL THEN RETURN ''; END IF;
v_sql := 'SELECT MAX(nodename) FROM t_wf_tasklist WHERE taskid = :1';
EXECUTE IMMEDIATE v_sql INTO v_name USING p_pre_task_id;
RETURN v_name;
END;
3. 標題截斷補綴
CREATE OR REPLACE FUNCTION format_title(
p_title VARCHAR2,
p_len NUMBER,
p_fill VARCHAR2 DEFAULT '...'
) RETURN VARCHAR2 IS
BEGIN
IF LENGTHB(p_title) > p_len * 2 THEN
RETURN SUBSTR(p_title, 1, p_len) || p_fill;
END IF;
RETURN p_title;
END;
Package 封裝最佳實踐
-- 规格
CREATE OR REPLACE PACKAGE pkg_hr_utils IS
FUNCTION get_emp_name(p_emp_id NUMBER) RETURN VARCHAR2;
PROCEDURE calc_bonus(p_dept_id NUMBER, p_year NUMBER);
END pkg_hr_utils;
/
-- 实现体
CREATE OR REPLACE PACKAGE BODY pkg_hr_utils IS
FUNCTION get_emp_name(p_emp_id NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(50);
BEGIN
SELECT ename INTO v_name FROM emp WHERE empno = p_emp_id;
RETURN v_name;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
PROCEDURE calc_bonus(p_dept_id NUMBER, p_year NUMBER) IS
BEGIN
-- 业务逻辑
NULL;
END;
END pkg_hr_utils;
/
-- 调用
BEGIN
pkg_hr_utils.calc_bonus(10, 2024);
END;
