Oracle 19c中的私有临时表

Oracle 19C介绍了一个私人临时表的概念，私有临时表的概念是基于内存的临时表，该表在会话结束时或事务结束时取决于设置。

    如果您从不同的数据库引擎（例如 SQL Server）转向 Oracle，您可能会对 Oracle 的临时表概念感到困惑。在SQL Server中，开发人员会定期创建临时表来完成一些工作并删除它。

在 Oracle 中，全局临时表 (GTT) 是一个永久元数据对象，它在特定于事务或特定于会话的基础上将行保存在临时段中。动态创建和删除 GTT 被认为是不正常的。随着私有临时表的引入，Oracle 有了一个类似于其他引擎中的选项，其中表对象本身是临时的，而不仅仅是数据。

一、临时表类型

Oracle 支持两种类型的临时表。

• 全局临时表：自 Oracle 8i 起可用。这里讨论过。
• 私有临时表：从 Oracle 18c 开始可用，也是本文的主题。

二、私有临时表的命名

PRIVATE_TEMP_TABLE_PREFIX 初始化参数默认为“ORA$PTT_”，定义创建私有临时表时名称中必须使用的前缀。在下面的示例中，我们创建一个私有临时表，但未在名称中使用正确的前缀，这会导致错误。

CREATE PRIVATE TEMPORARY TABLE my_temp_table (id           NUMBER,description  VARCHAR2(20)
);CREATE PRIVATE TEMPORARY TABLE my_temp_table (*
ERROR at line 1:
ORA-00903: invalid table nameSQL>

三、创建私有临时表

如果您使用过全局临时表，那么创建私有临时表的语法看起来会很熟悉。

ON COMMIT DROP DEFINITION 子句（默认值）指示应在事务结束或会话结束时删除表。

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (id           NUMBER,description  VARCHAR2(20)
)
ON COMMIT DROP DEFINITION;-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');SELECT COUNT(*) FROM ora$ptt_my_temp_table;COUNT(*)
----------1SQL>-- Commit and check contents.
COMMIT;SELECT COUNT(*) FROM ora$ptt_my_temp_table;
SELECT COUNT(*) FROM ora$ptt_my_temp_table*
ERROR at line 1:
ORA-00942: table or view does not existSQL>

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (id           NUMBER,description  VARCHAR2(20)
)
ON COMMIT PRESERVE DEFINITION;-- Insert, but don't commit, then check contents of PTT.
INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE');SELECT COUNT(*) FROM ora$ptt_my_temp_table;COUNT(*)
----------1SQL>-- Commit and check contents.
COMMIT;SELECT COUNT(*) FROM ora$ptt_my_temp_table;COUNT(*)
----------1SQL>-- Reconnect and check contents of GTT.
CONN test/test@pdb1SELECT COUNT(*) FROM ora$ptt_my_temp_table;
SELECT COUNT(*) FROM ora$ptt_my_temp_table*
ERROR at line 1:
ORA-00942: table or view does not existSQL>

CREATE PRIVATE TEMPORARY TABLE ora$ptt_emp AS
SELECT * FROM emp;

永久 PL/SQL 对象直接引用临时对象是没有意义的，因为它在编译时不存在。如果您想使用永久对象中的私有临时表，则必须使用动态 SQL 来完成。以下愚蠢的示例创建一个使用私有临时表的存储函数。

CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)
RETURN VARCHAR2
ASl_sql     VARCHAR2(32767);l_return  VARCHAR2(30);
BEGINl_sql := 'CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (id           NUMBER,description  VARCHAR2(20))ON COMMIT DROP DEFINITION';EXECUTE IMMEDIATE l_sql;EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, 'ONE')]';EXECUTE IMMEDIATE 'SELECT description INTO :l_return FROM ora$ptt_my_temp_table WHERE id = :id' INTO l_return USING p_id;RETURN l_return;
END;
/

SET SERVEROUTPUT ON
BEGINDBMS_OUTPUT.put_line('ptt_test(1) = ' || ptt_test(1));
END;
/
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
ORA-06512: at "SYS.PTT_TEST", line 13
ORA-06512: at line 2SQL>

五、相关视图

六、使用限制