Oracle分页函数样例——用于提高当前框架分页性能
create or replace procedure P_QuerySplit( sqlscript varchar2, --表名/SQL语句 pageSize integer, --每页记录数 pageIndex integer, --当前页 totalCount out number, --总记录数 totalPage out number, --总页数 v_cur out sys_refcursor --返回游标 ) is /** * * */ v_PageSize number; v_PageIndex number; v_SQL_Count varchar2(4000); v_SQL varchar2(4000); v_StartIndex number; v_EndIndex number; begin v_PageSize:=pageSize; if v_PageSize=0 then v_PageSize:=1; end if; --统计记录数量 v_SQL_Count := 'select count(*) from (' ? ? sqlscript ? ?') a '; execute immediate v_SQL_Count into totalCount; --计算总页数 totalPage:=CEIL(totalCount/v_PageSize); --验证页号 如果页号大余了最大页数，返回最后一页 v_PageIndex:=pageIndex; if v_PageIndex>totalPage then v_PageIndex:=totalPage; end if; --计算开始的Index和结束的Index v_StartIndex:=(v_PageIndex-1)*v_PageSize 1; v_EndIndex:=v_PageIndex*v_PageSize; v_SQL:='SELECT /* FIRST_ROWS */* FROM ('; v_SQL:=v_SQL ? ?' SELECT A.*, ROWNUM RN '; v_SQL:=v_SQL ? ?' FROM (' ? ?sqlscript ? ?') A '; v_SQL:=v_SQL ? ?' WHERE ROWNUM <= ' ? ?v_EndIndex; v_SQL:=v_SQL ? ?')WHERE RN >= ' ? ?v_StartIndex; open v_cur for v_SQL; end P_QuerySplit; 带排序的分页存储过程 CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size int, --the size of a page of list v_current_page int, --the current page of list v_table_name varchar2, --the talbe name v_order_field varchar2,--the order field v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank. --v_sql_select varchar2, --the select sql for procedure --v_sql_count varchar2, --the count sql for procedure --v_out_recordcount OUT int, --the num of return rows p_cursor OUT refcursor_pkg.return_cursor) as v_sql varchar2(3000); --the sql for select all rows of list v_sql_count varchar2(3000); --the count sql for procedure v_sql_order varchar2(2000); --the order of list v_count int; -- the amount rows fo original list v_endrownum int; --the end row num of the current page v_startrownum int; --the start row num of the current page BEGIN ----set the order of list if v_order_field!='NO' then v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence; else v_sql_order :=''; end if; ----catch the amount rows of list v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name; execute immediate v_sql_count into v_count; -- v_out_recordcount := v_count; ----set the value of start and end row 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; ----the sql for page slide v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' 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;