ORACLEFUNCTION函数中DETERMINISTIC测试

如果函数经常对于上述情况产生确定的记录,可以在
   函数声明中用DETERMINISTIC,这样ORACLE会自动在内存中缓存这些记录集,如果不确定,
   产生的结果就会不确定了
          1,概念及含义
              1,语法
                  CREATE OR REPLACE FUNCTION SCHEMA.FUNTION_NAME
                  (ARGUMENT IN NOC0PY DATATYPE)
                  RETURN DATETYPE
                  DETERMINISTIC
                  IS
                  BEGIN
                  END;
               2,指定DETERMINISTIC用于,对于调用多次同一参数相同值的过程,返回相同的结果时
               3,如果你在基于函数的索引相关的表达式或者对REFRESH FAST及ENABLE QUERY REWRITE的物化视图相关的查询
                 ,必须要指定DETERMINISTIC关键字。
               4,如果以后变更了上述表达式的定义,必须手工重建物化视图或者基于函数的索引
               5,如果一个函数使用包变量,或者访问数据库的方能会影响函数的结果集,则不要用DETERMINISTIC
               6,使用DETERMINISTIC语句的语义规则,此处皆指是定义或声明而非使用
                     1,可以在最顶级的子程序中,指包中
                     2,可以在包规范即包级的子程序中,但不能在包体中;指包的子程序声明中
                     3,不能在一个私有子程序中(子程序:另一个子程序内部或一个包体内部),即包体或另一个子程序中的子程序
                     4,一个有DETERMINISTIC的子程序能调用另一个子程序,
                        不管被调用的子程序是否声明DETERMINISTIC
          2,示例
              1,CREATE OR REPLACE FUNCTION text_length(a CLOB)
                 RETURN NUMBER DETERMINISTIC IS
                 BEGIN
                    RETURN DBMS_LOB.GETLENGTH(a);
                 END;
 
              2,自己编写的示例
                  1,SQL> desc t_deterministic;
                     名称                                      是否为空? 类型
                     ----------------------------------------- -------- -----------------------
                   
                     A                                                  CHAR(2)
                   
                   
                    SQL> select * from t_deterministic;
                   
                    A
                    --
                    12
                    ab
             
             
                   2,  create or replace function func_deterministic(a_len t_deterministic.a%type
                      2  return number deterministic is
                      3  v_len number;
                      4  begin
                      5  select length(a) into v_len from t_deterministic where a=a_len;
                      6  return v_len;
                      7* end;
                    SQL> /
                   
                    函数已创建。
                   
                    3,SQL> select func_deterministic('ab') from t_deterministic;--查询是NULL
                   
                    FUNC_DETERMINISTIC('AB')
                    ------------------------
                   
                   
                    SQL> select func_deterministic('12') from t_deterministic;--查询有结果
                   
                    FUNC_DETERMINISTIC('12')
                    ------------------------
                                          12
                   
                   
                   
          3,加与不加它的区别
          4,哪些语句可以有DETERMINISTIC,此处指FUNCTION,PACKAGE,PACKAGE BODY,PROCEDURE,VIEW
                1,create procedure过程中不能有DETERMINISTIC
                2,CRREATE OR REPLACE PACKAGE的语法参考:Oracle? Database PL/SQL User's Guide and Reference
                     测试包规范
                        --经测试可在包规范中创建有DETERMINISTIC的函数声明
                       CREATE OR REPLACE PACKAGE PKG_DETERMINISTIC
                       AS
                        FUNCTION FUNC_TEST
                         RETURN NUMBER DETERMINISTIC;
                        
                       END;
                      
                       --续上测试,包体也可以创建有DETERMINISTIC的函数声明
                       CREATE OR REPLACE PACKAGE BODY PKG_DETERMINISTIC
                       IS
                       function FUNC_TEST
                       return number deterministic is
                       v_len number;
                        begin
                       select length(a) into v_len from t_deterministic;
                        return v_len;
                       end;
                       END PKG_DETERMINISTIC;

分享名称:ORACLEFUNCTION函数中DETERMINISTIC测试
链接地址:http://hbruida.cn/article/goggid.html