Создание хранимой процедуры

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

Программные блоки вводятся в командах SQL CREATE FUNCTION, которые используются для определения функций PL/pgSQL в базах данных PostgreSQL. Команда CREATE FUNCTION определяет имя функции, типы ее аргументов и возвращаемого значения.

После определения функции (после AS) необходимо указать, что за этим следует тело функции. Символ ' или $BODY$ как раз указывают на это. После ключевого слова END также должен быть либо символ ', либо $BODY$.

Основной блок функ­ции начинается с секции объявлений. Секция объявлений обозначается ключевым словом DECLARE, а каждое объявление завершается символом точки с запятой ‘;’.

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

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

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

Структура программного блока PL/pgSQL:

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

DECLARE

объявление;

(…)

BEGIN

команда;

(…)

END;

'LANGUAGE 'plpgsql';

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

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

Типы данных и объявление переменных

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

Переменные, используемые в программном блоке PL/pgSQL, должны быть объяв­лены в секции объявлений этого блока, начинающейся с ключевого слова DECLARE в начале блока. Переменные, объявленные в блоке, доступны во всех его вложенных блоках, но обратное неверно: переменные, объявленные во вложенном блоке, уничтожаются в конце этого бло­ка и недоступны во внешнем блоке. Объявление переменной также может содержать дополнительные модифика­торы. Ключевое слово CONSTANT указывает на то, что вместо переменной определя­ется константа.

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

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

Синтаксис объявления переменной приведен в листинге

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

Например:

CREATE FUNCTION example_function О RETURNS text AS '

DECLARE

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

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

five CONSTANT integer := 5;

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

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

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

ten integer NOT NULL : = 10:

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

-- инициализированной значением "а",

letter char DEFAULT: = "a":

BEGIN

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

return letter;

END;

' LANGUAGE 'plpgsql';

Входные параметры

Для проверки работы хранимой процедуры со входной переменной, напишем функцию, которая будет удалять служащего по его идентификатору. Хранимая процедура delete_ slzh удаляет из таблицы zakazy.sluzhaschie запись о служащем, чей идентификатор указан в качестве входного параметра.

CREATE OR REPLACE FUNCTION zakazy.delete_slzh(slzh_id integer)

RETURNS void AS

$BODY$

BEGIN

delete from zakazy.sluzhaschie

where id_slzh = $1;

END;

$BODY$

LANGUAGE 'plpgsql';

Чтобы проверить работает ли наша процедура необходимо выполнить следующую команду:

select zakazy.delete_slzh(2110)

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

Если функция имеет большое количество аргументов, в обозначениях вида «$+номер» легко запутаться. Чтобы программисту было проще отличить один ар­гумент от другого (или если он хочет присвоить переменной аргумента более со­держательное имя), в PL/pgSQL предусмотрена возможность определения псевдонимов переменных.

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

Выходные параметры

Выходные параметры определяют набор данных, возвращаемых хранимой процедурой. Тип величин, возвращаемой функцией Pl/pgSQL, должен соответствовать типу возвращаемого значения, указанному при создании функции командой create function. Значение возвращается командой return. Команда return находится в конце функции, но она также часто встречается в командах if или других командах, осуществляющих передачу управления программе. Даже если команда return вызывается в одной из этих команд, функция все равно должна заканчиваться командой return (даже если управлене никогда не будет передано этой завершающей команде). Синтаксис команды return приведен ниже:

CREATE FUNCTION ФУНКЦИЯ (АРГУМЕНТЫ) RETURN ТИП AS

$BODY$

DECLARE

ОБЪЯВЛЕНИЕ:

[…]

BEGIN

КОМАНДА:

[…]

RETURN {ПЕРЕМЕННАЯ | ЗНАЧЕНИЕ}

END

$BODY$

LANGUAGE 'PLPGSQL';

Для примера создадим хранимую процедуру сount_family, которая подсчитывает и возвращает количество фамилий служащих всех оффисов:

CREATE OR REPLACE FUNCTION store.count_family()

RETURNS integer AS

$BODY$

declare

num integer;

begin

select into num count(family) from store.sluzhachie;

return num;

end;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

В данном случае входные параметры отсутствуют и определен только один выходной параметр num, которому присваивается результат выполнения оператора SELECT INTO num count(family) from store.sluzhachie.

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

CREATE OR REPLACE FUNCTION store.num_slzh_offica(office_id integer)

RETURNS store.num_slzh AS

$BODY$

DECLARE

office_id INTEGER;

slzh store.num_slzh;

BEGIN

--определяем город офиса

SELECT INTO slzh.city city FROM store.officy

WHERE id_ofc = $1;

--определяем количество сотрудников офиса

SELECT INTO slzh.num_slzh COUNT(id_slzh) FROM store.sluzhaschie

WHERE id_ofc = $1;

RETURN slzh;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

В данном случае мы использовали новый тип, который создали с помощью следующего скрипта:

CREATE TYPE store.num_slzh AS

(office_name character varying,

num_slzh integer);

Синтаксис создания пользовательского типа:

CREATE TYPE name AS

( attribute_name data_type [, ... ] ),

здесь name – название композитного типа; attribute_name – название переменной, data_type – ее тип.

Рис. 28. Результат выполнения хранимой процедуры num_slzh_offica(office_id)

Пользовательские типы данных используются в основном в хранимых процедурах, возвращающих более одного выходного параметра.

Рис. 29. Результат выполнения хранимой процедуры num_slzh_offica(office_id)

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

Вызов хранимых процедур

При вызове функций PL/pgSQL имя функции обычно включается в команду SQL SELECT. Синтаксис:

SELECT функция(аргументы);

Подобный способ вызова функция при выборке и присваивании стал стандартным, поскольку любая функция PostgreSQL должна возвращать значение некоторого типа.

Например, для вызова хранимой процедуры get_slzh() можно использовать оператор

SELECT store.get_slzh(2103)

Рис. 30. Вызов процедуры get_slzh()

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

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

левая переменная -.= правая_переменная

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

ПРИМЕЧАНИЕ

Для сохранения результатов запроса в новой таблице в PL/pgSQL используется альтернативный синтаксис SQL CREATE TABLE AS SELECT.

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

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

Покажем это на следующем примере, который возвращает фамилию и имя служащего.

CREATE OR REPLACE FUNCTION zakazy.get_slzh(id_slzh1 integer)

RETURNS character varying AS

$BODY$

DECLARE

id_slzh1 ALIAS FOR $1;

family1 text;

name1 text;

BEGIN

SELECT INTO family1, name1

family, name

FROM zakazy.sluzhaschie

WHERE id_slzh = id_slzh1;

RETURN family1 || ' ' || name1;

END;

$BODY$

LANGUAGE 'plpgsql'

В этом примере мы также использовали оператор конкатенации: ||, который объединяет две строки в одну.

Атрибуты

Для упрощения работы с объектами базы данных в PL/pgSQL существуют атри­буты переменных -- %TYPE и %R0WTYPE. Атрибуты требуются для объявления пере­менной, тип которой совпадает с типом объекта базы данных (атрибут %TYPE) или структурой записи (атрибут %ROWTYPE). Переменные объявляются с атрибутами в том случае, если они будут использоваться в программном блоке для хранения значений, полученных от объекта базы данных.

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

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

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

Следующая функция возвращает возраст служащего по его идентификатору:

CREATE OR REPLACE FUNCTION zakazy.get_age(id_slzh1 integer)

RETURNS zakazy.d_age AS

$BODY$

DECLARE

age1 zakazy.sluzhaschie.age%TYPE;

BEGIN

select into age1 age

from zakazy.sluzhaschie

where id_slzh = $1;

return age1;

end;

$BODY$

LANGUAGE 'plpgsql'

Атрибут %ROWTYPE используется в PL/pgSQL для переменной-записи, имеющей оди­наковую структуру с записями заданной таблицы. Не путайте атрибут %R0WTYPE с типом данных RECORD - переменная с атрибутом %R0WTYPE точно воспроизводит структуру записи конкретной таблицы, а переменная RECORD не структурирована, и ей можно присвоить запись любой таблицы.

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

CREATE OR REPLACE FUNCTION zakazy.get_slzh1(slzh_id integer)

RETURNS character varying AS

$BODY$

DECLARE

slzh zakazy.sluzhaschie%rowtype;

BEGIN

select into slzh * from zakazy.sluzhaschie

where id_slzh = $1;

return slzh.family || ' ' || slzh.name;

END;

$BODY$

LANGUAGE 'plpgsql'

Обратите внимание: в реализации функции используется переменная, объяв­ленная с атрибутом %R0WTYPE. Возможно, в данном случае применение %R0WTYPE только напрасно усложняет очень простую задачу, но по мере изучения PL/pgSQL важ­ность атрибута %R0WTYPE становится все более очевидной.

Точка (.) после имени переменной slzh используется для ссылки на имя поля, входящего в структуру slzh.

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

Конструкция IF ... THEN ... ELSE в языке SQL применяется точно так же, как и в других языках программирования. Если условие, заданное после ключевого слова IF, истинно, то выполняется блок операторов после ключевого слова THEN. В противном случае выполняется блок операторов после ключевого слова ELSE.

Полный синтаксис IF ... THEN ... ELSE:

IF условие THEN

команда;

[...]

ELSE IF условие

команда;

[...]

ELSE

команда;

[...]

END IF;

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

CREATE OR REPLACE FUNCTION zakazy.delete_slzh1(slzh_id integer)

RETURNS integer AS

$BODY$

DECLARE

num_client integer := 0;

BEGIN

select into num_client count(id_cln)

from zakazy.clienty

where id_slzh = $1;

if num_client > 0 then return -1;

ELSE

delete from zakazy.sluzhaschie

where id_slzh = $1;

return 1;

end if;

end;

$BODY$

LANGUAGE 'plpgsql'

Циклы

Циклы, как и условная команда, аналогичны циклам из обычных языков программирования.

Цикл while

Синтаксис цикла WHILE:

WHILE условие LOOP

команда;

[...]

END LOOP;

Цикл FOR

Синтаксис цикла FOR:

FOR переменная IN [ REVERSE ] выражение1 .. выражение2 LOOP

команда;

[...]

END LOOP:

Цикл FOR выполняет одну итерацию для каждого значения переменной переменнаяв интервале, границы которого определяются выражениями выражение1 и выражение2(включительно). В начале цикла переменная инициализируется значением выражения выражение1и увеличивается на 1 после каждой итерации. Если в заголовке цикла присутствует ключевое слово REVERSE, то переменная не увеличивается, а уменьшается.

Циклы FOR также используются для перебора результатов запросов.

Синтаксис цикла FOR с перебором записей:

FOR %переменная_rowtype IN команда_select LOOP

команда;

[...]

END LOOP:

Следующий пример возвращает список всех фамилий служащих в виде строки:

CREATE OR REPLACE FUNCTION zakazy.get_family()

RETURNS character varying AS

$BODY$

DECLARE

row zakazy.sluzhaschie%rowtype;

str text := '';

BEGIN

for row in select *

from zakazy.sluzhaschie loop

str := str || row.family || E'\n';

END loop;

return str;

end;

$BODY$

LANGUAGE 'plpgsql'

Вызов функций

При вызове функции PL/pgSQL из кода PL/pgSQL имя функции обычно включа­ется в команду SQL SELECT или в команду присваивания. Примеры:

SELECT функция(аргументы);

переменная := функция(аргументы):

Подобный способ вызова функций при выборке и присваивании стал стандарт­ным, поскольку любая функция PostgreSQL должна возвращать значение некото­рого типа. Ключевое слово PERFORM позволяет вызвать функцию и проигнориро­вать возвращаемое значение. Синтаксис вызова функции, с ключевым словом PERFORM:

PERFORM функция(аргументы);

Не нашли, что искали? Воспользуйтесь поиском по сайту: