Java调用Oracle中的Package

谯秀洁

1、首先新建一个用于测试的表，以及Package
create table student(
id int primary key,
name varchar2(20),
age int,
sex varchar2(10),
address varchar2(200)
);
insert into student values(1,'Kik',21,'男','北京');
insert into student values(2,'Civil',20,'女','上海');
insert into student values(3,'lucas',17,'男','北京');
insert into student values(4,'Leos',18,'女','广州');
2、创建Oralce的Package用于查询学生信息
--包的声明
create or replace package pack_stu_test
as
procedure find_stu(
          sid in int, --ID
          sname out varchar2,--姓名
          sage out int, --年龄
          ssex out varchar2, --性别
          saddress out varchar2 --地址
          );
end pack_stu_test;
--包体
Create or replace package body pack_stu_test
as
procedure find_stu(
          sid in int, --ID
  sname out varchar2,--姓名
          sage out int, --年龄
          ssex out varchar2, --性别
          saddress out varchar2 --地址
  )as
    v_id student.id%type;
begin
v_id:=sid;
select name,age,sex,address into sname,sage,ssex,saddress from student where id = sid;
end;  
end pack_stu_test;
3、java代码实现

public static void getAllStuInfo(int id) {
Connection conn = null;
CallableStatement stat = null;
ResultSet rs = null;
try {
conn = DbUtils.getConnection();
stat = conn.prepareCall("{call pack_stu_test.find_stu(?,?,?,?,?)}");
stat.setInt(1, id);
stat.registerOutParameter(2, Types.VARCHAR);
stat.registerOutParameter(3, Types.INTEGER);
stat.registerOutParameter(4, Types.VARCHAR);
stat.registerOutParameter(5, Types.VARCHAR);
stat.executeQuery();
System.out.println(stat.getString(2));
System.out.println(stat.getInt(3));
System.out.println(stat.getString(4));
System.out.println(stat.getString(5));
} catch (SQLException e) {
e.printStackTrace();
} finally{
DbUtils.close(rs, stat, conn);
}
}