在前两天文章描述的问题中，引出了一个BUG，到底函数的调用是发生在SQL运行之前，还是发生在SQL的运行之中。
动态返回结果的视图：http://yangtingkun.itpub.net/post/468/490262
使用DBMS_RANDOM过程引发的问题：http://yangtingkun.itpub.net/post/468/490322
sql语句中常量的处理：http://yangtingkun.itpub.net/post/468/20038
对于上一篇描述的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
关于DETERMINISTIC函数的文章可以参考：
Deterministic函数：http://yangtingkun.itpub.net/post/468/26793
Deterministic函数（二）：http://yangtingkun.itpub.net/post/468/394107
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-25 18:24:46 |
赞(
操作
