Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1448

6.1. хранимые процедуры или функции

Хранимые процедуры (в некоторых СУБД функции) – это скомпилирован- ный набор SQL-предложений, сохраненный в базе данных как именованный объект и выполняющийся как единый фрагмент кода. Хранимые процедуры мо- гут принимать и возвращать параметры. Когда пользователь создает хранимую процедуру, сервер компилирует ее и помещает в разделяемый кэш, после чего скомпилированный код может быть применен несколькими пользователями. Когда приложение использует хранимую процедуру, оно передает ей па- раметры, если таковые ей потребуются, и сервер выполняет процедуру без пе- рекомпиляции. Хранимая процедура позволяет повысить производительность приложений.

Во-первых, по сравнению с обычными SQL-запросами, посылаемыми из клиентского приложения, они требуют меньше времени для подготовки к вы-

полнению, поскольку они скомпилированы и сохранены.

Во-вторых, сетевой трафик в этом случае меньше, чем в случае передачи

SQL-запроса, т.к. по сети передается меньшее количество данных.

Хранимые процедуры автоматически перекомпилируются, если с объекта-

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

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

В программах PL/pgSQL могут использоваться все типы данных, операторы и функции PostgreSQL. SQL в название PL/pgSQL указывает на то, что програм- мист может напрямую использовать команды языка SQL в своих программах. Использование SQL в коде PL/pgSQL расширяет возможности, а также повышает гибкость и быстродействие программ. Несколько команд SQL в программном

блоке PL/pgSQL выполняются за одну операцию вместо обычной обработки каж-

дой команды.

6.1.1.  С ТРУКТУРА  ЯЗЫКА

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

Регистр символов в именах функций PL/pgSQL не учитывается. В ключевых словах и идентификаторах допускается использование произвольных комбина-

ций символов верхнего и нижнего регистров. Также обратите внимание на час-

тое удвоение апострофов во многих местах этой главы  всюду, где обычно ис- пользуются одиночные апострофы. Удвоение экранирует апострофы в опреде- лениях функций, поскольку определение функции в действительности пред- ставляет собой большую строковую константу в команде CREATE FUNCTION.

В  этом  разделе рассматривается блочная  структура программ PL/pgSQL,

комментарии, структура выражений PL/pgSQL и использование команд.

Бло к и

Программы PL/pgSQL состоят из  блоков. Такой метод организации про-

граммного кода обычно называется блочной структурой.

Команда CREATE FUNCTION. Функция (или хранимая процедура) вводится с помощью команды SQL CREATE FUNCTION, которая используются для определения

функций PL/pgSQL в базах данных PostgreSQL. Команда CREATE FUNCTION опре-

деляет имя функции, типы ее аргументов и возвращаемого значения.

Блок DECLARE. Основной блок функции начинается с секции объявлений. Все переменные объявляются (а также могут инициализироваться значениями по умолчанию) в секции объявлений программного блока. В объявлении указы- вается имя и тип переменной. Секция объявлений обозначается ключевым сло- вом DECLARE, а каждое объявление завершается символом точки с запятой (;).

Основной программный блок. После объявления переменных следует ключевое слово BEGIN, обозначающее начало основного программного блока. За

ключевым словом  BEGIN находятся команды, входящие в  блок.  Конец  про-

граммного блока обозначается ключевым словом END.

Возвращаемое значение. Основной блок функции PL/pgSQL должен вернуть значение заданного типа, а все вложенные блоки (блоки, начинающиеся внутри

других блоков) должны быть завершены до достижения ключевого слова END.

Структура программного блока PL/pgSQL приведена в листинге 6.1.

Листинг 6.1. Структура программного блока PL/pgSQL

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS ' DECLARE

объявление: [...]

BEGIN команда: [...]

. . .

END;

' LANGUAGE 'plpgsql':

Программный блок PL/pgSQL может содержать неограниченное количество вложенных блоков, которые читаются и интерпретируются по тем же правилам, что и обычные блоки. В свою очередь, они могут содержать свои вложенные блоки.

Вложенные блоки упрощают структуру кода в больших функциях PL/pgSQL.

Структура вложенных блоков не отличается от структуры обычных блоков: они также начинаются с ключевого слова DECLARE, за которым следует ключевое слово BEGIN и последовательность команд, а затем ключевое слово END.

К о мме нта р ии

В PL/pgSQL поддерживаются два вида комментариев, у которых имеются аналоги в других языках программирования: однострочные и блочные (много- строчные) комментарии.

Однострочные комментарии начинаются с двух дефисов (‐‐) и не имеют специального завершителя. Модуль лексического разбоpa интерпретирует все символы, следующие после двух дефисов, как часть комментария. Пример ис-

пользования однострочных комментариев приведен в листинге 6.2.

Листинг 6.2. Однострочный комментарий

‐‐ Это будет интерпретировано как однострочный комметарий.

Блочные комментарии знакомы каждому, кто когда-либо программировал на других языках. Блочный комментарий начинается с последовательности символов /* и завершается последовательностью */. Они могут распростра- няться на несколько строк, при этом весь текст между начальной и завершаю- щей парой /* и */ считается комментарием. Пример блочного комментария приведен в листинге 6.3.

Листинг 6.3. Блочный комментарий

/*

* Здесь размещен

* блочный комментарий. */

ПРИМЕЧАНИЕ Хотя блочные комментарии могут содержать вложенные однострочные комментарии, вложение блочных комментариев в другие блочные комментарии не допускается.

6.1.2.  К ОМА Н Д Ы  И  ВЫРАЖЕНИЯ

Программы PL/pgSQL, как и в большинстве языков программирования, со- стоят из команд и выражений. Вероятно, вам довольно часто придется пользо- ваться выражениями, потому что они крайне важны для некоторых типов ма- нипуляций с данными. Общие концепции команд и выражений одинаковы (или, по крайней мере, очень похожи) во всех языках. Если вы прежде работали с другими языками программирования, то наверняка знакомы с этими концеп- циями.

Ко манд ы

Команда выполняет некоторое действие в коде PL/pgSQL  например, при- сваивает значение переменной или выполняет запрос. Последовательность ко- манд в программных блоках PL/pgSQL определяет порядок выполнения действий в этом блоке. Большая часть команд обычно размещается в основной части блока, находящейся между ключевыми словами BEGIN и END. Некоторые коман- ды также могут присутствовать в секции объявлений (после ключевого слова DECLARE), но они всего лишь объявляют и/или инициализируют переменные, используемые в программном блоке.

Каждая команда завершается символом точки с запятой (;). В этом про-

слеживается сходство с языком SQL, в котором команды завершаются этим же символом. Почти вся оставшаяся часть этой главы посвящена типам команд, их использованию и основным задачам, решаемым при помощи команд в PL/pgSQL.

Выр а жения

Выражения представляют собой условную запись последовательности опе- раций, результат которой принадлежит одному из базовых типов данных Post‐ greSQL. В листинге 6.4 приведена простая функция PL/pgSQL, возвращающая ре- зультат простого выражения, а в листинге 6.5 продемонстрирован результат вызова этой функции в psql.

Листинг 6.4. Использование выражений

CREATE FUNCTION a_function () RETURNS int4 AS ' DECLARE

an_integer int4; BEGIN

an_integer := 10 * 10;

return an_integer;

END;

' LANGUAGE 'plpgsql';

Листинг 6.5. Результат вызова функции a_function()

booktown=# SELECT a_function() AS output;

output

100

(1 row)

6.1.3.  П ЕРЕМЕННЫЕ

Переменные используются в программах PL/pgSQL для хранения изменяе-

мых данных заранее определенного типа.

Типы  данных

Переменные PL/pgSQL могут относиться к любому из стандартных типов дан- ных SQL (например, integer или char). Помимо типов данных SQL, в PL/pgSQL так- же предусмотрен дополнительный тип RECORD, предназначенный для хранения за- писей без указания полей  эта информация передается при сохранении данных в переменной. Дополнительная информация о типе данных RECORD приводится ни-

же. Типы данных SQL были описаны в разделе 3.1.5. Самые распространенные ти-

пы PL/pgSQL: Boolean, text, char, integer, double precision, date, time.

Об ъяв л ени е  переме нных

Все переменные программного блока должны быть предварительно объяв- лены с ключевым словом DECLARE. Если переменная не инициализируется при объявлении, по умолчанию ей присваивается псевдозначение SQL NULL.

ПРИМЕЧАНИЕ. Как будет показано в разделе «Передача управления», в одной из команд  цикле FOR  предусмотрена возможность инициализации управляющей переменной. Переменную цикла FOR не нужно заранее объявлять в секции DECLARE того блока, в котором находится цикл. Таким образом, переменные цикла FOR составляют единственное исключение из правила, согласно которому все переменные должны объявляться в начале соответствующего блока.

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

имя_переменной тип_данных [ := значение ];

Таким образом, объявление состоит из имени и типа переменной (следую-

щих именно в этом порядке) и завершается символом точки с запятой (;).

В листинге 6.6 приведены объявления переменных типов integer, varchar

(число в круглых скобках обозначает максимальную длину строки в символах)

и float.

Листинг 6.6. Объявление переменных

CREATE FUNCTION identifier (arguments) RETURNS type AS ' DECLARE

‐‐ Объявить числовую переменную типа integer.

subject_id integer;

‐‐ Объявить строковую переменную переменной длины.

book_title varchar(10);

‐‐ Объявить вещественную числовую переменную.

book price float; BEGIN

команды; END;

' LANGUAGE 'plpgsql';

Объявление переменной также может содержать дополнительные модифи- каторы. Ключевое слово CONSTANT указывает на то, что вместо переменной оп- ределяется константа. Пример определения константы рассматриваются в лис- тинге 6.7 в этом разделе.

Ключевые слова NOT NULL означают, что переменной не может присваивать- ся псевдозначение NULL. Если переменной, объявленной с модификатором NOT NULL, в программном блоке присваивается псевдозначение NULL, происходит

ошибка времени выполнения. Поскольку при объявлении без инициализации всем переменным автоматически присваивается псевдозиачение NULL, перемен- ные с модификатором NOT NULL обязательно должны инициализироваться.

Ключевое слово DEFAULT определяет значение по умолчанию для перемен-

ной. Вместо него можно воспользоваться оператором (:=), эффект будет тем же.

Ниже приведен расширенный синтаксис объявления переменной:

имя_переменной [ CONSTANT ] тип_данных [ NOT NULL ] [ { DEFAULT | := } значение ];

В листинге 6.7 приведены примеры объявлений целочисленной константы, равной 5, переменной со значением 10, которой не может быть присвоено псев- дозначение NULL, и символьной переменной, содержащей символ «а».

Листинг 6.7. Объявления переменных

CREATE FUNCTION example_function О RETURNS texi AS ' DECLARE

‐‐ Объявление целочисленной константы,

‐‐ инициализированной значением 5.

five CONSTANT integer := 5;

‐‐ Объявление целочисленной переменной,

‐‐ инициализированной значением 10.

‐‐ Переменной не может присваиваться NULL.

ten integer NOT NULL := 10;

‐‐ Объявление символьной переменной,

‐‐ инициализированной значением "а".

letter char DEFAULT ''а''; BEGIN

‐‐ Функция возвращает символ и прекращает работу.

return letter; END;

' LANGUAGE 'plpgsql':

П р ис ваива н ие

Присваивание в  PL/pgSQL выполняется оператором присваивания (:=) в форме левая_ переменная := правая_переменная.

Команда  присваивает  левой  переменной  значение  правой  переменной.

Также допускается запись вида левая_переменная := выражение.

В этом случае левой переменной присваивается результат выражения, рас-

положенного справа от оператора присваивания.

Значения по умолчанию также могут присваиваться переменным в секции объявлений программных блоков PL/pgSQL. Инициализация переменной произ- водится оператором присваивания (:=) в одной строке с объявлением перемен- ной. Эта тема подробно рассматривается ниже, а в листинге 6.8 приведен не- большой пример.

Листинг 6.8. Инициализация переменной

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS ' DECLARE

a_integer int4 := 10; BEGIN

команда; END;

' LANGUAGE 'plpgsql';

Возможен и другой вариант  присваивание переменной результата запроса командой SELECT INTO. He путайте этот вариант использования команды SELECT INTO с командой SQL SELECT INTO, которая заносит результаты запроса в новую таблицу.

Команда SELECT INTO в основном требуется для сохранения данных записей в переменных, объявленных с типами ROWTYPE и RECORD. Чтобы команда SELECT INTO могла использоваться с обычной переменной, тип этой переменной дол- жен соответствовать типу поля, упоминаемому в команде SQL SELECT.

Синтаксис команды SELECT INTO:

CREATE FUNCTION идентификатор (аргументы) RETURNS тип AS ' DECLARE

команда; BEGIN

SELECT INTO переменная [. ...] поле [, ...] секции_select; END;

' LANGUAGE 'plpgsql':

В этом описании переменная  имя переменной, участвующей в присваи- вании, a ceкции select  любые поддерживаемые секции команды SQL SELECT, обычно следующие за списком целевых полей в команде SELECT.

В листинге 6.9 приведена простая функция, в которой используется коман-

да SELECT INTO. Ключевое слово ALIAS описано в подразделе 0 этого раздела. Примеры выполнения команды SELECT INTO для  переменных типа  RECORD и ROWTYPE приведены в разделе 6.1.7.

Листинг 6.9. Использование команды SELECT INTO

CREATE FUNCTION get_customer_id (text.text) RETURNS integer AS ' DECLARE

‐‐ Объявление псевдонимов для аргументов.

l_name ALIAS FOR $1;

f_name ALIAS FOR $2;

‐‐ Объявление переменной для хранения кода клиента.

customer_id integer; BEGIN

‐‐ Получение кода клиента, имя и фамилия которого

‐‐ совпадают с переданными значениями.

SELECT INTO customer_id id FROM customers

WHERE last_name = l_name AND first_name = f_name;

‐‐ Вернуть код.

RETURN customer_id; END;

' LANGUAGE 'plpgsql';

В листинге 6.10 показан результат вызова функции get_customer_id() с ар- гументами Jackson и Annie. Возвращенное число равно коду клиента «Annie Jackson» в таблице customers.

Листинг 6.10. Вызов функции get_customer_id()

booktown=# SELECT get_customer_id ('Jackson','Annie');

get_customer_id

107

(1 row)

Если требуется присвоить несколько значений нескольким переменным, в команду включаются две группы, разделенные запятыми и отделенные друг от друга пробелом. В первой группе перечисляются имена переменных, а во вто- рой  имена полей.

Функция, приведенная в листинге 6.11, решает обратную задачу по сравне- нию с функцией get_customer_id() из листинга 6.9  она возвращает имя и фа- милию клиента по заданному коду.

Листинг 6.11. Использование команды SELECT INTO с несколькими полями

CREATE FUNCTION get_customer_name (integer) RETURNS text AS ' DECLARE

‐‐ Объявление псевдонимов для аргументов,

customer_id ALIAS FOR $1;

‐‐ Объявление переменных для хранения компонентов

‐‐ полного имени клиента. customer_fname text; customer_lname text;

BEGIN

‐‐ Получение имени и фамилии клиента, код которого

‐‐ совпадает с переданным значением. SELECT INTO customer_fname, customer_lname first_name, last_name

FROM customers WHERE id = customer_id;

‐‐ Вернуть полное имя.

RETURN customer_fname | '' '' | customer_lname; END;

' LANGUAGE 'plpgsql1;

В листинге 6.12 показан результат вызова функции get_customer_name() с аргументом 107.

Листинг 6.12. Вызов функции get_customer_name()

booktown=# SELECT get_customer_name(107);

get_customer_name

Annie Jackson

(1 row)

Чтобы узнать, успешно ли были присвоены значения переменным коман- дой SELECT INTO, воспользуйтесь специальной логической переменной FOUND. Кроме того, можно проверить значение заданной переменной ключевыми сло- вами ISNULL или IS NULL (в большинстве случаев положительный результат оз- начает, что команда SELECT INTO завершилась неудачно!).

Ключевые слова FOUND, IS NULL и ISNULL следует использовать в условных командах (IF/THEN). Условные команды PL/pgSQL описаны в разделе 6.1.7.

В листинге 6.13 приведен простейший пример использования логической переменной FOUND в функции get_customer_id().

Листинг 6.13. Использование логической переменной FOUND в функции

get_customer_id()

[...]

SELECT INTO customer_id id FROM customers

WHERE last_name = l_name AND first_name = f_name;

‐‐ Если совпадение не найдено, вернуть ‐1.

‐‐ Другая функция, в которой вызывается

get_customer_id();

‐‐ может интерпретировать ‐1 как признак ошибки.

IF NOT FOUND THEN

return ‐1;

END IF;

[...]

В листинге 6.14 показано, что теперь функция get_customer_id() при пере-

даче имени несуществующего клиента возвращает ‐1.

Листинг 6.14. Вызов нового варианта функции get_customer_id()

booktown=# SELECT get_customer_id('Schmoe','Joe');

get_customer_id

‐1

(1 row)

Аргу менты

При вызове функции PL/pgSQL могут получать аргументы различных типов. В аргументах пользователь передает исходные данные, необходимые для работы функции. Аргументы делают функции PL/pgSQL более универсальными и значи- тельно расширяют область их возможного применения. Список аргументов при- водится после имени функции в круглых скобках и разделяется запятыми.

Количество и типы аргументов должны соответствовать первоначальному определению функции.

В листинге 6.15 приведены примеры двух вызовов функции из клиента psql.

Листинг 6.15. Примеры вызовов функций

booktown=# SELECT get_author('John');

get_author

John Worsley

(1 row)

booktown=# SELECT get_author(1111); get_author

Ariel Denham

(1 row)

ПРИМЕЧАНИЕ. Функции get_author(text) и get_author(integer) будут рассмотрены позднее в этой главе.

Аргументы, полученные функцией, поочередно присваиваются идентифи- каторам, состоящим из знака доллара ($) и порядкового номера. Первому ар- гументу соответствует идентификатор $1, второму -- $2 и т. д.

Максимальное количество аргументов равно 16, поэтому идентификаторы аргументов лежат в интервале от $1 до $16. В листинге 6.16 приведен пример функции, которая удваивает свой целочисленный аргумент.

Листинг 6.16. Непосредственное использование аргументов в переменных

CREATE FUNCTION double_price (float) RETURNS float AS ' DECLARE

BEGIN

‐‐ Вернуть значение аргумента, умноженное на 2.

return $1 * 2; END;

' LANGUAGE 'plpgsql';

Если функция имеет большое количество аргументов, в обозначениях вида

«$+номер» легко запутаться. Чтобы программисту было проще отличить одни аргумент от другого (или если он хочет присвоить переменной аргумента более

содержательное  имя),  в  PL/pgSQL предусмотрена  возможность  определения псевдонимов переменных.

Псевдоним создается при помощи ключевого слова ALIAS и представляет

собой альтернативный идентификатор для ссылки на аргумент. Перед исполь- зованием все псевдонимы (как и обычные переменные) должны быть объявле- ны в секции объявлений блока. В листинге 6.17 показан синтаксис применения ключевого слова ALIAS.

Листинг 6.17. Синтаксис использования ключевого слова ALIAS

CREATE FUNCTION функция (аргументы) RETURNS тип AS ' DECLARE

идентификатор ALIAS FOR $1;

идентификатор ALIAS FOR $2;

BEGIN

END;

' LANGUAGE 'plpgsql';

В листинге 6.18 приведен простой пример, демонстрирующий применение псевдонимов в функциях PL/pgSQL. Функция triple_price получает веществен- ное число, умножает его на три и возвращает результат.

Листинг 6.18. Псевдонимы PL/pgSQL

CREATE FUNCTION triple_price (float) RETURNS float AS ' DECLARE

‐‐ Переменная input_price объявляется как псевдоним

‐‐ для переменной аргумента, обычно обозначаемой

‐‐ идентификатором $1.

input_price ALIAS FOR $1; BEGIN

‐‐ Вернуть аргумент, умноженный на три.

RETURN input_price * 3; END;

' LANGUAGE 'plpgsql';

Если теперь вызвать функцию triple_price при выполнении команды SQL SELECT в клиенте psql, будет получен результат, показанный в листинге 6.19.

Листинг 6.19. Результат вызова функции triple_price()

booktown=# SELECT triple_price(12.50);

triple_price

37.5

(1 row)

6.1.4.  В ОЗВРА Щ ЕНИЕ  ПЕРЕМЕННЫХ

Тип величины, возвращаемой функцией PL/pgSQL, должен соответствовать типу возвращаемого значения, указанному при создании функции командой CREATE FUNCTION. Значение возвращается командой RETURN. Команда RETURN на- ходится в конце функции, но она также часто встречается в командах IF или других командах, осуществляющих передачу управления в программе. Даже если команда RETURN вызывается в одной из этих команд, функция вес равно должна заканчиваться командой RETURN (даже если управление никогда не бу- дет передано этой завершающей команде).

Синтаксис команды RETURN приведен в листинге 6.20.

Листинг 6.20. Синтаксис команды RETURN

CREATE FUNCTION функция (аргументы) RETURNS тип AS ' DECLARE

объявление; [...]

BEGIN команда; [...]

RETURN { переменная значение } END;

' LANGUAGE 'plpgsql' ;

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

PL/pgSQL, встречающейся в этой главе.

6.1.5.  А ТРИБУТ Ы

Для упрощения работы с объектами базы данных в PL/pgSQL существуют атрибуты переменных  \%TYPE и \%R0WTYPE. Атрибуты требуются для объявления переменной, тип которой совпадает с типом объекта базы данных (атрибут

\%TYPE) или структурой записи (атрибут \%ROWTYPE). Переменные объявляются с атрибутами в том случае, если они будут использоваться в программном блоке

для хранения значений, полученных от объекта базы данных. Таким образом,

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

ски переключится на новый тип данных, причем это не потребует дополни-

тельных усилий со стороны программиста.

Атрибу т  \%T YPE

Атрибут \%TYPE используется при объявлении переменных с типом данных, совпадающих с типом некоторого объекта базы данных (чаще всего поля). Син- таксис объявления переменной с атрибутом \%TYPE приведен в листинге 6.21.

Листинг 6.21. Объявление переменной с атрибутом \%TYPE

переменная таблица.поле\%TYPE

В листинге 6.22 приведена функция, использующая атрибут \%TYPE для хра- нения фамилии автора. В ней задействован оператор конкатенации (||), опи- санный ниже. Команда SELECT INTO рассматривалась ранее в этой главе.

В листинге 6.22 следует обратить особое внимание на атрибут \%TYPE. Фак- тически мы объявляем переменную, тип которой совпадает с типом поля таб- лицы authors. Затем команда SELECT находит запись, у которой поле first_name совпадает с аргументом, переданным при вызове функции. Команда SELECT чи- тает значение поля last_name этой записи и сохраняет его в переменной l_паmе. Пример вызова функции с передачей аргумента приведен ниже, в листинге

6.23. Кроме того, передача аргумента пользователем встречается во многих примерах этой главы.

Листинг 6.22. Использование атрибута \%TYPE

CREATE FUNCTION get_author (text) RETURNS text AS ' DECLARE

‐‐ Объявление псевдонима для аргумента функции.

‐‐ в котором должно передаваться имя автора,

f_name ALIAS FOR $1;

‐‐ Объявление переменной, тип которой совпадает

‐‐ с типом поля last_name таблицы authors.

l_name authors.last_name l TYPE; BEGIN

‐‐ Найти в таблице authors фамилию автора.

‐‐ имя которого совпадает с переданным аргументом.

‐‐ и присвоить ее переменной l_name.

SELECT INTO l_name last_name

FROM authors

WHERE first_name = f_name;

‐ Вернуть имя и фамилию, разделенные пробелом,

return f_name || '' '' || l_name; END;

' LANGUAGE 'plpgsql';

В листинге 6.23 приведен пример вызова функции get_author().

Листинг 6.23. Результат вызова функции get_author()

booktown=# SELECT get_author('Andrew');

get_author

Andrew Brook Ins

(1 row)

Атрибу т  \%ROWTYPE

Атрибут \%ROWTYPE используется в PL/pgSQL для переменной-записи, имею-

щей одинаковую структуру с записями заданной таблицы. Не путайте атрибут

\%ROWTYPE с типом данных RECORD  переменная с атрибутом ROWTYPE точно вос- производит структуру записи  конкретной таблицы, а  переменная RECORD не структурирована и ей можно присвоить запись любой таблицы.

В листинге 6.24 приведена перегруженная версия функции get_author()

(см. листинг 6.22). Она делает то же, что и прототип, но получает аргумент типа

integer вместо text и ищет автора, сравнивая код с переданным аргументом.

Обратите внимание: в реализации функции используется переменная, объ-

явленная с атрибутом \%ROWTYPE. Возможно, в данном случае применение \%ROW‐

TYPE только напрасно усложняет очень простую задачу, но по мере изучения

PL/pgSQL важность атрибута \%ROWTYPE становится все более очевидной.

Точка (.) после имени переменной found_author в листинге 6.24 использу-

ется для ссылки на имя поля, входящего в структуру found_author.

Листинг 6.24. Использование атрибута \%ROWTYPE

CREATE FUNCTION get_author (integer) RETURNS text AS ' DECLARE

‐‐ Объявление псевдонима для аргумента функции.

‐‐ в котором должен передаваться код автора,

author_id ALIAS FOR $1;

‐‐ Объявление переменной, структура которой

‐‐ совпадает со структурой таблицы authors,

found author authors ROWTYPE; BEGIN

‐‐ Найти в таблице authors фамилию автора,

‐‐ код которого совпадает с переданным аргументом.

SELECT INTO found_author * FROM authors WHERE id = author_id;

‐‐ Вернуть имя и фамилию, разделенные пробелом.

RETURN found_author.first_name || " " || found_author.last_name; END;

' LANGUAGE 'plpgsql':

Обратите внимание на звездочку (*) в списке полей команды SELECT. По- скольку переменная found_author объявлялась с атрибутом \%ROWTYPE для табли- цы authors, она имеет такую же структуру, как и записи таблицы authors. Та- ким образом, конструкция SELECT * заполняет переменную found_author значе- ниями полей найденной записи. Пример вызова новой версии get_author() приведен в листинге 6.25.

Листинг 6.25. Вызов новой версии функции get_author()

booktown=# SELECT get_author(1212);

get_author

John Worsley

(1 row)

6.1.6.  К ОН КАТ Е НАЦ ИЯ

Конкатенацией называется процесс построения новой строки посредством объединения двух (и более) строк. Конкатенация принадлежит к числу стан- дартных операций PostgreSQL и поэтому может напрямую использоваться с пе- ременными в функциях PL/pgSQL. Это незаменимый инструмент форматирова- ния при работе с несколькими переменными, содержащими символьные дан- ные.

Конкатенация используется только со строками. Оператор конкатенации (|) ставится между объединяемыми компонентами (литералами или строковы- ми переменными).

В листинге 6.26 операция конкатенации создает строку, возвращаемую функцией.

Листинг 6.26. Возвращение результата конкатенации

CREATE FUNCTION compound_word (text, text) RETURNS text AS ' DECLARE

‐‐ Объявление псевдонимов для аргументов функций.

word1 ALIAS FOR $1;

word2 ALIAS FOR $2;

BEGIN

‐‐ Вернуть объединение двух слов.

RETURN word1 || word2; END;

' LANGUAGE 'plpgsql' ;

Если передать функции аргументы «break» и «fast», функция вернет объе-

диненную строку «breakfast»:

booktown=# SELECT compound_word ('break', 'fast');

compound_word

breakfast

(1 row)

6.1.7.  П ЕРЕДАЧА  УПРАВЛЕНИЯ

Команды передачи управления существуют практически во всех современ- ных языках программирования, и PL/pgSQL не является исключением. С техни- ческой точки зрения сам вызов функции можно рассматривать как передачу управления последовательности команд PL/pgSQL. Тем не менее существуют и другие, более совершенные средства, определяющие последовательность вы- полнения команд PL/pgSQL. Речь идет об условных командах IF/THEN и циклах.

Усло вн ые  ко манды

Условная команда указывает на то, что некоторое действие (или последо- вательность действий) выполняется в зависимости от результатов проверки за- данного логического условия. Определение выглядит запутанно, но на самом деле условные команды весьма просты. В неформальной формулировке услов- ная команда означает следующее: «если условие истинно, выполнить такое‐то действие».

Команда IF/THEN

В соответствии с данным определением команда IF/THEN задает команду (или блок команд), выполняемых в случае истинности некоторого условия. Синтаксис команды IF/THEN показан в листинге 6.27.

Листинг 6.27. Синтаксис команды IF/THEN

CREATE FUNCTION функция (аргументы) RETURNS тип AS ' DECLARE

объявления

BEGIN

IF условие THEN

команда;

[...]

END IF;

END;

' LANGUAGE 'plpgsql';

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

Следовательно, функция stock_amount() предназначена для вызова из дру-

гих функций, а не прямого вызова из клиентской программы, поскольку боль-

шинству пользователей коды книг неизвестны.

Сначала мы читаем код ISBN командой SELECT INTO. Если команда SELECT INTO не смогла найти код ISBN по заданному коду книги и номеру издания, функция stock_amount() возвращает ‐1.

Функция, вызвавшая stock_amount(), интерпретирует это значение как признак ошибки. Если код ISBN найден, то другая команда SELECT INTO получает количество экземпляров книги на складе и возвращает полученную величину.

На этом работа функции завершается.

Листинг 6.28. Использование команды IF/THEN

CREATE FUNCTION stock_amount (integer, integer) RETURNS integer AS ' DECLARE

‐‐ Объявление псевдонимов для аргументов функции.

b_id ALIAS FOR $1;

b_edition ALIAS FOR $2;

‐‐ Объявление переменной для кода ISBN.

b_isbn text;

‐‐ Объявление переменной для количества экземпляров.

stock_amount integer;

BEGIN

‐‐ Команда SELECT INTO находит в таблице editions запись,

‐‐ у которой код книги и номер издания совпадают с аргументами

‐‐ функции. Код ISBN найденной записи присваивается переменной.

SELECT INTO b_isbn isbn FROM editions WHERE

book_id = b_id AND edition = b_edition;

‐‐ Проверить, не был ли полученный код ISBN равен NULL.

‐‐ Значение NULL говорит о том, что в базе данных

‐‐ не существует записи книги с кодом и номером издания,

‐‐ переданными в аргументах функции. Если запись не существует,

‐‐ функция возвращает ‐1 и завершает работу.

IF b_isbn IS NULL THEN RETURN ‐1;

END IF;

‐‐ Получить из таблицы stock количество экземпляров книги

‐‐ на складе и присвоить его переменной stock_amount.

SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn;

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

RETURN stock_amount; END;

' LANGUAGE 'plpgsql';

В листинге 6.29 показан результат вызова функции stock_amount() для ко-

да книги 7808 и издания 1.

Листинг 6.29. Результаты вызова функции stock_amount()

booktowrHf SELECT stock_amount(7808,1):

stock amount

22

(1 row)

Команда IF/THEN/ELSE

В команде IF/THEN/ELSE задаются два блока команд. Первый блок выполня- ется в том случае, если условие истинно, а второй  если оно ложно. Синтаксис команды IF/THEN/ELSE приведен в листинге 6.30.

Листинг 6.30. Синтаксис команды IF/THEN/ELSE

CREATE FUNCTION функция (аргументы) RETURNS тип AS ' DECLARE

объявления

BEGIN

IF условие THEN

команда;

[...]

ELSE

команда;

[...]

END IF;

END;

' LANGUAGE 'plpgsql';

Функция в листинге 6.31 делает практически то же, что и функция в лис- тинге 6.28: она также определяет код ISBN по коду книги и номеру издания, со- храняет его в переменной и получает количество экземпляров книги на складе.

Затем  команда  IF/THEN/ELSE проверяет, является  ли  количество книг  на складе положительной величиной. Если число положительно, функция возвра-

щает TRUE  признак наличия книг на складе. В противном случае функция воз-

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

Листинг 6.31. Команда IF/THEN/ELSE

CREATE FUNCTION in_stock (integer, integer) RETURNS boolean AS ' DECLARE

‐‐ Объявление псевдонимов для аргументов функции.

b_id ALIAS FOR $1;

b_edition ALIAS FOR $2;

‐‐ Объявление текстовой переменной для найденного кода ISBN.

b_isbn text;

‐‐ Объявление целочисленной переменной для количества экземпля‐

ров.

stock_amount integer;

BEGIN

‐‐ Команда SELECT INTO находит в таблице editions запись.

‐‐ у которой код книги и номер издания совпадают с аргументами

‐‐ функции. Код ISBN найденной записи присваивается переменной.

SELECT INTO b_isbn isbn FROM editions WHERE

book_id = b_id AND edition = b_edition;

‐‐ Проверить, не был ли полученный код ISBN равен NULL.

‐‐ Значение NULL говорит о тон. что в базе данных

‐‐ не существует записи книги с кодом и номером издания.

‐‐ переданными в аргументах функции. Если запись не существует.

‐‐ функция возвращает FALSE и завершает работу.

IF b_isbn IS NULL THEN RETURN FALSE;

END IF;

‐‐ Получить из таблицы stock количество экземпляров книги

‐‐ на складе и присвоить его переменной stock_amount.

SELECT INTO stock_amount stock FROM stock

WHERE isbn = b_isbn;

‐‐ Проверить, является ли количество книг на складе

‐‐ положительной величиной. Если количество положительно,

‐‐ функция возвращает TRUE, а если отрицательно

‐‐ или равно нулю ‐ FALSE.

IF stock_amount <= 0 THEN RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

END;

' LANGUAGE 'plpgsql':

В листинге 6.32 показан результат вызова in_stock() для кода книги 4513 и издания 2.

Листинг 6.32. Результат вызова функции in_stock()

booktown=# SELECT in_stock(4513,2);

in stock

t

(1 row)

Функция вернула значение TRUE  признак наличия книги на складе.

Команда IF/THEN/ELSE/IF

Команда  IF/THEN/ELSE/IF предназначена  для  последовательной  проверки нескольких условий. Сначала проверяется первое условие; если оно окажется равным FALSE, проверяется следующее условие и т. д. Последняя секция ELSE содержит команды, выполняемые в том случае, если пи одно из проверенных условий не было истинным. Синтаксис команды IF/THEN/ELSE/IF:

CREATE FUNCTION функция (аргументы) RETURNS тип AS ' DECLARE

объявление BEGIN IF условие THEN

команда; [...]

ELSE IF условие команда; [...]

END IF; END;

' LANGUAGE 'plpgsql' :

В листинге 6.33 приведен практический пример применения функции с ко- мандой IF/ THEN/ELSE/IF. Функция books_by_subject() сначала использует пе- реданный аргумент (тему книги) для выборки кода темы. Затем первая команда IF проверяет, не содержит ли переданный аргумент строку all.

Если  при  вызове был  передан аргумент all,  команда IF/THEN вызывает функцию extract_all_titles() и присваивает полученный список книг и тем

(возвращаемый в виде текстовой переменной) переменной found_text.

Если аргумент отличен от all, следующая команда ELSE IF проверяет, яв-

ляется ли код темы нулем или положительным числом. Если значение sub_id

больше либо равно нулю, выполняются команды, содержащиеся в теле конст- рукции ELSE IF  сначала вызывается функция extract_title(), которая воз- вращает список всех существующих книг по заданной теме, после чего тема возвращается вместе с полученным списком.

Затем другая команда ELSE IF сравнивает код темы с псевдозначением NULL. Если значение sub_id равно NULL, значит, переданная при вызове функции тема не встречается в базе данных booktown, а выполненная в самом начале команда SELECT INTO завершилась неудачей. В этом случае функция возвращает строку

«subject not found».

ПРИМЕЧАНИЕ Функции extract_all_titles() и extract_title(), используемые в листинге 11.40, бу-

дут рассмотрены ниже, когда речь пойдет о циклах.

Листинг 6.33. Команда IF/THEN/ELSE/IF

CREATE FUNCTION books_by_subject (text) RETURNS text AS ' DECLARE

‐‐ Объявление псевдонима для аргумента, содержащего либо

‐‐ строку all, либо тему.

sub_title ALIAS FOR $1;

‐‐ Объявление целочисленной переменной для хранения кода темы

‐‐ и текстовой переменной для хранения списка найденных книг.

‐‐ Текстовая переменная инициализируется пустой строкой.

sub_id integer;

found_text text;

BEGIN

‐‐ Получить код темы, описание которой передано в аргументе.

SELECT INTO sub_id id FROM subjects WHERE subject = sub_title;

‐‐ Проверить, запросил ли пользователь информацию обо всех темах

‐‐      (строка            all).       В          этом    случае вызвать           функцию

extract_all_titles() и вернуть полученную текстовую переменную.

IF sub_title = ''all'' THEN

found_text extract_all_titles(); RETURN found text;

‐‐ Если в аргументе НЕ БЫЛА передана строка "all", проверить,

‐‐ входит ли код темы в интервал допустимых значений.

‐‐ Если это так, вызвать функцию extract_title() с кодом темы

‐‐ и присвоить результат переменной found_text.

ELSE IF sub_id >= 0

THEN

found_text := extract_title(sub_id);

RETURN " " || sub_title || ": " | found_text;

‐‐ Если код темы равен NULL, вернуть сообщение о том. что

‐‐ заданная тема не найдена.

ELSE IF sub_id IS NULL THEN

RETURN "Subject not found."; END IF;

END IF; END IF;

RETURN "An error occurred."; END;

' LANGUAGE 'plpgsql';

В листинге 6.34 сначала приведен результат вызова функции books_by_subject() с аргументом аll (признак того, что пользователь хочет полу- чить список книг по всем темам). Затем следуют результаты, полученные при вы- зове функции с  аргументом Computers (получение списка книг, посвященных компьютерам).

Листинг 6.34. Результаты вызова функции books_by_subject()

booktown=# SELECT books_by_subject('al1');

books_by_subject

Arts:

Dynamic Anatomy

Business: Children's Books:

The Cat in the Hat

Bartholomew and the Oobleck

Franklin in the Dark

Goodnight Moon

[...]

Science:

Science Fiction: Dune

2001: A Space Odyssey

(1 row)

booktown=# SELECT books_by_subject('Computers');

books by_subject

Computers: Learning Python Perl Cookbook

Practical PostgreSQL Programming Python

(1 row)

Ци клы

Другую категорию команд, передающих управление внутри функций, со- ставляют циклы. В циклах используются разные виды итераций, предназначен- ные для решения разных задач. Итеративные вычисления значительно расши- ряют возможности функций PL/pgSQL.

В PL/pgSQL реализованы три типа циклов: простейший (безусловный) цикл,

цикл WHILE и цикл FOR. Вероятно, из этих трех циклов чаще всего применяется

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

Безусловный цикл

Ключевое слово LOOP начинает простейший безусловный цикл. Команды безусловного цикла выполняются до тех пор, пока не будет достигнуто ключе- вое слово EXIT. За ключевым словом EXIT может следовать секция WHEN с выра- жением, определяющим ус