如何使用Java JDBC调用Oracle存储过程？

Java JDBC调用Oracle存储过程一般有3种：

1.无返回值

2.有一个返回值

3.返回一个数据集，就是游标！

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

废话不说，见代码！

Java JDBC调用Oracle存储过程业务实例：

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

--创建存储过程如下  

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;      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：

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();        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.需求同上, 只是返回该部门的员工总数。（有一个返回值）

--创建存储过程如下  

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;      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：

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

--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;      BEGIN          OPEN 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;

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