Иван Задворьев - Язык PL/SQL

присвоение записи 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 объявляются на основе явных курсоров, рассматриваемых далее.

В PL/SQL, как и в большинстве процедурных языков программирования, наименьшей единицей группировки исходного кода является блок. Он представляет собой фрагмент кода, определяющий границы выполнения кода и области видимости для объявлений. Блоки могут вкладываться друг в друга.

Разделы блока PL/SQL

Блок PL/SQL состоит из четырех разделов:

раздел заголовка;

раздел объявлений;

исполняемый раздел;

раздел обработки исключений.

Разделов заголовка, объявлений и обработки исключений в блоке может не быть, обязательным является только исполняемый раздел.

Синтаксически блок PL/SQL выглядит следующим образом:

раздел заголовка

DECLARE

раздел объявлений

BEGIN

исполняемый раздел

EXCEPTION

раздел обработки исключений

END;

В разделе заголовка указываются:

тип блока (процедура, функция);

имя блока (имя процедуры, функции);

имена параметров, их типы данных и режимы передачи значений.

В разделе объявлений объявляются пользовательские типы данных, переменные и константы, которые потом используются в исполняемом разделе и разделе обработки исключений. В исполняемом разделе реализуется собственно логика программы. В вырожденном случае там может присутствовать только одна «пустая» команда NULL. Обработка исключений рассматривается далее в отдельном параграфе.

Ключевые слова BEGIN и END в языке PL/SQL являются операторными скобками, подобными символам { и } в других языках программирования и отмечают начало исполняемого раздела и конец блока. Каждая команда в PL/SQL должна завершаться точкой с запятой (символом ; ).

Виды блоков PL/SQL

В PL/SQL есть два вида блоков:

именованные блоки (с разделом заголовка);

анонимные блоки (без раздела заголовка).

Именованные блоки в свою очередь тоже бывают двух видов:

именованные блоки хранимых в базе данных программ (процедур, функций, пакетов и триггеров);

именованные блоки в разделах объявлений других блоков (анонимных или именованных).