LXF77:PHP - История изменений

Yaleks: викификация

2008-12-11T09:50:05Z

викификация

Yaleks: Новая: {{Цикл/PHP}} == PHP: Хранимые процедуры == ''часть 2 Как будто нам недостаточно изучения SQL! MySQL 5.0 приносит с со...

2008-12-11T09:49:34Z

Новая: {{Цикл/PHP}} == PHP: Хранимые процедуры == ''часть 2 Как будто нам недостаточно изучения SQL! MySQL 5.0 приносит с со...

Новая страница

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

{{Врезка
|Заголовок=где тут хранимые процедуры ?
|Содержание=Новые возможности MySQL 5.0 позволяют создавать
хранимые процедуры, используя диалект SQL под
названием SQL:2003. Сейчас идёт работа по созданию
системы подключаемых хранимых процедур, которая
представляет собой открытый API, позволяющий
разрабатывать хранимые процедуры на любом языке
программирования.

Разработчики MySQL утверждают, что, скорее всего
одним из первых поддерживаемых языков
программирования будет PHP (Урааа!), поскольку он
разработан так, что его довольно просто внедрять. Если
они справятся с этой задачей, мы сможем из сценариев
на PHP вызывать хранимые процедуры MySQL, которые в
свою очередь написаны на PHP. Они смогут выполнять
обратные вызовы к базе данных и выполнять серьёзную
обработку перед тем, как вернуть окончательный
результат. Если вас напрягает, что какой-то язык X не
умеет делать что-то, что умеет PHP, то такие хранимые
процедуры могут стать решением вашей проблемы...
|Ширина=200px}}
Я хотел бы сделать важное заявление: PHP не всегда самый
лучший язык программирования. Да, как правило, он лучший, но кроме него существует ещё и множество других,
подходящих в каждой конкретной ситуации. А при использовании
нескольких языков одновременно сразу же возникает вопрос – как
правильно взаимодействовать с базой данных. Возможно, вы мне не
поверите, но с MySQL 5.0 такой проблемы не существует.

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

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

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

=== Следующие процедуры ===
Давайте рассмотрим простую хранимую процедуру, которая умеет
обновлять возраст пользователя. Для начала создадим таблицу пользователей, примерно вот так:
<source lang="sql">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);</source>
Процедура будет менять возраст пользователя с заданным именем,
делая его на год старше. Как и с триггерами, нам потребуется изменить
разделитель команд с ; на // для того, чтобы ввести многострочную процедуру как единое целое.

Итак:
<source lang="sql">DELIMITER //
CREATE PROCEDURE get_older (IN pName VARCHAR(255))
BEGIN
UPDATE users SET Age = Age + 1 WHERE Name = pName;
END//
DELIMITER ;</source>
После смены разделителя мы создаём процедуру get_older и объявляем, что она принимает параметр типа VARCHAR(255) под названием pName. Обратите внимание, параметры BEGIN и END используются
для определения границ процедуры, но их можно опускать, если процедура состоит из одной единственной строки (как у нас). Наша процедура
обновляет все строки в таблице, удовлетворяющие указанному условию
(зависящему от значения параметра), увеличивая значение поля age на
единицу. В конце определения процедуры мы возвращаем определение
разделителя к стандартному значению.

Как вызвать нашу процедуру? Нет ничего проще.
<source lang="sql">SELECT * FROM users;
CALL get_older('Nick');
SELECT * FROM users;</source>
Два оператора SELECT нужны для того, чтобы увидеть, как изменилось значение Age после вызова get_older(), для работы хранимой
процедуры они не нужны. Как видите, get_older() работает именно
так, как вы ожидали, и её достаточно просто использовать. Если вас
интересует, что будет, если передать параметр, который не является VARCHAR(255), то ответ прост – MySQL преобразует его к типу
VARCHAR.

=== Чтение данных ===
Переместимся в более сложные области и добавим ещё одного пользователя в нашу таблицу:
<source lang="sql">INSERT INTO users (Name, Age) VALUES ('Nick', 32);
SELECT * FROM users;</source>
Теперь у нас есть два Ника, с ID равным 1 и 2. Мы можем создать
хранимую процедуру для возвращения значений из запроса SELECT
очень простым способом.
<source lang="sql">DELIMITER //
CREATE PROCEDURE get_user (IN pID VARCHAR(255))
BEGIN
SELECT * FROM users WHERE ID = pID;
END//
DELIMITER ;</source>
Здесь используется та же самая идея, за тем исключением, что
мы указываем ID для точного определения пользователя и используем оператор SELECT, чтобы извлечь информацию из таблицы. Вызов
get_user() в данном случае возвратит одну строку, но если бы так
случилось, что к условию подошли бы несколько строк, то все они были
бы получены.

Второй способ вернуть данные из процедуры – это использование
выходных (OUT) параметров. До сих пор мы помечали все параметры
как IN, что на самом деле не обязательно, поскольку MySQL считает все
параметры входящими по умолчанию. Если вы когда-нибудь использовали CORBA, COM+ или D-BUS, то понятие OUT-параметров вам знакомо. Если же нет, то всё станет очевидно после следующего примера.
Давайте попробуем вернуть значение из процедуры при помощи выходного параметра:
<source lang="sql">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 ;</source>
Важным является тот момент, что мы немного изменили работу
запроса, добавив оператор LIMIT 1. Он потребовался по той причине,
что в качестве OUT-параметра мы можем вернуть одно и только одно
значение, и если SQL-запрос вдруг вернёт несколько строк, то произойдёт ошибка. Вызов этой процедуры немного отличается от приведённого
выше, так как нам надо передать два параметра, один из которых является переменной SQL и может быть опрошен позднее. Итак:
<source lang="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)</source>

=== Inline-функции ===
{{Врезка
|Заголовок=Подсказки
|Содержание=
* Если вам нужно вернуть несколько значений сразу, то вы можете использовать столько OUT-параметров, сколько хотите. Для этого просто напишите оператор SELECT ... INTO и укажите в нём список из всех переменных, например “INTO foo, bar”.
* Создавать функции с именам функций, уже существующих в MySQL – это не очень хорошая идея. Кроме того, у вас возникнут проблемы, если имена переменных совпадут с названиями таблиц или отдельных полей в них.
* Система преобразования типов MySQL может успешно преобразовать друг в друга переменные почти всех типов данных, так что если ваша хранимая процедура ожидает параметр типа VARCHAR(255), а вы передадите ей CHAR(50), то никаких вопросов не возникнет. Но не забывайте, что любое преобразование типов сказывается на производительности, поэтому почему бы всегда не передавать процедурам именно то, что вы и планировали им передавать с самого начала?
|Ширина=250px}}
Процедуры хорошо работают, если вам нужно изменить несколько
вещей одной строкой кода, но из них очень неудобно возвращать значения переменных.

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

В MySQL процедуры не могут быть вызваны inline, и хотя они умеют
возвращать значения при помощи OUT-переменных, вам потребуется
выполнить ещё один запрос, чтобы прочитать их. Альтернативный вариант - использовать функции, разработанные так, что их можно вызывать
как часть более длинного SQL-запроса, наподобие встроенных функций
MIN() и MAX(). Чтобы протестировать, как это работает, давайте добавим две следующие строчки в таблицу users:
<source lang="sql">INSERT INTO users (Name, Age) VALUES ('Tanya', 45);
INSERT INTO users (Name, Age) VALUES ('William', 39);</source>
Итак, у нас теперь есть Nick и Tanya старше 40 лет, плюс William
и ещё один Nick моложе сорока. Мы могли бы отфильтровать его по
условию “WHERE Age > 40”, но в демонстрационных целях сделаем это при помощи хранимой процедуры age_over_40(). Вот как это
будет выглядеть:
<source lang="sql">DELIMITER //
CREATE FUNCTION age_over_40 (pAge INT) RETURNS
BOOLEAN
BEGIN
IF pAge > 40 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END//
DELIMITER ;</source>
Этот пример немного отличается от приведённого выше, так что
давайте рассмотрим, в чём разница. Во-первых, теперь нет никаких IN
или OUT – все параметры функции являются входными. Но зато теперь
мы должны определить возвращаемое значение при помощи оператора
RETURN. В нашем примере мы возвращаем логическую (Boolean) переменную, которая может принимать значения True или False. Внутри функции мы с вами впервые использовали условный оператор, и проверили,
превышает ли значение переданного нам параметра 40. Если да, то мы
возвращаем true, если нет – то false. Просто!

Самая потрясающая вещь в функциях – это то, что их можно использовать как часть обычных SQL-запросов, например, вот так:
<source lang="sql">SELECT Name FROM users WHERE age_over_40(Age);</source>
Этот запрос вернёт все значения Name из таблицы Users, для
которых функция age_over_40() вернула значение true. Представьте
всё богатство возможностей – вы можете запросить несколько таблиц,
написать сложные вложенные условия с множеством проверок, и так
далее... Если вам нужны более сложные проверки, возможно, вам больше подойдёт оператор CASE:
<source lang="sql">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 ;</source>
Для самых продвинутых пользователей существуют циклы, но вам стоит хорошенько подумать, чтобы найти для них
достойное применение.

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

@@ Строка 1: / Строка 1: @@
 {{Цикл/PHP}}
 == PHP: Хранимые процедуры ==
 ''часть 2 Как будто нам недостаточно изучения SQL! MySQL 5.0 приносит с собой ещё и новый способ программирования. Специалист по базам данным '''Пол Хадсон''' (Paul Hudson) представляет хранимые процедуры.''
@@ Строка 29: / Строка 30: @@
 лучший язык программирования. Да, как правило, он лучший, но кроме него существует ещё и множество других,
 подходящих в каждой конкретной ситуации. А при использовании
-нескольких языков одновременно сразу же возникает вопрос – как
+нескольких языков одновременно сразу же возникает вопрос — как
 правильно взаимодействовать с базой данных. Возможно, вы мне не
 поверите, но с MySQL 5.0 такой проблемы не существует.
@@ Строка 44: / Строка 45: @@
 Теперь у нас появилось решение! Вам больше не нужно думать о
 том, чтобы помногу раз копировать тысячи строк SQL-кода. Поддержка
-множества копий одного и того же запроса – это жуткий кошмар, тысячи человеко-часов и питательная среда для ошибок. Нет, самым
+множества копий одного и того же запроса — это жуткий кошмар, тысячи человеко-часов и питательная среда для ошибок. Нет, самым
 лучшим вариантом было бы сохранить SQL прямо в базе данных и в
 дальнейшем вызывать его изо всех приложений. Вместо того, чтобы
@@ Строка 62: / Строка 63: @@
 ваш арсенал администратора базы данных. В предыдущем номере мы с
 вами рассматривали, как создавать и использовать триггеры на таблицах,
-так что теперь вы умеете наблюдать за выполняемыми запросами и контролировать их. Хорошая новость: триггеры – это не более чем хранимые
+так что теперь вы умеете наблюдать за выполняемыми запросами и контролировать их. Хорошая новость: триггеры — это не более чем хранимые
 процедуры, разработанные для запуска в специфические моменты. Таким
-образом, триггеры – это подмножество хранимых процедур, не позволяющее получать и передавать ваши переменные. Так что если вы изучили
+образом, триггеры — это подмножество хранимых процедур, не позволяющее получать и передавать ваши переменные. Так что если вы изучили
 учебник из февральского номера, у вас уже есть фора.
@@ Строка 75: / Строка 76: @@
 Процедура будет менять возраст пользователя с заданным именем,
 делая его на год старше. Как и с триггерами, нам потребуется изменить
-разделитель команд с ; на // для того, чтобы ввести многострочную процедуру как единое целое.
+разделитель команд с; на // для того, чтобы ввести многострочную процедуру как единое целое.
 Итак:
@@ Строка 98: / Строка 99: @@
 процедуры они не нужны. Как видите, get_older() работает именно
 так, как вы ожидали, и её достаточно просто использовать. Если вас
-интересует, что будет, если передать параметр, который не является VARCHAR(255), то ответ прост – MySQL преобразует его к типу
+интересует, что будет, если передать параметр, который не является VARCHAR(255), то ответ прост — MySQL преобразует его к типу
 VARCHAR.
@@ Строка 120: / Строка 121: @@
 бы получены.
-Второй способ вернуть данные из процедуры – это использование
+Второй способ вернуть данные из процедуры — это использование
 выходных (OUT) параметров. До сих пор мы помечали все параметры
 как IN, что на самом деле не обязательно, поскольку MySQL считает все
@@ Строка 160: / Строка 161: @@
 Как Pascal или Fortran, MySQL различает процедуры и функции.
-Первые – это команды, а вторые – запросы. Если вы изучали компьютерные науки, то вам должно быть знакомо понятие «Command-Query
+Первые — это команды, а вторые — запросы. Если вы изучали компьютерные науки, то вам должно быть знакомо понятие «Command-Query
-Separation”.
+Separation».
 В MySQL процедуры не могут быть вызваны inline, и хотя они умеют
 возвращать значения при помощи OUT-переменных, вам потребуется
-выполнить ещё один запрос, чтобы прочитать их. Альтернативный вариант - использовать функции, разработанные так, что их можно вызывать
+выполнить ещё один запрос, чтобы прочитать их. Альтернативный вариант — использовать функции, разработанные так, что их можно вызывать
 как часть более длинного SQL-запроса, наподобие встроенных функций
 MIN() и MAX(). Чтобы протестировать, как это работает, давайте добавим две следующие строчки в таблицу users:
@@ Строка 172: / Строка 173: @@
 Итак, у нас теперь есть Nick и Tanya старше 40 лет, плюс William
 и ещё один Nick моложе сорока. Мы могли бы отфильтровать его по
-условию “WHERE Age > 40”, но в демонстрационных целях сделаем это при помощи хранимой процедуры age_over_40(). Вот как это
+условию «WHERE Age > 40», но в демонстрационных целях сделаем это при помощи хранимой процедуры age_over_40(). Вот как это
 будет выглядеть:
 <source lang="sql">DELIMITER //
@@ Строка 187: / Строка 188: @@
 Этот пример немного отличается от приведённого выше, так что
 давайте рассмотрим, в чём разница. Во-первых, теперь нет никаких IN
-или OUT – все параметры функции являются входными. Но зато теперь
+или OUT — все параметры функции являются входными. Но зато теперь
 мы должны определить возвращаемое значение при помощи оператора
 RETURN. В нашем примере мы возвращаем логическую (Boolean) переменную, которая может принимать значения True или False. Внутри функции мы с вами впервые использовали условный оператор, и проверили,
 превышает ли значение переданного нам параметра 40. Если да, то мы
-возвращаем true, если нет – то false. Просто!
+возвращаем true, если нет — то false. Просто!
-Самая потрясающая вещь в функциях – это то, что их можно использовать как часть обычных SQL-запросов, например, вот так:
+Самая потрясающая вещь в функциях — это то, что их можно использовать как часть обычных SQL-запросов, например, вот так:
 <source lang="sql">SELECT Name FROM users WHERE age_over_40(Age);</source>
 Этот запрос вернёт все значения Name из таблицы Users, для
 которых функция age_over_40() вернула значение true. Представьте
-всё богатство возможностей – вы можете запросить несколько таблиц,
+всё богатство возможностей — вы можете запросить несколько таблиц,
 написать сложные вложенные условия с множеством проверок, и так
-далее... Если вам нужны более сложные проверки, возможно, вам больше подойдёт оператор CASE:
+далее… Если вам нужны более сложные проверки, возможно, вам больше подойдёт оператор CASE:
 <source lang="sql">DELIMITER //
 CREATE FUNCTION describe_name (pName VARCHAR(255))