JDBC调用存储过程

call 语法格式{call 存储过程名(参数列表)}





业务实例：

1.添加员工,如果指定部门不存在,则先添加部门信息,再添加员工（无返回值）

--创建存储过程如下

Sql代码 JDBC调用存储过程

   1. CREATE OR REPLACE PROCEDURE sp_add_emp1(  
   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. )AS 
   7.    num1 NUMBER;  
   8.    num2 NUMBER;  
   9. BEGIN 
  10.    SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;  
  11. IF(num1=0) THEN 
  12.    INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);  
  13. END IF;  
  14.    SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;  
  15. IF(num2=0)THEN 
  16.    INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);  
  17. ELSE 
  18.    raise_application_error(-202021,'员工id 重复！！！');  
  19. END IF;  
  20.    commit;  
  21.  
  22. END; 

CREATE OR REPLACE PROCEDURE sp_add_emp1( v_empno emp.empno%TYPE, v_ename emp.ename%TYPE, v_deptno dept.deptno%TYPE, v_dname dept.dname%TYPE)AS num1 NUMBER; num2 NUMBER;BEGIN SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;IF(num1=0) THEN INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);END IF; SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;IF(num2=0)THEN INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);ELSE raise_application_error(-202021,'员工id 重复！！！');END IF; commit;END;

Java中调用代码1：

Java代码 JDBC调用存储过程

   1. Class.forName("oracle.jdbc.driver.OracleDriver");  
   2. conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");  
   3. conn.setAutoCommit(false);  
   4.  
   5. conn=DBConnection.getDBConnection().getConnection();  
   6. String spName="{call sp_add_emp1(?,?,?,?)}";  
   7. CallableStatement cstmt=conn.prepareCall(spName);  
   8. cstmt.setInt(1, 2);  
   9. cstmt.setString(2, "wwww");  
  10. cstmt.setInt(3, 1);  
  11. cstmt.setString(4, "qwqwq");  
  12. cstmt.executeUpdate();  
  13.  
  14. conn.close(); 

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_emp1(?,?,?,?)}"; CallableStatement cstmt=conn.prepareCall(spName); cstmt.setInt(1, 2); cstmt.setString(2, "wwww"); cstmt.setInt(3, 1); cstmt.setString(4, "qwqwq"); cstmt.executeUpdate(); conn.close();

2.需求同上, 只是返回该部门的员工总数。（有一个返回值）

--创建存储过程如下

Sql代码 JDBC调用存储过程

   1. CREATE OR REPLACE PROCEDURE sp_add_emp2(  
   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.    num out number  
   8. )AS 
   9.    num1 NUMBER;  
  10.    num2 NUMBER;  
  11. BEGIN 
  12.    SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;  
  13. IF(num1=0) THEN 
  14.    INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);  
  15. END IF;  
  16.    SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;  
  17. IF(num2=0)THEN 
  18.    INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);  
  19. ELSE 
  20.    raise_application_error(-202021,'员工id 重复！！！');  
  21. END IF;  
  22.  
  23.    num:=num1;  
  24.    commit;  
  25.  
  26. END; 

CREATE OR REPLACE PROCEDURE sp_add_emp2( v_empno emp.empno%TYPE, v_ename emp.ename%TYPE, v_deptno dept.deptno%TYPE, v_dname dept.dname%TYPE, num out number)AS num1 NUMBER; num2 NUMBER;BEGIN SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;IF(num1=0) THEN INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);END IF; SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;IF(num2=0)THEN INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);ELSE raise_application_error(-202021,'员工id 重复！！！');END IF; num:=num1; commit;END;

Java中调用代码2：

Java代码 JDBC调用存储过程

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

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.INTEGER); cstmt.executeUpdate(); int i = cstmt.getInt(5); System.out.println(i); cstmt.close(); conn.close();

3.需求同上, 并返回该部门的员工信息(工号和姓名)。（返回一个游标）

--创建存储过程如下

Sql代码 JDBC调用存储过程

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

--1.建包 CREATE OR REPLACE PACKAGE my_pak AS TYPE my_cus IS REF CURSOR ; END my_pak; --2.写存储返回过程CREATE OR REPLACE PROCEDURE sp_add_emp3( v_empno emp.empno%TYPE, v_ename emp.ename%TYPE, v_deptno dept.deptno%TYPE, v_dname dept.dname%TYPE, p_cus OUT my_pak.my_cus)AS num1 NUMBER; num2 NUMBER;BEGINOPEN p_cus FOR select empno,ename into v_empno,v_ename from emp where deptno =v_deptno SELECT COUNT(*) INTO num1 FROM dept WHERE deptno=v_deptno;IF(num1=0) THEN INSERT INTO dept(deptno,dname) VALUES(v_deptno,v_dname);END IF; SELECT COUNT(*) INTO num2 FROM emp WHERE empno=v_empno;IF(num2=0)THEN INSERT INTO emp(empno,ename,deptno) VALUES(v_empno,v_ename,v_deptno);ELSE raise_application_error(-202021,'员工id 重复！！！');END IF; commit;END;

Java中调用代码3：

Java代码 JDBC调用存储过程

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

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();