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存储的详细内容。