Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.) Жанр: Информационные системы и технологии Просмотров: 1435 |
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
объявление: [...]
. . . 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. Использование выражений
an_integer int4; BEGIN an_integer := 10 * 10; return an_integer; END; ' LANGUAGE 'plpgsql';
Листинг 6.5. Результат вызова функции a_function()
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. Объявление переменных
‐‐ Объявить числовую переменную типа integer. subject_id integer; ‐‐ Объявить строковую переменную переменной длины. book_title varchar(10); ‐‐ Объявить вещественную числовую переменную.
команды; END; ' LANGUAGE 'plpgsql';
Объявление переменной также может содержать дополнительные модифи- каторы. Ключевое слово CONSTANT указывает на то, что вместо переменной оп- ределяется константа. Пример определения константы рассматриваются в лис- тинге 6.7 в этом разделе. Ключевые слова NOT NULL означают, что переменной не может присваивать- ся псевдозначение NULL. Если переменной, объявленной с модификатором NOT NULL, в программном блоке присваивается псевдозначение NULL, происходит ошибка времени выполнения. Поскольку при объявлении без инициализации всем переменным автоматически присваивается псевдозиачение NULL, перемен- ные с модификатором NOT NULL обязательно должны инициализироваться. Ключевое слово DEFAULT определяет значение по умолчанию для перемен- ной. Вместо него можно воспользоваться оператором (:=), эффект будет тем же. Ниже приведен расширенный синтаксис объявления переменной:
В листинге 6.7 приведены примеры объявлений целочисленной константы, равной 5, переменной со значением 10, которой не может быть присвоено псев- дозначение NULL, и символьной переменной, содержащей символ «а».
Листинг 6.7. Объявления переменных
‐‐ Объявление целочисленной константы, ‐‐ инициализированной значением 5. five CONSTANT integer := 5; ‐‐ Объявление целочисленной переменной, ‐‐ инициализированной значением 10. ‐‐ Переменной не может присваиваться NULL. ten integer NOT NULL := 10; ‐‐ Объявление символьной переменной, ‐‐ инициализированной значением "а".
‐‐ Функция возвращает символ и прекращает работу.
' LANGUAGE 'plpgsql':
П р ис ваива н ие Присваивание в PL/pgSQL выполняется оператором присваивания (:=) в форме левая_ переменная := правая_переменная. Команда присваивает левой переменной значение правой переменной. Также допускается запись вида левая_переменная := выражение. В этом случае левой переменной присваивается результат выражения, рас- положенного справа от оператора присваивания. Значения по умолчанию также могут присваиваться переменным в секции объявлений программных блоков PL/pgSQL. Инициализация переменной произ- водится оператором присваивания (:=) в одной строке с объявлением перемен- ной. Эта тема подробно рассматривается ниже, а в листинге 6.8 приведен не- большой пример.
Листинг 6.8. Инициализация переменной
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:
команда; 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
‐‐ Объявление псевдонимов для аргументов.
f_name ALIAS FOR $2; ‐‐ Объявление переменной для хранения кода клиента.
‐‐ Получение кода клиента, имя и фамилия которого ‐‐ совпадают с переданными значениями.
WHERE last_name = l_name AND first_name = f_name; ‐‐ Вернуть код.
' LANGUAGE 'plpgsql';
В листинге 6.10 показан результат вызова функции get_customer_id() с ар- гументами Jackson и Annie. Возвращенное число равно коду клиента «Annie Jackson» в таблице customers.
Листинг 6.10. Вызов функции get_customer_id()
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; ‐‐ Объявление переменных для хранения компонентов
BEGIN ‐‐ Получение имени и фамилии клиента, код которого
FROM customers WHERE id = customer_id; ‐‐ Вернуть полное имя.
' LANGUAGE 'plpgsql1;
В листинге 6.12 показан результат вызова функции get_customer_name() с аргументом 107.
Листинг 6.12. Вызов функции get_customer_name()
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 как признак ошибки.
return ‐1; END IF; [...]
В листинге 6.14 показано, что теперь функция get_customer_id() при пере- даче имени несуществующего клиента возвращает ‐1.
Листинг 6.14. Вызов нового варианта функции get_customer_id()
get_customer_id ‐1 (1 row)
Аргу менты
При вызове функции PL/pgSQL могут получать аргументы различных типов. В аргументах пользователь передает исходные данные, необходимые для работы функции. Аргументы делают функции PL/pgSQL более универсальными и значи- тельно расширяют область их возможного применения. Список аргументов при- водится после имени функции в круглых скобках и разделяется запятыми. Количество и типы аргументов должны соответствовать первоначальному определению функции. В листинге 6.15 приведены примеры двух вызовов функции из клиента psql.
Листинг 6.15. Примеры вызовов функций
get_author John Worsley (1 row)
Ariel Denham (1 row)
ПРИМЕЧАНИЕ. Функции get_author(text) и get_author(integer) будут рассмотрены позднее в этой главе.
Аргументы, полученные функцией, поочередно присваиваются идентифи- каторам, состоящим из знака доллара ($) и порядкового номера. Первому ар- гументу соответствует идентификатор $1, второму -- $2 и т. д. Максимальное количество аргументов равно 16, поэтому идентификаторы аргументов лежат в интервале от $1 до $16. В листинге 6.16 приведен пример функции, которая удваивает свой целочисленный аргумент.
Листинг 6.16. Непосредственное использование аргументов в переменных
BEGIN ‐‐ Вернуть значение аргумента, умноженное на 2.
' LANGUAGE 'plpgsql';
Если функция имеет большое количество аргументов, в обозначениях вида «$+номер» легко запутаться. Чтобы программисту было проще отличить одни аргумент от другого (или если он хочет присвоить переменной аргумента более содержательное имя), в PL/pgSQL предусмотрена возможность определения псевдонимов переменных. Псевдоним создается при помощи ключевого слова ALIAS и представляет собой альтернативный идентификатор для ссылки на аргумент. Перед исполь- зованием все псевдонимы (как и обычные переменные) должны быть объявле- ны в секции объявлений блока. В листинге 6.17 показан синтаксис применения ключевого слова ALIAS.
Листинг 6.17. Синтаксис использования ключевого слова ALIAS
идентификатор ALIAS FOR $1; идентификатор ALIAS FOR $2; BEGIN END; ' LANGUAGE 'plpgsql';
В листинге 6.18 приведен простой пример, демонстрирующий применение псевдонимов в функциях PL/pgSQL. Функция triple_price получает веществен- ное число, умножает его на три и возвращает результат.
Листинг 6.18. Псевдонимы PL/pgSQL
‐‐ Переменная input_price объявляется как псевдоним ‐‐ для переменной аргумента, обычно обозначаемой ‐‐ идентификатором $1.
‐‐ Вернуть аргумент, умноженный на три.
' LANGUAGE 'plpgsql';
Если теперь вызвать функцию triple_price при выполнении команды SQL SELECT в клиенте psql, будет получен результат, показанный в листинге 6.19.
Листинг 6.19. Результат вызова функции triple_price()
triple_price 37.5 (1 row) 6.1.4. В ОЗВРА Щ ЕНИЕ ПЕРЕМЕННЫХ Тип величины, возвращаемой функцией PL/pgSQL, должен соответствовать типу возвращаемого значения, указанному при создании функции командой CREATE FUNCTION. Значение возвращается командой RETURN. Команда RETURN на- ходится в конце функции, но она также часто встречается в командах IF или других командах, осуществляющих передачу управления в программе. Даже если команда RETURN вызывается в одной из этих команд, функция вес равно должна заканчиваться командой RETURN (даже если управление никогда не бу- дет передано этой завершающей команде). Синтаксис команды RETURN приведен в листинге 6.20.
Листинг 6.20. Синтаксис команды RETURN
объявление; [...] 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
‐‐ Объявление псевдонима для аргумента функции. ‐‐ в котором должно передаваться имя автора, f_name ALIAS FOR $1; ‐‐ Объявление переменной, тип которой совпадает ‐‐ с типом поля last_name таблицы authors.
‐‐ Найти в таблице authors фамилию автора. ‐‐ имя которого совпадает с переданным аргументом. ‐‐ и присвоить ее переменной l_name.
FROM authors WHERE first_name = f_name; ‐ Вернуть имя и фамилию, разделенные пробелом,
' LANGUAGE 'plpgsql';
В листинге 6.23 приведен пример вызова функции get_author().
Листинг 6.23. Результат вызова функции get_author()
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
‐‐ Объявление псевдонима для аргумента функции. ‐‐ в котором должен передаваться код автора, author_id ALIAS FOR $1; ‐‐ Объявление переменной, структура которой ‐‐ совпадает со структурой таблицы authors,
‐‐ Найти в таблице authors фамилию автора, ‐‐ код которого совпадает с переданным аргументом. SELECT INTO found_author * FROM authors WHERE id = author_id; ‐‐ Вернуть имя и фамилию, разделенные пробелом.
' LANGUAGE 'plpgsql':
Обратите внимание на звездочку (*) в списке полей команды SELECT. По- скольку переменная found_author объявлялась с атрибутом \%ROWTYPE для табли- цы authors, она имеет такую же структуру, как и записи таблицы authors. Та- ким образом, конструкция SELECT * заполняет переменную found_author значе- ниями полей найденной записи. Пример вызова новой версии get_author() приведен в листинге 6.25.
Листинг 6.25. Вызов новой версии функции get_author()
get_author John Worsley (1 row)
6.1.6. К ОН КАТ Е НАЦ ИЯ
Конкатенацией называется процесс построения новой строки посредством объединения двух (и более) строк. Конкатенация принадлежит к числу стан- дартных операций PostgreSQL и поэтому может напрямую использоваться с пе- ременными в функциях PL/pgSQL. Это незаменимый инструмент форматирова- ния при работе с несколькими переменными, содержащими символьные дан- ные. Конкатенация используется только со строками. Оператор конкатенации (|) ставится между объединяемыми компонентами (литералами или строковы- ми переменными). В листинге 6.26 операция конкатенации создает строку, возвращаемую функцией. Листинг 6.26. Возвращение результата конкатенации
‐‐ Объявление псевдонимов для аргументов функций.
word2 ALIAS FOR $2; BEGIN ‐‐ Вернуть объединение двух слов.
' LANGUAGE 'plpgsql' ;
Если передать функции аргументы «break» и «fast», функция вернет объе- диненную строку «breakfast»:
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
объявления 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
‐‐ Объявление псевдонимов для аргументов функции.
b_edition ALIAS FOR $2; ‐‐ Объявление переменной для кода ISBN. b_isbn text; ‐‐ Объявление переменной для количества экземпляров. stock_amount integer; BEGIN ‐‐ Команда SELECT INTO находит в таблице editions запись, ‐‐ у которой код книги и номер издания совпадают с аргументами ‐‐ функции. Код ISBN найденной записи присваивается переменной.
book_id = b_id AND edition = b_edition; ‐‐ Проверить, не был ли полученный код ISBN равен NULL. ‐‐ Значение NULL говорит о том, что в базе данных ‐‐ не существует записи книги с кодом и номером издания, ‐‐ переданными в аргументах функции. Если запись не существует, ‐‐ функция возвращает ‐1 и завершает работу.
END IF; ‐‐ Получить из таблицы stock количество экземпляров книги ‐‐ на складе и присвоить его переменной stock_amount. SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn; ‐‐ Вернуть количество экземпляров на складе.
' LANGUAGE 'plpgsql';
В листинге 6.29 показан результат вызова функции stock_amount() для ко- да книги 7808 и издания 1.
Листинг 6.29. Результаты вызова функции stock_amount() booktowrHf SELECT stock_amount(7808,1):
22 (1 row)
Команда IF/THEN/ELSE В команде IF/THEN/ELSE задаются два блока команд. Первый блок выполня- ется в том случае, если условие истинно, а второй если оно ложно. Синтаксис команды IF/THEN/ELSE приведен в листинге 6.30.
Листинг 6.30. Синтаксис команды IF/THEN/ELSE
объявления 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
‐‐ Объявление псевдонимов для аргументов функции.
b_edition ALIAS FOR $2; ‐‐ Объявление текстовой переменной для найденного кода ISBN.
‐‐ Объявление целочисленной переменной для количества экземпля‐ ров. stock_amount integer;
BEGIN ‐‐ Команда SELECT INTO находит в таблице editions запись. ‐‐ у которой код книги и номер издания совпадают с аргументами ‐‐ функции. Код ISBN найденной записи присваивается переменной.
book_id = b_id AND edition = b_edition; ‐‐ Проверить, не был ли полученный код ISBN равен NULL. ‐‐ Значение NULL говорит о тон. что в базе данных ‐‐ не существует записи книги с кодом и номером издания. ‐‐ переданными в аргументах функции. Если запись не существует. ‐‐ функция возвращает FALSE и завершает работу.
END IF; ‐‐ Получить из таблицы stock количество экземпляров книги ‐‐ на складе и присвоить его переменной stock_amount.
WHERE isbn = b_isbn; ‐‐ Проверить, является ли количество книг на складе ‐‐ положительной величиной. Если количество положительно, ‐‐ функция возвращает TRUE, а если отрицательно ‐‐ или равно нулю ‐ FALSE.
ELSE RETURN TRUE; END IF; END; ' LANGUAGE 'plpgsql':
В листинге 6.32 показан результат вызова in_stock() для кода книги 4513 и издания 2.
Листинг 6.32. Результат вызова функции in_stock()
in stock t (1 row)
Функция вернула значение TRUE признак наличия книги на складе.
Команда IF/THEN/ELSE/IF Команда IF/THEN/ELSE/IF предназначена для последовательной проверки нескольких условий. Сначала проверяется первое условие; если оно окажется равным FALSE, проверяется следующее условие и т. д. Последняя секция ELSE содержит команды, выполняемые в том случае, если пи одно из проверенных условий не было истинным. Синтаксис команды IF/THEN/ELSE/IF:
объявление 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
‐‐ Объявление псевдонима для аргумента, содержащего либо ‐‐ строку all, либо тему. sub_title ALIAS FOR $1; ‐‐ Объявление целочисленной переменной для хранения кода темы ‐‐ и текстовой переменной для хранения списка найденных книг. ‐‐ Текстовая переменная инициализируется пустой строкой.
found_text text; BEGIN ‐‐ Получить код темы, описание которой передано в аргументе. SELECT INTO sub_id id FROM subjects WHERE subject = sub_title; ‐‐ Проверить, запросил ли пользователь информацию обо всех темах ‐‐ (строка all). В этом случае вызвать функцию extract_all_titles() и вернуть полученную текстовую переменную.
found_text extract_all_titles(); RETURN found text; ‐‐ Если в аргументе НЕ БЫЛА передана строка "all", проверить, ‐‐ входит ли код темы в интервал допустимых значений. ‐‐ Если это так, вызвать функцию extract_title() с кодом темы ‐‐ и присвоить результат переменной found_text.
THEN found_text := extract_title(sub_id); RETURN " " || sub_title || ": " | found_text; ‐‐ Если код темы равен NULL, вернуть сообщение о том. что ‐‐ заданная тема не найдена.
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()
books_by_subject
Dynamic Anatomy
The Cat in the Hat Bartholomew and the Oobleck Franklin in the Dark Goodnight Moon [...]
Science Fiction: Dune 2001: A Space Odyssey (1 row) booktown=# SELECT books_by_subject('Computers'); books by_subject
Practical PostgreSQL Programming Python (1 row)
Ци клы
Другую категорию команд, передающих управление внутри функций, со- ставляют циклы. В циклах используются разные виды итераций, предназначен- ные для решения разных задач. Итеративные вычисления значительно расши- ряют возможности функций PL/pgSQL. В PL/pgSQL реализованы три типа циклов: простейший (безусловный) цикл, цикл WHILE и цикл FOR. Вероятно, из этих трех циклов чаще всего применяется цикл FOR, подходящий для широкого круга задач программирования, хотя и другие циклы также достаточно часто встречаются на практике.
Безусловный цикл Ключевое слово LOOP начинает простейший безусловный цикл. Команды безусловного цикла выполняются до тех пор, пока не будет достигнуто ключе- вое слово EXIT. За ключевым словом EXIT может следовать секция WHEN с выра- жением, определяющим ус |
|