修改邏輯只需改存儲過程,無需動應用代碼。本文提供一個通用分頁存儲過程,支援動態表名、條件、排序。
create or replace package pkg_query as type cur_query is ref cursor; end pkg_query;
CREATE OR REPLACE PROCEDURE prc_query( p_tableName in varchar2, -- 表名 p_strWhere in varchar2, -- 查詢條件 p_orderColumn in varchar2, -- 排序欄位 p_orderStyle in varchar2, -- 排序方式 p_curPage in out Number, -- 當前頁 p_pageSize in out Number, -- 每頁筆數 p_totalRecords out Number, -- 總筆數 p_totalPages out Number, -- 總頁數 v_cur out pkg_query.cur_query) -- 結果集 IS v_sql VARCHAR2(1000); v_startRecord Number; v_endRecord Number; BEGIN -- 1. 取總筆數 v_sql := 'SELECT COUNT(*) FROM ' || p_tableName || ' WHERE 1=1'; IF p_strWhere IS NOT NULL THEN v_sql := v_sql || p_strWhere; END IF; EXECUTE IMMEDIATE v_sql INTO p_totalRecords; -- 2. 參數驗證與總頁數計算 IF p_pageSize < 1 THEN p_pageSize := 1; END IF; p_totalPages := CEIL(p_totalRecords / p_pageSize); IF p_curPage < 1 THEN p_curPage := 1; END IF; IF p_curPage > p_totalPages THEN p_curPage := p_totalPages; END IF; -- 3. 計算範圍並動態構建分頁查詢 v_startRecord := (p_curPage - 1) * p_pageSize + 1; v_endRecord := p_curPage * p_pageSize; v_sql := 'SELECT * FROM (SELECT A.*, ROWNUM r FROM (SELECT * FROM ' || p_tableName; IF p_strWhere IS NOT NULL THEN v_sql := v_sql || ' WHERE 1=1' || p_strWhere; END IF; IF p_orderColumn IS NOT NULL THEN v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle; END IF; v_sql := v_sql || ') A WHERE ROWNUM <= ' || v_endRecord || ') B WHERE r >= ' || v_startRecord; DBMS_OUTPUT.put_line(v_sql); OPEN v_cur FOR v_sql; END prc_query;
String sql = "{ call prc_query(?,?,?,?,?,?,?,?,?) }";
CallableStatement call = con.prepareCall(sql);
// 設定參數、註冊 OUT CURSOR
call.registerOutParameter(9, OracleTypes.CURSOR);
call.execute();
ResultSet rs = (ResultSet) call.getObject(9);