oracle中声明为deterministic的函数必须保证绝对纯净无副作用,因为优化器会利用该特性进行结果缓存和消除重复调用。如果函数存在副作用或依赖外部状态,这种缓存会导致数据不一致和逻辑错误。

为什么确定性函数不能修改数据库状态
确定性函数的核心承诺是:对于相同的输入参数,永远返回相同的结果。如果函数内部执行了INSERT、UPDATE或DELETE操作,不仅违反了“纯计算”的定义,还会导致优化器在多次调用时只执行一次并缓存结果,从而遗漏其他预期的数据变更。

CREATE OR REPLACE FUNCTION get_employee_name(p_id IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS v_name VARCHAR2(100); BEGIN -- 错误做法:在确定性函数中执行DML INSERT INTO audit_log (log_time, action) VALUES (SYSDATE, 'Accessed'); SELECT first_name || ' ' || last_name INTO v_name FROM employees WHERE employee_id = p_id; RETURN v_name; END;
上述代码展示了典型的违规场景。当查询语句多次引用get_employee_name时,Oracle可能只调用一次函数,导致审计日志中只有一条记录,而非每次访问都产生一条。此外,如果在事务中回滚,这些被优化的DML操作可能不会按预期回滚或提交,造成数据混乱。
为什么确定性函数不能读取易变的外部状态
除了不修改状态,确定性函数也不能读取随时间变化的外部状态,如SYSDATE、USERENV或全局变量。因为这些值在两次调用之间可能发生变化,导致相同输入产生不同输出,直接破坏确定性契约。

CREATE OR REPLACE FUNCTION get_current_year_salary(p_emp_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
v_sal NUMBER;
BEGIN
-- 错误做法:依赖非确定性系统函数
IF EXTRACT(YEAR FROM SYSDATE) > 2023 THEN
SELECT salary * 1.1 INTO v_sal FROM employees WHERE employee_id = p_emp_id;
ELSE
SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
END IF;
RETURN v_sal;
END;
在这个例子中,SYSDATE的值取决于当前时间。如果在2023年1月1日前后分别执行相同的SQL查询,get_current_year_salary可能会返回不同的结果。优化器基于DETERMINISTIC假设缓存了第一次调用的结果,后续调用将直接返回缓存值,即使实际年份已变,从而导致业务逻辑错误。
确定性函数在索引中的具体表现
当确定性函数用于创建函数索引时,其纯净性要求更为严格。索引存储的是函数计算结果的快照,如果函数有副作用或依赖外部状态,索引将无法正确反映数据变化,导致查询结果错误或索引维护失败。


















