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();
【编辑推荐】