create or replace procedure 分页查询(pvc_input varchar2,
pi_backcode out integer,
pvc_message out varchar2,
pvc_output out varchar2,
cur_entrusts out sys_refcursor) is
pvar_i_startnum number; --开始行数
pvar_i_onepagenum number; --每页数据量
pvar_i_endnum number; --结束行数
pvar_i_pageindex number; --第几页
pvar_s_tablename varchar2(100) := ' ';
pvar_s_sql varchar2(1024) := ' ';
begin
pvar_s_tablename := nvl(strfieldbyname(pvc_input, 'tablename', ';', '='), ' ');
pvar_s_sql := nvl(strfieldbyname(pvc_input, 'sql', ';', '='), ' ');
pvar_i_onepagenum := to_number(strfieldbyname(pvc_input, 'onepagenum', ';', '='));
pvar_i_pageindex := to_number(strfieldbyname(pvc_input, 'pageindex', ';', '='));
pvar_i_startnum := pvar_i_onepagenum * pvar_i_pageindex + 1;
pvar_i_endnum := pvar_i_startnum + pvar_i_onepagenum;
if cur_entrusts%isopen then
close cur_entrusts;
end if;
pi_backcode := 0;
pvc_message := 'OK';
--显示全部
if pvar_s_tablename <> ' 'then
open cur_entrusts for
'select * from (select a.*, rownum rn from (select * from ' || pvar_s_tablename || ') a where rownum <= ' || to_char(pvar_i_endnum) || ') where rn >=' || to_char(pvar_i_startnum + 1);
elsif pvar_s_sql <> ' ' then
open cur_entrusts for
'select * from (select a.*, rownum rn from (' || pvar_s_sql || ') a where rownum <= ' || to_char(pvar_i_endnum) || ') where rn >=' || to_char(pvar_i_startnum + 1);
end if;
end 分页查询;
传入表或SQL语句的分页查询存储过程
最新推荐文章于 2023-11-10 08:22:10 发布