创建具有与现有表相同的表结构的Oracle临时表

问题描述

如何创建具有与现有表相同的表结构的全局临时表?

我知道此概念在SQL Server中可用，例如从abc选择*到#temp123中".但是我想在Oracle中执行相同的操作.

解决方案

Oracle中的全局临时表与SQL Server中的临时表有很大的不同.它们是永久性的数据结构，只是其中的数据是临时的(限于会话或事务，这取决于表的定义方式).

因此，使用全局临时表的正确方法与我们在SQL Server中使用临时表的方法非常不同. CREATE GLOBAL TEMPORARY TABLE语句是一次性的练习(就像其他任何表一样).在Oracle中即时删除和重新创建表是一个坏习惯，不会阻止人们想要这样做.. >

鉴于一次性创建全局临时表，使用CREATE TABLE ... AS SELECT语法并没有真正的好处.应该明确定义该语句，并将脚本像其他任何DDL一样存储在源代码控制中.

---

您已标记问题[oracle18c].如果您真正使用的是Oracle 18c，则可以使用一个新功能，即私有临时表，它更接近SQL Server临时表.这些表是真正的内存表，在事务或会话结束时(根据定义再次)自动删除.这些内容 ，但这是标题.

使用来自永久表T23的数据子集创建私有临时表数据:

create table t23  (
    id number primary key
    , txt varchar2(24)
    );

insert into t23
select 10, 'BLAH' from dual union all
select 20, 'MEH' from dual union all
select 140, 'HO HUM' from dual
/


create private temporary table ORA$PTT_t23
on commit preserve definition
as
select * from t23
where id > 100;

ORA$PTT前缀是必需的(尽管可以通过设置init.ora参数PRIVATE_TEMP_TABLE_PREFIX进行更改，但是为什么要打扰呢?

之后，我们可以在表上执行任何常规DML:

select * from ORA$PTT_t23;

最大的限制是我们不能在静态PL/SQL中使用该表.该表本身就不存在于数据字典中，因此PL/SQL编译器也会运行-即使对于匿名块也是如此:

declare
    rec t23%rowtype;
begin
    select *
    into rec
    from ORA$PTT_t23';
    dbms_output.put_line('id = ' || rec.id);
end;
/

在PL/SQL中对私有临时表的任何引用都必须通过动态SQL来完成:

declare
    n pls_integer;
begin
    execute immediate 'select id from ORA$PTT_t23' into n;
    dbms_output.put_line('id = ' || n);
end;
/

基本上，这将它们的使用限制为SQL * Plus(或 sqlcl 脚本运行一系列纯SQL语句.因此，如果您有一个适合的用例，则应检出私有临时表，但是，请注意Oracle在许多方面与SQL Server有所不同，尤其是它的SQL Server多版本一致性模型:读者不会阻止作者.因此，Oracle中对临时表的需求大大减少.

How to create a global temporary table with same table structure to that of a existing table?

I know this concept is available in SQL server like "select * into #temp123 from abc". But I want to perform the same in Oracle.

解决方案

Global temporary tables in Oracle are very different from temporary tables in SQL Server. They are permanent data structures, it is merely the data in them which is temporary (limited to the session or transaction, depending on how a table is defined).

Therefore, the correct way to use global temporary tables is very different to how we use temporary tables in SQL Server. The CREATE GLOBAL TEMPORARY TABLE statement is a one-off exercise (like any other table). Dropping and recreating tables on the fly is bad practice in Oracle, which doesn't stop people wanting to do it.

Given the creation of a global temporary table should a one-off exercise, there is no real benefit to using the CREATE TABLE ... AS SELECT syntax. The statement should be explicitly defined and the script stored in source control like any other DDL.

---

You have tagged your question [oracle18c]. If you are really using Oracle 18c you have a new feature open to you, private temporary tables, which are closer to SQL Server temporary tables. These are tables which are genuinely in-memory and are dropped automatically at the end of the transaction or session (again according to definition). These are covered in the Oracle documentation but here are the headlines.

Creating a private temporary table data with a subset of data from permanent table T23:

create table t23  (
    id number primary key
    , txt varchar2(24)
    );

insert into t23
select 10, 'BLAH' from dual union all
select 20, 'MEH' from dual union all
select 140, 'HO HUM' from dual
/


create private temporary table ORA$PTT_t23
on commit preserve definition
as
select * from t23
where id > 100;

The ORA$PTT prefix is mandatory (although it can be changed by setting the init.ora parameter PRIVATE_TEMP_TABLE_PREFIX, but why bother?

There after we can execute any regular DML on the table:

select * from ORA$PTT_t23;

The big limitation is that we cannot use the table in static PL/SQL. The table doesn't exist in the data dictionary as such, and so the PL/SQL compiler hurls - even for anonymous blocks:

declare
    rec t23%rowtype;
begin
    select *
    into rec
    from ORA$PTT_t23';
    dbms_output.put_line('id = ' || rec.id);
end;
/

Any reference to a private temporary table in PL/SQL must be done with dynamic SQL:

declare
    n pls_integer;
begin
    execute immediate 'select id from ORA$PTT_t23' into n;
    dbms_output.put_line('id = ' || n);
end;
/

Basically this restricts their usage to SQL*Plus (or sqlcl scripts which run a series of pure SQL statements. So, if you have a use case which fits that, then you should check out private temporary tables. However, please consider that Oracle is different from SQL Server in many aspects, not least its multi-version consistency model: readers do not block writers. Consequently, there is much less need for temporary tables in Oracle.

这篇关于创建具有与现有表相同的表结构的Oracle临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！