Oracle实践-PL/SQL综合之分页存储过程

Oracle PL/SQL分页的存储过程

         Oracle，分页，存储过程三个词结合起来，来个综合点的小练习，运用之前的PL/SQL创建一个分页的存储过程，只需要简单几步即可。

1.声明一个引用游标

create or replace package cur_ref_typeas-- is or as can be usedtype cur_ref is ref cursor;end;

create or replace procedure divideByPge         (tableName varchar2,pageSize number,currentPage number,                 totalPage out number,totalRecord out number,results out cur_ref_type.cur_ref)--要带包名is v_sql varchar2(200);--用于写sql语句的 v_begin number;--开始位置 v_end number;--结束位置begin v_begin := (currentPage -1) * pageSize; v_end := currentPage * pageSize; v_sql :='select count(*) from '||tableName;--将表名字符串拼接sql语句 execute immediate v_sql into totalRecord;--查询所有记录 totalPage :=ceil(totalRecord/pageSize);--计算总页数 v_sql :='select * from (select rownum rn,'||tableName||'.* from '||tableName||' where rownum <= '||v_end||') where rn > '||v_begin; open  results for v_sql;end;

String driver ="oracle.jdbc.driver.OracleDriver";        String url  = "jdbc:oracle:thin:@localhost:1521:orcl";        String username = "scott";        String password = "ysjian";        try {           Class.forName(driver);           Connection conn =                        DriverManager.getConnection(url,username,password);           CallableStatement cs = conn.prepareCall("{call                                         divideByPge(?,?,?,?,?,?)}");           cs.setString(1,"emp");//表名           cs.setInt(2, 3);//分页单位           cs.setInt(3, 1);//当前页           cs.registerOutParameter(4, OracleTypes.INTEGER);//注册输出参数           cs.registerOutParameter(5, OracleTypes.INTEGER);           cs.registerOutParameter(6, OracleTypes.CURSOR);//注册输出参数游标           cs.execute();//执行           ResultSet rs = (ResultSet)cs.getObject(6);//获得游标           while(rs.next()){               int empno = rs.getInt("empno");               String ename = rs.getString("ename");               System.out.println(empno+"-->"+ename);           }        } catch (ClassNotFoundException | SQLException e) {           e.printStackTrace();        }