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