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
o 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
v_owner_detail_param_list.EXISTS(i) THEN v_noowner_detail_param_list.COUNT>0 thenfor j in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop
v_noowner_detail_param_list.EXISTS(j) THEN v_noowner_detail_param_list(j).acc_code=v_owner_detail_param_list(i).acc_code thenv_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
; end
; end loop;
end
;end
;
end loop;
v_noowner_detail_param_list.COUNT>0 thenfor k in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop
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
;end loop;
end
;for i in v_owner_detail_param_list.first..v_owner_detail_param_list.last loop
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
;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.\';
;exception
when others then
p_nStatus:=-1;
p_szErrormsg:=\'encounter a exception,sqlcode:\'||sqlcode
||\',sqlerrm:\'||sqlerrm||\'v_sql:\'||v_strSql;
;end proc_XXX;
最新评论