LXF77:PHP

Материал из Linuxformat.

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

часть 2 Как будто нам недостаточно изучения SQL! MySQL 5.0 приносит с собой ещё и новый способ программирования. Специалист по базам данным Пол Хадсон (Paul Hudson) представляет хранимые процедуры.

Я хотел бы сделать важное заявление: PHP не всегда самый лучший язык программирования. Да, как правило, он лучший, но кроме него существует ещё и множество других, подходящих в каждой конкретной ситуации. А при использовании нескольких языков одновременно сразу же возникает вопрос — как правильно взаимодействовать с базой данных. Возможно, вы мне не поверите, но с MySQL 5.0 такой проблемы не существует.

Условия задачи таковы: не важно, какой язык программирования вы используете, информация в базе данных должна быть одна и та же. Если вы работаете с базой данных вашей коллекции компакт-дисков, скорее всего, вам не придётся особенно заботиться о методах доступа к информации, достаточно просто прочитать несколько строк и, возможно, сделать некоторые изменения. Но если вы разрабатываете приложения для работы с банковской базой данных, то просто необходимо всегда следовать строгим правилам, выполнять все необходимые проверки, вести исчерпывающий журнал всех действий и так далее. Очень сложно контролировать соблюдение всех этих правил в среде, оперирующей различными языками программирования.

Теперь у нас появилось решение! Вам больше не нужно думать о том, чтобы помногу раз копировать тысячи строк SQL-кода. Поддержка множества копий одного и того же запроса — это жуткий кошмар, тысячи человеко-часов и питательная среда для ошибок. Нет, самым лучшим вариантом было бы сохранить SQL прямо в базе данных и в дальнейшем вызывать его изо всех приложений. Вместо того, чтобы поддерживать код, который загружает информацию о пользователе 50235, выполняет нужные изменения и сохраняет запись о них в журнале, гораздо лучше записать всю последовательность действий в базу данных в виде функции и затем вызывать её примерно так: updateuser(50235). При использовании этого метода код на любом языке программирования вообще не должен знать, что требуется сохранять что-то в журнале, не говоря уж о том, что именно надо туда записывать: база данных сама позаботится об этом. Если же правила затем изменятся, вам потребуется внести изменения в базу данных и никуда больше, вместо того чтобы править сотни файлов на разных языках программирования.

Это решение известно как хранимые процедуры. Если вы найдёте время на то, чтобы выучить их синтаксис, то получите ценное приобретение в ваш арсенал администратора базы данных. В предыдущем номере мы с вами рассматривали, как создавать и использовать триггеры на таблицах, так что теперь вы умеете наблюдать за выполняемыми запросами и контролировать их. Хорошая новость: триггеры — это не более чем хранимые процедуры, разработанные для запуска в специфические моменты. Таким образом, триггеры — это подмножество хранимых процедур, не позволяющее получать и передавать ваши переменные. Так что если вы изучили учебник из февральского номера, у вас уже есть фора.

Следующие процедуры

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

CREATE TABLE users (ID INT NOT NULL AUTO_INCREMENT PRIMARY
KEY, Name VARCHAR(255), Age TINYINT UNSIGNED);
INSERT INTO users (Name, Age) VALUES ('Nick', 44);

Процедура будет менять возраст пользователя с заданным именем, делая его на год старше. Как и с триггерами, нам потребуется изменить разделитель команд с; на // для того, чтобы ввести многострочную процедуру как единое целое.

Итак:

DELIMITER //
CREATE PROCEDURE get_older (IN pName VARCHAR(255))
BEGIN
UPDATE users SET Age = Age + 1 WHERE Name = pName;
END//
DELIMITER ;

После смены разделителя мы создаём процедуру get_older и объявляем, что она принимает параметр типа VARCHAR(255) под названием pName. Обратите внимание, параметры BEGIN и END используются для определения границ процедуры, но их можно опускать, если процедура состоит из одной единственной строки (как у нас). Наша процедура обновляет все строки в таблице, удовлетворяющие указанному условию (зависящему от значения параметра), увеличивая значение поля age на единицу. В конце определения процедуры мы возвращаем определение разделителя к стандартному значению.

Как вызвать нашу процедуру? Нет ничего проще.

SELECT * FROM users;
CALL get_older('Nick');
SELECT * FROM users;

Два оператора SELECT нужны для того, чтобы увидеть, как изменилось значение Age после вызова get_older(), для работы хранимой процедуры они не нужны. Как видите, get_older() работает именно так, как вы ожидали, и её достаточно просто использовать. Если вас интересует, что будет, если передать параметр, который не является VARCHAR(255), то ответ прост — MySQL преобразует его к типу VARCHAR.

Чтение данных

Переместимся в более сложные области и добавим ещё одного пользователя в нашу таблицу:

INSERT INTO users (Name, Age) VALUES ('Nick', 32);
SELECT * FROM users;

Теперь у нас есть два Ника, с ID равным 1 и 2. Мы можем создать хранимую процедуру для возвращения значений из запроса SELECT очень простым способом.

DELIMITER //
CREATE PROCEDURE get_user (IN pID VARCHAR(255))
BEGIN
SELECT * FROM users WHERE ID = pID;
END//
DELIMITER ;

Здесь используется та же самая идея, за тем исключением, что мы указываем ID для точного определения пользователя и используем оператор SELECT, чтобы извлечь информацию из таблицы. Вызов get_user() в данном случае возвратит одну строку, но если бы так случилось, что к условию подошли бы несколько строк, то все они были бы получены.

Второй способ вернуть данные из процедуры — это использование выходных (OUT) параметров. До сих пор мы помечали все параметры как IN, что на самом деле не обязательно, поскольку MySQL считает все параметры входящими по умолчанию. Если вы когда-нибудь использовали CORBA, COM+ или D-BUS, то понятие OUT-параметров вам знакомо. Если же нет, то всё станет очевидно после следующего примера. Давайте попробуем вернуть значение из процедуры при помощи выходного параметра:

DELIMITER //
CREATE PROCEDURE get_user (IN pName VARCHAR(255),
OUT pID INT)
BEGIN
SELECT ID FROM users WHERE Name = pName LIMIT 1
INTO pID;
END//
DELIMITER ;

Важным является тот момент, что мы немного изменили работу запроса, добавив оператор LIMIT 1. Он потребовался по той причине, что в качестве OUT-параметра мы можем вернуть одно и только одно значение, и если SQL-запрос вдруг вернёт несколько строк, то произойдёт ошибка. Вызов этой процедуры немного отличается от приведённого выше, так как нам надо передать два параметра, один из которых является переменной SQL и может быть опрошен позднее. Итак:

mysql> CALL get_user('Nick', @ID);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @ID;
+------+
| @ID |
+------+
| 1 |
+------+
1 row IN SET (0.00 sec)

Inline-функции

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

Как Pascal или Fortran, MySQL различает процедуры и функции. Первые — это команды, а вторые — запросы. Если вы изучали компьютерные науки, то вам должно быть знакомо понятие «Command-Query Separation».

В MySQL процедуры не могут быть вызваны inline, и хотя они умеют возвращать значения при помощи OUT-переменных, вам потребуется выполнить ещё один запрос, чтобы прочитать их. Альтернативный вариант — использовать функции, разработанные так, что их можно вызывать как часть более длинного SQL-запроса, наподобие встроенных функций MIN() и MAX(). Чтобы протестировать, как это работает, давайте добавим две следующие строчки в таблицу users:

INSERT INTO users (Name, Age) VALUES ('Tanya', 45);
INSERT INTO users (Name, Age) VALUES ('William', 39);

Итак, у нас теперь есть Nick и Tanya старше 40 лет, плюс William и ещё один Nick моложе сорока. Мы могли бы отфильтровать его по условию «WHERE Age > 40», но в демонстрационных целях сделаем это при помощи хранимой процедуры age_over_40(). Вот как это будет выглядеть:

DELIMITER //
CREATE FUNCTION age_over_40 (pAge INT) RETURNS
BOOLEAN
BEGIN
IF pAge > 40 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END//
DELIMITER ;

Этот пример немного отличается от приведённого выше, так что давайте рассмотрим, в чём разница. Во-первых, теперь нет никаких IN или OUT — все параметры функции являются входными. Но зато теперь мы должны определить возвращаемое значение при помощи оператора RETURN. В нашем примере мы возвращаем логическую (Boolean) переменную, которая может принимать значения True или False. Внутри функции мы с вами впервые использовали условный оператор, и проверили, превышает ли значение переданного нам параметра 40. Если да, то мы возвращаем true, если нет — то false. Просто!

Самая потрясающая вещь в функциях — это то, что их можно использовать как часть обычных SQL-запросов, например, вот так:

SELECT Name FROM users WHERE age_over_40(Age);

Этот запрос вернёт все значения Name из таблицы Users, для которых функция age_over_40() вернула значение true. Представьте всё богатство возможностей — вы можете запросить несколько таблиц, написать сложные вложенные условия с множеством проверок, и так далее… Если вам нужны более сложные проверки, возможно, вам больше подойдёт оператор CASE:

DELIMITER //
CREATE FUNCTION describe_name (pName VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
CASE pName
WHEN 'Nick' THEN RETURN 'Short for Nicholas';
WHEN 'William' THEN RETURN 'Often shortened to Will or
Bill';
ELSE RETURN 'I’ve never heard of that name!';
END CASE;
END//
DELIMITER ;

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

Вот и всё, друзья, что я хотел сказать на тему хранимых процедур (и хранимых функций!). Надеюсь, вы увидели, как полезно может быть инкапсуляция сложных запросов в однострочное выражение.