为什么这里需要立即执行? [英] Why EXECUTE IMMEDIATE is needed here?

问题描述

我是一名 SQL Server 用户，我有一个使用 Oracle 的小项目，所以我试图了解 Oracle 的一些特殊性，我认为我需要一些帮助才能更好地了解以下情况:

I am a SQL Server user and I have a small project to do using Oracle, so I’m trying to understand some of the particularities of Oracle and I reckon that I need some help to better understand the following situation:

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

I want to test if a temporary table exists before creating it so I had this code here:

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 语句:

It works normally, so after I executed it once, I added an else statement on my IF:

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

再次执行它并在我的测试表中添加了一行.

Executed it again and a line was added to my test table.

好的，我的代码已经准备好并且可以工作了，所以我删除了临时表并尝试再次运行整个语句，但是当我这样做时，我收到以下错误:

Ok, my code was ready and working, so I dropped the temp table and tried to run the entire statement again, however when I do that I get the following error:

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:
%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

然后我将我的 else 语句更改为这个，现在它又可以工作了:

Then I changed my else statement to this and now it works again:

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

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

My question is why running individually I can simply use the insert instead of the EXECUTE IMMEDIATE and also why my SELECT statement right after BEGIN still works when all the rest appears to need EXECUTE IMMEDIATE to run properly?

推荐答案

整个 PL/SQL 块在编译时被解析，但动态语句中的文本直到运行时才会被评估.(对于匿名块，它们几乎相同，但步骤仍然不同).

The whole PL/SQL block is parsed at compile time, but the text within a dynamic statement isn't evaluated until runtime. (They're close to the same thing for an anonymous block, but still distinct steps).

您的 if/else 也不会在运行时评估.编译器不知道在您插入时该表将始终存在，它只能在解析整个块时检查它是否存在.

Your if/else isn't evaluated until runtime either. The compiler doesn't know that the table will always exist by the time you do your insert, it can only check whether or not it exists at the point it parses the whole block.

如果表已经存在，那就没问题；编译器可以看到它，块执行，您的选择获得 1，然后您进入 else 进行插入.但如果它不存在，则插入的解析正确失败，ORA-00942 在编译时并且块中的任何内容都不会执行.

If the table does already exist then it's OK; the compiler can see it, the block executes, your select gets 1, and you go into the else to do the insert. But if it does not exist then the parsing of the insert correctly fails with ORA-00942 at compile time and nothing in the block is executed.

由于表的创建是动态的，因此对表的所有引用也必须是动态的 - 如您所见，您的插入，但如果您随后查询它，也是如此.基本上它使您的代码更难阅读并且可以隐藏语法错误 - 因为动态代码直到运行时才被解析，并且您可能在一个没有被命中的分支中的动态语句中出错好久不见.

Since the table creation is dynamic, all references to the table have to be dynamic too - your insert as you've seen, but also if you then query it. Basically it makes your code much harder to read and can hide syntax errors - since the dynamic code isn't parsed until run-time, and it's possible you could have a mistake in a dynamic statement in a branch that isn't hit for a long time.

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

Global temporary tables should not be created on-the-fly anyway. They are permanent objects with temporary data, specific to each session, and should not be created/dropped as part of your application code. (No schema changes should be made by your application generally; they should be confined to upgrade/maintenance changes and be controlled, to avoid errors, data loss and unexpected side effects; GTTs are no different).

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

Unlike temporary tables in some other relational databases, when you create a temporary table in an Oracle database, you create a static table definition. The temporary table is a persistent object described in the data dictionary, but appears empty until your session inserts data into the table. You create a temporary table for the database itself, not for every PL/SQL stored procedure.

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

Create the GTT once and make all your PL/SQL code static. If you want something closer to SQL Server's local temporary tables then look into PL/SQL collections.

这篇关于为什么这里需要立即执行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！