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

 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;