Тема: ИЗУЧЕНИЕ ПРОГРАММНЫХ СРЕДСТВ РАЗРАБОТКИ

СЕРВЕРНОЙ БИЗНЕС-ЛОГИКИ В СУБД MYSQL 5

Цель работы:

Ознакомиться с основными функциональными возможностями СУБД MySQL 5 для разработки объектов серверной бизнес-логики.

Общие сведения

СУБД MySQL 5 является системой, поддерживающей многоуровневые модели клиент-серверного взаимодействия. В практическом плане это означает, что MySQL 5 обладает достаточной функциональностью для переноса части бизнес-логики предметной области разрабатываемого приложения на сторону сервера БД путем создания соответсвующих объектов бизнес-логики (ОБЛ)). Основными типами ОБЛ являются такие серверные программные компоненты как

● представления (views).

● хранимые процедуры (stored procedures);

● триггеры (triggers),

Создание и использование ОБЛ при работе с некоторой БД позволяет решить следующие основные задачи:

● автоматизировать программирование сложных запросов к БД, за счет повторного использования ранее созданных и отлаженных ОБЛ;

● повысить производительность обработки данных, поскольку эти ОБЛ выполняются на сервере БД, который как правило, является более мощном, чем компьютеры клиентских приложений;

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

В конечном счете, разработка и применение ОБЛ в составе некоторой БД позволяет повысить надежность, производительность и масштабируемость соответствующей информационной системы.

Представление – это ОБЛ, представляющий собой виртуальную таблицу данных, которая является результатом выполнения заранее сконструированного запроса к одной или нескольким таблицам БД. Изменение данных в реальной таблице (таблицах) БД немедленно отражается в содержимом всех представлений, построенных на основании этой таблицы (таблиц).

Хранимая процедура (функция) – это ОБЛ, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере БД. Хранимые процедуры аналогичны обыкновенным процедурам языков высокого уровня, у них могут быть определены входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах также могут выполняться операции с базами данных, реализованные как с помощью DDL так и DML. Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления потоком.

Триггер - это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено определенной модификацией данных в заданной таблице или столбце реляционной базы данных. Триггеры применяются в основном для обеспечения целостности данных в схеме БД. Триггер запускается сервером

автоматически при попытке изменения данных в таблице, с которой он

связан. Все производимые им модификации данных рассматриваются как

одна транзакция. В случае обнаружения ошибки или нарушения целостности

данных происходит откат этой транзакции.

Особенности программной разработки ОБЛ в среде СУБД MySQL 5.

При работе с ОБЛ необходимо помнить, что в MySQL 5 они, как правило, поддерживаются только таблицами типа InnoDB (в пятой версии это тип таблицы по умолчанию).

Представления

MySQL5 поддерживает следующий синтаксис создания представлений:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]__

[SQL SECURITY { DEFINER | INVOKER }]

VIEW имя_представления [(список_столбцов)]

AS запрос_выборки

[WITH [CASCADED | LOCAL] CHECK OPTION]

Параметр CREATE / REPLACE определяет, создается ли новое или заменяется существующее представление

Параметр ALGORITHM определяет то, каким образом СУБД будет обрабатывать данное представление.

Для того чтобы представление было изменяемым (запросы типа INSERT, UPDATE, DELETE выполняются для данного представления и изменяют данные в таблицах на которые данное представление ссылается), на запрос_выборки накладываются те же ограничения, что и для использования алгоритма MERGE, и, кроме того, нельзя использовать следующие конструкции:

− Подзапрос после SELECT

− Join

− Необновляемое представление после FROM

− Подзапрос в WHERE использующий таблицу из FROM__

Пример:Создать представление, содержащее список студентов из таблицы students, и наименований академических групп в которых эти студенты учатся (таблица students содержит внешний ключ, ссылающийся на таблицу groups, откуда и может быть получено наименование академической группы). Запрос для создания такого представления имеет следующий вид:

CREATE

ALGORITHM = MERGE

VIEW student_group

AS

SELECT student_id, students.group_id, firstname, lastname, group_name

FROM students INNER JOIN groups ON (groups.group_id =

students.group_id);

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

INSERT INTO student_group (firstname, lastname, group_id) VALUES …

INSERT INTO student_group (group_name) VALUES …..

и т.д.

Но запрос вида:

INSERT INTO student_group (firstname, lastname, group_id, group_name)

не выполнится, так как включает в себя поля из разных таблиц. Аналогично будут выполняться запросы на обновление полей из отдельных таблиц, но не запросы на обновление полей из нескольких таблиц одновременно. Запросы типа DELETE также не будут выполняться для данного представления. EMS SQL MANAGER 2005 LITE (впрочем как и полнофункциональная версия) не представляет интерактивного режима для задания представлений (создание нового представления должно быть осуществлено непосредственно путем написанием соответствующего SQL кода), вместе с тем представления, созданные в текущей БД могут быть просмотрены (доступен код для создания, перечень полей и данные) в подменю Views, расположенного в левой части экрана.

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

MySQL 5 для хранимых процедур применяет синтаксис SQL:2003, который также используется в IBM DB2.

Хранимые процедуры и функции представляют собой подпрограммы, создаваемые с помощью операторов CREATE PROCEDURE и CREATE FUNCTION. Процедура вызывается с помощью оператора CALL и может только передавать значения обратно, используя выходные переменные. Функции могут возвращать скалярное значение и вызываются из оператора точно так же, как и любые другие функции (то есть, через указание имени функции). Хранимые процедуры могут вызывать другие хранимые процедуры.

В MySQL 5 используется следующий синтаксис задания хранимых процедур

и функций:

CREATE

[DEFINER = { имя пользователя | CURRENT_USER }]

PROCEDURE

имя_хранимой_процедуры ([параметр[, ...]])

[характеристика ...]

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

CREATE

[DEFINER = {имя пользователя | CURRENT_USER }]

FUNCTION имя_хранимой__процедуры ([параметр[, ...]])

[RETURNS тип]

[характеристика ...]

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

При этом:

параметр:

[ IN | OUT | INOUT ] - вид параметра

тип:

Любой допустимый тип данных MySQL.

характеристика:

LANGUAGE SQL

| [NOT] DETERMINISTIC

I SQL SECURITY {DEFINER I INVOKER}

| COMMENT 'строка1

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

BEGIN

/* набор допустимых операторов SQL */

END;

Параметр DEFINER – опциональное условие, устанавливает какая учетная запись MySQL должна быть использована при проверке привилегий во время запуска данной функции/процедуры (данное необязательное условие используется только в ветке 5.1).

Конструкция COMMENT является расширением MySQL и может использоваться для описания хранимой процедуры. Такая информация отображается операторами SHOW CREATE PROCEDURE И SHOW CREATE FUNCTION.

Ниже представлен пример простой хранимой процедуры с аргументом

OUT:

CREATE PROCEDURE simpleproc (OUT paraml INT)

BEGIN

SELECT COUNT(*) INTO paraml FROM students;

END;

После того, как данная процедура будет успешно создана, следующие

команды, выполненные из консоли:

CALL simpleproc(@a);

SELECT @a; /* здесь @a – имя произвольное имя переменной */

вернут следующий результат:

В EMS Manager для работы с хранимыми процедурами существует удобный интерфейс (см Рисунок 7.1). Данный интерфейс доступен из подменю Procedures выбранной базы данных.

Рисунок 7.1 – Утилита EMS SQL Manager Lite for MySQL: интерфейс пользователя в режиме редактирования хранимой процедуры.

Для сохранения процедуры используется ссылка Compile, для запуска Execute. В случае, если процедура содержит входные параметры (параметры задаются в Parameters), после запуска процедуры на выполнения EMS SQL Manager Lite for MySQL выведет специальное окно для ввода параметров.

Рассмотрим пример обработки условий:

CREATE PROCEDURE handlerdemo ()

BEGIN

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 =

122;

SET @x = 1;

INSERT INTO groups (group_id, group_name) VALUES (5,'if-34a');

SET @x = 2;

INSERT INTO groups (group_id, group_name) VALUES (5,'if-34a');

SET @x = 3;

END

После того, как данная процедура будет успешно создана, следующие

команды, выполненные из консоли:

CALL handlerdemo();

SELECT @x, @x2;

вернут результат:

Обратите внимание: @х равен З, что указывает на то, что MySQL выполнил процедуру до конца. Если бы строка DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 122; представлена не была (23000 – код обозначающий дублирование уникального ключа), MySQL принял бы значение пути по умолчанию (EXIT) после второй неудачной из- за ограничения PRIMARY KEY попытки выполнить INSERT, и оператор SELECT @x возвратил бы значение 2. Вместе с тем некоторая сигнальная переменная @x2 приняла значение 122 (в данном примере число выбрано произвольным образом), что позволяет разработчику понять, что ошибка все же случилась.

Курсоры

В хранимых процедурах и функциях поддерживаются простые курсоры.

Синтаксис такой же, как во встроенном SQL.

Оператор объявления курсора DECLARE

DECLARE имя_курсора CURSOR FOR onepaтop_sql

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

Оператор открытия курсора OPEN

OPEN имя_курсора

Данный оператор открывает объявленный ранее курсор.

Оператор выборки курсора FETCH

FETCH имя курсора INTO имя переменной [, имя_переменной] ...

Данный оператор выполняет выборку следующей строки (если строка существует) с помощью указанного открытого курсора и продвигает указатель курсора.

Оператор закрытия курсора CLOSE

CLOSE имя_курсора

Данный оператор закрывает открытый ранее курсор.