自定义聚合函数按位异或，十进制转2进制，以及自定义split

create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
     sum_string number(20),
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
     --对象初始化
     member function ODCIAggregateIterate(self in out string_sum_obj, value in number) return number,
     --聚合函数的迭代方法(这是最重要的方法)
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
     --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
     
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out number ,v_flags in number) return number
     --终止聚集函数的处理,返回聚集函数处理的结果.
)
/
create or replace type body string_sum_obj is
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
     begin
         v_self := string_sum_obj(0);
         return ODCICONST.Success;
     end;
     member function ODCIAggregateIterate(self in out string_sum_obj, value in number) return number is
     begin
           self.sum_string :=self.sum_string+value-bitand(self.sum_string,value);

          return ODCICONST.Success;
     end;
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
     begin
          self.sum_string :=self.sum_string+v_next.sum_string-bitand(self.sum_string,v_next.sum_string);
          
          return ODCICONST.Success;
     end;
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out number ,v_flags in number) return number is
     begin
          return_value:= self.sum_string;
          return ODCICONST.Success;
     end;
end;
/
create or replace function ConnStrSum(value number) return number
     parallel_enable aggregate using string_sum_obj;
    
--十进制转成2进制    
create or replace function number_2_bit(v_num number) return varchar is
v_rtn varchar(2000);
v_n1 number;
v_n2 number;
begin
v_n1 := v_num;
loop
v_n2 := mod(v_n1, 2);
v_n1 := trunc(v_n1 / 2);
v_rtn := to_char(v_n2) || v_rtn;
exit when v_n1 = 0;
end loop;
return v_rtn;
end;

 

--自定义split

create or replace type my_type_split as table of varchar2(288); 

--创建function

create or replace function my_split

(

   p_list varchar2,

   p_sep varchar2 := ','

)  return my_type_split pipelined

 is

   l_idx  pls_integer;

   v_list  varchar2(288) := p_list;

begin

   loop

      l_idx := instr(v_list,p_sep);

      if l_idx > 0 then

          pipe row(substr(v_list,1,l_idx-1));

          v_list := substr(v_list,l_idx+length(p_sep));

      else

          pipe row(v_list);

          exit;

      end if;

   end loop;

   return;

end my_split;

  
    
    
    
    
    
 declare
  ii             number(10);
  str            varchar2(288);
  num            number(10);
  str_s          VARCHAR2(288);
 begin
 ii := 0;
  for cur in (SELECT   t.cmmbsn, ConnStrSum (T.TIME1) c_time1,
              ConnStrSum (T.TIME2) c_time2,
              ConnStrSum (T.TIME3) c_time3,
              ConnStrSum (T.TIME4) c_time4,
              ConnStrSum (T.TIME5) c_time5,
              ConnStrSum (T.TIME6) c_time6,
              ConnStrSum (T.TIME7) c_time7,
              ConnStrSum (T.TIME8) c_time8,
              t.date_time FROM   t_teleview_statistics t
              WHERE   t.date_time > TO_DATE ('2012-08-31', 'yyyy-mm-dd')
              and t.date_time < TO_DATE ('2012-10-01', 'yyyy-mm-dd')  group by t.cmmbsn,t.date_time)
      loop
              str:=number_2_bit(cur.c_time1)||number_2_bit(cur.c_time2)||number_2_bit(cur.c_time3)||number_2_bit(cur.c_time4)
              ||number_2_bit(cur.c_time5)||number_2_bit(cur.c_time6)||number_2_bit(cur.c_time7)||number_2_bit(cur.c_time8);
              select count(*) into num from table(my_split(str,'0')) where column_value is not null;      
              ii:=ii+num;
              end loop;
              dbms_output.put_line(ii);

 END;