Oracle學習筆記整理之遊標篇

dbasdk發表於2014-08-08
Oracle筆記
遊標分為隱式、顯式、REF三種遊標。
而隱式遊標和顯式遊標又都屬於靜態遊標,REF遊標屬於動態遊標。
靜態遊標和動態遊標的最大區別在於靜態遊標是在執行前將遊標變數和SQL關聯，而動態遊標恰恰相反是在執行後將遊標變數和SQL進行關聯。
隱式遊標是由Oracle自動管理，也稱sql遊標。所有的DML都被Oracle解析成為一個名為SQL的隱式遊標。DML包括Insert、Delete、Update、Merge into。而Select屬於DQL。
隱式遊標的屬性有：
 
 屬性名  說明
 sql%found  影響行數大於等於1是為 ture
 sql%notfound  沒有影響行是為true
 sql%rowcount  受影響的行數
 sql%isopen  遊標是否開啟，始終為false
 sql%bulk_rowcount(index)  ...
 sql%bulk_exceptions(index).error_index  ...
 sql%bulk_exceptions(index).error_code  ...

隱式遊標例項：
begin
  --執行DML操作
  update chg_test_b b set b.chg_name = 'ff' where b.chg_id = 5;
  --判斷是否有受影響行
  if sql%found then
    --列印受影響行數
    dbms_output.put_line('影響行數：' || sql%rowcount);
  end if;
  --判斷是否沒有受影響行
  if sql%notfound then
    dbms_output.put_line('id為5的記錄不存在');
  end if; 
end;
無參顯式遊標例項：
declare
  --宣告遊標表變數並關聯sql
  cursor rowList is
    select * from chg_test_b b;
  --宣告行變數
  --如果上面的查詢語句只有一個查詢欄位，這裡也可以使用正常的變數宣告方式（v_rowValue varchar2(20);）。
  rowValue chg_test_b%rowtype;
begin
  open rowList; --開啟遊標
  --如果確定由表中只有一條記錄的話，loop和end loop可以不寫，而exit必須存在於遊標迴圈內，所以也不需要寫。
  loop
    fetch rowList
      into rowValue; --取出遊標內的值放到rowValue中
    exit when rowList%notfound; --判斷是否還存在記錄，如果不存在終止遊標
    dbms_output.put_line(rowValue.chg_name); --將的到的值列印,如果查詢只有一個欄位這裡只需寫變數名即可。
  end loop;
  close rowList; --關閉遊標
end;
有參顯式遊標例項：
declare
  --宣告帶引數的遊標變數並關聯sql,並將引數與sql進行關聯
  cursor rowList(c_name varchar2, c_id number) is
    select * from chg_test_b b where b.chg_name = c_name and b.chg_id = c_id;
  --宣告行變數
  --如果上面的查詢語句只有一個查詢欄位，這裡也可以使用正常的變數宣告方式（v_rowValue varchar2(20);）。
  rowValue chg_test_b%rowtype;
begin
  open rowList('chg1', 1); --開啟遊標，並將引數給出
  --如果確定由表中只有一條記錄的話，loop和end loop可以不寫，而exit必須存在於遊標迴圈內，所以也不需要寫。
  loop
    fetch rowList
      into rowValue; --取出遊標內的值放到rowValue中
    exit when rowList%notfound;--判斷是否還存在記錄，如果不存在終止遊標
    dbms_output.put_line(rowValue.chg_name); --將的到的值列印,如果查詢只有一個欄位這裡只需寫變數名即可。
  end loop;
  close rowList; --關閉遊標
end;
在遊標內進行DML操作例項：
declare
  --宣告遊標變數，並關聯sql
  --如果要在遊標內執行DML操作，必須使用for update。
  --使用for update後你所操作的行或列將會被鎖，如果這時其他人操作就會進入等待狀態。
  --如果你在執行select時記錄被鎖，那麼就會進入等待狀態，
  --為了避免這種情況可以在 for update後使用 nowait，這樣你的結果就可以立即返回，但不建議使用
  --如果被鎖在開啟遊標時會報出ORA-00054資源正忙異常，需捕獲異常進行處理
  --使用of的區別時鎖欄位和鎖行。
  cursor rowList is
    select b.* from chg_test_b b for update of b.chg_name nowait;
  --如果上面的查詢語句只有一個查詢欄位，這裡也可以使用正常的變數宣告方式（v_rowValue varchar2(20);）。
  rowValue chg_test_b%rowtype;
  v_i      number(11);
begin
  v_i := 1;
  open rowList; --開啟遊標
  --如果確定由表中只有一條記錄的話，loop和end loop可以不寫，而exit必須存在於遊標迴圈內，所以也不需要寫。
  loop
    fetch rowList
      into rowValue; --取出遊標內的值放到rowValue中
    exit when rowList%notfound;--判斷是否還存在記錄，如果不存在終止遊標
    dbms_output.put_line(rowValue.chg_name); --將的到的值列印,如果查詢只有一個欄位這裡只需寫變數名即可。
    --執行DML操作
    update chg_test_b b
       set b.chg_name = 'chg' || v_i
     where current of rowList;
    v_i := v_i + 1;
  end loop;
  close rowList; --關閉遊標
  commit;
end;
迴圈遊標例項：
迴圈遊標不需要手動進行開啟和管理操作，全部由oracle進行管理。
declare
  --宣告遊標變數並關聯sql
  cursor rowList is
    select level a from dual connect by level <= 10;
begin
  for rowValue in rowList loop
    --rowValue是每條記錄不需要事先宣告，rowList是集合中的所有記錄
    dbms_output.put_line(rowValue.a);--取出集合中的值進行列印
  end loop;
end;

declare
begin
  --可以將select語句for xx in 內，無論傳參或是巢狀更為方便簡潔。
  for rowValue in (select level a from dual connect by level <=10) loop
     for rv in (select col1 from tab1 where tab1.id = rowValue.a) loop
         dbms_output.put_line(rv.col1 );--將的到的值列印。
     end loop;
  end loop;
end;

declare
begin
  for rowValue in 1..10 loop
    dbms_output.put_line(rowValue);--將的到的值列印,如果查詢只有一個欄位這裡只需寫變數名即可。
  end loop;
end;
REF遊標例項：
declare
  type cus_cur_type is ref cursor return chg_test_b%rowtype; --強型別Ref遊標，查詢的sql必須返回chg_test_b表型別
  --type cus_cur_type is ref cursor; 弱型別Ref遊標，返回型別沒有限制
  rowList cus_cur_type; -- 宣告遊標變數
  rowValue chg_test_b%rowtype; --宣告行變數
begin
  open rowList for --開啟遊標，並關聯sql
    select * from chg_test_b b;
  loop
    fetch rowList
      into rowValue; --按行取出資料
    exit when rowList%notfound;--判斷是否還存在記錄，如果不存在終止遊標
    dbms_output.put_line(rowValue.chg_name);--將的到的值列印,如果查詢只有一個欄位這裡只需寫變數名即可。
  end loop;
  close rowList;--關閉遊標
end;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29734436/viewspace-1247979/，如需轉載，請註明出處，否則將追究法律責任。

相關文章