ORACLE：我们可以在存储过程中创建全局临时表或任何表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2692140/
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
ORACLE: can we create global temp tables or any tables in stored proc?
提问by niceApp
below is the stored proc I wrote:
下面是我写的存储过程：
create or replace procedure test005
as
begin
CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/
INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate);
INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate);
INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate);
COMMIT;
end;
when i executed it , i get an error message mentioning:
当我执行它时，我收到一条错误消息，其中提到：
create or replace procedure test005
as
begin
CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN
(
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS
/
INSERT INTO TEMP_TRAN VALUES(1,'D',sysdate);
INSERT INTO TEMP_TRAN VALUES(2,'I',sysdate);
INSERT INTO TEMP_TRAN VALUES(3,'s',sysdate);
COMMIT;
end;
Error at line 1
ORA-00955: name is already used by an existing object
Script Terminated on line 1.
I tried to drop the TEMP_TRAN and it says table doesn't exist. So there is no TEMP_TRAN table existed in system. why am I getting this error? I am using TOAD to create this stored proc.
我试图删除 TEMP_TRAN，它说表不存在。所以系统中不存在 TEMP_TRAN 表。为什么我收到这个错误？我正在使用 TOAD 创建这个存储过程。
Any help would be highly appreciated.
任何帮助将不胜感激。
回答by Tony Andrews
Global temporary tables are not meant to be created "on the fly" by stored procedures. They are to be created once, permanently, like any other table. It is the data that is temporary, not the table object.
全局临时表并不意味着由存储过程“即时”创建。它们将被创建一次，永久地，就像任何其他表一样。数据是临时的，而不是表对象。
Regarding TEMP_TRAN, perhaps an objectof that name exists, but is not a table? Try this:
关于 TEMP_TRAN，也许该名称的对象存在，但不是表？尝试这个：
select * from all_objects where object_name = 'TEMP_TRAN';
回答by nlastra
IF v_Exists = 1 THEN
EXECUTE IMMEDIATE "DROP TABLE TEMP_TRAN";
ENDIF
the only problem with this procedure is that when the table you are looking for doesn't exist then trouble began with a no data found error and you'll not even get into that line that check for v exists value. :)
此过程的唯一问题是，当您要查找的表不存在时，问题开始于未找到数据错误，您甚至无法进入检查 v 存在值的那一行。:)
回答by Will Marcouiller
Have you tried this query?
你试过这个查询吗？
select * from all_tables where table_name like '%TEMP_TRAN%'
If so, is the table listed?
如果是，是否列出了该表？
Another way to go is by using the EXECUTE IMMEDIATE statement.
另一种方法是使用 EXECUTE IMMEDIATE 语句。
CREATE OR REPLACE PROCEDURE P_TEST005 AS
v_Exists NUMBER;
BEGIN
v_Exists := 0;
SELECT 1 INTO v_Exists
FROM ALL_TABLES
WHERE TABLE_NAME LIKE '%TEMP_TRAN%';
IF v_Exists = 1 THEN
EXECUTE IMMEDIATE "DROP TABLE TEMP_TRAN";
ENDIF
EXECUTE IMMEDIATE
"CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN (
COL1 NUMBER(9),
COL2 VARCHAR2(30),
COL3 DATE
) ON COMMIT PRESERVE ROWS"
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Please consider my rusty Oracle skills. I haven't worked with Oracle for about 2 years now. But if you work your way around it, you might achieve what you're trying to do.
请考虑我生疏的 Oracle 技能。我已经有大约 2 年没有与 Oracle 合作了。但是，如果您以自己的方式解决它，您可能会实现您想要做的事情。
However, I do agree that a temporary table is not meant to be dropped procedurely, but rather to exist just like a regular table.
但是，我确实同意临时表并不是要按程序删除，而是要像常规表一样存在。
