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代码实现
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);
- }
- }