立即执行创建表和更新表 [英] execute immediate create table and update table

问题描述

我正在 pl/sql 中创建一个临时表，使用立即执行 &也在表中插入为什么要创建表.

I am creating a temp table in pl/sql using execute immediate & also inserting in the table why create table.

在那之后我更新了表格.但是我得到的错误表不存在，因为它没有创建表 thr 立即执行

After that I m updating the table. But i m getting error table doesn't exists as it is not creating the table thr execute immediate

示例代码---------

sample code---------

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  update t23 set aa ='2' where aa='1';
  COMMIT ;
end;

推荐答案

您正在使用静态 SQL 来执行更新，并且在运行 PL/SQL 之前验证了这一点，因此发现它引用了一个没有目前存在.您可以使用动态 SQL 来执行更新:

You are using static SQL to perform the update, and this is validated before the PL/SQL is run, and so finds that it references a table that doesn't currently exist. You could use dynamic SQL to perform the update:

begin
  execute immediate 'create table t23 as  select ''1'' aa from dual'; 
  execute immediate 'update t23 set aa =''2'' where aa=''1''';
  COMMIT ;
end;

然而，在 Oracle 中首先动态创建这样的临时表确实是不好的做法.你为什么要这样做?一旦我们知道，也许我们可以提出更好的替代方案.

However, really it is bad practice in Oracle to dynamically create temporary tables like this in the first place. Why are you doing it? Once we know that perhaps we can suggest a better alternative.

这篇关于立即执行创建表和更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！