sybase 使用預存程式產生分頁

 792 total views,  2 views today

一般在MySQL可以使用 limit來做分頁處理，但是sybase 12.5 系列卻沒有這個功能，後來找到這個預存程序先擋著用，未來在優化。

CREATE PROCEDURE query_page @sql varchar(2000),@start int, @pageSize int as 

begin 
    declare @ipage int
    declare @rcount int 
    declare @execsql varchar(2000)
    declare @sql1 varchar(2000) 
    declare @t int,@p int,@n int,@l int

    begin

        set @[email protected]
        set @n=0
        set @l=0
        set @t=charindex('select ',lower(@sql))
        set @sql=substring(@sql,@t+7,char_length(@sql)-7)
        set @[email protected]+1
        set @[email protected]+7
        
        while(@n!=0)
        begin
            set @t=charindex('select ',lower(@sql))
            set @p=charindex('from ',lower(@sql))
            if ((@t<@p) and (@t!=0))
                begin
                    set @sql=substring(@sql,@t+7,char_length(@sql)-7)
                    set @[email protected]+1
                    set @[email protected][email protected]
                end
            else
                begin
                    set @sql=substring(@sql,@p+5,char_length(@sql)-5)
                    set @[email protected]
                    set @[email protected][email protected]
                end
        end
        set @execsql = substring(@sql1,1,@l-5)+' ,sybid=identity(12) into #temp '+substring(@sql1,@l-4,char_length(@sql1)[email protected]+5)
        select @[email protected] + @pageSize
        set rowcount @rcount 
        set @execsql = @execsql || ' select * from #temp where sybid>' || convert(varchar,@start) || ' and sybid <= ' || convert(varchar,@rcount) 
        print @execsql
        execute (@execsql) 
        set rowcount 0 
    end
end

使用方法與mysql差不多

mysql的分頁法

雖然也可以取id在分頁(優化)

Select * From data Where id >=(
Select id From data Order By id limit 0,1
) limit 100

Select * From data Where id >=(
Select id From data Order By id limit 100,1
) limit 100

Select * From data Where id >=(
Select id From data Order By id limit 200,1
) limit 100

sybase使用query_page分頁法

query_page "select * from data where S_DATE='20151106'",0,100  /* 第一頁 */
query_page "select * from data where S_DATE='20151106'",0,100  /* 第二頁 */
query_page "select * from data where S_DATE='20151106'",0,100 /*  第三頁 */

參考網頁: http://www.cnblogs.com/warden/p/3340268.html