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;
/
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 清洗ROW_NUMBER() OVER + 索引覆蓋掃描OFFSET ... ROWS FETCH NEXT ... ROWS ONLY 簡化邏輯