如何在oracle存储过程中创建和使用临时表? [英] How to create and use temporary table in oracle stored procedure?

问题描述

我想在存储过程中创建临时表并以相同的方式访问它，但是出现了错误，提示ORA-00942:Table or view does not exists. 以下是我尝试过的步骤，

I want to create temporary table in stored procedure and access it in the same but I got error that ORA-00942:Table or view does not exists. Following is the procedure that i tried,

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;  

这是我用来创建临时表的方式，但出现错误，还有其他方法可以执行此任务吗?

This is the way I used to create temporary table but I got error, is there any other way to perform this task?

推荐答案

只需首先创建它(一次在您的过程之外)，然后在您的过程中使用它即可.您不想在每次调用该过程时都尝试创建它.

Just create it first (once, outside of your procedure), and then use it in your procedure. You don't want to (try to) create it on every call of the procedure.

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

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

这篇关于如何在oracle存储过程中创建和使用临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！