1。先建立包中的游标
CREATE OR REPLACE Package testpackage As
Type test_cursor Is Ref Cursor;
CREATE OR REPLACE Package testpackage As
Type test_cursor Is Ref Cursor;
End testpackage;
2。实现存储过程代码
CREATE OR REPLACE Procedure fenye(
tableName In Varchar2,
Pagesize In Number,
pageNow In Number,
myrows Out Number,
myPageCount Out Number,
p_cursor Out testpackage.test_cursor
)Is
v_sql Varchar2(1000);
v_begin Number:=(pageNow-1)*Pagesize+1;
v_end Number:=pageNow*Pagesize;
Begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<'||v_end||') where rn>='||v_begin||'';
CREATE OR REPLACE Procedure fenye(
tableName In Varchar2,
Pagesize In Number,
pageNow In Number,
myrows Out Number,
myPageCount Out Number,
p_cursor Out testpackage.test_cursor
)Is
v_sql Varchar2(1000);
v_begin Number:=(pageNow-1)*Pagesize+1;
v_end Number:=pageNow*Pagesize;
Begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<'||v_end||') where rn>='||v_begin||'';
Open p_cursor For v_sql;
v_sql:='select count(*) from '||tableName;
Execute Immediate v_sql Into myrows;
If Mod(myrows,Pagesize)=0 Then
myPageCount:=myrows/Pagesize;
Else
myPageCount:=myrows/Pagesize+1;
End If;
--Close p_cursor;
End;
3。在JAVA程序中获取返回列表的结果集
package com.nyl.ptest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
public class FenyeTest {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@192.168.13.07:1521:chenjian","iptv_zj","iptv");
CallableStatement cs=conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
cs.registerOutParameter(4, Types.INTEGER);
cs.registerOutParameter(5, Types.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int allpages=cs.getInt(4);
int pagenum=cs.getInt(5);
System.out.println("allpages:\t"+allpages+"pagenum:\t"+pagenum);
ResultSet rs=(ResultSet)cs.getObject(6);
while(rs.next()){
System.out.println("empno:\t"+rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}