Oracle PL/SQL колекции: Varrays, Nested & Index by Tables
Фиона Браун
Какво е колекция?
Колекцията е подредена група от елементи от определени типове данни. Това може да бъде колекция от прост тип данни или сложен тип данни (като дефинирани от потребителя или типове записи).
В колекцията всеки елемент се идентифицира с термин, наречен „долен индекс“. Всеки елемент в колекцията е присвоен с уникален долен индекс. Данните в тази колекция могат да бъдат манипулирани или извлечени чрез позоваване на този уникален индекс.
Колекциите са най-полезни неща, когато трябва да се обработват или манипулират големи данни от същия тип. Колекциите могат да се попълват и манипулират като цяло, като се използва опцията „BULK“. Oracle.
Колекциите се класифицират въз основа на структурата, индекса и съхранението, както е показано по-долу.
- Индекс по таблици (известен също като асоциативен масив)
- Вложени таблици
- Varrays
Във всеки момент данните в колекцията могат да бъдат посочени с три термина име на колекция, индекс, име на поле/колона като „ ( ). ”. Ще научите повече за тези горепосочени категории колекции в раздела по-долу.
Varrays
Varray е метод за събиране, при който размерът на масива е фиксиран. Размерът на масива не може да бъде надвишен от неговата фиксирана стойност. Долният индекс на Varray е с числова стойност. Следват атрибутите на Varrays.
- Размерът на горната граница е фиксиран
- Попълва се последователно, започвайки с долен индекс „1“
- Този тип колекция винаги е плътна, т.е. не можем да изтрием елементи от масива. Varray може да бъде изтрит като цяло или може да бъде изрязан от края.
- Тъй като винаги е плътен по природа, той има много по-малко гъвкавост.
- По-подходящо е да се използва, когато размерът на масива е известен и да се извършват подобни дейности върху всички елементи на масива.
- Индексът и последователността винаги остават стабилни, т.е. индексът и броят на колекцията са винаги еднакви.
- Те трябва да бъдат инициализирани преди да се използват в програми. Всяка операция (с изключение на операция EXISTS) върху неинициализирана колекция ще доведе до грешка.
- Може да бъде създаден като обект на база данни, който е видим в цялата база данни или вътре в подпрограмата, която може да се използва само в тази подпрограма.
Фигурата по-долу ще обясни схематично разпределението на паметта на Varray (dense).
| Долен | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Стойност | Xyz | Dfv | Sde | Cxs | Vbc | Нху | Qwe |
Синтаксис за VARRAY:
TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;
- В горния синтаксис име_на_тип се декларира като VARRAY от типа 'DATA_TYPE' за даденото ограничение на размера. Типът данни може да бъде прост или сложен тип.
Вложени таблици
Вложената таблица е колекция, в която размерът на масива не е фиксиран. Има числовия долен индекс. По-долу има още описания за типа вложена таблица.
- Вложената таблица няма горно ограничение за размер.
- Тъй като горната граница на размера не е фиксирана, колекцията, паметта трябва да се разширява всеки път, преди да я използваме. Можем да разширим колекцията с помощта на ключовата дума „EXTEND“.
- Попълва се последователно, започвайки с долен индекс „1“.
- Този тип колекция може да бъде и от двете плътен и рядък, т.е. можем да създадем колекцията като плътна, а също така можем да изтрием произволно отделния елемент от масива, което да я направи разредена.
- Това дава повече гъвкавост по отношение на изтриването на елемента от масива.
- Той се съхранява в генерираната от системата таблица на базата данни и може да се използва в заявката за избор за извличане на стойностите.
- Индексът и последователността не са стабилни, т.е. индексът и броят на елемента на масива могат да варират.
- Те трябва да бъдат инициализирани преди да се използват в програми. Всяка операция (с изключение на операция EXISTS) върху неинициализираната колекция ще изведе грешка.
- Може да бъде създаден като обект на база данни, който е видим в цялата база данни или вътре в подпрограмата, която може да се използва само в тази подпрограма.
Фигурата по-долу ще обясни схематично разпределението на паметта на вложена таблица (плътна и разредена). Оцветеното в черно пространство на елемента обозначава празния елемент в колекция, т.е. разреден.
| Долен | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Стойност (плътна) | Xyz | Dfv | Sde | Cxs | Vbc | Нху | Qwe |
| Стойност (разредена) | Qwe | Asd | афг | Asd | Wer |
Синтаксис за вложена таблица:
TYPE <tvpe name> IS TABLE OF <DATA TYPE>;
- В горния синтаксис име_на_тип се декларира като колекция от вложени таблици от типа 'DATA_TYPE'. Типът данни може да бъде прост или сложен тип.
Индекс по таблица
Index-by-table е колекция, в която размерът на масива не е фиксиран. За разлика от другите типове колекции, в колекцията индекс по таблица индексът може да се дефинира от потребителя. Следват атрибутите на индекс по таблица.
- Долният индекс може да бъде цяло число или низове. В момента на създаване на колекцията трябва да се упомене типът индекс.
- Тези колекции не се съхраняват последователно.
- Те винаги са редки по природа.
- Размерът на масива не е фиксиран.
- Те не могат да се съхраняват в колоната на базата данни. Те трябва да бъдат създадени и използвани във всяка програма в тази конкретна сесия.
- Те дават повече гъвкавост по отношение на поддържането на долен индекс.
- Долните индекси могат да бъдат и с отрицателна долна последователност.
- Те са по-подходящи за използване за относително по-малки колективни стойности, в които колекцията може да бъде инициализирана и използвана в рамките на същите подпрограми.
- Не е необходимо да се инициализират, преди да започнете да ги използвате.
- Не може да бъде създаден като обект на база данни. Може да се създаде само вътре в подпрограмата, която може да се използва само в тази подпрограма.
- BULK COLLECT не може да се използва в този тип колекция, тъй като индексът трябва да бъде даден изрично за всеки запис в колекцията.
Фигурата по-долу ще обясни схематично разпределението на паметта на вложена таблица (разредена). Оцветеното в черно пространство на елемента обозначава празния елемент в колекция, т.е. разреден.
| Долен индекс (varchar) | ПЪРВИ | ВТОРИ | ТРЕТИ | ЧЕТВЪРТО | ПЕТО | ШЕСТИ | СЕДМИ |
| Стойност (разредена) | Qwe | Asd | афг | Asd | Wer |
Синтаксис за индекс по таблица
TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);
- В горния синтаксис име_на_тип се декларира като колекция индекс по таблица от типа 'DATA_TYPE'. Типът данни може да бъде прост или сложен тип. Променливата subsciprt/index се дава като тип VARCHAR2 с максимален размер 10.
Концепция за конструктор и инициализация в колекции
Конструкторите са вградената функция, предоставена от оракула, която има същото име като на обекта или колекциите. Те се изпълняват първи, когато обект или колекции се препращат за първи път в сесия. По-долу са важните подробности за конструктора в контекста на колекцията:
- За колекции тези конструктори трябва да бъдат извикани изрично, за да я инициализират.
- Таблиците Varray и Nested трябва да бъдат инициализирани чрез тези конструктори, преди да бъдат препратени към програмата.
- Конструкторът имплицитно разширява разпределението на паметта за колекция (с изключение на Varray), следователно конструкторът може също да присвои променливите на колекциите.
- Присвояването на стойности на колекцията чрез конструктори никога няма да направи колекцията разредена.
Методи за събиране
Oracle предоставя много функции за манипулиране и работа с колекциите. Тези функции са много полезни в програмата за определяне и модифициране на различни атрибути на колекциите. Следващата таблица ще даде различните функции и тяхното описание.
| Начин на доставка | Descriptйон | СИНТАКСИС |
|---|---|---|
| СЪЩЕСТВУВА (n) | Този метод ще върне булеви резултати. Ще върне „ВЯРНО“, ако nth елемент съществува в тази колекция, в противен случай ще върне FALSE. Само EXISTS функции могат да се използват в неинициализирана колекция | .EXISTS(позиция_елемент) |
| COUNT | Дава общия брой елементи, присъстващи в колекция | .БРОЙ |
| ОГРАНИЧАВА | Връща максималния размер на колекцията. За Varray ще върне фиксирания размер, който е дефиниран. За вложена таблица и индекс по таблица дава NULL | .ГРАНИЦА |
| ПЪРВИ | Връща стойността на първата индексна променлива (долен индекс) на колекциите | .ПЪРВО |
| LAST | Връща стойността на последната индексна променлива (долен индекс) на колекциите | .ПОСЛЕДНО |
| ПРЕДИШЕН (n) | Връща предшестваща индексна променлива в колекция от nth елемент. Ако няма стойност на предхождащ индекс, връща се NULL | .ПРИОР(n) |
| СЛЕДВАЩ (n) | Връща успешна индексна променлива в колекция от nth елемент. Ако няма успешен индекс, се връща NULL | .СЛЕДВАЩ(n) |
| РАЗШИРИ | Разширява един елемент в колекция в края | .РАЗДЪЛЖАВАНЕ |
| РАЗШИРЯВАНЕ (n) | Разширява n елемента в края на колекция | .EXTEND(n) |
| РАЗШИРЯВАНЕ (n,i) | Разширява n копия на ith елемент в края на колекцията | .EXTEND(n,i) |
| TRIM | Премахва един елемент от края на колекцията | .ТРИМ |
| ПОДДРЪЖКА (n) | Премахва n елемента от края на колекцията | .TRIM (n) |
| ИЗТРИЙ | Изтрива всички елементи от колекцията. Прави колекцията празна | .ИЗТРИВАНЕ |
| ИЗТРИВАНЕ (n) | Изтрива n-тия елемент от колекцията. Ако nth елемент е NULL, тогава това няма да направи нищо | .DELETE(n) |
| ИЗТРИВАНЕ (m,n) | Изтрива елемента в диапазона mth към nth в колекцията | .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: Задаване на цикъла „ЗА“ за отпечатване на всички записи в колекцията един по един. Методът на събиране 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 ----------------------------------------------
