Oracle 游標與全域暫存表實戰指南

Oracle 游標與全域暫存表實戰指南

本文透過完整範例示範如何建立會話級暫存表、主表資料準備、使用顯式游標讀取資料並寫入暫存表，最後驗證結果。並詳細解析暫存表兩種類型差異、游標分類、屬性與標準操作流程。

1. 建立會話級全域暫存表

CREATE GLOBAL TEMPORARY TABLE pact_test (
  pact_id   VARCHAR2(100),
  pact_code VARCHAR2(100),
  pact_name VARCHAR2(800)
) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS 表示資料在 Session 結束前保留，Commit 不會清除。

2. 建立主表並插入測試資料

CREATE TABLE PACT_BASEINFO (
  pact_id   VARCHAR2(30) NOT NULL,
  pact_code VARCHAR2(150) NOT NULL,
  pact_name VARCHAR2(1000) NOT NULL
);

INSERT INTO pact_test (PACT_ID, PACT_CODE, PACT_NAME) VALUES ('2014', 'ZD0ZB', '天翼電信');
INSERT INTO pact_test (PACT_ID, PACT_CODE, PACT_NAME) VALUES ('2014', 'HISGS', '海南電信');
INSERT INTO pact_test (PACT_ID, PACT_CODE, PACT_NAME) VALUES ('2014', 'JSSGS', '江蘇電信');
INSERT INTO pact_test (PACT_ID, PACT_CODE, PACT_NAME) VALUES ('2014', 'CU12-', '中國聯通甘肅業務');
INSERT INTO pact_test (PACT_ID, PACT_CODE, PACT_NAME) VALUES ('2014', 'YNSGS', '中國電信雲南公司');

3. 使用顯式游標查詢並寫入暫存表

DECLARE
  v_cnt NUMBER := 5;
  v_pact_id   pact_test.pact_id%TYPE;
  v_pact_code pact_test.pact_code%TYPE;
  v_pact_name pact_test.pact_name%TYPE;
  CURSOR c_pact IS
    SELECT pact_id, pact_code, pact_name
    FROM (
      SELECT ROWNUM rn, pact_id, pact_code, pact_name
      FROM pact_baseinfo
    ) a
    WHERE a.rn < v_cnt;
BEGIN
  OPEN c_pact;
  LOOP
    FETCH c_pact INTO v_pact_id, v_pact_code, v_pact_name;
    EXIT WHEN c_pact%NOTFOUND;
    INSERT INTO pact_test (pact_id, pact_code, pact_name)
    VALUES (v_pact_id, v_pact_code, v_pact_name);
  END LOOP;
  CLOSE c_pact;
END;

4. 驗證暫存表資料

SELECT * FROM pact_test;

5. 核心知識總結

5.1 全域暫存表兩種模式

本範例使用會話級暫存表。交易級建立語法：

CREATE GLOBAL TEMPORARY TABLE pact_test (
  pact_id   VARCHAR2(100),
  pact_code VARCHAR2(100),
  pact_name VARCHAR2(800)
) ON COMMIT DELETE ROWS;

5.2 游標分類

• 隱式游標：執行 INSERT/UPDATE/DELETE/SELECT INTO 時由 PL/SQL 自動建立，不可控制，只能讀取屬性（%ROWCOUNT, %FOUND 等）。
• 顯式游標：用 CURSOR name IS SELECT ... 定義，可處理多行結果集。分為：
  • 靜態游標：普通游標、參數游標（編譯時確定查詢）
  • 動態游標：游標變數（REF CURSOR，執行時動態綁定查詢）

5.3 顯式游標標準操作四步驟

1. 定義：CURSOR cursor_name IS select_sql;（關鍵字必須用 IS，不可用 AS）
2. 開啟：OPEN cursor_name;
3. 提取：
   • 單行：FETCH cursor_name INTO var1[, var2...]; 需搭配迴圈批量處理
   • 批量：FETCH cursor_name BULK COLLECT INTO coll1, coll2... [LIMIT n];（9i+ 支援，較耗記憶體）
4. 關閉：CLOSE cursor_name;

5.4 顯式游標四大屬性

掌握暫存表與游標的正確用法，能有效解決複雜報表、ETL 中繼資料暫存、分批處理大量資料等場景問題。