FOR ADD_LOT_MAG_REC IN (SELECT OS_LOT_NO, OS_MAGAZINE, OS_WBMC, OS_OSMC
                             FROM FWCATNS_3O_MACH_LOT
                             WHERE OS_LOT_DT between reviewtime and smaxtime  AND OS_ACTIVE_STATUS = 'A')
    
      LOOP
              
      SELECT COUNT(*) INTO iAddcount
      FROM FWCATNS_3O_MACH_BASE
      WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE;
        
      IF iAddcount = 0 THEN

      INSERT INTO FWCATNS_3O_MACH_BASE
                  (OS_LOT_NO, 
                   OS_MAGAZINE,
                   OS_WBMC, 
                   OS_OSMC,
                   )
           VALUES (ADD_LOT_MAG_REC.OS_LOT_NO, 
                   ADD_LOT_MAG_REC.OS_MAGAZINE,
                   ADD_LOT_MAG_REC.OS_WBMC, 
                   ADD_LOT_MAG_REC.OS_OSMC,
                   );             
        
      END IF; 
                      
                        
  END LOOP;

EXECUTE IMMEDIATE ' INSERT INTO FWCATNS_3O_MACH_BASE(OS_LOT_NO,OS_MAGAZINE,OS_WBMC,OS_OSMC,) VALUES (:1,:2,:3,:4)'USING ADD_LOT_MAG_REC.OS_LOT_NO,ADD_LOT_MAG_REC.OS_MAGAZINE,ADD_LOT_MAG_REC.OS_WBMC,ADD_LOT_MAG_REC.OS_OSMC;

------解决方案--------------------
可以只添加新数据，不会覆盖以前的数据。


对于大批量的数据，merge into要比你游标count要快的多！

------解决方案--------------------
先创建个表：
SQL code
MERGE into FWCATNS_3O_MACH_BASE f
using (SELECT OS_LOT_NO, OS_MAGAZINE, OS_WBMC, OS_OSMC
       FROM   FWCATNS_3O_MACH_LOT
       WHERE  OS_LOT_DT between reviewtime and smaxtime
       AND    OS_ACTIVE_STATUS = 'A') t 
on    (f.OS_LOT_NO = t.OS_LOT_NO
       and f.OS_MAGAZINE = t.OS_MAGAZINE)
when  not match then
INSERT INTO FWCATNS_3O_MACH_BASE
                  (OS_LOT_NO, 
                   OS_MAGAZINE,
                   OS_WBMC, 
                   OS_OSMC,
                   )
           VALUES (ADD_LOT_MAG_REC.OS_LOT_NO, 
                   ADD_LOT_MAG_REC.OS_MAGAZINE,
                   ADD_LOT_MAG_REC.OS_WBMC, 
                   ADD_LOT_MAG_REC.OS_OSMC,
                   );

------解决方案--------------------
刚才写错了

SQL code

select *
from 表1
where
not exists
(
    SELECT 1 
      FROM FWCATNS_3O_MACH_BASE
     WHERE OS_LOT_NO = 表1.OS_LOT_NO AND OS_MAGAZINE = 表1.OS_MAGAZINE
)

------解决方案--------------------
     SELECT COUNT(*) INTO iAddcount
     FROM FWCATNS_3O_MACH_BASE
     WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE;

如果是这个慢，加个rownum, 速度就会快好多

    SELECT COUNT(*) INTO iAddcount
     FROM FWCATNS_3O_MACH_BASE
     WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE
       and rownum = 1;


最好能用一个insert搞掂

------解决方案--------------------
不清楚具体表数据量情况，先试一试这样的办法：
INSERT /*+append*/ INTO FWCATNS_3O_MACH_BASE NOLOGGING