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


 

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

33536. Идейно-тематические особенности рассказов М.Зощенко. Герои, конфликты 15.7 KB
  Несмотря на то что герой не считает себя удачливым в жизни так как выходит ему время от времени перетык и прискорбный случай он философствует Жизнь штука не простая а сложная имеет на все свои взгляды: и на мужицкую жизнь блекота и слабое развитие техники и на культуру иностранную которую он знает. Я всегда стремился к изображению положительных сторон жизни. которые проповедовали свободу искусства от политики изображали действительность исходя из фактов жизни быта. Главным фактом в то время была революция которую...
33537. Повесть В.Распутина «Прощание с Матерой» как итоговое произведение «деревенской» прозы 17.11 KB
  Жанр повести можно определить как философскую притчу. Таким образом один из основных философских смыслов повести заключается в том что не нами начинается жизнь на земле и не нашим уходом заканчивается. В повести двадцать две главы в которых воспроизводится быт жителей Матеры в последние три месяца их пребывания на острове. Трагическая развязка повести проявляет авторскую позицию.
33538. «Матренин двор» А. Солженицына как начало второго этапа развития «деревенской прозы. Особенности этого этапа 17.23 KB
  Хозяйка дома Матрена одинокая женщина лет шестидесяти. Матрена Васильевна избу не жалела ни для мышей ни для тараканов ибо в шуршании мышей непрерывном как далекий шум океана шорохе тараканов не было ничего злого не было лжи. Матрена отличалась трудолюбием: вставала в четырепять утра тихо вежливо стараясь не шуршать топила русскую печь ходила доить козу по воду ходила и варила в трех судочках . Матрена никому не могла отказать: без нее ни одна пахота не обходилась.
33539. Основные конфликты «деревенской» прозы 50-х гг 15.2 KB
  Деревенская проза ведет свое начало с 50х годов. Очеркистыдеревенщики 50 60х годов не позволяли себе сомневаться в необходимости колхозов не поднимали руку на то как осуществлялось партийное руководство ими но показывали сколько вреда наносят бездумные директивы галочная система. 50е ГОДЫ – овечкинский этап – МОМЕНТ ПРОЗРЕНИЯ после лакировочнобесконфликтного наваждения 40х годов. Овечкинское направление в литературе 50х годов было: резкой реакцией на литературную мифологию 40х годов; возвращением деревенской прозы...
33540. «Василий Теркин» А.Твардовского. Образ героя, художественные особенности 20.78 KB
  Твардовский возобновил работу над образом Василия Теркина которую он начал еще в 1940 г. Твардовский развертывает биографию Теркина как судьбу многих бойцов как обобщение тяжелого и славного пути всей Советской Армии. Создавая образ Теркина автор типизировал массовые явления действительности и прямо указывал на распространенность таких героев: Парень в этом роде В каждой роте есть всегда Да и в каждом взводе. Твардовский по его признанию освобождал ее от всего что сводит книгу к какойто частной истории мельчит ее лишает ее той...
33541. Тип героя и конфликты в рассказах В.Шукшина 16.71 KB
  Автор настойчиво подчеркивает его чудаковатость которая отличает героя от других правильных людей. Создается проблемная для героя ситуация: тайком присвоить бумажку или объявить всем о находке и отдать ее владельцу ведь она этакая зеленая дурочка лежит себе никто ее не видит. Употребляя по отношению к неодушевленному предмету слово дурочка Шукшин передает нюансы душевного состояния героя: радость от находки и от сознания того что никто кроме него не видит бумажку.
33542. Произведение “Царь-рыба” 13.6 KB
  Здесь он царь царь природы. И ведет он себя как царь: он аккуратен все свои дела доводит до конца. Но зачем природе царь не ценящий богатства которым владеет Неужели она покорится и не свергнет его Тогда появляется царьрыба царица рек посланная для борьбы с царем при роды. Каждый рыбак мечтает поймать царьрыбу ведь это знак свыше.
33543. Привычное дело 14.36 KB
  В новом варианте автор расширил объем повести углубил ее проблематику расширил временные рамки. Произведение было переработано стилистически например автор оформил частично заново отношение между речью повествователя и персонажей. Автор с большой любовью и уважением относится к своим героям. Автор не наделяет своего героя какимито особенными качествами и талантами.
33544. За повестью Ю. Бондарева «Горячий снег» 14.92 KB
  Всю военную прозу можно условно разделить на две группы: произведения написанные в годы войны и произведения послевоенного периода. Впервые в советской прозе так мощно и пристрастно зазвучал голос художника изнутри войны. Все 4 года войны они прожили не переводя дыхания и казалось концентрация деталей эпизодов конфликтов ощущений потерь образов солдат пейзажей запахов разговоров ненависти и любви была настолько густа и сильна после возвращения с фронта что просто невозможно было всё это организовать найти необходимый сюжет...