1. 首页
  2. IT资讯

ORA-30553: 函数不确定

From:http://www.byywee.com/page/M0/S234/234066.html在ORACALE数据库中,创建函数索引时,若函数是自定义的函数,须在定义函数时加上deterministic关健字,不然会出现“函数不确定”的错误提示。
创建函数索引 ORA-30553: 函数不确定.
原函数如下:create or replace function test(id in varchar) return number is res number ; begin res := pf_ia.test(id); return res ; end test;
修改后如下
create or replace function test(id in varchar) return number deterministic is res number ; begin res := pf_ia.test(id); return res ; end test;添加声明后解决。From:http://wallimn.iteye.com/blog/1186010今天学习物化视图,在Oracle9.2.0.0上遇到这个问题,但在9.2.0.8上没有这个问题。就上网查了查,又新掌握了点知识。 SQL> conn zwz Enter password: Connected. SQL> desc testfun; Name Null? Type —————————————– ——– —————————- ID NUMBER(38) NAME VARCHAR2(22) SQL> select * from testfun; ID NAME ———- ———————- 1 anbob.com 2 anbob.com 3 weijar.com SQL> create or replace function f_upp(p_name varchar2) 2 return varchar2 3is 4begin 5return upper(p_name); 6 end; 7 / Function created. SQL> create index idx_f_upp on testfun(f_upp(name)); create index idx_f_upp on testfun(f_upp(name)) * ERROR at line 1: ORA-30553: The function is not deterministic SQL> create or replace function f_upp(p_name varchar2) 2 return varchar2 deterministic 3 begin 4 return upper(p_name); 5* end; Warning: Function created with compilation errors. SQL> show err Errors for FUNCTION F_UPP: LINE/COL ERROR ——– —————————————————————– 3/1 PLS-00103: Encountered the symbol “BEGIN” when expecting one of the following: ; is authid as cluster order using external deterministic parallel_enable pipelined aggregate The symbol “is” was substituted for “BEGIN” to continue. SQL>l2 2* return varchar2 deterministic SQL> input is SQL> l 1 create or replace function f_upp(p_name varchar2) 2 return varchar2 deterministic 3 is 4 begin 5 return upper(p_name); 6* end; SQL> run 1 create or replace function f_upp(p_name varchar2) 2 return varchar2 deterministic 3 is 4 begin 5 return upper(p_name); 6* end; Function created. SQL> create index idx_f_upp on testfun(f_upp(name)); Index created.. Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views. 这个关键字表明:如果你的函数当输入一样时,会返回同样的结果. 这样, 数据库就用前一个计算的值,而不需要再重新计算一次. 这对于使用函数索引等,会得到相当大的好处. 英文说明: DETERMINISTIC Clause Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views. Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function. The following semantic rules govern the use of the DETERMINISTIC clause: You can declare a top-level subprogram DETERMINISTIC. You can declare a package-level subprogram DETERMINISTIC in the package specification but not in the package body. You cannot declare DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body). A DETERMINISTIC subprogram can call another subprogram whether the called program is declared DETERMINISTIC or not.

分享到: ORA-30553: 函数不确定 ORA-30553: 函数不确定

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-768131/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195896.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code