Начало этой статьи доступно по ссылке «Хранимые процедуры в MySQL и PHP»:
Проще говоря, хранимые процедуры («ХП») — это сохраненные в базе данных процедуры (написанные с помощью SQL и других управляющих операторов), которые могут быть выполнены движком баз данных и вызваны из программного кода, который с этим движком работает. »»» Читать полностью
Тэйлор Рен (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.
После выполнения этой команды в 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, как хранимые процедуры.
Тэйлор Рен, 03.01.2014 Перевод с английского: Дмитрий Скоробогатов, специально для xBB.uz, 12.03.2014 Оригинальный текст может быть найден по адресу http://www.sitepoint.com/stored-procedures-mysql-php/
Тэйлор Рен
Тэйлор — свободный разработчик веб- и десктопных приложений, проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.
Предыдущие публикации:
Последнее редактирование: 2014-03-12 05:31:13
Метки материала: db, mysql, базы данных, php, процедуры, базы данных mysql, хп, sql, хранимые процедуры, бд, программирование, mysql и php, базы
Оставьте, пожалуйста, свой комментарий к публикации
Источник: http://xbb.uz/db/Hranimye-procedury-v-MySQL-i-PHP-Chast-2
Хранимые процедуры в MySQL
Written on 12 Января 2009. Posted in 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, вам придется поработать с переменными. Так как это не входи в рамки данной статьи, покажу лишь несколько примеров.DECLARE iVar INT DEFAULT 0; SET iVar = 5; SELECT * FROM `data` WHERE `id` = iVar; 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 указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:SELECT * FROM `data` WHERE `id` = iInput1 AND `id2` = iInput2;Условия и циклы вам обязательно понадобятся при написании комплексных хранимых процедур, но зацикливаться на этой теме не буду. Думаю хоть какие-то навыки программирования у вас есть, так что покажу всего лишь синтаксис.IF условие THEN действие; ELSE действие; END IF; WHILE условие DO действие;
END WHILE;
Один из хороших случаев применения хранимых процедур — тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threadsCREATE 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.CREATE PROCEDURE `procedure2`(IN sTitle VARCHAR(255)) BEGIN INSERT INTO `threads` (`title`) VALUES (sTitle); UPDATE `variables` SET `value` = `value` + 1 WHERE `name` = 'threads';
END
Объяснять особо нечего, просто два запроса объединили в один.
Теперь мы можем вызвать эту процедуру таким образом:CALL procedure2('My new thread');Таким образом, вместо того, чтобы передать два или больше запросов (например через php), мы можем передать один — оптимизация, чистый код и можно изменить в любой момент не затрагивая другие скрипты.
Курсоры позволяют пройтись по всем полученным результатам запроса. На теории объяснить сложно, покажу на практике. Добавим еще одну таблицу к нашей базе данных — hits:CREATE TABLE `tags` ( `id` INT NOT NULL AUTO_INCREMENT , `tag` VARCHAR(255) NOT NULL , PRIMARY KEY ( `id` )
) ENGINE = MYISAM
Сюда мы будем записывать все тэги из всех тем.
Хранимая процедура будет выглядеть примерно так: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 вставляем новый тег.
В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.
Извлечение данных
Вспомним системные переменные и рассмотрим еще одну манипуляцию над нашими таблицами — получить общее количество тегов и тем. Перейдем сразу к процедуре: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;
Источник: http://www.CyberGuru.ru/database/mysql/stored-procedures.html
MySQL хранимые процедуры
Долго мучался с этим вопросом. Литературы в интернете мало, особенно на русском языке. Пришлось поспрашивать на зарубежных форумах, глубже покопаться в мануалах и разъяснить для себя некоторые непонятные моменты. Итак, коротко о хранимых процедурах в 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;SET iVar = 5;
SELECT * FROM `data` WHERE `id` = iVar;
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 указывает на то, что параметр указан только для чтения. Далее с этим параметром работаем как с обычной переменной внутри процедуры:
SELECT * FROM `data` WHERE `id` = iInput1 AND `id2` = iInput2;
Условия, Циклы. 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.
CREATE PROCEDURE `procedure2`(IN sTitle VARCHAR(255))BEGININSERT INTO `threads` (`title`) VALUES (sTitle);UPDATE `variables` SET `value` = `value` + 1 WHERE `name` = 'threads';
END
- Объяснять особо нечего, просто два запроса объединили в один. Теперь мы можем вызвать эту процедуру таким образом: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
Сюда мы будем записывать все тэги из всех тем. Хранимая процедура будет выглядеть примерно так:
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 ‘020002 означает это окончание).
В этом случае мы в переменную done запишем 1, чтобы в последствии выйти из цикла.Открываем курсор, и получаем первую запись. Дальше в цикле — Выбираем количество совпадений из таблицы тегов для текущего тега и помещаем результат в переменную iCount. Если результатов нет, то запросом INSERT вставляем новый тег.
В конце концов закрываем курсор и выходим из процедуры. Ну вот и всё.
Извлечение данных
Вспомним системные переменные и рассмотрим еще одну манипуляцию над нашими таблицами — получить общее количество тегов и тем. Перейдем сразу к процедуре:
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;
- Заключение
- А заключения и не будет 😉 буду рад ответить на ваши вопросы — пишите в отзывы.
Источник: https://lred.ru/bd/795-mysql—