Oracle Колекції PL/SQL: масиви, вкладені та індекси за таблицями

Що таке колекція?

Колекція — це впорядкована група елементів певного типу даних. Це може бути колекція простого типу даних або складного типу даних, такого як визначені користувачем або типи записів.

У колекції кожен елемент ідентифікується терміном, який називається «підрядковий». Кожному елементу присвоюється унікальний нижній індекс, і дані можна маніпулювати або отримувати, звертаючись до цього унікального нижнього індексу.

Колекції найбільш корисні, коли потрібно обробити або маніпулювати великою кількістю даних одного типу. Колекції можна заповнювати та маніпулювати ними як єдиним цілим, використовуючи опцію «BULK» у Oracle.

Колекції класифікуються за структурою, нижнім індексом та сховищем, як показано нижче:

• Таблиці з індексуванням (також відомі як асоціативні масиви)
• Вкладені таблиці
• Варіанти

У будь-який момент дані в колекції можна називати трьома термінами: назвою колекції, нижнім індексом та назвою поля або стовпця, наприклад « ( ). «Ви дізнаєтеся про ці категорії колекцій у розділах нижче».

Типи колекцій з першого погляду

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

Варіанти

Varray — це колекція, в якій розмір масиву фіксований і не може бути перевищений. Нижній індекс Varray — це числове значення. Атрибути Varray:

• Верхня межа розміру фіксована.
• Заповнюється послідовно, починаючи з нижнього індексу '1'.
• Цей тип колекції завжди щільний; ми не можемо видаляти окремі елементи масиву. Об'єкт Varray можна видалити цілком або обрізати з кінця.
• Оскільки він завжди щільний, він має дуже малу гнучкість.
• Доцільніше, коли розмір масиву відомий, і аналогічні дії виконуються над усіма елементами.
• Підрядковий індекс та кількість елементів у колекції завжди залишаються стабільними.
• Перед використанням його необхідно ініціалізувати. Будь-яка операція, окрім EXISTS, з неініціалізованою колекцією призводить до помилки.
• Його можна створити як об'єкт бази даних, видимий по всій базі даних, або всередині підпрограми для використання лише там.

На рисунку нижче пояснюється розподіл пам'яті для Varray (щільної) класу.

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

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

• У наведеному вище синтаксисі type_name оголошується як VARRAY типу 'DATA_TYPE' для заданого обмеження розміру. Тип даних може бути простим або складним.

Вкладені таблиці

Вкладена таблиця — це колекція, в якій розмір масиву не фіксований. Вона має числовий тип нижнього індексу. Детальніше про вкладений тип таблиці:

• Вкладена таблиця не має верхнього обмеження розміру.
• Оскільки верхня межа не фіксована, пам'ять потрібно розширювати щоразу перед використанням за допомогою ключового слова 'EXTEND'.
• Заповнюється послідовно, починаючи з нижнього індексу '1'.
• Цей тип колекції може бути обома щільний і рідкісний; ми можемо створити його як щільний, а також випадковим чином видаляти окремі елементи, що робить його розрідженим.
• Це надає більше гнучкості для видалення елементів масиву.
• Він зберігається в таблиці бази даних, згенерованій системою, і може бути використаний у запиті на вибірку для отримання значень.
• Підрядковий індекс та кількість можуть змінюватися.
• Перед використанням його необхідно ініціалізувати. Будь-яка операція, окрім EXISTS, з неініціалізованою колекцією призводить до помилки.
• Його можна створити як об'єкт бази даних, видимий по всій базі даних, або всередині підпрограми для використання лише там.

На рисунку нижче пояснюється розподіл пам'яті вкладеної таблиці (щільної та розрідженої). Порожній простір елементів позначає розріджений елемент.

Синтаксис для вкладеної таблиці:

TYPE <type_name> IS TABLE OF <DATA_TYPE>;

• У наведеному вище синтаксисі type_name оголошується як вкладена таблична колекція типу 'DATA_TYPE'. Тип даних може бути простим або складним.

Таблиця з індексом

Індексна таблиця — це колекція, в якій розмір масиву не фіксований. На відміну від інших типів колекцій, нижній індекс індексної таблиці може бути визначений користувачем. Атрибути індексної таблиці:

• Нижній індекс може бути цілим числом або рядком. Тип нижнього індексу слід вказати під час створення колекції.
• Ці колекції не зберігаються послідовно.
• За своєю природою вони завжди розріджені.
• Розмір масиву не фіксований.
• Їх не можна зберігати у стовпці бази даних. Вони створюються та використовуються протягом певного сеансу.
• Вони надають більшу гнучкість у підтримці нижнього індексу.
• Нижні індекси можуть бути негативною послідовністю.
• Вони більше підходять для відносно менших значень колекцій, що використовуються в одній підпрограмі.
• Їх не потрібно ініціалізувати перед використанням.
• Їх не можна створити як об'єкт бази даних; вони створюються лише всередині підпрограми.
• Функцію BULK COLLECT не можна використовувати з цим типом колекції, оскільки нижній індекс має бути вказано явно для кожного запису.

На рисунку нижче пояснюється розподіл пам'яті для таблиці з індексами (розріджений елемент). Порожній простір елементів позначає розріджений елемент.

Синтаксис для таблиці з індексуванням:

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

• У наведеному вище синтаксисі type_name оголошується як таблична колекція типу 'DATA_TYPE', що упорядковується за індексом. Змінна з підрядковим індексом має тип VARCHAR2 з максимальним розміром 10.

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

Конструктори – це вбудовані функції, що надаються Oracle які мають таку саму назву, як об'єкт або колекція. Вони виконуються першими щоразу, коли до об'єкта або колекції звертаються вперше в сеансі. Важливі деталі конструктора в контексті колекції:

• Для колекцій ці конструктори необхідно викликати явно для ініціалізації колекції.
• Як Varray, так і вкладені таблиці необхідно ініціалізувати за допомогою цих конструкторів, перш ніж до них звертатися в програмі.
• Конструктор неявно розширює виділення пам'яті для колекції (за винятком Varray), тому він також може присвоювати колекції змінні.
• Присвоєння значень через конструктори ніколи не робить колекцію розрідженою.

Методи збору

Oracle надає багато функцій для маніпулювання та роботи з колекціями. Ці функції визначають та змінюють різні атрибути колекції. У таблиці нижче наведено різні функції та їх описи.

Приклад 1: Тип запису на рівні підпрограми

У цьому прикладі ми бачимо, як заповнити колекцію за допомогою 'ОБ'ЄМНИЙ ЗБІР«і як посилатися на дані колекції».

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
TYPE emp_det_tbl IS TABLE OF emp_det;
guru99_emp_rec emp_det_tbl:= emp_det_tbl();
BEGIN
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1000,'AAA',25000,1000);
INSERT INTO emp (emp_no,emp_name, salary, manager) VALUES (1001,'XXX',10000,1000);
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
INSERT INTO emp (emp_no,emp_name,salary, manager) VALUES (1003,'ZZZ',7500,1000);
COMMIT;
SELECT emp_no,emp_name,manager,salary BULK COLLECT INTO guru99_emp_rec
FROM emp;
dbms_output.put_line ('Employee Detail');
FOR i IN guru99_emp_rec.FIRST..guru99_emp_rec.LAST
LOOP
dbms_output.put_line ('Employee Number: '||guru99_emp_rec(i).emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec(i).emp_name);
dbms_output.put_line ('Employee Salary:'|| guru99_emp_rec(i).salary);
dbms_output.put_line('Employee Manager Number:'||guru99_emp_rec(i).manager);
dbms_output.put_line('--------------------------------');
END LOOP;
END;
/

Code Пояснення

• Code рядки 2-8: Тип запису 'emp_det' оголошується зі стовпцями emp_no, emp_name, manager та salary типу даних NUMBER, VARCHAR2, NUMBER та NUMBER.
• Code рядок 9: Створення колекції 'emp_det_tbl' з елемента типу запису 'emp_det'.
• Code рядок 10: Оголошення змінної 'guru99_emp_rec' типу 'emp_det_tbl' та її ініціалізація конструктором null.
• Code рядки 12-15: Вставлення зразків даних у таблицю «emp».
• Code рядок 16: Здійснення транзакції вставки.
• Code рядок 17: Вибір записів з таблиці 'emp' та масове заповнення змінної колекції за допомогою "BULK COLLECT". Змінна 'guru99_emp_rec' тепер містить усі записи, присутні в таблиці 'emp'.
• Code рядки 19-26: Налаштування циклу 'FOR' для друку всіх записів у колекції по черзі. Методи колекції FIRST та LAST використовуються як нижня та верхня межі петля.

вихід: Коли наведений вище код буде виконано, ви отримаєте наступний вивід.

Employee Detail
Employee Number: 1000
Employee Name: AAA
Employee Salary: 25000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1002
Employee Name: YYY
Employee Salary: 15000
Employee Manager Number: 1000
----------------------------------------------
Employee Number: 1003
Employee Name: ZZZ
Employee Salary: 7500
Employee Manager Number: 1000
----------------------------------------------

Поширені запитання