Création d'une table temporaire Oracle avec la même structure de table que celle d'une table existante

Create global temporary table mytemp 
as 
select * from myTable
where 1=2

Les tables temporaires globales dans Oracle sont très différentes des tables temporaires dans SQL Server. Ce sont des structures de données permanentes, ce ne sont que les données qu'elles contiennent qui sont temporaires (limitées à la session ou à la transaction, selon la manière dont une table est définie).

Par conséquent, la manière correcte d'utiliser les tables temporaires globales est très différente de la manière dont nous utilisons les tables temporaires dans SQL Server. L'instruction CREATE GLOBAL TEMPORARY TABLE est un exercice ponctuel (comme toute autre table). Supprimer et recréer des tables à la volée est une mauvaise pratique dans Oracle, qui n'empêche pas les gens de vouloir le faire . p>

Etant donné que la création d'une table temporaire globale devrait être un exercice ponctuel, il n'y a pas de réel avantage à utiliser la syntaxe CREATE TABLE ... AS SELECT. L'instruction doit être explicitement définie et le script stocké dans le contrôle de code source comme n'importe quel autre DDL.

Vous avez marqué votre question [oracle18c] . Si vous utilisez vraiment Oracle 18c, vous disposez d'une nouvelle fonctionnalité, des tables temporaires privées, plus proches des tables temporaires SQL Server. Ce sont des tables qui sont véritablement en mémoire et qui sont supprimées automatiquement à la fin de la transaction ou de la session (toujours selon la définition). Ils sont traités dans la documentation Oracle mais voici les titres.

Création d'une table temporaire privée de données avec un sous-ensemble de données de la table permanente T23 :

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

Le préfixe ORA $ PTT est obligatoire (bien qu'il puisse être changé en définissant le paramètre init.ora PRIVATE_TEMP_TABLE_PREFIX, mais pourquoi s'embêter?

Ensuite, nous pouvons exécuter n'importe quel DML normal sur la table:

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

La grande limitation est que nous ne pouvons pas utiliser la table en PL / SQL statique. La table n'existe pas dans le dictionnaire de données en tant que telle, et donc le compilateur PL / SQL lance - même pour les blocs anonymes:

select * from ORA$PTT_t23;

ORA-06550: ligne 6, colonne 10: PL / SQL: ORA-00942: la table ou la vue n'existe pas

Toute référence à une table temporaire privée en PL / SQL doit être faite avec du SQL dynamique:

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;

Fondamentalement, cela limite leur utilisation à SQL * Plus ( ou sqlcl qui exécutent une série d'instructions SQL pures. Ainsi, si vous avez un cas d'utilisation qui correspond à cela, vous devriez consulter les tables temporaires privées. Cependant, veuillez considérer qu'Oracle est différent de SQL Server à bien des égards, notamment son modèle de cohérence multi-version: les lecteurs ne bloquent pas rédacteurs . Par conséquent, il y a beaucoup moins besoin de tables temporaires dans Oracle.

Dans la syntaxe de SQL Server, le préfixe "#" (hachage) dans le nom de la table # temp123 signifie - créer une table temporaire qui n'est accessible que via la session courante ("##" signifie "Global ").

Pour obtenir exactement la même chose dans Oracle, vous pouvez utiliser tables temporaires privées :

declare
    r mytab%rowtype;
begin 
    insert into ora$ptt_mytab values (2, 'bbb');
    select id + 1, name||'x' into r from ora$ptt_mytab where rownum = 1;
    insert into ora$ptt_mytab values r;
end;
/
select * from mytab
union all
select * from ora$ptt_mytab;

        ID NAME                            
---------- --------------------------------
         1 aaa                             
         2 bbb                             
         3 bbbx                            

Ensuite, vous pouvez utiliser ces tables en SQL et Blocs PL / SQL:

SQL> show parameter private_temp_table            

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
private_temp_table_prefix            string      ORA$PTT_

create table mytab as 
    select 1 id, cast ('aaa' as varchar2 (32)) name from dual
;

create private temporary table ora$ptt_mytab on commit preserve definition as
    select * from mytab where 1=0
;
Private TEMPORARY created.

Quelques restrictions importantes sur les tables temporaires privées:

• Le nom doit toujours être précédé de ce qui est défini avec le paramètre PRIVATE_TEMP_TABLE_PREFIX . La valeur par défaut est ORA$PTT_.

• Vous ne pouvez pas référencer PTT dans les instructions statiques des blocs PL / SQL nommés, par exemple packages, fonctions ou déclencheurs.

• L'attribut % ROWTYPE ne s'applique pas à ce type de table.

• Vous ne pouvez pas définir de colonne avec des valeurs par défaut.