java 调用 oracle 存储过程

	private static String url ="jdbc:oracle:thin:@ip:port:inst";
	private static String user="xxx"; 
	private static String password="xxx"; 
	static Connection conn;
	static CallableStatement cstat;
	@Before
	public void init(){
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn= DriverManager.getConnection(url,user,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 只有入参
	 * a.测试表
	 * create table test_a(
     *  name varchar2(32)
     *  )
     *  b.测试存储过程
     *  create or replace procedure pro_a(p1 varchar2)
		as 
		begin
		insert into test_a(name)values(p1);
		end;
		c.执行后查询test_a表有一条数据
	 * @date 2014-2-8 下午1:53:45
	 * @author bird
	 */
	@Test
	public void method1(){
		String sql ="call pro_a(?)";
		try {
			cstat = conn.prepareCall(sql);
			cstat.setString(1, "bird");
			cstat.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			destroy();
		}
	}

	/**
	 * 输出参数
	 * 》存储过程定义
	 * create or replace procedure pro_b(p1 in varchar2,p2 out number)
		as 
		begin
		select count(name) into p2 from test_a where name = p1;
		end;
	 * @date 2014-2-8 下午2:04:27
	 * @author bird
	 */
	@Test
	public void method2(){
		String sql ="call pro_b(?,?)";
		try {
			cstat = conn.prepareCall(sql);
			cstat.setString(1, "bird");
			cstat.registerOutParameter(2, Types.INTEGER);
			cstat.execute();
			System.out.println(cstat.getInt(2));
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			destroy();
		}
	}

	/**
	 * 返回多行记录
	 *  》存储过程定义
	 *  create or replace procedure pro_c(r_cs out sys_refcursor)
		as 
		begin
		open r_cs for select name from test_a;
		end;
	 * @date 2014-2-8 下午2:25:08
	 * @author bird
	 */
	@Test
	public void method3(){
		String sql ="call pro_c(?)";
		try {
			cstat = conn.prepareCall(sql);
			cstat.registerOutParameter(1,OracleTypes.CURSOR);
			cstat.execute();
			ResultSet rs = (ResultSet) cstat.getObject(1);
			while(rs.next()){
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			destroy();
		}
	}


	private void destroy(){
		try {
			cstat.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}