select语里里的function的读一致性问题

2014-11-24 13:31:21 · 作者: · 浏览: 0
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” 即可.