在存儲過程中怎么表示臨時表啊 CREATE GLOBAL TEMPORARY TABLE TABLENAME ( COL1 VARCHAR2(10), COL2 NUMBER ) ON COMMIT PRESERVE ROWS TABLENAME 這是一個臨時表名,但是我在ORACLE里面試了不行啊.出錯啊
我一般就像建普通表一样,写sql语句,上sql窗口执行一下来建临时表。区别是后面加上一句 on commit delete rows;
Compilation errors for PROCEDURE MESDB.AAAError: PLS-00103: Encountered the symbol "TSQL" when expecting one of the following:
:= . ( @ % ; immediate The symbol ":=" was substituted for "TSQL" to continue. Line: 10 Text: execute tsql;
create or replace procedure aaa as tsql clob; begin tsql:='CREATE GLOBAL TEMPORARY TABLE TABLENAME ( COL1 VARCHAR2(10), COL2 NUMBER ) ON COMMIT PRESERVE ROWS ';execute tsql; insert into TABLENAME select * from a;
end;
直接建立 CREATE GLOBAL TEMPORARY TABLE TABLENAME ( COL1 VARCHAR2(10), COL2 NUMBER ) ON COMMIT PRESERVE ROWS然后在过程使用不是很方便吗? 会话级临时表使用后会话结束表就会truncate 清空了
select * from aa
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE ROWS
TABLENAME 這是一個臨時表名,但是我在ORACLE里面試了不行啊.出錯啊
on commit delete rows;
:= . ( @ % ; immediate
The symbol ":=" was substituted for "TSQL" to continue.
Line: 10
Text: execute tsql;
as
tsql clob;
begin
tsql:='CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE ROWS ';execute tsql;
insert into TABLENAME
select * from a;
end;
CREATE GLOBAL TEMPORARY TABLE TABLENAME (
COL1 VARCHAR2(10),
COL2 NUMBER
) ON COMMIT PRESERVE ROWS然后在过程使用不是很方便吗?
会话级临时表使用后会话结束表就会truncate 清空了