sql - ORACLE - 过程中出现错误 PLS-00103

Question

为了练习 PL/SQL，我根据 ORACLE 文档编写了这个简单的过程:

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
  loops NUMBER;  
BEGIN  
  loops := badges;  
  loops := loops - 1;  
  BEGIN LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', '01-JAN-01');       
    loops := loops - 1;  
    EXIT WHEN loops < 0;  
  END LOOP;  
END;  
/

这是我的 BADGE 表:

CREATE TABLE BADGE  
(badge_id NUMBER PRIMARY KEY,   
name VARCHAR(20),   
surname VARCHAR(20) NOT NULL,   
birthday DATE);

最后这是我的顺序:

CREATE SEQUENCE BADGE_ID_SEQUENCE   
start with 1      
increment by 1    
nocache   
nocycle;   

但是，程序编译时出现以下错误:

LINE/COL ERROR
-------- -----------------------------------------------------------------
11/5     PLS-00103: Encountered the symbol "end-of-file" when expecting
        one of the following:
        ( begin case declare end exception exit for goto if loop mod
        null pragma raise return select update while with
        <an identifier> <a double-quoted delimited-identifier>
        <a bind variable> << continue close current delete fetch lock
        insert open rollback savepoint set sql execute commit forall
        merge pipe purge

我做错了什么？

Answer 1

syntax for a loop是 LOOP ... END LOOP; 没有 BEGIN。

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
  loops NUMBER;  
BEGIN  
  loops := badges;  
  loops := loops - 1;  
  LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', DATE '2001-01-01');       
    loops := loops - 1;  
    EXIT WHEN loops < 0;  
  END LOOP;  
END;  
/

然而，一个更简单的版本是:

create procedure BADGE_PROCEDURE (badges NUMBER) AS  
BEGIN  
  FOR loops IN 1 .. badges LOOP  
    insert into BADGE values (BADGE_ID_SEQUENCE.nextval, 'prova', 'prova', DATE '2001-01-01');       
  END LOOP;  
END;  
/