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