Oracle游標實戰精要：從SELECT INTO到DML操作全解析

核心概念

在PL/SQL程式設計中，游標是處理多行查詢結果的核心機制。本文系統梳理隱式與顯式游標的操作差異、%TYPE屬性應用、DML語句整合及游標屬性監控等實戰技巧。

一、SELECT INTO基礎語法與限制

PL/SQL中的SELECT INTO僅支援單行查詢，語法結構如下：

SELECT [DISTINCT|ALL] {*|column[,column,...]}
INTO {variable[,variable,...] | record}
FROM {table|(sub-query)}[alias]
WHERE condition;

關鍵限制：若查詢返回超過一行，必須改用顯式游標；INTO子句變量數量、類型必須與SELECT列表完全對應。

二、%TYPE屬性：動態型別綁定的最佳實踐

透過%TYPE屬性，變數可自動繼承資料庫欄位或其他變數的資料型別與長度，實現結構變更時的零代碼維護。

應用場景示例

-- 繼承資料表欄位型別
v_empno  SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;

-- 繼承變數型別（連鎖傳遞）
DECLARE
  v_a NUMBER(5) := 10;
  v_b v_a%TYPE := 15;
  v_c v_a%TYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('v_a='||v_a||' v_b='||v_b||' v_c='||v_c);
END;

此機制極大降低結構變更帶來的維護成本，建議所有變數宣告優先採用%TYPE。

三、DML語句與游標屬性監控

INSERT、UPDATE、DELETE、LOCK TABLE在PL/SQL中語法與SQL一致，可直接引用DECLARE區塊宣告的變數。

實戰範例：員工離職處理程序

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (p_empno IN NUMBER) AS
  v_ename EMP.ENAME%TYPE;
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno = p_empno;
  INSERT INTO former_emp(empno, ename) VALUES (p_empno, v_ename);
  DELETE FROM emp WHERE empno = p_empno;
  UPDATE former_emp SET date_deleted = SYSDATE WHERE empno = p_empno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee Number Not Found!');
END;

隱式游標四大屬性對照表

四、效能優化建議

• 批次處理優先使用BULK COLLECT與FORALL減少上下文切換
• 大量資料操作時適度調整PGA_AGGREGATE_TARGET參數
• 長時間執行的DML建議分批次提交，避免UNDO表空間耗盡

總結

熟練掌握游標機制、%TYPE動態綁定、DML異常處理與屬性監控，是編寫健壯高效PL/SQL程式的基石。實務中建議建立程式碼審查清單，強制要求異常處理區塊與游標屬性檢查。