select语里里的function的读一致性问题
[sql]
drop table test;
/
create table test as select 1 as t1 from dual
/
create or replace function f_test return number
--deterministic
as
v_t1 test.t1%type;
v_date date;
begin
v_date := sysdate + 1 / 24 / 60 / 60;
for i in 1 .. 1000000 loop
exit when sysdate >= v_date;
for j in 1 .. 1000000 loop
exit when sysdate >= v_date;
for k in 1 .. 1000 loop
exit when sysdate >= v_date;
end loop;
end loop;
end loop;
select t1 into v_t1 from test where rownum <= 1;
return v_t1;
end;
/
--session1:
begin
loop
dbms_lock.sleep(1);
update test set t1 = t1 + 1;
commit;
end loop;
end;
/
--session2:
select f_test as fun_t1,t1 from test connect by level <= 10;
SQL>
select f_test as fun_t1,t1 from test connect by level <= 10;
FUN_T1 T1
---------- ----------
5 4
6 4
7 4
8 4
9 4
10 4
11 4
12 4
13 4
14 4
10 rows selected
那想让fun返回数据一致,怎么办
使用参数“deterministic” 即可.