SQLServer2008和Oracle10g分页存储过程

一、SQLServer2008

1.1分页存储过程：

-- =============================================
-- Author:  <gaga>
-- 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<@pageIndex)
  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();
  }
 }
 
}