Как создать временную таблицу в sql oracle

SQL ЛОКАЛЬНЫЕ ВРЕМЕННЫЕ ТАБЛИЦЫ

В этом учебном материале вы узнаете, как создавать SQL LOCAL TEMPORARY TABLES с синтаксисом и примерами.

Описание

SQL локальные временные таблицы — уникальные в модулях и встроенных программах SQL в сеансах SQL.

Синтаксис

Синтаксис для DECLARE LOCAL TEMPORARY TABLE в SQL.

DECLARE LOCAL TEMPORARY TABLE table_name
( column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
);

Параметры или аргумент

table_name Имя локальной временной таблицы, которую вы хотите создать column1 , column2 Столбцы, которые вы хотите создать в локальной временной таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение оставить пустым, база данных принимает NULL в качестве значения по умолчанию

Пример

Давайте посмотрим SQL пример DECLARE LOCAL TEMPORARY TABLE .

DeepEdit!

Временные таблицы используются в Oracle для хранения данных, которые относятся к одной сессии или одной транзакции.

Так, например, целесообразно использовать временные таблицы для хранения данных об открытых неким клиентским приложением или процессом файлах, об открытых дочерних формах. Можно так же сохранять во временных таблицах, данные матриц преобразований в сложных математических задачах.

Итак, временные таблицы(GLOBAL TEMPORARY), а отличие от таблиц регулярных целесообразно использовать в тех случаях, когда сохраняемее данные часто изменяются, и непостоянны.

Временные таблицы (GLOBAL TEMPORARY TABLE) отличаются от обычных регулярных таблиц тем, что эти таблицы предназначены только для хранения временных для некоторой сессии данных.

Данные во временной таблицы будут видны только в той сессии, которая вставила эти данные в таблицу.

После создания временной таблицы ее описание сохраняется в словаре данных ORACLE, но в этот момент не определяется сегмент , где будут сохранятся данные из этой таблицы.

Место в соответствующем сегменте под данные, выделяется динамически, в момент обращения первой команды манипулирования данными — DML (select, insert, update) к этой временной таблице.

Временная таблица описывается таким же образом, что и обычная регулярная таблица, но сегменты таблицы, и все данные такой таблицы, подразделяются :

— данные используемые только в данной сессии
— или же данные используемые только в данной транзакции

Специфику поведения данных относительно сессии определяет ключевые слова ON COMMIT и ON RESERVE в команде CREATE TABLE

Можно использовать операторы определения данных DDL такие как (ALTER TABLE, CREATE INDEX ) для временных таблиц, но только тогда когда сессия не обращется к временной таблице, не связана с ней. Сессия связана с временной таблицей и при выполнении команды INSERT над данными таблицы.

Существует несколько способов, чтобы сделать сессию несвязанной с временной таблицей:
1. Использовать команду TURNICATE для данной таблицы.
2. Аннулировать данную сессию
3. Использовать команды фиксации работы транзакции COMMIT или ROLLBACK
Ограничения для временных таблиц в ORACLE
На временные таблицы в ORACLE распространяются следующие ограничения :

1. Временная таблица в Oracle не может быть партицирована particioned, кластеризована clustered, или быть организованной по индексу index organized.

2. Нельзя связывать вторичные ключи с колонками временной таблицы
3. Временная таблица не может включать в себя вложенных таблиц (nested table)

4. Нельзя использовать в описании временных таблиц следующие команды :LOB_storage_clause: TABLESPACE, storage_clause, или logging_clause

5. Подсказка Parallel и параллельные запросы не поддерживаются во временных таблицах
6. Распределенные транзакции так же не могут работать с временными таблицами
7. Сегментирование не работает в временных таблицах
Создание временной таблицы

Синтаксис для создания временных таблиц практически аналогичен синтаксису для создания регулярных таблиц, но есть некоторые операторы, которые используются только для временных таблиц:

ОN COMMIT DELETE ROWS используется во временных таблицах, данные которой существуют в пределах одной транзакции.

Oracle удаляет все строки — все данные из временной таблицы после завершения транзакции, после выполнения команды COMMIT.

Данная инструкция подразумевается по умолчанию, то есть при создании временной таблицы — она всегда будет ОN COMMIT DELETE ROWS

ОN COMMIT RESERVE ROWS используется во временных таблицах, данные которой существуют в пределах одной сессии. СУБД Oracle удаляет все строки из временной таблицы — очищает таблицу после завершения сессии.

Читать:

Как отключить проверку приложений в mac os

Данные примеры продемонстрируют создание и использование временных таблиц, отличия временных таблиц ОN COMMIT DELETE ROWS и ОN COMMIT RESERVE ROWS, отличие временных таблиц от таблиц регулярных.

Как создать временную таблицу со структурой постоянной таблицы?

В SQL Server префикс «#» (hash) в имени таблицы означает — создать временную таблицу, которая видна только в текущей сессии (в противоположность «##» — глобальная временная таблица).

В версии Oracle 18c также появилась возможность создать приватные временные таблицы, которые по завершению сессии (по-умолчанию транзакции без предиката on commit ) будут полностью разрушены. Префикс обязателен и определён параметром private_temp_table_prefix (по-умолчанию ORA$PTT_ ). Например:

create table mytab as select 1 id, cast ('aaa' as varchar2 (32)) name from dual ; show parameter private NAME TYPE VALUE ------------------------- ------ -------- private_temp_table_prefix string ORA$PTT_ create private temporary table ora$ptt_mytab on commit preserve definition as select * from mytab where 1=0 ; Private TEMPORARY created.

И используем как обычные таблицы:

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

С некоторыми ограничениями:

для %rowtype они не доступны
в именных PL/SQL блоках они могут использоваться только в динамическом SQL, т.к. во время компиляции их ещё нет

Создание и использование временных таблиц

Временная таблица это таблица определение которой видно всем сессиям, но данные для каждой сессии только те, которые были добавлены этой сессией. Программисты могут использовать их для обработки больших данных. Синтаксис команды

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

(column datatype [,column datatype] )

Определение столбцов такое же как и для обычной таблицы и так же может быть получено подзапросом. Дополнительная директива определяет время жизни добавленных в таблицу строк. По умолчанию используется удаление строк по окончании добавивших их транзакции, но можно использовать PRESERVE и данных будут хранится до конца сессии. В любом случае данные во временных таблицах свои для каждой сесии: разные пользователи могут добавлять свои строки в таблицу и они никогда не увидят строк другой сессии.

Временные таблицы во многом схожи с обычными. Вы можете выполнять DML или SELECT команды используя их. Можно создать индексы, ограничеиня и триггеры для этих таблицы. Для них можно создать представления, синонимы и все SQL команды к временным таблицам будут выполняться быстрее чем к обычным таблицам.

Первой причиной такого быстродействия является то что временные таблицы не являются сегментами в табличном пространстве. В идеале они существуют только в PGA сессии которая их использует, т.е. не используется жесткий диск и даже буфер БД. Если PGA не доступно памяти для хранения временной таблицы (к примеру если миллионы строк вставляются в таблицы – такое часто бывает при создании отчётов) то таблица записывается во временное табличное пространтсво. Операции чтения/записи во временном табличном пространстве гораздо быстрее так как не используется буфер БД: все операции производятся соотствующим серверным процессом сразу на диск.

Вторая причина быстродействия – DML команды дял временных таблиц не создают вектором изменений. Так как данные хранятся только во время жизни сессии (или транзакции) нет смысла создавать данные для отмены изменений. Это даёт двойной выигрыш – быстрое выполнение DML операция и снижение нагрузки на систему генерации отмены изменений, что могло бы плохо влиять на производительность в нагруженной системе с большим числом пользователей.

На рисунке 7-2 показано создание временной таблицы используя SQL *Plus. Также можно создать временную таблицу используя Database Control Table Creation Wizrad.

Создание простой таблицы
DDL и объекты схемы — Итоги
Создание и управление ролями
Создание и управление профилями
Создание и управление табличными пространствами