Oracle索引表的使用（Table Index）

create or replace procedure proc_XXX(
        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_iAclearcase/" target="_blank" >ccId        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_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_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;  
       
     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_NewDetail:='';
        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_XXX;

原文转自：http://www.ltesting.net