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

最新推荐文章于 2021-02-23 15:49:18 发布

阿钿_

最新推荐文章于 2021-02-23 15:49:18 发布

阅读量322

点赞数

分类专栏： oracle 文章标签： Java Oracle SQL JDBC F#

oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

        一般在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

阿钿_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
java 通过调用存储过程获取结果集

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

扫一扫