Java访问oracle11G存储过程

oracle224

---

---

Java访问oracle11G存储过程

SQL>SELECT*FROMSTUDENT;

IDNAME AGE

----------- -------------------- -----------

1张三20

2李四25

3王五30

4麻子30

5赵北60

SQL>

createorreplacepackagepack_paging--通过Java来访问列表类型的数据就必须创建带ref游标的包

as

typeref_paging_cursorisrefcursor;--声明ref游标类型

endpack_paging;

createorreplaceprocedurepro_paging(psinnumber,csinnumber,pagingoutpack_paging.ref_paging_cursor)

is

begin

openpagingforselectid,name,agefrom(selects.*,rownumrnfromstudent s)wherern>cs*(ps-1)andrn<=cs*ps;

end;

packagecom.test.oracle;

importjava.sql.CallableStatement;

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.ResultSet;

importjava.sql.SQLException;

/**

*@Description通过oracle来操作oracle的procedure

*@Author永文

*@Date2015-1-20下午5:05:36

*/

publicclassTestOracleProcedure {

privateStringjdbcDriver="oracle.jdbc.OracleDriver";

privateStringjdbcUrl="jdbc:oracle:thin:@192.168.91.3:1521:jiagulun";

privateConnectionconn=null;

privateCallableStatementcstmt=null;

privateResultSetrs=null;

publicstaticvoidmain(String[] args) {

newTestOracleProcedure().testOracleProcedurePaging(2, 3);

}

/**

*@Description通过oracle存储过程分页

*@Author永文

*@paramps第几页

*@paramcs每页的记录条数

*@Returnvoid

*/

publicvoidtestOracleProcedurePaging(intps,intcs){

try{

Class.forName(jdbcDriver);

conn= DriverManager.getConnection(jdbcUrl,"hr","hr");

cstmt=conn.prepareCall("{call hr.pro_paging(?,?,?)}");//调用过程

cstmt.setInt(1, ps);//设置参数

cstmt.setInt(2, cs);//设置参数

cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);//注册输出参数游标类型

cstmt.execute();

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

while(rs.next()){

System.out.println("学号："+rs.getInt(1)+"姓名："+rs.getString(2)+"年龄："+rs.getInt(3));

}

}catch(SQLException e) {

e.printStackTrace();

}catch(Exception e ){

e.printStackTrace();

}finally{//关闭资源

if(conn!=null)

try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

if(rs!=null)

try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

}

}

}