--声明包 create or replace package p_base as Type p_cur is ref cursor; end p_base; --游标类型 create or replace procedure test(cur out p_base.p_cur) as begin open cur for select t.deptno, t.dname, t.loc From dept t; end test; --基本类型 create or replace procedure obtaindeptnames(p_dname out varchar2) as begin select dname into p_dname from dept t where t.deptno = 10; end;
//数据连接类DatabaseConnection.java package invokeprocedure; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DatabaseConnection { private Connection conn; public Connection getConnection() { String username = "scott"; String password = "tiger"; String url = "jdbc:oracle:thin:@10.40.71.13:1521:mdsp"; try { Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void main(String[] args) { Connection conn1 = new DatabaseConnection().getConnection(); System.out.print(conn1); } } //JavaBean:DepartmentInfo.java package invokeprocedure; public class DepartmentInfo { private int deptno; private String deptname; private String loc; public int getDeptno() { return deptno; } public void setDeptno(int deptno) { this.deptno = deptno; } public String getDeptname() { return deptname; } public void setDeptname(String deptname) { this.deptname = deptname; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; } } //调用 QueryWithProcedure.java package invokeprocedure; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import oracle.jdbc.OracleTypes; public class QueryWithProcedure { private Connection con; private CallableStatement cs; private ResultSet rs; private void getConn() { con = new DatabaseConnection().getConnection(); } public List<DepartmentInfo> getResults() { getConn(); List<DepartmentInfo> list = new ArrayList<DepartmentInfo>(); try { cs = con.prepareCall("{call test(?)}"); cs.registerOutParameter(1, OracleTypes.CURSOR); boolean ifsuccess = cs.execute(); if (!ifsuccess) { rs = (ResultSet)cs.getObject(1); while (rs.next()) { DepartmentInfo dept = new DepartmentInfo(); dept.setDeptno(rs.getInt(1)); dept.setDeptname(rs.getString(2)); dept.setLoc(rs.getString(3)); list.add(dept); } return list; } else { System.out.print("execute procedure failed!"); return list; } } catch (SQLException e) { e.printStackTrace(); return list; } finally { try { rs = null; if (cs != null) cs.close(); if (con != null) con.close(); } catch (SQLException e) { e.printStackTrace(); } } } public List<String> getDeptNames() { getConn(); List<String> list = new ArrayList<String>(); try { cs = con.prepareCall("{call obtaindeptnames(?)}"); cs.registerOutParameter(1, oracle.jdbc.OracleTypes.VARCHAR); boolean ifsuccess = cs.execute(); if (!ifsuccess) { String str = (String)cs.getObject(1); list.add(str); return list; } else { System.out.print("execute procedure failed!"); return list; } } catch (SQLException e) { e.printStackTrace(); return list; } catch (Exception e) { e.printStackTrace(); return null; } finally { try { rs = null; if (cs != null) cs.close(); if (con != null) con.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void main(String[] arg) { testGetResult(); } private static void testGetResult() { List<DepartmentInfo> list1 = new QueryWithProcedure().getResults(); for (DepartmentInfo di : list1) { System.out.print(di.getDeptno() + "-->" + di.getDeptname() + "-->" + di.getLoc()); } } @SuppressWarnings("unused") private static void testGetDeptNames() { List<String> list2 = new QueryWithProcedure().getDeptNames(); for (String str : list2) { System.out.print(str); } } }