ORACLE:我们可以在存储过程中创建全局临时表或任何表吗? [英] ORACLE: can we create global temp tables or any tables in stored proc?

问题描述

下面是我写的存储过程:

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.

我试图删除 TEMP_TRAN，它说表不存在.所以系统中不存在 TEMP_TRAN 表.为什么我收到这个错误?我正在使用 TOAD 创建这个存储过程.

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.

任何帮助将不胜感激.

推荐答案

全局临时表不应由存储过程即时"创建.它们将被永久创建一次，就像任何其他表一样.数据是临时的，而不是表对象.

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.

关于 TEMP_TRAN，也许该名称的对象存在，但不是表?试试这个:

Regarding TEMP_TRAN, perhaps an object of that name exists, but is not a table? Try this:

select * from all_objects where object_name = 'TEMP_TRAN';

这篇关于ORACLE:我们可以在存储过程中创建全局临时表或任何表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！