存储过程代码:
create or replace procedure fenye1 (v_in_table in varchar2,v_in_pageSize in number,
v_in_pageNow in number,v_out_cursor out pack1.my_cursor,v_out_r ows out number,v_out_pageCount out number) is
--定义变量
v_sql varchar2(2000);
v_start number:=v_in_pageSize*(v_in_pageNow-1)+1;
v_end number:=v_in_pageSize*v_in_pageNow;
begin
v_sql :='select t2.* from (select t1.* ,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标指向结果集
open v_out_cursor for v_sql;
--返回多少条
select count(*) into v_out_rows from emp;
--查询多少行
if mod(v_out_rows,v_in_pageSize)=0 then
v_out_pageCount :=v_out_rows/v_in_pageSize;
else
v_out_pageCount :=v_out_rows/v_in_pageSize+1;
end if;
end;
/
java调用代码:
public class TestPro3 {
public static void main(String [] args) {
Connection ct= null;
CallableStatement cs =null;
ResultSet rs= null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
cs = ct.prepareCall("{call fenye1(?,?,?,?,?,?)}");
cs.setString(1,"emp");
cs.setInt(2, 3);
cs.setInt(3, 1);
//给第二个问号,因为是输出值
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.NUMBER);
cs.execute();
//取出输出的值
rs = (ResultSet) cs.getObject(4);
while(rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
}
int count = cs.getInt(5);
System.out.println(count);
int pageCount = cs.getInt(6);
System.out.println(pageCount);
} catch (Exception e) {
e.printStackTrace();
}
}
}
create or replace procedure fenye1 (v_in_table in varchar2,v_in_pageSize in number,
v_in_pageNow in number,v_out_cursor out pack1.my_cursor,v_out_r ows out number,v_out_pageCount out number) is
--定义变量
v_sql varchar2(2000);
v_start number:=v_in_pageSize*(v_in_pageNow-1)+1;
v_end number:=v_in_pageSize*v_in_pageNow;
begin
v_sql :='select t2.* from (select t1.* ,rownum rn from (select * from '||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标指向结果集
open v_out_cursor for v_sql;
--返回多少条
select count(*) into v_out_rows from emp;
--查询多少行
if mod(v_out_rows,v_in_pageSize)=0 then
v_out_pageCount :=v_out_rows/v_in_pageSize;
else
v_out_pageCount :=v_out_rows/v_in_pageSize+1;
end if;
end;
/
java调用代码:
public class TestPro3 {
}