Oracle как создать тип

Обновлено: 15.01.2025

Я решил написать цикл небольших заметок благодаря посту Хорошие привычки в PL/SQL, советы которого и рассмотрю.

1. Нюансы %TYPE/%ROWTYPE

Cделаем тест-кейс — создадим табличку с полем типа Integer и внесем в нее тестовое значение:

create table test_integer(a integer ); create type integer_table as table of integer ; / insert into test_integer values (1E125); commit ; select * from test_integer;

Как видите, значение прекрасно вставилось. А теперь давайте попробуем его получить в pl/sql блоке:

declare l_a test_integer.a%type; begin select a into l_a from test_integer; dbms_output.put_line( 'Все ок, значение=' || l_a); exception when others then dbms_output.put_line( 'Все плохо! ' ||sqlerrm); end ; /

Причина ошибки кроется в том, что INTEGER в SQL это number(*,0) — floating-point number, у которого scale = 0, а INTEGER в PL/SQL это «SUBTYPE INTEGER IS NUMBER(38,0);».

Что же насчет типа из той статьи "CREATE TYPE id_list IS TABLE OF INTEGER;"?
Естественно, это коллекция pl/sql integer'ов:

Тип id_list я создал как integer_table — это просто моя прихоть создавать типы коллекций с постфиксом _TABLE. Кроме того, я обычно не создаю по типу на каждую таблицу table of table_name%rowtype, а только на часто используемые или если эти типы используются в параметрах процедур и функций. Кстати говоря, если у вас oracle < 11g и вы создаете тип в пакете и затем используете его в параметрах или полях объектов уровня схемы, то это автоматически создает и тип на уровне схемы с «многозначными» названиями типа: SYS_PLSQL_2906526_17_1, в 11g эти типы просто скрыли.

2. Select for update

Не секрет, что если мы хотим что-то проапдейтить в выборке, то надо использовать select for update для блокировки необходимых строк целиком или полей в них. Например, в той процедуре из обсуждаемой статьи нет кляузы for update и результаты могут быть фееричными(изменение salary не у тех работников, если в параллельной сессии изменялся отдел, потеря изменений salary в случае параллельного изменения salary другой сессией в зависимости от очередности коммита в 1-й или 2-й сессии), поэтому select следовало бы переписать так:

SELECT e.employee_id,e.salary,e.hire_date FROM employee e WHERE department_id = dept_in FOR UPDATE OF e.salary;

Учтите, что «OF e.salary» не означает, что заблокировано будет изменение только поля e.salary(такое невозможно), будут заблокированы все строки таблицы с алиасом e:

The columns in the OF clause only indicate which table or view rows are locked.

Указание здесь e.salary означает лишь к кому дополнительно применять механизм statement restart, и, поэтому в данном случае, как и во всех случаях когда вам нужно заблокировать строки всех таблиц из FROM, а изменяемые поля перечислены в списке select, можно не указывать «OF . »
Кстати, с for update можно нарваться на ORA-00060: deadlock detected.
Рассмотрим пример:

create table test_integer pctfree 99 pctused 1 as select level id, trunc(10*dbms_random. value ) val, rpad( 'x' ,100) padding from dual connect by level <=100 / create index test_indeger_idx on test_integer(id) / exec dbms_stats.gather_table_stats(ownname => user ,tabname => 'TEST_INTEGER' , cascade => true );

create or replace function integer_delay(p_i in integer ,p_interval in integer ) return integer is begin dbms_lock.sleep(seconds => p_interval); return p_i; end ;

И запустим в параллельных сессиях:
1:

begin for rec in ( select --+ index (test_integer_idx) id,val from test_integer where integer_delay(id,1) in (100,30,1) order by id desc for update ) loop dbms_output.put_line(rec.id); end loop; commit ; end ;

Иногда когда важна скорость можно воспользоваться параметрами к for update: nowait или skip locked. Кстати, следует учесть, что в случае использования skip locked оконные функции и rownum будут отрабатывать на целом наборе не исключая блокированных. Пример:
1-я сессия:

select * from test_integer where id in (1,3,4,8) for update ;

select id ,row_number() over ( order by id) rn ,rownum from test_integer t where id < 10 order by id for update skip locked

3. Работа с коллекциями

Проведем пример снова с test_ab, только сначала снова установите b=1 для всех столбцов.

Помимо сравнительно простых встроенных типов данных — как перешедших из стандартов SQL , так и собственных, — в Oracle имеется возможность использовать составные. Это конструируемые типы объектов, рассчитанные на хранение в БД данных, имеющих внутреннюю структуру. Эта структура известна СУБД , и СУБД позволяет с ней работать. Объектные типы позволяют хранить и обрабатывать средствами СУБД "сложно устроенные данные" более продвинутым образом, нежели это позволяет техника "больших неструктурированных объектов" типов LOB . Ввиду наличия вполне определенного типа (даже если это тип коллекции ), единичное объектное значение можно полагать за скаляр, хотя оно и не будет атомарным.

Хранение в столбцах таблицы значений в виде объектов, в смысле объектного подхода (ОП в программировании и моделировании), фирма Oracle впервые обеспечила в рамках так называемой "объектно-реляционной модели" начиная с версии Oracle 8. Некоторые существенные пробелы первой реализации (например, отсутствие наследования типов ) были устранены в версии 9. Примеры ниже не выходят за рамки возможностей версии 9.2, позже которой, впрочем, никаких существенных нововведений по объектной части не наблюдалось. Объектные возможности Oracle в общем следуют определениям SQL :1999, однако делают это непунктуально.

Программируемые типы данных и объекты в БД

Простой пример

Ниже приводится простой пример использования программируемых (объектных) типов.

Вначале требуется создать "тип", как разновидности хранимых элементов БД. Пример создания типа объекта (в SQL*Plus):

Здесь типу ADDRESS_TYPE приписаны два "свойства" (по объектной терминологии): ZIP и LOCATION . В реальной жизни для представления адреса в типе наверняка будет указано большее количество свойств, однако в ознакомительном примере их более пространный перечень излишен и не добавит понимания техники.

Определение типа напоминает определение таблицы, однако в отличие от таблицы (а также стандарта SQL и от реляционного подхода) тип объекта в Oracle не имеет права содержать ограничений целостности (которые в таком случае можно было бы назвать "ограничениями целостности типа"). Если необходимо их указать, сделать это придется только по месту употребления типа, то есть в описании таблицы.

В соответствии с традициями объектного подхода (уместно вспомнить, что "объектной теории", в отличие от реляционной, не создано) Oracle разрешает использовать тип для создания "буквальных значений" и собственно объектов. Далее приводится сначала несколько примеров первого, а затем второго.

"Буквальные значения" фактически позволяют работать со значениями, обладающими известной СУБД структурой и однозначно определяются набором значений элементов своей структуры.

Примеры использования типа ADDRESS_TYPE для определения столбца в обычной таблице:

Столбцы ADDR и HOME можно с некоторой вольностью назвать "объектными атрибутами". Они не позволяют хранить объектные значения в виде самостоятельной сущности и ссылаться на них ссылками. Локализовать такие значения можно только по обычным правилам поиска данных в таблице.

В выражениях явно указанные объектные значения формулируются с помощью конструктора. В отличие от других объектных систем, например, от Java, в Oracle конструктор умолчательно имеет список параметров, соответствующих свойствам типа. Примеры применения в операциях добавления данных:

Oracle допускает определенные синтаксические вольности в записи выражения над объектными данными. Здесь и далее используются частные случаи возможных формулировок.

Пример применения в запросе о сотрудниках, "работающих по месту жительства", за исключением конкретно указанного адреса:

Пример показывает легкость формулирования сравнения составных величин, каковыми являются адреса. Сравнение осуществляется поэлементно, путем сравнением всех свойств по очереди. Увы, но простота формулировки не дает права программисту расслабляться и забывать об особых случаях сравнения с данными типа CHAR и с NULL . Так, присутствие NULL в буквальных объектных значениях запутывает проблему сравнения еще больше, чем для случая простых типов. Сравните:

То есть получается, что x = x не дает TRUE , но притом x IS NOT NULL дает TRUE ( x имеет значение).

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

Таблицы объектов

Созданный в БД тип можно употребить и для создания "таблиц объектов":

Хотя для этой категории хранимых элементов используется термин "таблица", такая таблица всегда содержит ровно один столбец, и именно объектного типа.

Запись занесения "строк" в такую таблицу может быть, в частности, такой:

SELECT a.*, UPPER ( location ) FROM addresses1 a;

Объекты в таких таблицах хранятся как самостоятельные сущности, у которых имеется автоматически порождаемый СУБД внутренний уникальный идентификатор object ID, в соответствии с классическим объектным подходом позволяющий ссылаться на конкретные объекты из других таблиц или из программы. Сравнение элементов-"строк" в таблице объектов друг с другом происходит уже не по значениям свойств, как в случае объектного столбца в обычной таблице, а по значению object ID. Перейти на сравнение значений свойств позволяет функция VALUE, например:

Сделан запрос об отделах, расположенных по адресам из таблицы ADDRESS1 .

Не исключено, что создатели функции VALUE обсуждали другое ее название — LITERAL_VALUE . По крайней мере, оно точнее описывает совершаемое действие: создание значения со структурой из объекта. Буквальные значения сравниваются друг с другом по значениям их свойств, а объекты — по значениям object ID.

Группирует один или несколько столбцов для формирования нового типа данных.
Эти столбцы будут иметь свое имя и тип данных
Тип записи может принимать данные
- Как одна запись, состоящая из множества столбцов ИЛИ
- Может принимать значение для одного конкретного столбца записи
Синтаксис для объявления на уровне базы данных:

В первом синтаксисе мы видим ключевое слово «CREATE TYPE», которое инструктирует компилятору создать тип записи с именем «type_name_db» с указанным столбцом в качестве объекта базы данных.

Это дается как отдельное утверждение, а не внутри какого-либо блока.

Синтаксис для объявления на уровне подпрограммы:

В синтаксисе мы создаем тип записи с именем «type_name» только внутри подпрограммы.

В обоих методах объявления способ определения столбца и типа данных одинаков.

Пример 1: Тип записи как объект базы данных
- EMP_NO (NUMBER)
- EMP_NAME (VARCHAR2 (150))
- МЕНЕДЖЕР (НОМЕР)
- Заработная плата (НОМЕР)
- Приведенный выше код создаст тип emp_det в качестве объекта базы данных.
- Он будет иметь 4 столбца emp_no, emp_name, manager и salary, как определено.
- Теперь emp_det похож на другой тип данных (например, NUMBER, VARCHAR @ и т. Д.) И виден во всей базе данных. Следовательно, это можно использовать во всей базе данных для объявления переменной этого типа.
Вывод:

Создан тип emp_det в качестве типа записи на уровне базы данных.

В этом примере мы увидим, как создать тип записи на уровне подпрограммы и как заполнить и извлечь значения из нее на уровне столбца.

Мы собираемся создать тип записи emp_det на уровне подпрограммы, и мы будем использовать то же самое для заполнения и отображения данных из него.

Вывод:

Объяснение кода:

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

Объяснение кода:

Вывод:

Примечание. Доступ к типу записи возможен только на уровне столбца при перенаправлении его значения в любой режим вывода.

В версии 8 в Oracle появилась возможность хранения неатомарных (нескалярных) значений в поле таблицы, а именно объекта в смысле объектного подхода (в рамках так называемой “объектно-реляционной модели” Oracle). Некоторые существенные пробелы этой первой реализации были устранены в версии 9. Примеры ниже используют возможности версии 9.2.

Сразу надо предостеречь от преувеличений достоинств объектного подхода в базах данных вообще. Действительно, неискушенный читатель некоторых руководств или рекламных материалов быстро впадет в недоумение: зачем же такие маститые разработчики СУБД, как фирмы IBM, Informix или Oracle так долго занимались табличной организацией данных, когда все это время рядом существовала более совершенная, удобная и т. д. объектная, первая реализация которой фирмой Xerox известна с 1980 года?

Непредвзятый ответ состоит в том, что ни табличная организация (часто вольно называемая “реляционной” применительно к конкретным СУБД), ни объектная не являются универсально “хорошими”, и что имеются свои достоинства и недостатки у одной и у другой. Некоторые соображения относительно областей применения обоих подходов к хранению данных можно найти в статье “Что объектам здорово, то реляциям смерть, и наоборот, и еще пол-оборота”.

В целом объектная реализация в Oracle традиционна для объектного подхода вообще. В основе лежит понятие объекта как совокупности свойств (атрибутов), причем действия с объектом регламентируются формулируемым набором методов (процедур или функций). Тип объекта задается сохраняемым в БД объектом TYPE.

Хранимые объекты

Простой пример

Рассмотрим схему БД, где хранятся данные о сотрудниках и отделах. Будем работать в схеме SCOTT, из которой на время нужно удалить таблицы EMP и DEPT (позже мы их восстановим).

Предположим, что и те, и другие имеют адреса: сотрудники - домашний, а отделы – юридический. Адрес имеет несколько полей (например, “индекс”, “район”, “населенный пункт”, “место”). В традиционной табличной реализации есть два способа промоделировать наличие адреса:

- включить одинаковые группы полей в таблицы сотрудников и отделов;
- создать отдельную таблицу адресов и включить в таблицы сотрудников и отделов ссылки на нее.

Первое решение неудобно тем, что адрес теряет свою идентичность: неудобно, например, сравнивать адреса, особенно в разных таблицах. Второе решение искусственно, если только не считать адреса самостоятельными объектами моделирования.

Объектные возможности последних версий Oracle дают возможность более приемлемой альтернативы. Для описания адреса создадим тип (здесь и далее предполагается использование в качестве рабочего инструмента SQL*Plus):

CREATE TYPE address_typ AS OBJECT (
zip CHAR(6),
location VARCHAR2(200))
/

Воспользуемся этим типом для описания сотрудников и отделов:

CREATE TABLE dept (
dname VARCHAR2(50),
deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
addr address_typ);

CREATE TABLE emp (
ename VARCHAR2(50),
empno NUMBER CONSTRAINT pk_emp PRIMARY KEY,
deptno NUMBER CONSTRAINT fk_emp REFERENCES dept,
home address_typ);

Проверим описания созданных объектов:

DESCRIBE address_typ
DESCRIBE dept
DESCRIBE emp

Пример заведения сотрудников и отделов:

INSERT INTO dept VALUES (
'Sales',
10,
address_typ('123456', 'Boston 123. '));

INSERT INTO emp VALUES (
'Smith',
1001,
10,
address_typ('123333', 'Boston 567. '));

Здесь выражение ADDRESS_TYP('123333', 'Boston 567. ') означает обращение к конструктору объекта, то есть к функции, автоматически создаваемой СУБД при заведении нового типа для возможности создавать новые объекты этого типа с нужными значениями атрибутов. Понятие конструктора общепринято в объектном подходе. В приведенных предложениях INSERT простановку адреса можно оформить чуть иначе, добавив, в соответствии с духом объектного подхода, ключевое слово NEW перед обращением к конструктору:

INSERT INTO emp VALUES (
'Allen',
1002,
10,
NEW address_typ('123456', 'Boston 123. '));

COLUMN dname FORMAT A20
COLUMN ename FORMAT A20
COLUMN addr FORMAT A40
COLUMN home FORMAT A40

SELECT * FROM dept;

SELECT * FROM emp;

SELECT ename, home FROM emp;

SELECT e.ename, d.dname FROM emp e, dept d WHERE e.home = d.addr;

SELECT e.ename, e.home.zip FROM emp e;

UPDATE emp
SET home = address_typ('123457', 'Boston 777. ')
WHERE ename = 'Allen';

UPDATE emp e SET e.home.zip = '123458' WHERE ename = 'Allen';

Создание таблицы объектов

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

CREATE TABLE addr_list1 OF address_typ;

CREATE TABLE addr_list2 OF address_typ;

Таблицы объектов в Oracle было бы точнее называть списками объектов, так как это всегда таблицы ровно из одного столбца объектного типа.

Заполнение данными происходит как и ранее:

INSERT INTO addr_list1 VALUES
(NEW address_typ('123456', 'Boston 123. '));

INSERT INTO addr_list1 VALUES
(address_typ('123458', 'Boston 123. '));

INSERT INTO addr_list2 VALUES
(address_typ('123333', 'Boston 567. '));

COLUMN location FORMAT A30

SELECT * FROM addr_list1;

SELECT VALUE(a) FROM addr_list1 a;

SELECT e.ename, e.home
FROM addr_list1 a, emp e
WHERE VALUE(a) = e.home;

(Функция VALUE специально придумана для возвращения значений объектов, а не атрибутов объектов по отдельности).

Ссылки на объект

Объекты, заведенные в объектных таблицах, имеют одно преимущество перед объектами, указанными как атрибут строки: на них можно ссылаться. Ссылка есть уникальный внутренний идентификатор объекта, и получить его можно с помощью функции REF:

COLUMN ref FORMAT A90
COLUMN value FORMAT A40

SELECT REF(a) ref, VALUE(a) FROM addr_list1 a;

Теперь можно поменять описание таблицы, например, DEPT, чтобы она заимствовала адреса отделов из имеющегося списка, а не хранила вместе со своими данными:

ALTER TABLE dept DROP (addr);

ALTER TABLE dept ADD (addr REF address_typ SCOPE IS addr_list1);

SELECT * FROM dept;

UPDATE dept d
SET d.addr =
(SELECT REF(a)
FROM addr_list1 a
WHERE VALUE(a)= address_typ('123458', 'Boston 123. '))
WHERE d.deptno = 10;

SELECT * FROM dept;

Фраза SCOPE IS при определении типа как ссылки на существующий объект необязательна, но позволяет фактически ссылаться только на объекты какой-нибудь объектной таблицы.

Раскрытие ссылки делается с помощью специальной функции DEREF:

COLUMN deref(addr) FORMAT A40

SELECT d.dname, DEREF(addr) FROM dept d;

Однако при обращении к нижележащим атрибутам раскрытие может выполняться и неявно (неявное преобразование типов, присутствующее в Oracle-диалекте SQL):

SELECT d.dname, d.addr.zip FROM dept d;

вместо более правильного

SELECT d.dname, DEREF(d.addr).zip FROM dept d;

Методы объектов

Выше было рассмотрено определение типа, содержащее описание атрибутов (“свойств”). Создадим тип сотрудников, в котором определен еще и метод:

CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
hiredate DATE,
deptno NUMBER,
home REF address_typ,
MEMBER FUNCTION days_at_company RETURN NUMBER)
/

Для описания тела метода-функции необходимо создать тело типа (аналогия пакет – тело пакета в PL/SQL):

CREATE TYPE BODY employee_typ IS
MEMBER FUNCTION days_at_company RETURN NUMBER IS
BEGIN
RETURN TRUNC(SYSDATE-hiredate);
END;
END;
/

Создадим таблицу объектов-сотрудников:

CREATE TABLE emp OF employee_typ;

INSERT INTO emp VALUES (
'Scott',
SYSDATE,
10,
(SELECT REF(a) FROM addr_list1 a
WHERE VALUE(a) = address_typ('123458', 'Boston 123. ')));

Пример обращения к методу:

COLUMN home.location FORMAT A20

SELECT e.ename, e.home.location, e.days_at_company() FROM emp e;

Виртуальные объекты

Переводить в существующей БД табличные описания данных в объектные не всегда возможно, а иногда и не нужно. В силу разных обстоятельств может оказаться удобной имитация объектов на основе данных, хранимых в традиционных таблицах. Тогда к одним и тем же данным можно обращаться и через объектный интерфейс, и через табличный. Достигается это с помощью виртуальных объектов (object views), которых можно так назвать по аналогии с виртуальными таблицами (views).

Для примера вернем описания и наполнение традиционным таблицам схемы SCOTT: EMP и DEPT.

(Сценарий demobld.sql выводит нас из SQL*Plus).

Упростим для примера описание типа EMPLOYEE_TYP:

ALTER TYPE employee_typ DROP ATTRIBUTE (home);

ALTER TYPE employee_typ ADD ATTRIBUTE (empno NUMBER);

ALTER TYPE employee_typ COMPILE;

Построим таблицу виртуальных объектов типа EMPLOYEE_TYP по исходным данным, хранящимся в EMP:

CREATE VIEW emp_ov OF employee_typ
WITH OBJECT IDENTIFIER (empno) AS
SELECT e.ename, e.hiredate, e.deptno, e.empno FROM emp e;

По своему поведению виртуальные объекты ничем не отличаются от первичных. Проверка (“объектного доступа” к табличным данным):

SELECT e.ename, e.days_at_company () FROM emp_ov e;

SELECT VALUE(e) FROM emp_ov e;

SELECT REF(e) FROM emp_ov e;

UPDATE emp_ov e SET e.ename = INITCAP(e.ename)
WHERE e.empno = 7934;

SELECT ename FROM emp_ov;

Возможность выполнения традиционных DML-операторов над базовыми таблицами, естественно, сохраняется:

Читайте также: