Oracle游标（Cursor）的应用（一）

thzmlnnndh

功能：将某个入库批次中号码数量等于40的札拆分成两札

declare row1 number;
cursor cr1 is
--取得要拆分的札库存记录
select * from sim_card_zha_resource_ where zha_num_ in(
select zha_num_ from (
select zha_num_,count(id_) amount_ from sim_card_detail_
where status_ not in('RESOURCE_STATUS_12','RESOURCE_STATUS_14')
and zha_num_ like '20080328135622_%'
having count(id_)=40
group by zha_num_)
) order by id_ asc;
begin
row1 := 100;
for x in cr1 --对数据集进行遍历处理
loop
row1 := row1 + 1;
dbms_output.put_line(row1);
declare row2 number;
cursor cr2 is
select * from sim_card_detail_ where zha_num_=x.zha_num_ order by id_ asc;
begin
row2 := 0;
for y in cr2
loop
row2 := row2 + 1;
--从第21条记录开始，这些记录将被分配到新的札中。这里需要添加新的札记录。
if(row2 = 21) then
insert into sim_card_zha_resource_(id_,send_org_id_,receive_org_id_,card_type_id_,box_num_,zha_num_,amount_,is134_,input_date_time_,last_active_date_,status_,input_sid_)
values(row1,x.send_org_id_,x.receive_org_id_,x.card_type_id_,x.box_num_,x.zha_num_ || '_2',20,x.is134_,x.input_date_time_,x.last_active_date_,x.status_,x.input_sid_);
update sim_card_zha_resource_ set amount_=20 where id_=x.id_;
end if;
--更新号码的札编号
if(row2 > 20) then
update sim_card_detail_ set zha_num_=x.zha_num_ || '_2' where id_=y.id_;
end if;
end loop;
dbms_output.put_line(x.zha_num_ || '_2');
end;
end loop;
end;