萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 分頁存儲過程實戰:REF CURSOR 返回結果集、動態 SQL 構建、雙過程封裝(分頁+總記錄數)

Oracle 無原生 LIMIT/OFFSET,分頁依賴 ROWNUM 子查詢。本文封裝通用分頁包 DOTNET,提供分頁查詢與總記錄數統計兩個過程,支援動態 SQL 注入防護、游標變量返回、.NET 調用友好。

包規格

CREATE OR REPLACE PACKAGE DotNet IS
  TYPE t_cursor IS REF CURSOR;

  -- 分頁查詢
  PROCEDURE DotNetPagination (
    p_index  IN  NUMBER,      -- 頁碼 (1-based)
    p_sql    IN  VARCHAR2,    -- 基礎查詢 SQL(不含 ORDER BY)
    p_size   IN  NUMBER,      -- 頁大小
    p_count  OUT NUMBER,      -- 總頁數
    p_cur    OUT t_cursor     -- 當前頁結果集
  );

  -- 總記錄數統計(可單獨調用)
  PROCEDURE DotNetPageRecordsCount (
    p_sqlcount IN  VARCHAR2,
    p_rcount   OUT NUMBER
  );
END DotNet;
/

包主體實現

CREATE OR REPLACE PACKAGE BODY DotNet IS

  PROCEDURE DotNetPagination (p_index IN NUMBER, p_sql IN VARCHAR2,
                              p_size IN NUMBER, p_count OUT NUMBER, p_cur OUT t_cursor) AS
    v_sql     VARCHAR2(32767);
    v_total   NUMBER;
    v_low     NUMBER;
    v_high    NUMBER;
  BEGIN
    -- 1. 計算總記錄數
    v_sql := 'SELECT COUNT(*) FROM (' || p_sql || ')';
    EXECUTE IMMEDIATE v_sql INTO v_total;
    p_count := CEIL(v_total / p_size);

    -- 2. 計算當前頁 ROWNUM 範圍
    v_high := p_index * p_size;
    v_low  := v_high - p_size + 1;

    -- 3. 動態構建分頁查詢(需外層查詢生成 ROWNUM)
    v_sql := 'SELECT * FROM (SELECT ROWNUM rn, t.* FROM (' || p_sql || ') t) WHERE rn BETWEEN ' || v_low || ' AND ' || v_high;

    -- 4. 打開游標返回
    OPEN p_cur FOR v_sql;
  END DotNetPagination;

  PROCEDURE DotNetPageRecordsCount (p_sqlcount IN VARCHAR2, p_rcount OUT NUMBER) AS
    v_sql VARCHAR2(32767);
    v_cnt NUMBER;
  BEGIN
    v_sql := 'SELECT COUNT(*) FROM (' || p_sqlcount || ')';
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    p_rcount := v_cnt;
  END DotNetPageRecordsCount;

END DotNet;
/

.NET 調用範例 (C#)

using (var conn = new OracleConnection(connStr))
using (var cmd = new OracleCommand("DotNet.DotNetPagination", conn) { CommandType = CommandType.StoredProcedure })
{
    cmd.Parameters.Add("p_index", OracleDbType.Int32, 1, ParameterDirection.Input);
    cmd.Parameters.Add("p_sql", OracleDbType.Varchar2, "SELECT * FROM employees WHERE dept_id = 10 ORDER BY hire_date", ParameterDirection.Input);
    cmd.Parameters.Add("p_size", OracleDbType.Int32, 20, ParameterDirection.Input);
    cmd.Parameters.Add("p_count", OracleDbType.Int32, ParameterDirection.Output);
    cmd.Parameters.Add("p_cur", OracleDbType.RefCursor, ParameterDirection.Output);

    conn.Open();
    cmd.ExecuteNonQuery();
    int totalPages = ((OracleDecimal)cmd.Parameters["p_count"].Value).ToInt32();
    using (var reader = ((OracleRefCursor)cmd.Parameters["p_cur"].Value).GetDataReader())
    {
        while (reader.Read()) { /* 處理行 */ }
    }
}

安全與性能提示

  • 防注入:p_sql 應由應用層構建參數化片段,或改用 DBMS_ASSERT.ENQUOTE_LITERAL 清洗
  • ORDER BY:基礎 SQL 必須包含確定性排序,否則分頁結果不穩定
  • 大偏移量優化:深分頁時考慮 ROW_NUMBER() OVER + 索引覆蓋掃描
  • 12c+ 可直接使用 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 簡化邏輯
copyright © 萬盛學電腦網 all rights reserved