如何在oracle函数中使用全局临时表

问题描述

我想在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.

推荐答案

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.




创建全局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函数中使用全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！