В PL/SQL имеются скалярные и составные типы данных:
данные скалярных типов состоят из одного неделимого (атомарного) значения (логические значения, числа, даты, строки);
данные составных типов состоят из нескольких значений (записи и коллекции).
Скалярные типы данных PL/SQL
Объявления типов данных PL/SQL находятся в пакете STANDARD, находящемся в схеме пользователя SYS:
package STANDARD AUTHID CURRENT_USER is
type BOOLEAN is (FALSE, TRUE);
type DATE is DATE_BASE;
type NUMBER is NUMBER_BASE;
subtype FLOAT is NUMBER; NUMBER(126)
subtype REAL is FLOAT; FLOAT(63)
subtype INTEGER is NUMBER(38,0);
subtype INT is INTEGER;
subtype DEC is DECIMAL;
subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
subtype NATURAL is BINARY_INTEGER range 0..2147483647;
type VARCHAR2 is NEW CHAR_BASE;
subtype VARCHAR is VARCHAR2;
subtype STRING is VARCHAR2;
Видно, что объявленные в пакете STANDARD типы данных PL/SQL либо соответствуют типам данных Oracle SQL (_BASE-типы), либо вводятся как их подтипы (subtype).
Отметим наличие типа данных BOOLEAN, которого нет в Oracle SQL. Значения типа BOOLEAN можно, например, использовать в коде следующего вида:
l_amount_negative_flag BOOLEAN := amount<0;
IF l_amount_negative_flag THENEND IF;
Существенным отличием типов данных PL/SQL и Oracle SQL является большая максимальная длина значений типов CHAR и VARCHAR2, предназначенных для представления строк фиксированной и переменной длины:
для VARCHAR2 в PL/SQL максимальная длина значений находится в диапазоне от 1 до 32 767 байт (в Oracle SQL до версии Oracle 12c максимальная длина VARCHAR2 была до 4000 байт, в Oracle 12c она была увеличена также до 32 767 байт);
для CHAR в PL/SQL максимальная длина значений находится в диапазоне от 1 до 32 767 байт (в Oracle SQL до версии Oracle 12c максимальная длина CHAR была до 2000 байт, в Oracle 12c она была увеличена также до 32 767 байт).
Записи PL/SQL
Записи PL/SQL относятся к составным типам данных и определяются как наборы атрибутов, связанных определенными отношениями. Атрибуты записи могут быть как скалярных типов данных, так и других составных типовдругими записями и коллекциями.
Запись PL/SQL объявляется как пользовательский тип данных с помощью ключевого слова RECORD, в целом работа с записями PL/SQL похожа на работу с записями в языке Pascal или структурами в языке C:
DECLARE
TYPE t_person IS RECORD
(name VARCHAR2 (100),
secname VARCHAR2 (100),
surname VARCHAR2 (100),
born DATE);
l_person t_person;
BEGIN
l_person.surname := 'Ильин';
l_person.name := 'Виктор';
l_person.secname := 'Семенович';
l_person.born := TO_DATE('07.08.1969','dd.mm.yyyy');
print(l_person);
END;
Назначение записей PL/SQL:
считывание в записи PL/SQL строк результирующих выборок SQL-запросов (при объявлении записей PL/SQL на основе таблиц и курсоров с помощью атрибута %ROWTYPE);
объединение в одну структуру нескольких параметров процедур и функций (вместо большого числа параметров скалярных типов удобнее передавать в процедуры и функции один параметр составного типа).
Компактность и расширяемость исходного кодаосновное преимущество от использования записей PL/SQL. Сравните два варианта вызова процедуры печати сведений о человекес одним параметром-записью PL/SQL и с несколькими параметрами скалярных типов данных:
print(l_person) и print(l_name, l_secname, l_surname, l_born)
Первый вариант вызова выглядит более компактным. Кроме того, если появится необходимость обрабатывать новые сведения о человеке, например, ИНН и СНИЛС, то для второго варианта во все вызовы процедуры print по всему коду понадобится дописать по два новых параметра. Если же передавать описание человека в виде записи PL/SQL, то потребуется только добавить новые атрибуты в объявление типа t_person. Вносить изменения в заголовок функции print и в ее вызовы по исходному коду не потребуется. Тем самым с помощью использования записей PL/SQL обеспечивается расширяемость исходного кода.
Приведем основные правила работы с записями PL/SQL:
в определении атрибутов записей могут быть указаны ограничения NOT NULL и заданы значения атрибутов по умолчанию;
присвоение записи NULL присваивает NULL всем ее атрибутам;
чтобы сравнить две записи на равенство или неравенство нужно последовательно попарно сравнить значения всех атрибутов.
Так как записи PL/SQL похожи на строки таблиц, то особенно выпукло преимущества их использования для обеспечения компактности и расширяемости кода проявляются при выполнении предложений SQL в PL/SQL. Одна строка таблицыодна запись PL/SQL. Строки таблиц «живут» в базе данных, записи PL/SQL «живут» в программах. Строку таблицы можно одной командой считать в запись PL/SQL, запись PL/SQL можно одной командой вставить как строку таблицы, то есть обеспечивается движение данных в обоих направлениях. В PL/SQL также есть специальные языковые конструкции, которые позволяют перемещать между базой данных и программой PL/SQL не отдельные записи PL/SQL и строки таблиц, а их множества. И все это делается очень компактноодной-двумя строками кода PL/SQL.
Объявление переменных с привязкой
Так как язык PL/SQL предназначен для обработки данных, которые находятся в таблицах базы данных Oracle, то в нем предусмотрена возможность объявления переменных с привязкой к схемам этих таблиц. Например, если какая-то переменная используется для считывания в нее значений столбца surname таблицы person, то логично было бы указать при объявлении этой переменной тип данных, совпадающий с типом данных столбца.
Существует два вида привязки переменных:
скалярная привязка (c помощью атрибута %TYPE переменная объявляется с типом данных указанного столбца таблицы);
привязка к записи (с помощью атрибута %ROWTYPE объявляется переменная-запись PL/SQL с атрибутами по числу столбцов указанной таблицы или курсора).
Рассмотрим пример. Пусть в базе данных имеется таблица tab1 со столбцами at1 типа DATE и at2 типа VARCHAR2(20). Тогда в коде PL/SQL можно объявить переменные следующим образом:
l_tab1 tab1%ROWTYPE;
l_at1 tab1.at1%TYPE;
Переменная l_tab1 будет являться записью PL/SQL с двумя атрибутами at1, at2, типы данных которых будут такими же, как типы данных столбцов at1, at2 таблицы tab1, то есть DATE и VARCHAR2(20) соответственно. Переменная l_at1 будет иметь тип данных, такой же, как у столбца at1, то есть date.
Преимущества объявления переменных с привязкой:
автоматически выполняется синхронизация со схемами таблиц;
компактный расширяемый код для считывания строк результирующих выборок SQL-запросов без перечисления столбцов.
Автоматическая синхронизация объявлений переменных в программах PL/SQL и схем таблиц базы данных делает программы PL/SQL устойчивыми к возможным в будущем изменениям, таким как добавление, удаление или переименование столбцов таблиц, изменениям их типов данных. На практике такие изменения схем таблиц происходят довольно часто.
Приведем конкретный пример. В базе данных CRM-системы была таблица clients, в которой имелся столбец inn. На момент разработки системы клиентами могли быть только юридические лица, у которых длина ИНН составляет 10 символов. Со временем компания стала обслуживать и физических лиц, у которых длина ИНН 12 символов. Администратор базы данных изменил тип данных столбца inn таблицы clients с VARCHAR2(10) на VARCHAR2(12) и в таблице стал появляться строки с длинными ИНН. Так как в коде PL/SQL все переменные для работы с ИНН были объявлены как VARCHAR2(10), то при считывании из базы данных ИНН физических лиц в программах PL/SQL стали происходить ошибки. Если бы переменные для ИНН в свое время были объявлены с привязкой к столбцу inn с помощью атрибута %TYPE, то они автоматически «расширились» бы сами и ошибок на стадии выполнения не происходило бы.
Без перечисления столбцов результирующих выборок SQL-запросов пишется очень компактный код вида
l_person person%ROWTYPE;
SELECT * INTO l_person FROM person WHERE id=13243297;
print(l_person);
SQL-запрос выбирает все столбцы таблицы person, и у объявленной с помощью %ROWTYPE переменной l_person будет ровно столько же атрибутов, сколько столбцов у таблицы person, с такими же именами и типами данных, в том же порядке следования. Значения всех столбцов считываемой строки таблицы присвоятся соответствующим атрибутам записи PL/SQL. Если в таблице person в будущем появится новый столбец, он автоматически «подхватится» и SQL-запросом (SELECT *) и объявлением переменной l_person в программе PL/SQL. Никаких изменений в код вносить не потребуется, автоматическая перекомпиляция программы произойдет при первом обращении к ней.
Объявлять переменные как записи PL/SQL с помощью атрибута %ROWTYPE можно не только на основе какой-то одной таблицы, но и на основе столбцов результирующих выборок произвольных SQL-запросов. Для этого записи PL/SQL объявляются на основе явных курсоров, рассматриваемых далее.