【Oracle】ORA-30553: 函数不确定的解决办法

 如果需要创建基于自定义函数的索引,那么我们需要指定deterministic参数，在函数建立的时候指定该参数问题可解决。

SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE )

  2  RETURN DATE

  3  IS

  4  otime DATE ;

  5  BEGIN

  6    otime:=NVL(itime,SYSDATE);

  7    RETURN otime;

  8  END ;

  9  /

Function created.

--想采用自定义函数屏蔽掉sysdate在创建index时候的影响

SQL>  create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;

create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging

                                           *

ERROR at line 1:

ORA-30553: The function is not deterministic

SQL> !oerr ora 30553

30553, 00000, "The function is not deterministic"

// *Cause:  The function on which the index is defined is not deterministic

// * Action : If the function is deterministic, mark it DETERMINISTIC.  If it

//          is not deterministic (it depends on package state, database state,

//          current time , or anything other than the function inputs) then

//          do not create the index .  The values returned by a deterministic

//          function should not change even when the function is rewritten or

//          recompiled.

--因为函数缺少deterministic不能使用于index上

SQL> CREATE OR REPLACE FUNCTION f_xifenfei (itime DATE )

  2  RETURN DATE deterministic

  3  IS

  4  otime DATE ;

  5  BEGIN

  6    otime:=NVL(itime,SYSDATE);

  7    RETURN otime;

  8  END ;

  9  /

Function created.

SQL> create index in_t_xifenfei on t_xifenfei (f_xifenfei(intime)) online nologging;

Index created.

--创建函数index成功