sybase 使用預存程式產生分頁
1,145 total views, 1 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的分頁法
Select * From data limit 0, 100 第一頁 Select * From data limit 100,100 第二頁 Select * From data limit 200,100 第三頁 |
雖然也可以取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