PLSQL學習-【5遊標】

遊標是一個容器，本質是一塊記憶體，如果不釋放，就會佔用記憶體。其實相當於java中的list。

declare
  cursor cu_emp is select * from emp;
  v_cu cu_emp%rowtype;
  begin
  open cu_emp;
    loop
       fetch cu_emp into v_cu;
       exit when cu_emp%notfound;
       dbms_output.put_line(v_cu.ename ||'--' || v_cu.sal);
    end loop;
  close cu_emp;
  end;

for 看不出cursor本質：

declare
  cursor cu_emp is select * from emp where empno <100 or empno >9000;
  begin
    for v_cu in cu_emp loop
       dbms_output.put_line(v_cu.ename ||' ' || v_cu.sal);
    end loop;
  end;

首先判斷遊標有沒有開啟，如果沒有開啟，開啟遊標，一般們有這麼寫的。

 create or replace procedure query_dept(p_empno in number, p_cur out sys_refcursor)
 is
 begin
   open p_cur for 
   select * from emp where empno = p_empno;
 end; 
Procedure created.

當emp中資料隨著時間延續，emp中資料越來越多，open cursor會很長時間。

這時候建立臨時表解決問題。
insert into tmp_emp select * from emp where empno = p_empno;
open p_cur for select * from tmp_emp;
一般是不會用到臨時表的。我們的cursor結果集是反給java的。


高階顯示遊標：

傳入值不寫長度

declare
  cursor cu_emp (p_deptno number, p_job varchar2) is
   select * from emp where deptno=p_deptno and job=p_job for update; 
  v_cu cu_emp%rowtype;
  begin
   open cu_emp(10,'CLERK'); 
    loop
     fetch cu_emp into v_cu;
     exit when cu_emp%notfound;
      dbms_output.put_line(v_cu.ename||' '||v_cu.deptno||' '||v_cu.job);
    end loop;
   close cu_emp;
  end;

新增了行級鎖和表級鎖

for update of sal nowait

查詢比各部門平均工資高的那些人，內聯檢視

1 、 for update  和  for update nowait  的區別：

  首先一點，如果只是 select  的話， Oracle 是不會加任何鎖的，也就是 Oracle 對  select  讀到的資料不會有任何限制，雖然這時候有可能另外一個程式正在修改表中的資料，並且修改的結果可能影響到你目前 select 語句的結果，但是因為沒有鎖，所以 select 結果為當前時刻表中記錄的狀態。

  如果加入了 for update ，   則 Oracle 一旦發現（符合查詢條件的）這批資料正在被修改，則不會發出該 select 語句查詢，直到資料被修改結束（被 commit ），馬上自動執行這個 select 語句。

  同樣，如果該查詢語句發出後，有人需要修改這批資料（中的一條或幾條），它也必須等到查詢結束後（ commit ）後，才能修改。

for update nowait 和  for update  都會對所查詢到得結果集進行加鎖，所不同的是，如果另外一個執行緒正在修改結果集中的資料， for update nowait  不會進行資源等待，只要發現結果集中有些資料被加鎖，立刻返回   “ ORA-00054 錯誤，內容是資源正忙 ,  但指定以  NOWAIT  方式獲取資源”。

for update  和  for update nowait  加上的是一個行級鎖，也就是隻有符合 where 條件的資料被加鎖。如果僅僅用 update 語句來更改資料時，可能會因為加不上鎖而沒有響應地、莫名其妙地等待，但如果在此之前， for  update NOWAIT 語句將要更改的資料試探性地加鎖，就可以透過立即返回的錯誤提示而明白其中的道理，或許這就是 For Update 和 NOWAIT 的意義之所在。

  經過測試，以 for update  或  for update nowait 方式進行查詢加鎖，在 select 的結果集中，只要有任何一個記錄在加鎖，則整個結果集都在等待系統資源（如果是 nowait ，則丟擲相應的異常）

2 、 for update nowait  與  for update  的目的 : 
鎖定表的所有行，排斥其他針對這個表的寫操作。確保只有當前事務對指定表進行寫操作。  
for update nowait 和  for update 的區別：

別的事務要對這個表進行寫操作時，是等待一段時間還是馬上就被資料庫系統拒絕而返回 . 制定採用 nowait 方式來進行檢索，所以當發現資料被別的 session 鎖定中的時候，就會迅速返回 ORA-00054 錯誤，內容是資源正忙 ,  但指定以  NOWAIT  方式獲取資源。所以在程式中我們可以採用 nowait 方式迅速判斷當前資料是否被鎖定中，如果鎖定中的話，就要採取相應的業務措施進行處理。  
如何理解上面的話 . 
開啟一會話  ( 就是開一個 sqlwindow) 
  select  empno,ename from emp where empno='7369' for update nowait ; 
得到下面結果集 : 
    empno  ename 
    7369    smith 
開啟另一會話  
  select  empno,ename from emp where empno='7369' for update nowait ; 
返回 RA-00054 錯誤，內容是資源正忙 ,  但指定以  NOWAIT  方式獲取資源  
上面會話都提交 commit; 
~~~~~~~~~~~~~~~~~~~~~ 
開啟一會話 , 
  select  empno,ename from emp where empno='7369' for update ; 
得到下面結果集 : 
    empno  ename 
    7369    smith 
開啟另一會話  
  select  empno,ename from emp where empno='7369' for update;

阻塞，不返回錯誤。  
提交第一個會話，第二個回話自動執行  
提交第二個會話  
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
   for update:  當第一個 session 最後 commit 或者 rollback 之後，第二個 session 中的檢索結果就是自動跳出來，並且也把資料鎖定住 . 
   開啟一會話：  
     select empno,ename from emp   where empno="7369" for update ；  
得到下面結果集 : 
    empno  ename 
    7369    smith 
開啟另一個會話，  
   update emp set ename='ALLEN'  where empno="7396"; 
阻塞。  
   提交第一個會話， update  語句執行  
再開啟一會話  
    update emp set ename="SMITH" where empno='7396'; 
同樣阻塞，雖然第一個會話因為提交而釋放了鎖，但是第二個會話中的 update  又給這一行加鎖了 ; 
for update nowait: 當你第一個 session 放開鎖定以後 , 第二個 session 才能正常執行。當你第二個 session 語句執行後，資料又被你第二個 session 語句鎖定住了，這個時候只要你第二個 session 語句後還沒有 commit ，別的 session 照樣不能對資料進行鎖定更新等等。

對比區別：  
select * from TTable1 for update 鎖定表的所有行，只能讀不能寫  
2  select * from TTable1 where pkid = 1 for update 只鎖定 pkid=1的行  
3  select * from Table1 a join Table2 b on a.pkid=b.pkid for update 鎖定兩個表的所有記錄  
4 select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update 鎖定兩個表的中滿足條件的行  
5. select * from Table1 a join Table2 b on a.pkid=b.pkid where a.pkid = 10 for update of a.pkid 只鎖定 Table1中滿足條件的行  
for update 是把所有的表都鎖點  for update of 根據 of 後表的條件鎖定相對應的表  
----------- 
關於 NOWAIT(如果一定要用 FOR UPDATE，我更建議加上 NOWAIT) 
當有 LOCK衝突時會提示錯誤並結束 STATEMENT而不是在那裡等待 (比如 :要查的行已經被其它事務鎖了 ,當前的鎖事務與之衝突 ,加上 nowait,當前的事務會結束會提示錯誤並立即結束  STATEMENT而不再等待 ). 
如果加了 for update後 該語句用來鎖定特定的行（如果有 where子句，就是滿足 where條件的那些行）。當這些行被鎖定後，其他會話可以選擇這些行，但不能更改或刪除這些行，直到該語句的事務被 commit語句或 rollback語句結束為止。  
因為 FOR   UPDATE子句獲得了鎖，所以 COMMIT將釋放這些鎖。當鎖釋放了，該遊標就無效了。  
就是這些區別了  
  
關於 oracle中的 select...for update of columns 
問題，如下： select * from emp where empno = 7369 for update; 會對錶中員工編號為 7369的記錄進行上鎖。其他使用者無法對該記錄進行操作，只能查詢。 select * from emp where empno = 7369 for update of sal; 這條語句是不是意味著只對表中的 7369 這一行的 sal欄位的資料進行了上鎖，其他資料則可以被其他使用者做更新操作呢。學員測試結果為二條語句的效果是一樣的。其他使用者對整行都無法更新，那麼是不是意味著  for update of columns這句沒有什麼意義呢？

   這個問題估計很多玩 ORACLE的同學們都沒有去思考過【網上相關的帖子不多】。現在將其功能講解一下。

   從單獨一張表的操作來看，上面二條語句的效果確實是相同的。但是如果涉及到多表操作的時候  for update of columns就起到了非常大的作用了。現假定有二個使用者， scott和 mm。

scott 執行語句： select * from emp e,dept d where e.deptno = d.deptno for update; --對二張表都進行了整表鎖定  
mm執行語句： select * from scott.dept for update wait 3; --試圖鎖定 scott使用者的 dept表

結果是：  
ERROR 位於第  1 行 : 
ORA-30006: 資源已被佔用 ; 執行操作時出現  WAIT 超時

現在， scott使用者先進行解鎖 rollback,再在 for update語句後面加上 of columns，進行測試

scott 執行語句： select * from emp e,dept d where e.deptno = d.deptno for update of sal ; 
mm執行語句： select * from scott.dept for update wait 3;

結果是：  
成功鎖定了 dept表的資料 .

mm 再次執行語句： select * from scott.emp for update wait 3;

結果是：  
ERROR 位於第  1 行 : 
ORA-30006: 資源已被佔用 ; 執行操作時出現  WAIT 超時

透過這段程式碼案例，我們可以得到結論， for update of columns 用在多表連線鎖定時，可以指定要鎖定的是哪幾張表，而如果表中的列沒有在 for update of 後面出現的話，就意味著這張表其實並沒有被鎖定，其他使用者是可以對這些表的資料進行 update操作的。這種情況經常會出現在使用者對帶有連線查詢的檢視進行操作場景下。使用者只鎖定相關表的資料，其他使用者仍然可以對檢視中其他原始表的資料來進行操作。  
  
 的 for update行鎖  
SELECT...FOR UPDATE 語句的語法如下：  
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 
其中：  
OF 子句用於指定即將更新的列，即鎖定行上的特定列。  
WAIT 子句指定等待其他使用者釋放鎖的秒數，防止無限期的等待。  
“使用 FOR UPDATE WAIT”子句的優點如下：  
１防止無限期地等待被鎖定的行；  
２允許應用程式中對鎖的等待時間進行更多的控制。  
３對於互動式應用程式非常有用，因為這些使用者不能等待不確定  
４ 若使用了 skip locked，則可以越過鎖定的行，不會報告由 wait n 引發的‘資源忙’異常報告

示例 : 
create table t(a varchar2(20),b varchar2(20)); 
insert into t values('1','1'); 
insert into t values('2','2'); 
insert into t values('3','3'); 
insert into t values('4','4'); 
現在執行如下操作：  
在 plsql develope中開啟兩個 sql視窗，  
在 1視窗中執行 sql 
select * from t where a='1' for update; 
在 2視窗中執行 sql1 
1. select * from t where a='1'; 這一點問題也沒有，因為行級鎖不會影響純粹的 select語句  
再執行 sql2 
2. select * from t where a='1' for update; 則這一句 sql在執行時，永遠處於等待狀態，除非視窗 1中 sql被提交或回滾。  
如何才能讓 sql2不等待或等待指定的時間呢？ 我們再執行 sql3 
3. select * from t where a='1' for update nowait; 則在執行此 sql時，直接報資源忙的異常。  
若執行  select * from t where a='1' for update wait 6; 則在等待 6秒後，報 資源忙的異常。  
如果我們執行 sql4 
4. select * from t where a='1' for update nowait skip Locked; 則執行 sql時，即不等待，也不報資源忙異常。  
現在我們看看執行如下操作將會發生什麼呢？  
在視窗 1中執行：  
select * from t where rownum<=3 nowait skip Locked; 
在視窗 2中執行：  
select * from t where rownum<=6 nowait skip Locked; 
select for update 也就如此了吧， insert、 update、 delete操作預設加行級鎖，其原理和操作與 select for update並無兩樣。  
select for update of ，這個 of子句在牽連到多個表時，具有較大作用，如不使用 of指定鎖定的表的列，則所有表的相關行均被鎖定，若在 of中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。