支援任意 ORDER BY 欄位與排序方向 (ASC/DESC) 的通用分頁程序,採用 ROWNUM 雙層嵌套寫法。
CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT (
v_page_size IN INT,
v_current_page IN INT,
v_table_name IN VARCHAR2,
v_order_field IN VARCHAR2, -- 'col1' 或 'col1, col2','NO' 表示不排序
v_order_sequence IN VARCHAR2, -- 'ASC' 或 'DESC'
p_cursor OUT SYS_REFCURSOR
) AS
v_sql VARCHAR2(3000);
v_sql_count VARCHAR2(3000);
v_sql_order VARCHAR2(2000);
v_count INT;
v_endrownum INT;
v_startrownum INT;
BEGIN
-- 1. 動態 ORDER BY 子句
IF v_order_field != 'NO' THEN
v_sql_order := ' ORDER BY ' || v_order_field || ' ' || v_order_sequence;
ELSE
v_sql_order := '';
END IF;
-- 2. 取得總筆數
v_sql_count := 'SELECT COUNT(*) FROM ' || v_table_name;
EXECUTE IMMEDIATE v_sql_count INTO v_count;
-- 3. 計算分頁範圍 (注意 DESC 時行號反向)
IF v_order_sequence = 'DESC' THEN
v_endrownum := v_count - (v_current_page - 1) * v_page_size;
v_startrownum := v_endrownum - v_page_size + 1;
ELSE
v_endrownum := v_current_page * v_page_size;
v_startrownum := v_endrownum - v_page_size + 1;
END IF;
-- 4. 組裝分頁查詢 (內層截取 ROWNUM <= end,外層過濾 RN >= start)
v_sql := 'SELECT * FROM (SELECT t.*, ROWNUM rn FROM ' || v_table_name ||
' t WHERE ROWNUM <= ' || TO_CHAR(v_endrownum) || v_sql_order || ') ' ||
'WHERE rn >= ' || TO_CHAR(v_startrownum) || v_sql_order;
OPEN p_cursor FOR v_sql;
END TABLEPAGE_SELECT;
VAR rc REFCURSOR EXEC TABLEPAGE_SELECT(10, 2, 'EMP', 'ENAME', 'ASC', :rc); PRINT rc
v_table_name、v_order_field 合法性。ROW_NUMBER() OVER (ORDER BY ...) (10g+) 或 OFFSET ... FETCH (12c+) 寫法,效能更穩定。v_order_field 可傳入多欄位如 'deptno, hiredate'。