Объектный тип данных

1. ВPL\SQLDeveloperкликаемПКМпоузлу «типы», создаемобъявлениеновоготипа. Создаваяметод, выбираемодиниздвухвариантовреализации – функцию. По умолчанию шаблон настроен на процедуру.

create or replace type PersonT as object

(

id NUMBER,

b_date date,

member function info return varchar2

);

2. На второй вкладке создаем тело типа (класс):

create or replace type body PersonT is

member function info return varchar2 is

r varchar2(100);

begin

SELECT ename || ' (' || job || ')'

INTO r

FROM Emp

WHERE empno = self. id;

RETURN r;

EXCEPTION WHEN OTHERS THEN

RETURN 'Info not found';

end;

3. Создаем и используем таблицу, типом одного из полей которой будет созданный нами новый тип PersonT:

CREATE TABLE Person

(

id NUMBER,

p PersonT

);

INSERT INTO Person VALUES(1, PersonT(7369, to_date('25. 10. 1960', 'dd. mm. yy')));

SELECT * FROM Emp;

SELECT pers. id, pers. p. info() FROM Person pers;

4. Можно также создать таблицу с одним полем для хранения объектов нового типа при помощи сокращенного синтаксиса:

CREATE TABLE Tp OF PersonT;

INSERT INTO Tp (SELECT PersonT(empno, null) FROM Emp);

SELECT * FROM Tp;

SELECT VALUE(t). info() FROM Tp t;

5. В приведенных выше примерах объекты нового типа при запросе к ним клонировались, но можно работать и с оригиналами объектов по ссылкам:

CREATE TABLE Tr

(

id NUMBER,

person_ref REF PersonT

);

INSERT INTO Tr

(

SELECT rownum, p_ref

FROM

(

SELECT REF(tp1) p_ref

FROM Tp tp1

WHERE tp1. id > 7500

)

);

SELECT * FROM Tr;

SELECT t. id, DEREF(t. person_ref). info() FROM Tr t;

6. Создадим еще один новый тип, содержащий более полную информацию о сотруднике, и контейнер для него - тип-коллекцию:

CREATE TYPE EmpT IS OBJECT (ename VARCHAR2(30)

, job VARCHAR2(30)

, sal NUMBER);

CREATE TYPE EmpTArray IS VARRAY(1000) OF EmpT;

7. Создадим таблицу для деревьев данных вида «информация об отделе» - «коллекция объектов с информацией о сотрудниках отдела»:

CREATE TABLE EmpTArrayTable (id NUMBER

, dname VARCHAR2(30)

, emparr EmpTArray);

8. Заполнить такую таблицу можно при помощи двух циклов – перебор отделов и перебор сотрудников отдела:

DECLARE

earray EmpTArray;

BEGIN

FOR dept_item IN (SELECT *

FROM Dept)

LOOP

earray: = EmpTArray();

FOR emp_item IN (SELECT *

FROM Emp

WHERE deptno = dept_item. deptno)

LOOP

earray. extend(1);

earray(earray. count): =EmpT(emp_item. ename

, emp_item. job

, emp_item. sal);

END LOOP;

INSERT INTO EmpTArrayTable VALUES (dept_item. deptno

, dept_item. dname

, earray);

END LOOP;

COMMIT;

END;

SELECT * FROM EmpTArrayTable;

9. Аналогичную структуру данных можно получить, используя вместо коллекций встроенные таблицы. К таким таблицам, расположенным в ячейках родительской таблицы, можно обращаться при помощи расширенного OracleSQL вместо PL/SQL, что увеличивает производительность приложения и читабельность сценария. Сначала создадим тип, пригодный для встраивания, и таблицу с полем, способным принимать значения такого типа (здесь EmpNestTab – имя технической таблицы, которая будет создана в ходе выполнения запроса и в дальнейшем будет принимать данные встроенных таблиц):

CREATE TYPE EmpNest IS TABLE OF EmpT;

CREATE TABLE DeptEmp (deptno NUMBER

, dname VARCHAR2(100)

, e EmpNest)

NESTED TABLE e STORE AS EmpNestTab;

10. Заполним верхний уровень основной таблицы:

INSERT INTO DeptEmp (SELECT deptno

, dname

, EmpNest()

FROM Dept);

11. Заполним второй уровень – встроенные таблицы:

INSERT INTO THE (SELECT e

FROM DeptEmp

WHERE deptno=10)

(SELECT EmpT(ename, job, sal)

FROM Emp

WHERE deptno = 10);

-- … и т. д. для каждого из отделов

SELECT * FROM DeptEmp;

12. Задание: создать альтернативную связку таблиц «ТипПользователя» - «Пользователь», используя объектный тип данных; написать запрос чтения для демонстрации результата.