通过oracle实现 : select * from 存储教程(或函数) 例子

关注

最新推荐文章于 2026-07-29 15:01:14 发布 1k 阅读 AI 写同款· GEO 优化 ›

        在上个例子中,我们演示了通过java调用存储过程,获取数据集.可能有人觉得这个调用有点麻烦,本文又演示多个例子.

        示范:   "select * from table(wilent_group_count1)";
       其中wilent_group_count1为oracle函数,返回一个用户所需的虚拟表,再通过table()函数转换成表.

     步骤:
    一. 数据库表:

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 函数
            1. 返回虚拟表结构.

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)
);

2. 虚拟表类型.

sql 代码

--定义一种嵌套类型;
Create Or Replace Type wilent_tab_type Is Table Of wilent_row_table;

3. oracle 函数

sql 代码

Create Or Replace Function wilent_group_count1
Return wilent_tab_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
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;
Return v_tab;
End;