java 通过调用存储过程获取结果集

最新推荐文章于 2019-08-10 15:44:44 发布

llyzq

最新推荐文章于 2019-08-10 15:44:44 发布

阅读量100

点赞数

分类专栏： Oracle 文章标签： Java Oracle SQL JDBC Blog

本文链接：https://blog.csdn.net/llyzq/article/details/83771736

版权

Oracle 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

转自 http://wilent.iteye.com/blog/40704

        一般在java中,数据查询是通过Statement, PreparedStatement获取结果集,今天向大家介绍通过CallableStatement调用存储过程,从而获取结果集.
       本文是所用的数据库为oracle.
      一. 测试数据库表:

sql 代码

create table wilent_user(
id number(5) primary key ,
name varchar2(100),
sex varchar2(1), --Y为男,F为女;
group_id number(5),
teach varchar2(50) --学历;
);
create table wilent_group(
id number(5) primary key ,
name varchar2(100)
);
insert into wilent_group values (1,'组1');
insert into wilent_group values (2,'组2');
insert into wilent_group values (3,'组3');
insert into wilent_group values (4,'组4');
insert into wilent_group values (5,'组5');
insert into wilent_user values (1,'吴','Y',1,'大专');
insert into wilent_user values (2,'李','Y',1,'大专');
insert into wilent_user values (3,'赵','N',2,'本科');
insert into wilent_user values (4,'金','Y',2,'高中');
insert into wilent_user values (5,'钱','N',2,'大专');
insert into wilent_user values (6,'孙','N',1,'大专');
insert into wilent_user values (7,'高','Y',3,'本科');
insert into wilent_user values (8,'宋','N',3,'高中');
insert into wilent_user values (9,'伍','Y',3,'大专');
insert into wilent_user values (10,'欧','Y',4,'本科');
insert into wilent_user values (11,'庄','N',4,'硕士');
insert into wilent_user values (12,'纪','Y',4,'本科');
insert into wilent_user values (13,'陈','Y',5,'大专');
insert into wilent_user values (14,'龙','N',5,'大专');
insert into wilent_user values (15,'袁','Y',5,'高中');
insert into wilent_user values (16,'杨','Y',1,'本科');
insert into wilent_user values (17,'江','N',1,'大专');
insert into wilent_user values (18,'刘','Y',1,'硕士');
insert into wilent_user values (19,'郭','N',3,'硕士');
insert into wilent_user values (20,'张','Y',3,'大专');
insert into wilent_user values (21,'文','N',3,'硕士');
insert into wilent_user values (22,'李','N',4,'大专');
insert into wilent_user values (23,'梅','Y',4,'本科');
insert into wilent_user values (24,'王','N',4,'大专');
insert into wilent_user values (25,'吕','N',5,'高中');
insert into wilent_user values (26,'范','Y',5,'本科');
insert into wilent_user values (27,'许','N',1,'大专');
insert into wilent_user values (28,'墨','Y',1,'高中');
insert into wilent_user values (29,'孔','N',1,'本科');
insert into wilent_user values (30,'蔡','Y',1,'大专');

二. oracle 存储过程

sql 代码

--自定义类型;
Create Or Replace Type wilent_row_table As Object
(
group_name Varchar2(100),
group_count Number(4),
male_count Number(4),
woman_count Number(4),
da_count Number(4),
ben_count Number(4)
);
/
--定义一个嵌套表类型;
Create Or Replace Type wilent_tab_type Is Table Of wilent_row_table;
/
--返回一个游标类型;
Create Or Replace Package wilent_types As
Type cursor_type Is Ref Cursor ;
End wilent_types;
/
Create Or Replace Procedure wilent_group_count(recordSet Out wilent_types.cursor_type)
As
v_tab wilent_tab_type := wilent_tab_type();
index_max Number(4); --wilent_group最大的id;
index_min Number(4); --wilent_group最小的id;
index_for Number(4);
group_name Varchar2(100);
user_count Number(4);
male_count Number(4);
woman_count Number(4);
da_count Number(4);
ben_count Number(4);
Begin
dbms_output.put_line('as ');
Select Max (g.Id) Into index_max From wilent_group g;
--dbms_output.put_line(index_max);
Select Min (g.Id) Into index_min From wilent_group g;
--dbms_output.put_line(index_min);
For index_for In Index_min..index_max Loop
--添加新记录;
v_tab.Extend;
Select Name Into group_name From wilent_group Where Id=index_for;
Select Count (*) Into user_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for;
Select Count (*) Into male_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And sex='Y';
Select Count (*) Into woman_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And sex='N';
Select Count (*) Into da_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And teach='大专';
Select Count (*) Into ben_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for And teach='本科';
--把记录写入;
v_tab(v_tab.Last ) := wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);
End Loop;
--把记录放在游标里;
Open recordset For
--Table(Cast(v_tab As wilent_tab_type))目的是把v_tab强转为wilent_tab_type表
Select group_name,group_count ,male_count ,woman_count ,da_count ,ben_count From Table ( Cast (v_tab As wilent_tab_type)) Order By group_name;
End wilent_group_count;
/
--测试wilent_group_count();
declare
recordset wilent_types.cursor_type;
Begin
wilent_group_count(recordset);
End ;

三. java代码:

java 代码

package com.wilent.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleTypes;
import com.wilent.db.ConnectionManager;
public class TestProcedure {
public static void main(String[] args) {
//获得conn连接,读者可以自行写;
Connection conn = ConnectionManager.getConnection();
ResultSet rs = null ;
try {
CallableStatement proc = conn.prepareCall("{call wilent_group_count(?)}" );
proc.registerOutParameter(1 , OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(1 );
System.out.println("组名\t总计\t男性\t女性\t大专\t本科" );
while (rs.next())
{
StringBuffer buffer = new StringBuffer();
buffer.append(rs.getString("group_name" ));
buffer.append("\t" );
buffer.append(rs.getInt("group_count" ));
buffer.append("\t" );
buffer.append(rs.getInt("male_count" ));
buffer.append("\t" );
buffer.append(rs.getInt("woman_count" ));
buffer.append("\t" );
buffer.append(rs.getInt("da_count" ));
buffer.append("\t" );
buffer.append(rs.getInt("ben_count" ));
System.out.println(buffer.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

    四. 运行结果

    组名    总计    男性    女性    大专    本科
    组1    10        6    4      6      2
    组2    3         1      2      1      1
    组3    6         3      3      2      1
    组4    6         3      3      2      3
    组5    5         3      2      2      1