如何在Oracle存储过程中创建和使用临时表？

Question

我想在存储过程中创建临时表，并在同一存储过程中访问它，但是我收到了错误消息：ORA-00942：表或视图不存在。以下是我尝试的存储过程：

Create procedure myproc
  IS
  stmt varchar2(1000);
  BEGIN
  stmt:='CREATE GLOBAL TEMPORARY table temp(list if columns) ON COMMIT DELETE ROWS';

  execute immediate stmt;

  insert into temp values('list of column values');

 END;  

这是我曾经用来创建临时表的方法，但是我遇到了错误，有没有其他方法可以执行此任务？

- Rajaram Shelar

---

@tbone的回答展示了你应该做什么，但为了解释你所看到的情况：你不能这样做，因为在编译过程中temp还不存在。编译器不会尝试解析动态SQL，最重要的是它不知道它是否在运行时有效。这种方法能够成功的唯一途径是将插入语句也转换为动态SQL；但是这不是Oracle中临时表的工作方式，所以不要像这样做。 - Alex Poole

可能是[Oracle 10中本地临时表（用于存储过程的范围）]的重复问题（http://stackoverflow.com/questions/1192265/local-temporary-table-in-oracle-10-for-the-scope-of-stored-procedure）。 - APC

此问题已在以下帖子中得到解决：https://dev59.com/smMl5IYBdhLWcg3wCDGW - user9253440

Answer 1

首先创建它 (在你的程序之外，只需创建一次)，然后在你的程序中使用它。你不想在每次调用程序时都创建它。

create global temporary table tmp(x clob)
on commit delete rows;

create or replace procedure...
-- use tmp here
end;

- tbone

---

但是为什么呢？我觉得这很奇怪。比如说，我需要一个“虚拟”表。我能在过程内部创建它吗？ - Revious

@Gik25，你是使用实际的临时表还是其他方法取决于你的具体情况。也许你可以发布一个新问题并提供更多细节，这样你很可能会得到一些好的回答。 - tbone

我同意@Revious的观点。 我需要在存储过程中创建、使用和销毁临时表，但Oracle不支持，有什么建议吗？ - ZeExplorer

如果此过程定义在一个包内呢？ - Himansz

如果您有多个并发进程同时运行并尝试使用此表，除非您有某种使其在进程运行之间唯一的东西，否则该方法将无法正常工作 - 否则您可能会在查询中捕获不属于该表的垃圾数据，或者将数据放入其中以破坏其他进程运行。几个数据库系统允许在过程内创建临时表，例如 Select * into #tmp - 表名。这些表不会干扰相同过程的其他实例。 - John Foll

Answer 2

Create or replace procedure myprocedure
is 
   stmt varchar2(1000);
   stmt2 varchar2(1000);
begin
    stmt := 'create global temporary table temp(id number(10))';
    execute immediate stmt;
    stmt2 := 'insert into temp(id) values (10)';
    execute immediate stmt2;
end;

- LAV KUMAR

Answer 3

我已编辑此答案，因为它是错误的。我最近转换到MSSQL，并且由于Oracle实现全局临时表的方式，如果您确实需要使用临时表，则创建它们一次并将它们留在那里是正确的方法。除非您在存储过程中仅使用动态SQL（尝试调试时好运），否则您将无法成功编译包，除非引用的表已经存在。Oracle验证您尝试编译的方法中引用的任何对象，这就是您收到942错误的原因。我喜欢Oracle通过这些全局临时表管理范围的方式。仅凭这一点，就让我对这个想法感到满意。

- swimswithbricks

---

这通常是Oracle中非常糟糕的做法。表格不会随意消失。为什么要添加额外（缓慢）的代码，强制在任何地方使用动态SQL，并失去所有配置管理的希望？临时表格几乎从来没有用处。它们通常可以被普通表格、内联视图或集合所替代。 - Jon Heller

不用在意我的答案，我最近转换到了 MSSQL。在过去的一周里，我更好地理解了 Oracle 对临时表的实现。我使用 Oracle 越多，就越喜欢它。 - swimswithbricks

Answer 4

使用这个

Create of replace procedure myprocedure
is 
   stmt varchar2(1000);
   stmt2 varchar2(1000);
begin
    stmt := 'create global temporary table temp(id number(10))';
    execute immediate stmt;
    stmt2 := 'insert into temp(id) values (10)';
    execute immediate stmt2;
end;

- Swapni and Avinash

---

那实际上是错误的建议。在进行插入/更新/删除操作的代码块之外创建临时表。 - Mat

Answer 5

CREATE OR REPLACE PROCEDURE myproc IS
BEGIN

    CREATE GLOBAL TEMPORARY TABLE temp (id NUMBER(10)) ON COMMIT DELETE ROWS AS
        SELECT 10 FROM dual;

END;
/

- Enrico Cairo

---

请在您的代码中加入一些解释说明。 - Ishita Sinha