JDBC之调用存储过程

 --Oralce存储过程

--输入参数
 create or replace procedure insertProc
 (
	 v_deptno in dept.deptno%type,
	 v_dname in dept.dname%type,
	 v_loc in dept.loc%type
 )
 is
 begin
 	insert into dept(deptno,dname,loc) 
 	values(v_deptno,v_dname,v_loc);
 	commit;
 end;
 
--输出参数
 create or replace procedure updateProc
 (
	 v_deptno in dept.deptno%type,
	 v_dname in dept.dname%type,
	 v_loc in dept.loc%type,
	 v_result out varchar2
 )
 is
 begin
	 update dept
	 set dname=v_dname,loc=v_loc
	 where deptno=v_deptno;
	 commit;
 	v_result:='update success';
 end;

 

 

package org.monday.demo;

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

import org.junit.Test;
import org.monday.util.JdbcUtil;

public class ProcMain {

	@Test
	public void test_insert() {
		String proc = "{call insertProc (?,?,?)}"; // 要调用的存储过程
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			conn = JdbcUtil.getConnection();
			// 调用存储过程
			cstmt = conn.prepareCall(proc);
			// 为输入参数赋值
			cstmt.setInt(1, 70);
			cstmt.setString(2, "web");
			cstmt.setString(3, "chengdu");
			// 执行
			cstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.release(conn, cstmt, null);
		}
	}

	@Test
	public void test_update() {
		String proc = "{call updateProc (?,?,?,?)}"; // 要调用的存储过程
		Connection conn = null;
		CallableStatement cstmt = null;
		try {
			conn = JdbcUtil.getConnection();
			// 调用存储过程
			cstmt = conn.prepareCall(proc);
			// 为输入参数赋值
			cstmt.setInt(1, 70);
			cstmt.setString(2, "jdbc");
			cstmt.setString(3, "guangzhou");
			// 注册输出参数
			cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
			// cstmt.registerOutParameter(4, java.sql.Types.VARCHAR, "v_result");
			// 执行
			cstmt.executeUpdate();
			// 获取返回信息
			System.out.println(cstmt.getString(4));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.release(conn, cstmt, null);
		}
	}
}