java调用存储过程分页带条件_java调用存储过程分页

create or replace package mypack

2 as

3 type emp_cursor is ref cursor;

4 end mypack;

分页存储过程

create or replace procedure pro_emp(

--传入表名

v_in_tableName in varchar2,

--传人每页显示几条记录

v_in_pageSize in number,

--传入显示第几页的记录

v_in_pageNow in number,

--输出总共多少页

v_out_pageCount out number,

--输出总共有多少条记录

v_out_myRows out number,

--输出当页的记录

p_cursor out mypack.emp_cursor)

as

v_sql varchar2(1000);

v_begin number(4):=(v_in_pageNow-1)*v_in_pageSize+1;

v_end number(4):=v_in_pageNow*v_in_pageSize;

begin

--oracle里字符串用单引号，赋值用:=，连接用||

v_sql:='select * from (select t.*,rownum rn from (select * from '||v_in_tableName||' ) t where rownum<='||v_end||' ) where rn>='||v_begin;

open p_cursor for v_sql;

v_sql:='select count(*) from '||v_in_tableName;

--将执行结果写入v_out_myRows

execute immediate v_sql into v_out_myRows;

--注意oracle里取余必须用mod()函数，判断是否等于0用=，不是==

if mod(v_out_myRows,v_in_pageSize)=0 then

v_out_pageCount:=v_out_myRows/v_in_pageSize;

else

v_out_pageCount:=v_out_myRows/v_in_pageSize+1;

end if;

--close p_cursor;

end pro_emp;

/

java代码调用sql存储过程;

public static void main(String[] args) {

Connection conn=null;

CallableStatement cstmt=null;

ResultSet rs=null;

try{

//注册驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取连接

conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORA","scott","tiger");

//调用存储过程

cstmt=conn.prepareCall("{call pro_emp(?,?,?,?,?,?)}");

//对?赋值

cstmt.setString(1, "EMP");

cstmt.setInt(2, 3);

cstmt.setInt(3, 2);

cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

//执行

cstmt.execute();

//获取结果

int myCounts=cstmt.getInt(4);

System.out.println("总页数为："+myCounts);

int myRows=cstmt.getInt(5);

System.out.println("总记录数为："+myRows);

rs=(ResultSet)cstmt.getObject(6);

while(rs.next()){

System.out.println("用户名为："+rs.getString(2)+" 职位为："+rs.getString(3)+" 薪水为"+rs.getInt(6));

}

}

catch(Exception e){

e.printStackTrace();

}

finally{

try{

//逆序关闭资源

rs.close();

cstmt.close();

conn.close();

}

catch(Exception e1){

e1.printStackTrace();

}

}

}

}

[java] view plain copy

import java.sql.*;

public class oracleFenYe {

public static void main(String[] args) {

Connection conn=null;

CallableStatement cstmt=null;

ResultSet rs=null;

try{

//注册驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

//获取连接

conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORA","scott","tiger");

//调用存储过程

cstmt=conn.prepareCall("{call pro_emp(?,?,?,?,?,?)}");

//对?赋值

cstmt.setString(1, "EMP");

cstmt.setInt(2, 3);

cstmt.setInt(3, 2);

cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

//执行

cstmt.execute();

//获取结果

int myCounts=cstmt.getInt(4);

System.out.println("总页数为："+myCounts);

int myRows=cstmt.getInt(5);

System.out.println("总记录数为："+myRows);

rs=(ResultSet)cstmt.getObject(6);

while(rs.next()){

System.out.println("用户名为："+rs.getString(2)+" 职位为："+rs.getString(3)+" 薪水为"+rs.getInt(6));

}

}

catch(Exception e){

e.printStackTrace();

}

finally{

try{

//逆序关闭资源

rs.close();

cstmt.close();

conn.close();

}

catch(Exception e1){

e1.printStackTrace();

}

}

}