Вредоносное ПО (malware) - это назойливые или опасные программы,...
![Лучшие утилиты для удаления вирусов и вредоносных программ](https://i2.wp.com/webhelper.info/images/danger.jpg)
Лабораторная работа №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. Понятие хранимой процедуры
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием.
CREATE PROCEDURE p2()
SQL SECURITY INVOKER
COMMENT "Это моя первая процедура"
BEGIN
SELECT "Привет";
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово 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 отлавливает ошибку в случае, если такой процедуры не существует.
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
Естественно, вы можете задавать несколько параметров разных типов.
Рассмотрим создание переменных и сохранение их внутри процедур. Переменные должны быть должны объявлены явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
Синтаксис объявления переменной выглядит так:
DECLARE varname DATA-TYPE DEFAULT defaultvalue ;
Давайте объявим несколько переменных:
Как только вы объявили переменную, вы можете задать ей значение с помощью команд:
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, мы можем выполнять задачи, содержащие условия:
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
BEGIN
DECLARE variable1 INT;
SET variable1 = param1 + 1;
CASE variable1
WHEN 0 THEN
WHEN 1 THEN
ELSE
END CASE;
или :
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла :
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
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 все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
Примеры :
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;
Наиболее общая разница между процедурами и функциями заключается в том, что они вызываются по-разному и для разных целей:
Синтаксис для рутинного создания несколько отличается для процедур и функций:
Функция возвращает значение, поэтому в определении функции должно быть предложение RETURNS, чтобы указать тип данных возвращаемого значения. Кроме того, в теле функции должно быть хотя бы одно выражение RETURN, чтобы вернуть значение вызывающему. RETURNS и RETURN не отображаются в определениях процедур.
Чтобы вызвать хранимую процедуру, используйте CALL statement . Чтобы вызвать хранимую функцию, обратитесь к ней в выражении. Функция возвращает значение во время оценки выражения.
Процедура вызывается с помощью инструкции CALL и может только возвращать значения с использованием выходных переменных. Функция может вызываться изнутри оператора точно так же, как любая другая функция (то есть, вызывая имя функции), и может возвращать скалярное значение.
Задание параметра IN, OUT или INOUT действительна только для ПРОЦЕДУРЫ. Для функции FUNCTION параметры всегда считаются параметрами IN.
Если ключевое слово не задано перед именем параметра, оно по умолчанию является параметром 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 в хранимой процедуре, такую хранимую процедуру нельзя вызывать из хранимой функции или триггера.
Проще говоря, хранимые процедуры («ХП») - это сохраненные в базе данных процедуры (написанные с помощью SQL и других управляющих операторов), которые могут быть выполнены движком баз данных и вызваны из программного кода, который с этим движком работает. »»» Читать полностью
Тэйлор Рен (Taylor Ren ), 03.01.2014
Поскольку ХП хранятся на сервере, то и создавать их рекомендуется непосредственно на сервере, т.е. не следует использовать 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 .
При использовании PDO вызов ХП довольно прост. Вот соответствующий PHP-код:
$dbms = "mysql"; // Замените следующие параметры соединения на соответствующие вашему окружению: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=new PDO($dsn, $user, $pass); $q=$cn->exec("call avg_sal(@out)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);
Переменная $res содержит среднюю зарплату по таблице salary . Теперь пользователь может производить дальнейшую обработку вывода с помощью PHP.
В этой статье мы рассмотрели давно забытую составляющую баз данных MySQL : хранимые процедуры. Преимущества использования ХП очевидны, но позвольте мне напомнить: Хранимые процедуры позволяют нам применять более строгий контроль доступа к определенным данным, когда это требуется бизнес-логикой.
Кроме того, мы продемонстрировали основные шаги в создании хранимых процедур, пользователей и назначения соответствующих привилегий, показали, как ХП вызываются из PHP.
Эта статья не покрывает всю тему хранимых процедур. Некоторые важные аспекты, такие как параметры ввода/вывода , управляющие операторы, курсоры, полный синтаксис и др. не были освещены в этой краткой статье.
Если вы заинтересовались, пожалуйста, оставьте здесь свой комментарий. Если нужно, мы будем рады предложить более глубокие статьи о таком полезном и мощном аспекте MySQL, как хранимые процедуры.
Тэйлор - свободный разработчик веб- и десктопных приложений , проживающий в Сужоу в восточном Китае. Начинал со средств разработки Borland (C++Builder, Delphi), опубликовал книгу по InterBase. С 2003 является сертифицированным экспертом Borland. Затем переключился на веб-разработку в типичной конфигурации LAMP. Позднее начал работать с jQuery, Symfony, Bootstrap, Dart и т.д.
Предыдущие публикации:
В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World !"; END//
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
DROP PROCEDURE IF EXISTS p2;
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
Естественно, вы можете задавать несколько параметров разных типов.
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
DELIMITER // 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 today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; END //
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value <> 0"; END IF; END //
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/ OPEN cursor-name; /*Открыть курсор*/ FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/ CLOSE cursor-name; /*Закрыть курсор*/
В этом примере мы проведем кое-какие простые операции с использованием курсора:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
Внимание! Данная работа построена на основе перевода раздела «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 -выражений, который может быть сохранен на сервере. Как только это сделано, клиенту уже не нужно повторно передавать запрос, а требуется просто вызвать хранимую программу.
Это может быть полезным тогда, когда:
Хранимые процедуры и функции (подпрограммы) могут обеспечить лучшую производительность потому, что меньше информации требуется для пересылки между клиентом и сервером. Выбор увеличивает нагрузку на сервер БД, но снижает затраты на стороне клиента. Используйте это, если много клиентских машин (таких как Веб-серверы) обслуживаются одной или несколькими БД.
Хранимые подпрограммы также позволяют вам использовать библиотеки функций, хранимые в БД сервера. Эта возможность представлена для многих современных языков программирования, которые позволяют вызывать их непосредственно (например, используя классы).
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, загруженная процедура или функция связана с конкретной базой данных. Это имеет несколько смыслов:
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 значение этой переменной будет увеличивать согласно вставляемой части.
Замечание . Если значение переменной не инициализировано, то триггер работать не будет!
Синтаксис создания триггера
CREATEЕсли с именем триггера и именем пользователя все понятно сразу, то о «времени триггера» и «событии» поговорим отдельно.
время_триггера
Определяет время свершения действия триггера. BEFORE означает, что триггер выполнится до завершения события срабатывания триггера, а AFTER означает, что после. Например, при вставке записей (см. пример выше) наш триггер срабатывал до фактической вставки записи и вычислял сумму. Такой вариант уместен при предварительном вычислении каких-то дополнительных полей в таблице или параллельной вставке в другую таблицу.
событие_срабатывания_триггера
Здесь все проще. Тут четко обозначается, при каком событии выполняется триггер.