Структуры управления потоками
Лабораторная работа №6
Х ранимые процедуры в MySQL
Цель работы: Научится создавать и использовать хранимые процедуры в базе данных.
Теоретические сведения: Хранимая процедура - это способ объединения данных и функций повторяющихся действий в один класс и хранение от внешнего воздействия.
В хранимых процедурах можно объявлять переменные, управлять потоками данных между различными приложениями, применять технику программирования, защищать данные.
О граничитель
Ограничитель “//”- это символ или строка символов, который используется для указания клиенту MySQL, что завершено написание выражения SQL.
Создание хранимой процедуры
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `p2` () | |
| 04 | LANGUAGE SQL | |
| 05 | DETERMINISTIC | |
| 06 | SQL SECURITY DEFINER | |
| 07 | COMMENT 'A procedure' | |
| 08 | BEGIN | |
| 09 | SELECT 'Hello World !'; | |
| 10 | END// | |
Первая часть кода создает хранимую процедуру. Следующая - содержит необязательные параметры. Затем идет название и тело самой процедуры.
Названия хранимых процедур чувствительны к регистру. Нельзя создавать несколько процедур с одинаковым названием. Внутри хранимой процедуры не может быть выражений, изменяющих саму базу данных.
4 характеристики хранимой процедуры:
· Language: в целях обеспечения переносимости, по умолчанию указан SQL.
· Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
|
|
|
· SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
· Comment: в целях документирования, значение по умолчанию - ""
Вызов хранимой процедуры
Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
| 1 | CALL stored_procedure_name (param1, param2, ....) |
| 3 | CALL procedure1(10 , 'string parameter' , @parameter_var); |
Изменение хранимой процедуры
В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если нужно изменить параметры или тело процедуры, следует удалить и создать ее заново.
Удаление хранимой процедуры
| 1 | DROP PROCEDURE IF EXISTS p2; |
Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Параметры
Значительное преимущество хранимых процедур в том, что они могут как принимать, так и передавать значения.
Существует три типа параметров:
|
|
|
IN: По умолчанию. Этот параметр принимается процедурой и может изменяться внутри процедуру, но не может изменятся вне процедуры.
OUT: Никакие значения не передаются (это означает что они имеют значение NULL), но они могут быть изменены внутри процедуры, а также изменены вне процедуры.
INOUT: Совмещает в себе свойства параметров IN и OUT. Значение может передаваться в процедуру, изменяться внутри процедуры и быть доступным вне процедуры.
Рассмотрим, как можно передавать в хранимую процедуру параметры.
· 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): один параметр, одновременно входящий и возвращаемый.
Можно задавать несколько параметров разных типов.
Пример1 параметра IN
| 1 | DELIMITER // |
| 3 | CREATE PROCEDURE `proc_IN` (IN var1 INT) | |
| 4 | BEGIN | |
| 5 | SELECT var1 + 2 AS result; | |
| 6 | END// | |
Пример2 IN
Так как параметр IN используется по умолчанию, его можно не обязательно писать.
CREATE PROCEDURE sp_in(p VARCHAR(11)) SET @x = P;
CALL sp_in('Hello world');
SELECT @x
Пример3 IN
Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения. Далее с этим параметром работают как с обычной переменной внутри процедуры:
|
|
|
CREATE PROCEDURE `procedure1`(IN iInput1 INT, IN iInput2 INT)
SELECT * FROM `data` WHERE `id` = iInput1 AND `id2` = iInput2;
Пример1 параметра OUT
| 1 | DELIMITER // |
| 3 | CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) | |
| 4 | BEGIN | |
| 5 | SET var1 = 'This is a test'; | |
| 6 | END // | |
Пример2 OUT
SET @x=”Something”;
CREATE PROCEDURE sp_out(OUT p VARCHAR(10)) SET P=”Hello world”;
CALL sp_out(@x);
SELECT @x
Пример1 параметра INOUT
| 1 | DELIMITER // |
| 3 | CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) | |
| 4 | BEGIN | |
| 5 | SET var1 = var1 * 2; | |
| 6 | END // | |
Пример2 INOUT
CREATE PROCEDURE sp_inout(INOUT P INT) SET @x=P*2;
set @a = 5;
CALL sp_inout(@a);
SELECT @x
Переменные
Создаются переменные и сохраняются внутри процедуры. Их объявляют в начале блока BEGIN/END, вместе с их типами данных. Как только объявлена переменная, ее можно использовать где переменные сессии, литералы или имена колонок.
Простые переменные
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;
Разница между простыми и системными переменными в том, что системные переменные доступны из вне хранимой процедуры. То есть, чтобы извлечь какие-то данные нужно пользоваться системными, а переменные которые нужны только внутри процедуры должны быть простыми.
|
|
|
Синтаксис объявления переменной:
| 1 | DECLARE varname DATA-TYPE DEFAULT defaultvalue; |
Пример1. Объявление нескольких переменных:
| 1 | DECLARE a, b INT DEFAULT 5; |
| 2 | DECLARE str VARCHAR(50); |
| 3 | DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; |
| 4 | DECLARE v1, v2, v3 TINYINT; |
Работа с переменными
После объявления переменной, необходимо задать ей значение с помощью команд SET или SELECT:
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) |
| 05 | DECLARE a, b INT DEFAULT 5; | |
| 06 | DECLARE str VARCHAR(50); | |
| 07 | DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; | |
| 08 | DECLARE v1, v2, v3 TINYINT; | |
| 10 | INSERT INTO table1 VALUES (a); |
| 11 | SET str = 'I am a string'; | |
| 12 | SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; | |
| 13 | END // |
Структуры управления потоками
MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Конструкции IF, CASE и WHILE, наиболее часто используются.
Синтаксис:IF условие THENдействие;
ELSE
действие;
END IF;
WHILE условие DO
действие;
END WHILE;
Конструкция IF
С помощью конструкции IF, выполняются задачи, содержащие условия:
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `proc_IF` (IN param1 INT) | |
| 04 | BEGIN | |
| 05 | DECLARE variable1 INT; | |
| 06 | SET variable1 = param1 + 1; | |
| 08 | IF variable1 = 0 THEN |
| 09 | SELECT variable1; | |
| 10 | END IF; | |
| 12 | IF param1 = 0 THEN |
| 13 | SELECT 'Parameter value = 0'; | |
| 14 | ELSE | |
| 15 | SELECT 'Parameter value <> 0'; | |
| 16 | END IF; | |
| 17 | END // |
Конструкция CASE
CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `proc_CASE` (IN param1 INT) | |
| 04 | BEGIN | |
| 05 | DECLARE variable1 INT; | |
| 06 | SET variable1 = param1 + 1; | |
| 08 | CASE variable1 |
| 09 | WHEN 0 THEN | |
| 10 | INSERT INTO table1 VALUES (param1); | |
| 11 | WHEN 1 THEN | |
| 12 | INSERT INTO table1 VALUES (variable1); | |
| 13 | ELSE | |
| 14 | INSERT INTO table1 VALUES (99); | |
| 15 | END CASE; |
| 17 | END // |
или:
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `proc_CASE` (IN param1 INT) | |
| 04 | BEGIN | |
| 05 | DECLARE variable1 INT; | |
| 06 | SET variable1 = param1 + 1; | |
| 08 | CASE |
| 09 | WHEN variable1 = 0 THEN | |
| 10 | INSERT INTO table1 VALUES (param1); | |
| 11 | WHEN variable1 = 1 THEN | |
| 12 | INSERT INTO table1 VALUES (variable1); | |
| 13 | ELSE | |
| 14 | INSERT INTO table1 VALUES (99); | |
| 15 | END CASE; |
| 17 | END // |
Конструкция WHILE
Существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Можно организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Пример цикла:
| 01 | DELIMITER // |
| 03 | CREATE PROCEDURE `proc_WHILE` (IN param1 INT) | |
| 04 | BEGIN | |
| 05 | DECLARE variable1, variable2 INT; | |
| 06 | SET variable1 = 0; | |
| 08 | WHILE variable1 < param1 DO |
| 09 | INSERT INTO table1 VALUES (param1); | |
| 10 | SELECT COUNT(*) INTO variable2 FROM table1; | |
| 11 | SET variable1 = variable1 + 1; | |
| 12 | END WHILE; | |
Дата добавления: 2019-11-16; просмотров: 158; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!