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

Для создания хранимой процедуры существуют два оператора: CREATE PROCEDURE и CREATE FUNCTION, которые имеют следующий синтаксис:

CREATE PROCEDURE имя_процедуры ([параметр [,…]])

[характеристики] тело_процедуры

CREATE FUNCTION имя_функции ([параметр [,…]])

RETURNS тип

[характеристики] тело_процедуры

Каждый параметр позволяет передать в процедуру или из нее входные данные или результат работы процедуры и имеет следующий синтаксис:

[ IN ç OUT çINOUT] имя_параметра тип

Где

IN – данные передаются внутрь процедуры. Если такому параметру в теле процедуры присваивается новое значение, то по выходе из процедуры оно не сохраняется и параметр принимает значение, которое он имел до вызова процедуры.

OUT – данные передаются из хранимой процедуры.

INOUT – значение этого параметра принимается во внимание как внутри процедуры, так и сохраняет свое значение по выходу из нее.

Оператор CREATE FUNCTION позволяет создать пользовательскую функцию, т.е. такой вид хранимой процедуры, который возвращает единственное значение. Тип этого значения позволяет задать оператор return.

О характеристиках процедуры будет сказано ниже.

Тело процедуры состоит из составного оператора BEGIN . . . END, внутри которого могут располагаться другие операторы, в том числе и другие составные операторы BEGIN . . . END. Оператор имеет следующий синтаксис:

[ метка:] BEGIN

Операторы

END [метка]

Оператор BEGIN . . . END может быть вложенным.

Например,

BEGIN

UPDATE tabl1 SET col1 = ‘1234;

Inner: BEGIN

UPDATE tabl2 SET col2 = ‘1234;

UPDATE tabl3 SET col3 = ‘1234;

END Inner

END

Основная трудность, которая возникает при работе с хранимыми процедурами, заключается в том, что обязательный символ точки с запятой «;» в конце каждого запроса воспринимается консольным клиентом как сигнал к отправке запроса на сервер. Чтобы избежать этого, при работе с хранимыми процедурами следует переопределить разделитель при помощи параметра – delimiter = / / консольного клиента mysql. В этом случае для обозначения окончания ввода вместо точки с запятой необходимо будет использоваться последовательность”/ /”. Ниже приведен пример смены разделителя при помощи команды DELIMITER, после которой указать сам разделитель:

mysql> DELIMITER / /

и обратно:

mysql> DELIMITER ;

Пример создания простейшей хранимой процедуры, выводящей версию mysql:

mysql> CREATE PROCEDURE my_version ( )

−> BEGIN

−> SELECT VERSION ( );

−> END

−> / /

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

mysql> CALL my_version ( ) / /

В теле хранимой процедуры допускается использовать операторы

INSERT, UPDATE, DELETE, SELECT, DROP, REPLACE и др.

Параметры хранимой процедуры-подпрограммы

Пример использования ключевого слова IN:

mysql> USE Postavki / /

Database changed

mysql> CREATE PROCEDURE set_x (IN value INT)

−> BEGIN

−> SET @x = value;

−> END

−> / /

mysql> CALL set_x (123456) / /

mysql> SELECT @x / /

Будет выведено:

@x

Пример использования ключевого слова OUT

mysql> USE Postavki / /

Database changed

mysql> CREATE PROCEDURE set_y (OUT value INT)

−> BEGIN

−> SET @x = value ;

−> SET value = 7;

−> END

−> / /

mysql> SET @val = 123456 / /

mysql> CALL set_y (@val) / /

mysql> SELECT @x, @val / /

Будет выведено:

@x @val

NULL 7

Пример использования ключевого слова INOUT

mysql> USE Postavki / /

Database changed

mysql> CREATE PROCEDURE set_y (INOUT value INT)

−> BEGIN

−> SET @x = value ;

−> SET value = 7;

−> END

−> / /

mysql> SET @val = 123456 / /

mysql> CALL set_y (@val) / /

mysql> SELECT @x, @val / /

Будет выведено:

@x @val

123456 7

Работа с таблицами базы данных

Создадим процедуру, которая подсчитывает число записей в таблице Post.

mysql> USE Postavki / /

Database changed

mysql> CREATE PROCEDURE num_Post (OUT total INT)

−> BEGIN

−> SELECT COUNT (*) INTO total FROM Post ;

−> END

−> / /

Вызов этой процедуры:

mysql> USE Postavki / /

mysql> CALL num_Post (@a) / /

mysql> SELECT @a / /

Будет выведено:

@a

В качестве параметра процедуры передается пользовательская переменная @a.

Создадим хранимую процедуру proc_PIM, которая будет возвращать по первичному ключу PN имя поставщика PIM из таблицы Post. Для этого потребуется определить параметр pn_post как IN, а pim_post как OUT.

mysql> CREATE PROCEDURE proc_pim (IN pn_post INT, OUT pim_post CHAR)

−> BEGIN

−> SELECT PIM INTO pim_post FROM Post

−> WHERE PN = pn_post;

−> END

−> / /

mysql> SET @pn_post := 2 / /

mysql> CALL proc_pim (@pn_post, @pim_post) / /

mysql> SELECT @pn_post, @pim_post / /

Будет выведено:

@pn_post, @pim_post

Петров

Создадим хранимую процедуру pim_gor, которая по имени поставщика (PIM) возвращает город (GOR), в котором размещен этот поставщик.

mysql> CREATE PROCEDURE pim_gor (IN pim_post CHAR, OUT gor_post

−> CHAR)

−> BEGIN

−> DECLARE pn_post INT;

−> SELECT PN INTO pn_post FROM Post

−> WHERE PIM = pim_post LIMIT 1;

−> CALL proc_pim (pn_post, );

−> END

−> / /

mysql> CALL pim_gor (‘Иванов’, @gorod) / /

mysql> SELECT @gorod / /

@gorod

СПб

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

В процедуре pim_gor потребовалась временная переменная pim_gor. Для использования любой переменной в процедуре требуется ее объявление при помощи оператора DECLARE, который имеет следующий синтаксис:

DECLARE имя_переменной [, …] тип [DEFAULT значение ]

Одним оператором DECLARE можно объявить несколько переменных, например:

DECLARE id, num INT DEFAULT 0;

Оператор DECLARE может быть использован только внутри блока BEGIN … END, область видимости объявленной переменной также ограничена этим блоком. Это означает, что в разных блоках BEGIN … END могут быть объявлены переменные с одинаковым именем, и действовать они будут только в рамках данного блока, не пересекаясь с переменными других блоков.

Поиск по сайту: