Oracle索引表的使用（Table Index）

一个无主数据表转有主的处理示例：

A：sum_noowner_yyyymm（月表）

B：sum_yyyymm_NN（月分表）

对于A.acc_id在master_subNN中有记录的用户，合并其在B表中的字段detail_data

（注意：相同acc_code下的累加，在B.detail_data中没有的acc_code追加在后面），

然后更新到B中，同事delete表A中相应记录。

create or replace procedure proc_no2owner(
        p_iBillMonth    in  number,
        p_tab           in  number,       
        p_nStatus       out number,
        p_szErrorMsg    out varchar2
)
is
        type t_cur is ref cursor;
        v_ser           t_cur;
       v_iAll          number;
       v_iAccId        number(11);
       v_iSubId        number(10);
       v_strDetail     varchar2(4000);
        v_noDetail      varchar2(4000);
        v_NewDetail     varchar2(4000);           
       v_strSql        varchar2(4000); 
        v_BeginDate     varchar2(14);
        v_EndDate       varchar2(14);
        v_RowId         rowid;
        v_num           number;
        v_iLen          number(2);
        v_AccCode       varchar2(7);
        v_strFee        varchar2(9);
        v_strCount      varchar2(7);
        v_strUnit       varchar2(12);
        v_NoWnerCode    number;
        v_SumId         number;
        v_iTemp         number;
        TYPE detail_param IS RECORD
        (
          acc_code         number(7),
          fee              number(9),
          time             number(1),
          cout             number(7),
          unit             number(12)
        );
        TYPE detail_param_list IS TABLE OF detail_param INDEX BY BINARY_INTEGER;
        v_noowner_detail_param_list detail_param_list;
        v_owner_detail_param_list   detail_param_list;
begin
        v_num:=0;
        v_strSql:=' select acc_id,sub_id,to_char(begin_date,''yyyymmddhh24miss''),'
             ||' to_char(end_date,''yyyymmddhh24miss''),detail_data,rowid from sum_noowner_'||p_iBillMonth
                ||' where mod(acc_id,10)='||p_tab;
        open v_ser for v_strSql;
        loop
         <<nextsub>>
         fetch v_ser into v_iAccId,v_iSubId,v_BeginDate,v_EndDate,v_noDetail,v_RowId;
         exit when v_ser%notfound;

       v_strSql:=' select count(1) from master_sub0'||p_tab||' where acc_id='||v_iAccId
                 ||' and sub_id='||v_iSubId||' and sub_status=1';
       execute immediate v_strSql into v_iAll;
       if v_iAll>0 then
        begin
             v_strSql:=' select detail_data from sum_'||p_iBillMonth||'_0'||p_tab
                     ||' where acc_id='||v_iAccId||' and sub_id='||v_iSubId;
          execute immediate v_strSql into v_strDetail;
             exception
            when no_data_found then
                  v_strSql:=' insert into sum_'||p_iBillMonth||'_0'||p_tab
                          ||' values('||v_iAccId||','||v_iSubId||',to_date('||v_BeginDate
                          ||' ,''yyyymmddhh24miss''),to_date('||v_EndDate
                          ||' ,''yyyymmddhh24miss''),'''||v_noDetail||''')';
              execute immediate v_strSql;
                 
                  v_strSql:='delete sum_noowner_'||p_iBillMonth||' where rowid='''||v_RowId||'''';
                execute immediate v_strSql; 
                 
                  v_num:=v_num+1; 
                  goto nextsub;
           end;
       
       for i in 0..length(v_noDetail)/36-1 loop
          v_noowner_detail_param_list(i+1).acc_code:=substr(v_noDetail,i*36+1,7);
            v_noowner_detail_param_list(i+1).fee:=substr(v_noDetail,i*36+8,9);
            v_noowner_detail_param_list(i+1).time:=substr(v_noDetail,i*36+17,1);
            v_noowner_detail_param_list(i+1).cout:=substr(v_noDetail,i*36+18,7); 
            v_noowner_detail_param_list(i+1).unit:=substr(v_noDetail,i*36+25,12);         
       end loop;
    
       for i in 0..length(v_strDetail)/36-1 loop
          v_owner_detail_param_list(i+1).acc_code:=substr(v_strDetail,i*36+1,7);
            v_owner_detail_param_list(i+1).fee:=substr(v_strDetail,i*36+8,9);
            v_owner_detail_param_list(i+1).time:=substr(v_strDetail,i*36+17,1);
            v_owner_detail_param_list(i+1).cout:=substr(v_strDetail,i*36+18,7); 
            v_owner_detail_param_list(i+1).unit:=substr(v_strDetail,i*36+25,12);  
       end loop;    
                
          
           for i in v_owner_detail_param_list.first..v_owner_detail_param_list.last loop
               if v_owner_detail_param_list.EXISTS(i) THEN
                 if v_noowner_detail_param_list.COUNT>0 then
                  for j in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop   
                      if v_noowner_detail_param_list.EXISTS(j) THEN
                         if v_noowner_detail_param_list(j).acc_code=v_owner_detail_param_list(i).acc_code then
                            v_owner_detail_param_list(i).fee:=
                              v_owner_detail_param_list(i).fee+v_noowner_detail_param_list(j).fee;
                            v_owner_detail_param_list(i).time:=
                              v_owner_detail_param_list(i).time+v_noowner_detail_param_list(j).time;
                            v_owner_detail_param_list(i).cout:=
                              v_owner_detail_param_list(i).cout+v_noowner_detail_param_list(j).cout;
                            v_owner_detail_param_list(i).unit:=
                              v_owner_detail_param_list(i).unit+v_noowner_detail_param_list(j).unit;                  
                            v_noowner_detail_param_list.delete(j);
                         end if;   
                      end if;
                   end loop;
                 end if;
               end if;
           end loop;
       
           if v_noowner_detail_param_list.COUNT>0 then
              for k in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop               
                  if v_noowner_detail_param_list.EXISTS(k) THEN
                     v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).acc_code:=
                       v_noowner_detail_param_list(k).acc_code;
                     v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).fee:=
                       v_noowner_detail_param_list(k).fee;
                     v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).time:=
                       v_noowner_detail_param_list(k).time;
                     v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).cout:=
                       v_noowner_detail_param_list(k).cout;
                     v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).unit:=
                       v_noowner_detail_param_list(k).unit;
                  end if;
              end loop;
           end if;
           
           for i in v_owner_detail_param_list.first..v_owner_detail_param_list.last loop                   
             if v_owner_detail_param_list.EXISTS(i) THEN
               v_AccCode:=v_owner_detail_param_list(i).acc_code;
            v_iLen:=length(v_AccCode);   
            while 7-v_iLen>0 loop
             v_AccCode:=' '||v_AccCode;
             v_iLen:=length(v_AccCode);
            end loop;
                      
               v_strFee:=v_owner_detail_param_list(i).fee;
            v_iLen:=length(v_strFee);   
            while 9-v_iLen>0 loop
             v_strFee:=' '||v_strFee ;
             v_iLen:=length(v_strFee);
            end loop;
   
               v_strCount:=v_owner_detail_param_list(i).cout;
            v_iLen:=length(v_strCount);
            while 7-v_iLen>0 loop 
             v_strCount:=' '||v_strCount ;
             v_iLen:=length(v_strCount);
            end loop;
                 
            v_strUnit:=v_owner_detail_param_list(i).unit;
            v_iLen:=length(v_strUnit);
            while 12-v_iLen>0 loop
             v_strUnit:=' '||v_strUnit ;
             v_iLen:=length(v_strUnit);
            end loop;
                 
               v_NewDetail:=v_NewDetail||v_AccCode||v_strFee
                          ||v_owner_detail_param_list(i).time||v_strCount||v_strUnit;                             
              
             end if;
           end loop;   
           v_owner_detail_param_list.delete; 
       
           v_strSql:=' update sum_'||p_iBillMonth||'_0'||p_tab
                   ||' set detail_data='''||v_NewDetail||''' where acc_id='||v_iAccId
                   ||' and sub_id='||v_iSubId;
           execute immediate v_strSql;
                                                    
           v_strSql:=' delete sum_noowner_'||p_iBillMonth||' where rowid='''||v_RowId||'''';
         execute immediate v_strSql; 
         end if;
         v_NewDetail:='';
        v_num:=v_num+1;
        
         if v_num = 1000 then
           commit;
         end if;
        end loop;
        close v_ser; 
        p_nStatus:=0;
       p_szErrorMsg:='Succeed to finish proc_no2ower.';
       return;
exception
 when others then
  p_nStatus:=-1;
    p_szErrormsg:='encounter a exception,sqlcode:'||sqlcode||',sqlerrm:'||sqlerrm||'v_sql:'||v_strSql;
  return;
end proc_no2owner;