函数调用发生在SQL调用之前还是过程中

 

对于上一篇描述的DBMS_RANDOM.VALUE函数而言，显然函数的调用是发生在SQL语句的执行过程中。但是如果查看《SQL语句中常量的处理》这篇文章，可以看到对于TO_DATE之类的函数调用，当输入参数为常数时，Oracle会将其作为常数处理，在SQL语句执行之前就进行了调用。

同样都是函数，同样都以常数作为参数，同样都和表的列没有依赖，为什么有的函数在SQL调用前运行，而有的函数在SQL调用中执行。难道仅仅是因为一个是普通的函数，另一个是包中的函数。

问题显然与函数是否存储在包中没有关系，实际上是函数的一个特性控制了函数调用的时间。如果一个函数是确定性的，对于常量的输入，得到的结果也是常量，因此Oracle会在SQL运行之前对函数进行调用。而如果函数不是确定性的，Oracle无法保证函数输出的确定性，因此只能在SQL的运行时执行。

SQL> CREATE TABLE T (ID NUMBER);

Table created.

SQL> INSERT INTO T SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE FUNCTION F_TEST_DETER

  2  RETURN NUMBER DETERMINISTIC AS

  3  BEGIN

  4  DBMS_LOCK.SLEEP(0.01);

  5  RETURN 1;

  6  END;

  7  /

Function created.

SQL> CREATE OR REPLACE FUNCTION F_TEST_NODETER

  2  RETURN NUMBER AS

  3  BEGIN

  4  DBMS_LOCK.SLEEP(0.01);

  5  RETURN 1;

  6  END;

  7  /

Function created.

SQL> SELECT OBJECT_NAME, DETERMINISTIC

  2  FROM USER_PROCEDURES

  3  WHERE OBJECT_NAME LIKE 'F_TEST_%';

OBJECT_NAME                    DET

------------------------------ ---

F_TEST_NODETER                 NO

F_TEST_DETER                   YES

SQL> SET TIMING ON

SQL> SELECT * FROM T WHERE ID = F_TEST_DETER;

        ID

----------

         1

Elapsed: 00:00:00.02

SQL> SELECT * FROM T WHERE ID = F_TEST_NODETER;

        ID

----------

         1

Elapsed: 00:01:49.99

根据运行时间就可以判断处理，确定性函数只在SQL调用之前运行了一次，而非确定性函数则对于T表的每条记录都运行了一次。

如果将上一篇文章中的DBMS_RANDOM.VALUE包进行封装，并设置为确定性函数，则上一篇的查询结果就会改变：

SQL> SET TIMING OFF

SQL> CREATE OR REPLACE FUNCTION F_RANDOM

  2  RETURN NUMBER DETERMINISTIC AS

  3  BEGIN

  4  RETURN ROUND(DBMS_RANDOM.VALUE(1, 10000));

  5  END;

  6  /

Function created.

SQL> SELECT * FROM T_ID WHERE ID = F_RANDOM;

        ID

----------

      9548

SQL> SELECT * FROM T_ID WHERE ID = F_RANDOM;

        ID

----------

      6925

SQL> SELECT * FROM T_ID WHERE ID = F_RANDOM;

        ID

----------

      7783

SQL> SELECT * FROM T_ID WHERE ID = F_RANDOM;

        ID

----------

      7302

SQL> SELECT * FROM T_ID WHERE ID = ROUND(DBMS_RANDOM.VALUE(1, 10000));

        ID

----------

      2730

SQL> SELECT * FROM T_ID WHERE ID = ROUND(DBMS_RANDOM.VALUE(1, 10000));

        ID

----------

      9391

SQL> SELECT * FROM T_ID WHERE ID = ROUND(DBMS_RANDOM.VALUE(1, 10000));

no rows selected

SQL> SELECT * FROM T_ID WHERE ID = ROUND(DBMS_RANDOM.VALUE(1, 10000));

        ID

----------

      3935

SQL> SELECT * FROM T_ID WHERE ID = ROUND(DBMS_RANDOM.VALUE(1, 10000));

        ID

----------

      6132

      7810

这很好的说明了确定性和非确定性函数的区别。

最后通过例子说明问题之和函数的确定性有关，和函数是否在包中无关：

SQL> CREATE OR REPLACE PACKAGE PA_TEST AS

  2  FUNCTION F_DETER RETURN NUMBER DETERMINISTIC;

  3  FUNCTION F_NODETER RETURN NUMBER;

  4  END;

  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PA_TEST AS

  2  FUNCTION F_DETER RETURN NUMBER DETERMINISTIC AS

  3  BEGIN

  4  DBMS_LOCK.SLEEP(0.01);

  5  RETURN 1;

  6  END;

  7  FUNCTION F_NODETER RETURN NUMBER AS

  8  BEGIN

  9  DBMS_LOCK.SLEEP(0.01);

 10  RETURN 1;

 11  END;

 12  END;

 13  /

Package body created.

SQL> SET TIMING ON

SQL> SELECT * FROM T_ID WHERE ID = PA_TEST.F_DETER;

        ID

----------

         1

Elapsed: 00:00:00.02

SQL> SELECT * FROM T_ID WHERE ID = PA_TEST.F_NODETER;

        ID

----------

         1

Elapsed: 00:01:49.98