Разработка внешних хранимых процедур в MySQL

1.

Разработка внешних хранимых
процедур в MySQL

2.

Введение
• Хранимые процедуры и функции являются важнейшим элементом современных
промышленных СУБД. В MySQL хранимые процедуры поддерживаются начиная с версии 5.
Хранимые процедуры и функции (а также триггеры как разновидность процедур) реализуются
в виде подпрограмм. При этом процедуры и функции называются хранимыми т.к. они
хранятся в базе данных наряду с другими объектами (таблицами, ограничениями и т.д.).
Хранимые подпрограммы представляют собой набор команд SQL. Кроме операторов SQL в
хранимой подпрограмме могут быть использованы основные элементы свойственные любому
языку программирования – переменные, операторы ветвления, циклы и др.
• Хранимые процедуры и функции позволяют реализовать часть логики работы прикладной
системы на стороне сервера, что оптимизирует производительность, упрощает разработку
клиентской части системы, существенно повышает безопасность. Преимущество, связанное с
безопасностью, заключается в защите программного кода бизнес — логики от
несанкционированного просмотра и изменения. Защитить подпрограмму, которая находится
на сервере намного проще, чем гарантировать безопасность клиентского приложения.

3.

Первая хранимая
процедура
• Для начала открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую
схему (базу данных): щелкаем Catalogs, выбираем Create New Schema в области Schemata
(Ctrl+N). Даём название. Открываем только что созданную схему, выбираем вкладку Stored
procedures и щелкаем кнопку Create Stored Proc. Назовём свою процедуру procedure1. В тело
процедуры (между BEGIN и END) впишем следующее:
• Execute SQL - процедура создана. Откроем MySQL Query Browser, выберем свою схему и
впишем следующий запрос:

4.

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

5.

Параметры в хранимых
процедурах
и циклы
• Для начала изменяем первую строку,
объявляющую саму процедуру:
Здесь, ключевое слово IN указывает на то, что параметр указан только для чтения.
• Далее с этим параметром работаем как с обычной
переменной внутри процедуры:
• При написании процедур нам нужны циклы. Их
структура приведена ниже:

6.

Пример
• Один из примеров применения хранимых процедур – когда нам нужно объединить несколько запросов в один
например добавление темы в форум и увеличение общего количества тем. Создаем таблицу threads:
title будет заголовком новой темы
• Так же создадим таблицу с различными статистическими переменными:
• Предположим у нас есть запись с name=threads и value=0. Создадим новую хранимую процедуру:
Таким образом, вместо того, чтобы передать два или больше запросов (например через php), мы можем передать
один. Благодаря этому мы получаем оптимизацию, чистый код и возможность изменить в любой момент не
затрагивая другие скрипты.

7.

Курсоры (MySQL
Cursors)
• Курсоры позволяют пройтись по всем полученным результатам запроса. Добавим еще одну
таблицу к нашей базе данных - tags:
• В эту таблицу мы будем записывать все тэги из всех тем. Хранимая
процедура(procedure3) будет выглядеть так:

8.

Извлечение
данных
• Нам нужно получить общее количество тегов и тем. Для этого создадим еще
одну процедуру(procedure4) и перейдем к ней:
• Далее объявляем две переменных - iTags - количество тегов, и iThreads - общее
количество тем. Далее делаем два простых запроса на выборку, заполняя наши
переменные. Присваиваем системным переменным значения текущих простых
переменных. При вызове данная процедура ничего не возвращает, но после ее
вызова мы можем считать требуемые значения из системных переменных: