Я заканчиваю серию из трех выпусков об объектно-ориентированном программировании в Oracle PL/SQL.

Объявления SQL типов, эквивалентных коллекциям типов PL/SQL

Для хранения вложенных таблиц и массивов внутри таблицы базы данных, Вы дополнительно должны определить SQL типы, используя выражение CREATE TYPE. Типы SQL могут быть использованы как колонки или как атрибуты объектного типа SQL. Подробно выражение CREATE TYPE описано в "Oracle Database SQL Reference", о выражении CREATE TYPE BODY смотрите в " Oracle Database SQL Reference", сведенья об объектных типах смотрите в "Oracle Database Application Developer's Guide - Object-Relational Features".

Вы можете объявить эквивалентные типы внутри PL/SQL или использовать типы SQL, объявляя переменные PL/SQL.

Пример 12-10 иллюстрирует как можно объявить вложенную таблицу в SQL и использовать ее как атрибут объектного типа.

Пример 12-10 Объявление вложенной таблицы в SQL
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- объявление типа
/
CREATE TYPE student AS OBJECT 
(  -- создание типа
  id_num  INTEGER(4),
  name     VARCHAR2(25),
  address  VARCHAR2(35),
  status     CHAR(2),
  courses  CourseList
);  -- объявление вложенной таблицы как атрибута
/
CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;

Идентификатор "courses" представляет всю вложенную таблицу. Каждый элемент courses хранит название курса обучения, например 'Math 1020'.

Пример 12-11 создает колонку, которая хранит массивы типа varchar2. Каждый элемент массива xранит значение типа VARCHAR2.

Пример 12-11 Создание таблицы с колонкой типа "массив строк".
-- Каждый проект имеет 16-символьный текстовый код.
-- Мы собираемся хранить до 50 проектов в одной колонке базы данных.
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
/
CREATE TABLE dept_projects (  -- создание таблицы
   dept_id  NUMBER(2),
   name     VARCHAR2(15),
   budget   NUMBER(11,2),
-- Каждый департамент может хранить до 50 проектов
   projects ProjectList);

В примере 12-12 в таблицу dept_projects вставляется ряд. Конструктор массива строк ProjectList() передает инициализационные значения.

Пример 12-12 Использование конструктора массив строк в выражении SQL.
BEGIN
  INSERT INTO dept_projects
    VALUES(60, 'Security', 750400,  ProjectList('New Badges', 'Track Computers', 'Check Exits'));
END;
/

Пример 12-13 Конструктор вложенной таблицы в SQL выражении 
CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;
BEGIN
   INSERT INTO sophomores
        VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT', CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
END;
/

Манипулирование отдельными элементами коллекции в 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;
/

Использование коллекций 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;
/

Использование динамического SQL с объектами

Пример 12-18 иллюстрирует использование объектов и коллекций с динамическим SQL. Во первых, определяется объектный тип person_typ и массив строк (VARRAY) hobbies_var, а потом - пакедж, в котором будут использоваться эти типы.

Пример 12-18  Пакедж TEAMS, в котором динамический SQL применяется для манипуляции с объектами и коллекциями.
CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
/
CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
/
CREATE OR REPLACE PACKAGE teams 
   AUTHID CURRENT_USER AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
                        ' (pers person_typ, hobbs hobbies_var)';
   END;
   PROCEDURE insert_row (
                         tab_name VARCHAR2,
                         p        person_typ,
                         h        hobbies_var
                        ) IS 
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;
   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE  refcurtyp IS REF CURSOR;
      v_cur refcurtyp;
      p     person_typ;
      h     hobbies_var;
   BEGIN
      OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH v_cur INTO p, h;
         EXIT WHEN v_cur%NOTFOUND;
         -- печать атрибутов 'p' и элементов of 'h'
         DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
         FOR i IN h.FIRST..h.LAST 
         LOOP
           DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
         END LOOP;
      END LOOP;
      CLOSE v_cur;
   END;
END;
/

Пример 12-19 Вызов процедур пакеджа TEAMS
DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   TEAMS.create_table(team_name);
   TEAMS.insert_row(team_name, person_typ('John', 31),
      hobbies_var('skiing', 'coin collecting', 'tennis'));
   TEAMS.insert_row(team_name, person_typ('Mary', 28),
      hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
   TEAMS.print_table(team_name);
END;
/