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

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

SQL>CREATEOR REPLACE FUNCTION f_xifenfei (itime DATE)

 2 RETURNDATE

 3 IS

 4  otime DATE;

 5 BEGIN

 6    otime:=NVL(itime,SYSDATE);

 7   RETURNotime;

 8 END;

 9  /

Functioncreated.

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

SQL> createindex in_t_xifenfei ont_xifenfei (f_xifenfei(intime)) online nologging;

createindex in_t_xifenfei ont_xifenfei (f_xifenfei(intime)) online nologging

                                          *

ERRORatline 1:

ORA-30553: The functionis not deterministic

SQL> !oerr ora 30553

30553, 00000, "The function is not deterministic"

// *Cause:  The functionon which the indexis defined isnot deterministic

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

//         isnot deterministic (it depends onpackage state, databasestate,

//         currenttime,oranything other than the functioninputs) then

//          do notcreate the index.  The valuesreturned bya deterministic

//         functionshould notchange even whenthe functionis rewritten or

//          recompiled.

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

SQL>CREATEOR REPLACE FUNCTION f_xifenfei (itime DATE)

 2 RETURNDATE deterministic

 3 IS

 4  otime DATE;

 5 BEGIN

 6    otime:=NVL(itime,SYSDATE);

 7   RETURNotime;

 8 END;

 9  /

Functioncreated.

SQL>createindex in_t_xifenfei ont_xifenfei (f_xifenfei(intime)) online nologging;

Indexcreated.

--创建函数index成功