Oracle学习-存储过程及调用

写了存储过程就必须会调用，不然用来干嘛勒

存储过程--查询用户姓名，年龄，月薪

create or replace procedure queryuserinfo(pid in number,pname out VARCHAR2,page out NUMBER,pmoney out VARCHAR2)
as
begin
select name,age,money into pname,page,pmoney from tb_user where id = pid;
end;

Oracle连接--JDBCUtil

package jdbc.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {
	private static String driver = "oracle.jdbc.OracleDriver";
	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	private static String user = "xhs";
	private static String password = "123456";
	// 注册数据库的驱动
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	// 获取数据库连接
	public static Connection getConn() {
		try {
			return DriverManager.getConnection(url, user, 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;
			}
		}
	}
}

调用：

package jdbc.test;

import java.sql.CallableStatement;
import java.sql.Connection;

import org.junit.Test;

import jdbc.utils.JDBCUtils;
import oracle.jdbc.driver.OracleTypes;

public class testProcedure {
@Test
public void testUserProcedure(){
	String sql = "{call queryuserinfo(?,?,?,?)}";
	Connection conn=null;
	CallableStatement call =null;
	try {
		conn=JDBCUtils.getConn();
		call=conn.prepareCall(sql);
		call.setInt(1, 25);
		call.registerOutParameter(2, OracleTypes.VARCHAR);
		call.registerOutParameter(3, OracleTypes.NUMBER);
		call.registerOutParameter(4, OracleTypes.VARCHAR);
		call.execute();
		String name = call.getString(2);
		String age = call.getString(3);
		String money = call.getString(4);
		System.out.println(name+"的年龄是"+age+"月薪是"+money);
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		JDBCUtils.release(conn, call, null);
	}
}
}

oracle的jdbc jar包：
链接：http://pan.baidu.com/s/1kVc8IPL 密码：unbz