JAVA调用Oracle存储过程和函数详解数据库

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:@192.168.56.101:1521:orcl"; 
    private static String user = "scott"; 
    private static String password = "tiger"; 
     
    static{ 
        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; 
    } 
 
   
    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 
            } 
        } 
        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; 
            } 
        } 
    } 
}

import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.ResultSet; 
 
import oracle.jdbc.OracleCallableStatement; 
import oracle.jdbc.OracleTypes; 
 
import org.junit.Test; 
 
import JDBCUtils; 
 
public class TestOracle { 
/* 
 * create or replace procedure queryEmpInfo(eno in number, 
                                         pename out varchar2, 
                                         psal   out number, 
                                         pjob   out varchar2) 
 */ 
    @Test 
    public void testProcedure(){ 
        //{call <procedure-name>[(<arg1>,<arg2>, ...)]} 
        String sql = "{call queryEmpInfo(?,?,?,?)}"; 
        Connection conn = null; 
        CallableStatement call = null; 
        try { 
            conn = JDBCUtils.getConnection(); 
            call = conn.prepareCall(sql); 
             
            //对于in参数，赋值 
            call.setInt(1, 7839); 
             
            //对于out参数，申明 
            call.registerOutParameter(2, OracleTypes.VARCHAR); 
            call.registerOutParameter(3, OracleTypes.NUMBER); 
            call.registerOutParameter(4, OracleTypes.VARCHAR); 
             
            //执行 
            call.execute(); 
             
            //取出结果 
            String name = call.getString(2); 
            double sal = call.getDouble(3); 
            String job = call.getString(4); 
            System.out.println(name+"/t"+sal+"/t"+job); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        }finally{ 
            JDBCUtils.release(conn, call, null); 
        } 
    } 
     
/* 
 * create or replace function queryEmpIncome(eno in number) 
return number 
 */ 
    @Test 
    public void testFunction(){ 
        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 
        String sql = "{?=call queryEmpIncome(?)}"; 
         
        Connection conn = null; 
        CallableStatement call = null; 
        try { 
            conn = JDBCUtils.getConnection(); 
            call = conn.prepareCall(sql); 
             
            //对于out参数，申明 
            call.registerOutParameter(1, OracleTypes.NUMBER); 
             
            //对于in参数，赋值 
            call.setInt(2, 7839); 
 
            //执行 
            call.execute(); 
             
            //取出结果 
            double income = call.getDouble(1); 
            System.out.println(income); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        }finally{ 
            JDBCUtils.release(conn, call, null); 
        }         
    } 
/* 
查询某个部门中所有员工的所有信息 
包头 
CREATE OR REPLACE PACKAGE MYPACKAGE AS  
  type empcursor is ref cursor; 
  procedure queryEmpList(dno in number,empList out empcursor); 
END MYPACKAGE; 
 
包体 
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS 
  procedure queryEmpList(dno in number,empList out empcursor) AS 
  BEGIN 
    open empList for select * from emp where deptno=dno;   
  END queryEmpList; 
END MYPACKAGE; 
*/ 
    @Test 
    public void testCursor(){ 
        String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; 
         
        Connection conn = null; 
        CallableStatement call = null; 
        ResultSet rs = null; 
        try { 
            conn = JDBCUtils.getConnection(); 
            call = conn.prepareCall(sql);     
             
            call.setInt(1, 20); 
            call.registerOutParameter(2, OracleTypes.CURSOR); 
            call.execute(); 
             
            //取出集合 
            rs = ((OracleCallableStatement)call).getCursor(2); 
            while(rs.next()){ 
                String name = rs.getString("ename"); 
                double sal = rs.getDouble("sal"); 
                System.out.println(name+"/t"+sal); 
            } 
        } catch (Exception e) { 
            e.printStackTrace(); 
        }finally{ 
            JDBCUtils.release(conn, call, rs); 
        }     
    } 
}