函式呼叫發生在SQL呼叫之前還是過程中
在前兩天文章描述的問題中,引出了一個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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-612713/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 函式棧幀(呼叫過程)函式
- 避免SQL中的函式呼叫SQL函式
- 【圖文】函式呼叫過程中棧的變化函式
- sbrk函式是不是系統呼叫,還是c標準庫函式?函式
- 深入理解 C 語言的函式呼叫過程函式
- 深入理解C語言的函式呼叫過程C語言函式
- C中呼叫Lua函式函式
- SQL中呼叫包含dbms_lock.sleep的函式SQL函式
- PB中呼叫儲存過程儲存過程
- java中呼叫儲存過程Java儲存過程
- Presto 標量函式註冊和呼叫過程簡述REST函式
- php呼叫mysql儲存過程和函式的方法(轉)PHPMySql儲存過程函式
- SQL Server SQL語句中的函式呼叫與Oracle SQL語句函式呼叫一個有趣的差別SQLServer函式Oracle
- JS中函式內套函式的呼叫JS函式
- Matlab中@與函式呼叫Matlab函式
- Sql Server 資料庫中呼叫dll檔案的過程SQLServer資料庫
- 在 SQL Server 的儲存過程中呼叫 Com 元件 (轉)SQLServer儲存過程元件
- jsp中呼叫儲存過程JS儲存過程
- java中呼叫ORACLE儲存過程JavaOracle儲存過程
- 什麼是IIFE(立即呼叫函式表示式)?函式
- 觸發器trigger中呼叫包package(包中含:儲存過程procedure及函式function)_plsql觸發器Package儲存過程函式FunctionSQL
- 子函式呼叫函式
- C++中函式呼叫的用法C++函式
- [譯] 解析 Go 中的函式呼叫Go函式
- 在PowerBuilder中動態呼叫函式UI函式
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- 高階語言反彙編程式的函式呼叫過程 (轉)函式
- 呼叫儲存過程儲存過程
- Oracle如何限制非法呼叫包中過程Oracle
- Jsp中呼叫Oracle儲存過程JSOracle儲存過程
- 外部函式的呼叫函式
- gdb 如何呼叫函式?函式
- 函式指標呼叫函式指標
- Java呼叫SQL Server的儲存過程詳解JavaSQLServer儲存過程
- JavaScript 中匿名函式的遞迴呼叫JavaScript函式遞迴
- 詳解 JS 中 new 呼叫函式原理JS函式
- 函式呼叫中堆疊的個人理解函式
- 程式碼 or 指令,淺析ARM架構下的函式的呼叫過程架構函式