文章目錄

1 概述
- 1.1 思維導圖
2 文法
- 2.1 基本寫法（4步）
- 2.2 遊标4大屬性
3 分類
- 3.1 靜态遊标
- - 3.1.1 隐式遊标 dml
  - 3.1.2 顯式遊标 cursor
- 3.2 動态遊标
- - 3.2.1 自定義類型 ref cursor
  - 3.2.2 系統類型 sys_refcursor
4 擴充
- 4.1 三種遊标循環效率對比
- 4.2 執行個體：實際開發中，遊标周遊資料

1 概述

1. 遊标是什麼？
   用來存儲多條查詢資料的一種資料結構（'結果集'），
   它有一個 '指針'，從上往下移動（'fetch'），進而能夠 '周遊每條記錄'   
2. 優缺點
   (1) 提高 sql '執行效率'
   (2) 犧牲 '記憶體'

遊标概念圖：

Oracle cursor 遊标詳解1 概述2 文法3 分類4 擴充

1.1 思維導圖

Oracle cursor 遊标詳解1 概述2 文法3 分類4 擴充

2 文法

2.1 基本寫法（4步）

-- 測試基礎資料
create table stu_info (
  id   number(3),
  name varchar2(30),
  sex  varchar2(2)
);
insert into stu_info(id, name, sex) values (1, '小遊子', '女');
insert into stu_info(id, name, sex) values (2, '小優子', '男');
commit;

遊智語法：4 個步驟，擷取學生資訊表（stu_info）的記錄

declare
  -- 1 聲明遊标
  cursor cur_stu_info is
    select * from stu_info;
  v_stu_info cur_stu_info%rowtype;
begin
  -- 2 開啟遊标
  open cur_stu_info;
  -- 3 擷取資料（一次擷取一行）
  fetch cur_stu_info
    into v_stu_info;
  dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);
  -- 4 關閉遊标
  close cur_stu_info;
end;

執行截圖：

1 : 小遊子

2.2 遊标4大屬性

屬性            傳回值類型		作用
sql%isopen      布爾型			判斷遊标是否 '開啟'
sql%found		布爾型			判斷遊标是否 '擷取' 到值
sql%notfound	布爾型		    判斷遊标是否 '沒有擷取' 到值（常用于 "退出循環"）
sql%rowcount	整型			    '目前' 成功執行的資料行數（非 "總記錄數"）

特别說明：sql%notfound

Oracle 官方文檔解釋： Before the first fetch%NOTFOUND returns NULL . If fetch never executes susscessfully. the loop is never exited, because then EXIT WHEN statement executes only if it’s WHEN condition is true. To be safe. you might want to use the following EXIT statement instead: EXIT WHEN SQL%NOTFOUND OR SQL%NOTFOUND IS NULL;

簡單說，退出循環的必要條件：fetch 語句執行成功 + 第一次 fetch 的值傳回 null

-- **************************************************************
-- 功能：循環讀取資料
-- 核心：先後順序 = 先 fetch ... 再 exit when *%notfound
-- ************************************************************** 
declare
  cursor cur_stu_info is
    select * from stu_info t where t.id = 9;
  v_stu_info cur_stu_info%rowtype;
  begin
  open cur_stu_info;
  loop
    fetch cur_stu_info
      into v_stu_info; -- 可測試，這段 fetch 放在 %notfound 後面  
    exit when cur_stu_info%notfound;  
    dbms_output.put_line('該語句不會執行，因為沒有 id = 9 的記錄');  
  end loop;
  close cur_stu_info;
end;

3 分類

3.1 靜态遊标

3.1.1 隐式遊标 dml

1. 自動建立
   (1) DML
   (2) select into

2. 自動管理

(1) 無需人為幹預（自動聲明、打開、關閉）

(2) 預設遊标名：‘SQL’

示範：

declare
  v_count number;
begin
  insert into stu_info (id, name, sex) values (3, '瑤瑤', '女');
  if sql%found then
    dbms_output.put_line('插入成功!');
  end if;
  update stu_info t set t.name = '悠悠' where t.id = 3;
  if sql%found then
    dbms_output.put_line('更新成功!');
  end if;
  delete from stu_info t where t.id = 3;
  if sql%found then
    dbms_output.put_line('删除成功!');
  end if;
  select count(1) into v_count from stu_info t;
  if sql%found then
    dbms_output.put_line('總記錄為： ' || v_count);
  end if;
  if sql%isopen then
    dbms_output.put_line('不可能的，永遠不可能走這一步');
  else
    dbms_output.put_line('系統已自動關閉遊标');
  end if;
end;

測試結果：

插入成功!
更新成功!
删除成功!
總記錄為： 2
系統已自動關閉遊标

3.1.2 顯式遊标 cursor

由關鍵字 cursor 聲明，可帶參數，也可不帶參數

情況1：不帶參數：同上 -> 遊智語法：(4 個步驟)

情況2：帶參數（聲明參數值和類型）：

declare
  v_stu_info stu_info%rowtype;
  -- 步驟1: 聲明遊标
  cursor cur_stu_info(v_id stu_info.id%type) is
    select * from stu_info t where t.id = v_id;
begin
  -- 步驟2: 打開遊标
  open cur_stu_info(1);
  -- 步驟3: 提取資料
  fetch cur_stu_info
    into v_stu_info;
  dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name);
  -- 步驟4: 關閉遊标
  close cur_stu_info;
end;

測試結果（都一樣，因為都是取得 id=1 的記錄）：

1 : 小遊子

3.2 動态遊标

3.2.1 自定義類型 ref cursor

有兩種使用情況
1. 弱類型，無 return（常用）
2. 強類型，有 return（遊标的類型必須和 return 的類型完全一緻）
   (1) '字段數量、字段類型、字段順序' 均完全同 return 一緻
   (2) 此時 open ... for 後隻能用 sql 語句，而不能是 '字元串'

情況1：弱類型，無 return（常用）

declare
  v_sql_select varchar(200);
    v_id         scott.stu_info.id%type;
  v_name       scott.stu_info.name%type;
  type cur_stu_type is ref cursor; -- 無 return
  cur_stu_info cur_stu_type;
begin
  -- 測試
  v_id := 1;
  v_sql_select := 'SELECT t.id, t.name 
                     FROM stu_info t 
                    WHERE t.id <= :b1';
  open cur_stu_info for v_sql_select
    using v_id; -- 綁定變量（大資料處理常用優化手段）
  fetch cur_stu_info
    into v_id, v_name;
  dbms_output.put_line(v_id || ' ：' || v_name);
  close cur_stu_info;
end;

輸出結果：

1 ：小遊子

情況2：強類型，有 return

declare
  v_sql_select    varchar(200);
  v_stu_info_rows scott.stu_info%rowtype;
  type cur_stu_type is ref cursor return scott.stu_info%rowtype; -- 有 return 
  cur_stu_info cur_stu_type;
begin
  -- 測試
    /*  v_id := 1;  
  v_sql_select := 'SELECT t.id, t.name 
                     FROM stu_info t 
                    WHERE t.id <= :b1';*/
                      open cur_stu_info for
    select t.id, t.name, t.sex from stu_info t;
  fetch cur_stu_info
    into v_stu_info_rows;
  dbms_output.put_line(v_stu_info_rows.id || ' ：' || v_stu_info_rows.name);
  close cur_stu_info;
end;

輸出結果：(同弱類型一樣)

1 ：小遊子

3.2.2 系統類型 sys_refcursor

常用，省去了手動定義動态遊标的步驟，以下效果等同：

declare
   -- type cur_stu_type is ref cursor; -- 手動定義動态遊标
   -- cur_stu_info cur_stu_type;   
   -- 聲明動态遊标， 這一個步驟等于上面兩個步驟
   cur_stu_info sys_refcursor;  
begin
end;

4 擴充

4.1 三種遊标循環效率對比

結論：一般來說 '批量處理' 的速度要最好,'隐式遊标' 的次之,'單條處理' 的最差
說明：若有興趣，可以在資料量多的表裡面，分别嘗試下列三種寫法，并列印時間，用作比較
1. 批量處理
   open 遊标;
   loop
      fetch 遊标 bulk collect  
       into 集合變量（也就是 table 類型哦） limit 數值; -- 一般 500 左右       
       exit when 條件; -- cursor.count = 0
       邏輯處理;
    end loop;
    close 遊标;
2. 隐式遊标
   for x in (sql 語句) loop
      邏輯處理;
   end loop;
3. 單條處理
   open  遊标;
   loop
      fetch 遊标 
       into 變量;     
       exit when 條件;
       邏輯處理;
   end loop;
   close 遊标;

4.2 執行個體：實際開發中，遊标周遊資料

-- *************************************************
-- 功能：讀取表 stu_info 資料
-- *************************************************
declare
  v_sql_select varchar(300);
  cur_stu_info sys_refcursor;
  type record_stu_info is record(
    v_id   scott.stu_info.id%type,
    v_name scott.stu_info.name%type);
  type table_stu_info is table of record_stu_info;
  v_stu_info_table table_stu_info;
begin
  v_sql_select := 'SELECT t.id, t.name 
                     FROM stu_info t 
                    WHERE t.id <= :b1';
  open cur_stu_info for v_sql_select
    using 3; -- 綁定變量，此處 3，僅用作測試
  loop
    fetch cur_stu_info bulk collect
      into v_stu_info_table limit 1; -- 資料量太少，僅目前測試使用哦，實際開發 建議 500 左右
    -- 此時 %notfound 不合适，count 适用
    -- 因為 可能找到資料了（found 非空值），但是小于 limit n
    exit when v_stu_info_table.count = 0; -- 退出條件！  
    for i in v_stu_info_table.first .. v_stu_info_table.last loop
      dbms_output.put_line('序号：' || v_stu_info_table(i).v_id || ' , ' ||
                           '姓名：' || v_stu_info_table(i).v_name);
    end loop;
  end loop;  
  close cur_stu_info;
exception
  when others then
    -- 異常時，仍要 關閉遊标
    if cur_stu_info%isopen then
      close cur_stu_info;
    end if;
    dbms_output.put_line(sqlcode || ' : ' || sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;

測試結果：（建議在 ‘測試視窗’ debug 看看執行步驟）

序号：1 , 姓名：小遊子
序号：2 , 姓名：小優子

Oracle cursor 遊标詳解1 概述2 文法3 分類4 擴充

文章目錄

1 概述

1.1 思維導圖

2 文法

2.1 基本寫法（4步）

2.2 遊标4大屬性

3 分類

3.1 靜态遊标

3.1.1 隐式遊标 dml

3.1.2 顯式遊标 cursor

3.2 動态遊标

3.2.1 自定義類型 ref cursor

3.2.2 系統類型 sys_refcursor

4 擴充

4.1 三種遊标循環效率對比

4.2 執行個體：實際開發中，遊标周遊資料

繼續閱讀

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

Oracle選擇重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List