执行立即创建表和更新表

发布于 2024-11-02 05:07:47 字数 270 浏览 0 评论 0原文

我正在 pl/sql 中使用“立即执行”和“创建临时表”还插入表中为什么创建表。

之后我正在更新表格。但我收到错误表不存在，因为它没有创建表并执行立即

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

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

原文

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

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

陈年往事 2024-11-09 05:07:47

您正在使用静态 SQL 来执行更新，并且这在运行 PL/SQL 之前进行了验证，因此发现它引用了当前不存在的表。您可以使用动态 SQL 来执行更新：

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 中，首先动态创建这样的临时表实际上是一种不好的做法。你为什么这么做？一旦我们知道也许我们可以提出更好的替代方案。

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;

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.

回复收藏 0 原文

~没有更多了~