oracle 调用mysql存储过程_Oracle数据库连接、存储过程及调用

Oracle数据库连接、存储过程及调用

1. 定义一个存储过程

create or replace procedure getuser(eid in number, na out varchar, em out varchar)

as

begin

select username, email into na, em from T_USER where userid = eid;

dbms_output.put_line(na);

end;

2. 应用程序连接数据库

package utils;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

/**

* 连接oracle数据库工具类

* @author 宾桀锋

* @date 2017年8月13日

*/

public class JDBCUtils {

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

private static String url = "jdbc:oracle:thin:@192.168.2.152:1521:orcl";

private static String username= "binjf";

private static String password = "123456";

//注册数据库的驱动

static{

try {

Class.forName(driver);

} catch (ClassNotFoundException e) {

throw new ExceptionInInitializerError(e);

}

}

//获取数据库连接

public static Connection getConnection(){

try {

return DriverManager.getConnection(url, username, password);

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

//释放数据库的资源

public static void release(Connection conn, Statement st, ResultSet rs){

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

rs = null;

}

}

if(st != null){

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

st = null;

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

} finally {

conn = null;

}

}

}

}

3. 调用存储过程

package oracle;

import java.sql.CallableStatement;

import java.sql.Connection;

import oracle.jdbc.internal.OracleTypes;

import org.junit.Test;

import utils.JDBCUtils;

/**

* 测试存储过程类

* @author 宾桀锋

* @date 2017年8月13日

*/

public class TestProcedure {

@Test

public void testProcedure(){

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

Connection conn = null;

CallableStatement call = null;

try{

//得到一个连接

conn = JDBCUtils.getConnection();

//通过连接创建出statement

call = conn.prepareCall(sql);

//对于in参数，赋值

call.setInt(1, 5);

//对于out参数，申明

call.registerOutParameter(2, OracleTypes.VARCHAR);

call.registerOutParameter(3, OracleTypes.VARCHAR);

//执行调用

call.execute();

//取出结果

String username = call.getString(2);

String email = call.getString(3);

System.out.println("用户名：" + username + " 邮箱：" + email);

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(conn, call, null);

}

}

}