Oracle--使用Java程序调用存储过程（CallableStatement）

1. 创建工程并导入Oracle的驱动jar包

创建一个JDBCUtils工具类

package pers.zhang.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:localhost/orcl";
	private static String user = "scott";
	private static String password = "123456";
	
	static{
		//注册驱动
		//DriverManager.registerDriver(driver)
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 运行Java：
	 * java -Xms100M -Xmx200M HelloWorld
	 * 
	 * 技术方向：
	 * 1、性能优化
	 * 2、故障诊断：死锁(JDK: ThreadDump)
	 *               Oracle: 自动处理
	 */
	public static void release(Connection conn,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null; ///-----> 原因：Java GC: Java的GC不受代码的控制
			}
		}
		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;
			}
		}
	}
}

实现过程的调用

1. 过程定义：

--统计年薪的过程
create or replace procedure proc_countyearsal(eno in number, esal out number)
as
begin
  select sal * 12 + nvl(comm, 0) into esal from emp where empno = eno;
end;  

--调用
declare
   esal number;
begin
  proc_countyearsal(7839,esal);
  dbms_output.put_line(esal);
end;

过程调用：

	@Test
	public void testProcedure01() {
		Connection conn = null;
		CallableStatement callSt = null;
		
		try {
			conn = JDBCUtils.getConnection();
			callSt = conn.prepareCall("{call proc_countyearsal(?,?)}");
			
			//对于in参数赋值
			callSt.setInt(1, 7839);
			//对于out参数声明
			callSt.registerOutParameter(2, OracleTypes.NUMBER);
			
			callSt.execute();
			
			System.out.println(callSt.getObject(2));
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

运行JUnit测试输出：

60000

游标引用的Java测试

1. 定义过程，并返回引用型游标

--定义过程
create or replace procedure proc_cursor_ref (dno in number, empList out sys_refcursor)
as
begin
  open empList for select * from emp where deptno = dno;
end;

--在pl/sql中调用
declare
  mycursor_c sys_refcursor;
  myempc emp%rowtype;
begin
  proc_cursor_ref(20,mycursor_c);
  loop
    fetch mycursor_c into myempc;
    exit when mycursor_c%notfound;
    dbms_output.put_line(myempc.empno || ',' || myempc.ename);
  end loop;
  close mycursor_c;
end;

java代码调用游标类型的out参数:

	@Test
	public void testFunction01() {
		Connection conn = null;
		CallableStatement callSt = null;
		ResultSet rs = null;
		
		try {
			conn = JDBCUtils.getConnection();
			callSt = conn.prepareCall("{call proc_cursor_ref(?,?)}");
			
			callSt.setInt(1, 20);
			callSt.registerOutParameter(2, OracleTypes.CURSOR);
			
			callSt.execute();
			
			rs = ((OracleCallableStatement)callSt).getCursor(2);
			while(rs.next()) {
				System.out.println(rs.getObject(1) + "," + rs.getObject(2));
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
	}

运行JUnit测试输出：

7369,SMITH
7566,JONES
7788,SCOTT
7876,ADAMS
7902,FORD