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