Использование объектных типов в 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(); END; /

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

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

Петрелевич Сергей
petrelevich@yandex.ru

Метки: Oracle   PL/SQL   СУБД  

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

Пожалуйста авторизуйтесь или зарегистрируйтесь.