declare
CURSOR branch_code_cursor is select branch_code from params.tb_branch where branch_code<>8898 and branch_code<>8899 order by branch_code;
serial_table serial_no_type;
v_branch_code char(4);
begin
open branch_code_cursor;
Loop
fetch branch_code_cursor into v_branch_code;
exit when branch_code_cursor%notfound;
select serial_no into serial_table from
(select a.*,b.entry_value as branch_code from hs_his.hisdeliverx a,vw_subbranch b
where a.branch_no=b.branch_no and a.init_date=20071214 and b.entry_value=v_branch_code
and a.business_flag in (select branch_function_flag from params.tb_function_flag_map where branch_version = 16 and branch_func_type='5'))
c
where c.serial_no not in
(select br_serial_no from history.tb_stk_cap_chg where tradedate='20071214' and branch_code=v_branch_code);
for i in 1..serial_table.count Loop
dbms_output.put_line(v_branch_code||':'||serial_table(i));
end Loop;
end Loop;
close branch_code_cursor;
End;其中的serial_no_type是这样定义的create type serial_no_type is table of number(10);
CURSOR branch_code_cursor is select branch_code from params.tb_branch where branch_code<>8898 and branch_code<>8899 order by branch_code;
serial_table serial_no_type;
v_branch_code char(4);
begin
open branch_code_cursor;
Loop
fetch branch_code_cursor into v_branch_code;
exit when branch_code_cursor%notfound;
select serial_no into serial_table from
(select a.*,b.entry_value as branch_code from hs_his.hisdeliverx a,vw_subbranch b
where a.branch_no=b.branch_no and a.init_date=20071214 and b.entry_value=v_branch_code
and a.business_flag in (select branch_function_flag from params.tb_function_flag_map where branch_version = 16 and branch_func_type='5'))
c
where c.serial_no not in
(select br_serial_no from history.tb_stk_cap_chg where tradedate='20071214' and branch_code=v_branch_code);
for i in 1..serial_table.count Loop
dbms_output.put_line(v_branch_code||':'||serial_table(i));
end Loop;
end Loop;
close branch_code_cursor;
End;其中的serial_no_type是这样定义的create type serial_no_type is table of number(10);
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货