oracle存储过程跟函数的应用实例，复杂查询并赋值--------------------------------gxy

野性酷女 2022-05-11 07:33 156阅读 0赞

function getSql(Clpfbh1 varchar2,Clpfbh2 varchar2,Clpfbh3 varchar2,Clpfbh4 varchar2) return varchar2 is 
         sqlDeal varchar2(3000) := 'create or replace view xmxxdb_v as ';
         sqlgroupby number := -1;
         wlbm_num1 number;wlbm_num2 number;wlbm_num3 number;wlbm_num4 number;wlbm_num5 number;wlbm_num6 number;
         wlbm_num7 number;wlbm_num8 number;wlbm_num9 number;wlbm_num10 number;wlbm_num11 number;wlbm_num12 number;
         wlbm_num13 number;wlbm_num14 number;wlbm_num15 number;wlbm_num16 number;wlbm_num17 number;wlbm_num18 number;
         wlbm_num19 number;wlbm_num20 number;
         begin
            
            select count(t.wlbm1) into wlbm_num1
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num1 >= 1) then
                 sqlDeal := sqlDeal || ' select t.wlbm1, t.wlms1,  listagg(t.fs1, chr(127)) within GROUP(order by t.wlbm1, t.wlms1) fs1 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm1, t.wlms1';
                 sqlgroupby := 1;
             end if;
             
             select count(t.wlbm2) into wlbm_num2
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num2 >= 1) then
              if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm2, t.wlms2, listagg(t.fs2, chr(127)) within GROUP(order by t.wlbm2, t.wlms2) fs2 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm2, t.wlms2';
                
             end if;
             
             
             select count(t.wlbm3) into wlbm_num3
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num3 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm3, t.wlms3,  listagg(t.fs3, chr(127)) within GROUP(order by t.wlbm3, t.wlms3) fs3 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm3, t.wlms3';
                
             end if;
             
             
             select count(t.wlbm4) into wlbm_num4
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num4 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm4, t.wlms4,listagg(t.fs4, chr(127)) within GROUP(order by t.wlbm4, t.wlms4) fs4 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm4, t.wlms4';
                 
             end if;
             
             select count(t.wlbm5) into wlbm_num5
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num5 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm5, t.wlms5,listagg(t.fs5, chr(127)) within GROUP(order by t.wlbm5, t.wlms5) fs5 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm5, t.wlms5';
                  
             end if;
             
             select count(t.wlbm6) into wlbm_num6
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num6 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm6, t.wlms6,listagg(t.fs6, chr(127)) within GROUP(order by t.wlbm6, t.wlms6) fs6 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm6, t.wlms6';
                 
             end if;
             
             select count(t.wlbm7) into wlbm_num7
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num7 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm7, t.wlms7,  listagg(t.fs7, chr(127)) within GROUP(order by t.wlbm7, t.wlms7) fs7 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm7, t.wlms7';
                 
             end if;
             
             select count(t.wlbm8) into wlbm_num8
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num8 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm8, t.wlms8,  listagg(t.fs8, chr(127)) within GROUP(order by t.wlbm8, t.wlms8) fs8 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm8, t.wlms8';
                 
             end if;
             
             select count(t.wlbm9) into wlbm_num9
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num9 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm9, t.wlms9,  listagg(t.fs9, chr(127)) within GROUP(order by t.wlbm9, t.wlms9) fs9 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm9, t.wlms9';
                
             end if;
             
             select count(t.wlbm10) into wlbm_num10
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num10 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                 sqlDeal := sqlDeal || ' select t.wlbm10, t.wlms10,  listagg(t.fs10, chr(127)) within GROUP(order by t.wlbm10, t.wlms10) fs10 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm10, t.wlms10';
                
             end if;
             
             select count(t.wlbm11) into wlbm_num11
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num11 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm11, t.wlms11,  listagg(t.fs11, chr(127)) within GROUP(order by t.wlbm11, t.wlms11) fs11 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm11, t.wlms11';
               
             end if;
             
             select count(t.wlbm12) into wlbm_num12
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num12 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm12, t.wlms12,  listagg(t.fs12, chr(127)) within GROUP(order by t.wlbm12, t.wlms12) fs12 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm12, t.wlms12';
                
             end if;
             
             select count(t.wlbm13) into wlbm_num13
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num13 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm13, t.wlms13,  listagg(t.fs13, chr(127)) within GROUP(order by t.wlbm13, t.wlms13) fs13 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm13, t.wlms13';
                
             end if;
             
             select count(t.wlbm14) into wlbm_num14
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num14 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm14, t.wlms14,  listagg(t.fs14, chr(127)) within GROUP(order by t.wlbm14, t.wlms14) fs14 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm14, t.wlms14';
                
             end if;
             
             select count(t.wlbm15) into wlbm_num15
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num15 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm15, t.wlms15,  listagg(t.fs15, chr(127)) within GROUP(order by t.wlbm15, t.wlms15) fs15 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm15, t.wlms15';
                
             end if;
             
             select count(t.wlbm16) into wlbm_num16
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num16 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm16, t.wlms16,  listagg(t.fs16, chr(127)) within GROUP(order by t.wlbm16, t.wlms16) fs16 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm16, t.wlms16';
                
             end if;
             
             select count(t.wlbm17) into wlbm_num17
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num17 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm17, t.wlms17,  listagg(t.fs17, chr(127)) within GROUP(order by t.wlbm17, t.wlms17) fs17 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm17, t.wlms17';
               
             end if;
             
             select count(t.wlbm18) into wlbm_num18
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num18 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm18, t.wlms18,  listagg(t.fs18, chr(127)) within GROUP(order by t.wlbm18, t.wlms18) fs18 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm18, t.wlms18';
                
             end if;
             
             select count(t.wlbm19) into wlbm_num19
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num19 >= 1) then
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm19, t.wlms19,  listagg(t.fs19, chr(127)) within GROUP(order by t.wlbm19, t.wlms19) fs19 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm19, t.wlms19';
                
             end if;
             
             select count(t.wlbm20) into wlbm_num20
             from user_clpfd t
             where t.clpfbh in (Clpfbh1,Clpfbh2,Clpfbh3,Clpfbh4);
             
             if(wlbm_num20 >= 1) then 
             if sqlgroupby >0 then 
                 sqlDeal := sqlDeal || ' union ' ;
              else
                 sqlgroupby := 1;  
              end if;
                sqlDeal := sqlDeal || ' select t.wlbm20, t.wlms20,  listagg(t.fs20, chr(127)) within GROUP(order by t.wlbm20, t.wlms20) fs20 from user_clpfd t where t.clpfbh in ('''||Clpfbh1||''','''||Clpfbh2||''','''||Clpfbh3||''','''||Clpfbh4||''') group by t.wlbm20, t.wlms20';
                
             end if;
             
             
             return sqlDeal;
         end;