游标的问题

Question

begin
select ens_id,ens_date,ens_stoteroom into v_ens_id,v_ens_date,v_storeroom_id from enteistore_head where ens_id=ensbill_id ;
open ensd_cursor for select ensd_id,item_id,batch_id,ens_unit,ensd_quantity,unit_price,produce_date,test_sign from enterstore_detail 
where ens_id=ensbill_id ; 
loop
  fetch ensd_cursor into v_ensd_id,v_item_id,v_batch,v_unit_id,v_ensdqty,v_price,v_produce_date,v_testsign;
  insert into storage_list(instore_date,sroreroom_id,item_id,quantity,item_unit,unit_price,batch_id,stored_id)values(v_ens_date,v_storeroom_id,v_item_id,v_ensdqty,v_unit_id,v_price,v_batch,'1');
  exit when ensd_cursor%notfound;
end loop;end;
执行的时候，总是在最后一行执行2遍为什么？

Answer 1

--EXIT WHEN退出循环紧跟在FETCH..INTO之后
--否则最后一次已经NOTFOUND了还是执行了INSERT INTO
BEGIN
  SELECT ens_id, ens_date, ens_stoteroom
    INTO v_ens_id, v_ens_date, v_storeroom_id
    FROM enteistore_head
   WHERE ens_id = ensbill_id;  OPEN ensd_cursor FOR
    SELECT ensd_id,
           item_id,
           batch_id,
           ens_unit,
           ensd_quantity,
           unit_price,
           produce_date,
           test_sign
      FROM enterstore_detail
     WHERE ens_id = ensbill_id;  LOOP
    FETCH ensd_cursor
      INTO v_ensd_id,
           v_item_id,
           v_batch,
           v_unit_id,
           v_ensdqty,
           v_price,
           v_produce_date,
           v_testsign;
    EXIT WHEN ensd_cursor%NOTFOUND;
    INSERT INTO storage_list
      (instore_date, sroreroom_id, item_id, quantity, item_unit, unit_price,
       batch_id, stored_id)
    VALUES
      (v_ens_date, v_storeroom_id, v_item_id, v_ensdqty, v_unit_id, v_price,
       v_batch, '1');
  END LOOP;END;

Answer 2

当取到最后一条记录的时候，你执行了insert into命令，
这时还没有进行下一次取数据，缓存里面还存放着最后一条记录，
但是循环在此检测取出的数据时，发现缓存里面有一条(组后一条)，
于是又进行了insert into
接着才获取下一行数据，但是这是nodata了，才跳出loop，所以最后你将会得到两条末尾数据，
EXIT WHEN ensd_cursor%NOTFOUND;一般写在取数据之后，所有操作之前

Answer 3

open ensd_cursor1 for select ensd_id,item_id,batch_id,ens_unit,ensd_quantity,unit_price,produce_date,test_sign,test_qty from enterstore_detail 
where ens_id=ensbill_id and test_sign='1'; 
  loop
    fetch ensd_cursor1 into v_ensd_id,v_item_id,v_batch,v_unit_id,v_ensdqty,v_price,v_produce_date,v_testsign,v_ensd_qty_pass;
    exit when ensd_cursor1%notfound;
    
    if v_ensdqty>v_ensd_qty_pass then
    v_ensd_qty_nopass:=v_ensdqty-v_ensd_qty_pass;
    insert into storage_list(instore_date,sroreroom_id,item_id,quantity,item_unit,unit_price,batch_id,stored_id,state,test_id)values(v_ens_date,v_storeroom_id,v_item_id,v_ensd_qty_pass,v_unit_id,v_price,v_batch,'1','1','1');
    insert into storage_list(instore_date,sroreroom_id,item_id,quantity,item_unit,unit_price,batch_id,stored_id,state,test_id)values(v_ens_date,v_storeroom_id,v_item_id,v_ensd_qty_nopass,v_unit_id,v_price,v_batch,'1','0','0');
    
    open snm_cursor1 for select sn_op_id_z,sn_no,sn_id_z,sn_bill_id,testok_id  from sn_manage where sn_op_id=v_ensd_id and testok_id='1';
    loop
      fetch snm_cursor1 into V_sn_op_id_z,v_snno,v_sn_id_z,v_sn_bill_id,v_testok_id;
      exit when snm_cursor1%notfound;
      insert into sn_manage(sn_type,sn_op_id_z,sn_no,sn_id_z,sn_op_id,sn_id,sn_bill_id,testok_id) values('sto',v_sn_op_id_z,v_snno,v_sn_id_z,v_ensd_id,v_sn_id,v_ens_id,'1');
    end loop;
    
    open snm_cursor2 for select sn_op_id_z,sn_no,sn_id_z,sn_bill_id,testok_id  from sn_manage where sn_op_id=v_ensd_id and testok_id='0';
    loop
      fetch snm_cursor2 into V_sn_op_id_z,v_snno,v_sn_id_z,v_sn_bill_id,v_testok_id;
      exit when snm_cursor2%notfound;
      insert into sn_manage(sn_type,sn_op_id_z,sn_no,sn_id_z,sn_op_id,sn_id,sn_bill_id,testok_id) values('sto',v_sn_op_id_z,v_snno,v_sn_id_z,v_ensd_id,v_sn_id,v_ens_id,'0');
    end loop;
    
    
    
    
    else
    insert into storage_list(instore_date,sroreroom_id,item_id,quantity,item_unit,unit_price,batch_id,stored_id,state,test_id)values(v_ens_date,v_storeroom_id,v_item_id,v_ensd_qty_pass,v_unit_id,v_price,v_batch,'1','1','1');
   /* open snm_cursor3 for select sn_op_id_z,sn_no,sn_id_z,sn_bill_id,testok_id  from sn_manage where sn_op_id=v_ensd_id and testok_id='1';
   */ /*loop
      fetch snm_cursor3 into V_sn_op_id_z,v_snno,v_sn_id_z,v_sn_bill_id,v_testok_id;
      exit when snm_cursor3%notfound;
      insert into sn_manage(sn_type,sn_op_id_z,sn_no,sn_id_z,sn_op_id,sn_id,sn_bill_id,testok_id) values('sto',v_sn_op_id_z,v_snno,v_sn_id_z,v_ensd_id,v_sn_id,v_ens_id,'1');
    end loop;*/
   /*  end loop;*/
    end if;
    
  end loop;

Answer 4

--你说的游标嵌套叫做游标子查询
--比如:
declare
       c_inner dept%rowtype;
......
cursor c_outer
is
  select e.name,
  cursor (
  select * from dept d
  where d.deptno=e.deptno)
  from emp e
  where sal>2000
......
 open c_outer
 loop
     ......
 end loop;

Answer 5

你exit when 要跟在FETCH之后嘛