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

Хранимые процедуры

Хранимая процедура — это программная функция, которая создается и хранится в базе данных MySQL. Она может состоять из SQL-операторов и ряда специальных управляющих структур. Хранимая функция может быть полезна, когда одну и ту же функцию необходимо выполниять из различных приложений или с различных платформ, либо в качестве средства инкапсуляции функциональных возможностей. Хранимые процедуры в базе данных можно считать аналогом объектно-ориентированного подхода в программировании. Они позволяют управлять способом доступа к данным.

Простой пример

Прежде чем мы приступим к рассмотрению примера, изменим столбец amount в талице orders

CREATE TABLE `orders` (
`orderid` int(10) unsigned NOT NULL auto_increment,
`customerid` int(10) unsigned NOT NULL,
`amount` float(7,2) default NULL,
`date` date NOT NULL,
PRIMARY KEY (`orderid`);

Как видно из таблицы мы изменили значение float c float(6,2) на float (7,2). Это сделано для того, чтобы при тестировании примера, MySQL не выдавал ошибки типа:

Объявляем хранимую процедуру.

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

delimiter //

create procedure total_orders (out toyal float)

BEGIN

select sun (amount) into total from orders;

END

//

Рассмотрим этот код.

Первый оператор:

delimiter //

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

Следующая строка:

create procedure total_orders (out toyal float)

содержит собственно хранимую процедуру. Ей присваивается имя total_orders. Она содержит единственный параметр total — значение, которое будет вычисляться. Слово OUT показывает, что этот параметр должен передаваться или возвращаться.

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

Слово float задает тип параметра. В данном случае мы возвращаем сумму всех заказов в таблице orders/ Тип столбца orders-float, поэтому возвращаемым типом такде является float. Допустимые типы данных преобразуются в доступные типы столбцов.

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

Тело процедуры размещается между операторами BEGIN и END. Они аналогичны фигурным скобкам внутри кода PHP ({}), поскольку ограничивают блок операторов.

В теле просто выполняется оператор SELECT. Единственное отличие от обычного использования этого оператора состоит в том, что в него включено выражение into total для загрузки результата выполнения запроса в параметр total.

Завершается процедура снова символом двойной четы:

//

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

call total_orders (@t);

Этот оператор вызывает процедуру total_orders и передает ей переменную для сохранения результата. Чтобы увидеть результат, необходимо просмотреть содержимое переменной:

select @t;

Результат выполнения этой команды должен быть таким:

Аналогично созданию процедуры можно создать функцию. Функция принимает входные параметры (только входные) и возвращает единственное значение.

Общий синтаксис выполнения этой задачи такой же.

Общий синтаксис создания функции

delimiter //

create function add_tax (price float) returns float

return price*1.1;

//

Как видите, в этом примере вместо ключевого слова procedure используется ключевое слово function/ Есть и несколько других различий.

Параметры не обязательно указывать как имеющие тип IN или OUT, поскольку все они являются параметрами типа IN, или входными параметрами. За списком параметров следует выражение return float. Оно задает тип возвращаемого значения. Это значение может иметь любой из допустимых типов MySQL.

Возврат значения выполняется с помощью оператора return подобно тому, как это делается в PHP.

Обратите внимание, что в этом примере операторы BEGIN и EТВ не используются. Их можно применять, однако они не обязательны. Как и в PHP, если блок операторов содержит только один оператор, его начало и конец помечать не обязательно.

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

select add_tax(100);

Оператор должен вернуть следующий результат:

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

show create procedure total_orders;

Результат будет таким:

или

show create function add_tax;

Результат будут таким:

Их можно удалить с помощью оператора

drop procedure total_orders;

или

drop function add_tax;

Хранимые процедуры допускают использование управляющих структур, переменных, обработчиков DECLARE (подобно исключениям), а также важных компонентов, называемых курсорами.

Локальные переменные

Локальные переменные можно объявить внутри блока begin .. end с помощью оператора declare. Например, функцию add_tax можно было бы изменить, чтобы для хранения налоговой ставки она использовала локальную переменную.

Общий синтаксис создания функции

delimiter //

create function add_tax (price float) retuns float

begin

declare tax float default 0.18;

return price*(1+tax);

end

//

Как видите, для объявления переменной используется ключевое слово declare, за которым следует имя переменной и тип. Выражение default не обязательно и оно указывает начальное значение переменной. Затем переменную можно использовать как обычно.

Курсоры и управляющие структуры

Рассмотрим более сложный пример. Для этого создадим хранимую процедуру, которая выясняет, какой заказ имел максимальную общую стоимость, и возвращает значение orderid. Конечно, это значение достаточно легко можно было бы выяснить с помощью единственного запроса, но этот простой пример иллюстрирует применение курсоров и управляющих структур. Код этой хранимой процедуры такой:

Процедура для поиска orderid с максимальной суммой заказа.

Эту задачу можно былобы выполнить с помощью функции max, но данная процедура служит лишь для иллюстрации

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

delimiter //
create procedure largest_order (out largest_id int)
begin
declare this_id int;
declare this_amount float;
declare l_amount float default 0.0;
declare l_id int;
declare done int default 0;
declare continue handler for sqlstate '02000' set done=1;
declare cl cursor for select orderid, amount from orders;

open cl;
repeat
fetch cl into this_id, this_amount;
if not done then
if this_amount > l_amount then
set l_amount = this_amount;
set l_id = this_id;
end if;
end if;
until done end repeat;
close cl;
set largest_id = l_id;
end
//

В этом коде задействованы управляющие структуры (как условия, так и циклы), курсоры и обработчики объявлений. Рассмотрим их.

В начале процедуры мы объявляем несколько локальных переменных, которые будут использоваться внутри нее. Переменные this_id и this_amount хранят значения полей orderid и amount текущей строки. Переменные l_amount и l_id служат для хранаения суммы максимального заказа и соответствующего идентификатора. Поскольку максимальная сумма заказа будет вычисляться путем сравнения каждого значения с текущим наибольшим значением, этой переменной присвоено начальное значение, равное нулю.

Следующая объявленная переменная —done, которой присвоено начальное значение, равное нулю (false). Эта переменная представляет собой флаг цикла. Когда строк для просмотра не останется, ее значение будет установлено равным 1 (true).

Строка

declare continue handler for sqlstate '02000' set done=1;

называется обработчиком объявления (declare handler). В хранимых процедурах он подобен исключению.

Следующая часть обработчика объявления указывает условия вызова этого обработчика. В данном случае он будет вызываться при достижении состояния sqlstate '02000'. Это условие может оказаться весьма загадочным. В действительности же оно означает, что обработчик будет вызываться, если ни одна строка не найдена. Результирующий набор обрабатывается строка за строкой, и когда строк для обработки не останется, процедура вызовет этот обработчик. С таким же успехом можно было бы указать FOR NOT FOUND. Другими возможными параметрами являются SQLWARNING и SQLEXCEPTION.

Следующий компонент, с которым необходимо познакомится — курсор(cursor). Курсор ничем не отличается от массива. Он извлекает результирующий набор запроса (такой, как возвращаемый функцией musqli_query() и позволяет выполнить его построчную обработку (как, например, с помощью функции mysqli_fetch_row()).

Рассмотрим следующий курсор:

declare cl cursor for select orderid, amount from orders;

Этот курсор имеет имя cl. Это всего лишь определение того, что он будет содержать. Пока что запрос не будет выполняться.

Следующая строка:

open cl;

в действительности выполняет запрос. Для получения каждой строки данных потребуется выполнить оператор fetch. Это делается в цикле repeat. В данном случае цикл выглядит следующим образом:

repeat

. . .

until done end repeat;

Обратите внимание, что условие (until done) не проверяется до завершения процедуры. Хранимые процедуры поддерживают также циклы while, имеющие показанную ниже форму:

while условие do

. . .

end while;

Существуют также циклы loop^

loop

. . .

end loop;

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

Обратите внимание на отсутствие цикла for.

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

fetch cl into this_id, this_amount;

Эта строка извлекает строку из запроса курсора. Два атрибута, полученные запросом, сохраняются в двух указанных локальных переменных.

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

if not done then
if this_amount > l_amount then
set l_amount = this_amount;
set l_id = this_id;
end if;
end if;

Обратите внимание, что значение переменных устанавливается с помощью оператора set.

Помимо структуры if . . then хранимые процедуры поддерживают также конструкцию if . . then . . else, которая имеет следующую форму:

if условие then

. . .

[elseif условие then]

. . .

[else]

. . .

end if

Можно использовать оператор case, форма которого показана ниже:

case значение

when значение then оператор

[when значение then оператор . . .]

[else оператор ]

end case

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

close cl;
set largest_id = l_id;

Оператор close закрывает курсор.

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

Если эта процедура была создана, ее можно вызвать как любую другую процедуру:

call larget_order(@l);

select @l;