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

た入场券 2021-08-14 02:13 429阅读 0赞

#### 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;

![在这里插入图片描述][20190915122854381.png]  
**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

[20190915122854381.png]: /images/20210813/41fe0600da854919a7b90bc50bfcf255.png