Forall Statmant 不起作用 - 未找到数据

Question

>继续得到

"索引 [1] 中的元素不存在"...

但我确实从"dbms_output.put_line (v_lahad_wiz_eq.count);"获得输出，大约 20 行"100"，然后是一行"61"和"0"，所以有数据进入......

declare 
c_limit number :=100;
--set data type and set verible
type lahad_wiz_eq_typ is table of  lahad_wiz_eq%rowtype INDEX BY BINARY_INTEGER;                                    
v_lahad_wiz_eq                                lahad_wiz_eq_typ;
cursor c1 is 
   select *  
   FROM wiz_equip_trx q
   WHERE q.ACCOUNT_NUMBER > 0
   AND q.MANUFACTURER = 'FK';
begin 
open c1 ;
loop 
 fetch c1 
 bulk collect into  v_lahad_wiz_eq
 limit c_limit;
 dbms_output.put_line (v_lahad_wiz_eq.count);
 exit when v_lahad_wiz_eq.count = 0;
end loop;      
close c1; 
dbms_output.put_line (v_lahad_wiz_eq.last);
forall i in 1  ..  100
   insert into lahad_wiz_eq 
   values v_lahad_wiz_eq (i) ;  
dbms_output.put_line ( sql%rowcount);
commit;
end;

Answer 1

您不需要像对普通FETCH INTO语句那样循环通过BULK COLLECT获取记录，因此不需要EXIT WHEN。实际上，您的循环第二次覆盖集合时没有值，exit when v_lahad_wiz_eq.count = 0第二次

满足于退出循环。

SET SERVEROUTPUT ON;
DECLARE
    c_limit          NUMBER := 100;
--set data type and set verible
    TYPE lahad_wiz_eq_typ IS
        TABLE OF lahad_wiz_eq%rowtype INDEX BY BINARY_INTEGER;
    v_lahad_wiz_eq   lahad_wiz_eq_typ;
    CURSOR c1 IS SELECT
        *
           FROM
        wiz_equip_trx q
                 WHERE
        q.account_number > 0
        AND   q.manufacturer = 'FK';
BEGIN
    OPEN c1;
    FETCH c1 BULK COLLECT INTO v_lahad_wiz_eq LIMIT c_limit;
     dbms_output.put_line (v_lahad_wiz_eq.count);
    CLOSE c1;
    dbms_output.put_line(v_lahad_wiz_eq.last);
    FORALL i IN 1..v_lahad_wiz_eq.COUNT
        INSERT INTO lahad_wiz_eq VALUES v_lahad_wiz_eq ( i );
    dbms_output.put_line(SQL%rowcount);
    COMMIT;
END;
/