oracle 存储过程分页
--------------------包头--------------------------- create or replace package pages is type type_cur is ref cursor; procedure p_pagintion(psql in varchar2, pfrist in number, psize in number, pcount out number, pnotecount out number, pnote out type_cur); END; --------------------包体-------------------- create or replace package body pages is procedure p_pagintion(psql in varchar2, pfrist in number, psize in number, pcount out number, pnotecount out number, pnote out type_cur) AS v_pfrist number; v_sql varchar2(100); v_sql1 varchar2(100); v_sql2 varchar2(100); v_sql3 varchar2(1000); v_notecount number; v_min number; v_max number; BEGIN v_sql := 'select count(*) from (' || Psql || ')'; execute immediate v_sql into v_notecount; pnotecount := v_notecount; pcount := ceil(pnotecount/psize); v_pfrist := pfrist; IF (v_pfrist > pcount) THEN v_pfrist := pcount; end IF; v_max := v_pfrist * psize; v_min := v_max - psize + 1; v_sql1 := 'select * from (select rownum rn,t.* from '; v_sql2 := ' t ) where rn between ' || v_min || ' and ' || v_max; v_sql3 := v_sql1 || ' ( ' || psql || ' ) ' || v_sql2; open pnote for v_sql3; END p_pagintion; END pages;