Sybase,Oracle,sqlserver中sql分页的方法汇总

Sybase

因为Sybase数据库本身的局限性,要实现分页相对其它数据库来说还是比较麻烦.

--方法1,
--这个方法从网上找来的,好处可直接传入SQL,并且对传入的SQL语句没有什么限制,合法的就行,
--缺点是需要的权限较大,因为需要创建临时表

CREATE PROCEDURE GetDataByPage(
                                 @SqlStr         varchar(8000), --SQL语句
                                 @PageSize       int,           --每页记录数
                                 @CurrentPage    int            --当前页数
                               )
AS
DECLARE @FirstRec int, @LastRec int, @dt varchar(10) --页起始行，页结束行，生成临时表的随机数
BEGIN
 SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
 SELECT @LastRec = (@CurrentPage * @PageSize + 1) --计算页结束行
 SELECT @dt= substring(convert(varchar,rand()),3,10) --一个字符型的随机数
 --将搜索结果放入临时表中，表名随机生成，在' FROM '前插入'INTO '+随机临时表名
--set chained off
 SELECT @SqlStr = stuff(@SqlStr, charindex(' FROM ',upper(@SqlStr)), 6 ,' INTO tempdb..Paging'+@dt+' FROM ')
--set chained on
 EXECUTE (@SqlStr)
 --为临时表增加id号
 SELECT @SqlStr = 'ALTER TABLE tempdb..Paging'+@dt+' ADD TEMPDB_ID numeric(10) IDENTITY PRIMARY KEY'
 EXECUTE (@SqlStr)
 --计算临时表中的记录数
 --SELECT @SqlStr = 'SELECT Count(*) From tempdb..Paging'+@dt
 --EXECUTE (@SqlStr)
 --选取记录号在起始行和结束行中间的记录
 SELECT @SqlStr = 'SELECT * FROM tempdb..Paging' + @dt  + ' WHERE TEMPDB_ID > ' + convert(varchar,@FirstRec)  + ' and TEMPDB_ID < '+convert(varchar,@LastRec)
 EXECUTE (@SqlStr)
 --删除临时表
 SELECT @SqlStr = 'DROP TABLE tempdb..Paging'+@dt
 EXECUTE (@SqlStr)
END
go

--方法2.

--优点,实现代码简单,而且可以做成通用的分页方法.

--缺点:对传入的SQL语句有限制,仅适用于简单的查询.

CREATE PROCEDURE GetDataByPage(
                                @CurrentPage    int,           --当前页数
                                @PageSize       int,           --每页记录数
                                @SqlStr         varchar(5000)
                               )
as 
declare @FirstRec int,
        @LastRec int

begin 
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
SELECT @LastRec = (@CurrentPage * @PageSize + 1)  --计算页结束行

set rowcount @FirstRec    --从第几行开始
set rowcount @LastRec     --取的记录数

EXECUTE (@SqlStr)

set rowcount 0 --重置
end 
go

sp_procxmode GetDataByPage, 'anymode';
go

--方法3,本质上和方法2是一样的,是对方法1和方法2不能执行的SQL语句的一种补充.

--优点,几乎没有限制,适用范围广.

--缺点:开发时较繁琐,需要对每条查询语句书写一个PROCEDURE,灵活性差.

CREATE PROCEDURE GetDataByPageF(
                                @CurrentPage    int,           --当前页数
                                @PageSize       int,           --每页记录数
                                @pid 		varchar,       --参数
                                @clnt 		varchar        --参数
                               )
as 
declare @id1 varchar(20),
        @FirstRec int,
        @LastRec int 

SELECT @FirstRec = (@CurrentPage - 1) * @PageSize --计算页起始行
SELECT @LastRec = (@CurrentPage * @PageSize + 1) --计算页结束行
set rowcount @FirstRec   --the start row 
SELECT @id1 = A.EBELN 
FROM SAPSR3.EKKO AS A 
WHERE A.LIFNR =  @pid  AND A.SUBMI <> ''  AND A.MANDT =  @clnt  AND A.LOEKZ = '' 
AND (SELECT COUNT(*) FROM SAPSR3.ZTRFQ003  WHERE LIFNR = A.LIFNR AND EBELN = A.EBELN) > 0  
AND (SELECT COUNT(*) FROM SAPSR3.EKET AS C 
LEFT JOIN SAPSR3.EBAN AS B ON C.BANFN = B.BANFN  
WHERE C.EBELN = A.EBELN AND B.STATU = 'B') > 0  order by EBELN DESC
set rowcount @LastRec --the no. of rows to return
SELECT  A.EBELN, A.AEDAT 
FROM SAPSR3.EKKO AS A 
WHERE A.LIFNR =  @pid  AND A.SUBMI <> ''  AND A.MANDT =  @clnt  AND A.LOEKZ = '' 
AND (SELECT COUNT(*) FROM SAPSR3.ZTRFQ003  WHERE LIFNR = A.LIFNR AND EBELN = A.EBELN) > 0  
AND (SELECT COUNT(*) FROM SAPSR3.EKET AS C  
LEFT JOIN SAPSR3.EBAN AS B ON C.BANFN = B.BANFN  
WHERE C.EBELN = A.EBELN AND B.STATU = 'B') > 0 AND EBELN <= @id1  order by EBELN DESC
set rowcount 0 --resetting
go

----------------------------------------
sp_procxmode GetDataByPageF, 'anymode'; 
go

另附在java中调用储存过程的方法

	 public List findByProcForPage(int page,int count,String sql) {
		Session s = getSession();
		Connection c = s.connection();
		ResultSet rs = null;
		try {
			CallableStatement cs = c
					.prepareCall("{call GetDataByPage(?,?,?)}");
			cs.setInt(1, page); 					// 当前页数
			cs.setInt(2, count); 					// 每页显示的数量
			cs.setString(3, sql);					// 要执行的sql语句
			rs = cs.executeQuery(); 				// 获得存储过程返回的结果集
			return resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) rs.close();
				c.setAutoCommit(true);
				c.close();
				HibernateSessionFactory.closeSession();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return Collections.EMPTY_LIST;
	}

 Oracle

Oracle中分页相对来说是容易的,而且易于封装成通用的方法.

public static String getOraclePagePartSql(String sql,int start,int end) {
    String recSql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM ("+sql+") A WHERE ROWNUM <= "+end+")WHERE RN >= "+start;
    return recSql;
}

SqlServer

sqlserver中一般使用top与row_number来进行分页,具体方法网上一搜一大把,就不累述了.