There are generally three types of Java JDBC calls to Oracle stored procedures:
1. No return value
2. There is a return value
3. Return a data set, It’s the cursor!
Keywords: call syntax format {call stored procedure name (parameter list)}
Enough nonsense, see the code!
Java JDBC calls Oracle stored procedure business example:
1. Add employees, if the specified department does not exist, add department information first, and then add employees (no return value)
--Create the stored procedure as follows
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;
Calling code 1 in Java:
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. The requirements are the same as above, except that the total number of employees in the department is returned. (There is a return value)
--Create the stored procedure as follows
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;
Calling code 2 in Java:
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. The requirements are the same as above, and return to the department Employee information (employee number and name). (Return a cursor)
--Create the stored procedure as follows
Sql code
--1. Build package
CREATE OR REPLACE PACKAGE my_pak AS TYPE my_cus IS REF CURSOR ; END my_pak;
--2. Write storage Return process
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. Build package
CREATE OR REPLACE PACKAGE my_pak AS TYPE my_cus IS REF CURSOR ; END my_pak;
--2. Write stored return process
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 calls Oracle, Java Calling code 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();
The above is the detailed content of How Java uses JDBC to call Oracle storage. For more information, please follow other related articles on the PHP Chinese website!