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

问题描述

我是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语句更改为此，现在它又可以工作了:

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

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

解决方案

整个PL/SQL块在编译时进行解析，但是直到运行时才对动态语句中的文本进行求值. (对于匿名阻止，它们接近同一件事，但仍然是截然不同的步骤).

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

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

由于表的创建是动态的，因此对表的所有引用也必须是动态的-如您所见，您的插入内容也包括查询内容.基本上，这会使您的代码更难阅读，并且可以隐藏语法错误-因为动态代码要等到运行时才能解析，而且您可能在分支中的动态语句中遇到了错误而没有被时间长了.

全局临时表不应为即时创建.它们是具有临时数据的永久对象，特定于每个会话，并且不应作为应用程序代码的一部分进行创建/删除. (您的应用程序一般不应进行模式更改；应将其限制在升级/维护更改之内，并加以控制，以避免错误，数据丢失和意外的副作用； GTT不变).

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

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

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;

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

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

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

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?

解决方案

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).

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.

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.

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).

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.

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屋！