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); 
}     
} 
}