java oracle 操作日志_JAVA操作ORACLE数据库的存储过程

packagecom.zhwy;import java.sql.*;public classTest {

String driver= "oracle.jdbc.driver.OracleDriver";

String strUrl= "jdbc:oracle:thin:@localhost:1521:orcl";

ResultSet rs= null;

Connection conn= null;

CallableStatement cstmt= null;public static voidmain(String[] args) {new Test().testPageSet(3, 1);

}/*** 没有返回参数的存储过程

*@paraminputeSno

*@paraminputSage*/

public void testNoOutParameterUpdate(int inputeSno, intinputSage) {try{

Class.forName(driver);

conn= DriverManager.getConnection(strUrl, "scott", "scott");

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

cstmt.setInt(1, inputeSno);

cstmt.setInt(2, inputSage);

cstmt.execute();

System.out.println("执行成功！");

}catch(SQLException ex) {

ex.printStackTrace();

}catch(Exception ex) {

ex.printStackTrace();

}finally{try{if (cstmt != null)

cstmt.close();if (conn != null) {

conn.close();

conn= null;

}

}catch(SQLException ex) {

ex.printStackTrace();

}

}

}/*** 没有返回参数的存储过程

*@paraminputeSno

*@paraminputSage*/

public void testNoOutParameterInsert(int a, String b, intc) {try{

Class.forName(driver);

conn= DriverManager.getConnection(strUrl, "scott", "scott");

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

cstmt.setInt(1, a);

cstmt.setString(2, b);

cstmt.setInt(3, c);

cstmt.execute();

}catch(SQLException ex) {

ex.printStackTrace();

}catch(Exception ex) {

ex.printStackTrace();

}finally{try{if (cstmt != null)

cstmt.close();if (conn != null) {

conn.close();

conn= null;

}

}catch(SQLException ex) {

ex.printStackTrace();

}

}

}/*** 有返回参数的存储过程

*@paraminputeSno

*@paraminputSage*/

public void testOutParameter(intinputSno) {try{

Class.forName(driver);

conn= DriverManager.getConnection(strUrl, "scott", "scott");

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

cstmt.setInt(1, inputSno);

cstmt.registerOutParameter(2, Types.VARCHAR);

cstmt.registerOutParameter(3, Types.INTEGER);

cstmt.execute();

String name= cstmt.getString(2);int age = cstmt.getInt(3);

System.out.print("学号是：" + inputSno + "的学生的名字是：" + name + ",年龄是：" +age);

}catch(SQLException ex) {

ex.printStackTrace();

}catch(Exception ex) {

ex.printStackTrace();

}finally{try{if (cstmt != null)

cstmt.close();if (conn != null) {

conn.close();

conn= null;

}

}catch(SQLException ex) {

ex.printStackTrace();

}

}

}/*** 返回列表的存储过程

*@paraminputeSno

*@paraminputSage*/

public voidtestOutResult() {try{

Class.forName(driver);

conn= DriverManager.getConnection(strUrl, "scott", "scott");

cstmt= conn.prepareCall("{ call scott.testc(?)}");

cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//指定是oracle里规定的类型

cstmt.execute();

rs= (ResultSet) cstmt.getObject(1);while(rs.next()) {

System.out.print("学号是：" + rs.getInt(1) + "的学生的名字是：" + rs.getString(2) + ",年龄是：" + rs.getInt(3) + "\r\n");

}

}catch(SQLException ex) {

ex.printStackTrace();

}catch(Exception ex) {

ex.printStackTrace();

}finally{try{if (cstmt != null)

cstmt.close();if (conn != null) {

conn.close();

conn= null;

}

}catch(SQLException ex) {

ex.printStackTrace();

}

}

}/*** 分页返回列表的存储过程

*@paraminputeSno

*@paraminputSage*/

public void testPageSet(int recordPerPage, intcurrentPage) {try{

Class.forName(driver);

conn= DriverManager.getConnection(strUrl, "scott", "scott");

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

cstmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);//指定是oracle里规定的类型

cstmt.setInt(1, recordPerPage);

cstmt.setInt(2, currentPage);

cstmt.execute();

rs= (ResultSet) cstmt.getObject(3);while(rs.next()) {

System.out.print("学号是：" + rs.getInt(1) + "的学生的名字是：" + rs.getString(2) + ",年龄是：" + rs.getInt(3) + "\r\n");

}

}catch(SQLException ex) {

ex.printStackTrace();

}catch(Exception ex) {

ex.printStackTrace();

}finally{try{if (cstmt != null)

cstmt.close();if (conn != null) {

conn.close();

conn= null;

}

}catch(SQLException ex) {

ex.printStackTrace();

}

}

}

}