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

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

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

sql 代码

 

1. --用户表信息;  
2. create table wilent_user(  
3.     id number(5) primary key,  
4.     name varchar2(100),  
5.     sex varchar2(1),    --Y为男,F为女;  
6.     group_id number(5),  
7.     teach varchar2(50)  --学历;  
8. );  
9.   
10. --用户组信息;  
11. create table wilent_group(  
12.     id number(5) primary key,  
13.     name varchar2(100)  
14. );  
15.   
16. insert into wilent_group values(1,'组1');  
17. insert into wilent_group values(2,'组2');  
18. insert into wilent_group values(3,'组3');  
19. insert into wilent_group values(4,'组4');  
20. insert into wilent_group values(5,'组5');  
21.   
22. insert into wilent_user values(1,'吴','Y',1,'大专');  
23. insert into wilent_user values(2,'李','Y',1,'大专');  
24. insert into wilent_user values(3,'赵','N',2,'本科');  
25. insert into wilent_user values(4,'金','Y',2,'高中');  
26. insert into wilent_user values(5,'钱','N',2,'大专');  
27. insert into wilent_user values(6,'孙','N',1,'大专');  
28. insert into wilent_user values(7,'高','Y',3,'本科');  
29. insert into wilent_user values(8,'宋','N',3,'高中');  
30. insert into wilent_user values(9,'伍','Y',3,'大专');  
31. insert into wilent_user values(10,'欧','Y',4,'本科');  
32. insert into wilent_user values(11,'庄','N',4,'硕士');  
33. insert into wilent_user values(12,'纪','Y',4,'本科');  
34. insert into wilent_user values(13,'陈','Y',5,'大专');  
35. insert into wilent_user values(14,'龙','N',5,'大专');  
36. insert into wilent_user values(15,'袁','Y',5,'高中');  
37. insert into wilent_user values(16,'杨','Y',1,'本科');  
38. insert into wilent_user values(17,'江','N',1,'大专');  
39. insert into wilent_user values(18,'刘','Y',1,'硕士');  
40. insert into wilent_user values(19,'郭','N',3,'硕士');  
41. insert into wilent_user values(20,'张','Y',3,'大专');  
42. insert into wilent_user values(21,'文','N',3,'硕士');  
43. insert into wilent_user values(22,'李','N',4,'大专');  
44. insert into wilent_user values(23,'梅','Y',4,'本科');  
45. insert into wilent_user values(24,'王','N',4,'大专');  
46. insert into wilent_user values(25,'吕','N',5,'高中');  
47. insert into wilent_user values(26,'范','Y',5,'本科');  
48. insert into wilent_user values(27,'许','N',1,'大专');  
49. insert into wilent_user values(28,'墨','Y',1,'高中');  
50. insert into wilent_user values(29,'孔','N',1,'本科');  
51. insert into wilent_user values(30,'蔡','Y',1,'大专');  

   二. oracle 函数
            1.  返回虚拟表结构.
              

sql 代码

 

1. --自定义一种类型;
2. Create Or Replace Type wilent_row_table As Object  
3. (  
4.        group_name Varchar2(100),  
5.        group_count Number(4),  
6.        male_count Number(4),  
7.        woman_count Number(4),  
8.        da_count Number(4),  
9.        ben_count Number(4)  
10. );  

             2. 虚拟表类型.
            

sql 代码

 

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

             3. oracle 函数  
            

sql 代码

 

1. Create Or Replace Function wilent_group_count1  
2. Return wilent_tab_type   
3. As  
4.   v_tab wilent_tab_type := wilent_tab_type();               
5.   index_max Number(4);                         --wilent_group最大的id;  
6.   index_min Number(4);                         --wilent_group最小的id;  
7.   index_for Number(4);  
8.     
9.   group_name Varchar2(100);  
10.   user_count Number(4);  
11.   male_count Number(4);  
12.   woman_count Number(4);  
13.   da_count Number(4);  
14.   ben_count Number(4);  
15. Begin  
16.      Select Max(g.Id) Into index_max From wilent_group g;  
17.      --dbms_output.put_line(index_max);  
18.      Select Min(g.Id) Into index_min From wilent_group g;  
19.      --dbms_output.put_line(index_min);  
20.      For index_for In Index_min..index_max Loop  
21.          --添加新记录;  
22.          v_tab.Extend;  
23.          Select Name Into group_name From wilent_group Where Id=index_for;  
24.          Select Count(*) Into user_count From wilent_user u, wilent_group g Where u.group_id=g.Id And g.Id=index_for;  
25.          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';  
26.          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';  
27.          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='大专';  
28.          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='本科';  
29.          --把记录写入;  
30.          v_tab(v_tab.Last) := wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);  
31.      End Loop;  
32.        
33.      Return v_tab;  
34. End;  

   三.测试
       1. 测试sql
                   

sql 代码

 

1. select * from table(wilent_group_count1);  

       2. 测试结果   
   

sql 代码

1. GROUP_NAME GROUP_COUNT MALE_COUNT WOMAN_COUNT DA_COUNT BEN_COUNT  
2. 组1      10  6   4   6   2         
3. 组2      3   1   2   1   1         
4. 组3      6   3   3   2   1         
5. 组4      6   3   3   2   3         
6. 组5      5   3   2   2   1