原文:http://www.cnblogs.com/zakaz/articles/2319576.html
存以备用!
PL/SQL 存储过程
--分页查询
create or replace package testpackage as
type 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)
is
v_sql varchar2(1000);
v_begin number:=(zk_pagenow-1)*zk_pagesize;
v_end number:=zk_pagenow*zk_pagesize;
begin
v_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 connt(*) 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;
end;
/
=================
java代码段
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();
}
}
}
}