[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语句,才存在递归调用.