Знакомство с хранимыми процедурами

Написано в основном по статье Введение в хранимые процедуры.

Введение

Хранимые процедуры поддерживаются MySQL с 5-й версии. Хранимая процедура – это способ инкапсуляции повторяющихся действий. Отметим достоинства и недостатки использования хранимых процедур.

Достоинства

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

Недостатки

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

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

Рассмотрим код процедуры, выводящей «Привет, мир!» Примеры приводятся в системе phpMyAdmin, в которой процедура может быть создана или через SQL-запрос, или через меню Процедуры.

Если создаем через меню Процедуры, то в определение надо указать код, расположенный между BEGIN и END. Остальные параметры задаются через интуитивно понятные элементы формы.

hr_proc_1

CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`()
    NO SQL
    DETERMINISTIC
    COMMENT '''A procedure'''
BEGIN 
    SELECT ' Привет, мир!';
END

 

Некоторые замечания:

  • названия процедур чувствительны к регистру;
  • имена процедур должны быть уникальны;
  • внутри хранимой процедуры нельзя изменять базу данных.

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

CALL stored_procedure_name (param1, param2, ….)

где stored_procedure_name – название процедуры.

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

Способы изменения:

  • команда ALTER PROCEDURE, но она изменяет лишь некоторых характеристики;
  • удалить процедуру и создать ее снова;
  • в системе phpMyAdmin можно изменять процедуру через меню.

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

DROP PROCEDURE IF EXISTS p2;

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

Использование параметров хранимых процедур

У процедур могут быть 4 типа параметров

  • без параметров – наш первый запрос относится к этому типу;
  • входящие параметры – IN – этот тип задается по умолчанию;
  • возвращаемые параметры – OUT;
  • параметр, одновременно и входящий, и выходящий – INOUT.

Рассмотрим примеры на эти типы параметров.

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_in`(IN `var1` INT)
    NO SQL
select var1+2 as result

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_out`(OUT `var1` VARCHAR(20))
    NO SQL
set var1='Привет'

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_inout`(INOUT `var1` INT)
    NO SQL
set var1=var1*2

Переменные

Объявление переменных

Переменные объявляют явно в начале блока BEGIN/END с указанием типа данных.

Синтаксис объявления переменной выглядит так:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Примеры:

DECLARE a, b INT DEFAULT 5; 
DECLARE v1, v2, v3 TINYINT;
DECLARE str VARCHAR(50);
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;

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

Переменной можно присваивать значение при помощи команд SET или SELECT.

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 = 'Строка';
SELECT CONCAT(str,paramstr), today, b FROM table2 WHERE b >=5;
END 

Замечание. Должны быть заранее созданы таблицы table1, table2.

Команда CONCAT – объединяет строки.

Поделиться:
  • Добавить ВКонтакте заметку об этой странице
  • Мой Мир
  • Facebook
  • Twitter
  • LiveJournal
  • FriendFeed
  • В закладки Google
  • Google Buzz
  • Яндекс.Закладки
  • StumbleUpon
  • Technorati
  • БобрДобр
  • Memori.ru
  • МоёМесто.ru

4 комментария к “Знакомство с хранимыми процедурами”

Комментарии закрыты.