如何在Oracle中使用for循环进行插入？

Question

我在使用以下for循环向Oracle插入100万条记录时遇到了问题。插入语句本身是可以工作的，但在循环内部却不行。我在这里做错了什么？

BEGIN
    FOR v_LoopCounter IN 385000000..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');
        COMMIT;
    END LOOP;
END;

- Gunnlaugur

---

这句话的意思是什么，“它不起作用”？有错误吗？如果有，是哪一个？如果没有，那么呢？ - Littlefoot

Answer 1

不要那样做，尤其是不要在循环内使用 COMMIT。

使用行生成器；有许多技术可供选择，其中之一是这个：

SQL> create table test (snb number, real_exch varchar2(20));

Table created.

SQL> insert into test (snb, real_exch)
  2    select 385000000 + level - 1, 'GSMB'
  3    from dual
  4    connect by level <= 10;        --> you'd put a million here

10 rows created.

SQL> select * from test;

       SNB REAL_EXCH
---------- --------------------
 385000000 GSMB
 385000001 GSMB
 385000002 GSMB
 385000003 GSMB
 385000004 GSMB
 385000005 GSMB
 385000006 GSMB
 385000007 GSMB
 385000008 GSMB
 385000009 GSMB

10 rows selected.

SQL>

- Littlefoot

---

为什么在for循环中提交代码是不好的？ - Phaki

@Paki，如果你必须回滚整个事务，但在循环的中途发生错误，那么你就无法回滚了，因为部分更改已经被提交。如果循环运行次数不是很多（取决于它所做的工作），则在循环外提交。如果有大量的行受到影响，则考虑将整个过程分批处理，并在每个批次完成后进行提交。此外，在循环内提交（通常？）最终会导致ORA-01555快照太旧的错误。 - Littlefoot

Answer 2

这对我很有用：

BEGIN
FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');

END LOOP;
COMMIT;
END;

- Satish Ingle

---

期望对代码执行过程进行简要说明，这将有助于读者理解问题和答案之间的差异。 - krishh

Answer 3

您不需要使用循环来插入数据。尝试使用直接的SQL语句，这将以优雅的方式为您提供所需内容。

 INSERT 
   INTO PORT (snb
              , real_exch
              , act_exch
              , user_type
              , status_id
              , category_id
              , assignable
             ) 
    select * 
      from (
            SELECT row_number() over(order by 1) + 385000000 -1 as loop_val
                   ,'GSMB'
                   ,'GSMB'
                   ,'GSM'
                   ,0
                   ,90
                   ,'0'
               FROM (select level as lvl
                       from dual
                     connect by level<=1000)a
               JOIN (select level as lvl
                       from dual
                     connect by level<=1000)b
                 ON 1=1  
            )x
       where x.loop_val<=385999999;

commit;

- George Joseph

Answer 4

尝试：

BEGIN
    FOR v_LoopCounter IN 1..1000000 LOOP
        INSERT INTO PORT (snb, real_exch, act_exch, user_type, status_id, category_id, assignable) 
            VALUES (TO_CHAR(v_LoopCounter),'GSMB','GSMB','GSM',0,90,'0');
        COMMIT;
END LOOP;

- Bikash Ranjan Bhoi

---

我需要将v_LoopCounter的初始值设为385000000，并递增到385999999。 - Gunnlaugur