FrashBird 跪求大侠将oracle存储过程改成sql2005的，谢谢

Question

如题，我有一个oracle的分页存储过程，现在数据库换成sql2005的了，小弟不才，对数据库不了解。所以请大家帮忙修改一下。多谢！！！！不多说，贴代码~create or replace procedure sp_WebPager(p_PageSize int,          --每页记录数
                  p_PageNo int,            --当前页码,从 1 开始
                  p_SqlSelect varchar,    --查询语句,含排序部分
                  p_SqlSelectCount varchar,   --取记录总个数的SQL，如果空不取
                  p_OutRecordCount out int,--返回总记录数
                  p_OutCursor out sys_refcursor)
as
    v_sql varchar(3000);
    v_count int;
    v_pageNo int;
    v_heiRownum int;
    v_lowRownum int;
begin
 v_count := 0;
 v_pageNo := p_PageNo;  ----取记录总数
  if p_SqlSelectCount > ' ' then
     v_sql := p_SqlSelectCount;
     execute immediate v_sql into v_count;     --计算当前页码
     if(p_PageNo > 1 and ((p_PageNo - 1) * p_PageSize + 1) > v_count) then
        v_pageNo := ceil(v_count / p_PageSize);
     end if;
  end if;
  p_OutRecordCount := v_count;  ----执行分页查询
  v_heiRownum := v_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_WebPager;

Answer 1

这个?分页的在论坛到是一大堆,不知道是否适合你?http://topic.csdn.net/u/20100203/17/8F916471-597D-481A-B170-83BCEFE3B199.html应一个朋友的要求，贴上收藏的SQL常用分页的办法～～ 表中主键必须为标识列，[ID] int IDENTITY (1,1) 1.分页方案一：(利用Not In和SELECT TOP分页) 语句形式：  
SELECT TOP 页记录数量 * 
FROM 表名 
WHERE (ID NOT IN 
  (SELECT TOP (每页行数*(页数-1)) ID 
  FROM 表名 
  ORDER BY ID)) 
  ORDER BY ID 
//自己还可以加上一些查询条件 
例: 
select top 2 * 
from Sys_Material_Type 
where (MT_ID not in 
    (select top (2*(3-1)) MT_ID from Sys_Material_Type  order by MT_ID)) 
order by MT_ID 2.分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式： 
SELECT TOP 每页记录数量 * 
FROM 表名 
WHERE (ID > 
          (SELECT MAX(id) 
    FROM (SELECT TOP 每页行数*页数 id  FROM 表 
          ORDER BY id) AS T) 
      ) 
ORDER BY ID 例: 
SELECT TOP 2 * 
FROM Sys_Material_Type 
WHERE (MT_ID > 
          (SELECT MAX(MT_ID) 
          FROM (SELECT TOP (2*(3-1)) MT_ID 
                FROM Sys_Material_Type 
                ORDER BY MT_ID) AS T)) 
ORDER BY MT_ID 3.分页方案三：(利用SQL的游标存储过程分页) 
create  procedure SqlPager 
@sqlstr nvarchar(4000), --查询字符串 
@currentpage int, --第N页 
@pagesize int --每页行数 
as 
set nocount on 
declare @P1 int, --P1是游标的id 
@rowcount int 
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output 
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1 
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1 
set nocount off 4.总结: 
其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 
建议优化的时候，加上主键和索引，查询效率会提高。 通过SQL 查询分析器，显示比较：我的结论是: 
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 
分页方案一：(利用Not In和SELECT TOP分页)  效率次之，需要拼接SQL语句 
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用 

Answer 2

---可以再把变变量重新命名一下
create procedure sp_WebPager(
                p_PageSize int,          --每页记录数
                  p_PageNo int,            --当前页码,从 1 开始
                  p_SqlSelect varchar,    --查询语句,含排序部分
                  p_SqlSelectCount varchar,   --取记录总个数的SQL，如果空不取
                  p_OutRecordCount out int,--返回总记录数
                  p_OutCursor out sys_refcursor)
as
    v_sql varchar(3000);
    v_count int;
    v_pageNo int;
    v_heiRownum int;
    v_lowRownum int;
begin
 set v_count = 0;
 set v_pageNo = p_PageNo;  ----取记录总数
  if p_SqlSelectCount > ' ' then
   set   v_sql = p_SqlSelectCount;
     execute immediate v_sql into v_count;     --计算当前页码
     if(p_PageNo > 1 and ((p_PageNo - 1) * p_PageSize + 1) > v_count) then
       set  v_pageNo = ceil(v_count / p_PageSize);
     end if;
  end if;
  set p_OutRecordCount = v_count;  ----执行分页查询
 set  v_heiRownum = v_pageNo * p_PageSize;
 set  v_lowRownum = v_heiRownum - p_PageSize + 1;  sety v_sql = 'SELECT *
            FROM (
                  SELECT A.*, rownum rn
                  FROM  ('p_SqlSelect') A
                  WHERE rownum <= 'convert(varchar(10),v_heiRownum) '
                 ) B
            WHERE rn >= ' convert(varchar(10),v_lowRownum)' ;
            --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn  OPEN p_OutCursor FOR  v_sql;end ;

Answer 3

---变量重新命名一下，上边有点问题
create procedure sp_WebPager(
                p_PageSize int,          --每页记录数
                  p_PageNo int,            --当前页码,从 1 开始
                  p_SqlSelect varchar,    --查询语句,含排序部分
                  p_SqlSelectCount varchar,   --取记录总个数的SQL，如果空不取
                  p_OutRecordCount out int,--返回总记录数
                  p_OutCursor out sys_refcursor)
as
    v_sql varchar(3000);
    v_count int;
    v_pageNo int;
    v_heiRownum int;
    v_lowRownum int;
begin
 set v_count = 0;
 set v_pageNo = p_PageNo;  ----取记录总数
  if p_SqlSelectCount > ' ' then
   set   v_sql = p_SqlSelectCount;
     execute immediate v_sql into v_count;     --计算当前页码
     if(p_PageNo > 1 and ((p_PageNo - 1) * p_PageSize + 1) > v_count) then
       set  v_pageNo = ceil(v_count / p_PageSize);
     end if;
  end if;
  set p_OutRecordCount = v_count;  ----执行分页查询
 set  v_heiRownum = v_pageNo * p_PageSize;
 set  v_lowRownum = v_heiRownum - p_PageSize + 1;  set v_sql = 'SELECT *
            FROM (
                  SELECT A.*, rownum rn
                  FROM  ('p_SqlSelect') A
                  WHERE rownum <= 'convert(varchar(10),v_heiRownum) '
                 ) B
            WHERE rn >= ' convert(varchar(10),v_lowRownum)' ;
            --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn  OPEN p_OutCursor FOR  v_sql;end ;

Answer 4

create procedure sp_WebPager(
                p_PageSize int,          --每页记录数
                  p_PageNo int,            --当前页码,从 1 开始
                  p_SqlSelect varchar,    --查询语句,含排序部分
                  p_SqlSelectCount varchar,   --取记录总个数的SQL，如果空不取
                  p_OutRecordCount out int,--返回总记录数
                  p_OutCursor out sys_refcursor)
as
    v_sql varchar(3000);
    v_count int;
    v_pageNo int;
    v_heiRownum int;
    v_lowRownum int;
begin
 set v_count = 0;
 set v_pageNo = p_PageNo;  ----取记录总数
  if p_SqlSelectCount > ' ' then
   set   v_sql = p_SqlSelectCount;
     execute immediate v_sql into v_count;     --计算当前页码
     if(p_PageNo > 1 and ((p_PageNo - 1) * p_PageSize + 1) > v_count) then
       set  v_pageNo = ceil(v_count / p_PageSize);
     end if;
  end if;
  set p_OutRecordCount = v_count;  ----执行分页查询
 set  v_heiRownum = v_pageNo * p_PageSize;
 set  v_lowRownum = v_heiRownum - p_PageSize + 1;  set v_sql = 'SELECT *
            FROM (
                  SELECT A.*, rownum rn
                  FROM  ('p_SqlSelect') A
                  WHERE rownum <= 'convert(varchar(10),v_heiRownum) '
                 ) B
            WHERE rn >= ' convert(varchar(10),v_lowRownum)'';
            --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn  OPEN p_OutCursor FOR  v_sql;end ;

Answer 5

谁能告诉我 execute immediate v_sql into v_count;（代码21行处）是什么意思啊？如果在sql2005中表示，要怎么改？

Answer 6

----取记录总数
  if p_SqlSelectCount > ' ' then
     v_sql := p_SqlSelectCount;
     execute immediate v_sql into v_count;select count(1) from 你的table

Answer 7

 额我这个存储结构没错误的，只是他是oracle的，我希望有个兄弟帮我改成sql2005的~~

Answer 8

http://www.chinahtml.com/0610/mssql11611410557341.html