PL/SQL 分页
--分页查询create or replace package testpackage astype testcursor is ref cursor;end testpackage;/--select * from (select rownum rn,a1.* from (select * from emp)a1 where rownum<=10) where rn>=6;create or replace procedure zk_pro7(zk_table in varchar2,zk_pagesize in number,zk_pagenow in number,zk_rownum out number,zk_rowcount out number,zk_cursor out testpackage.testcursor)isv_sql varchar2(1000);v_begin number:=(zk_pagenow-1)*zk_pagesize;v_end number:=zk_pagenow*zk_pagesize;beginv_sql:='select * from (select rownum rn,a1.* from (select * from '||zk_table||')a1 where rownum<='||v_end||') where rn>='||v_begin;open zk_cursor for v_sql;v_sql:='select count(*) from '||zk_table;execute immediate v_sql into zk_rownum;if mod(zk_rownum,zk_pagesize)=0 then zk_rowcount := zk_rownum/zk_pagesize;else zk_rowcount:=zk_rownum/zk_pagesize+1;end if;--关闭游标close zk_cursor;end;/package com.gentle;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;public class Test1 {private static Connection connection = null;private static CallableStatement statement = null;private static ResultSet resultSet = null;public static void main(String[] args) {conn();}static void conn() {try {Class.forName("oralce.jdbc.driver.OracleDriver");connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORACLE","scott","tiger");statement = connection.prepareCall("{call zk_pro7(?,?,?,?,?,?)}");statement.setString(1, "emp");statement.setInt(2, 3);statement.setInt(3, 1);statement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);statement.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);statement.execute();int rownum = statement.getInt(4);int rowcount = statement.getInt(5);System.out.println("rownum:" + rownum);System.out.println("rowcount:" + rowcount);resultSet = (ResultSet) statement.getObject(6);while(resultSet.next()) {resultSet.getString("ename");resultSet.getInt("sal");resultSet.getDate("hiredate");//....}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();} finally {closeDB();}}static void closeDB() {if(connection!=null) {try {connection.close();connection = null;} catch (SQLException e) {e.printStackTrace();}}if(statement!=null) {try {statement.close();statement = null;} catch (SQLException e) {e.printStackTrace();}}if(resultSet!=null) {try {resultSet.close();resultSet = null;} catch (SQLException e) {e.printStackTrace();}}}}
页:
[1]