42308

Хранимые процедуры в MySQL

Лабораторная работа

Информатика, кибернетика и программирование

Введение Хранимые процедуры один из наиболее мощных инструментов предлагаемых разработчикам приложений баз данных MySQL для реализации бизнеслогики. Хранимые процедуры англ stoied proceduies позволяют реализовать значительную часть логики приложения на уровне базы данных и таким образом повысить производительность всего приложения централизовать обработку данных и уменьшить количество кода необходимого для выполнения поставленных задач. Помимо этих широко известных преимуществ использования хранимых процедур общих для большинства...

Русский

2013-10-29

94 KB

18 чел.

Лабораторная работа №1 «Хранимые процедуры в M

ySQL»

3.1.Цель работы

- изучить виды используемых в MySQL хранимых процедур.

- получить навыки работы с хранимыми процедурами с помощью команд SQL и с помощью утилиты dbForge for MySQL.

3.2. Введение

Хранимые процедуры - один из наиболее мощных инструментов, предлагаемых разработчикам приложений баз данных MySQL для реализации бизнес-логики. Хранимые процедуры (англ, stoied proceduies) позволяют реализовать значительную часть логики приложения на уровне базы данных и таким образом повысить производительность всего приложения, централизовать обработку данных и уменьшить количество кода, необходимого для выполнения поставленных задач. Практически любое достаточно сложное приложение баз данных не обходится без использования хранимых процедур.

Помимо этих широко известных преимуществ использования хранимых процедур, общих для большинства реляционных СУБД, хранимые процедуры InterBase могут играть роль практически полноценных наборов данных, что позволяет использовать возвращаемые ими результаты в обычных SQL-запросах.

3.3. Методика выполнения работы

1. Изучить рекомендации по выполнению работы и определить вариант задания.

2. Разработать текст хранимых процедур для трех запросов в соответствии с вариантом задания к лабораторной работе №4.

3. Модифицировать приложение в соответствии с проведенной модификацией базы данных. Добиться корректной работы приложения. Организовать запрос к данным в приложении через хранимые процедуры SQL.

3.4. Варианты заданий

Исходными данными для лабораторной работы являются варианты заданий и результаты предыдущих лабораторных работ.

3.5. Содержание отчета

Отчет должен содержать описание и результаты работы, представляемые в следующей последовательности:

1.  Описание бизнес-правил, реализованных с помощью хранимых процедур.

2. Распечатка сценария создания хранимых процедур с комментариями к созданным хранимым процедурам.

3.6. Контрольные вопросы

1. Дайте определение хранимой процедуры.

2. Как создается хранимая процедура?

3. Как задать выходные параметры хранимой процедуры?

4. Как присваиваются значения переменным?

5. Как организовать цикл в хранимой процедуре?

6. Как оформить условный переход в хранимой процедуре?

7. Как обработать ошибки исполнения хранимой процедуры?

3.7. Рекомендации по выполнению работы

3.7.1. Понятие хранимой процедуры

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

Причина их создания ясна и подтверждается частым использованием.

За

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

Против

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

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

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

CREATE PROCEDURE p2()

 SQL SECURITY INVOKER

 COMMENT 'Это моя первая процедура'

BEGIN

 SELECT 'Привет';

END

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

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

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

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

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

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

CALL p2();

CALL stored_procedure_name (param1, param2, ....)  

CALL procedure1(10 , 'string parameter' , @parameter_var);

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

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

  •  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

CREATE PROCEDURE proc_IN (IN var1 INT)  

BEGIN  

   SELECT var1 + 2 AS result;  

END  

Пример параметра OUT

CREATE PROCEDURE proc_OUT (OUT var1 VARCHAR(100))  

BEGIN  

   SET var1 = 'This is a test';  

END  

Пример параметра INOUT

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;

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд:

 

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

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:

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. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.

 

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

или:

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. Вот пример цикла:

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;                                

В этом примере мы проведем кое-какие простые операции с использованием курсора:

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

У курсоров есть три свойства, которые вам необходимо понять, чтобы избежать получения неожиданных результатов:

  •  Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
  •  Доступен только для чтения: курсоры нельзя изменять.
  •  Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.

Заключение

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

Примеры:

CREATE PROCEDURE test.p2(IN P_bdate DATE)

 SQL SECURITY INVOKER

 COMMENT 'Это моя первая процедура'

BEGIN

SELECT * FROM persona WHERE bdate<=P_bdate;

END

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;

END


 

А также другие работы, которые могут Вас заинтересовать

5666. Расчет электростанции теплохода Лагода 390.5 KB
  Данные электростанции теплохода проект №112 название головного судна Ладога Расчет производится табличным методом, исходя из того, что СЭС должна полностью обеспечивать электроэнергией все установленные на судне приемники при всех режимах работы. Ро...
5667. Культурология в системе наук. Шпаргалка 239.5 KB
  Предмет культурологии. Культурология в системе гуманитарного знания. Культурология - наука о наиболее общих закономерностях развития культуры, о множественности развития различных культур. Предмет - культура, взятая как целостное явлени...
5668. Бизнес-планирование. Комбинированный урок 44 KB
  Бизнес-планирование Предмет: технология Класс: Цели занятия: Ознакомление с сущностью бизнес-планирования 2. Выступить с докладами и выслушать доклады других по темам: Роль бизнес-планирования в организации ПД, Специфика бизнес-планир...
5669. Языки программирования. Курс лекций 316 KB
  Программа курса Языки программирования Основные понятия языков программирования: данные, операции и связывание. ОД(объект данных) - переменные, константы - абстрактное место, где могут храниться данные. Виды связывания: 1)...
5670. Будівельні матеріали. Загальні технічні властивості будівельних матеріалів 164.85 KB
  Вступ Ця дисципліна вивчає будівельні матеріали і вироби, їх значення для розвитку будівництва та підвищення ефективності капіталовкладень. Приділяється увага питанням класифікації будівельних матеріалів, їх складу і структури, корозії матеріалів, е...
5671. В поисках мелодии. Конспект урока 20.31 KB
  В поисках мелодии Цель. Развитие творческих способностей детей посредством элементарного музицирования с использованием ИКТ. Задачи. Воспитывать интерес и любовь к музыке и музицированию. Развивать эмоциональность детей как важнейшую основу их внутр...
5672. Правознавство. Галузі права. Конспект лекцій 1.38 MB
  Посібник містить конспект лекцій для студентів усіх напрямів денної та заочної форми навчання, короткий словник юридичних термінів. Видання також містить питання до екзамену та список рекомендованої літератури. ЗМІСТ ЛЕКЦІЯ 1. Основи державного (кон...
5674. Логістична підтримка інноваційної діяльності 472.79 KB
  Перехід до інноваційної моделі розвитку економіки - найхарактерніша прикмета сучасного етапу в розвинутих країнах. Реалізація економічних цілей пов'язана з інноваційним типом розвитку, в основі якого закладений безперервний і цілеспрямований процес пошуку