sql - 不能在里面有 CREATE TABLE if else

Question

当我运行此查询时

DECLARE
      num NUMBER;
BEGIN

    SELECT COUNT(*) INTO num FROM user_all_tables WHERE TABLE_NAME=upper('DatabaseScriptLog')
    ;

    IF num < 1 THEN

      CREATE TABLE DatabaseScriptLog 
      (ScriptIdentifier VARCHAR(100) NOT NULL,
       ScriptType VARCHAR(50), 
       StartDate TIMESTAMP, 
       EndDate TIMESTAMP, 
       PRIMARY KEY (ScriptIdentifier)
       );

    END IF;

END;

执行上述操作时，我得到以下信息:

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma raise return select update while with << close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

Answer 1

你不能像那样运行 DDL 语句。您需要使用动态 SQL (EXECUTE IMMEDIATE)。

IF num < 1 THEN

  EXECUTE IMMEDIATE 'CREATE TABLE DatabaseScriptLog (ScriptIdentifier VARCHAR(100) NOT NULL, ScriptType VARCHAR(50), StartDate TIMESTAMP, EndDate TIMESTAMP, PRIMARY KEY (ScriptIdentifier))'

END IF;