create or replace packagepkg_query as
typecur_query is ref cursor ;
end pkg_query;

CREATE OR REPLACE PROCEDURE prc_query
(p_tableName in varchar2 , -- 表名
p_strWhere in varchar2 , -- 查詢(xún)條件
p_orderColumn in varchar2 , -- 排序的列
p_orderStyle in varchar2 , -- 排序方式
p_curPage in out Number , -- 當(dāng)前頁(yè)
p_pageSize in out Number , -- 每頁(yè)顯示記錄條數(shù)
p_totalRecordsout Number , -- 總記錄數(shù)
p_totalPagesout Number , -- 總頁(yè)數(shù)www@bitscn@com
v_curoutpkg_query.cur_query) -- 返回的結(jié)果集
IS
v_sql VARCHAR2 ( 1000 ): = '' ; -- sql語(yǔ)句
v_startRecord Number ( 4 ); -- 開(kāi)始顯示的記錄條數(shù)
v_endRecord Number ( 4 ); -- 結(jié)束顯示的記錄條數(shù)
BEGIN
-- 記錄中總記錄條數(shù)
v_sql: = ' SELECTTO_NUMBER(COUNT(*))FROM ' || p_tableName || ' WHERE1=1 ' ;
IF p_strWhere IS NOT NULL or p_strWhere <> ' ' THEN
v_sql: = v_sql || p_strWhere;
END IF ;
EXECUTE IMMEDIATEv_sql INTO p_totalRecords;

-- 驗(yàn)證頁(yè)面記錄大小
IF p_pageSize < 0 THEN
p_pageSize: = 0 ;
END IF ;




-- 根據(jù)頁(yè)大小計(jì)算總頁(yè)數(shù)
IF MOD(p_totalRecords,p_pageSize) = 0 THEN
p_totalPages: = p_totalRecords / p_pageSize;
ELSE
p_totalPages: = p_totalRecords / p_pageSize + 1 ;
END IF ;

-- 驗(yàn)證頁(yè)號(hào)
IF p_curPage < 1 THEN
p_curPage: = 1 ;
END IF ;
IF p_curPage > p_totalPages THEN
p_curPage: = p_totalPages;
END IF ;

-- 實(shí)現(xiàn)分頁(yè)查詢(xún)
v_startRecord: = (p_curPage - 1 ) * p_pageSize + 1 ;
v_endRecord: = p_curPage * p_pageSize;
v_sql: = ' SELECT*FROM(SELECTA.*,rownumrFROM ' ||
' (SELECT*FROM ' || p_tableName;
IF p_strWhere IS NOT NULL or p_strWhere <> ' ' THEN


v_sql: = v_sql || ' WHERE1=1 ' || p_strWhere;
END IF ;
IF p_orderColumn IS NOT NULL or p_orderColumn <> ' ' THEN
v_sql: = v_sql || ' ORDERBY ' || p_orderColumn || ' ' || p_orderStyle;
END IF ;
v_sql: = v_sql || ' )AWHERErownum<= ' || v_endRecord || ' )BWHEREr>= '
|| v_startRecord;
DBMS_OUTPUT.put_line(v_sql);
OPEN v_cur FOR v_sql;
END prc_query;