ANBOB™

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参数，保证返回值

打赏

微信扫一扫，打赏作者吧～