Использование объектных типов в PL/SQL

Статьи про Oracle -> Программирование

Использование объектных типов в PL/SQL

Статья написана на основе официальной документации фирмы Oracle.
Oracle® Database PL/SQL User's Guide and Reference
10g Release 2 (10.2)
Part Number B14261-01

v:1.0 2008.02.09
Петрелевич Сергей

Объектно-ориентированное программирование особенно подходит для создания повторно используемых компонентов и комплексных приложений. В PL/SQL объектно-ориентированное программирование основано на объектных типах. Объектные типы позволяют моделировать объекты реального мира, разделяя интерфейсы и детали внутренней реализации, позволяют постоянно хранить объектно-ориентированные данные в базе данных.

Объявление и инициализация объектов в PL/SQL

Объектный тип может моделировать любую сущность реального мира. Например, объектный тип может имитировать сущности студент, банковский счет, экран компьютера, натуральное число или структуры данных такие как очередь, стек или лист.

В настоящий момент Вы не можете описать типы в PL/SQL блоке, подпрограмме или пакедже. Типы можно описать интерактивно в SQL*Plus, используя SQL выражение CREATE TYPE.

Информацию о выражениях CREATE TYPE и CREATE TYPE BODY можно найти в "Oracle Database SQL Reference".

После того как объектный тип описан и установлен в схеме, Вы можете использовать его объявление в любом PL/SQL блоке, подпрограмме или пакедже. Например, Вы можете использовать объектный тип чтобы определить тип данных атрибута, колонки, переменной, связанной переменной, записи, элемента таблицы, формального параметра или результата функции. Во время выполнения создается экземпляр объекта. Каждый объект может хранить различные значения. Объекты подчиняются обычным правилам области видимости и инициализации. В блока или подпрограммах локальные объекты инициализируются, когда вы запускаете блок или подпрограмму и уничтожается, когда программа заканчивает свою работу. В пакеджах объекты инициализируются когда Вы первый раз ссылаетесь на эти пакеджи и уничтожаются при закрытии сессии базы данных.

В следующем примере демонстрируется создание объектного типа, тела объекта и таблицы объектных типов

Пример 12-1 Работа с объектными типами.

CREATE TYPE address_typ AS OBJECT
(
 street     VARCHAR2(30),
 city      VARCHAR2(20),
 state      CHAR(2),
 postal_code   VARCHAR2(6)
);
/
CREATE TYPE employee_typ AS OBJECT
(
 employee_id    NUMBER(6),
 first_name    VARCHAR2(20),
 last_name     VARCHAR2(25),
 email       VARCHAR2(25),
 phone_number   VARCHAR2(20),
 hire_date     DATE,
 job_id      VARCHAR2(10),
 salary      NUMBER(8,2),
 commission_pct  NUMBER(2,2),
 manager_id    NUMBER(6),
 department_id   NUMBER(4),
 address      address_typ,
 MAP MEMBER FUNCTION get_idno RETURN NUMBER,
 MEMBER PROCEDURE display_address (SELF IN OUT NOCOPY employee_typ)
);
/
CREATE TYPE BODY employee_typ AS
 MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
 BEGIN
  RETURN employee_id;
 END;
 MEMBER PROCEDURE display_address (SELF IN OUT NOCOPY employee_typ) IS
 BEGIN
  DBMS_OUTPUT.PUT_LINE(first_name || ' '|| last_name);
  DBMS_OUTPUT.PUT_LINE(address.street);
  DBMS_OUTPUT.PUT_LINE(address.city || ', '|| address.state || ' '||address.postal_code);
 END;
END;
/
CREATE TABLE employee_tab OF employee_typ;

Объявление объектов в PL/SQL блоке

Вы можете использовать объектные типы точно так же как и встроенные типы такие как CHAR или NUMBER. В примере 12-2, объявляется объект emp типа employee_typ. Для инициализации объекта вызывается конструктор типа employee_typ.

Пример 12-2 Объявление объектного типа в PL/SQL блоке

DECLARE
 emp employee_typ; -- emp по умолчанию NULL
BEGIN
-- вызов конструктора типа employee_typ
 emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
    '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
     address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
 DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details
 emp.display_address(); -- вызывается метод объекта для вывода данных объекта
END;
/



Вы можете объявлять объекты как формальные параметры функций и процедур. Таким образом, Вы можете передавать объект из одной подпрограммы в другую. В следующем примере объектный тип employee_typ является типом данных формального параметра.
PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...


В следующем примере объектный тип employee_typ является типом данных возвращаемого функцией значения:
FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...

Как обращаться с неинициализированными объектами в PL/SQL

Пока вы ни инициализируете объект, вызвав конструктор соответствующего типа, объект будет null. Объект сам равен Null, но не его атрибуты.

Неинициализированный объект никогда не равен другому объекту. Фактически, сравнение NULL с любым другим объектом всегда вернет NULL. Если вы присваиваете объект NULL другому объекту, то тот объект становится неинициализированным. Выражения и атрибуты неинициализированного объекта равны NULL. Если к неинициализированному объекту или его атрибутам применить выражение сравнения IS NULL, то результат будет TRUE.

Пример 12-3, иллюстрация объектов null и объектов с аргументами null.

DECLARE
 emp employee_typ; -- emp по умолчанию null
BEGIN
 IF emp IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('emp is NULL #1');
 END IF;
 IF emp.employee_id IS NULL THEN
   DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #1');
 END IF;
 emp.employee_id := 330;
 IF emp IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('emp is NULL #2');
 END IF;
 IF emp.employee_id IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #2');
 END IF;
 emp := employee_typ(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                                                   address_typ(NULL, NULL, NULL, NULL));
 -- emp := NULL; -- это бы сделало следующее выражение равным TRUE
 IF emp IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('emp is NULL #3');
 END IF;
 IF emp.employee_id IS NULL THEN
  DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #3');
 END IF;
EXCEPTION
  WHEN ACCESS_INTO_NULL THEN
   DBMS_OUTPUT.PUT_LINE('Cannot assign value to NULL object');
END;
/


Вывод сообщений: emp is NULL #1 emp.employee_id is NULL #1 emp is NULL #2 emp.employee_id is NULL #3
Вызов методов неинициализированного объекта вызывает предопределенное исключение NULL_SELF_DISPATCH. Если атрибуты неинициализированного объекта передаются как параметры in, то они считаются равными null. Если такие атрибуты передаются как параметры OUT или IN OUT, то генерируется исключение при попытке присвоить им значение.

Манипулирование объектами в PL/SQL

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

Доступ к атрибутам объекта в "точечной" нотации

Вы ссылаетесь к атрибуту по его имени. Чтобы получить доступ к значению атрибута или изменить его используйте "точечную" нотацию. Имена атрибутов могут выстраиваться в последовательности, таким образом осуществляется доступ к вложенным типам.

Пример 12-4 Доступ к атрибутам объекта

DECLARE
 emp employee_typ;
BEGIN
 emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
    '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,
     address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
 DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);
 DBMS_OUTPUT.PUT_LINE(emp.address.street);
 DBMS_OUTPUT.PUT_LINE(emp.address.city || ', ' ||emp. address.state || ' ' ||
            emp.address.postal_code); 
END;
/

Вызов конструкторов и методов объекта

Вызовы конструкторов осуществляются так же как и вызовы функций. Как и все функции, конструктор вызывается как часть выражения, как показано в примерах 12-4 и 12-5.

Пример 12-5 Вставка рядов в объектную таблицу

DECLARE
 emp employee_typ;
BEGIN
 INSERT INTO employee_tab
    VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON',
        '555.111.2222', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110,
        address_typ('123 Main', 'San Francisco', 'CA', '94111'))
       );
 INSERT INTO employee_tab
    VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN',
        '555.111.3333', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110,
        address_typ('123 Broadway', 'Redwood City', 'CA', '94065'))
       );
END;
/



Когда вы передаете параметры в конструктор, инициализационные значения передаются атрибутам создаваемого объекта. Если Вы вызываете конструктор по умолчанию для передачи значений атрибутам, Вы должны передать значения для всех атрибутов. Вы можете вызвать конструктор, используя именованную или позиционную нотацию.

Подобно подпрограммам пакеджей, методы вызываются используя "точечную" нотацию. В примере 12-6, метод display_address вызывается, чтобы показать атрибуты объекта. Обратите внимание на использование функции VALUE, которая возвращает значение объекта. VALUE принимает аргумент для корреляции переменных. В этом контексте корреляция переменных это переменная ряда или псевдоним таблицы с рядом объектной таблицы.

Пример 12-6 Доступ к методам объекта

DECLARE
 emp employee_typ;
BEGIN
 SELECT VALUE(e)
    INTO emp
   FROM employee_tab e
 WHERE e.employee_id = 310;
 emp.display_address();

Для статических методов, используйте вызов типа type_name.method_name вместо вызова, специфического для экземпляра типа.

При вызове метода экземпляра подтипа фактически вызываемый метод зависит от объявлений в иерархии типов. Если подтип переопределяет метод, который наследуется от супертипа, то фактически выполняется метод подтипа. Если подтип не переопределяет метод, то вызывается метод супертипа. Эта возможность известна как динамическая диспетчеризация метода.
Примечание:
Если Вы реализуете методы на PL/SQL, Вы не можете вызывать метод базового объекта и супертипа с ключевым словом super или эквивалентный метод в наследуемом объекте.

Изменение и удаление объектов

Из PL/SQL блока вы можете модифицировать и удалять ряды в объектной таблице.

Пример 12-7 Изменение и удаление рядов в объектной таблице

DECLARE
 emp employee_typ;
BEGIN
 INSERT INTO employee_tab
    VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS','555.111.2277','07-NOV-04',
        'SA_REP', 8800, .12, 101, 110,
        address_typ('540 Fillmore', 'San Francisco', 'CA', '94011'))
       );
 UPDATE employee_tab e
   SET e.address.street = '1040 California'
  WHERE e.employee_id = 370;

 DELETE FROM employee_tab e
    WHERE e.employee_id = 310;
END;
/

Манипуляции объектами через Ref модификаторы

Вы можете использовать ссылки, используя функцию REF, аргументом которой является связанная переменная.

Пример 12-8 Изменение рядов о объектной таблице, используя REF модификатор.

DECLARE
 emp     employee_typ;
 emp_ref REF employee_typ;
BEGIN
 SELECT REF(e)
  INTO emp_ref
  FROM employee_tab e
  WHERE e.employee_id = 370;

 UPDATE employee_tab e
   SET e.address = address_typ('8701 College', 'Oakland', 'CA', '94321')
  WHERE REF(e) = emp_ref;
END;
/

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

Вы не можете использовать ссылки в PL/SQL. Например, присвоение в примере 12-9 с использованием ссылки не допустимо. Вместо ссылки используйте функцию DEREF или обратитесь к объекту через пакедж UTL_REF. Информацию о функции REF смотрите в "Oracle Database SQL Reference".

Пример 12-9 Использование DEREF в выражении SELECT INTO

DECLARE
 emp      employee_typ;
 emp_ref   REF employee_typ;
 emp_name VARCHAR2(50);
BEGIN
 SELECT REF(e)
  INTO emp_ref
  FROM employee_tab e
  WHERE e.employee_id = 370;
-- следующее присвоение вызовет исключение, такая операция не допустима в PL/SQL
-- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
-- emp := DEREF(emp_ref); не допускается использование DEREF в процедурных выражениях
 SELECT DEREF(emp_ref)
  INTO emp
  FROM DUAL; -- использование пустой таблицы DUAL
 emp_name := emp.first_name || ' ' || emp.last_name;
 DBMS_OUTPUT.PUT_LINE(emp_name);

Информацию о функции DEREF смотрите в "Oracle Database SQL Reference".

Объявления 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, вставка нескольких скалярных величин и вложенной таблицы CourseList в таблицу sophomores.

Пример 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 используется с многоуровневой коллекцией, которая содержит объектный тип.
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;
/



Из анонимного блока, Вы можете вызвать процедуры пакеджа TEAMS:

Пример 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;
/

Метки: PL/SQL  

Комментарии.

Внимание.
Комментировать могут только зарегистрированные пользователи.
Возможно использование следующих HTML тегов: <a>, <b>, <i>, <br>.

guest Oct 10, 2014 1:37:46 PM
Если что-то подобное где-то написано (здесь, в блоге или на хабре), то прошу прошения то вытереть все это. используем sqlplus var r refcursor begin open :r for select sysdate from dual; end; / print r