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条记录。