Oracle Колекції PL/SQL: масиви, вкладені та індекси за таблицями
Фіона Браун
Що таке колекція?
Колекція — це впорядкована група елементів певних типів даних. Це може бути набір простих типів даних або складних типів даних (наприклад, визначені користувачем або типи записів).
У колекції кожен елемент ідентифікується терміном, який називається «підрядковий». Кожному елементу в колекції присвоюється унікальний індекс. Даними в цій колекції можна маніпулювати або отримувати, посилаючись на цей унікальний індекс.
Колекції є найбільш корисними речами, коли потрібно обробити або маніпулювати великими даними одного типу. Колекції можна заповнювати та маніпулювати як ціле за допомогою параметра BULK Oracle.
Колекції класифікуються на основі структури, індексу та місця зберігання, як показано нижче.
- Індекс за таблицями (також відомий як асоціативний масив)
- Вкладені таблиці
- Варіанти
У будь-який момент дані в колекції можна позначати трьома термінами: назва колекції, індекс, назва поля/стовпця як « ( ). ”. Ви збираєтеся дізнатися про ці вищезазначені категорії колекцій далі в розділі нижче.
Варіанти
Varray — це метод збирання, у якому розмір масиву є фіксованим. Розмір масиву не може перевищувати його фіксоване значення. Нижній індекс Varray має числове значення. Нижче наведено атрибути Varrays.
- Верхня межа розміру фіксована
- Заповнюється послідовно, починаючи з нижнього індексу "1"
- Цей тип колекції завжди щільний, тобто ми не можемо видалити елементи масиву. Varray можна видалити цілком або обрізати з кінця.
- Оскільки він завжди щільний за своєю природою, він має дуже меншу гнучкість.
- Доцільніше використовувати, коли відомий розмір масиву, і виконувати однакові дії з усіма елементами масиву.
- Індекс і послідовність завжди залишаються стабільними, тобто індекс і кількість колекції завжди однакові.
- Їх потрібно ініціалізувати перед використанням у програмах. Будь-яка операція (крім операції EXISTS) над неініціалізованою колекцією викличе помилку.
- Його можна створити як об’єкт бази даних, видимий у всій базі даних або всередині підпрограми, який можна використовувати лише в цій підпрограмі.
На малюнку нижче схематично пояснюється розподіл пам’яті Varray (dense).
| Індекс | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| значення | Сиз | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
Синтаксис для VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- У наведеному вище синтаксисі type_name оголошено як VARRAY типу "DATA_TYPE" для заданого обмеження розміру. Тип даних може бути простим або складним.
Вкладені таблиці
Вкладена таблиця — це колекція, у якій розмір масиву не фіксований. Він має тип числового індексу. Нижче наведено додаткові описи типу вкладеної таблиці.
- Вкладена таблиця не має верхнього обмеження розміру.
- Оскільки верхня межа розміру не є фіксованою, колекцію, пам’ять потрібно розширювати кожного разу, перш ніж ми її використовуємо. Ми можемо розширити колекцію за допомогою ключового слова "EXTEND".
- Заповнюється послідовно, починаючи з нижнього індексу "1".
- Цей тип колекції може бути обох щільний і рідкісний, тобто ми можемо створити колекцію як щільну, а також можемо випадково видалити окремий елемент масиву, що зробить її розрідженою.
- Це дає більше гнучкості щодо видалення елемента масиву.
- Він зберігається в створеній системою таблиці бази даних і може використовуватися в запиті на вибірку для отримання значень.
- Індекс і послідовність не є стабільними, тобто нижній індекс і кількість елементів масиву можуть змінюватися.
- Їх потрібно ініціалізувати перед використанням у програмах. Будь-яка операція (крім операції EXISTS) над неініціалізованою колекцією викличе помилку.
- Його можна створити як об’єкт бази даних, видимий у всій базі даних або всередині підпрограми, який можна використовувати лише в цій підпрограмі.
На малюнку нижче схематично показано розподіл пам’яті вкладеної таблиці (щільної та розрідженої). Чорний колір позначає порожній елемент у колекції, тобто розріджений.
| Індекс | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Значення (щільний) | Сиз | Dfv | Sde | Cxs | Vbc | Nhu | Qwe |
| Значення (розріджене) | Qwe | Asd | Afg | Asd | Wer |
Синтаксис для вкладеної таблиці:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- У наведеному вище синтаксисі type_name оголошено як колекцію вкладених таблиць типу "DATA_TYPE". Тип даних може бути простим або складним.
Індекс за таблицею
Index-by-table — колекція, у якій розмір масиву не фіксований. На відміну від інших типів колекції, у колекції індексів за таблицею індекс може складатися з визначення користувача. Нижче наведено атрибути індексу за таблицею.
- Індекс може складатися з цілого числа або рядків. Під час створення колекції слід зазначити підрядковий тип.
- Ці колекції не зберігаються послідовно.
- За своєю природою вони завжди розріджені.
- Розмір масиву не фіксований.
- Вони не можуть зберігатися в стовпці бази даних. Вони повинні бути створені та використані в будь-якій програмі в цьому конкретному сеансі.
- Вони надають більшу гнучкість щодо підтримки нижнього індексу.
- Індекси також можуть мати негативну послідовність.
- Їх доцільніше використовувати для відносно менших колективних значень, у яких колекція може бути ініціалізована та використана в межах однієї підпрограми.
- Їх не потрібно ініціалізувати перед початком використання.
- Його не можна створити як об’єкт бази даних. Його можна створити лише всередині підпрограми, яка може використовуватися лише в цій підпрограмі.
- BULK COLLECT не можна використовувати в цьому типі колекції, оскільки нижній індекс слід вказувати явно для кожного запису в колекції.
На малюнку нижче схематично показано розподіл пам’яті вкладеної таблиці (розрідженої). Чорний колір позначає порожній елемент у колекції, тобто розріджений.
| Нижній індекс (varchar) | ПЕРШИЙ | ДРУГИЙ | ТРЕТИЙ | ЧЕТВЕРТИЙ | ПЯТИЙ | ШОСТЕ | СІМ |
| Значення (розріджене) | Qwe | Asd | Afg | Asd | Wer |
Синтаксис для індексу за таблицею
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- У наведеному вище синтаксисі type_name оголошено як колекцію індексів за таблицею типу "DATA_TYPE". Тип даних може бути простим або складним. Змінна subsciprt/index надається як тип VARCHAR2 з максимальним розміром 10.
Концепція конструктора та ініціалізації в колекціях
Конструктори — це вбудовані функції, надані оракулом, які мають те саме ім’я, що й об’єкт або колекції. Вони виконуються першими щоразу, коли об’єкт або колекція посилаються вперше в сеансі. Нижче наведено важливі деталі конструктора в контексті колекції:
- Для колекцій ці конструктори слід викликати явно для її ініціалізації.
- І таблиці Varray, і таблиці Nested потрібно ініціалізувати за допомогою цих конструкторів, перш ніж потрапити в програму.
- Конструктор неявно розширює виділення пам’яті для колекції (окрім Varray), отже, конструктор також може призначати змінні колекціям.
- Присвоєння значень колекції за допомогою конструкторів ніколи не зробить колекцію розрідженою.
Методи збору
Oracle надає багато функцій для маніпулювання та роботи з колекціями. Ці функції дуже корисні в програмі для визначення та зміни різних атрибутів колекцій. У наступній таблиці наведено різні функції та їх опис.
| Метод | Опис | СИНТАКС |
|---|---|---|
| ІСНУЄ (n) | Цей метод повертає логічні результати. Він поверне «ІСТИНА», якщо nth елемент існує в цій колекції, інакше він поверне FALSE. У неініціалізованій колекції можна використовувати лише функції EXISTS | .EXISTS(позиція_елемента) |
| COUNT | Дає загальну кількість елементів у колекції | .РАХУВАТИ |
| МЕЖА | Він повертає максимальний розмір колекції. Для Varray він поверне фіксований розмір, який було визначено. Для вкладеної таблиці та індексу за таблицею він дає NULL | .ОБМЕЖЕННЯ |
| ПЕРШИЙ | Повертає значення першої індексної змінної (індекс) колекцій | .ПЕРШИЙ |
| LAST | Повертає значення останньої змінної індексу (індекс) колекцій | .ОСТАННІЙ |
| ПОПЕРЕДНІЙ (n) | Повертає перед індексною змінною в колекції nth елемент. Якщо попереднього значення індексу немає, повертається NULL | .PRIOR(n) |
| ДАЛІ (n) | Повертає успішну індексну змінну в колекції nth елемент. Якщо немає успішного індексу, повертається значення NULL | .НАСТУПНИЙ(n) |
| РОЗШИРИТИ | Розширює один елемент колекції в кінці | .ПРОДОВЖИТИ |
| ПРОДОВЖИТИ (n) | Розширює n елементів у кінці колекції | .EXTEND(n) |
| ПОДОВЖИТИ (n,i) | Розширює n копій ith елемент у кінці колекції | .EXTEND(n,i) |
| TRIM | Видаляє один елемент із кінця колекції | .ОБРІЗАТИ |
| ТРИМ (n) | Видаляє n елементів із кінця колекції | .TRIM (n) |
| DELETE | Видаляє всі елементи з колекції. Робить колекцію порожньою | .ВИДАЛИТИ |
| ВИДАЛИТИ (n) | Видаляє n-й елемент із колекції. Якщо тодіth елемент дорівнює NULL, тоді це нічого не дасть | .DELETE(n) |
| ВИДАЛИТИ (m,n) | Видаляє елемент у діапазоні mth до нth у колекції | .DELETE(m,n) |
Приклад 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;
/
Пояснення коду:
- Рядок коду 2-8: Тип запису 'emp_det' оголошено зі стовпцями emp_no, emp_name, salary і менеджер типу даних NUMBER, VARCHAR2, NUMBER, NUMBER.
- Рядок коду 9: Створення колекції 'emp_det_tbl' елемента типу запису 'emp_det'
- Рядок коду 10: Оголошення змінної 'guru99_emp_rec' як типу 'emp_det_tbl' і ініціалізація нульовим конструктором.
- Рядок коду 12-15: Вставлення зразків даних у таблицю «emp».
- Рядок коду 16: Здійснення транзакції вставки.
- Рядок коду 17: Отримання записів із таблиці «emp» і масове заповнення змінної колекції за допомогою команди «BULK COLLECT». Тепер змінна 'guru99_emp_rec' містить усі записи, присутні в таблиці 'emp'.
- Рядок коду 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 ----------------------------------------------
