select语句中调用函数

SELECT语句中调用的函数，下面分三个情况来测试：1、自己编写的函数。2、SYSDATE。3、序列值。这三个也是我们在SELECT语句会用到的情况。

计算调用次数的包：

CREATE OR REPLACE PACKAGE counter IS

PROCEDURE reset;

PROCEDURE inc;

PROCEDURE show;

END;

/

CREATE OR REPlACE PACKAGE BODY counter IS

cnt   PLS_INTEGER := 0;

PROCEDURE reset IS

BEGIN

    cnt := 0;

END reset;

END reset;

PROCEDURE inc IS

BEGIN

    cnt := cnt + 1;

END inc;

PROCEDURE show IS

BEGIN

    DBMS_OUTPUT.PUT_LINE('Execution Count:'||cnt);

END show;

在示例数据库以SCOTT用户登录进行测试：

1、创建三个函数：

create or replace function fun_test1 return number

as

begin

       return 1;

end;

create or replace function fileNumber return varchar2
      as
      v_result varchar2(3);
    begin
      select lpad(to_char(delreportnum.nextval),3,'0') into v_result from dual;
      return v_result;
    end;

create or replace
FUNCTION FUN_TEST(DAT DATE)
RETURN VARCHAR2
IS
BEGIN
  DBMS_LOCK.SLEEP(1);
  RETURN TO_CHAR(DAT,'YYYY-MM-DD HH24:MI;SS');
END;

 

测试：

1、select empno,fun_test1 from emp;

     exec counter.show;

      结果：14次调用。

2、select empno,sysdate,FUN_TEST from emp;

     结果：虽然在每次调用函数的时候程序都SLEEP1秒，但是每一条的时间都一样，这说明一个查询中不管有多少条数据，而SYSDATE只获取一次。

3、insert all
when mod(c1,2)=0 then
 INTO T1
 (Col)
 VALUES
 (C1||empno)
 select c1,empno from (select fileNumber as c1,empno from (select * from emp order by empno));

结果：T1表中的数据全是奇数，为什么呢？其执行过程很可能是如下：

           对每一个查询的结果都执行一次INSERT ALL，MOD函数先调用一次FILENUMBER函数结果是偶数，则执行插入，SELECT又执行一次FILENUMBER，所以看到的结果全是奇数。

4、insert all

     into t1 (Col) VALUES (C1)

     into t2 (Col) VALUES (C1)

select fileNumber as c1 from emp;

结果：一个表是全是奇数，一个表里全是偶数。其执行的情况可能如下：

          对每一个查询结果，执行T1表插入时执行一次函数FILENUMBER，执行T2表插入时执行一次函数。

5、insert all

     into t1 (Col) VALUES (C1)

     into t2 (Col) VALUES (C1)

select fileNumber as c1 ,rownum from emp;

结果：两个表的结果是一样的，都是14条记录。

ORACLE官方文档里一些话可能能解释这个现象：

“假设视图并不包含以下任何信息，那么当视图有一个或更多基础表时，优化器可以把视图并入引用查询块中。

设置操作符（UNION，UNION ALL， INTERSECT，MINUS），一个CONNET BY子句，一个ROWNUM伪列，聚集函数（AVG，COUNT，MAX，MIN，SUM）”

6、 select fileNumber,c1, fileNumber as d1 from (select fileNumber as c1 from emp) where mod(c1,2)=0;

结果：一次有结果，一次没有结果，这样交替。这是怎么产生的呢？其执行顺序可能如下：

先执行WHERE条件的MOD调用FILENUMBER函数一次，若是奇数查询结果为空，若是偶数，则查询出14条记录。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23065269/viewspace-700342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23065269/viewspace-700342/