Oracle локальные типы коллекций не допускаются в операторах sql

Обновлено: 06.07.2024

Для типов коллекций по сравнению с одним типом данных коллекцию следует рассматривать как единое целое, то есть как группу комбинаций данных одного типа, а не отдельные данные. Поэтому установите
Объявление, присвоение и инициализация комбинированного набора типов сильно отличаются от одного типа. В частности, вложенные таблицы и массивы переменной длины должны быть инициализированы перед назначением. При вложении таблиц и чисел переменной длины
Когда группа объявляется, они автоматически устанавливаются в значения NULL. То есть в коллекции вложенных таблиц и массивов переменной длины нет элементов, и это не для тех элементов, которые в ней есть. Можно использовать системные настройки
Определите функцию с тем же именем, что и тип коллекции, чтобы инициализировать коллекцию. Мы должны явно вызвать конструктор для инициализации каждого массива переменной длины и переменной вложенной таблицы (для ассоциативных массивов
Конструктор для инициализации использовать не нужно).

1. Назначение ассоциативного массива
Ассоциативный массив не нужно инициализировать, просто назначьте его напрямую. (Инициализация упомянутой ниже коллекции относится к вложенным таблицам и массивам переменной длины)
Во-вторых, инициализация и присвоение коллекции
1. Метод инициализации
Тип коллекции в основном делится на три этапа для завершения: один - объявление, другой - инициализация, а третий - присвоение. Инициализация и присвоение могут быть выполнены в блоке объявления или в блоке исполнения.
Collection_name collection_type: = collection_type (); -> Исходная коллекция пуста (пуста)

Инициализация коллекции в основном осуществляется через конструктор (конструктор - это имя типа объявленного типа). Следующие часто используемые методы инициализации включают:
A. Объявите коллекцию в блоке объявления и используйте конструктор в блоке объявления для инициализации пустым, но не NULL, и присвойте значение после использования метода расширения в блоке выполнения
Б. Объявите коллекцию в блоке объявления, используйте конструктор в блоке выполнения, чтобы инициализировать его пустым, но не NULL, и используйте метод расширения в блоке выполнения, чтобы назначить
C. Объявите коллекцию в блоке объявления и присвойте значения при использовании инициализации конструктора в блоке выполнения.
D. Объявите коллекцию в блоке объявления и используйте конструктор для инициализации и присвоения значений, то есть три шага объединяются в один шаг для завершения
Для коллекции, которая инициализирована как пустая (пустая), необходимо использовать метод расширения для расширения емкости позже, если не используется метод массового сбора в

2. Способ присвоения множества
collection_name(subscript) := expression;

3. Исключения, которые могут возникнуть при назначении
В следующих случаях присвоения значений элементам набора могут возникать различные исключения.
A. Если индекс индекса не существует или не может быть преобразован в правильный тип данных, PL / SQL выдаст предопределенное исключение VALUE_ERROR.
Обычно нижний индекс является целым числом. Но индекс ассоциативного массива также может иметь тип VARCHAR2.
B. Если данный индекс индекса указывает на неинициализированный элемент, PL / SQL вызовет исключение SUBSCRIPT_BEYOND_COUNT.
C. Если коллекция автоматически инициализируется нулевым значением и программа ссылается на один из ее элементов, PL / SQL выдаст исключение COLLECTION_IS_NULL.

4. Ссылка на элементы
collection_name(subscript)

Элементы таблицы можно передавать как параметры. Например, verify_loc (nest_loc_tab (i)), verify_loc - это функция или процедура.

10. Обратите внимание на перенос переменных элементов между составными типами (вложенные таблицы и массивы переменной длины со встроенными записями PL / SQL) и методами сбора BULK COLLECT INTO, LAST, EXTEND и т. Д.

Я получаю «ORA-22905: невозможно получить доступ к строкам из не вложенного элемента таблицы», когда он попадает в оператор SELECT from TABLE.

Можно ли даже запросить тип таблицы в PLSQL?

Решение

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

ORA-22905: невозможно получить доступ к строкам из не вложенного элемента таблицы

означает, что вы пытаетесь сделать запрос из неподдерживаемого типа таблицы. Ваш тип type_tab_AB является ассоциативным массивом, из-за INDEX BY BINARY_INTEGER пункт. Удалить INDEX BY BINARY_INTEGER пункт, чтобы сделать ваш type_tab_AB тип вложенной таблицы. (Varrays также работал бы здесь, но я не рекомендовал бы использовать их, если вы не знаете верхнюю границу для числа ожидаемых строк. При объявлении типа varray вам нужно указать максимальное количество элементов, тогда как типы вложенных таблиц имеют нет такого ограничения.)

После внесения этого изменения ваш код все еще может не работать. Следующая ошибка, которую вы можете получить (см. Примечание внизу, если нет)

PLS-00642: локальные типы коллекций не допускаются в инструкциях SQL

Это потому, что тип, который вы выбираете, объявлен внутри PL / SQL. Вы должны объявить type_tab_AB , а также record_AB вне PL / SQL, используя CREATE TYPE . ,

Последняя проблема, с которой вы столкнетесь, связана с SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM . заявление. В таком виде этот запрос выдаст вам следующую ошибку:

PL / SQL: ORA-00947: недостаточно значений

В совокупности эти изменения должны решить проблему. Вот полный скрипт SQL * Plus, который создает тестовую таблицу с некоторыми тестовыми данными и проверяет, может ли он запрашивать тип таблицы:

НОТА: Для простоты я предположил, что спрашивающий использует Oracle 11 или старше. Я полагаю, что в Oracle 12 вам разрешено использовать типы, объявленные в PL / SQL, в SQL-запросе, поэтому вы можете не столкнуться с ошибкой PLS-00642. Я не могу сказать, какие другие изменения в моем ответе могут быть необходимы для Oracle 12, поскольку я еще не использовал Oracle 12.

Другие решения

Вы не можете запросить тип, созданный внутри блока pl / sql. Вам нужно создать его в командной строке sql, а затем вы можете запросить его. Смотрите пример ниже:

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

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

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

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

    Создание коллекции происходит в два этапа Сначала мы объявляем тип(type) коллекции (конструкции assoc_array_type_def, varray_type_def и nested_table_type_def будут приведены далее)

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

Тип коллекции Количество элементов Тип индекса Плотная или разреженная Без инициализации Где объявляется Использование в 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)

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

Записи

Коллекции

Методы коллекций

Метод коллекции - это подпрограмма PL/SQL - либо функция, которая возвращает информацию о коллекции, либо процедура, которая работает с коллекцией. Методы сбора данных упрощают использование коллекций и упрощают поддержку ваших приложений.

С нулевой коллекцией EXISTS является единственным методом сбора, который не вызывает предопределенное исключение COLLECTION_IS_NULL.

Метод коллекции Тип Описание
DELETE Процедура Удаляет элементы из коллекции.
TRIM Процедура Удаляет элементы из конца varray или Nested Tables.
EXTEND Процедура Добавляет элементы в конец varray или Nested Tables.
EXISTS Функция Возвращает TRUE тогда и только тогда, когда существует определенный элемент varray или Nested Tables.
FIRST и LAST Функции FIRST возвращает первый, LAST последний индекс в коллекции.
COUNT Функция Возвращает количество элементов в коллекции.
LIMIT Функция Возвращает максимальное количество элементов, которые может иметь коллекция.
PRIOR и NEXT Функции PRIOR возвращает индекс, который предшествует указанному индексу, NEXT возвращает индекс, который следует за указанным индексом.

Операции над коллекциями

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

Исключительные ситуации коллекции.

В большинстве случаев, если вы ссылаетесь на несуществующий элемент коллекции, PL/SQL вызывает предопределенное исключение. Рассмотрим следующий пример:

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

Следующий список показывает, когда возникает данное исключение:

Исключение коллекции Когда возникает
COLLECTION_IS_NULL вы пытаетесь оперировать с атомарно нулевой коллекцией.
NO_DATA_FOUND обозначает элемент, который был удален, или несуществующий элемент ассоциативного массива.
SUBSCRIPT_BEYOND_COUNT индекс превышает количество элементов в коллекции.
SUBSCRIPT_OUTSIDE_LIMIT индекс находится за пределами допустимого диапазона.
VALUE_ERROR индекс не имеет значения или не может быть преобразован в тип ключа. Это исключение может возникнуть, если ключ определен как диапазон PLS_INTEGER, а индекс находится за пределами этого диапазона.

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

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