Дневник megavtogal.com

МЕНЮ
  • Контакты
  • Статьи

Удалить элемент коллекции oracle

Обновлено: 07.01.2025

Oracle*
Статья имеет довольно таки тезисный стиль. Более подробное содержание можно найти в приложенном внизу статьи видео с записью лекции по коллекциям Oracle.

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

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

[b]Содержание статьи[/b]

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

[b]Типы коллекций[/b]

Тип коллекции
Количество элементов
Тип индекса
Плотная или разреженная
Без инициализации
Где объявляется
Использование в SQL

(index by table)
Не задано
String

Pls_integer
Плотная и разреженная
Empty
PL/SQL block

(variable-size array)
Задано
Integer
Только плотная
Null
PL/SQL block

Schema level
Только определенные на уровне схемы

Nested table
Не задано
Integer
При создании плотная, может стать разреженной
Null
PL/SQL block

Schema level
Только определенные на уровне схемы

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

Подробности можно узнать из видео-лекции в конце статьи.

Также его называют index by table или pl/sql table.

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

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

Используются для:

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

Restrictions:

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

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

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

  • Размер задается при создании
  • Индексируется с 1
  • Инициализируется конструктором

Используется, если:

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

Restrictions:

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

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

  • Размер коллекции изменяется динамически
  • Может быть в разряженном состоянии, как показано на картинке

[b]Set operations с nested tables[/b]

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

Результатом операции также является коллекция nested table.

MULTISET UNION
Возвращает объединение двух коллекций

MULTISET UNION DISTINCT
Возвращает объединение двух коллекций с дистинктом (убирает дубли)

MULTISET INTERSECT
Возвращает пересечение двух коллекций

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

SET
Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)

MULTISET EXCEPT
Возвращает разницу двух коллекций

MULTISET EXCEPT DISTINCT
Возвращает разницу двух коллекций с дистинктом (убирает дубли)

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

DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
answer nested_typ;
BEGIN
answer := nt1 MULTISET UNION nt4;
answer := nt1 MULTISET UNION nt3;
answer := nt1 MULTISET UNION DISTINCT nt3;
answer := nt2 MULTISET INTERSECT nt3;
answer := nt2 MULTISET INTERSECT DISTINCT nt3;
answer := SET(nt3);
answer := nt3 MULTISET EXCEPT nt2;
answer := nt3 MULTISET EXCEPT DISTINCT nt2;
END;

nt1 MULTISET UNION nt4: 1 2 3 1 2 4
nt1 MULTISET UNION nt3: 1 2 3 2 3 1 3
nt1 MULTISET UNION DISTINCT nt3: 1 2 3
nt2 MULTISET INTERSECT nt3: 3 2 1
nt2 MULTISET INTERSECT DISTINCT nt3: 3 2 1
SET(nt3): 2 3 1
nt3 MULTISET EXCEPT nt2: 3
nt3 MULTISET EXCEPT DISTINCT nt2: empty set

IS NULL (IS NOT NULL)
Сравнивает коллекцию со значением NULL

Сравнение =
Две коллекции nested table можно сравнить, если они одного типа и не содержат записей типа record. Они равны, если имеют одинаковые наборы элементов (не зависимо от порядка хранения элементов внутри коллекции)

IN
Сравнивает коллекцию с перечисленными в скобках

SUBMULTISET OF
Проверяет, является ли коллекция подмножеством другой коллекции

MEMBER OF
Проверяет, является ли конкретный элемент(объект) частью коллекции

IS A SET
Проверяет, содержит ли коллекция дубли

IS EMPTY
Проверяет, пуста ли коллекция

nt1 = nt2
nt1 IN (nt2,nt3,nt4)
nt1 SUBMULTISET OF nt3
3 MEMBER OF nt3
nt3 IS NOT A SET
nt4 IS EMPTY


[b]Методы коллекций[/b]

Синтаксис вызова методов: collection_name.method

Метод
Тип
Описание
Index by table
Varray
Nested table

DELETE
Процедура
Удаляет элементы из коллекции
Да
Нет
Да

TRIM
Процедура
Удаляет элементы с конца коллекции (работает с внутренним размером коллекции)
Нет
Да
Да

EXTEND
Процедура
Добавляет элементы в конец коллекции
Нет
Да
Да

EXISTS
Функция
Возвращает TRUE, если элемент присутствует в коллекции
Да
Да
Да

FIRST
Функция
Возвращает первый индекс коллекции
Да
Да
Да

LAST
Функция
Возвращает последний индекс коллекции
Да
Да
Да

COUNT
Функция
Возвращает количество элементов в коллекции
Да
Да
Да

LIMIT
Функция
Возвращает максимальное количество элементов, которые может хранить коллекция
Нет
Да
Нет

PRIOR
Функция
Возвращает индекс предыдущего элемента коллекции
Да
Да
Да

NEXT
Функция
Возвращает индекс следующего элемента коллекции
Да
Да
Да

  • Delete удаляет все элементы. Сразу же очищает память, выделенную для хранения этих элементов.
  • Delete(n) удаляет элемент с индексом n. Память не освобождает. Элемент можно восстановить (т.е. задать новый) и он займет ту же память, что занимал предыдущий.
  • Delete(n, m) удаляет элементы с индексами в промежутке n..m
  • Если удаляемого элемента в коллекции нет, ничего не делает.

Пример использования
DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
nt.DELETE(2); -- Удаляет второй элемент
nt(2) := 2222; -- Восстанавливает 2-й элемент
nt.DELETE(2, 4); -- Удаляет элементы со 2-го по 4-й
nt(3) := 3333; -- Восстанавливает 3-й элемент
nt.DELETE; -- Удаляет все элементы
END;

beginning: 11 22 33 44 55 66
after delete(2): 11 33 44 55 66
after nt(2) := 2222: 11 2222 33 44 55 66
after delete(2, 4): 11 55 66
after nt(3) := 3333: 11 3333 55 66
after delete: empty set

  • Trim() – удаляет один элемент в конце коллекции. Если элемента нет, генерирует исключение SUBSCRIPT_BEYOND_COUNT
  • Trim(n) – удаляет n элементов в конце коллекции. Если элементов меньше, чем n, генерируется исключение SUBSCRIPT_BEYOND_COUNT
  • Работает с внутренним размером коллекции. Т.е. если последний элемент был удален с помощью Delete, вызов Trim() удалит уже удаленный ранее элемент.
  • Сразу очищает память, выделенную для хранения этих элементов
  • Лучше не использовать в сочетании с Delete()

Пример использования
DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
BEGIN
nt.TRIM; -- Trim last element
nt.DELETE(4); -- Delete fourth element
nt.TRIM(2); -- Trim last two elements
END;

beginning: 11 22 33 44 55 66
after TRIM: 11 22 33 44 55
after DELETE(4): 11 22 33 55
after TRIM(2): 11 22 33

  • EXTEND добавляет один элемент со значением null в конец коллекции
  • EXTEND(n) добавляет n элементов со значением null в конец коллекции
  • EXTEND(n,i) добавляет n копий элемента с индексом i в конец коллекции. Если коллекция имеет NOT NULL констрейнт, только этой формой можно пользоваться.
  • Если элементы были ранее удалены с помощью метода Delete, Extend не будет использовать сохранившиеся за коллекцией ячейки памяти, а добавит новый элемент (выделит новую память)

Пример использования
DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(11, 22, 33);
BEGIN
nt.EXTEND(2, 1); -- Append two copies of first element
nt.DELETE(5); -- Delete fifth element
nt.EXTEND; -- Append one null element
END;

beginning: 11 22 33
after EXTEND(2,1): 11 22 33 11 11
after DELETE(5): 11 22 33 11
after EXTEND: 11 22 33 11

  • Для удаленных элементов возвращает false
  • При выходе за границы коллекции возвращает false
  • Для varray First всегда возвращает единицу, Last всегда возвращает то же значение, что и Count

Before deletions:
FIRST = A
LAST = Z
After deletions:
FIRST = K
LAST = R

Пример использования
DECLARE
TYPE NumList IS VARRAY(10) OF INTEGER;
n NumList := NumList(1, 3, 5, 7);

n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2

  • Для varray возвращает максимально допустимое количество элементов в коллекции, для остальных коллекций возвращает null

Пример использования
DECLARE
TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
aa aa_type; -- associative array

TYPE va_type IS VARRAY(4) OF INTEGER;
va va_type := va_type(2, 4); -- varray

TYPE nt_type IS TABLE OF INTEGER;
nt nt_type := nt_type(1, 3, 5); -- nested table

aa.COUNT = 4
aa.LIMIT =
va.COUNT = 2
va.LIMIT = 4
nt.COUNT = 3
nt.LIMIT =


[b]Prior и Next[/b]

  • Позволяют перемещаться по коллекции
  • Возвращают индекс предыдущего/следующего элемента (или null, если элемента нет)

Пример использования
DECLARE
TYPE nt_type IS TABLE OF NUMBER;
nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);

nt(4) was deleted.
nt.PRIOR(1) =
nt.NEXT(1) = 2
nt.PRIOR(2) = 1
nt.NEXT(2) = 3
nt.PRIOR(3) = 2
nt.NEXT(3) = 5
nt.PRIOR(4) = 3
nt.NEXT(4) = 5
nt.PRIOR(5) = 3
nt.NEXT(5) = 6
nt.PRIOR(6) = 5
nt.NEXT(6) =
nt.PRIOR(7) = 6
nt.NEXT(7) =

  • Возвращает результаты sql-оператора в PL/SQL пачками, а не по одному
  • SELECT BULK COLLECT INTO
  • FETCH BULK COLLECT INTO [LIMIT]
  • RETURNING BULK COLLECT INTO
  • Не работает с ассоциативными массивами (кроме тех, что индексированы pls_integer)

Пример использования
DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;

CURSOR c1 IS SELECT employee_id,last_name
FROM employees
WHERE salary > 10000
ORDER BY last_name;

enums NumTab;
names NameTab;
BEGIN
SELECT employee_id, last_name
BULK COLLECT INTO enums, names
FROM employees
ORDER BY employee_id;

OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO enums, names LIMIT 10;
EXIT WHEN names.COUNT = 0;
do_something();
END LOOP;
CLOSE c1;

DELETE FROM emp_temp WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
END;


[b]Цикл forall[/b]

  • посылает DML операторы из PL/SQL в SQL пачками, а не по одному
  • может содержать только один DML оператор
  • для разряженных коллекций используется форма:

  • SQL%BULK_ROWCOUNT – коллекция, содержит количество строк, на которые повлиял каждый dml оператор
  • SQL%ROWCOUNT – общее количество строк, на которые повлияли dml-операторы в цикле forall

Пример использования
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);

TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
e_ids enum_t;

TYPE dept_t IS TABLE OF employees.department_id%TYPE;
d_ids dept_t;
BEGIN
FORALL j IN depts.FIRST .. depts.LAST
DELETE FROM emp_temp
WHERE department_id = depts(j)
RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
END;

  • При возникновении исключения в любом из dml-операторов в цикле, транзакция полностью откатывается
  • Если описать обработчик ошибок, в нем можно зафиксировать успешно выполнившиеся операторы dml (это те операторы, которые выполнились до возникновения исключения).
  • Конструкция FORALL j IN collection.FIRST.. collection.LAST SAVE EXCEPTIONS

.ERROR_INDEX – значение индекса j, при котором произошло исключение (sql%bulk_exception(i).error_index)

[b]Collection exceptions[/b]

  • COLLECTION_IS_NULL – попытка работать с неинициализированной коллекцией
  • NO_DATA_FOUND – попытка прочитать удаленный элемент
  • SUBSCRIPT_BEYOND_COUNT – выход за границы коллекции
  • SUBSCRIPT_OUTSIDE_LIMIT – индекс вне предела допустимого диапазона
  • VALUE_ERROR – индекс равен null или не конвертируется в integer

Примеры ситуаций, генерирующих исключения
DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList;
BEGIN
nums(1) := 1; -- raises COLLECTION_IS_NULL
nums := NumList(1, 2);
nums(NULL) := 3; -- raises VALUE_ERROR
nums(0) := 3; -- raises SUBSCRIPT_BEYOND_COUNT
nums(3) := 3; --raises SUBSCRIPT_OUTSIDE_LIMIT
nums.Delete(1);
IF nums(1) = 1 THEN . -- raises NO_DATA_FOUND
END;

<a name="TwentyOne">
[b]DBMS_SESSION.FREE_UNUSED_USER_MEMORY[/b]

  • Процедура DBMS_SESSION.FREE_UNUSED_USER_MEMORY возвращает неиспользуемую более память системе
  • В документации Oracle процедуру советуют использовать «редко и благоразумно».
  • В случае подключения в режиме Dedicated Server вызов этой процедуры возвращает неиспользуемую PGA память операционной системе
  • В случае подключения в режиме Shared Server вызов этой процедуры возвращает неиспользуемую память в Shared Pool

В каких случаях нужно освобождать память:

  • Большие сортировки, когда используется вся область sort_area_size
  • Компиляция больших PL/SQL пакетов, процедур или функций
  • Хранение больших объемов данных в индексных таблицах PL/SQL

Пример использования
CREATE PACKAGE foobar
type number_idx_tbl is table of number indexed by binary_integer;

store1_table number_idx_tbl; -- PL/SQL indexed table
store2_table number_idx_tbl; -- PL/SQL indexed table
store3_table number_idx_tbl; -- PL/SQL indexed table
.
END; -- end of foobar

DECLARE
.
empty_table number_idx_tbl; -- uninitialized ("empty") version
BEGIN
FOR i in 1..1000000 loop
store1_table(i) := i; -- load data
END LOOP;
.
store1_table := empty_table; -- "truncate" the indexed table
.
-
dbms_session.free_unused_user_memory; -- give memory back to system

store1_table(1) := 100; -- index tables still declared;
store2_table(2) := 200; -- but truncated.
.
END;

В Oracle PL/SQL метод DELETE удаляет все элементы из коллекции любого типа. Эта операция немедленно освобождает память, выделенную для удаляемых элементов.

Синтаксис

Синтаксис метода коллекций DELETE в Oracle PL/SQL.

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

collection_name - имя коллекции один из следующих типов associative arrays или nested tables.
DELETE(n) - удаляет элемент, индекс которого равен n , если этот элемент существует; в противном случае он ничего не делает.
DELETE(m,n) удаляет все элементы, индексы которых находятся в диапазоне m..n , если существуют как m , так и n , а m ; в противном случае он ничего не делает.

Примечание

Пример

Рассмотрим некоторые пример, чтобы понять как использовать метод коллекций DELETE в Oracle PL/SQL.

Пример метода DELETE с Nested Tables

В этом примере мы используем Nested Tables и делаем следующее:

  1. объявляем переменную Nested Tables
  2. инициализируем ее шестью элементами
  3. удаляем и затем восстанавливаем второй элемент
  4. удаляем ряд элементов, а затем восстанавливаем один из них
  5. и в конце удаляем все элементы

Восстановленные элементы занимают ту же память, что и соответствующие удаленные элементы. Процедура print_nt печатает переменную Nested Tables после инициализации и после каждой операции DELETE.

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS

Пример метода DELETE с Associative Arrays

Следующий пример заполняет Associative Arrays, индексированный строкой, и удаляет все элементы, что освобождает выделенную им память. Затем пример заменяет удаленные элементы, то есть добавляет новые элементы, которые имеют те же индексы, что и удаленные элементы. Новые элементы замены не занимают ту же память, что и соответствующие удаленные элементы. Наконец, пример удаляет один элемент, а затем ряд элементов. Процедура print_aa_str показывает эффекты операций.

Встроенные методы коллекций PL/SQL

PL/SQL предоставляет для создаваемых вами коллекций множество встроенных функций и процедур, называемых методами коллекций. Эти методы предназначены для получения информации о содержимом коллекции и ее изменения. Их полный список приведен в табл. 1.

Метод (функция или процедура) Описание
COUNT (функция) Возвращает текущее значение элементов в коллекции
DELETE (процедура) Удаляет из коллекции один или несколько элементов. Уменьшает значение, возвращаемое функцией COUNT, если заданные элементы еще не удалены. Со структурами VARRAY может использоваться только для удаления всего содержимого
EXISTS (функция) Возвращает значение TRUE или FALSE , определяющее, существует ли в коллекции заданный элемент
EXTEND (процедура) Увеличивает количество элементов во вложенной таблице или VARRAY , а также значение, возвращаемое функцией COUNT
FIRST, LAST (функции) Возвращают индексы первого ( FIRST ) и последнего ( LAST ) элемента в коллекции
LIMIT (функция) Возвращает максимальное количество элементов в массиве VARRAY
PRIOR, NEXT (функции) Возвращают индексы элементов, предшествующих заданному ( PRIOR ) и следующему за ним ( NEXT ). Всегда используйте PRIOR и NEXT для перебора коллекций, особенно при работе с разреженными (или потенциально разреженными) коллекциями
TRIM (функция) Удаляет элементы, начиная с конца коллекции (элемент с наибольшим индексом)

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

Рассмотрим синтаксис вызова методов на примере LAST . Эта функция возвращает наибольший индекс элемента ассоциативного массива. Стандартный вызов этой функции выглядел бы так:

Иначе говоря, ассоциативный массив передается ей в качестве аргумента. Но поскольку функция LAST является методом, она «принадлежит» объекту — в данном случае ассоциативному массиву. Правильный синтаксис ее вызова выглядит так:

В общем случае синтаксис вызова методов ассоциативного массива выглядит так:

  • Операция, не требующая передачи аргументов:
  • Операция, аргументами которой являются индексы элементов:

Например, следующая команда возвращает TRUE , если в ассоциативном массиве company_tab определена запись 15:

Методы коллекций недоступны из SQL; их можно использовать только в программах PL/SQL.

Метод COUNT

Метод COUNT возвращает количество элементов в ассоциативном массиве, вложенной таблице или массиве VARRAY . В значении не учитываются элементы, удаленные из коллекции методом DELETE или TRIM .

Синтаксис вызова:

Рассмотрим пример. Прежде чем что-либо делать с коллекцией, мы проверяем, содержит ли она хотя бы один элемент:

Граничные условия

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

Возможные исключения

При вызове метода COUNT для неинициализированной вложенной таблицы или VARRAY инициируется заранее определенное исключение COLLECTION_IS_NULL . Такое исключение не может возникнуть при работе с ассоциативными массивами, не требующими инициализации.

Метод DELETE

Метод DELETE предназначен для удаления одного, нескольких или всех элементов ассоциативного массива, вложенной таблицы или массива VARRAY . При вызове без аргументов он удаляет все элементы коллекции. Вызов DELETE (i) удаляет i-й элемент вложенной таблицы или ассоциативного массива. А вызов DELETE ( i,j ) удаляет все элементы с индексами от i до j включительно. Если коллекция представляет собой ассоциативный массив, индексируемый строками, i и j должны быть строковыми значениями; в противном случае они являются целыми числами.

При вызове с аргументами метод резервирует место, занимавшееся «удаленным» элементом, и позднее этому элементу можно присвоить новое значение.

Фактически PL/SQL освобождает память лишь при условии, что программа удаляет количество элементов, достаточное для освобождения целой страницы памяти. (Если же метод DELETE вызывается без параметров и очищает всю коллекцию, память освобождается немедленно.)

Применительно к массивам VARRAY метод DELETE может вызываться только без аргументов. Иначе говоря, с помощью указанного метода из этой структуры нельзя удалять отдельные элементы, поскольку в таком случае она станет разреженной, что недопустимо. Единственный способ удалить из VARRAY один или несколько элементов — воспользоваться методом TRIM , предназначенным для удаления группы расположенных рядом элементов, начиная с конца коллекции.

Следующая процедура удаляет из коллекции все элементы, кроме последнего. В ней используются четыре метода: FIRST — для получения номера первого удаляемого элемента; LAST — для получения номера последнего удаляемого элемента; PRIOR — для определения номера предпоследнего элемента; DELETE — для удаления всех элементов, кроме последнего:

Несколько дополнительных примеров:

  • Удаление всех строк из таблицы names :
  • Удаление 77-й строки из таблицы globals :
  • Удаление из таблицы temp_reading всех элементов, начиная с индекса –15 000 и до индекса 0 включительно:

Граничные условия

Если значения индексов i и/или j указывают на несуществующие элементы, DELETE пытается «сделать наилучшее» и не генерирует исключение. Например, если таблица содержит три элемента с индексами 1, 2 и 3, то вызов метода DELETE (–5,1) удалит только один элемент с индексом 1, а вызов DELETE (–5) не изменит состояния коллекции.

Возможные исключения

Вызов метода DELETE для неинициализированной вложенной таблицы или массива VARRAY инициирует исключение COLLECTION_ IS_NULL .

Метод EXISTS

Метод EXISTS используется с вложенными таблицами, ассоциативными массивами и массивами VARRAY для определения наличия в коллекции заданного элемента. Если таковой имеется, метод возвращает значение TRUE , а если отсутствует — значение FALSE . Значение NULL не возвращается ни при каких условиях. Кроме того, EXISTS возвращает FALSE и в том случае, если заданный элемент был удален из коллекции с помощью метода TRIM или DELETE .

Следующий блок проверяет, присутствует ли заданный элемент в коллекции, и при положительном ответе присваивает ему значение NULL :

Граничные условия

Возможные исключения

Метод EXISTS не инициирует исключения.

Метод EXTEND

Чтобы добавить элемент во вложенную таблицу или массив VARRAY , необходимо сначала выделить для него область памяти. Соответствующая операция, не зависящая от присваивания элементу значения, выполняется методом EXTEND . Следует помнить, что указанный метод неприменим к ассоциативным массивам.

Метод EXTEND , как уже было сказано, добавляет элементы в коллекцию. При вызове без аргументов он добавляет один элемент со значением NULL . Вызов EXTEND ( n ) присоединяет n элементов со значением NULL , а вызов EXTEND ( n,i ) — n элементов, и всем им присваивает значение i -го элемента. Последняя форма метода применяется к коллекциям, для элементов которых задано ограничение NOT NULL .

Синтаксис перегруженного метода EXTEND :

В следующем примере процедура push добавляет в список один элемент и присваивает ему новое значение:

В другом фрагменте кода метод EXTEND используется для включения в коллекцию 10 новых элементов с одинаковыми значениями. Сначала в коллекцию добавляется один элемент, которому явно присваивается нужное значение. При повторном вызове метода EXTEND в коллекцию добавляется еще 9 элементов, которым присваивается значение первого элемента коллекции new_value :

Граничные условия

Если параметр n имеет значение NULL , метод не выполнит никаких действий.

Возможные исключения

При вызове метода EXTEND для неинициализированной вложенной таблицы или VARRAY инициируется исключение COLLECTION_IS_NULL . Попытка добавить в массив VARRAY элементы, индекс которых превышает максимальный индекс массива в его объявлении, инициирует исключение SUBSCRIPT_BEYOND_LIMIT .

Методы FIRST и LAST

Методы FIRST и LAST возвращают соответственно наименьший и наибольший индексы элементов вложенной таблицы, ассоциативного массива или массива VARRAY . Для ассоциативных массивов, индексируемых строками, эти методы возвращают строки; «наименьшее» и «наибольшее» значения определяются порядком набора символов, используемого данным сеансом. Для других типов коллекций методы возвращают целые числа.

Синтаксис этих функций:

Так, следующий фрагмент перебирает все элементы коллекции от начала к концу:

Запомните, что такой цикл будет выполнен корректно (то есть не породит исключения NO_DATA_FOUND ) лишь при условии, что коллекция является плотной.

В следующем примере для добавления элементов в конец ассоциативного массива используется оператор COUNT . Цикл FOR с курсором используется для копирования данных из базы в ассоциативный массив. При выборке первой записи коллекция companies пуста, поэтому COUNT возвращает 0.

Граничные условия

Если методы FIRST и LAST вызываются для инициализированных коллекций, не содержащих ни одного элемента, они возвращают NULL . Для массива VARRAY , всегда содержащего хотя бы один элемент, FIRST всегда возвращает 1, а LAST — то же значение, что и метод COUNT .

Возможные исключения

При вызове методов FIRST и LAST для неинициализированной вложенной таблицы или массива VARRAY инициируется исключение COLLECTION_ IS_NULL.

Метод LIMIT

Метод LIMIT возвращает максимальное количество элементов, которое можно определить в массиве VARRAY . В случае вложенной таблицы или ассоциативного массива он возвращает NULL . Синтаксис этого метода:

В следующем примере перед добавлением нового элемента в конец массива VARRAY мы сначала проверяем, есть ли в нем еще свободное место:

Граничные условия

У метода LIMIT граничных условий не существует.

Возможные исключения

Вызов метода LIMIT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_ IS_NULL .

Методы PRIOR и NEXT

Методы PRIOR и NEXT используются для перемещения по коллекциям — вложенным таблицам, ассоциативным массивам и массивам VARRAY . Метод PRIOR возвращает индекс предыдущего, а метод NEXT — следующего элемента коллекции. Следующая функция возвращает сумму чисел, хранящихся в коллекции list_t :

Та же программа, но с перебором элементов от последней к первой определенной записи коллекции:

В данном случае направление перемещения не имеет значения, но в других программах оно может повлиять на результат обработки.

Граничные условия

Методы PRIOR и NEXT для инициализированной коллекции, не содержащей ни одного элемента, возвращают NULL . Если значение i больше или равно COUNT , метод NEXT возвращает NULL ; если i меньше или равно FIRST , метод PRIOR возвращает NULL .

Вплоть до версии Oracle12c, если коллекция не пуста, а параметр i больше или равен COUNT , метод PRIOR возвращает LAST ; если параметр i меньше FIRST , метод NEXT возвращает FIRST . Однако сохранение такого поведения в будущих версиях Oracle не гарантировано.

Возможные исключения

Вызов методов PRIOR и NEXT для неинициализированной вложенной таблицы или массива VARRAY генерирует исключение COLLECTION_IS_NULL .

Метод TRIM

Метод TRIM удаляет n последних элементов коллекции — вложенной таблицы или массива VARRAY . Если метод вызывается без аргументов, он удалит только один элемент. Как упоминалось ранее, при совместном использовании методов TRIM и DELETE возможна накладка: если заданный в вызове метода TRIM элемент был уже удален методом DELETE , метод TRIM «повторит» удаление, но считает его частью n, поэтому количество реально удаленных элементов окажется меньшим, чем вы рассчитывали.

Попытка вызова метода TRIM для ассоциативного массива приведет к ошибке компиляции.

Синтаксис метода TRIM :

Следующая функция извлекает из списка последнее значение и возвращает его вызывающему блоку. Операция извлечения реализуется как выборка значения с последующим усечением коллекции на один элемент:

Граничные условия

Если значение n равно NULL , метод не выполнит никаких действий.

Возможные исключения

При попытке удалить больше элементов, чем имеется в коллекции, инициируется исключение SUBSCRIPT_BEYOND_COUNT . Если метод TRIM вызывается для неинициализированной вложенной таблицы или массива VARRAY , инициируется исключение COLLECTION_IS_NULL .

Вызывая методы TRIM и DELETE для одной и той же коллекции, можно получить неожиданные результаты. На сколько элементов станет меньше в коллекции, если удалить последний элемент методом DELETE , а затем вызвать метод TRIM с тем же значением параметра? Казалось бы, это приведет к удалению двух элементов, но в действительности оба метода удалят один и тот же элемент. Чтобы избежать накладок, компания Oracle рекомендует использовать только один из этих двух методов при работе с конкретной коллекцией.

В коллекции каждый элемент идентифицируется термином «индекс». Каждому элементу в коллекции присваивается уникальный индекс. Данные в этой коллекции можно манипулировать или получать, ссылаясь на этот уникальный индекс.

Коллекции наиболее полезны, когда необходимо обрабатывать или обрабатывать большие данные одного типа. Коллекции могут заполняться и обрабатываться как единое целое с помощью опции «BULK» в Oracle.

В этом уроке вы узнаете

Коллекции классифицируются на основе структуры, индекса и хранилища, как показано ниже.

  • Индекс по таблицам (также известный как ассоциативный массив)
  • Вложенные таблицы
  • VARRAY,

В любой момент данные в коллекции могут быть названы тремя терминами «Имя коллекции», «Подстрочный индекс», «Имя поля / столбца» как «<имя_собрания> (<индекс>). <Имя_ столбца>». Об этих вышеупомянутых категориях коллекций вы узнаете в следующем разделе.

VARRAY,

На рисунке ниже показано распределение памяти Varray (плотного) в схематическом виде.

индекс 1 2 3 4 5 6 7
Ценность Xyz DFV Сд CxS Vbc Nhu Qwe

Синтаксис для VARRAY:

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

На приведенном ниже рисунке схематично показано распределение памяти для вложенной таблицы (плотной и разреженной). Черное пространство элементов обозначает пустой элемент в коллекции, т.е. разреженный.

индекс 1 2 3 4 5 6 7
Значение (плотное) Xyz DFV Сд CxS Vbc Nhu Qwe
Значение (редкие) Qwe Asd афг Asd Wer

Синтаксис для вложенной таблицы:

Index-на-таблицы

  • Индекс может целого числа или строк. Во время создания коллекции следует указать тип индекса.
  • Эти коллекции не хранятся последовательно.
  • Они всегда редки по своей природе.
  • Размер массива не фиксирован.
  • Они не могут быть сохранены в столбце базы данных. Они должны быть созданы и использованы в любой программе в этой конкретной сессии.
  • Они дают больше гибкости с точки зрения поддержания индекса.
  • Индексы также могут иметь отрицательную последовательность индексов.
  • Их более целесообразно использовать для относительно небольших коллективных значений, в которых коллекция может быть инициализирована и использована в одних и тех же подпрограммах.
  • Их не нужно инициализировать перед началом их использования.
  • Он не может быть создан как объект базы данных. Он может быть создан только внутри подпрограммы, которая может использоваться только в этой подпрограмме.
  • BULK COLLECT нельзя использовать в этом типе коллекции, так как нижний индекс должен быть задан явно для каждой записи в коллекции.

На приведенном ниже рисунке схематично показано распределение памяти для вложенной таблицы (разреженной). Черное пространство элементов обозначает пустой элемент в коллекции, т.е. разреженный.

Нижний индекс (varchar) ПЕРВЫЙ ВТОРОЙ В ТРЕТЬИХ ЧЕТВЕРТЫЙ ПЯТЫЙ ШЕСТОЙ СЕДЬМОЙ
Значение (редкие) Qwe Asd афг Asd Wer

Синтаксис для индекса по таблице

Конструктор и концепция инициализации в коллекциях

  • Для коллекций эти конструкторы должны вызываться явно для его инициализации.
  • Обе таблицы Varray и Nested должны быть инициализированы с помощью этих конструкторов, прежде чем их можно будет использовать в программе.
  • Конструктор неявно расширяет выделение памяти для коллекции (кроме Varray), поэтому конструктор также может присваивать переменные коллекциям.
  • Присвоение значений коллекции через конструкторы никогда не сделает коллекцию разреженной.

Методы сбора

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

метод Описание СИНТАКСИС
СУЩЕСТВУЕТ (n) Этот метод будет возвращать логические результаты. Он вернет «ИСТИНА», если n- й элемент существует в этой коллекции, иначе он вернет ЛОЖЬ. Только неиспользуемые функции могут быть использованы в неинициализированной коллекции <Имя_выборки> .EXISTS (element_position)
COUNT Дает общее количество элементов, присутствующих в коллекции <Collection_name> .Count
ПРЕДЕЛ Возвращает максимальный размер коллекции. Для Varray он вернет фиксированный размер, который был определен. Для вложенной таблицы и индекса по таблице она дает NULL <Collection_name> .limit
ПЕРВЫЙ Возвращает значение первой индексной переменной (индекса) коллекций <Collection_name> .Первый
ПРОШЛОЙ Возвращает значение последней индексной переменной (индекса) коллекций <Collection_name> .LAST
ПРИОР (n) Возвращает предшествующую индексную переменную в коллекции n- го элемента. При отсутствии предшествующего значения индекса возвращается NULL <Имя_выборки> .Перед (п)
ДАЛЬШЕ (n) Возвращает преуспевающую индексную переменную в коллекции n- го элемента. Если нет успешных значений индекса, возвращается NULL <Имя_выборки> .next (п)
ПРОДЛИТЕ Расширяет один элемент в коллекции в конце <Collection_name> .extend
EXTEND (n) Расширяет n элементов в конце коллекции <Имя_выборки> .extend (п)
EXTEND (n, i) Расширяет n копий i- го элемента в конце коллекции <Имя_выборки> .extend (п, я)
ОТДЕЛКА Удаляет один элемент из конца коллекции <Collection_name> .trim
TRIM (n) Удаляет n элементов из конца коллекции <collection_name> .TRIM (n)
УДАЛЯТЬ Удаляет все элементы из коллекции. Делает коллекцию пустой <Collection_name> .DELETE
УДАЛИТЬ (n) Удаляет n-й элемент из коллекции. Если n- й элемент НЕДЕЙСТВИТЕЛЕН, то это ничего не сделает <Имя_выборки> .DELETE (п)
УДАЛИТЬ (м, н) Удаляет элемент в диапазоне от m- го до n- го в коллекции <Имя_выборки> .DELETE (т, п)

Пример 1: Тип записи на уровне подпрограммы

Сложные типы данных в PL / SQL

Объяснение кода:

Вывод : Как вы можете видеть на приведенном выше снимке экрана, когда приведенный выше код выполняется, вы получите следующий вывод

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

      
  • Программа конвертер pdf в excel обзор лучших программ
    •   
  • Оплата по заявке в 1с 11 как убрать галочку
    •   
  • Как сделать резервную копию документа ворд
    •   
  • Application vnd ms excel что это
    •   
  • 1с получить адрес физического лица
  • Контакты
  • Политика конфиденциальности