oracle oracle存储过程中如何创建和使用临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9310860/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create and use temporary table in oracle stored procedure?
提问by Rajaram Shelar
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,
我想在存储过程中创建临时表并以相同的方式访问它，但出现ORA-00942:Table or view does not exists.以下错误是我尝试过的过程，
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?
这是我用来创建临时表的方式，但出现错误，还有其他方法可以执行此任务吗？
回答by tbone
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;
回答by swimswithbricks
I have edited this answer as it was wrong. I am a recent MSSQL convert and because of the way oracle implements global temp tables, if you really DO need to use temp tables, creating them once and leaving them there is the way to go. Unless you use dynamic sql in your procs exclusively (have fun debugging), you will not be able to successfully compile your package unless the tables referenced already exist. Oracle validates any objects referenced in methods that you attempt to compile, which is why you got the 942 error. I love the way Oracle manages scope with these global temp tables. That, alone, sold me on the idea.
我已经编辑了这个答案，因为它是错误的。我是最近的 MSSQL 转换者，并且由于 oracle 实现全局临时表的方式，如果您确实需要使用临时表，创建它们一次并将它们留在那里是可行的方法。除非您在 procs 中专门使用动态 sql（调试起来很有趣），否则除非引用的表已经存在，否则您将无法成功编译您的包。Oracle 会验证您尝试编译的方法中引用的任何对象，这就是您收到 942 错误的原因。我喜欢 Oracle 使用这些全局临时表管理范围的方式。仅凭这一点，我就相信了这个想法。
回答by LAV KUMAR
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;
回答by Enrico Cairo
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;
/
回答by Swapni and Avinash
Use this
用这个
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;
