Oracle переменная типа таблица

Обновлено: 04.07.2024

Коллекцией называется упорядоченная группа элементов одного типа. Язык PL/SQL поддерживает три вида коллекций:

вложенные таблицы (nested tables) ;
индексированные таблицы ;
varray-массивы (variable-size arrays) .

Доступ к любому элементу вложенной таблицы или varray-массива осуществляется по его индексу, который указывается в скобках после имени переменной типа коллекции . Коллекция может быть передана в качестве параметра. Коллекцию можно использовать:

для обмена с таблицами баз данных и столбцами данных;
для передачи столбца данных из приложения клиента в хранимую процедуру или обратно.

Для создания коллекции следует определить тип коллекции - TABLE или VARRAY - и объявить переменную этого типа. Определение типа выполняется в секции объявлений блока PL/SQL , подпрограммы или пакета.

Вложенные таблицы

Определение типа вложенной таблицы может иметь следующее формальное описание:

Параметр type_name указывает имя определяемого типа, а element_type - это любой допустимый тип данных PL/SQL, исключая некоторые типы, в том числе VARRAY , TABLE , BOOLEAN , LONG , REF CURSOR и т.п.

Вложенную таблицу можно рассматривать как одномерный массив , в котором индексами служат значения целочисленного типа в диапазоне от 1 до 2147483647 . Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой DELETE . Вложенная таблица может динамически увеличиваться.

Индексированные таблицы

Индексированные таблицы позволяют работать со столбцами как с единой переменной - массивом.

Определение индексированной таблицы (index-by tables ) может иметь следующее формальное описание:

Индексированная таблица - это вариант вложенной таблицы , в которой элементы могут иметь произвольные целочисленные значения индексов. Такой тип данных очень удобен, если в качестве индекса использовать значение первичного ключа.

VARRAY-массивы

Определение типа Varray-массива может иметь следующее формальное описание:

Параметр type_name указывает имя определяемого типа, size_limit - максимальное количество элементов, а element_type - это любой допустимый тип данных PL/SQL, исключая некоторые типы, такие как VARRAY , TABLE , BOOLEAN , LONG , REF CURSOR и т.п.

Если типом элемента является тип "запись" , то каждое поле записи должно быть скалярного или объектного типа .

Максимальное количество элементов в Varray-массиве указывается при определении типа и не может изменяться динамически. Доступ к каждому элементу Varray-массива осуществляется по индексу. Varray-массивы можно передавать в качестве параметров. Varray-массивы не могут иметь пустот, так как для них нет операции удаления произвольного элемента массива.

Инициализация коллекций

Для инициализации коллекции используется конструктор - автоматически создаваемая функция, одноименная с типом коллекции .

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

Оператор CREATE TYPE позволяет сохранить определяемый тип в базе данных.

Конструктор можно вызывать в любом месте, где допустим вызов функции. Для того чтобы добавить в таблицу базы данных строку, одно из полей которой имеет тип коллекции, следует использовать конструктор .

Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.

Таким же образом коллекции используются и в Oracle.

Содержание статьи

Общие сведения о коллекциях в pl/sql

Типы коллекций

Тип коллекции	Количество элементов	Тип индекса	Плотная или разреженная	Без инициализации	Где объявляется	Использование в SQL
Ассоциативный массив (index by table)	Не задано	String Pls_integer	Плотная и разреженная	Empty	PL/SQL block Package	Нет
Varray (variable-size array)	Задано	Integer	Только плотная	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы
Nested table	Не задано	Integer	При создании плотная, может стать разреженной	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы

Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.

Ассоциативный массив

Набор пар ключ-значение
Данные хранятся в отсортированном по ключу порядке
Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
При объявлении как константа должен быть сразу инициализирован функцией
Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
Нельзя объявить тип на уровне схемы, но можно в пакете
Не имеет конструктора
Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
Datatype – это любой тип данных, кроме ref cursor

Для помещения в память небольших таблиц-справочников
Для передачи в качестве параметра коллекции

Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP

Varray

Представляет собой массив последовательно хранящихся элементов

Тип описывается следующим образом (varay_type_def):

Размер задается при создании
Индексируется с 1
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor

Знаем максимально возможное количество элементов
Доступ к элементам последовательный

Restrictions:
Максимальный размер – 2 147 483 647 элементов

Nested table

Тип описывается следующим образом (nested_table_type_def):

Размер коллекции изменяется динамически
Может быть в разряженном состоянии, как показано на картинке
<
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor
Если содержит только одно скалярное значение, то имя колонки – Column_Value

Set operations с nested tables

Операции возможны только с коллекциями nested table. Обе коллекции, участвующие в операции, должны быть одного типа.
Результатом операции также является коллекция nested table.

Операция	Описание
MULTISET UNION	Возвращает объединение двух коллекций
MULTISET UNION DISTINCT	Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT	Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT	Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET	Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT	Возвращает разницу двух коллекций
MULTISET EXCEPT DISTINCT	Возвращает разницу двух коллекций с дистинктом (убирает дубли)

Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)

Пришло, я думаю, время разобрать составной тип, который, по моему мнению, является наиболее мощным и простым средством скоростной обработки больших массивов данных. Такой составной тип имеет название TABLE! Рассмотрим его синтаксис: (он подобен синтаксису RECORD)

Вот так просто объявить составной тип TABLE! В своей сути это одномерный массив скалярного типа. Он не может содержать тип RECORD или другой тип TABLE. Но может быть объявлен от любого другого стандартного типа. И это еще не все! TABLE может быть объявлен от атрибута %ROWTYPE! Вот где скрывается, по истине огромная мощь, этого типа данных! Но пока, все по порядку. Тип TABLE, можно представить как одну из разновидностей коллекции. Хотя в более глубоком понимании это не совсем так. При первом рассмотрении он похож на массив языка C. Массив TABLE индексируется типом BINARY_INTEGER и может содержать, что-то вроде - 2,147,483,647 - 0 - + 2,147,483,647 как в положительную, так и в отрицательную часть. Начинать индексацию можно с 0 или 1 или любого другого числа. Если массив будет иметь разрывы, то это не окажет какой-либо дополнительной нагрузки на память. Так же следует помнить, что для каждого элемента, например, типа VARCHAR2 будет резервироваться столько памяти, сколько вы укажете, по этому определяйте размерность элементов по необходимости. Скажем не стоит объявлять VARCHAR2(255), если хотите хранить строки менее 100 символов! :) Итак, давайте объявим массив и посмотрим, как он работает. Запишем такой блок:

Получаем после обработки в SQL*Plus:

Рассмотрим, что же здесь происходит. Мы объявили две одномерные коллекции m_SmplTable, m_SmplTblData, одна из них содержит элементы размерностью VARCHAR2(128), другая DATE. Затем объявили две переменные данного типа - MY_TBL, MY_TBL_DT и присвоили первому элементу строкового массива значение "Buber", а второму элементу массива дат, значение текущей даты минус 1. Результат вывели на консоль! Вот собственно и все. При этом хорошо видно, что тип TABLE очень схож с таблицей БД и содержит обычно два ключа KEY и VALUE, ключ и значение. Ключ имеет тип BINARY_INTEGER:

В нашем случае имеет место:

Число строк таблицы ни чем не ограничено. Единственное ограничение это значения, которые могут быть представлены типом BINARY_INTEGER.
Порядок элементов таблицы PL/SQL не обязательно должен быть строго определен. Эти элементы хранятся в памяти не подряд как массивы и по этому могут вводится с произвольными ключами.
Ключи, используемые в таблице PL/SQL, не обязательно должны быть последовательными. В качестве индекса таблицы может быть использовано любое значение или выражение имеющее тип BINARY_INTEGER.

Здесь мы объявили коллекцию из строковых переменных, и с помощью известного вам цикла FOR записали с 1-го по 10-й элемент значениями исходный плюс пять. Затем мы вывели на экран пятый элемент, как и следовало ожидать его значение равно 10 (5+5). Вот один из способов наполнения массива значениями. Но этот способ бесполезен и показан в качестве примера. А, вот следующий пример более осмыслен. Перепишем блок из предыдущего шага вот так:

Здесь мы объявили коллекцию, с типом запись из таблицы CUSTOMERS! И получили тот же результат как в прошлый раз, но более элегантно! Итак, объявляем тип:

Это значит, что каждая строка коллекции содержит полную запись из таблицы БД CUSTOMERS. Замечательно. Далее 100 элементу массива присваиваем значение записи таблицы с индексом 2108. Вот так это выглядит изнутри:

Обращаться к отдельному элементу (полю) записи можно через точечную нотацию. Что мы и проделали с вами в примере! Так же скажу, что используя привязку таблиц PL/SQL к массивам интерфейса OCI можно достичь очень высокой скорости передачи данных в клиентских приложениях БД. В следующий раз рассмотрим атрибуты таблиц PL/SQL.

При настройке производительности SQL Server возникает ни с чем не сравнимая проблема: как работать с временными наборами данных в фрагменте кода, который занимает много времени или часто вызывается? Табличные переменные и временные таблицы - это два варианта.

Табличная переменная

Табличные переменные - это особый тип данных, тип переменных, которые можно использовать для хранения наборов результатов для последующей обработки. Таблица в основном используется для временного хранения строк, возвращаемых набором результатов. Если тип объявленной функции и переменной является таблицей, табличная переменная может быть в функции, хранимой процедуре и пакете.

Создать синтаксис табличной переменной

Общие примечания к табличным переменным

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

Если есть связанный запрос, в предложении FROM внешняя таблица должна использовать псевдоним для ссылки на переменную, как показано в следующем примере:

Табличные переменные ведут себя как локальные переменные. Есть четко определенный объем. Это функция, хранимая процедура или пакет, в котором объявлена переменная. В рамках своей области таблица может использовать переменные, как обычные таблицы. Эта переменная может применяться к любому месту, где таблицы или табличные выражения используются в операторах SELECT, INSERT, UPDATE и DELETE. В конце функции, хранимой процедуры или пакета, определяющего табличную переменную, табличная переменная будет автоматически очищена;
Табличные переменные используют системную память, быстрое чтение и запись, но память ограничена, и все операции не имеют журналов.
Табличные переменные не должны учитывать другие проблемы доступа к сеансу, поэтому нет необходимости в механизме блокировки.Для очень загруженной системы отказ от использования блокировок может снизить часть нагрузки на систему;
Не все переменные таблицы существуют в памяти. Существует определенный предел для табличных переменных, которые могут храниться в памяти. Если объем данных табличных переменных превышает пороговое значение, память будет исчерпана и будет использовано пространство TempDB.
Поскольку переменные таблицы не будут записывать журналы, не вызовут накладных расходов на блокировку и не могут создавать индексы первичного ключа вне Declare, переменные таблицы не вызовут изменений в архитектуре и, следовательно, не вызовут перекомпиляции. План выполнения хранимой процедуры был сгенерирован при создании хранимой процедуры, поэтому последующее выполнение хранимой процедуры с использованием табличных переменных не вызовет перекомпиляции плана выполнения.

Ограничения табличных переменных:

Невозможно выполнить оператор SELECT INTO для табличных переменных, например:

В SQL Server2000 переменные таблицы не могут использоваться в таких операторах, как INSERT INTO переменная_таблицы EXEC хранимая_процедура, и версии после 2005 г. начали поддерживать такое использование.
После DECLARE табличная переменная не может быть изменена, то есть операция DDL не может быть выполнена;
Вы не можете создать индекс непосредственно для табличной переменной (поскольку после создания табличной переменной вы не можете выполнять для нее операторы DDL, включая оператор Create Index), но вы можете создать индекс, создав ограничения (первичный ключ, уникальный);
Проверочные ограничения, значения по умолчанию и вычисляемые столбцы в объявлениях табличных переменных не могут вызывать пользовательские функции.
Табличные переменные не поддерживают операции присваивания между переменными;
Поскольку переменные таблицы имеют ограниченный диапазон и не являются частью постоянной базы данных, они не подлежат откату транзакции;
Табличные переменные существуют в памяти. Когда объем данных велик, использование табличных переменных потребляет слишком много памяти;
Некластеризованные индексы не могут быть созданы для табличных переменных (за исключением системных индексов, созданных для ограничений PRIMARY или UNIQUE). По сравнению с временными таблицами с некластеризованными индексами это может повлиять на производительность запросов;
Табличные переменные не имеют статистической информации о распределении данных, они не вызовут перекомпиляцию. Во многих случаях оптимизатор создает план запроса в предположении, что переменная таблицы не имеет строк. Это не способствует тому, чтобы оптимизатор составлял правильный план выполнения, и не подходит для ситуаций с большим объемом данных.
Если табличная переменная создается вне инструкции EXEC или хранимой процедуры sp_executesql, вы не можете использовать инструкцию EXEC или хранимую процедуру sp_executesql для выполнения динамических запросов SQL Server, которые ссылаются на табличную переменную. Поскольку на переменные таблицы можно ссылаться только в их локальной области

[Пример]
(1) Создание и запрос табличных переменных

(2) Используйте табличные переменные в INSERT . EXEC

Временные таблицы

Временные таблицы - это своего рода временные объекты, и есть временные объекты, такие как временные хранимые процедуры и временные функции.Временные объекты хранятся в tempdb.

Два способа создания формата временной таблицы
①CREATE TABLE

②SELECT INTO
Использование SELECT INTO автоматически создает временную таблицу, создавать которую заранее не нужно.

Общие примечания к временной таблице

Использование временных таблиц (таких как INSERT, UPDATE, DELETE) такое же, как и общие таблицы;
Временные таблицы не могут быть разделены;
Если в одной хранимой процедуре или пакете создается несколько временных таблиц, они должны иметь разные имена;
Если используются временные таблицы, все временные таблицы должны быть явно удалены в конце хранимой процедуры, сначала усекая таблицу, а затем удаляя таблицу, чтобы избежать долговременной блокировки системных таблиц;
Если локальная временная таблица создается хранимой процедурой или приложением, которое выполняется несколькими пользователями одновременно, ядро базы данных должно иметь возможность различать таблицы, созданные разными пользователями. С этой целью ядро базы данных внутренне добавляет числовой суффикс к имени таблицы каждой локальной временной таблицы;
Временные таблицыПривязанный к сеансу, Если временная таблица не будет явно удалена с помощью DROP TABLE, временная таблица будет автоматически удалена системой при выходе из ее области видимости;

Локальная временная таблица будет удалена в следующих ситуациях:
a. Вызов оператора Drop Table явно;
б. По завершении хранимой процедуры локальная временная таблица, созданная в хранимой процедуре, будет автоматически удалена.
c. В конце текущего сеанса все локальные временные таблицы, созданные в сеансе, будут удалены;
Глобальные временные таблицы будут удалены в следующих ситуациях:
а. Глобальная временная таблица находится вКонец сеанса, создавшего эту таблицуА такжеВсе остальные задачи перестают относиться к немуВремя будет автоматически удалено. Другими словами, когда заканчивается сеанс создания глобальной временной таблицы, таблица будет автоматически удалена после завершения последнего оператора Transact-SQL, который ссылается на эту таблицу.

Временные таблицы хранятся в TempDb, поэтому доступ к временным таблицам может вызвать физический ввод-вывод. Конечно, необходимо создавать журналы, чтобы гарантировать согласованность при изменении, и механизм блокировки также незаменим;

Ограничения временной таблицы

Невозможно разделить временную таблицу;
Невозможно добавить ограничения внешнего ключа во временные таблицы;
Тип данных столбца во временной таблице не может быть определен, поскольку в TempDb не определен пользовательский тип данных (пользовательский тип данных - это объект уровня базы данных, а временная таблица принадлежит TempDb)

Временная таблица с таким же именем
Из общих замечаний мы знаем, что если локальная временная таблица создается хранимой процедурой, ядро базы данных внутренне добавляет числовой суффикс к имени таблицы каждой локальной временной таблицы. Когда хранимая процедура завершена, локальная временная таблица, созданная в хранимой процедуре, будет автоматически удалена. Таким образом, даже имя локальной временной таблицы, созданной в хранимой процедуре или триггере, может бытьИмя временной таблицы, созданной перед вызовом хранимой процедуры или триггера.То же самое, но временная таблица - это всего лишь кодовое имя в сеансе.В реальной системной временной базе данных реальное имя таблицы обрабатывается автоматически, поэтому они также разные.
Создается в хранимой процедуре или триггереИмя локальной временной таблицыМожет быть сИмя временной таблицы, созданной перед вызовом хранимой процедуры или триггера.то же. Однако, если запрос относится к временной таблице и одновременно существуют две временные таблицы с одинаковыми именами, он не определяет, по какой таблице анализируется запрос.
Вложенная хранимая процедура также может создавать временную таблицу с тем же именем, что и временная таблица, созданная хранимой процедурой, которая ее вызывает. Все ссылки на имя таблицы в наборе хранимых процедур интерпретируются как относящиеся к Таблица, созданная в процессе вложения. Однако, чтобы изменить его и преобразовать в таблицу, созданную во вложенной процедуре, эта таблица должна иметь ту же структуру и имена столбцов, что и таблица, созданная вызывающей процедурой.

Кроме типов данных Oracle7, PL/SQL поддерживает несколько дополнительных типов данных и позволяет использовать в своих конструкциях некоторые основные типы с большим диапазоном.

Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать одномерные массивы и записи, широко используемые в программах PL/SQL.

Таблицы PL/SQL

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF < column_type | variable%TYPE |
table.column%TYPE > [NOT NULL] INDEX BY BINARY_INTEGER;

Имя (например, name_plsql_table), которое описывается табличным типом данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE, имеет вид: name_plsql_table type_name;
Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы одномерного массива: name_plsql_table(index), где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку в таблице PL/SQL «ename_tab» следует написать: ename_tab(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется синтаксис: name_plsql_table(index) := expr;

Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы или представления, а также для выборки значений из таблицы PL/SQL, необходимо использовать цикл.

Записи PL/SQL

Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе DECLARE с помощью атрибута %ROWTYPE: sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее тип данных. Для описания типа данных RECORD используется синтаксис:

При объявлении типа записи можно присвоить ее полям некоторые значения. Если же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых значений), то этому полю надо обязательно присвоить значение. Например:

TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,

familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);

Объявление создаваемой записи (например, name_plsql_record) производится в разделе DECLARE и имеет вид: name_plsql_record type_name;
Ссылки на отдельные поля записи осуществляются так: name_plsql_record.field_name;
Для присвоения значения конкретному полю записи используется синтаксис: name_plsql_record.field_name := expr;

Примеры использования записей в программах PL/SQL приведены ниже.
Для ввода в таблицу PL/SQL значений из какого-либо столбца базовой таблицы или представления, а также для выборки значений из таблицы PL/SQL, необходимо использовать цикл.

Переменные, константы и выражения PL/SQL

В программах PL/SQL могут использоваться переменные и константы, описываемые в разделе DECLARE с помощью конструкции вида: variable_name [CONSTANT] type_name [NOT NULL] [ < := | DEFAULT >expr ]
Например:

Запись опубликована 08.04.2010 в 5:50 дп и размещена в рубрике Oracle7 краткий справочник. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

Читайте также: