ORACLE paging stored procedure and usage

Create or replace package body curspkg is procedure sp_page (p_pagesize in out int, -- number of records per page p_curpageno in out int, -- current page number p_sqlselect varchar2, -- query statement p_totalpages out int, -- total page p_totalrecords out int, -- total number of records p_outcursor out refcursortype) is v_countsql varchar2 (1000); v_startrownum int; v_endrownum int; v_ SQL varchar2 (4000); begin v_countsql: = 'select to_char (count (*) from ('| p_sqlselect |') '; -- obtain the total number of records execute immediate v_countsql into p_totalrecords; -- The total number of records is assigned to p_totalrecords if p_pagesize <0 then p_pagesize: = 10; end if; If Mod (p_totalrecords, p_pagesize) = 0 then -- Mod (N1, N2 ): returns the remainder of N1 divided by N2 p_totalpages: = p_totalrecords/p_pagesize; else p_totalpages: = floor (p_totalrecords/p_pagesize) + 1; -- floor, returns an integer end if for the given number; if rows <1 then rows: = 1; end if; If p_curpageno> p_totalpages then p_curpageno: = p_totalpages; end if; v_startrownum: = p_curpageno * p_pagesize; -- execute paging query v_endrownum: = (p_curPageNo-1) * p_pagesize + 1; v_ SQL: = 'select * from (select rownum RN,. * From ('| p_sqlselect |') A where rownum <= '| to_char (v_startrownum) |') B where rn> = '| to_char (v_endrownum ); open p_outcursor for v_ SQL; -- rownum is the number of rows sequentially allocated from the query returned by the Oracle System: End sp_page; end curspkg;

 PROCEDURE P_jwt_jyxx_list(in_ssdw in VARCHAR2,in_jyid in VARCHAR2,in_jsid in VARCHAR2,in_sfqy in VARCHAR2,in_jyxm in VARCHAR2,in_jyjh in VARCHAR2,p_pageSize   IN OUT  INT,p_curPageNo IN OUT  INT,p_totalPages  OUT INT,p_totalRecords OUT INT,p_outCursor  OUT refCursorType,OUT_RETURN_CODE    OUT VARCHAR2) as   v_sql VARCHAR2(2000);   v_where VARCHAR2(1000);   begin           v_where:=' 1=1';            ----alter by xinlingmen 20111104           if in_ssdw is not null then                if in_ssdw <> '41' then                   if substr(in_ssdw,1,6)='41000000' then                     v_where :=v_where||' and a.ssdw like '''||substr(in_ssdw,1,6)||'%''';                     else                      v_where :=v_where||' and a.ssdw like '''||f_right_zero(in_ssdw)||'%''';                      end if;               end if;             end if;            if in_jsid is not null then               v_where :=v_where||' and a.jsid='||in_jsid;             end if;            if in_jyid is not null then               v_where :=v_where||' and a.jyid='||in_jyid;             end if;             if in_sfqy is not null then               v_where :=v_where||' and a.sfqy='''||in_sfqy||'''';             end if;             if in_jyxm is not null then               v_where :=v_where||' and a.jyxm like ''%'||in_jyxm||'%''';             end if;             if in_jyjh is not null then               v_where :=v_where||' and a.jh = '''||in_jyjh||'''';             end if;             v_sql:='select a.*,b.jsmc,c.dwmc from jwt_jyxx a left outer join jwt_jsxx b on a.jsid = b.jsid left outer join t_dwxx c on a.ssdw=c.dwid where'||v_where;             ---dbms_output.put_line(v_sql);             curspkg.sp_page(p_pageSize,p_curPageNo,v_sql,p_totalPages,                          p_totalRecords,p_outCursor);           OUT_RETURN_CODE:='0';           EXCEPTION                  WHEN OTHERS THEN                   ROLLBACK;                   OUT_RETURN_CODE := '1045';                   RETURN;     end;