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