Наиболее общая разница между процедурами и функциями заключается в том, что они вызываются по-разному и для разных целей:
- Процедура не возвращает значение. Вместо этого он вызывается с помощью инструкции CALL для выполнения операции, такой как изменение таблицы или обработка извлеченных записей.
- Функция вызывается внутри выражения и возвращает одно значение непосредственно вызывающему, которое должно использоваться в выражении.
- Вы не можете вызывать функцию с помощью инструкции CALL и вы не можете вызвать процедуру в выражении.
Синтаксис для рутинного создания несколько отличается для процедур и функций:
- Параметры процедуры могут быть определены как входные, выходные или оба. Это означает, что процедура может передавать значения обратно вызывающему, используя выходные параметры. Доступ к этим значениям можно получить в операторах, которые следуют за инструкцией CALL. Функции имеют только входные параметры. В результате, хотя обе процедуры и функции могут иметь параметры, определение параметра процедуры отличается от описания функций для функций.
Чтобы вызвать хранимую процедуру, используйте CALL statement . Чтобы вызвать хранимую функцию, обратитесь к ней в выражении. Функция возвращает значение во время оценки выражения.
Процедура вызывается с помощью инструкции CALL и может только возвращать значения с использованием выходных переменных. Функция может вызываться изнутри оператора точно так же, как любая другая функция (то есть, вызывая имя функции), и может возвращать скалярное значение.
Задание параметра IN, OUT или INOUT действительна только для ПРОЦЕДУРЫ. Для функции FUNCTION параметры всегда считаются параметрами IN.
Функция возвращает значение, поэтому в определении функции должно быть предложение RETURNS, чтобы указать тип данных возвращаемого значения. Кроме того, в теле функции должно быть хотя бы одно выражение RETURN, чтобы вернуть значение вызывающему. RETURNS и RETURN не отображаются в определениях процедур.
Если ключевое слово не задано перед именем параметра, оно по умолчанию является параметром IN. Параметрам для сохраненных функций не предшествуют IN, OUT или INOUT. Все параметры функции рассматриваются как параметры IN.
Чтобы определить хранимую процедуру или функцию, используйте CREATE PROCEDURE или CREATE FUNCTION соответственно:
CREATE PROCEDURE proc_name () routine_body CREATE FUNCTION func_name () RETURNS data_type // diffrent routine_body
Расширение MySQL для хранимой процедуры (не функций) состоит в том, что процедура может генерировать набор результатов или даже несколько наборов результатов, которые вызывающий выполняет так же, как результат инструкции SELECT. Однако содержимое таких наборов результатов не может быть использовано непосредственно в выражении.
Сохраненные процедуры (ссылаясь как на хранимые процедуры, так и на хранимые функции) связаны с конкретной базой данных, подобно таблицам или представлениям. При удалении базы данных любые сохраненные процедуры в базе данных также отбрасываются.
Хранимые процедуры и функции не используют одно и то же пространство имен. Возможно, в базе данных есть процедура и функция с тем же именем.
В хранимых процедурах можно использовать динамический SQL, но не в функциях или триггерах.
Готовые заявления SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) могут использоваться в хранимых процедурах, но не в хранимых функциях или триггерах. Таким образом, хранимые функции и триггеры не могут использовать Dynamic SQL (где вы строите операторы как строки, а затем выполняете их). (Динамический SQL в MySQL хранимых процедурах)
Еще несколько интересных отличий между FUNCTION и STORED PROCEDURE:
Функции обычно используются для вычислений, где as процедуры обычно используются для выполнения бизнес-логики.
Функции Не могут повлиять на состояние базы данных
(Заявления, которые делают явное или неявное совершение или откат, запрещены в функции)
В то время как
Хранимые процедуры могут влиять на состояние базы данных, используя фиксацию и т.д.
refrence: J.1. Ограничения на хранимые процедуры и триггеры
Функции не могут использовать FLUSH , тогда как Хранимые процедуры могут выполнять.
Внутри хранимой функции или триггера не разрешается изменять таблицу, которая уже используется (для чтения или записи) оператором, вызывающим функцию или триггер. Хороший пример: Как обновить ту же таблицу при удалении в MYSQL?
Примечание : хотя некоторые ограничения обычно применяются к хранимым функциям и триггерам, но не к хранимым процедурам, эти ограничения применяются к хранимым процедурам, если они вызываются из хранимой функции или триггера. Например, хотя вы можете использовать FLUSH в хранимой процедуре, такую хранимую процедуру нельзя вызывать из хранимой функции или триггера.
Внимание! Данная работа построена на основе перевода раздела «17.1. Stored Routines and the Grant Tables» описания ПО MySQL 5.0.19, «Reference Manual. It documents MySQL 5.0 through 5.0.19. Document generated on: 2006-01-23 (revision:995)»
``Сначала прочти все, а потом пробуй примеры"
Хранимые процедуры представляют собой набор команд SQL, которые могут компилироваться и храниться на сервере. Таким образом, вместо того, чтобы хранить часто используемый запрос, клиенты могут ссылаться на соответствующую хранимую процедуру. Это обеспечивает лучшую производительность, поскольку данный запрос должен анализироваться только однажды и уменьшается трафик между сервером и клиентом. Концептуальный уровень можно также повысить за счет создания на сервере библиотеки функций.
Триггер представляет собой хранимую процедуру, которая активизируется при наступлении определенного события. Например, можно задать хранимую процедуру, которая срабатывает каждый раз при удалении записи из транзакционной таблицы - таким образом, обеспечивается автоматическое удаление соответствующего заказчика из таблицы заказчиков, когда все его транзакции удаляются.
Хранимые программы (процедуры и функции) поддерживаются в MySQL 5.0. Хранимые процедуры - набор SQL -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.
Это может быть полезным тогда, когда:
- многочисленные приложения клиента написаны в разных языках или работают на других платформах, но нужно использовать ту же базу данных операций
- безопасность на 1 месте
Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.
Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).
MySQL следует в синтаксисе за SQL:2003 для хранимых процедур, который уже используется в IBM"s DB2.
От слов к делу…
При создании, модификации, удалении хранимых подпрограмм сервер манипулирует с таблицей mysql.proc
Начиная с MySQL 5.0.3 требуются следующие привилегии:
CREATE ROUTINE для создания хранимых процедур
ALTER ROUTINE необходимы для изменения или удаления процедур. Эта привилегия автоматически назначается создателю процедуры (функции)
EXECUTE привилегия потребуется для выполнения подпрограммы. Тем не менее, автоматически назначается создателю процедуры (функции). Также, по умолчанию, SQL SECURITY параметр для подпрограммы DEFINER , который разрешает пользователям, имеющим доступ к БД вызывать подпрограммы, ассоциированные с этой БД.
Синтаксис хранимых процедур и функций
Хранимая подпрограмма представляет собой процедуру или функцию. Хранимые подпрограммы создаются с помощью выражений CREATE PROCEDURE или CREATE FUNCTION . Хранимая подпрограмма вызывается, используя выражение CALL , причем только возвращающие значение переменные используются в качестве выходных. Функция может быть вызвана подобно любой другой функции и может возвращать скалярную величину. Хранимые подпрограммы могут вызывать другие хранимые подпрограммы.
Начиная с MySQL 5.0.1, загруженная процедура или функция связана с конкретной базой данных. Это имеет несколько смыслов:
- Когда подпрограмма вызывается, то подразумевается, что надо произвести вызов USE db_name (и отменить использование базы, когда подпрограмма завершилась, и база больше не потребуется)
- Вы можете квалифицировать обычные имена с именем базы данных. Это может быть использовано, чтобы ссылаться на подпрограмму, которая - не в текущей базе данных. Например, для выполнения хранимой процедуры p или функции f которые связаны с БД test , вы можете сказать интерпретатору команд так: CALL test.p() или test.f() .
- Когда база данных удалена, все загруженные подпрограммы связанные с ней тоже удаляются. В MySQL 5.0.0, загруженные подпрограммы - глобальные и не связанны с базой данных. Они наследуют по умолчанию базу данных из вызывающего оператора. Если USE db_name выполнено в пределах подпрограммы, оригинальная текущая БД будет восстановлена после выхода из подпрограммы (Например текущая БД db_11 , делаем вызов подпрограммы, использующей db_22 , после выхода из подпрограммы остается текущей db_11)
MySQL поддерживает полностью расширения, которые разрешают юзать обычные SELECT выражения (без использования курсоров или локальных переменных) внутри хранимых процедур. Результирующий набор, возвращенный от запроса, а просто отправляется напрямую клиенту. Множественный SELECT запрос генерирует множество результирующих наборов, поэтому клиент должен использовать библиотеку, поддерживающую множественные результирующие наборы.
CREATE PROCEDURE - создать хранимую процедуру.
CREATE FUNCTION - создать хранимую функцию.
Синтаксис:
CREATE PROCEDURE имя_процедуры ([параметр_процедуры[,...]])[характеристёика...] тело_подпрограммы
CREATE FUNCTION
имя_функции ([параметр_функции[,...]])
RETURNS
тип
[характеристика...] тело_подпрограммы
параметр_процедуры:
[ IN
| OUT
| INOUT
] имя_параметра тип
параметр_функции:
имя_параметра тип
тип:
Любой тип данных MySQL
характеристика:
LANGUAGE SQL
| DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"
тело_подпрограммы:
Правильное SQL выражение.
Рассмотрим все на практике.
Сначала создадим хранимую процедуру следующим запросом:
CREATE PROCEDURE `my_proc`(OUT t INTEGER(11))
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
BEGIN
select val1+val2 into "t" from `my` LIMIT 0,1;
END;
Применение выражения LIMIT в этом запросе сделано из соображений того, что не любой клиент способен принять многострочный результирующий набор.
После этого вызовем ее:
CALL my_proc(@a);
SELECT @a;
Для отделения внутреннего запроса от внешнего всегда используют разделитель отличный от обычно (для задания используют команду DELIMITER <строка/символ>)
Вот еще один пример с учетом всех требований.
Mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
Весь процесс можно пронаблюдать на рисунке ниже:
Триггеры
Поддержка триггеров появилась в MySQL начиная с версии 5.0.2.
Триггер - поименованный объект БД, который ассоциирован с таблицей и активируемый при наступлении определенного события, события связанного с этой таблицей.
Например, нижеприведенный код создает таблицу и INSERT триггер. Триггер суммирует значения, вставляемые в один из столбцов таблицы.
Mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)
Объявим переменную sum и присвоим ей значение 1. После этого при каждой вставке в таблицу account значение этой переменной будет увеличивать согласно вставляемой части.
Замечание . Если значение переменной не инициализировано, то триггер работать не будет!
Синтаксис создания триггера
CREATETRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера
Если с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.
время_триггера
Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.
событие_срабатывания_триггера
Здесь все проще. Тут четко обозначается, при каком событии выполняется триггер.
- INSERT: т.е. при операциях вставки или аналогичных ей выражениях (INSERT, LOAD DATA, и REPLACE)
- UPDATE: когда сущность (строка) модифицирована
- DELETE: когда запись удаляется (запросы, содержащие выражения DELETE и/или REPLACE)
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;
Лабораторная работа №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;
Что для этого нужно? Установите 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;
Простой пример
Один из хороших случаев применения хранимых процедур - тогда, когда вам нужно объединить несколько запросов в один, например добавление темы в форум и увеличение общего количества тем. Допустим таблица threadsCREATE TABLE `threads` (
`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), мы можем передать один - оптимизация, чистый код и можно изменить в любой момент не затрагивая другие скрипты.
Курсоры (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 ‘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;
