Oracle PL/SQL之自訂函數的讀一致性

讀一致性即確保查詢的結果與發起查詢的時刻的資料一致，不管在此查詢期間其他事務有沒有更改資料。

 

首先假設我們有如下函數用來根據部門id求該部門的總薪資：

CREATE OR REPLACE FUNCTION sum2(p_deptno IN NUMBER)<br /> RETURN NUMBER IS --PRAGMA AUTONOMOUS_TRANSACTION;<br />l_ret NUMBER;<br />BEGIN<br /> dbms_lock.sleep(5);<br /> --DBMS_BACKUP_RESTORE.SLEEP(5);<br /> dbms_output.put_line(systimestamp);<br /> SELECT SUM(sal)<br /> INTO l_ret<br /> FROM emp<br /> WHERE deptno = p_deptno;<br /> RETURN l_ret;<br />END sum2;

 

然後我們開個session（s1），執行如下查詢（q1）：

SELECT deptno<br /> ,SUM(sal)<br /> ,sum2(deptno)<br /> ,systimestamp<br />FROM emp<br />GROUP BY deptno;

 

在q1執行的過程中，我們又開個新的session（s2），執行更新命令並提交：

SQL> update emp set sal=sal+1;</p><p>14 rows updated</p><p>SQL> commit;</p><p>Commit complete</p><p>SQL>

 

接著回到s1，看q1的執行結果：

SQL> SELECT deptno<br /> 2 ,SUM(sal)<br /> 3 ,sum2(deptno)<br /> 4 ,systimestamp<br /> 5 FROM emp<br /> 6 GROUP BY deptno;</p><p>DEPTNO SUM(SAL) SUM2(DEPTNO) SYSTIMESTAMP<br />------ ---------- ------------ --------------------------------------------------------------------------------<br /> 30 9400 9406 24-JUN-11 08.31.19.722000 PM +08:00<br /> 20 10875 10880 24-JUN-11 08.31.19.722000 PM +08:00<br /> 10 8750 8753 24-JUN-11 08.31.19.722000 PM +08:00</p><p>SQL>

 

我們發現sum(sal)和sum2(deptno)的結果不一致。

當我們將自訂函數應用於sql語句中，而該sql又需要執行很長時間，並且在這段時間中恰好又有其他dml會更改該sql中的某些表時，這種不一致就會體現出來。

 

關於如何避免這種不一致，可以使用SET TRANSACTION READ ONLY，詳細內容可參看我下面的這篇文章：

Oracle PL/SQL之SET TRANSACTION READ ONLY（事務隔離性）

http://blog.csdn.net/t0nsha/archive/2011/06/24/6566517.aspx