我们如何在pl/sql块中使用oracle私有临时表？

Question

我发现Oracle中的临时表概念与SQL Server等其他数据库有很大不同。在Oracle中，我们有一个全局临时表概念，并且只创建一次，在每个会话中填充数据，这与其他数据库不同。

在18c中，Oracle引入了私有临时表概念，表明成功使用后，可以像其他数据库一样删除表。但是如何在PL / SQL块中使用它呢？

我尝试使用动态SQL- EXECUTE IMMEDIATE 进行使用。但它给我一个“表必须声明”的错误。那我该怎么办呢？

- shashi kumar

Answer 1

但是我们如何在PL/SQL块中使用它呢？

如果你的意思是，如何在PL/SQL程序（过程或函数）中使用私有临时表，那么答案很简单：我们不能。 PL/SQL程序需要在调用之前编译。这意味着程序中引用的任何表必须存在于编译时。私有临时表也不会改变这一点。

私有临时表适用于即席SQL工作。它允许我们创建一个数据结构，在SQL语句中为会话持续时间内使用，从而使我们的生活更加轻松。

例如，假设我有一个巨大的销售数据表 - 低级交易 - 我的任务是研究每月的趋势。因此，我只需要按月销售总额。不幸的是，没有提供这个摘要的材料化视图。我不想在我的选择语句中包含聚合查询。在以前的版本中，我必须创建一个永久表（并记得之后删除它），但是在18c中，我可以使用私有临时表为会话准备我的摘要。

create private temporary table ora$ptt_sales_summary (
sales_month date
, total_value number )
/

insert into ora$ptt_sales_summary
select trunc(sales_date, 'MM')
       , sum (qty*price)
from massive_sales_table
group by trunc(sales_date, 'MM')
/

select * 
from ora$ptt_sales_summary
order by sales_month
/

显然，我们可以在会话中编写匿名PL/SQL块，但是假设您不需要这样做。那么，在永久的PL/SQL程序中，私有临时表的等价物是什么？与几个版本以来一样：PL/SQL集合或SQL嵌套表类型。

- APC

Answer 2

它可以使用动态SQL运行：

declare 
  cnt int; 
begin 
  execute immediate 'create private temporary table ora$ptt_tmp (id int)'; 
   
  execute immediate 'insert into ora$ptt_tmp values (55)'; 
  execute immediate 'insert into ora$ptt_tmp values (66)'; 
  execute immediate 'insert into ora$ptt_tmp values (77)'; 
   
  execute immediate 'select count(*) from ora$ptt_tmp' into cnt; 
  dbms_output.put_line(cnt); 
   
  execute immediate 'delete from ora$ptt_tmp where id = 66'; 
  cnt := 0; 
   
  execute immediate 'select count(*) from ora$ptt_tmp' into cnt; 
  dbms_output.put_line(cnt); 
end;

以下是示例: https://livesql.oracle.com/apex/livesql/s/l7lrzxpulhtj3hfea0wml09yg

- Okloks

Answer 3

私有临时表（从Oracle 18c开始提供）根据PTT的定义在session/transaction结束时被删除。

• ON COMMIT DROP DEFINITION选项创建一个事务特定的私有临时表。在事务结束时，Oracle会删除表定义和数据。
• ON COMMIT PRESERVE DEFINITION选项创建一个会话特定的私有临时表。Oracle会在会话结束时删除所有数据并删除表。

您不需要手动drop它。 Oracle会为您执行此操作。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_temp_table (
  ......
)
ON COMMIT DROP DEFINITION; 
-- or
-- ON COMMIT PRESERVE DEFINITION; 

ON COMMIT DROP DEFINITION 的示例（在执行 COMMIT 后表将被删除）

在编程中，ON COMMIT PRESERVE DEFINITION是一个示例（当执行COMMIT后，表将被保留，但在会话结束时将被删除）。

注意：目前我没有访问18c数据库的权限，而且db<>fiddle面临一些问题，所以我已经为您发布了图片。

干杯！！

- Popeye

---

我认为它没有回答问题。他如何在Pl/SQL块中使用它，你也必须提到...我们可以看到很多私有临时表的例子。 - Andrew

我想，可以使用动态SQL来完成。 - Okloks

这个评论对我帮助很大，谢谢你，Popeye。 - undefined