Java JDBC调用Oracle存储过程一般有3种: 1.无返回值 2.有一个返回值 3.返回一个数据集,就是游标! 关键字:call 语法格式{call 存储过程名(参数列表)} [color=red]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;
[/color] [color=red]Java中调用代码1:
[/color]
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); c
stmt.setInt(1, 2);
cstmt.setString(2, "wwww");
cstmt.setInt(3, 1);
cstmt.setString(4, "qwqwq");
cstmt.executeUpdate();
conn.close(); [color=red]
2.需求同上, 只是返回该部门的员工总数。(有一个返回值)[/color] --创建存储过程如下
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; [color=red]