MySQL хранимые процедуры
Stored procedures - что это?
Хранимые процедуры появились начиная с 5 версии MySQL. Они позволяют автоматизировать сложные процессы на уровне MySQL, нежели использовать для этого внешние скрипты. Это даёт нам наиболее высокую скорость выполнения, т.к. мы не гоняем большое количество запросов, а всего лишь один раз вызываем ту или иную процедуру (или функцию).
Что для этого нужно? Установите MySQL сервер версии 5 или выше (dev.mysql.com/downloads). Процедуры можно создавать как запросы, например через командную строку MySQL, но для удобства советую скачать MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Данный пакет включает в себя три программы - MySQL Administrator, MySQL Query Browser и MySQL Migration Toolkit. Нам понадобятся первые две. (Хотя можно обойтись одним MySQL Query Browser, но все эти $$ в хранимых процедурах иногда могут сбить с толку).
Первая хранимая процедура
Итак, открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую схему (базу данных): щелкните Catalogs, выберите Create New Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например db). Откройте только что созданную схему, выберите вкладку Stored procedures и щелкните кнопку Create Stored Proc. Назовите свою процедуру procedure1. В тело процедуры (между BEGIN и END) впишите следующее:
SELECT "This is my stored procedure" ;
И нажмите Execute SQL - процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:
CALL procedure1() ;
Вуала! Поздравляю.
Переменные в MySQL
Для того, чтобы извлечь каку-то пользу от хранимых процедур в MySQL, вам придется поработать с переменными. Так как это не входи в рамки данной статьи, покажу лишь несколько примеров.
Простые переменные
DECLARE iVar INT DEFAULT 0; SELECT COUNT(* ) INTO iVar FROM `data` ;
Системные переменные
SET @iVar = 5 ; SELECT @iVar;
Разница между простыми и системными переменными в том, что системные переменные доступны из вне хранимой процедуры. То есть, чтобы извлечь какие-то данные нужно пользоваться системными, а переменные которые нужны только внутри процедуры должны быть простыми.
Параметры в хранимых процедурах
Здесь тоже всё достаточно просто. Изменяем первую строку, объявляющая саму процедуру:
CREATE PROCEDURE `procedure1` (IN iInput1 INT, IN iInput2 INT)
Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:
Условия, Циклы. IF THEN ELSE, WHILE
Условия и циклы вам обязательно понадобятся при написании комплексных хранимых процедур, но зацикливаться на этой теме не буду. Думаю хоть какие-то навыки программирования у вас есть, так что покажу всего лишь синтаксис.
IF условие THEN действие; ELSE действие; END IF ;
WHILE условие DO действие; END WHILE;
Простой пример
Один из хороших случаев применения хранимых процедур - тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threads
CREATE TABLE `threads` ( `id` INT NOT NULL AUTO_INCREMENT , `title` VARCHAR(255 ) NOT NULL , `tag` VARCHAR(255 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM;
Здесь title у нас будет заголовком новой темы. Ну и таблица, например с различными статистическими переменными сайта, в том числе общее количество тем в форме.
CREATE TABLE `variables` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(255 ) NOT NULL , `value` INT NOT NULL DEFAULT 0, PRIMARY KEY ( `id` ) ) ENGINE = MYISAM;
Тут вроде всё понятно, допустим у нас там есть запись с name = threads и value = 0. Создадим новую хранимую процедуру procedure2.
Объяснять особо нечего, просто два запроса объединили в один. Теперь мы можем вызвать эту процедуру таким образом:
CALL procedure2("My new thread" ) ;
Таким образом, вместо того, чтобы передать два или больше запросов (например через php), мы можем передать один - оптимизация, чистый код и можно изменить в любой момент не затрагивая другие скрипты.
Курсоры (MySQL Cursors)
Курсоры позволяют пройтись по всем полученным результатам запроса. На теории объяснить сложно, покажу на практике. Добавим еще одну таблицу к нашей базе данных - hits:
CREATE TABLE `tags` ( `id` INT NOT NULL AUTO_INCREMENT , `tag` VARCHAR(255 ) NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE = MYISAM
Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | CREATE PROCEDURE `procedure3` () BEGIN DECLARE done INT DEFAULT 0; DECLARE sTag VARCHAR(255 ) ; DECLARE iCount INT DEFAULT 0; DECLARE rCursor CURSOR FOR SELECT `tag` FROM `threads` WHERE 1 ; DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done= 1 ; OPEN rCursor; FETCH rCursor INTO sTag; WHILE done = 0 DO SELECT COUNT(* ) INTO iCount FROM `tags` WHERE `tag` = sTag; IF iCount = 0 THEN INSERT INTO `tags` (`tag` ) VALUES (sTag) ; END IF ; FETCH rCursor INTO sTag; END WHILE; CLOSE rCursor; END |
Подробно. Процедура пройдет через каждую тему, каждый тег пробьет по таблице tags, и если данный тег отсутствует, то она его добавит.
Курсор для запроса SELECT, который выберет теги из всех тем (WHERE 1). После курсора объявляем что-то вроде исключения - что делать, когда результаты кончатся (SQLSTATE ‘02000′ означает это окончание). В этом случае мы в переменную done запишем 1, чтобы в последствии выйти из цикла.
Открываем курсор, и получаем первую запись. Дальше в цикле - Выбираем количество совпадений из таблицы тегов для текущего тега и помещаем результат в переменную iCount. Если результатов нет, то запросом INSERT вставляем новый тег.
В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.
Извлечение данных
Вспомним системные переменные и рассмотрим еще одну манипуляцию над нашими таблицами - получить общее количество тегов и тем. Перейдем сразу к процедуре:
| 1 2 3 4 5 6 7 8 9 10 | CREATE PROCEDURE `procedure4` () BEGIN DECLARE iTags INT DEFAULT 0; DECLARE iThreads INT DEFAULT 0; SELECT COUNT(* ) INTO iTags FROM `tags` ; SELECT COUNT(* ) INTO iThreads FROM `threads` ; SET @tags = iTags, @threads = iThreads; END |
Объявляем две переменных - iTags - количество тегов, и iThreads - общее количество тем.
Далее два простых запроса на выборку, заполняя наши переменные. Ну и в конце присваиваем системным переменным значения текущих простых переменных. При вызове данная процедура ничего не возвращает, но после ее вызова мы можем считать требуемые значения из системных переменных:
CALL procedure4() ; SELECT @tags, @threads;
Проще говоря, хранимые процедуры («ХП») - это сохраненные в базе данных процедуры (написанные с помощью 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 и т.д.
Предыдущие публикации:
Представляем вашему вниманию новый курс от команды The Codeby - "Тестирование Веб-Приложений на проникновение с нуля". Общая теория, подготовка рабочего окружения, пассивный фаззинг и фингерпринт, Активный фаззинг, Уязвимости, Пост-эксплуатация, Инструментальные средства, Social Engeneering и многое другое.
Хранимые процедуры MySQL (часть 1)
Продолжаем работать базой данных 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;
От автора: чего это вы спите на рабочем месте! Вы не спите, а ждете, пока СУБД выполнит запрос? Так ее нужно разогнать. Хранимые процедуры MySQL применяли? Не знаете как? Ну, тогда просыпайтесь, потому что сейчас мы будем рассматривать как раз эту тему.
Что за процедуры еще?
Если у вас фобия насчет медицинских процедур, то данные структуры «не из той темы». Так что можно не бояться. А если серьезно, то хранимые процедуры – это удобная и полезная для «здоровья» СУБД вещь. Их еще называют «хранимыми функциями MySQL», но это не совсем точное определение. Хотя давайте разбираться со всем по порядку.
Хранимые процедуры позволяют значительно оптимизировать работу сервера и повысить скорость его работы, поскольку их код после первого выполнения сохраняется в кэше оперативки. При всех последующих вызовах процедура будет извлекаться из кэша, а не отправляться снова на выполнение.
В MySQL вызов хранимой процедуры означает, что вписанные в ее код запросы обработаются лишь наполовину. И то, если будет изменены значения их параметров. Но не все так идеально. Опишем сначала положительные стороны использования процедур:
Инкапсуляция функциональности – весь код хранится в одном месте, что облегчает доступ к нему для других приложений. В этом хранимые процедуры схожи с программными функциями.
Изоляция доступа к данным – все пользователи получают доступ не к строкам таблиц, а только к хранимым процедурам. Что в свою очередь повышает уровень защищенности всех данных.
Повышение скорости работы сервера – за счет кэширования и объединения запросов.
В MySQL хранимые процедуры в теории являются структурами, относящимся к более «высоким материям» — программированию СУБД. Так что мы с вами (как профессионалы) хоть потихоньку, но . Но вернемся к процедурам, и опишем негативные стороны их использования:
Нагрузка на сервер БД повышается – большая часть кода процедур выполняется на стороне сервера. Данная СУБД построена по модели «клиент-сервер», в которой задействованы несколько устройств.
Усложняется манипуляция данными – при разработке приложений придется прописывать часть кода и на клиентской стороне.
Усложняется процесс переноса БД на другие рельсы (СУБД).
Процедуры в phpMyAdmin
Для начала рассмотрим применение в MySQL хранимых процедур на примере phpMyAdmin. Таким образом нам будет легче разобраться с этим типом структур. Стартуем!
Запускаем программную оболочку, справа выбираем тестовую БД. У меня таковой является база world. Затем в главном меню сверху переходим по вкладке «Процедуры». Здесь жмем на «Добавить процедуру».
После этого появится окно диалога «Добавить процедуру». Заполняем в ней все указанные на снимке поля. Указываем имя процедуры, тип. В графе «Определение» вводим учетную запись пользователя, и в комментариях (необязательно) указываем для себя, что это всего лишь пример хранимой процедуры.
Уже на этом этапе мы знакомимся с особенностями синтаксиса создания хранимых процедур MySQL. В поле «Определение» прописываем тело структуры. Обратите внимание, что выполняемый запрос находится между ключевыми словами BEGIN и END:
BEGIN SELECT "HELLO, WORD!"; END
BEGIN SELECT "HELLO, WORD!" ; |
Данный запрос не выполняет никаких действий с базой, а лишь выводит надпись. Это мы указали в поле «Доступ к 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
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 ; |
Вы, наверное, заметили, что мы не передаем никаких значений внутрь процедуры. Кроме этого в 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
CREATE DEFINER = ` root ` @ ` localhost ` PROCEDURE ` proc6 ` (out col decimal ) BEGIN SELECT COUNT (* ) into col FROM city ; |
Для вызова из файла PHP процедуры и вывода результата будем использовать возможности класса PDOStatement, созданного специально для работы с БД через SQL
Этот класс реализован сравнительно недавно, и поддерживается PHP, начиная с версии 5.1.0. Советую перед использованием проверить используемую версию языка c помощью встроенной функции phpversion().
Лабораторная работа №1 «Хранимые процедуры в M
ySQL»
3.1.Цель работы
Изучить виды используемых в MySQL хранимых процедур.
Получить навыки работы с хранимыми процедурами с помощью команд SQL и с помощью утилиты dbForge for MySQL .
3.2. Введение
Хранимые процедуры - один из наиболее мощных инструментов, предлагаемых разработчикам приложений баз данных MySQL для реализации бизнес-логики. Хранимые процедуры (англ, stoied proceduies) позволяют реализовать значительную часть логики приложения на уровне базы данных и таким образом повысить производительность всего приложения, централизовать обработку данных и уменьшить количество кода, необходимого для выполнения поставленных задач. Практически любое достаточно сложное приложение баз данных не обходится без использования хранимых процедур.
Помимо этих широко известных преимуществ использования хранимых процедур, общих для большинства реляционных СУБД, хранимые процедуры InterBase могут играть роль практически полноценных наборов данных, что позволяет использовать возвращаемые ими результаты в обычных SQL-запросах.
3.3. Методика выполнения работы
2. Разработать текст хранимых процедур для трех запросов в соответствии с вариантом задания к лабораторной работе №4.
3. Модифицировать приложение в соответствии с проведенной модификацией базы данных. Добиться корректной работы приложения. Организовать запрос к данным в приложении через хранимые процедуры SQL.
3.4. Варианты заданий
Исходными данными для лабораторной работы являются варианты заданий и результаты предыдущих лабораторных работ.
3.5. Содержание отчета
Отчет должен содержать описание и результаты работы, представляемые в следующей последовательности:
1. Описание бизнес-правил, реализованных с помощью хранимых процедур.
2. Распечатка сценария создания хранимых процедур с комментариями к созданным хранимым процедурам.
3. 6. Контрольные вопросы
1. Дайте определение хранимой процедуры.
2. Как создается хранимая процедура?
3. Как задать выходные параметры хранимой процедуры?
4. Как присваиваются значения переменным?
5. Как организовать цикл в хранимой процедуре?
6. Как оформить условный переход в хранимой процедуре?
7. Как обработать ошибки исполнения хранимой процедуры?
3.7.1. Понятие хранимой процедуры
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием.
За
- Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
- Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
- Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
- Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
Против
- Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
- Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
- Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
- Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
Как работать с хранимыми процедурами
Создание хранимой процедуры
CREATE PROCEDURE p2()
SQL SECURITY INVOKER
COMMENT "Это моя первая процедура"
BEGIN
SELECT "Привет";
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
- Language: в целях обеспечения переносимости, по умолчанию указан SQL.
- Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
- SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
- Comment: в целях документирования, значение по умолчанию
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
CALL p2();
CALL stored_procedure_name (param1, param2, ....)
CALL procedure1(10 , "string parameter" , @parameter_var);
Изменение хранимой процедуры
В MySQL есть выражение ALTER PRO CEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Параметры
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
- 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
CREATE PROCEDURE proc_IN (IN var1 INT)
BEGIN
SELECT var1 + 2 AS result;
END
Пример параметра OUT
CREATE PROCEDURE proc_OUT (OUT var1 VARCHAR(100))
BEGIN
SET var1 = "This is a test";
END
Пример параметра INOUT
CREATE PROCEDURE proc_INOUT (OUT var1 INT)
BEGIN
SET var1 = var1 * 2;
END
Переменные
Рассмотрим создание переменных и сохранение их внутри процедур. Переменные должны быть должны объявлены явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue ;
Давайте объявим несколько переменных:
DECLARE a, b INT DEFAULT 5;
DECLARE str VARCHAR(50);
DECLARE v1, v2, v3 TINYINT;
Работа с переменными
Как только вы объявили переменную, вы можете задать ей значение с помощью команд:
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;
Структуры управления потоками
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
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. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE variable1
WHEN 0 THEN
WHEN 1 THEN
ELSE
END CASE;
или :
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. Вот пример цикла :
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
Курсоры
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
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;
В этом примере мы проведем кое-какие простые операции с использованием курсора:
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;
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
- Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
- Доступен только для чтения: курсоры нельзя изменять.
- Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
Заключение
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
Примеры :
CREATE PROCEDURE test.p2(IN P_bdate DATE)
SQL SECURITY INVOKER
COMMENT " Это моя первая процедура "
BEGIN
SELECT * FROM persona WHERE bdate<=P_bdate;
CREATE PROCEDURE test.p1(IN sortingField VARCHAR(255), IN sortingOrder VARCHAR(255), IN firstRowIndex SMALLINT, IN rowsPerPage SMALLINT)
SQL SECURITY INVOKER
COMMENT "Это моя первая процедура"
BEGIN
SET @var = concat("SELECT fio, Count(*) AS count FROM persona,pnumber WHERE persona.id_person=pnumber.id_person GROUP BY fio",
" ORDER BY ", sortingField, " ", sortingOrder, " LIMIT ", firstRowIndex, ",", rowsPerPage);
PREPARE zxc FROM @var;
EXECUTE zxc;