几个较好的数据库分页过程

作者：Maverick

 blog： http://blog.youkuaiyun.com/zhaohuabing   转载请注明出处

一　oracle
CREATE OR REPLACE PROCEDURE page(
    p_num integer,
    p_size integer,
    condition clob,
    table_name varchar,
    p_rowset out sys_refcursor)
AS
BEGIN
OPEN p_rowset FOR
    'SELECT *
        FROM (
            SELECT ROWNUM r, t1.*
                FROM (
                    SELECT '|| table_name || '.* FROM '|| table_name || ' ' 
                      || condition || 'ORDER BY happentime desc) t1
               WHERE ROWNUM <= ' || p_size*p_num || ' ) t2
        WHERE t2.r > ' || p_size*(p_num-1);
END PAGE;

二　mssql
CREATE PROCEDURE page
    @p_num int,
    @p_size int,
    @condition text,
    @table_name nvarchar(100),
    @current_page_size int
AS
if @p_num = 1
   execute('SELECT TOP '+ @p_size +' * FROM '+ @table_name + ' '+ @condition +' ORDER BY happentime DESC')
else
   if @current_page_size != @p_size
      execute('SELECT * FROM(
                    SELECT TOP '+ @current_page_size +' * FROM '+ @table_name + ' '+ @condition +' ORDER BY happentime
              ) as t ORDER BY happentime DESC')
   else
      begin
      declare @tmp int
      set @tmp = @p_size * @p_num
      execute('SELECT * FROM(
                    SELECT TOP '+ @p_size +' * FROM(
                         SELECT TOP '+ @tmp +' * FROM '+ @table_name + ' '+ @condition +' ORDER BY happentime DESC
                    ) AS t1 ORDER BY happentime
              ) as t2 ORDER BY happentime DESC')
      end
GO

三　sybase
CREATE PROCEDURE page
    @p_num int,
    @p_size int,
    @condition  nvarchar(3000),
    @table_name nvarchar(100),
    @current_p_size int
AS
DECLARE @str_p_size varchar(20),
        @str_tmp varchar(20),
        @str_current_p_size varchar(20),
     @i_rowcount int 
begin
 select @str_tmp=cast(@p_size * @p_num as varchar(20))
 select @str_p_size=cast(@p_size as varchar(20))
 select @str_current_p_size=cast(@current_p_size as varchar(20))
 
 if @p_num = 1
  begin
   set @i_rowcount=@p_size*@p_num
   set rowcount @i_rowcount
   execute('SELECT  * FROM '+ @table_name + ' '+ @condition +' ORDER BY happentime DESC')
  end
 else
  if @current_p_size != @p_size
   begin
    set rowcount @current_p_size
    execute('SELECT  * INTO #temp FROM '+ @table_name + ' '+ @condition +' ORDER BY happentime 
             SELECT * FROM #temp ORDER BY happentime DESC')
   end
  else
   begin
    set @i_rowcount=@p_size*@p_num
    set rowcount @i_rowcount
    execute('SELECT  * INTO #temp1 FROM '+ @table_name + ' ' + @condition +' ORDER BY happentime DESC
       SELECT TOP '+ @str_p_size + ' * INTO #temp2 FROM #temp1 ORDER BY happentime
       SELECT * FROM #temp2 ORDER BY happentime DESC')
   end
end