函数索引 ORA-30553: The function is not deterministic 解决方法

建函数索引的时候报错：ORA-30553:Thefunctionisnotdeterministic，这个函数是自定义的。

SQL>createindexmobileIndexonmobile(getmobilearea(callerno));

Google一下：

ORA-30553:	Thefunctionisnotdeterministic
Cause:	Thefunctiononwhichtheindexisdefinedisnotdeterministic
Action:	Ifthefunctionisdeterministic,markitDETERMINISTIC. Ifitisnotdeterministic(itdependsonpackagestate,databasestate,currenttime,oranythingotherthan thefunctioninputs)thendonotcreatetheindex.Thevaluesreturnedbyadeterministicfunction shouldnotchangeevenwhenthefunctionisrewrittenorrecompiled.

<!--EndFragment-->

解决如下：

在创建基于自定义函数时,指定deterministic参数，在创建函数索引，就没有问题了。

CREATEORREPLACEFUNCTIONICD.getmobilearea(mobilenoVARCHAR2)

RETURNVARCHAR2deterministic

IS

sVARCHAR2(20);

iINTEGER;

cINTEGER;

BEGIN

FORiIN4..11

LOOP

SELECTCOUNT(*)

INTOc

FROMmobilearea

WHEREshortnoLIKESUBSTR(mobileno,1,i)||'%';

s:='000';

IFc=0

THEN

EXIT;

ELSE

IFc=1

THEN

SELECTareacode

INTOs

FROMmobilearea

WHEREshortnoLIKESUBSTR(mobileno,1,i)||'%';

EXIT;

ENDIF;

ENDIF;

ENDLOOP;

RETURNs;

ENDgetmobilearea;

/

<!--EndFragment-->