一 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
三 sybaseCREATE 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
posted on 2006-05-26 11:02
SIMONE 阅读(363)
评论(0) 编辑 收藏 所属分类:
收藏