java调用存储过程

JDBC调用存储过程(2009-02-24 22:46:39)标签：杂谈   分类：oracle
?         案例1

–     添加员工,如果指定部门不存在,则先添加部门信息,再添加员工。

–     特点：无返回值存储过程

?         步骤

–     开发sp_add_emp(部门号，部门名称，工号，姓名)

–     JDBC调用

Connection conn = ...;

CallableStatement cstmt = null;

String spName = "{call sp_add_emp2(?,?)}";

cstmt = conn.prepareCall(spName);

cstmt.setInt(1, 工号);

cstmt.registerOutParameter(2, java.sql.Types.INTEGER); cstmt.executeUpdate();

int empCount = cstmt.getInt(2);

System.out.println(“该部门总人数： "+ empCount);

 

 

指定参数为out类型即可返回值

create or replace procedure sp_value(

id1 in number,

id2 out number

)

as

begin

  id2 := id1*200;

end;

-----------------调用(有简单返回值)----------------

Connection conn = null;

CallableStatement cstmt = null;

conn = DriverManager.getConnection(url, user, password);

String procedure = "{call sp_value(?,?)}";

cstmt = conn.prepareCall(procedure);

 

 

?         案例2

–     需求如案例1, 只是返回该部门的员工总数。

–     特点：有返回值存储过程，返回一个值

?         步骤

–     开发sp_add_emp2(工号，姓名，部门号，输出参数)

–     JDBC调用

Connection conn = ...;

CallableStatement cstmt = null;

String spName = "{call sp_add_emp2(?,?)}";

cstmt = conn.prepareCall(spName);

cstmt.setInt(1, 工号);

cstmt.registerOutParameter(2, java.sql.Types.INTEGER); cstmt.executeUpdate();

int empCount = cstmt.getInt(2);

System.out.println(“该部门总人数： "+ empCount);

 

 

指定参数为out类型即可返回值

create or replace procedure sp_value(

id1 in number,

id2 out number

)

as

begin

  id2 := id1*200;

end;

-----------------调用(有简单返回值)----------------

Connection conn = null;

CallableStatement cstmt = null;

conn = DriverManager.getConnection(url, user, password);

String procedure = "{call sp_value(?,?)}";

cstmt = conn.prepareCall(procedure);

 

 

 

 

?         案例  3

–     需求如案例1, 并返回该部门的员工信息(工号和姓名)。

–     特点：有返回值存储过程，返回数据集

?         步骤

–     建包,包中定义游标类型

–     开发sp_add_emp3(部门号，部门名称，工号，姓名,游标变量x)

–     存储过程返回指定游标类型变量

–     JDBC调用

CallableStatement cstmt = null;

String spName = “{call sp_add_emp2(?,?,?,?,?)}";

cstmt = conn.prepareCall(spName);

cstmt.setInt(1, 工号); ...

cstmt.registerOutParameter(5,

                                               oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

ResultSet rs = (ResultSet)cstmt.getObject(5);

……

 

 

·建包,包中定义游标类型变量

CREATE OR REPLACE PACKAGE TESTPACKAGE  AS

    TYPE Test_CURSOR IS REF CURSOR;

end;

·存储过程返回指定游标类型变量

CREATE OR REPLACE PROCEDURE sp_select_stu

(

p_c_id t_class.c_id%type,

p_cursor out TESTPACKAGE.Test_CURSOR

) IS

BEGIN

OPEN p_CURSOR FOR

   SELECT s.stu_id,s.s_name,to_char(s.s_birthday,'yy.mm') FROM t_stu s

   where s.c_id=p_c_id

   order by s.s_name;

END;

-----------------JDBC获取存储过程中的结果集---------------

Connection conn = null;

CallableStatement cstmt = null;

conn = DriverManager.getConnection(url, user, password);

String procedure = "{call sp_select_stu(?,?)}";

cstmt = conn.prepareCall(procedure);

//学号、姓名、班级编号

cstmt.setString(1, "C01");

//注册输出参数

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

//从输出参数中获取值

ResultSet rs = (ResultSet)cstmt.getObject(2);//第2个?所代表的参数

ResultSetMetaData rmd =rs.getMetaData();

while(rs.next()){

for(int i=0;i<rmd.getColumnCount();i++){

  System.out.print(rs.getString(i+1)+" ");

}

System.out.println("");

}

rs.close();