Коллекции в PL/SQL

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

Язык PL/SQL поддерживает три вида коллекций:

1. вложенные таблицы (nested tables);
2. ассоциативные массивы (associative arrays);
3. массивы переменной длины (variable-size arrays).

Плюсы использования коллекций:

• Кеширование статичной информации
• Отслеживание элементов данных для спец.обработки
• Хранение списков непосредственно в столбцах таблицы

Вложенные таблицы (Nested Tables)

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

Максимальное число строк вложенной таблицы составляет 2 гигабайта.

Наиболее важным отличием между вложенными и индексированными таблицами является то, что вложенные таблицы являются постоянной формой коллекции.

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

• Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
• Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
• Требуется инициализация, при использовании в PL\SQL
• Порядок элементов не зафиксирован
• Используется память PGA (для всех 3 типов коллекций)
• При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND

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

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

Позволяет работать со столбцами как с единой переменной – массивом.

Является временной формой коллекции, существует в течении сессии.

Другое название ассоциативных массивов – индексированные таблицы или pl\sql таблицы.

Ассоциативный массив - это наиболее часто используемый вид коллекции. Его не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам.

Массив переменной длины (VARRAY)

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

Особенности:

• Размер всегда ограничен и массив не может быть разреженным.
• Максимальное количество элементов указывается при определении типа.
• Могут использоваться как в PL\SQL так и в таблицах.
• Порядок элементов при сохранение и извлечении, в отличии от вложенных таблиц, сохраняется.
• Индекс – положительное число от 1 до 2,147,483,647
• Нельзя удалить произвольный элемент массива

Как узнать размер индекса в Oracle

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

Хранение паролей в БД Oracle

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

В данном примере мы будем использовать пакет DBMS_OBFUSCATION_TOOLKIT, который доступен, начиная с Oracle8i:

• Таблицы с секретными данными
• Пакет для работы с шифрованием
• Тестирование

Узнать версию БД Oracle и совместимость

Простейший код для того, чтобы узнать версию БД и совместимость:

DECLARE
  l_version  VARCHAR2(100);
  l_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version       => l_version,
                           compatibility => l_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || l_version || '  Compatibility: ' || l_compatibility);
END;

Comma_to_table & table_to_comma

Есть удобное средство для создания небольших виртуальных таблиц со значениями, взятыми из строкового выражения с "," в качестве разделителя.