Java µ÷ÓÃOracleµÄPLSQLÈëÃÅ

1.½¨±í

http://blog.csdn.net/shihuacai/article/details/8889205

2.½¨Á¢plsql

--ÎÞ·µ»Ø½á¹û
create or replace procedure pro_insert_dept(v_deptno in number, v_dname in varchar2, v_loc in varchar2) is
begin
  insert into dept(deptno, dname, loc) values(v_deptno, v_dname, v_loc);
end;
--Ò»¸ö·µ»Ø½á¹û
create or replace procedure pro_select_dept(v_deptno in number, v_dname out varchar2)  as
begin
   select dname INTO v_dname FROM dept WHERE deptno = v_deptno;
end;

--½á¹û¼¯
CREATE OR REPLACE PACKAGE deptPackage AS
 TYPE DEPT_CURSOR IS REF CURSOR;
end;

CREATE OR REPLACE PROCEDURE pro_select_list_dept(p_CURSOR out deptPackage.DEPT_CURSOR) IS
BEGIN
    OPEN p_CURSOR FOR SELECT * FROM dept;
END;

3.javaÖ´ÐÐ

public class ProDetpTest {
 String driver = "oracle.jdbc.driver.OracleDriver";
 String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 Statement stmt = null;
 ResultSet rs = null;
 Connection conn = null;
 CallableStatement cstmt = null;
 CallableStatement proc = null;

 @Before
 public void init() {
  try {
   Class.forName(driver);
   conn = DriverManager.getConnection(strUrl, "scott", "*****");
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 @Test
 public void proWithNoBack() {
  try {
   proc = conn.prepareCall("{call pro_insert_dept(?,?,?)}");
   proc.setInt(1, 70);
   proc.setString(2, "TestOne");
   proc.setString(3, "±±¾©");
   proc.execute();
  } catch (SQLException ex2) {
   ex2.printStackTrace();
  } catch (Exception ex2) {
   ex2.printStackTrace();
  } finally {
   try {
    if (rs != null) {
     rs.close();
     if (stmt != null) {
      stmt.close();
     }
     if (conn != null) {
      conn.close();
     }
    }
   } catch (SQLException ex1) {

   }
  }
 }

 /**
  * Óзµ»ØÖµµÄ´æ´¢¹ý³Ì(·ÇÁбí)
  */
 @Test
 public void proWithBack() {
  try {
   proc = conn.prepareCall("{call pro_select_dept(?,?)}");
   proc.setInt(1, 50);
   proc.registerOutParameter(2, Types.VARCHAR);
   proc.execute();
   String testPrint = proc.getString(2);
   System.out.println("=name=is=" + testPrint);
  } catch (SQLException ex2) {
   ex2.printStackTrace();
  } catch (Exception ex2) {
   ex2.printStackTrace();
  } finally {
   try {
    if (rs != null) {
     rs.close();
     if (stmt != null) {
      stmt.close();
     }
     if (conn != null) {
      conn.close();
     }
    }
   } catch (SQLException ex1) {
   }
  }
 }

 @Test
 public void proWithList() {
  try {
   proc = conn.prepareCall("{call pro_select_list_dept(?)}");
   proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
   proc.execute();
   
   rs = (ResultSet) proc.getObject(1);
   while (rs.next()) {
    //System.out.println(rs.getInt(1) +  "  " + rs.getString(2) + "   " + rs.getString(3));
    System.out.println(rs.getInt("DEPTNO") +  "  " + rs.getString("DNAME") + "   " + rs.getString("LOC"));
   }
  } catch (SQLException ex2) {
   ex2.printStackTrace();
  } catch (Exception ex2) {
   ex2.printStackTrace();
  } finally {
   try {
    if (rs != null) {
     rs.close();
     if (stmt != null) {
      stmt.close();
     }
     if (conn != null) {
      conn.close();
     }
    }
   } catch (SQLException ex1) {
   }
  }
 }