ORA-25007: functions or methods not allowed in WHEN clause

文档解释

ORA-25007: functions or methods not allowed in WHEN clause

Cause: PLSQL function call or method invocation is not allowed in the WHEN clause when creating a trigger.

Action: Remove the function call or method invocation from the WHEN clause.

ORA-25007 错误信息指出，在 WHEN 子句中不允许使用函数或方法。

官方解释

ORA-25007: functions or methods not allowed in WHEN clause

Cause: An attempt to use a function or method in the WHEN clause of a trigger action was made.

Action: Create a function or procedure and place the call to the function or procedure in the trigger action.

常见案例

例如，下列触发器包含引用函数 Uppercase():

CREATE OR REPLACE TRIGGER trg_001

BEFORE INSERT OR UPDATE ON emp FOR EACH ROW

BEGIN

WHEN (Uppercase(:NEW.ename) = ‘SMITH’) THEN

:NEW.SAL := 20000;

END WHEN;

END;

/

此事例中将会引发ORA-25007错误，因为在WHEN子句内有一个函数调用，而在WHEN子句内不允许有函数调用，因此报错。

一般处理方法及步骤

1.将函数调用的逻辑抽取出来，形成一个函数或存储过程。

2.将WHEN子句内的函数调用替换成函数或存储过程的调用。

例如替换后的触发器：

CREATE OR REPLACE FUNCTION Uppercase(ename VARCHAR2) RETURN VARCHAR2

IS

BEGIN

RETURN UPPER(ename);

END;

/

—

CREATE OR REPLACE FUNCTION Set_Salary(ename VARCHAR2) RETURN NUMBER

IS

BEGIN

IF Uppercase(ename) = ‘SMITH’ THEN

RETURN 20000;

ELSE

RETURN 0;

END IF;

END;

/

—

CREATE OR REPLACE TRIGGER trg_001

BEFORE INSERT OR UPDATE ON emp FOR EACH ROW

BEGIN

WHEN (Set_Salary(:NEW.ename) > 0) THEN

:NEW.SAL := Set_Salary(:NEW.ename);

END WHEN;

END;

/