Использование коллекций PL/SQL с объектными типами SQL
Коллекции позволяют в PL/SQL манипулировать составными типами данных. Ваша программа может выполнять операции в памяти с индексированными элементами и используя SQL, сохранить результат в таблицах базы данных.
В SQL*Plus Вы можете создать объектные типы, чьи объявления соответствуют вложенным таблицам или массивам строк PL/SQL, как показано в примере 12-15. Каждый элемент колонки dept_names - это вложенная таблица, которая хранит имена отделений в соответствующем регионе. Операнд NESTED TABLE необходим, если таблица базы данных должна содержать вложенную таблицу. NESTED TABLE определяет вложенную таблицу и имена для генерируемых системой таблиц, в которых Oracle хранит данные вложенной таблицы.
В PL/SQL Вы можете работать с вложенной таблицей, организуя цикл по ее элементам, используя такие методы как TRIM или EXTEND, изменяя все или некоторые элементы. После чего, измененная таблица может быть снова сохранена в базе данных. Вы можете вставлять ряды, содержащие вложенные таблицы, изменять или заменять вложенные таблицы, вычитывать вложенные таблицы в переменные PL/SQL. Однако непосредственно через SQL невозможно изменение или удаление отдельных элементов вложенных таблиц. Для этого Вам необходимо зачитать вложенную таблицу, изменить ее в PL/SQL и после этого сохранить.
Пример 12-15 Применение к вложенным таблицам выражений INSERT, UPDATE, DELETE, и SELECT
CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab)
NESTED TABLE dept_names STORE AS dnames_nt;
BEGIN
INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
COMMIT;
END;
/
DECLARE
-- Объявление типа не требуется, т.к. PL/SQL может обращаться к объектному типу SQL
-- TYPE dnames_tab IS TABLE OF VARCHAR2(30); не требуется
-- Объявление переменной, которая может хранить набор наименований отделений
v_dnames dnames_tab;
-- Объявление переменной для хранения ряда таблицы
-- Одно поле записи - это набор наименований отделений
v_depts depts%ROWTYPE;
new_dnames dnames_tab;
BEGIN
-- Выборка региона и наименований отделений, которые ассоциированы с ним
SELECT dept_names
INTO v_dnames
FROM depts
WHERE region = 'Europe';
FOR i IN v_dnames.FIRST .. v_dnames.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
END LOOP;
-- Поиск региона и выборка всего ряда
SELECT *
INTO v_depts
FROM depts
WHERE region = 'Asia';
-- А сейчас dept_names это поле записи, мы обращаемся к нему через "точечную" нотацию
FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST
LOOP
-- Т.к. мы имеем все колонки таблицы, мы можем обратиться к региону
DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' || v_depts.dept_names(i));
END LOOP;
-- Используя выражение UPDATE, мы можем заменить набор имен отделений новой коллекцией
new_dnames := dnames_tab('Sales','Payroll','Shipping');
UPDATE depts
SET dept_names = new_dnames
WHERE region = 'Europe';
-- Или изменим первоначальную коллекцию, мы добавляем еще один элемент и передаем ему значение
v_depts.dept_names.EXTEND(1);
v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
UPDATE depts
SET dept_names = v_depts.dept_names
WHERE region = v_depts.region;
/*
Мы можем обращаться с колонкой, содержащей вложенную таблицу, как будто это колонка обычной таблицы,
мы можем вставлять, изменять или удалять элементы.
Оператор TABLE позволяет применить к таким таблицам обычные выражения,
преобразуя из в подзапросы
*/
INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
VALUES('Sales');
DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia')
WHERE column_value = 'Payroll';
UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
SET column_value = 'Payroll' WHERE column_value = 'Finance';
COMMIT;
END;
/
Пример 12-16 показывает как Вы можете манипулировать с объектным типом SQL varray (массив строк), используя выражения PL/SQL. В этом примере, varrays передается между переменными PL/SQL и таблицами SQL. Вы можете встать в таблицу ряд, содержащий varrays, изменить ряд или заменить его varray, после чего зачитать массив в переменные PL/SQL. Непосредственно из SQL вы мене можете изменять или удалять отдельные элементы массива, для этого надо зачитать массив, изменить его требуемым образом и сохранить в базе данных.
Пример 12-16 Использование выражений INSERT, UPDATE, DELETE, и SELECT для работы с массивами.
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/
DECLARE
new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 'Executive', 'Marketing');
some_dnames dnames_var;
BEGIN
UPDATE depts
SET dept_names = new_dnames
WHERE region = 'Europe';
COMMIT;
SELECT dept_names
INTO some_dnames
FROM depts
WHERE region = 'Europe';
FOR i IN some_dnames.FIRST .. some_dnames.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
END LOOP;
END;
/
В примере 12-17, PL/SQL BULK COLLECT используется с многоуровневой коллекцией, которая содержит объектный тип.
Пример 12-17 Использование BULK COLLECT с вложенной таблицей
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
INSERT INTO depts VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
COMMIT;
END;
/
DECLARE
TYPE dnames_tab IS TABLE OF dnames_var;
v_depts dnames_tab;
BEGIN
SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/