Oracle 預存程序中 SELECT 語法規範：INTO 變數、游標兩大模式完整指南

在 PL/SQL 區塊（預存程序、函數、觸發器、匿名區塊）中，單獨的 SELECT 語句是非法的，會編譯錯誤 PLS-00428。必須採用 SELECT INTO（單筆）或 游標（多筆/結果集）兩種模式。本文系統整理兩類游標寫法與實務範本。

核心規則

• SELECT ... INTO var1, var2 ...：查詢結果必須恰好 1 筆，否則拋出 TOO_MANY_ROWS 或 NO_DATA_FOUND。
• 需處理多筆或回傳結果集給呼叫端 → 必須使用游標。

模式一：隱性游標 SELECT INTO（單筆）

CREATE OR REPLACE PROCEDURE get_emp_name (
  p_empno IN  NUMBER,
  p_ename OUT VARCHAR2
) IS
BEGIN
  SELECT ename INTO p_ename FROM emp WHERE empno = p_empno;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_ename := 'NOT FOUND';
  WHEN TOO_MANY_ROWS THEN
    p_ename := 'DUPLICATE';
END;

模式二：顯性游標（EXPLICIT CURSOR）

2.1 宣告區定義、BEGIN 區開啟/迴圈/關閉

CREATE OR REPLACE PROCEDURE list_dept_emps (p_deptno IN NUMBER) IS
  CURSOR c_emps IS
    SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
BEGIN
  OPEN c_emps;
  LOOP
    FETCH c_emps INTO v_empno, v_ename, v_sal;
    EXIT WHEN c_emps%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename || ' ' || v_sal);
  END LOOP;
  CLOSE c_emps;
END;

2.2 FOR 迴圈隱式開啟/關閉（推薦，簡潔）

CREATE OR REPLACE PROCEDURE list_dept_emps (p_deptno IN NUMBER) IS
BEGIN
  FOR r IN (SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno) LOOP
    DBMS_OUTPUT.PUT_LINE(r.empno || ' ' || r.ename || ' ' || r.sal);
  END LOOP;
END;

模式三：REF CURSOR 回傳結果集給呼叫端（Java/.NET/報表工具）

3.1 弱型別 SYS_REFCURSOR（通用）

CREATE OR REPLACE PROCEDURE get_emps_rc (
  p_deptno IN NUMBER,
  p_rc     OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_rc FOR
    SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;
END;

3.2 強型別 REF CURSOR（編譯期檢查）

CREATE OR REPLACE PACKAGE pkg_types IS
  TYPE t_emp_rc IS REF CURSOR RETURN emp%ROWTYPE;
END pkg_types;

CREATE OR REPLACE PROCEDURE get_emps_strong (
  p_deptno IN NUMBER,
  p_rc     OUT pkg_types.t_emp_rc
) IS
BEGIN
  OPEN p_rc FOR SELECT * FROM emp WHERE deptno = p_deptno;
END;

呼叫端取用 REF CURSOR 範例

• SQL*Plus：VAR rc REFCURSOR; EXEC get_emps_rc(10, :rc); PRINT rc;
• Java (JDBC)：CallableStatement cs = conn.prepareCall("{call get_emps_rc(?, ?)}"); cs.setInt(1,10); cs.registerOutParameter(2, OracleTypes.CURSOR); cs.execute(); ResultSet rs = (ResultSet) cs.getObject(2);
• .NET (ODP.NET)：OracleCommand cmd = new OracleCommand("get_emps_rc", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("p_deptno", 10); cmd.Parameters.Add("p_rc", OracleDbType.RefCursor, ParameterDirection.Output); OracleDataReader dr = cmd.ExecuteReader();

常見錯誤與修正

效能提示

• 大量資料用 BULK COLLECT ... LIMIT 1000 分批取回，減少 Context Switch。
• REF CURSOR 避免在迴圈中重複 OPEN，造成游標洩漏。
• 靜態 SQL 優於動態 SQL（EXECUTE IMMEDIATE），優化器可預先解析。

遵循「單筆用 INTO、多筆用 FOR 迴圈、跨語言回傳用 REF CURSOR」三大模式，即能撰寫編譯通過、效能穩健、易於維護的 PL/SQL 程式單元。