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();
以上是Java如何利用JDBC呼叫Oracle存儲的詳細內容。更多資訊請關注PHP中文網其他相關文章!