为什么此处需要立即执行

Question

我是SQL Server用户，我有一个使用Oracle的小项目要做，所以我正在努力了解Oracle的一些特殊性，我认为我需要一些帮助来更好地了解以下情况：

我想在创建临时表之前测试它是否存在，所以我在这里有这样的代码：

DECLARE
  table_count INTEGER;
  var_sql VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (
            hello varchar(1000) NOT NULL)';
BEGIN
  SELECT COUNT(*) INTO table_count FROM all_tables WHERE table_name = 'TEST';
  IF table_count = 0 THEN
    EXECUTE IMMEDIATE var_sql;
  END IF;
END;

它工作正常，所以在我执行了一次之后，我在我的IF:上添加了一个else语句

ELSE
  insert into test (hello) values ('hi');

再次执行它，一行被添加到我的测试表中。

好吧，我的代码已经准备好了，可以工作了，所以我放弃了临时表，并试图再次运行整个语句，但当我这样做时，我会得到以下错误：

ORA-06550: line 11, column 19:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 11, column 7:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

然后我把我的其他声明改成了这个，现在它又起作用了：

ELSE
  EXECUTE IMMEDIATE 'insert into test (hello) values (''hi'')';

我的问题是，为什么单独运行我可以简单地使用插入而不是EXECUTE IMMEDIATE，以及为什么在BEGIN之后的SELECT语句仍然有效，而其他所有语句似乎都需要EXECUTE IMMEDIATE才能正常运行？

Answer 1

整个PL/SQL块在编译时被解析，但动态语句中的文本直到运行时才被求值。（对于匿名块，它们几乎是一样的，但步骤仍然不同）。

您的if/else也要到运行时才会求值。编译器不知道在您插入时该表是否始终存在，它只能在解析整个块时检查它是否存在。

如果表已经存在，那么它是可以的；编译器可以看到它，块执行，select得到1，然后进入else进行插入。但是，如果它不存在，那么在编译时，插入的解析将正确失败，ORA-00942，并且块中的任何内容都不会执行。

由于表的创建是动态的，所以对表的所有引用也必须是动态的-正如你所看到的那样，你的插入也是动态的，但如果你查询它的话也是如此。基本上，这会使你的代码更难阅读，并可能隐藏语法错误-因为动态代码要到运行时才能解析，而且你可能会在长时间未命中的分支中的动态语句中出错。

无论如何，全局临时表都不应该在运行中创建。它们是具有临时数据的永久对象，特定于每个会话，不应作为应用程序代码的一部分创建/删除。（您的应用程序一般不应进行架构更改；它们应仅限于升级/维护更改并受到控制，以避免错误、数据丢失和意外的副作用；GTT也不例外）。

与其他一些关系数据库中的临时表不同，在Oracle数据库中创建临时表时，将创建静态表定义。临时表是数据字典中描述的持久对象，但在会话将数据插入表之前，临时表显示为空。您可以为数据库本身创建一个临时表，而不是为每个PL/SQL存储过程创建一个表。

创建一次GTT，并使所有PL/SQL代码成为静态代码。如果您想要更接近SQL Server的本地临时表的内容，请查看PL/SQL集合。

Answer 2

PL/SQL:ORA-00942：表或视图不存在

这是编译时错误，即在创建GTT之前解析静态SQL。

让我们看看编译时和运行时error之间的区别：

静态SQL:

SQL> DECLARE
  2  v number;
  3  BEGIN
  4  select empno into v from a;
  5  end;
  6  /
select empno into v from a;
                         *
ERROR at line 4:
ORA-06550: line 4, column 26:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 4, column 1:
PL/SQL: SQL Statement ignored

动态SQL：

SQL> DECLARE
  2  v number;
  3  BEGIN
  4  execute immediate 'select empno from a' into v;
  5  end;
  6  /
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4

在第一个PL/SQL块中，在编译时进行了语义检查，您可以看到PL/SQL: ORA-00942: table or view does not exist。在第二个PL/SQL块中，您没有看到PL/SQL错误。

底线，

在编译时，不知道表是否存在，因为它是仅在运行时创建。

在您的情况下，为了避免这种行为，您需要使INSERT也是动态的，并使用EXECUTE IMEDIATE。通过这种方式，您可以转义编译时错误并动态创建表，还可以在运行时动态插入。

话虽如此，基本问题是您试图在运行中创建GTT，这不是一个好主意。你应该创建一次，然后按照你想要的方式使用它。

Answer 3

我对您的代码做了一点修改，就逻辑而言，它是有效的。但正如exp在早期帖子中所说，在运行时创建动态GTT根本不是一个好主意

--- Firstly by dropping the table i.e NO TABLE EXISTS in the DB in AVROY 
SET serveroutput ON;
DECLARE
  table_count INTEGER;
  var_sql     VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (            
hello varchar(1000) NOT NULL)';
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE AVROY.TEST'; --Added the line just to drop the table as per your comments
  SELECT COUNT(*)
  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';
  IF table_count   = 0 THEN
    EXECUTE IMMEDIATE var_sql;
    dbms_output.put_line('table created');
  ELSE
    INSERT INTO AVROY.test
      (hello
      ) VALUES
      ('hi'
      );
  END IF;
END;
--------------------OUTPUT-----------------------------------------------
anonymous block completed
table created
SELECT COUNT(*)
--  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';
COUNT(*)
------
1
--------
-- Second option is without DROPPING TABLE

SET serveroutput ON;
DECLARE
  table_count INTEGER;
  var_sql     VARCHAR2(1000) := 'create GLOBAL TEMPORARY table TEST (            
hello varchar(1000) NOT NULL)';
BEGIN
--EXECUTE IMMEDIATE 'DROP TABLE AVROY.TEST';
  SELECT COUNT(*)
  INTO table_count
  FROM all_tables
  WHERE table_name = 'TEST'
  AND OWNER        = 'AVROY';
  IF table_count   = 0 THEN
    EXECUTE IMMEDIATE var_sql;
    dbms_output.put_line('table created');
  ELSE
    INSERT INTO AVROY.test
      (hello
      ) VALUES
      ('hi'
      );
      dbms_output.put_line(SQL%ROWCOUNT||' Rows inserted into the table');
  END IF;
END;
-------------------------------OUTPUT-------------------------------------
anonymous block completed
1 Rows inserted into the table

---------------------------------------------------------------------------