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

功能：将某个入库批次中号码数量等于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;
         [u]dbms_output.put_line[/u](row1);
         
         declare row2 number;
         cursor cr2 is 
         select * from sim_card_detail_ where zha_num_=[u]x.zha_num_[/u] 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;