Oracle procedure return DataSet used in JDBC

Sql代码

--1.建包   

   1. CREATE OR REPLACE PACKAGE my_pak AS    
   2.    TYPE my_cus IS REF CURSOR   ;     
   3. END my_pak;    

--2.写存储返回过程   

 

   1. CREATE OR REPLACE PROCEDURE sp_add_emp3(     
   2.     v_empno emp.empno%TYPE,     
   3.     v_ename emp.ename%TYPE,     
   4.     v_deptno dept.deptno%TYPE,     
   5.     v_dname dept.dname%TYPE,     
   6.     
   7.     p_cus OUT my_pak.my_cus     
   8. )AS    
   9.     num1 NUMBER;     
  10.     num2 NUMBER;     
  11. BEGIN    
  12.     
  13. OPEN p_cus FOR select empno,ename into v_empno,v_ename  from emp where deptno =v_deptno     
  14.    SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;     
  15. IF(num1=0) THEN    
  16.    INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);     
  17. END IF;     
  18.    SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;     
  19. IF(num2=0)THEN    
  20.    INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);     
  21. ELSE    
  22.     raise_application_error(-202021,'员工id 重复！！！');     
  23. END IF;     
  24.    commit;     
  25.     
  26. END;   

 

Java JDBC调用Oracle，Java中调用代码3：
 

Class.forName("oracle.jdbc.driver.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger"); conn.setAutoCommit(false); conn=DBConnection.getDBConnection().getConnection(); String spName="{call sp_add_emp2(?,?,?,?,?)}"; CallableStatement cstmt=conn.prepareCall(spName); cstmt.setInt(1,1111); cstmt.setString(2, "qqqq"); cstmt.setInt(3, 50); cstmt.setString(4, "pppp"); cstmt.registerOutParameter(5, java.sql.Types.ORACLETYPE); cstmt.executeUpdate(); int i = cstmt.getInt(5); System.out.println(i); cstmt.close(); conn.close();