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[( , , ...)]} 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[( , , ...)]} 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; } } } }