Манипулирование отдельными элементами коллекции в SQL
По умолчанию, SQL операторы хранят и работают с всей коллекцией целиком, но не с отдельными элементами. Для обращения к отдельному элементу коллекции в SQL применяется оператор TABLE. Оператор Table использует подзапросы для извлечения элемента массива или вложенной таблицы, таким образом операторы INSERT, UPDATE или DELETE применяются к отдельным элементам, а не к таблице верхнего уровня.
Чтобы в PL/SQL выполнить операции DML для обработки вложенной таблицы, используйте операторы TABLE и CAST. Таким образом, вы можете выполнить набор операций над вложенной таблицей используя нотацию SQL, без фактического сохранения этой таблицы в базе данных.
Операнд CAST - это фактически коллекция PL/SQL или тип коллекции SQL (созданной выражением CREATE TYPE). CAST преобразует коллекцию PL/SQL в SQL тип. Операторы TABLE и CAST "разворачивают" коллекции в таблицы, чтобы можно было выполнить стандартные манипуляции, используя операторы SQL.
Пример 12-14 выполнение операций над вложенной таблицей в PL/SQL, используя оператор CAST
CREATE TYPE Course AS OBJECT
(
course_no NUMBER,
title VARCHAR2(64),
credits NUMBER
);
/
CREATE TYPE CourseList AS TABLE OF course;
/
-- создание таблицы department
CREATE TABLE department
(
name VARCHAR2(20),
director VARCHAR2(20),
office VARCHAR2(20),
courses CourseList
)
NESTED TABLE courses STORE AS courses_tab;
INSERT INTO department
VALUES ('English', 'June Johnson', '491C',
CourseList(Course(1002, 'Expository Writing', 4),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 4))
);
DECLARE
revised CourseList :=
CourseList(Course(1002, 'Expository Writing', 3),
Course(2020, 'Film and Literature', 4),
Course(4210, '20th-Century Poetry', 4),
Course(4725, 'Advanced Workshop in Poetry', 5));
num_changed INTEGER;
BEGIN
SELECT COUNT(*)
INTO num_changed
FROM
TABLE(CAST(revised AS CourseList)) new,
TABLE(
SELECT courses
FROM department
WHERE name = 'English'
) old
WHERE new.course_no = old.course_no
AND (new.title != old.title OR new.credits != old.credits);
DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/