PLS-00103在过程中创建表时编译错误

Question

我正在编写一些为数据库创建表的PL/SQL代码。

当我尝试跟踪代码时，我一直收到错误PLS-00103。我查看了其他线程，它表明缺少循环的开始，但我看不到可能在哪里。

设置服务器输出

执行THELO；

 CREATE OR REPLACE PROCEDURE THELLO AS
    WELCOME VARCHAR(50);
BEGIN
    CREATE TABLE NEW_PART(
P_PARTKEY   NUMBER(12)  NOT NULL,
P_NAME      VARCHAR(55) NOT NULL,
P_MFGR      VARCHAR(25) NOT NULL,
P_BRAND     CHAR(10)    NOT NULL,
P_TYPE      VARCHAR(25) NOT NULL,
P_SIZE      NUMBER(12)  NOT NULL,
P_CONTAINER     CHAR(10)    NOT NULL,
P_RETAILPRICE   NUMBER(12,2)    NOT NULL,
P_COMMENT   VARCHAR(23) NOT NULL,
    CONSTRAINT NEW_PART_PEKEY PRIMARY KEY (P_PARTKEY),
    CONSTRAINT NEW_PART_CHECK1 CHECK(P_PARTKEY >= 0),
    CONSTRAINT NEW_PART_CHECK2 CHECK(P_SIZE >= 0),
    CONSTRAINT NEW_PART_CHECK3 CHECK(P_RETAILPRICE >= 0) );

CREATE TABLE NEW_SUPPLIER(
S_SUPPKEY   NUMBER(12)  NOT NULL,
S_NAME      CHAR(25)    NOT NULL,
S_ADDRESS   VARCHAR(40) NOT NULL,
S_NATIONKEY NUMBER(12)  NOT NULL,
S_PHONE     CHAR(15)    NOT NULL,
S_ACCTBAL   NUMBER(12,2)    NOT NULL,
S_COMMENT   VARCHAR(101)    NOT NULL,
    CONSTRAINT NEW_SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY),
    CONSTRAINT NEW_SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY)
        REFERENCES NATION(N_NATIONKEY),
    CONSTRAINT NEW_SUPPLIER_CHECK1 CHECK(S_SUPPKEY >= 0) );

CREATE TABLE NEW_PARTSUPP(
PS_PARTKEY  NUMBER(12)  NOT NULL,
PS_SUPPKEY  NUMBER(12)  NOT NULL,
PS_AVAILQTY NUMBER(12)  NOT NULL,
PS_SUPPLYCOST   NUMBER(12,2)    NOT NULL,
PS_COMMENT  VARCHAR(199)    NOT NULL,
    CONSTRAINT NEW_PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY)
        REFERENCES NEW_PART(P_PARTKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY)
        REFERENCES NEW_SUPPLIER(S_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_CHECK1 CHECK(PS_PARTKEY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK2 CHECK(PS_AVAILQTY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK3 CHECK(PS_SUPPLYCOST >= 0) );

DECLARE

BEGIN

  FOR PARTKEY IN (SELECT DISTINCT PS_PARTKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_PART ( SELECT * 
                           FROM PART
                           WHERE P_PARTKEY = PARTKEY.PS_PARTKEY );
    COMMIT;
  END LOOP;

  FOR SUPPKEY IN (SELECT DISTINCT PS_SUPPKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_SUPPLIER ( SELECT * 
                               FROM SUPPLIER
                               WHERE S_SUPPKEY = SUPPKEY.PS_SUPPKEY );
    COMMIT;
  END LOOP;

  INSERT INTO NEW_PARTSUPP ( SELECT * 
                             FROM PARTSUPP 
                 WHERE PS_PARTKEY IN ( SELECT P_PARTKEY
                                                   FROM NEW_PART ) AND
                                   PS_SUPPKEY IN ( SELECT S_SUPPKEY
                                                   FROM NEW_SUPPLIER ) );
  COMMIT;
END THELLO;
/
show errors
prompt Done.

问题：

PLS-00103：在预期以下情况之一时遇到符号“CREATE”：（begin case declare end exception exit for goto if loop mod null pragma raise return select update while with

Answer 1

正如我所评论的，表创建不能作为过程的一部分，除非它在执行立即数中使用。

在创建过程之前单独创建表。

CREATE TABLE NEW_PART(
P_PARTKEY   NUMBER(12)  NOT NULL,
P_NAME      VARCHAR(55) NOT NULL,
P_MFGR      VARCHAR(25) NOT NULL,
P_BRAND     CHAR(10)    NOT NULL,
P_TYPE      VARCHAR(25) NOT NULL,
P_SIZE      NUMBER(12)  NOT NULL,
P_CONTAINER     CHAR(10)    NOT NULL,
P_RETAILPRICE   NUMBER(12,2)    NOT NULL,
P_COMMENT   VARCHAR(23) NOT NULL,
    CONSTRAINT NEW_PART_PEKEY PRIMARY KEY (P_PARTKEY),
    CONSTRAINT NEW_PART_CHECK1 CHECK(P_PARTKEY >= 0),
    CONSTRAINT NEW_PART_CHECK2 CHECK(P_SIZE >= 0),
    CONSTRAINT NEW_PART_CHECK3 CHECK(P_RETAILPRICE >= 0) );

CREATE TABLE NEW_SUPPLIER(
S_SUPPKEY   NUMBER(12)  NOT NULL,
S_NAME      CHAR(25)    NOT NULL,
S_ADDRESS   VARCHAR(40) NOT NULL,
S_NATIONKEY NUMBER(12)  NOT NULL,
S_PHONE     CHAR(15)    NOT NULL,
S_ACCTBAL   NUMBER(12,2)    NOT NULL,
S_COMMENT   VARCHAR(101)    NOT NULL,
    CONSTRAINT NEW_SUPPLIER_PKEY PRIMARY KEY (S_SUPPKEY),
    CONSTRAINT NEW_SUPPLIER_FKEY1 FOREIGN KEY (S_NATIONKEY)
        REFERENCES NATION(N_NATIONKEY),
    CONSTRAINT NEW_SUPPLIER_CHECK1 CHECK(S_SUPPKEY >= 0) );

CREATE TABLE NEW_PARTSUPP(
PS_PARTKEY  NUMBER(12)  NOT NULL,
PS_SUPPKEY  NUMBER(12)  NOT NULL,
PS_AVAILQTY NUMBER(12)  NOT NULL,
PS_SUPPLYCOST   NUMBER(12,2)    NOT NULL,
PS_COMMENT  VARCHAR(199)    NOT NULL,
    CONSTRAINT NEW_PARTSUPP_PKEY PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY1 FOREIGN KEY (PS_PARTKEY)
        REFERENCES NEW_PART(P_PARTKEY),
    CONSTRAINT NEW_PARTSUPP_FKEY2 FOREIGN KEY (PS_SUPPKEY)
        REFERENCES NEW_SUPPLIER(S_SUPPKEY),
    CONSTRAINT NEW_PARTSUPP_CHECK1 CHECK(PS_PARTKEY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK2 CHECK(PS_AVAILQTY >= 0),
    CONSTRAINT NEW_PARTSUPP_CHECK3 CHECK(PS_SUPPLYCOST >= 0) );

现在，您的过程代码。

CREATE OR REPLACE PROCEDURE THELLO AS
    WELCOME VARCHAR(50);
BEGIN

  FOR PARTKEY IN (SELECT DISTINCT PS_PARTKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_PART ( SELECT * 
                           FROM PART
                           WHERE P_PARTKEY = PARTKEY.PS_PARTKEY );
    COMMIT;
  END LOOP;

  FOR SUPPKEY IN (SELECT DISTINCT PS_SUPPKEY FROM PARTSUPP)
  LOOP
    INSERT INTO NEW_SUPPLIER ( SELECT * 
                               FROM SUPPLIER
                               WHERE S_SUPPKEY = SUPPKEY.PS_SUPPKEY );
    COMMIT;
  END LOOP;

  INSERT INTO NEW_PARTSUPP ( SELECT * 
                             FROM PARTSUPP 
                 WHERE PS_PARTKEY IN ( SELECT P_PARTKEY
                                                   FROM NEW_PART ) AND
                                   PS_SUPPKEY IN ( SELECT S_SUPPKEY
                                                   FROM NEW_SUPPLIER ) );
  COMMIT;
END THELLO;
/