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

Хранимые процедуры MySQL (часть 1)

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

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

Открываем phpmyadmin. Выбираем базу данных mytest и нажимаем на её заголовок, либо на значок Browse. Затем переходим на вкладку Routines и создаём новую процедуру, нажав на надпись Add routine.

вкладка Routines

Появится форма, которую необходимо заполнить.

форма

Routine Name (имя процедуры/функции) — ReplyToComment.

Type (тип) — процедура. Отличие процедуры от функции в том, что функция всегда возвращает какое-то значение и содержит оператор return.

Parameters (параметры) — наша процедура будет принимать два параметра: текст ответа и id комментария, на который мы отвечаем. Оба параметра будут передаваться из нашего клиентского приложения.

Создание параметров процедуры

Создадим первый параметр

Direction — указываем направление параметра (IN, OUT, INOUT). В нашей процедуре оба передаваемых параметра будут входящими (IN).

Name (имя параметра) — Content.

Type (тип) — INT, VARCHAR, DATETIME и так далее. Параметр Content содержит текст ответа, который будет храниться в столбце comment_content. Данная колонка имеет определенный тип, чтобы его определить, открываем таблицу wp_comments и переходим на вкладку Structure, находим нужное нам имя столбца и смотрим на его тип в колонке Type. В данном примере колонка имеет тип — text, этот же тип нужно указать для нашего параметра.

структура таблицы

Length/Values (длина или значение) для типа Text данное поле установить нельзя, но обычно здесь указывается длина, например VARCHAR(20), INT(10), либо какое-то значение по умолчанию.

Options — в качестве дополнительных опций можно указать текущую кодировку столбца, её так же можно посмотреть на вкладке Structure колонка Collation. Установим значение utf8.

результат

первый параметр

Добавим второй параметр, нажав на кнопку Add parameter.

Direction  -  IN
Name       -  ComID
Type       -  BIGINT
Length/Values - 20
Options  -  UNSIGNED

второй параметр

Оба параметра созданы, продолжаем заполнять форму.

Definition — здесь мы описываем тело процедуры. Тело представляет собой блок, который начинается с ключевого слова BEGIN и заканчиваются ключевым словом END. Внутри тела процедуры можно размещать текст запроса, объявлять переменные, использовать конструкции ветвления, циклы и многое другое, как в любом языке программирования.

тело процедуры

Для начала создадим блок начала и конца тела нашей процедуры.

BEGIN
END;

Теперь добавим текст запроса, который будет заполнять поля в таблице wp-comments, при добавлении нового комментария (ответа).

BEGIN
INSERT INTO wp_comments (comment_author, comment_author_email,
comment_content, comment_date,
comment_date_gmt, comment_post_id,
comment_parent, comment_approved, user_id)
VALUES (подставляемые значения);
END;

Хранить подставляемые значения будем в переменных. Для создания переменной используется ключевое слово DECLARE затем указывается имя, тип и длина переменной, так же можно указать значение по умолчанию. Если у переменной есть параметр DEFAULT, то такая переменная является инициализированной.

DECLARE имя тип (длина) DEFAULT значение по умолчанию; 

Так же можно установить значение, для любой переменной используя, оператор SET.

 SET имя переменной = значение;

И так создадим три переменных: Author, Email, UsedID, которые будут хранить значения для колонок: comment_author, comment_author_email, user_id.

BEGIN
DECLARE Author tinytext DEFAULT 'admin';
DECLARE UserID bigint(20) DEFAULT 1;
-- Объявили переменную Email
DECLARE Email varchar(100);
-- Установили значение переменной Email
SET Email = 'adm@local.local';
END;

comment_content — данная колонка хранит текст комментария, который передается в процедуру в виде входного параметра Cоntent. Мы не будем создавать отдельную переменную, а просто подставим в VALUES значение входного параметра.

comment_date и comment_date_gmt — обе колонки при первом заполнение будут иметь одинаковые значения. Создадим переменную Date и присвоим ей в качестве значения результат, который будет возвращать встроенная функция NOW. Данная функция возвращает текущую дату и время в формате DATETIME.

DECLARE MyCurDate DATETIME DEFAULT NOW();

либо так

DECLARE MyCurDate DATETIME;
SET MyCurDate = NOW();

comment_approved — Одобрен ли комментарий, 1 (да) иначе 0. Создадим переменную Approved, но перед установкой значения сделаем небольшую проверку.

DECLARE Approved varchar(20);
IF Author = 'admin' THEN
SET Approved = 1;
ELSE
SET Approved = 0;
END IF;

comment_parent — здесь в качестве значения нужно указать id комментария, на который мы отвечаем. ID передается в процедуру как второй входной параметр. Создадим переменную ParentCom и присвоем ей значение переданного параметра.

DECLARE ParentCom varchar(20);
SET ParentCom = ComID ;

Остался последний параметр comment_post_id — здесь нужно указать id поста, в котором будет размещён наш комментарий. Объявим переменную с именем PostID.

DECLARE PostID BIGINT(20);

на данный момент тело процедуры должно выглядеть так

BEGIN
-- блок объявления переменных
DECLARE Author tinytext DEFAULT 'admin';
DECLARE UserID bigint(20) DEFAULT 1;
DECLARE Email varchar(100);
DECLARE Date DATETIME DEFAULT NOW();
DECLARE ParentCom varchar(20);
DECLARE Approved varchar(20);
DECLARE PostID BIGINT(20);
-- Установка значений переменных
IF Author = 'admin' THEN
SET Approved = 1;
ELSE
SET Approved = 0;
END IF;
SET Email = 'adm@local.local';
SET ParentCom = ComID ;
-- запрос
INSERT INTO wp_comments (comment_author, comment_author_email,
comment_content, comment_date,
comment_date_gmt, comment_post_id,
comment_parent, comment_approved, user_id)
VALUES (Author, Email, Content, Date, Date, PostID, ParentCom,
Approved, UserID);
END;

вторая часть

Читайте также:

One Comment

  1. Георгий says:

    Спасибо! Очень помогло!

Leave a Reply

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*