СУБД/лекция 2 семестр 2 - История изменений

Vidokq: /* Шаг 6: Курсоры */

2014-03-24T13:26:47Z

‎Шаг 6: Курсоры

Vidokq: /* Источник */

2014-03-10T11:07:16Z

‎Источник

Vidokq: /* Шаг 6: Курсоры */

2014-03-10T10:55:56Z

‎Шаг 6: Курсоры

Vidokq: /* Заключение */

2014-03-03T09:00:12Z

‎Заключение

Vidokq: /* Заключение */

2014-03-03T08:57:31Z

‎Заключение

Vidokq: /* Заключение */

2014-03-03T08:57:16Z

‎Заключение

Vidokq: /* Шаг 5: Структуры управления потоками */

2014-03-03T08:56:33Z

‎Шаг 5: Структуры управления потоками

Vidokq: Новая страница: «== Слайды == * Файл:Лекция_14.rar == Источник == [http://ruseller.com/lessons.php?rub=28&id=1189 Исходный материал] =…»

2014-03-03T08:54:42Z

Новая страница: «== Слайды == * Файл:Лекция_14.rar == Источник == [http://ruseller.com/lessons.php?rub=28&id=1189 Исходный материал] =…»

Новая страница

== Слайды ==
* [[Файл:Лекция_14.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

@@ Строка 272: / Строка 272: @@
 row in set (0.00 sec)
 </source>
 == Заключение ==

@@ Строка 3: / Строка 3: @@
 == Источник ==
 [http://ruseller.com/lessons.php?rub=28&id=1189 Исходный материал]
 == Введение в хранимые процедуры MySQL 5 ==

@@ Строка 225: / Строка 225: @@
 	END //</source>
 == Шаг 6: Курсоры ==
@@ Строка 271: / Строка 317: @@
 *'''Доступен только для чтения:''' курсоры нельзя изменять.
 *'''Без перемотки:''' курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.
 == Заключение ==

@@ Строка 277: / Строка 277: @@
 Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.
 Данный урок подготовлен для вас командой сайта [http://ruseller.com]
   Источник урока: [http://www.net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/]
   Перевел: '''''Станислав Протасевич'''''
   Урок создан: 7 Июля 2011

@@ Строка 142: / Строка 142: @@
 == Шаг 5: Структуры управления потоками ==
-MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
+MySQL поддерживает конструкции '''IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT''' для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
-Конструкция IF
+=== Конструкция IF ===
 С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
@@ Строка 164: / Строка 164: @@
 	END //</source>
-Конструкция CASE
+=== Конструкция CASE ===
 CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
@@ Строка 206: / Строка 206: @@
 	END //</source>
-Конструкция WHILE
+=== Конструкция WHILE ===
 Технически, существует три вида циклов: цикл '''WHILE''', цикл '''LOOP''' и цикл '''REPEAT'''. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения '''GOTO'''. Вот пример цикла:
@@ Строка 223: / Строка 223: @@
 	        SET variable1 = variable1 + 1;
 	    END WHILE;
 	END //</source>
 == Шаг 6: Курсоры ==