我的存储过程如下:CREATE OR REPLACE PACKAGE PageTest
is
type T_CURSOR is ref cursor;
Procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_TableName in varchar2, --表名
p_SqlSelect in varchar2, --查询语句,含排序部分
p_order in varchar, --含排序部分
p_OutRecordCount out number, --返回总记录数
p_OutCursor out T_CURSOR );
end PageTest;create or replace package body PageTest
is
procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_TableName in varchar2, --表名
p_SqlSelect in varchar2, --查询语句,
p_order in varchar, --含排序部分
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
if p_SqlSelect is not null then
v_sql :='select count(*) from '|| p_TableName ||' '|| p_SqlSelect ||' '|| p_order ;
else
v_sql :='select count(*) from '|| p_TableName||' '|| p_order ;
end if ;
execute immediate v_sql into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;
if p_SqlSelect is not null then
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM '|| p_TableName || ' A '|| ' '|| p_SqlSelect ||'
and rownum <= '|| to_char(v_heiRownum) || ''|| p_order ||'
) B WHERE rn >= ' || to_char(v_lowRownum) ;
else
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM '|| p_TableName || ' A
WHERE rownum <= '|| to_char(v_heiRownum) || ' '|| p_order ||'
) B WHERE rn >= ' || to_char(v_lowRownum) ;
end if;
OPEN p_OutCursor FOR v_sql;
end Per_QuickPage;
end PageTest;
这个存储过程编译可以通过,并且可以取到记录,但是有一个很郁闷的问题,我在.net中使用gridview控件调用这个分页的存储过程,如果可以取到两页的记录,在第一页的时候,这个排序的条件是正确的,但是翻到第二页后,这个排序的条件就不对了,真是郁闷这应该是存储过程的问题吧,,请教各位,改改这个存储过程,得到正确答案,立即结贴!
is
type T_CURSOR is ref cursor;
Procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_TableName in varchar2, --表名
p_SqlSelect in varchar2, --查询语句,含排序部分
p_order in varchar, --含排序部分
p_OutRecordCount out number, --返回总记录数
p_OutCursor out T_CURSOR );
end PageTest;create or replace package body PageTest
is
procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_TableName in varchar2, --表名
p_SqlSelect in varchar2, --查询语句,
p_order in varchar, --含排序部分
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
if p_SqlSelect is not null then
v_sql :='select count(*) from '|| p_TableName ||' '|| p_SqlSelect ||' '|| p_order ;
else
v_sql :='select count(*) from '|| p_TableName||' '|| p_order ;
end if ;
execute immediate v_sql into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;
if p_SqlSelect is not null then
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM '|| p_TableName || ' A '|| ' '|| p_SqlSelect ||'
and rownum <= '|| to_char(v_heiRownum) || ''|| p_order ||'
) B WHERE rn >= ' || to_char(v_lowRownum) ;
else
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM '|| p_TableName || ' A
WHERE rownum <= '|| to_char(v_heiRownum) || ' '|| p_order ||'
) B WHERE rn >= ' || to_char(v_lowRownum) ;
end if;
OPEN p_OutCursor FOR v_sql;
end Per_QuickPage;
end PageTest;
这个存储过程编译可以通过,并且可以取到记录,但是有一个很郁闷的问题,我在.net中使用gridview控件调用这个分页的存储过程,如果可以取到两页的记录,在第一页的时候,这个排序的条件是正确的,但是翻到第二页后,这个排序的条件就不对了,真是郁闷这应该是存储过程的问题吧,,请教各位,改改这个存储过程,得到正确答案,立即结贴!
procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out ResultData)
as
v_sql varchar2(3000);
v_count int;
v_heiRownum int;
v_lowRownum int;
begin
----取记录总数
execute immediate p_SqlCount into v_count;
p_OutRecordCount := v_count;
----执行分页查询
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize +1; v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql; end sp_Page;
FROM (
SELECT A.*, rownum rn
FROM '|| p_TableName || ' A '|| ' '|| p_SqlSelect ||'
and rownum <= '|| to_char(v_heiRownum) || ''|| p_order ||'
) B WHERE rn >= ' || to_char(v_lowRownum) ;
-------------------------------------------------
order by 時rownum有問題
rn<= '|| to_char(v_heiRownum) ->rn< '|| to_char(v_heiRownum)
rn >= ' || to_char(v_lowRownum)
的问题,你重复了=只出现1次
=====================
v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM (select * from '|| p_TableName || ' '|| p_order ||') A '|| ' '|| p_SqlSelect ||'
and rownum <= '|| to_char(v_heiRownum) || '
) B WHERE rn >= ' || to_char(v_lowRownum) ;
然后在rn<
最后rn>
v_sql := 'SELECT *
FROM (
SELECT *
FROM (select A.*, rownum rn from '|| p_TableName || ' '|| p_order ||') A '|| ' '|| p_SqlSelect ||')
where rn <= '|| to_char(v_heiRownum) || '
) B WHERE rn >= ' || to_char(v_lowRownum) ;
套一個表是先排序,再取rownum
参考:http://community.csdn.net/Expert/topic/5573/5573323.xml?temp=.1048395