ТИпы данных PL/SQL

Все типы определены в пакете STANDARD, где также содержатся встроенные SQL-функции и функции преобразования типов.

Типы данных PL/SQL могут быть отнесены к одной из следующих категорий:

1) скалярные;

2) составные;

3) ссылочные;

4) объектные.

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

Таблица Символьные типы

CHAR[(размер)]	CHARACTER (размер)	Сохраняет строки символов фиксированной длины. Максимальный размер 32767 байт, однако, для баз данных ограничен 2000 байт.
VARCHAR2(размер)	VARCHAR(размер), STRING	Сохраняет строки символов переменной длины. Максимальный размер 32767 байт, однако, в БД может хранить только 4000 байт.
LONG		Хранит последовательность символов переменной длины, максимально 32760 байт, не совпадает с типом LONG БД, размер которого больше.
RAW (размер)		Хранит двоичные данные фиксированной длины и может содержать до 32Кбайт данных. В БД содержит только 2Кбайт.
LONG RAW		Хранит до 32 Кбайт двоичных данных, не совпадает с типом LONG RAW БД, размер которого больше.
ROWID		Хранит значение ROWID записи БД без конвертирования его к символьному типу, поддерживает физические идентификаторы строк.
UROWID		Поддерживает как физические, так и логические идентификаторы строк.
NCHAR		Содержит национальные символьные данные фиксированной длины.
NVARCHAR2		Содержит национальные символьные данные переменной длины.

Числовые типы PL/SQL приведены в таблице

Таблица Числовые типы

Тип данных	Подтип	Описание
BINARY_INTEGER (-2147483647..2147483647)		Сохраняет целые числа со знаком, используется для значений, которые не будут храниться в БД, а только используются в вычислениях. Использует библиотечную арифметику.
NUMBER (P, S) (P - точность, S - масштаб)	DEC, DECIMAL, DOUBLE PRECISION, FLOAT(точность), INTEGER, INT, NUMERIC, REAL, SMALLINT	P, S либо не указываются, либо указываются вместе. Сохраняет числа с фиксированной и плавающей точкой, имеют перечисленные подтипы. Использует библиотечную арифметику, хранятся в десятичном формате. Если используются в вычислениях, то автоматически преобразуются в BINARY_INTEGER и назад.
PLS_INTEGER (-2147483647..2147483647)	NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE	Сохраняет целые числа со знаком, имеет тот же формат и диапазон, что и BINARY_INTEGER, однако, если при выполнении некоторой операции переполнить значение этого типа, то возникнет ошибка. Для ускорения вычислений использует машинную арифметику. NATURAL и NATURALN сохраняют только неотрицательные целые числа, причем последний запрещает NULL-значения. POSITIVE и POSITIVEN сохраняют только положительные целые числа, причем последний запрещает NULL-значения. SIGNTYPE сохраняет только -1, 0, 1
BINARY_DOUBLE		Тип двойной точности с плавающей точкой, используется для научных вычислений.
BINARY_FLOAT		Тип одинарной точности с плавающей точкой, используется для научных вычислений.
TRUSTED
MLSLABEL

К логическим (булевым) типам относится единственный тип BOOLEAN, который принимает значения TRUE, FALSE, NULL.

К числу подтипов дата-время относятся типы DATE, TIMESTAMP, INTERVAL, которые практически совпадают с соответствующими типами БД.!!!!

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

Записи. Запись (record) – это составная структура данных, которая и по сути, и по структуре похожа на строку таблицы базы данных. Она состоит из нескольких элементов, называемых полями (fields). Запись в целом не имеет значения – значение имеет каждое отдельное поле, а запись предоставляет возможность хранения этих значений и доступ к ним как к группе. Для того чтобы использовать запись, необходимо определить её и объявить переменную такого типа.

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

DECLARE

v_roomrecord rooms%ROWTYPE;

Записи, определяемые программистом, должны быть явно объявлены командой TYPE в секции объявлений PL/SQL или в спецификации пакета. Затем можно объявить переменные такого типа или использовать его как часть другого типа

DECLARE

TYPE name_rectype IS RECORD

(prefix VARCHAR2(15),

fname VARCHAR2(15),

lname VARCHAR2(30),

suffix VARCHAR2(10));

new_emp_rec name_rectype;

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

Инициализация записей. На отдельные поля записи можно ссылаться посредством точечной нотации:

имя_записи. имя_поля

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

shipto_address_rec:=customer_address_rec;

Но приведенное ниже сравнение уже не разрешено (вместо этого следует сравнивать отдельные поля записей):

IF shipto_address_rec=customer_address_rec

THEN

....

END IF;

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

1. применить оператор присваивания для присваивания значений полю:

new_emp_rec.hire_date:=SYSDATE;

2. выполнить SELECT INTO для целой записи или отдельных полей:

SELECT emp_id, dept, title, hire_date, college_recruit

INTO new_emp_rec

FROM emp

WHERE surname=’LI’

3. выполнить FETCH INTO для целой записи или отдельных полей:

FETCH emp_cur INTO new_emp_rec;

FETCH emp_cur INTO new_emp_rec.emp_id, new_emp_rec.name;

4. присвоить все поля одной переменной записи другой переменной записи того же типа:

IF rehire THEN

new_emp_rec:=former_emp_rec;

ENDIF;

Коллекции PL/SQL. Коллекция(collection) – это составная структура данных, которая ведёт себя как список или одномерный массив (PL/SQL не поддерживает традиционные массивы).

В PL/SQL существует три типа коллекций: асcоциативные (индексные) массивы (associative arrays), вложенные таблицы (nested tables) и массивы переменной размерности (variable arrays) – VARRAY.

Коллекции реализуются как типы (TYPE). Как и для других типов, определяемых пользователем, необходимо сначала определить тип, а затем объявить экземпляр такого типа. Определение типа может быть сохранено в БД или помещено в программу PL/SQL. Каждый экземпляр такого типа является коллекцией.

Сравнение типов коллекций

Таблица

Характеристика	Ассоциативный массив	Вложенная таблица	VARRAY
Размерность	Одна	Одна	Одна
Может применяться в SQL	Нет	Да	Да
Может использоваться как тип данных столбца таблицы?	Нет	Да; данные хранятся в отдельной таблице	Да; данные обычно хранятся в той же таблице
Неинициализиро- ванное состояние	Пустой (не может содержать NULL); элементы не определены	Атомарно пустая; обращение к элементам недопустимо	Атомарно пустой; обращение к элементам недопустимо
Инициализация	Автоматическая при объявлении	Посредством конструктора, выборки, присваивания	Посредством конструктора, выборки, присваивания
В PL/SQL на элементы можно ссылаться при помощи:	BINARY_INTEGER или VARCHAR2	Положительное целое от 1 до 2147483647	Положительное целое от 1 до 2147483647
Разреженная?	Да	Изначально-нет; после удалений-да	Нет
Ограниченная?	Нет	может быть расширена	Да
Можно присваивать значение любому элементу в любой момент времени?	Да	Нет; сначала может потребоваться выполнить процедуру EXTEND	Нет; сначала может потребоваться выполнить EXTEND, причём расширение возможно только до верхней границы
Средства расширения	Присвоить значение элементу с новым индексом	Использовать встроенную процедуру EXTEND или TRIM для уплотнения, без предопределённого максимума	EXTEND или TRIM, но только до объявленного максимального размера
Может сравниваться на предмет равенства	Нет	Нет	Нет
Элементы сохраняют порядковую позицию и индекс при извлечении из БД	Коллекция не может храниться в БД	Нет	Да

Ассоциативный массив это одномерная неограниченная разреженная коллекция однородных элементов, которая доступна только в PL/SQL, но не в БД. Этот массив больше похож на списки и карты распределения в классических языках программирования, чем на обычные массивы. Ассоциативный массив может индексироваться при помощи типов BINARY_INTEGER, PLS_INTEGER или VARCHAR2. Он является ключом к использованию оператора FORALL или фразы BULK COLLECT, которые разрешают групповую пересылку данных из БД в программный модуль.

Полный синтаксис:

CREATE [OR REPLACE] TYPE имя_типа IS TABLE OF тип_элемента [NOT NULL]

INDEX BY [BINARY_INTEGER| PLS_INTEGER | VARCHAR2(размер)];

Ключевые слова:

имя_типа – любой разрешённый идентификатор, который впоследствии будет использоваться для объявления коллекции.

тип_элемента – это тип элементов коллекции. Все элементы должны быть одного типа; обычно бывает скалярный тип данных, объектный тип или ссылочный объектный тип. Если элементы представляют собой объекты, то сам объектный тип не может иметь в качестве атрибута коллекцию. К явно запрещённым типам данных элементов коллекций относятся BOOLEAN, NCHAR, NCLOB, NVARCHAR2, BCURSOR, TABLE и VARRAY.

NOT NULL – означает, что в коллекции данного типа не может быть элементов со значением NULL. Однако она может быть атомарно пустой (неинициализированной).

Вложенная таблица – это одномерная неограниченная коллекция однородных элементов, доступная как и PL/SQL, так и в БД в виде столбца или таблицы. Вложенные таблицы изначально являются плотными (имеют последовательные индексы), но могут стать разреженными в результате удалений. Самыми близкими соответствиями со стандартными языками программирования для них являются множества и множества с повторяющимися элементами.

[CREATE [OR REPLACE]] TYPE имя_типа IS TABLE OF тип_элемента [NOT NULL];

Значения ключевых слов аналогичны aссоциативному массиву.

VARRAY – одномерная ограниченная коллекция однородных элементов, которая доступна как в PL/SQL, так и в БД. Массивы VARRAY всегда ограничены и никогда не бывают разреженными. В отличие от вложенных таблиц порядок их элементов сохраняется при сохранении их в БД и извлечении оттуда. Массив переменной длины является массивом в традиционном смысле языков программирования. Для ссылки на элементы структуры в них используются последовательные значения индексов

[CREATE [OR REPLACE]] TYPE имя_типа IS{VARRAY|

VARYING ARRAY} (максимально_элементов) OF тип_элемента [NOT NULL];

Ключевые слова:

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

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

TYPE index_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

indtab intex_table;

где indtab – это экземпляр index_table.

При помощи атрибута %TYPE коллекции можно связать с ней тип данных другой коллекции, например:

TYPE varray1 IS VARRAY(20) OF NUMBER;

v1 varray1;

v2 v1% TYPE;

Если определение v1 измениться, то вместе с ним изменится и определение v2.

Функции и методы коллекций. Действия с коллекциями можно выполнить посредством следующих функций:

CAST – преобразует тип одной коллекции к типу другой коллекции, например:

SELECT column_value

FROM table (SELECT CAST (colors AS color-tab_t)

FROM color_models_a

WHERE model_type=’RGB’);

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

SELECT b.genus, b.species,

CAST(MULTISET(SELECT bh.country

FROM bird_ habitats bh

WHERE bh.genus=b.genus

AND bh.species=b.species)

AS_ country_tab_t)

FROM birds b;

TABLE – отображает коллекцию на таблицу БД; обратная функция к MULTISET.

SELECT *

FROM color_models c

WHERE ‘RED’ IN (SELECT *FROM TABLE (c. colors));

Посредством TABLE() можно преобразовать нерезидентную коллекцию:

DECLARE

birthdays Birthdate_t:= Birthdate_t(’24-SEP-1984’, ’19-JUN-1993’);

BEGIN

FOR the_rec IN

(SELECT COLUMN_VALUE

FROM TABLE (CAST(birthdays AS Birthdate_t)));

Коллекции также поддерживают ряд методов, приведенных в таблице

Таблица

Метод	Описание
EXISTS(x)	Возвращает TRUE, если существует x-й элемент вложенной таблицы или изменяемого массива. В противном случае возвращает FALSE.
COUNT	Возвращает число элементов, находящихся в данный момент во вложенной таблице или в изменяемом массиве.
LIMIT	Возвращает максимальное число элементов, которое может содержать изменяемый массив.
FIRST	Возвращает первый компонент вложенной таблицы или изменяемого массива.
LAST	Возвращает последний компонент вложенной таблицы или изменяемого массива.
PRIOR(x)	Возвращает компонент, предшествующий x-тому компоненту вложенной таблицы или изменяемого массива.
NEXT(x)	Возвращает компонент, следующий за x-м компонентом вложенной таблицы или изменяемого массива.
EXTEND(x,y)	Добавляет x копий y-го элемента вложенной таблицы или изменяемого массива.
TRIM(x)	“Вырезает” x элементов с конца вложенной таблицы или изменяемого массива.
DELETE(x)	Удаляет некоторые или все элементы вложенной таблицы или изменяемого массива.

Для вызова этих методов применяется синтаксис точечной нотации:

имя_коллекции.имя_метода[(параметры)]

Добавление и удаление элементов. Можно добавить элементы в ассоциативный массив, просто сославшись на новые индексы. Для добавления элементов во вложенные таблицы или массивы VARRAY необходимо сначала увеличить коллекцию с помощью функции EXTEND, а затем присвоить новому элементу значение посредством одного из ранее представленных методов.

Для удаления элемента вложенной таблицы, независимо от его позиции, применяется функция DELETE. Удалять элементы позволяет и функция TRIM, но только для удаления из конца коллекции.

Не стоит использовать для одной коллекции DELETE и TRIM – результаты могут быть совсем не теми, которые вы ожидали.

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

Приведем простой пример определения вложенной коллекции:

CREATE TYPE books IS TABLE OF VARCHAR2(64);

CREATE TYPE our_books IS TABLE OF books;

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

- явно, при помощи конструктора;

- неявно, посредством извлечения из БД;

- неявно, посредством прямого присваивания другой переменной коллекции.

Конструктор – это встроенная функция, имя которой совпадает с именем коллекции. Она создает коллекцию из переданных ей элементов. Например, можно создать вложенную таблицу цветов и явно инициализировать ее тремя элементами при помощи конструктора:

DECLARE

TYPE color_tab_t IN TABLE OF VARCHAR2(30);

colors_tab_t (‘RED’, ‘GREEN’, ‘BLUE’);

BEGIN

Применяя второй способ инициализации, можно создать вложенную таблицу цветов и неявно инициализировать ее при помощи извлечения данных из БД:

--Создать хранение в БД тип вложенной таблицы.

CREATE TYPE colors_tab_t IS TABLE OF VARCHAR2(32);

--Инициализировать коллекцию цветов данными из таблицы.

DECLARE

basic_colors colors_tab_t;

BEGIN

SELECT colors INTO basic_colors

FROM color_models

WHERE model_type=’RGB’;

END;

Третий метод инициализации – инициализируем таблицу неявно, используя присваивание из существующей коллекции:

DECLARE

basic_colors Color_tab_t:=

Color_tab_t(‘RED’, ‘GREEN’, ‘BLUE’);

my_colors Color_tab_t;

BEGIN

my_colors:=basic_colors;

my_colors(2):=’MUSTARD’;

Ссылочные типы данных REF CURSOR и REF. Для хранения скалярных и составных типов переменных выделяется определенная область памяти. Переменная присваивает ей имя, по которому происходит ссылка на переменную. При этом нельзя отменить выделение памяти и одновременно сохранить возможность работы с переменной. Ссылочные типы обходят этот запрет, т.е. переменные, объявленные как ссылочные, могут указывать на различные области памяти.

Переменные типа REF CURSOR называются курсорными переменными. Можно определить курсорную переменную как принадлежащую типу SYS_REFCURSOR и получать с ее помощью наборы данных из процедуры или функции. Например:

CREATE OR REPLACE PROCEDURE authors_sel

(cv_results IN OUT SYS_ REFCURSOR)

IS

BEGIN

OPEN cv_results FOR

SELECT id, first_name, last_name

FROM authors;

END;

Проверку процедуры можно выполнить следующим образом:

VARIABLE x REFCURSOR

EXEC authors_sel(:x)

PRINT x

Тип REF используется с объектными типами. Значение REF следует представлять себе как указатель на экземпляр объекта в объектной таблице или объектном представлении.

Объектные типы. Oracle поддерживает следующие четыре типа для больших объектов:

BFILE – для внешнего двоичного файла, сохраняет указатели на объекты LOB, управляемые файловыми системами, внешними по отношению к СУБД;

BLOB – для внутреннего двоичного объекта;

CLOB – для внутреннего символьного объекта;

NCLOB – для внутреннего символьного объекта, учитывающего национальный набор символов.

Типы LOB от типа LONG отличаются, главным образом, тем, что при выборе значения любого LOB-типа посредством оператора SELECT возвращается указатель, а не само значение; кроме того, типы LOB могут быть и внешними.

Любой объект LOB состоит из двух частей: данных и указателя на эти данные, называемого локатором.

Типы BLOB, CLOB или NCLOB могут использоваться как для столбца базы данных, так и для переменной PL/SQL.

Для загрузки объекта LOB предусмотрен пакет PL/SQL DBMS_LOB.

Процедуры и функции для работы с LOB-типами приведены в таблице

Таблица

Синтаксис	Описание
APPEND (d1,d2)	Добавляет d2 к d1
COMPARE(d1,d2,n,pos1,pos2)	Сравнивает n байт значений d1 и d2
COPY (d,s,n,dp,sp)	Копирует n байт из d в s.
FILEOPEN (bdata,m)	Открывает объект типа BFILE в режиме, указанном параметром m
LOADFROMFILE (bdata1,data2,n,pos1,pos2)	Копирует n байт объекта типа BFILE bdata1 в любой объект LOB data2
GETLENGTH (data)	Возвращает длину указанного объекта LOB
READ (data,n,pos,buf)	Читает из объекта data n байт
WRITE (data,n,pos,buf)	Копирует из буфера buf n байт
EMPTY_CLOB (), EMPTY_BLOB ()	Создают "пустой" объект указанного типа

Дата добавления: 2014-01-04