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

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

Открываем phpmyadmin. Выбираем базу данных mytest и нажимаем на её заголовок, либо на значок Browse. Затем переходим на вкладку Routines и создаём новую процедуру, нажав на надпись Add routine.

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

Routine Name (имя процедуры/функции) ReplyToComment .

Type (тип) — процедура. Отличие процедуры от функции в том, что функция всегда возвращает какое-то значение и содержит оператор return.

Parameters (параметры) наша процедура будет принимать два параметра: текст ответа и id комментария, на который мы отвечаем. Оба параметра будут передаваться из нашего клиентского приложения.

Создание параметров процедуры

Создадим первый параметр

Direction указываем направление параметра (IN, OUT, INOUT). В нашей процедуре оба передаваемых параметра будут входящими (IN).

Name (имя параметра) Content .

Type (тип) INT, VARCHAR, DATETIME и так далее. Параметр Content содержит текст ответа, который будет храниться в столбце comment_content. Данная колонка имеет определенный тип, чтобы его определить, открываем таблицу wp_comments и переходим на вкладку Structure, находим нужное нам имя столбца и смотрим на его тип в колонке Type. В данном примере колонка имеет тип - text, этот же тип нужно указать для нашего параметра.

Length/Values (длина или значение) для типа Text данное поле установить нельзя, но обычно здесь указывается длина, например VARCHAR(20), INT(10), либо какое-то значение по умолчанию.

Options в качестве дополнительных опций можно указать текущую кодировку столбца, её так же можно посмотреть на вкладке Structure колонка Collation. Установим значение utf8.

результат

Добавим второй параметр, нажав на кнопку Add parameter.

Direction - IN Name - ComID Type - BIGINT Length/Values - 20 Options - UNSIGNED

Оба параметра созданы, продолжаем заполнять форму.

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

тело процедуры

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

BEGIN END;

Теперь добавим текст запроса, который будет заполнять поля в таблице wp-comments, при добавлении нового комментария (ответа).

BEGIN INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (подставляемые значения); END;

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

DECLARE имя тип (длина) DEFAULT значение по умолчанию;

Так же можно установить значение, для любой переменной используя, оператор SET.

SET имя переменной = значение;

И так создадим три переменных: Author, Email, UsedID, которые будут хранить значения для колонок: comment_author, comment_author_email, user_id.

BEGIN DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; -- Объявили переменную Email DECLARE Email varchar(100); -- Установили значение переменной Email SET Email = "[email protected]"; END;

comment_content данная колонка хранит текст комментария, который передается в процедуру в виде входного параметра Cоntent. Мы не будем создавать отдельную переменную, а просто подставим в VALUES значение входного параметра.

comment_date и comment_date_gmt обе колонки при первом заполнение будут иметь одинаковые значения. Создадим переменную Date и присвоим ей в качестве значения результат, который будет возвращать встроенная функция NOW. Данная функция возвращает текущую дату и время в формате DATETIME.

DECLARE MyCurDate DATETIME DEFAULT NOW();

DECLARE MyCurDate DATETIME; SET MyCurDate = NOW();

comment_approved Одобрен ли комментарий, 1 (да) иначе 0. Создадим переменную Approved, но перед установкой значения сделаем небольшую проверку.

DECLARE Approved varchar(20); IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF;

comment_parent здесь в качестве значения нужно указать id комментария, на который мы отвечаем. ID передается в процедуру как второй входной параметр. Создадим переменную ParentCom и присвоем ей значение переданного параметра.

DECLARE ParentCom varchar(20); SET ParentCom = ComID ;

Остался последний параметр comment_post_id здесь нужно указать id поста, в котором будет размещён наш комментарий. Объявим переменную с именем PostID.

DECLARE PostID BIGINT(20);

на данный момент тело процедуры должно выглядеть так

BEGIN -- блок объявления переменных DECLARE Author tinytext DEFAULT "admin"; DECLARE UserID bigint(20) DEFAULT 1; DECLARE Email varchar(100); DECLARE Date DATETIME DEFAULT NOW(); DECLARE ParentCom varchar(20); DECLARE Approved varchar(20); DECLARE PostID BIGINT(20); -- Установка значений переменных IF Author = "admin" THEN SET Approved = 1; ELSE SET Approved = 0; END IF; SET Email = "[email protected]"; SET ParentCom = ComID ; -- запрос INSERT INTO wp_comments (comment_author, comment_author_email, comment_content, comment_date, comment_date_gmt, comment_post_id, comment_parent, comment_approved, user_id) VALUES (Author, Email, Content, Date, Date, PostID, ParentCom, Approved, UserID); END;

Проще говоря, хранимые процедуры («ХП») - это сохраненные в базе данных процедуры (написанные с помощью SQL и других управляющих операторов), которые могут быть выполнены движком баз данных и вызваны из программного кода, который с этим движком работает. »»» Читать полностью

Хранимые процедуры в MySQL и PHP. Часть 2

Тэйлор Рен (Taylor Ren ), 03.01.2014

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

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

Давайте рассмотрим, как создать ХП на сервере MySQL, как создать пользователя для нее и как назначить ему привилегии на запуск нашей ХП. Затем проверим корректность результата. Для этого я воспользуюсь MySQL Workbench . Можно использовать и другие программы (например, PHPMyAdmin). Вы можете выбрать тот инструментарий, который вам больше подходит.

Допустим, наша таблица выглядит так:

CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Для нашего служащего, которому нужна статистическая информация по зарплатам (средняя, максимальная, минимальная и т.п.) из этой таблицы, мы создадим пользователя "tr" следующим образом:

CREATE USER "tr"@"localhost" IDENTIFIED BY "mypass";

Теперь назначим этому пользователю единственную привилегию EXECUTE в той схеме, где находится таблица salary:

Grant execute on hris.* to tr@`%`

Мы можем убедиться в том, что назначили нужную привилегию, открыв «Users and Privileges» в MySQL Bench:

Теперь создадим саму ХП следующим образом:

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END

После выполнения этой команды в MySQL Workbench, будет создана готовая к использованию ХП avg_sal . Она возвращает среднюю зарплату по таблице salary .

Чтобы проверить, действительно ли пользователь tr может запустить ХП и не имеет доступа к таблице salary , нам нужно переподключиться к серверу MySQL, залогинившись как tr . В MySQL Workbench это можно сделать создав другое соединение и указав нужного пользователя и его пароль .

После подключения из под tr , первое, что мы замечаем, - это то, что пользователь вообще не видит каких-либо таблиц, видит только ХП:

Очевидно, что пользователь tr не может обращаться ни к одной из таблиц (а значит, не может видеть и подробную информацию о зарплатах из таблицы salary), но может запустить созданную нами ХП, которая вернет ему среднюю зарплату по компании:

Call avg_sal(@out); select @out;

Будет отображена средняя зарплата.

Итак, мы выполнили всю подготовительную работу: создали пользователя, назначили ему привилегии, создали ХП и протестировали ее. Теперь посмотрим, как вызывать эту ХП из PHP .

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

При использовании PDO вызов ХП довольно прост. Вот соответствующий PHP-код:

$dbms = "mysql"; // Замените следующие параметры соединения на соответствующие вашему окружению: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

Переменная $res содержит среднюю зарплату по таблице salary . Теперь пользователь может производить дальнейшую обработку вывода с помощью PHP.

Выводы

В этой статье мы рассмотрели давно забытую составляющую баз данных MySQL : хранимые процедуры. Преимущества использования ХП очевидны, но позвольте мне напомнить: Хранимые процедуры позволяют нам применять более строгий контроль доступа к определенным данным, когда это требуется бизнес-логикой.

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

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

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

Тэйлор Рен

Тэйлор - свободный разработчик веб- и десктопных приложений , проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.

Предыдущие публикации:

От автора: чего это вы спите на рабочем месте! Вы не спите, а ждете, пока СУБД выполнит запрос? Так ее нужно разогнать. Хранимые процедуры MySQL применяли? Не знаете как? Ну, тогда просыпайтесь, потому что сейчас мы будем рассматривать как раз эту тему.

Что за процедуры еще?

Если у вас фобия насчет медицинских процедур, то данные структуры «не из той темы». Так что можно не бояться. А если серьезно, то хранимые процедуры – это удобная и полезная для «здоровья» СУБД вещь. Их еще называют «хранимыми функциями MySQL», но это не совсем точное определение. Хотя давайте разбираться со всем по порядку.

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

В MySQL вызов хранимой процедуры означает, что вписанные в ее код запросы обработаются лишь наполовину. И то, если будет изменены значения их параметров. Но не все так идеально. Опишем сначала положительные стороны использования процедур:

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

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

Повышение скорости работы сервера – за счет кэширования и объединения запросов.

В MySQL хранимые процедуры в теории являются структурами, относящимся к более «высоким материям» — программированию СУБД. Так что мы с вами (как профессионалы) хоть потихоньку, но . Но вернемся к процедурам, и опишем негативные стороны их использования:

Нагрузка на сервер БД повышается – большая часть кода процедур выполняется на стороне сервера. Данная СУБД построена по модели «клиент-сервер», в которой задействованы несколько устройств.

Усложняется манипуляция данными – при разработке приложений придется прописывать часть кода и на клиентской стороне.

Усложняется процесс переноса БД на другие рельсы (СУБД).

Процедуры в phpMyAdmin

Для начала рассмотрим применение в MySQL хранимых процедур на примере phpMyAdmin. Таким образом нам будет легче разобраться с этим типом структур. Стартуем!

Запускаем программную оболочку, справа выбираем тестовую БД. У меня таковой является база world. Затем в главном меню сверху переходим по вкладке «Процедуры». Здесь жмем на «Добавить процедуру».

После этого появится окно диалога «Добавить процедуру». Заполняем в ней все указанные на снимке поля. Указываем имя процедуры, тип. В графе «Определение» вводим учетную запись пользователя, и в комментариях (необязательно) указываем для себя, что это всего лишь пример хранимой процедуры.

Уже на этом этапе мы знакомимся с особенностями синтаксиса создания хранимых процедур MySQL. В поле «Определение» прописываем тело структуры. Обратите внимание, что выполняемый запрос находится между ключевыми словами BEGIN и END:

BEGIN SELECT "HELLO, WORD!"; END

Данный запрос не выполняет никаких действий с базой, а лишь выводит надпись. Это мы указали в поле «Доступ к SQL данным».

Для окончания создания нашей первой процедуры жмем внизу «Ок». После этого программа выводит «зелененькое» сообщение об удачном выполнении запроса. Его код представлен ниже. В MySQL хранимые процедуры и функции создаются с помощью специальной команды CREATE PROCEDURE. Но об этом позже.

Теперь запустим созданную структуру на выполнение. Для этого в разделе «Процедуры» нажимаем ссылку «Выполнить». Но что это за безобразие! Куда делся наш любимый «зелененький»? Почему программа «ругается» и «кричит», что ей не хватает выделенной памяти?

Куда смотрел автор данной публикации…! Извините, немного запутался. Ведь автор – это я . Спокойствие, сейчас все исправим! Такая ошибка возникает из-за того, что в главном конфигурационном файле значение параметра thread_stack оставлено без изменений. По умолчанию для каждого потока выделяется 128 Kb. Выделенного лимита оперативки для выполнения простых запросов вполне хватает, но для процедур мало.

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

Перейдите в конфигурационный файл my.ini, и увеличьте лимит оперативки, установленной для каждого потока, до 256 kb. Теперь еще раз запустите на выполнение созданную процедуру. В этот раз все прошло как надо, и программа вернула результат без ошибки.

Обратите внимание, что для вызова используется команда CALL с указанием имени процедуры и принимаемых параметров (в скобках).

Более сложный пример

Но все же phpMyAdmin по своим возможностям больше подходит для быстрого составления процедур. А для разработки в MySQL хранимой процедуры с динамическим количеством аргументов (например) потребуется более удобное ПО. Почему:

phpMyAdmin не хочет нормально «понимать» процедуры, созданные не через специальный конструктор.

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

Если вы внимательно следите за моими публикациями и выполняете все прописанные в них «пожелания», то у вас должен уже стоять MySQL Administrator. В привязку к нему осталось скачать MySQL Query Browser по этой ссылке. Эти две программы лучше использовать вместе: в первой создавать процедуры, а в другой тестировать их. Поехали:

Слева вверху переходим через вкладку «Catalog».

Выбираем нужную БД, и в верхнем меню жмем на «Stored procedures», а внизу на «Create Stored Proc»

В появившемся окне редактора вводим код процедуры и жмем «Execute SQL».

CREATE DEFINER=`roman`@`localhost` PROCEDURE `proc5`() BEGIN declare a int; set a="SELECT COUNT(*) FROM city as a"; if(a > 1000)THEN SELECT "<1000"; ELSE SELECT ">1000"; END IF; END

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

Для запуска процедуры заходим в MySQL Query Browser. Сначала вводим свою учетку и пароль, а затем слева в «Object Explorer» находим папку с нужной базой. Остальная очередность действия показана на следующем снимке.

Запуск процедуры в PHP

Теперь рассмотрим, как происходит в PHP вызов хранимой процедуры MySQL. Для этого нам придется немного «перекроить» код нашего предыдущего примера. Мы добавим в процедуру параметр на вывод, а также изменим код запроса:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc6`(out col decimal) BEGIN SELECT COUNT(*) into col FROM city; END

Для вызова из файла PHP процедуры и вывода результата будем использовать возможности класса PDOStatement, созданного специально для работы с БД через SQL

Этот класс реализован сравнительно недавно, и поддерживается PHP, начиная с версии 5.1.0. Советую перед использованием проверить используемую версию языка c помощью встроенной функции phpversion().

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

Введение

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

Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.

За

• Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
• Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
• Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
• Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

• Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
• Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
• Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
• Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

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

Шаг 1: Ставим ограничитель

Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.

Шаг 2: Как работать с хранимыми процедурами

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

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//

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

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

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

• Language: в целях обеспечения переносимости, по умолчанию указан SQL.
• Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
• SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
• Comment: в целях документирования, значение по умолчанию - ""

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

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

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);

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

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Шаг 3: Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

• CREATE PROCEDURE proc1 (): пустой список параметров
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

Естественно, вы можете задавать несколько параметров разных типов.

Пример параметра IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//

Пример параметра OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //

Пример параметра INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.

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

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Давайте объявим несколько переменных:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //

Шаг 5: Структуры управления потоками

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //

Конструкция CASE

CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //

Конструкция WHILE

Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Шаг 6: Курсоры

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

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

DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/

В этом примере мы проведем кое-какие простые операции с использованием курсора:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //

У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:

• Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
• Доступен только для чтения: курсоры нельзя изменять.
• Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.

Заключение

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

Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.

Если по простому, то хранимые процедуры («ХП ») представляют собой хранимые в базе данных процедуры (написанные с помощью SQL и других операторов), которые можно назвать движком базы данных или связанным языком программирования.

В этой статье я расскажу, как с помощью MySQL создать ХП и выполнить ее на сервере MySQL и через PHP .

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

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

Большинство из нас хорошо знакомы с обычными настройками, которые позволяют создать приложение базы данных: создание базы данных, создание таблиц, создание индексов, CRUD-данные, формирование запросов со стороны клиента и дальнейшая обработка, если это необходимо .

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

Я могу назвать, по крайней мере, четыре веских причины использовать ХП в приложениях баз данных. Во-первых , она уменьшает сетевой трафик и нагрузку на сервер.

В стандартном PHP веб-приложении базы данных существует четыре составляющих:

• Клиентский уровень , который обычно представлен веб-браузером. Он предоставляет возможность взаимодействия с пользователем и позволяет вводить данные через интерфейс пользователя;
• Уровень веб-сервера , на котором обрабатываются запросы пользователей и отправляются ответы назад на клиентский уровень;
• PHP-уровень , который обрабатывает все PHP-составляющие, создает логику приложений и генерирует PHP часть ответа;
• Уровень базы данных , на котором обрабатываются все запросы к базе данных, включая (но, не ограничиваясь только ими) запросы SELECT , операторы INSERT , и т.д.

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

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

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

Во-вторых , это повышает производительность. ХП хранятся и запускаются непосредственно на сервере MySQL . Они могут быть предварительно скомпилированы и проанализированы на сервере базы данных.

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

Это похоже на исполнение интерпретируемого языка (на стороне клиента) и компилируемого языка (на стороне сервера базы данных). А мы знаем, что скомпилированная программа будет работать быстрее.

В-третьих , написанные однажды ХП выполняются где угодно. SQL является стандартной и 100% независимой платформой. Она опирается только на сервер баз данных. Подумайте, как много существует различных языков/библиотек, которые используются для работы с базой данных.

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

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

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

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

Мы знаем, что MySQL имеет систему полного управления привилегиями. Очевидно, что в этом случае мы не можем предоставить этому сотруднику отдела кадров даже привилегию SELECT (потому что, если мы это сделаем, это будет означать, что он / она может видеть детальную зарплату каждого сотрудника).

Но если он /она не может получить доступ к таблице зарплаты, то, как этот сотрудник сможет получить сводные данные по зарплате? Как мы можем обеспечить работнику отдела кадров доступ к этой информации без ущерба для кадровой политики компании?

Решить эту проблему можно с помощью хранимой процедуры, которая выдает запрашиваемую информацию и гранты сотруднику, который имеет привилегию EXECUTE . (Подробный список и описание привилегий MySQL вы можете найти в официальной документации .

В данном случае ХП является мостом между пользователем (нашим сотрудником отдела кадров) и таблицей (salary ), к которой пользователь не имеет прямого доступа:

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

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

• Отсутствие контроля версии самой ХП. Когда изменяется версия ХП, она изменяется без сохранения истории действий для предыдущих версий на стороне сервера. Это может создать некоторые неудобства, когда пользователь хотел бы откатить изменения.

  Я предлагаю в таких случаях писать ХП на стороне клиента и обеспечивать контроль версии здесь. Когда ХП готова, код можно легко скопировать, скажем, в MySQL Workbench и создать процедуру на стороне сервера. Таким образом, мы можем получить некоторую степень контроля версии.

• Отсутствие простого способа «синхронизировать » введение в силу изменений и заставить все субъекты использовать последнюю версию, в частности, когда каждый из членов команды имеет свою собственную локальную базу данных для разработки и тестирования.

  Контроль версий может стать решением, но все еще требуется ручное вмешательство через обновление локальной копии ХП на локальном сервере БД. Другой способ заключается в использовании «условного объекта ». Членов команды можно разделить так, чтобы хотя бы один человек отвечал на поддержание ХП и решения, использующие ее вызов через код.

  Остальные участники группы, которым нужны результаты исполнения ХП, могут разрабатывать и тестировать свою часть с помощью условного объекта, предполагая, что «условные » вызовы ХП будут выдавать нужный результат. На более позднем этапе, можно объединить различные части, удалив условный код.

• Сложности с созданием резервных копий/экспортом. ХП находится на стороне сервера. Разработчики будут иметь только основные привилегии (SELECT , EXECUTE и т.д.) и никаких прав администратора для резервного копирования и экспорта. В некотором смысле, это является вовсе не недостатком, а скорее одним из основополагающих аспектов безопасности баз данных.

  И обойти это ограничение нельзя, это и не рекомендуется. Предполагается, что в команде будет назначен отдельный администратор базы данных, который будет заниматься этой работой. Регулярное резервное копирование БД может также служить для целей экспорта (и импорта).

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

Давайте рассмотрим, как создать ХП в сервере MySQL , создать пользователя, назначить ему привилегии и запустить (под этим логином) ХП, чтобы проверить, правильно ли обрабатываются данные. В своей рабочей среде я использую MySQL Workbench .

Существуют также и другие инструменты (PHPMyAdmin , например), так что вы можете выбрать то, что лучше всего подходят именно вам.

Предположим, у нас есть такая таблица:

CREATE TABLE `salary` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, PRIMARY KEY (`empid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Сперва мы создаем пользователя ‘tr’ для сотрудника отдела кадров, который должен получить из этой таблицы сводную информацию о зарплате (средняя зарплата по фирме, максимальная, минимальная и т.д.):

CREATE USER "tr"@"localhost" IDENTIFIED BY "mypass";

Этому пользователю мы назначаем только привилегию EXECUTE для таблицы, в которой содержатся данные по зарплате:

grant execute on hris.* to tr@`%`

Необходимость предоставления тех или иных привилегий мы можем уточнить, ознакомившись с разделом «Пользователи и привилегии » документации MySQL :

DELIMITER $$ CREATE PROCEDURE `avg_sal`(out avg_sal decimal) BEGIN select avg(sal) into avg_sal from salary; END

ПРИМЕЧАНИЕ : Все вышеперечисленные операции требует прав администратора на сервере MySQL .

После выполнения команды в MySQL Workbench , ХП avg_sal будет создана и готова к вызову. Она выводит среднюю зарплату из таблицы salary .

Чтобы проверить, может ли пользователь tr запустить ХП, не получая при этом доступ к таблице salary , мы можем сменить свою роль, войдя на сервер MySQL под логином пользователя tr. Это можно сделать, создав новое соединение в MySQL Workbench с другими логином и паролем.

После входа в систему от имени пользователя tr первое, что мы увидим, это то, что мы не можем видеть таблицы, нам доступны только ХП:

call avg_sal(@out); select @out;

В результате выводится средняя зарплата.

К этому моменту мы выполнили всю подготовительную работу: создали пользователя, назначили ему привилегии, создали ХП и проверили ее исполнение. Далее мы покажем, как вызвать эту ХП из PHP.

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

С помощью PDO вызов ХП выполняется довольно просто. Код РНР выглядит следующим образом:

$dbms = "mysql"; //Replace the below connection parameters to fit your environment $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);

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

Заключение

В этой статье мы рассмотрели неотъемлемый компонент базы данных MySQL : хранимые процедуры.

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

Мы также показали основные этапы создания хранимых процедур, создания пользователя и назначения привилегий, и как вызывать ХП через PHP.

Данная статья не охватывает все аспекты, которые касаются хранимых процедур. Некоторые важные моменты, такие как параметры ввода / вывода, операторы управления, курсоры, полный синтаксис и т.д. в этой короткой статье мы не рассматривали.

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