oracle存储应用程序,oracle 应用程序调用存储函数

packagecom.founder.ec.common.lucene;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importoracle.jdbc.internal.OracleCallableStatement;importoracle.jdbc.internal.OracleTypes;public classReadMysql {public static Connection getConnection() throwsException {

String url= "jdbc:oracle:thin:@192.168.";

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

String userName= "";

String password= "";

Connection con=DriverManager.getConnection(url, userName, password);returncon;

}/*** CREATE PROCEDURE member_procedure_out(memberId in NUMBER,realname OUT VARCHAR2,mobile_phone OUT VARCHAR2)*/

//释放数据库资源

public static voidrelease(Connection con, CallableStatement call, ResultSet res) {if (res != null || call != null || con != null) {try{

res.close();

con.close();

call.close();

}catch(Exception e) {

e.printStackTrace();

}finally{

res= null;

call= null;

res= null;

}

}

}public static voidmain(String[] args) {/***

CREATE PACKAGE mypackage AS

TYPE empcursor is REF CURSOR ;

PROCEDURE queryEmpList(memberId in NUMBER,empList OUT empcursor);

END mypackage;*/

try{//调用存储过程,根据会员id获得会员name//testpro();//调用存储函数,根据会员id计算其年收入//testfunction();//调用包下的存储过程,游标

testCursor();

}catch(Exception e) {

e.printStackTrace();

}finally{//release(con, call, null);

}

}private static voidtestCursor() {

Connection con= null;

CallableStatement call= null;try{

String sql= "{call mypackage.queryEmpList(?,?)}";

con=getConnection();

call=con.prepareCall(sql);

call.setInt(1,7923);

call.registerOutParameter(2,OracleTypes.CURSOR);//光标

call.execute();//取出该会员的所有信息

ResultSet res = ((OracleCallableStatement) call).getCursor(2);while(res.next()){

String name=res.getString("real_name");int memberId=res.getInt("member_id");

System.out.print(name+memberId);

}

}catch(Exception e) {

e.getMessage();

}

}private static voidtestfunction() {

Connection con= null;

CallableStatement call= null;try{

String sql= "{?=call query_member(?)}";

con=getConnection();

call=con.prepareCall(sql);

call.registerOutParameter(1,OracleTypes.NUMBER);

call.setInt(2,7923);

call.execute();double sum=call.getDouble(1);

System.out.println(sum);

}catch(Exception e) {

e.getMessage();

}

}private static voidtestpro() {

Connection con= null;

CallableStatement call= null;try{

String sql= "{call member_procedure_out(?,?)}";

con=getConnection();

call=con.prepareCall(sql);

call.setInt(1, 7923);

call.registerOutParameter(2, OracleTypes.VARCHAR);

}catch(Exception e) {

e.getMessage();

}try{

call.execute();

String realName= call.getString(2);

System.out.println(realName);

}catch(Exception e) {

e.printStackTrace();

}

}

}