Oracle索引表的使用(Table Index)

王朝mssql·作者佚名  2006-12-17
窄屏简体版  字體: |||超大  

Oracle索引表的使用(Table Index)

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航