[20180531]函數調用與遞歸.txt

[20180531]函數調用與遞歸.txt

--//前幾天我發現開發把一個計算公式建立為函數,我本想這樣不好,因為執行select f(x)...,的每行都會調用自定義的函數.

--//我本以為這樣會産生大量的遞規調用.但是還是有點吃驚,如果函數裡面沒有一些select語句,這樣的調用自定義函數,不會

--//産生遞規,當然我個人這樣還是不提倡這樣的方式.

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

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

IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

grant execute on sys.dbms_lock to scott;

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)

RETURN NUMBER AS

BEGIN

--//  sys.dbms_lock.sleep(seconds);

  RETURN seconds;

END;

/

2.測試1:

set autot traceonly

select rowid,dept.*,sleep(1) from dept ;

set autot off

SCOTT@test01p> select rowid,dept.*,sleep(1) from dept ;

Execution Plan

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

Plan hash value: 3383998547

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |

Statistics

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

          0  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

       1021  bytes sent via SQL*Net to client

        544  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

--//可以發現沒有遞歸,如果第一次存在遞歸,多執行計劃在檢視.(以下測試采用相似的方式).

SCOTT@test01p> set autot off

3.測試2:

--//如果修改如下:

  sys.dbms_lock.sleep(seconds);

--//可以發現調用sys.dbms_lock.sleep也沒有出現遞歸.

4.測試3:

RETURN NUMBER

is

d_date date;

  select sysdate into d_date from dual;

          4  recursive calls

--//可以發現隻有在函數裡面執行sql語句,才存在遞歸調用.