SQL中呼叫包含dbms_lock.sleep的函式

問題：
SQL中函式呼叫時：
對每一個返回行呼叫一次？有快取嗎？
函式中有dbms_lock.sleep時，會停頓嗎？每行還是在輸出陣列？
update中有會如何處理？

--1，test objects
create or replace function sleep_10 return date
As
Begin
  dbms_lock.sleep(1);
  Return sysdate;
End;
/

create or replace function get_sysdate return date
As
Begin
  Return sysdate;
End;
/

--2,select
SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 5月 26 15:59:14 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL> show array
arraysize 15
SQL> set timing on
SQL>
SQL> alter session set nls_date_format='HH24MISS';

會話已更改。

已用時間:  00: 00: 00.00
SQL>
SQL> --test1
SQL> SELECT rownum, SYSDATE, get_sysdate, sleep_10
  2    FROM dual
  3  CONNECT BY LEVEL < 20;

    ROWNUM SYSDAT GET_SY SLEEP_
---------- ------ ------ ------
         1 155401 155401 155402
         2 155401 155402 155403
         3 155401 155403 155404
         4 155401 155404 155405
         5 155401 155405 155406
         6 155401 155406 155407
         7 155401 155407 155408
         8 155401 155408 155409
         9 155401 155409 155410
        10 155401 155410 155411
        11 155401 155411 155412

    ROWNUM SYSDAT GET_SY SLEEP_
---------- ------ ------ ------
        12 155401 155412 155413
        13 155401 155413 155414
        14 155401 155414 155415
        15 155401 155415 155416
        16 155401 155416 155417
        17 155401 155417 155418
        18 155401 155418 155419
        19 155401 155419 155420

已選擇19行。

已用時間:  00: 00: 19.01
SQL>
SQL> --test2
SQL> SELECT rownum, SYSDATE, get_sysdate, sleep_10
  2    FROM all_objects
  3   WHERE rownum < 20;

    ROWNUM SYSDAT GET_SY SLEEP_
---------- ------ ------ ------
         1 155640 155640 155641
         2 155640 155641 155642
         3 155640 155642 155643
         4 155640 155643 155644
         5 155640 155644 155645
         6 155640 155645 155646
         7 155640 155646 155647
         8 155640 155647 155648
         9 155640 155648 155649
        10 155640 155649 155650
        11 155640 155650 155651

    ROWNUM SYSDAT GET_SY SLEEP_
---------- ------ ------ ------
        12 155640 155651 155652
        13 155640 155652 155653
        14 155640 155653 155654
        15 155640 155654 155655
        16 155640 155655 155656
        17 155640 155656 155657
        18 155640 155657 155658
        19 155640 155658 155659

已選擇19行。

已用時間:  00: 00: 19.04
SQL>

結論：
*查詢時sysdate一次性處理,是查詢第一行時的值
*由於呼叫sleep_10,每呼叫一行,sleep一次
*從sleep_10和get_sysdate之間1秒的時差,表明處理二者執行時差在1秒
*執行過程與array size無關,是在獲取結構後,一次性輸出

 

--3,select,函式返回常量
create or replace function sleep_10_return_1 return int
As
Begin
  dbms_lock.sleep(1);
  Return 1;
End;
/

SELECT rownum, SYSDATE, get_sysdate, sleep_10_return_1
  FROM all_objects
 WHERE rownum < 20;

SQL> SELECT rownum, SYSDATE, get_sysdate, sleep_10_return_1
  2    FROM all_objects
  3   WHERE rownum < 20;

    ROWNUM SYSDAT GET_SY SLEEP_10_RETURN_1
---------- ------ ------ -----------------
         1 160446 160446                 1
         2 160446 160447                 1
         3 160446 160448                 1
         4 160446 160449                 1
         5 160446 160450                 1
         6 160446 160451                 1
         7 160446 160452                 1
         8 160446 160453                 1
         9 160446 160454                 1
        10 160446 160455                 1
        11 160446 160456                 1

    ROWNUM SYSDAT GET_SY SLEEP_10_RETURN_1
---------- ------ ------ -----------------
        12 160446 160457                 1
        13 160446 160458                 1
        14 160446 160459                 1
        15 160446 160500                 1
        16 160446 160501                 1
        17 160446 160502                 1
        18 160446 160503                 1
        19 160446 160504                 1

已選擇19行。

已用時間:  00: 00: 19.10

結論:每次sleep後返回

--4,select,函式返回常量+DETERMINISTIC

create or replace function sleep_10_return_2 return int
DETERMINISTIC
As
Begin
  dbms_lock.sleep(1);
  Return 2;
End;
/

SQL>
SQL> SELECT rownum, SYSDATE, get_sysdate, sleep_10_return_2
  2    FROM all_objects
  3   WHERE rownum < 20;

    ROWNUM SYSDAT GET_SY SLEEP_10_RETURN_2
---------- ------ ------ -----------------
         1 161828 161828                 2
         2 161828 161829                 2
         3 161828 161829                 2
         4 161828 161829                 2
         5 161828 161829                 2
         6 161828 161829                 2
         7 161828 161829                 2
         8 161828 161829                 2
         9 161828 161829                 2
        10 161828 161829                 2
        11 161828 161829                 2

    ROWNUM SYSDAT GET_SY SLEEP_10_RETURN_2
---------- ------ ------ -----------------
        12 161828 161829                 2
        13 161828 161829                 2
        14 161828 161829                 2
        15 161828 161829                 2
        16 161828 161829                 2
        17 161828 161829                 2
        18 161828 161829                 2
        19 161828 161829                 2

已選擇19行。

已用時間:  00: 00: 01.06
 
結論:第一次sleep後返回,後續呼叫沒有sleep

--5,update
drop table t;
create table t(d date);
insert into t
select null from dual
connect by level<20;
commit;

SQL> update t set d=sleep_10;

已更新19行。

已用時間:  00: 00: 19.00
SQL> commit;

提交完成。

已用時間:  00: 00: 00.01
SQL> select * from t;

D
------
162247
162248
162249
162250
162251
162252
162253
162254
162255
162256
162257
162258
162259
162300
162301
162302
162303
162304
162305

已選擇19行。

已用時間:  00: 00: 00.01

結論:每次sleep後返回

--6,update,函式返回常量

create or replace function sleep_10_return_1day return date
As
Begin
  dbms_lock.sleep(1);
  Return trunc(sysdate);
End;
/

SQL> update t set d=sleep_10_return_1day;

已更新19行。

已用時間:  00: 00: 19.00 **sleep
SQL>
SQL> select * from t;

D
------
000000
000000
000000
000000
000000
000000
000000
000000
000000
000000
000000

D
------
000000
000000
000000
000000
000000
000000
000000
000000

已選擇19行。

已用時間:  00: 00: 00.01

結論:每次sleep後返回

--7,update,函式返回常量+DETERMINISTIC
create or replace function sleep_10_return_2day return date
DETERMINISTIC
As
Begin
  dbms_lock.sleep(1);
  Return sysdate;
End;
/

SQL> update t set d=sleep_10_return_2day;

已更新19行。

已用時間:  00: 00: 01.01
SQL> select * from t;

D
------
000000
000000
000000
000000
000000
000000
000000
000000
000000
000000
000000

D
------
000000
000000
000000
000000
000000
000000
000000
000000

已選擇19行。

已用時間:  00: 00: 00.00

結論:第一次sleep後返回,後續呼叫沒有sleep

--8,update,函式返回常量+DETERMINISTIC(acturely,variable)
create or replace function sleep_10_return_3day return date
DETERMINISTIC
As
Begin
  dbms_lock.sleep(1);
  Return sysdate;
End;
/

SQL> update t set d=sleep_10_return_3day;

已更新19行。

已用時間:  00: 00: 01.00  **
SQL> select * from t;

D
------
163330
163330
163330
163330
163330
163330
163330
163330
163330
163330
163330

D
------
163330
163330
163330
163330
163330
163330
163330
163330

已選擇19行。

已用時間:  00: 00: 00.01

結論:第一次sleep後返回,後續呼叫沒有sleep