Первая программа на PL/SQL

По давней традиции, восходящей к языку C, учебники по языкам программирования начинаются с программы «Hello, World!» и описания типов данных изучаемого языка. Не будем нарушать эту традицию, но внесем в нее одно изменение. Так как язык PL/SQL предназначен для работы с базами данных Oracle, то строку «Hello, World!» не будем задавать статически в исходном коде, а возьмем из таблицы базы данных.

CREATE TABLE hello_world_table(message VARCHAR2(30));

INSERT INTO hello_world_table VALUES('Hello, World!');

Выполним в SQL*Plus следующий код:

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 l_message VARCHAR2(30);

3 BEGIN

4 SELECT message INTO l_message FROM hello_world_table;

5 DBMS_OUTPUT.PUT_LINE(l_message);

6 END;

7 /

Hello, World!

PL/SQL procedure successfully completed.

В представленной выше программе PL/SQL с помощью команды SELECT INTO значение столбца message строки таблицы hello_world_table считывается из базы данных и присваивается локальной переменной l_message, значение которой затем выводится в окне SQL*Plus. Переменная l_message предварительно объявлена в разделе объявлений после ключевого слова DECLARE.

Экранный вывод в PL/SQL осуществляет процедура PUT_LINE встроенного пакета DBMS_OUTPUT, который есть во всех базах данных Oracle. Можно считать, что процедура DBMS_OUTPUT.PUT_LINE в языке PL/SQL – аналог процедуры printf в языке C.

Напомним о двух вещах, важных при работе с утилитой SQL*Plus:

для запуска в SQL*Plus программы PL/SQL на выполнение необходимо на новой строке напечатать символ / и нажать клавишу Enter на клавиатуре;

в SQL*Plus экранный вывод программ на PL/SQL включается командой SET SERVEROUTPUT ON (по умолчанию экранный вывод выключен).

Если не выполнить команду SET SERVEROUTPUT ON, то в консоли SQL*Plus ничего печататься не будет. В популярном GUI-клиенте Quest SQL Navigator экранный вывод PL/SQL тоже по умолчанию выключен и включается специальной кнопкой «Turn the server output», которая после нажатия должна остаться во «вдавленном» положении.

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

Напомним, что типом данных (data туре) называется именованное множество значений данных заданной структуры, удовлетворяющих специфицированным ограничениям целостности и допускающих выполнение над ними определенного, ассоциированного с этим множеством набора операций. Например, числа и даты можно складывать и вычитать, а строки и логические значения нельзя.

Язык PL/SQL относится к языкам со статической типизацией. Статической типизацией называется проверка типов данных во время компиляции программ. К числу языков программирования со статической типизацией относятся Pascal, Java, C/C++/C#. Языки с динамической типизацией (JavaScript, Python, Ruby) выполняют большинство проверок типов во время выполнения программ. Статическая типизация позволяет выявлять ошибки во время компиляции, что повышает надежность программ.

Виды типов данных

Так как язык PL/SQL является процедурным расширением языка SQL, то в PL/SQL есть все типы данных, которые имеются в диалекте Oracle SQL с некоторыми несущественными различиями. В дополнение к ним в PL/SQL есть и такие типы данных, которых нет в Oracle SQL.

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