|
|
| Строка 1: |
Строка 1: |
| | == Слайды == | | == Слайды == |
| − | * [[Файл:Лекция_14.rar]] | + | * [[Файл:Лекция_13.rar]] |
| − | == Источник ==
| + | |
| − | [http://ruseller.com/lessons.php?rub=28&id=1189 Исходный материал]
| + | |
| − | | + | |
| − | == Введение в хранимые процедуры MySQL 5 ==
| + | |
| − | В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.
| + | |
| − | | + | |
| − | == Введение ==
| + | |
| − | | + | |
| − | '''Хранимая процедура''' - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.
| + | |
| − | | + | |
| − | Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.
| + | |
| − | == За (преимущества)==
| + | |
| − | Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
| + | |
| − | Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
| + | |
| − | Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
| + | |
| − | Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.
| + | |
| − | == Против (недостатки)==
| + | |
| − | Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
| + | |
| − | Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
| + | |
| − | Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
| + | |
| − | Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.
| + | |
| − | | + | |
| − | Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.
| + | |
| − | | + | |
| − | Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.
| + | |
| − | == Шаг 1: Ставим ограничитель ==
| + | |
| − | | + | |
| − | Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
| + | |
| − | == Шаг 2: Как работать с хранимыми процедурами ==
| + | |
| − | Создание хранимой процедуры
| + | |
| − | <source lang="sql">
| + | |
| − | DELIMITER //
| + | |
| − |
| + | |
| − | CREATE PROCEDURE `p2` ()
| + | |
| − | LANGUAGE SQL
| + | |
| − | DETERMINISTIC
| + | |
| − | SQL SECURITY DEFINER
| + | |
| − | COMMENT 'A procedure'
| + | |
| − | BEGIN
| + | |
| − | SELECT 'Hello World !';
| + | |
| − | END//</source>
| + | |
| − | | + | |
| − | | + | |
| − | Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.
| + | |
| − | | + | |
| − | Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
| + | |
| − | | + | |
| − | == Характеристики хранимой процедуры: ==
| + | |
| − | Language: в целях обеспечения переносимости, по умолчанию указан SQL.
| + | |
| − | Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
| + | |
| − | SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
| + | |
| − | Comment: в целях документирования, значение по умолчанию - ""
| + | |
| − | == Вызов хранимой процедуры ==
| + | |
| − | | + | |
| − | Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
| + | |
| − | <source lang="sql"> CALL stored_procedure_name (param1, param2, ....)
| + | |
| − |
| + | |
| − | CALL procedure1(10 , 'string parameter' , @parameter_var);</source>
| + | |
| − | | + | |
| − | Изменение хранимой процедуры
| + | |
| − | | + | |
| − | В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
| + | |
| − | Удаление хранимой процедуры1 DROP PROCEDURE IF EXISTS p2;
| + | |
| − | | + | |
| − | | + | |
| − | Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
| + | |
| − | == Шаг 3: Параметры ==
| + | |
| − | | + | |
| − | Давайте посмотрим, как можно передавать в хранимую процедуру параметры.
| + | |
| − | | + | |
| − | '''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
| + | |
| − | <source lang="sql"> DELIMITER //
| + | |
| − |
| + | |
| − | CREATE PROCEDURE `proc_IN` (IN var1 INT)
| + | |
| − | BEGIN
| + | |
| − | SELECT var1 + 2 AS result;
| + | |
| − | END//</source>
| + | |
| − | | + | |
| − | Пример параметра OUT
| + | |
| − | <source lang="sql">
| + | |
| − | DELIMITER //
| + | |
| − |
| + | |
| − | CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))
| + | |
| − | BEGIN
| + | |
| − | SET var1 = 'This is a test';
| + | |
| − | END //</source>
| + | |
| − | | + | |
| − | Пример параметра INOUT
| + | |
| − | <source lang="sql"> DELIMITER //
| + | |
| − |
| + | |
| − | CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)
| + | |
| − | BEGIN
| + | |
| − | SET var1 = var1 * 2;
| + | |
| − | END //</source>
| + | |
| − | | + | |
| − | == Шаг 4: Переменные ==
| + | |
| − | | + | |
| − | Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока '''BEGIN/END''', вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.
| + | |
| − | | + | |
| − | Синтаксис объявления переменной выглядит так:
| + | |
| − | | + | |
| − | <source lang="sql"> DECLARE varname DATA-TYPE DEFAULT defaultvalue;</source>
| + | |
| − | | + | |
| − | | + | |
| − | Давайте объявим несколько переменных:
| + | |
| − | <source lang="sql">
| + | |
| − | DECLARE a, b INT DEFAULT 5;
| + | |
| − |
| + | |
| − | DECLARE str VARCHAR(50);
| + | |
| − |
| + | |
| − | DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
| + | |
| − |
| + | |
| − | DECLARE v1, v2, v3 TINYINT;</source>
| + | |
| − | | + | |
| − | === Работа с переменными ===
| + | |
| − | | + | |
| − | Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
| + | |
| − | | + | |
| − | <source lang="sql">
| + | |
| − | 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 //</source>
| + | |
| − | | + | |
| − | == Шаг 5: Структуры управления потоками ==
| + | |
| − | | + | |
| − | MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
| + | |
| − | Конструкция IF
| + | |
| − | | + | |
| − | С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
| + | |
| − | <source lang="sql"> 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 //</source>
| + | |
| − | | + | |
| − | Конструкция CASE
| + | |
| − | | + | |
| − | CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
| + | |
| − | | + | |
| − | <source lang="sql"> 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 //
| + | |
| − | </source>
| + | |
| − | | + | |
| − | или:
| + | |
| − | <source lang="sql"> 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 //</source>
| + | |
| − | | + | |
| − | Конструкция WHILE
| + | |
| − | | + | |
| − | Технически, существует три вида циклов: цикл '''WHILE''', цикл '''LOOP''' и цикл '''REPEAT'''. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения '''GOTO'''. Вот пример цикла:
| + | |
| − | | + | |
| − | <source lang="sql">
| + | |
| − | 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 //</source>
| + | |
| − | | + | |
| − | == Шаг 6: Курсоры ==
| + | |
| − | | + | |
| − | Курсоры используются для прохождения по набору строк, возвращенному запросом, а также обработки каждой строки.
| + | |
| − | | + | |
| − | MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
| + | |
| − | | + | |
| − | <source lang="sql">1 DECLARE cursor-name CURSOR FOR SELECT ...; /*Объявление курсора и его заполнение */
| + | |
| − | 2 DECLARE CONTINUE HANDLER FOR NOT FOUND /*Что делать, когда больше нет записей*/
| + | |
| − | 3 OPEN cursor-name; /*Открыть курсор*/
| + | |
| − | 4 FETCH cursor-name INTO variable [, variable]; /*Назначить значение переменной, равной текущему значению столбца*/
| + | |
| − | 5 CLOSE cursor-name; /*Закрыть курсор*/
| + | |
| − | | + | |
| − | </source>
| + | |
| − | | + | |
| − | В этом примере мы проведем кое-какие простые операции с использованием курсора:
| + | |
| − | | + | |
| − | <source lang="sql">
| + | |
| − | 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 //
| + | |
| − | </source>
| + | |
| − | | + | |
| − | У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:
| + | |
| − | *'''Не чувствительный:''' открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
| + | |
| − | *'''Доступен только для чтения:''' курсоры нельзя изменять.
| + | |
| − | *'''Без перемотки:''' курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
| + | |
| − | == Заключение ==
| + | |
| − | | + | |
| − | В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.
| + | |
| − | | + | |
| − | Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
| + | |
| − | | + | |
| − | Данный урок подготовлен для вас командой сайта [ruseller.com]
| + | |
| − | Источник урока: [www.net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/]
| + | |
| − | Перевел: '''''Станислав Протасевич'''''
| + | |
| − | Урок создан: 7 Июля 2011
| + | |
