本文主要是介绍函数索引 ORA-30553: The function is not deterministic 解决方法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
建函数索引的时候报错：ORA-30553: The function is not deterministic， 这个函数是自定义的。
SQL>create index mobileIndex on mobile(getmobilearea (callerno));
Google 一下：
| ORA-30553: | 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参数，在创建函数索引，就没有问题了。
CREATE OR REPLACE FUNCTION ICD.getmobilearea (mobileno VARCHAR2)
RETURN VARCHAR2 deterministic
IS
s VARCHAR2 (20);
i INTEGER;
c INTEGER;
BEGIN
FOR i IN 4 .. 11
LOOP
SELECT COUNT (*)
INTO c
FROM mobilearea
WHERE shortno LIKE SUBSTR (mobileno, 1, i) || '%';
s := '000';
IF c = 0
THEN
EXIT;
ELSE
IF c = 1
THEN
SELECT areacode
INTO s
FROM mobilearea
WHERE shortno LIKE SUBSTR (mobileno, 1, i) || '%';
EXIT;
END IF;
END IF;
END LOOP;
RETURN s;
END getmobilearea;
/
这篇关于函数索引 ORA-30553: The function is not deterministic 解决方法的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
