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

        在上个例子中,我们演示了通过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;

   三.测试
       1. 测试sql
                   

sql 代码

select * from table(wilent_group_count1);

       2. 测试结果   
   

sql 代码

GROUP_NAME GROUP_COUNT MALE_COUNT WOMAN_COUNT DA_COUNT BEN_COUNT
组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