ORACLE：我们可以在存储过程中创建全局临时表或任何表吗？
下面是我写的存储过程：
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;
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.
没有找到相关结果
已邀请:
3 个回复
耐钨徒
鞘垒飘
IF v_Exists = 1 THEN EXECUTE IMMEDIATE "DROP TABLE TEMP_TRAN"; ENDIF辰炔诚薯
如果是，表格是否列出？ 另一种方法是使用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;