Java调用Oracle的存储过程、存储函数详解数据库

存储过程

create or replace procedure queryEmpInfo(eno in number, 
                                         pename out varchar2, 
                                         psal   out number, 
                                         pjob   out varchar2) 
as 
begin 
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno; 
 
end; 
/

Java代码

@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); 
            System.out.println(sal); 
            System.out.println(job); 
        } catch (Exception e) { 
            e.printStackTrace(); 
        }finally{ 
            JDBCUtils.release(conn, call, null); 
        } 
    }

存储函数

create or replace function queryEmpIncome(eno in number) 
return number 
as 
  psal emp.sal%type; 
  pcomm emp.comm%type; 
begin 
 
  select sal,comm into psal,pcomm from emp where empno=eno; 
 
  return psal*12+nvl(pcomm,0); 
end; 
/

Java代码

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

Java代码

@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); 
 
            //对于in参数，赋值 
            call.setInt(1, 20); 
 
            //对于out参数,申明 
            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+"的薪水是"+sal); 
            } 
 
        } catch (Exception e) { 
            e.printStackTrace(); 
        }finally{ 
            JDBCUtils.release(conn, call, rs); 
        } 
    }

JDBCUtils

package demo.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:1521:orcl"; 
    private static String user  = "scott"; 
    private static String password = "tiger"; 
 
    static{ 
        //DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 
 
        try { 
            Class.forName(driver); 
        } catch (ClassNotFoundException e) { 
            e.printStackTrace(); 
        } 
    } 
 
    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; 
            } 
        } 
        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; 
            } 
        } 
    } 
}