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_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_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;   
     &

[1] [2]  下一页