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

Зміст:

Anonim

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

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

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

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

У цьому підручнику ви дізнаєтесь-

  • Що таке колекція?
  • Варіанти
  • Вкладені таблиці
  • Індекс за таблицею
  • Конструктор та концепція ініціалізації у колекціях
  • Методи збору

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

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

У будь-який момент дані в колекції можуть називатися трьома термінами Назва колекції, Індекс, Ім'я поля / Стовпця як "<ім'я колекції> (<підпис>). <Ім'я стовпця>". Ви дізнаєтесь про ці вищезазначені категорії колекцій далі в розділі нижче.

Варіанти

Varray - це метод збору, при якому розмір масиву є фіксованим. Розмір масиву не може перевищувати фіксованого значення. Індекс Varray має числове значення. Нижче наведено атрибути Varrays.

  • Розмір верхньої межі встановлений
  • Заповнюється послідовно, починаючи з нижнього індексу '1'
  • Цей тип колекції завжди щільний, тобто ми не можемо видалити будь-які елементи масиву. Масив можна видалити в цілому, або його можна обрізати з кінця.
  • Оскільки за своєю природою вона завжди щільна, вона має набагато меншу гнучкість.
  • Доцільніше використовувати, коли розмір масиву відомий, і виконувати подібні дії з усіма елементами масиву.
  • Індекс та послідовність завжди залишаються стабільними, тобто індекс та кількість колекції завжди однакові.
  • Їх потрібно ініціалізувати перед використанням у програмах. Будь-яка операція (крім операції ІСНУЄ) над неініціалізованою колекцією призведе до помилки.
  • Він може бути створений як об'єкт бази даних, який видно у всій базі даних або всередині підпрограми, яка може використовуватися лише в цій підпрограмі.

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

Підрядковий знак 1 2 3 4 5 6 7
Значення Xyz Dfv Сде Cxs Vbc Нху Qwe

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

TYPE  IS VARRAY () OF ;
  • У наведеному вище синтаксисі type_name оголошено як VARRAY типу "DATA_TYPE" для заданого обмеження розміру. Тип даних може бути простим або складним типом.

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

Вкладена таблиця - це колекція, в якій розмір масиву не визначений. Він має числовий підрядковий тип. Нижче наведено більше описів про тип вкладеної таблиці.

  • Вкладена таблиця не має верхнього обмеження розміру.
  • Оскільки верхня межа розміру не фіксована, колекцію, пам'ять потрібно подовжувати щоразу перед тим, як ми її використовуємо. Ми можемо розширити колекцію за допомогою ключового слова "EXTEND".
  • Заповнюється послідовно, починаючи з нижнього індексу '1'.
  • Цей тип колекції може бути як щільним, так і розрідженим , тобто ми можемо створити колекцію як щільну, а також можна випадковим чином видалити окремий елемент масиву, що робить його рідким.
  • Це надає більшу гнучкість щодо видалення елемента масиву.
  • Він зберігається у створеній системою таблиці бази даних і може бути використаний у запиті вибору для отримання значень.
  • Індекс та послідовність не стабільні, тобто індекс та кількість елементів масиву можуть змінюватися.
  • Їх потрібно ініціалізувати перед використанням у програмах. Будь-яка операція (крім операції ІСНУЄ) над неініціалізованою колекцією призведе до помилки.
  • Він може бути створений як об'єкт бази даних, який видно у всій базі даних або всередині підпрограми, яка може використовуватися лише в цій підпрограмі.

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

Підрядковий знак 1 2 3 4 5 6 7
Значення (щільне) Xyz Dfv Сде Cxs Vbc Нху Qwe
Значення (розріджене) Qwe Asd Афг Asd Вер

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

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

Індекс за таблицею

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

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

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

Індекс (varchar) СПОЧАТКУ ДРУГИЙ ТРЕТІЙ ЧЕТВЕРТА П’ЯТА ШОСТИЙ СЬОМА
Значення (розріджене) Qwe Asd Афг Asd Вер

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

TYPE  IS TABLE OF  INDEX BY VARCHAR2 (10);
  • У наведеному вище синтаксисі type_name оголошено як колекцію по індексу за таблицею типу 'DATA_TYPE'. Тип даних може бути простим або складним типом. Змінна subsciprt / index вказана як тип VARCHAR2 з максимальним розміром як 10.

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

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

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

Методи збору

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

Метод Опис СИНТАКС
ІСНУЄ (n) Цей метод поверне логічні результати. Він поверне "TRUE", якщо n- й елемент існує у цій колекції, інакше поверне FALSE. Тільки функції EXISTS можна використовувати в неініціалізованій колекції .EXISTS (елемент_позиція)
РАХУВАТИ Дає загальну кількість елементів, присутніх у колекції <ім'я_збору> .COUNT
ОБМЕЖЕННЯ Він повертає максимальний розмір колекції. Для Varray він поверне фіксований розмір, який був визначений. Для вкладеної таблиці та індексу за таблицею вона дає значення NULL <ім'я_збору> .LIMIT
СПОЧАТКУ Повертає значення першої змінної індексу (індексу) колекцій <ім'я_збору>. ПЕРШИЙ
ОСТАННІЙ Повертає значення останньої змінної індексу (індексу) колекцій <ім'я_збору> .LAST
PRIOR (n) Повертає попередню змінну індексу в колекції n- го елемента. Якщо немає попереднього значення індексу, повертається NULL <ім'я_збору> .PRIOR (n)
ДАЛІ (n) Повертає змінну індексу в колекції n- го елемента. Якщо значення успіху не вдалося, повертається значення NULL <ім'я_збору> .ДАЛІ (n)
РОЗШИРИТИ Розширює один елемент у колекції в кінці <ім'я_збору> .EXTEND
РОЗШИРИТИ (n) Розширює n елементів у кінці колекції <ім'я_збору> .EXTEND (n)
EXTEND (n, i) Розширює n копій i- го елемента в кінці колекції <ім'я_збору> .EXTEND (n, i)
ОБРІЗКА Видаляє один елемент із кінця колекції <ім'я_збору> .TRIM
TRIM (n) Видаляє n елементів з кінця колекції <ім'я_збору> .TRIM (n)
ВИДАЛИТИ Видаляє всі елементи з колекції. Робить колекцію порожньою <ім'я_збору> .DELETE
ВИДАЛИТИ (n) Видаляє n-й елемент із колекції. Якщо n- й елемент має значення NULL, це нічого не дасть <ім'я колекції> .DELETE (n)
ВИДАЛИТИ (м, п) Видаляє елемент у діапазоні від m- го до n- го в колекції <ім'я_збору> .DELETE (m, n)

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

У цьому прикладі ми побачимо, як заповнити колекцію за допомогою 'BULK COLLECT' та як передати дані колекції.

DECLARETYPE 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();BEGININSERT 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_recFROM emp;dbms_output.put_line (‘Employee Detail');FOR i IN guru99_emp_rec.FIRST… guru99_emp_rec.LASTLOOPdbms_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, заробітна плата та менеджер типу даних НОМЕР, VARCHAR2, НОМЕР, НОМЕР.
  • Рядок коду 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' за допомогою друку всіх записів у колекції по одному. Метод збору ПЕРШИЙ та ОСТАННИЙ використовується як нижча та верхня межа циклу.

Результат : Як ви можете бачити на наведеному вище скріншоті, коли буде виконано наведений вище код, ви отримаєте наступний результат

Employee DetailEmployee Number: 1000Employee Name: AAAEmployee Salary: 25000Employee Manager Number: 1000----------------------------------------------Employee Number: 1001Employee Name: XXXEmployee Salary: 10000Employee Manager Number: 1000----------------------------------------------Employee Number: 1002Employee Name: YYYEmployee Salary: 15000Employee Manager Number: 1000----------------------------------------------Employee Number: 1003Employee Name: ZZZEmployee Salary: 7500Employee Manager Number: 1000----------------------------------------------