之前中国联通项目写的一个分页存储过程,楼主可以参考下!create or replace procedure p_cms_batchload_err_query ( /** * PURPOSE : 浏览批量加载内容的错误信息 * REVISION: Version Date Author Description -------- -------- ---------- ------------------- cp001 20081110 hdb 1. 创建存储过程 */ i_beginrownum in number := 1, --开始行号 i_endrownum in number := 50, --结束行号 cur_result out sys_refcursor, --结果集 i_result out number --结果:0-失败;1-成功 ) is l_i_beginrownum number := nvl(i_beginrownum, 1); l_i_endrownum number := nvl(i_endrownum, 50); begin i_result := 0; open cur_result for select filename, operator, operatetime, errormsg, spid, contenttype, contentid, contentinfo, fileinfo from (select filename, operator, operatetime, errormsg, spid, contenttype, contentid, contentinfo, fileinfo, rownum rn from (select row_number() over(partition by filename, operator, contenttype order by contenttype asc, operatetime desc) n, filename, operator, operatetime, errormsg, spid, contenttype, contentid, contentinfo, fileinfo from t_cms_batchload_err order by operatetime desc ) where n = 1 and rownum <= l_i_endrownum) where rn >= l_i_beginrownum; end if; i_result := 1; end; /
/**
* PURPOSE : 浏览批量加载内容的错误信息
* REVISION:
Version Date Author Description
-------- -------- ---------- -------------------
cp001 20081110 hdb 1. 创建存储过程
*/
i_beginrownum in number := 1, --开始行号
i_endrownum in number := 50, --结束行号
cur_result out sys_refcursor, --结果集
i_result out number --结果:0-失败;1-成功
) is l_i_beginrownum number := nvl(i_beginrownum, 1);
l_i_endrownum number := nvl(i_endrownum, 50);
begin i_result := 0; open cur_result for
select filename, operator, operatetime, errormsg, spid,
contenttype, contentid, contentinfo, fileinfo
from (select filename, operator, operatetime, errormsg, spid,
contenttype, contentid, contentinfo, fileinfo, rownum rn
from (select row_number() over(partition by filename, operator, contenttype order by contenttype asc, operatetime desc) n,
filename, operator, operatetime, errormsg, spid,
contenttype, contentid, contentinfo, fileinfo
from t_cms_batchload_err
order by operatetime desc
)
where n = 1 and rownum <= l_i_endrownum)
where rn >= l_i_beginrownum; end if; i_result := 1;
end;
/