oracle10g和2008,SQLServer2008和Oracle10g分页存储过程

一、SQLServer2008

1.1分页存储过程：

-- =============================================

-- Author:

-- Create date: <2013-7-26>

-- Description:

-- =============================================

--标量变量,表变量,本地变量

create procedure pagedByProc

@tablename varchar(20),--表名

@pageIndex int--分页

as

begin

set nocount on

declare @startPos int --起始点

declare @endPos int --结束点

declare @sum int--总的记录数

declare @getCountDataSql nvarchar(100)--获取记录数的sql语句

declare @tmp int --临时变量

declare @totalPage int--总的页数

declare @pageSize int--页面大小

--拼接-获取记录数sql语句

set @getCountDataSql = 'select @tmp = COUNT(1) from '+@tablename;

exec sp_executesql @getCountDataSql,N'@tmp int output',@sum output

--获取总页数

set @pageSize = 10;

if (@sum % @pageSize = 0)

set @totalPage = @sum / @pageSize;

else

set @totalPage = @sum / @pageSize + 1;

--判断索引参数是否合法

if(@pageIndex<1)

set @pageIndex = 1;

if(@totalPage

set @pageIndex = @totalPage;

--初始化

set @startPos = (@pageIndex-1)*@pageSize+1;

set @endPos = @pageIndex*@pageSize;

--执行分页语句

exec ('select * from (select ROW_NUMBER() over(order by id) as rownum,* from '+@tablename+') t where t.rownum between '+@startPos +' and '+ @endPos);

end

go

1.2 T-SQL测试

--建表

if OBJECT_ID('tb_user','U') is not null

drop table tb_user;

create table tb_user(

id varchar(6) primary key,

name varchar(12) not null,

birthday datetime not null

)

--插入数据

declare @i int

set @i=10;

while @i<100

begin

insert into tb_user values('2010'+CONVERT(varchar,@i),'user'+CONVERT(varchar,@i),GETDATE());

set @i=@i+1;

end

--调用存储过程

exec pagedByProc 'tb_user', -1;

二、Oracle10g

2.1存储过程

/*

思路：

输入：表名,当前页数，页大小，查询条件，排序

输出：当前页，页大小，总页数，总记录数

既然要分页，必然要知道记录的起始点和结束点；

起始点和结束点是通过当前页数和页大小计算得来。

1.获取总的记录数

2.求得页数

3.验证查询条件，排序字段和排序方式以及当前页数的合法性

4.计算起始和结束点

5.动态拼接sql语句

6.execute immediate 执行sql语句

*/

--package 和 package body

--相当于java接口和实现类

--在package中定义变量类型，方法签名，函数签名部分

--定义一个包，在包中定义一个游标类型

create or replace package pkg_query as

type cur_query is ref cursor;

end pkg_query;

create or replace procedure pagedByProc

(

tablename in varchar2,--表名

condition in varchar2,--查询条件

orderColumn in varchar2,--排序的列

orderStyle in varchar2,--排序方式

curPage in out int,--当前页

pageSize in out int,--每页的记录数

totalPage out int,--总页数

totalRecords out int, --总记录数

cur_result out pkg_query.cur_query--结果集

)

is

v_sql varchar2(20000):='';--查询语句

v_start_position int;

v_end_position int;

begin

v_sql:='select count(1) from '||tablename;

if condition is not null or condition <>'' then --拼接查询条件

v_sql := v_sql || ' where 1=1 and '||condition;

end if;

dbms_output.put_line(v_sql);

execute immediate v_sql into totalRecords; --获取总记录数

if pageSize < 1 then --验证pageSize的合法性

pageSize := 1;

end if;

if mod(totalRecords,pageSize) = 0 then --获取总页数

totalPage := totalRecords / pageSize;

else

totalPage := totalRecords / pageSize + 1;

end if;

if curPage <1 then --验证curPage的合法性

curPage := 1;

else if curPage >totalPage then

curPage := totalPage;

end if;

end if;

v_start_position := (curPage-1)*curPage; --求开始位置

v_end_position := curPage * pageSize; --求结束位置

v_sql := 'select * from (select t.*,rownum r from '||tablename||

' t) tt where tt.r between '||v_start_position ||' and '||v_end_position;

if condition is not null or condition <>'' then --拼接查询条件

v_sql := v_sql ||' and '||condition;

end if;

if orderColumn is not null or orderColumn <>'' then --排序的列

v_sql := v_sql || ' order by ' ||orderColumn;

end if;

if orderStyle is not null or orderStyle <>'' then --排序风格

v_sql := v_sql || ' ' ||orderStyle;

end if;

dbms_output.put_line(v_sql);

--execute immediate v_sql;

open cur_result for v_sql;

end;

2.2 PL-SQL测试

--建表

create table tb_user(

userId varchar2(32) default sys_guid() primary key,

userName varchar2(32) not null,

pass varchar2(16) not null,

state varchar2(10)

)

--插入数据

declare

i int:=1;

begin

for i in 1..10000 loop

insert into tb_user(userName,pass,state) values('w'||i,'w'||i,1);

end loop;

commit;

end;

declare

v_curPage int := 1;

v_pageSize int := 10;

v_totalPage int;

v_totalRecords int;

v_sys_cur SYS_REFCURSOR;

v_row_cur tb_user%rowtype;

begin

pagedByProc('tb_user',

'',

'userid',

'desc',

v_curPage,

v_pageSize,

totalPage => v_totalPage,

totalRecords => v_totalRecords,

cur_result => v_sys_cur);

dbms_output.put_line(v_curPage);

dbms_output.put_line(v_pageSize);

dbms_output.put_line(v_totalPage);

dbms_output.put_line(v_totalRecords);

loop

fetch v_sys_cur

into v_row_cur;

dbms_output.put_line(v_row_cur.userid);

--dbms_output.put_line(v_row_cur.username);

-- dbms_output.put_line(v_row_cur.pass);

-- dbms_output.put_line(v_row_cur.state);

-- dbms_output.put_line(v_row_cur.r);

exit when v_sys_cur%notfound;

end loop;

end;

2.3Java连接Oracle测试

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

import oracle.jdbc.driver.OracleCallableStatement;

public class PagedByProcTest {

public static void main(String[] args) {

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

String user = "guest";

String password = "guest";

Connection conn = DriverManager.getConnection(url, user, password);

String sql = "{call pagedByProc(?,?,?,?,?,?,?,?,?)}";

CallableStatement cs = conn.prepareCall(sql);

cs.setString(1, "tb_user");

cs.setString(2, "");

cs.setString(3, "userid");

cs.setString(4, "desc");

cs.setInt(5, 1);

cs.setInt(6, 10);

cs.registerOutParameter(5, OracleTypes.INTEGER);

cs.registerOutParameter(6, OracleTypes.INTEGER);

cs.registerOutParameter(7, OracleTypes.INTEGER);

cs.registerOutParameter(8, OracleTypes.INTEGER);

cs.registerOutParameter(9, OracleTypes.CURSOR);

cs.execute();

ResultSet rs = ((OracleCallableStatement)cs).getCursor(9);

while(rs.next())

{

System.out.println(rs.getString("userid"));

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

}

}