«…лишь недалекие люди боятся конкуренции, а люди подлинного творчества ценят общение с каждым талантом…» А. Бек, Талант.

СУБД/лекция 2 семестр 2

Материал из Wiki

Перейти к: навигация, поиск

Содержание

Слайды

Источник

Исходный материал [1]

Введение в хранимые процедуры MySQL 5

В MySQL 5 есть много новых функций, одной из самых весомых из которых является создание хранимых процедур. В этом уроке я расскажу о том, что они из себя представляют, а также о том, как они могут облегчить вам жизнь.

Введение

Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.

Причина их создания ясна и подтверждается частым использованием. С другой стороны, если вы поговорите с теми, кто работает с ними нерегулярно, то мнения разделятся на два совершенно противоположных фланга. Не забывайте об этом.

За (преимущества)

Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями. Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц. Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE. Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против (недостатки)

Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской. Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур. Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными. Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

Кстати, я использую элементарную структуру таблиц, чтобы вам было легче разобраться в этой теме. Я ведь рассказываю о хранимых процедурах, а они достаточно сложны, чтобы вникать еще и в громоздкую структуру таблиц.

Шаг 1: Ставим ограничитель

Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.

Шаг 2: Как работать с хранимыми процедурами

Создание хранимой процедуры 
	DELIMITER //  
 
	CREATE PROCEDURE `p2` ()  
	LANGUAGE SQL  
	DETERMINISTIC  
	SQL SECURITY DEFINER  
	COMMENT 'A procedure' 
	BEGIN 
	    SELECT 'Hello World !';  
	END//


Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и, наконец, тело самой процедуры.

Названия хранимых процедур чувствительны к регистру. Вам также нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.

Характеристики хранимой процедуры:

Language: в целях обеспечения переносимости, по умолчанию указан SQL. Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC. SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER. Comment: в целях документирования, значение по умолчанию - ""

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны. 
	CALL stored_procedure_name (param1, param2, ....)  
 
	CALL procedure1(10 , 'string parameter' , @parameter_var);

Изменение хранимой процедуры

В 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 
	   DELIMITER //  
 
	CREATE PROCEDURE `proc_IN` (IN var1 INT)  
	BEGIN 
	    SELECT var1 + 2 AS RESULT;  
	END//
Пример параметра OUT 
	DELIMITER //  
 
	CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100))  
	BEGIN 
	    SET var1 = 'This is a test';  
	END //
Пример параметра INOUT 
	DELIMITER //  
 
	CREATE PROCEDURE `proc_INOUT` (OUT var1 INT)  
	BEGIN 
	    SET var1 = var1 * 2;  
	END //

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока 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 //

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

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции 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

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

Технически, существует три вида циклов: цикл 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 //

Инструкция LEAVE 

LEAVE label

Эта инструкция используется, чтобы из выйти любой помеченной конструкции управления потоком данных. Это может использоваться внутри BEGIN ... END или же конструкций цикла (LOOP, REPEAT, WHILE).

Инструкция ITERATE 

ITERATE label

ITERATE может появляться только внутри инструкций LOOP, REPEAT и WHILE. ITERATE означает "выполнить цикл снова ".

Пример: 
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END
5.2.10.6. Инструкция REPEAT 
[begin_label:]
  REPEAT statement_list
    UNTIL search_condition
  END REPEAT
[end_label]
Операторный список внутри инструкции REPEAT повторен, пока search_condition равно true. Таким образом, REPEAT всегда проходит цикл по крайней мере один раз. Перечень statement_list состоит из одной или большего числа инструкций. Инструкция REPEAT может быть помечена по обычным правилам. 
delimiter //
CREATE PROCEDURE dorepeat(p1 INT)
 BEGIN
   SET @x = 0;
   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
 END
 //
 
 CALL dorepeat(1000)//
 
 SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

Заключение

В этом уроке я ознакомил вас с основами работы с хранимыми процедурами и с некоторыми специфическими свойствами, связанными с ней. Конечно, вам нужно будет углубить знания в таких областях, как безопасность, выражения SQL и оптимизация, прежде чем стать настоящим гуру MySQL процедур.

Вы должны подсчитать, какие преимущества даст вам использование хранимых процедур в вашем конкретном приложении, и только потом создавать лишь необходимые процедуры. В общем, я использую процедуры; по-моему, их стоит внедрять в проекты в следствие их безопасности, обслуживания кода и общего дизайна. К тому же, не забывайте, что над процедурами MySQL все еще ведется работа. Ожидайте улучшений, касающихся функциональности и улучшений. Прошу, не стесняйтесь делиться мнениями.


Данный урок подготовлен для вас командой сайта http://ruseller.com 

Источник урока: [2]
Перевел: Станислав Протасевич
Урок создан: 7 Июля 2011
Источник — «http://www.simhard.com/wiki/index.php?title=СУБД/лекция_2_семестр_2»