JDBC调用oracle存储过程

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]