如何在oracle函数中使用全局临时表 [英] How to use global temporary table in oracle function

问题描述

我想在oracle函数中使用一个临时变量，但该函数不接受表，可以为我提供创建的语法和在oracle函数中使用临时表。



我在函数中使用了以下代码

Hi,

I want to use a temporary variable in oracle function, but the function is not accepting the table, can any provide me the syntax to create and use temporary table in oracle function.

I used following code in function

create global temporary table store_sales 
on commit delete rows 
as select * from tbl_folders

但我收到以下错误

but i am getting following error

Compilation failed,line 17 (18:44:28)
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

谢谢。

Thank you.

推荐答案

你不能在函数中使用DDL。



只需创建临时表并让它存在于你的表空间中。您插入的所有数据只能在该会话中使用。所以没有必要为每个事务创建和删除它。

所以你可以使用：

You cannot use DDL inside a function.

Simply create the temp table and let it exist in your tablespace. All data you insert into it will be usable from that session only. So there is no need to create and delete it for every transaction.
So you can just use:

INSERT INTO store_sales 
select * from tbl_folders

由于您在提交删除行上指定了，因此在提交或回滚事务时将自动删除数据。

Since you have specified on commit delete rows, the data will be automatically deleted when you commit or rollback the transaction.

我使用下面的代码并成功运作。

以下用于创建全局临时表的语句。

I Have use below code and successfully work.
Below statement for creation of global temporary table.

创建全局TEMPORARY TABLE xx_am_temp_tbl_emp2

提交删除行为select employee_id，first_name来自XX_am_employees;



结果：创建全局临时TABLE。

CREATE global TEMPORARY TABLE xx_am_temp_tbl_emp2
on commit delete rows as select employee_id, first_name from XX_am_employees;

Result : Global temporary TABLE created.

这篇关于如何在oracle函数中使用全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！