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

问题描述

我想在存储过程中创建临时表并以相同的方式访问它，但出现ORA-00942:表或视图不存在的错误.以下是我尝试过的程序，

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