Условные операторы в хранимых процедурах в mysql — для студента

Начало этой статьи доступно по ссылке «Хранимые процедуры в 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:

Условные операторы в хранимых процедурах в 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, первое, что мы замечаем, — это то, что пользователь вообще не видит каких-либо таблиц, видит только ХП:

Условные операторы в хранимых процедурах в mysql - Для студента

Очевидно, что пользователь 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 и т.д.

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

Условные операторы в хранимых процедурах в mysql - Для студента

Последнее редактирование: 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;

Простой пример

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

CALL procedure4();SELECT @tags, @threads;