for循环中的语法错误

Question

从第1行开始错误:

DECLARE
   x NUMBER := 0;
   counter NUMBER := 0;
BEGIN
   FOR i IN 1..4 LOOP
      x := x + 1000;
      counter := counter + 1;
      INSERT INTO temp VALUES (x, counter, 'in OUTER loop');
      END;
   END LOOP;
   COMMIT;
END;

错误报告:

ORA-06550: line 11, column 10:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Answer 1

这个插入模式不需要PL/SQL。以下操作在纯SQL中工作:

create table temp(x number, counter number, text varchar2(20));
insert into temp
select (rownum-1)*1000, rownum-1, 'in OUTER loop'
from dual
connect by level <=4;

Answer 2

第11行第一个END;不应该在那里:

DECLARE
   x NUMBER := 0;
   counter NUMBER := 0;
BEGIN
   FOR i IN 1..4 LOOP
      x := x + 1000;
      counter := counter + 1;
      INSERT INTO temp VALUES (x, counter, 'in OUTER loop');
   END LOOP;
   COMMIT;
END;

只有在INSERT周围有子块(例如)时才需要它，例如用于特定的异常处理。

一般来说，最好在INSERT:

中指定列名。

      INSERT INTO temp(col1, col2, col3) VALUES (x, counter, 'in OUTER loop');