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
<
1<nextsub>>
2fetch v_ser into v_iAccId,v_iSubId,v_BeginDate,v_EndDate,v_noDetail,v_RowId;
3exit when v_ser%notfound;
4
5for i in 0..length(v_noDetail)/36-1 loop
6v_noowner_detail_param_list(i+1).acc_code:=substr(v_noDetail,i*36+1,7);
7v_noowner_detail_param_list(i+1).fee:=substr(v_noDetail,i*36+8,9);
8v_noowner_detail_param_list(i+1).time:=substr(v_noDetail,i*36+17,1);
9v_noowner_detail_param_list(i+1).cout:=substr(v_noDetail,i*36+18,7);
10v_noowner_detail_param_list(i+1).unit:=substr(v_noDetail,i*36+25,12);
11end loop;
12
13for i in 0..length(v_strDetail)/36-1 loop
14v_owner_detail_param_list(i+1).acc_code:=substr(v_strDetail,i*36+1,7);
15v_owner_detail_param_list(i+1).fee:=substr(v_strDetail,i*36+8,9);
16v_owner_detail_param_list(i+1).time:=substr(v_strDetail,i*36+17,1);
17v_owner_detail_param_list(i+1).cout:=substr(v_strDetail,i*36+18,7);
18v_owner_detail_param_list(i+1).unit:=substr(v_strDetail,i*36+25,12);
19end loop;
20
21for i in v_owner_detail_param_list.first..v_owner_detail_param_list.last loop
22if v_owner_detail_param_list.EXISTS(i) THEN
23if v_noowner_detail_param_list.COUNT>0 then
24for j in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop
25if v_noowner_detail_param_list.EXISTS(j) THEN
26if v_noowner_detail_param_list(j).acc_code=v_owner_detail_param_list(i).acc_code then
27v_owner_detail_param_list(i).fee:=
28v_owner_detail_param_list(i).fee+v_noowner_detail_param_list(j).fee;
29v_owner_detail_param_list(i).time:=
30v_owner_detail_param_list(i).time+v_noowner_detail_param_list(j).time;
31v_owner_detail_param_list(i).cout:=
32v_owner_detail_param_list(i).cout+v_noowner_detail_param_list(j).cout;
33v_owner_detail_param_list(i).unit:=
34v_owner_detail_param_list(i).unit+v_noowner_detail_param_list(j).unit;
35v_noowner_detail_param_list.delete(j);
36end if;
37end if;
38end loop;
39end if;
40end if;
41end loop;
42
43if v_noowner_detail_param_list.COUNT>0 then
44for k in v_noowner_detail_param_list.first..v_noowner_detail_param_list.last loop
45if v_noowner_detail_param_list.EXISTS(k) THEN
46v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).acc_code:=
47v_noowner_detail_param_list(k).acc_code;
48v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).fee:=
49v_noowner_detail_param_list(k).fee;
50v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).time:=
51v_noowner_detail_param_list(k).time;
52v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).cout:=
53v_noowner_detail_param_list(k).cout;
54v_owner_detail_param_list(v_owner_detail_param_list.COUNT + 1).unit:=
55v_noowner_detail_param_list(k).unit;
56end if;
57end loop;
58end if;
59
60for i in v_owner_detail_param_list.first..v_owner_detail_param_list.last loop
61if v_owner_detail_param_list.EXISTS(i) THEN
62v_AccCode:=v_owner_detail_param_list(i).acc_code;
63v_iLen:=length(v_AccCode);
64while 7-v_iLen>0 loop
65v_AccCode:=' '||v_AccCode;
66v_iLen:=length(v_AccCode);
67end loop;
68
69v_strFee:=v_owner_detail_param_list(i).fee;
70v_iLen:=length(v_strFee);
71while 9-v_iLen>0 loop
72v_strFee:=' '||v_strFee ;
73v_iLen:=length(v_strFee);
74end loop;
75
76v_strCount:=v_owner_detail_param_list(i).cout;
77v_iLen:=length(v_strCount);
78while 7-v_iLen>0 loop
79v_strCount:=' '||v_strCount ;
80v_iLen:=length(v_strCount);
81end loop;
82
83v_strUnit:=v_owner_detail_param_list(i).unit;
84v_iLen:=length(v_strUnit);
85while 12-v_iLen>0 loop
86v_strUnit:=' '||v_strUnit ;
87v_iLen:=length(v_strUnit);
88end loop;
89v_NewDetail:=v_NewDetail||v_AccCode||v_strFee
90||v_owner_detail_param_list(i).time||v_strCount||v_strUnit;
91end if;
92end loop;
93v_owner_detail_param_list.delete;
94
95v_NewDetail:='';
96end loop;
97close v_ser;
98
99p_nStatus:=0;
100p_szErrorMsg:='Succeed to finish proc_no2ower.';
101return;
102exception
103when others then
104p_nStatus:=-1;
105p_szErrormsg:='encounter a exception,sqlcode:'||sqlcode
106||',sqlerrm:'||sqlerrm||'v_sql:'||v_strSql;
107return;
108end proc_XXX;</nextsub>