name type security_type comment

Test PROCEDURE DEFINER

mysql> ALTER PROCEDURE test

−> SQL SECURITY INVOKER

−> COMMENT ‘Процедура возвращает версию сервера';

mysql> SELECT name, type, security_type, comment

−> FROM mysql.proc

−> WHERE db = ‘test’;

Результат выполнения запроса:

Name type security_type comment

Test PROCEDURE DEFINER Процедура возвращает версию сервера

ЛАБОРАТОРНАЯ РАБОТА № 3. Триггеры. Представления

Триггеры

Триггер – это та же хранимая процедура, но привязанная к событию изменения содержимого таблицы (INSERT, DELETE, UPDATE).

Синтаксис оператора:

CREATE TRIGGER имя_триггера момент_выполнения_триггера событие

ON имя_таблицы FOR EACH ROW тело_триггера

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

Момент_выполнения_триггера может принимать два значения:

· BEFORE - действия триггера производятся до выполнения операции изменения таблицы;

· AFTER – действия триггера производятся после выполнения операции изменения таблицы.

Конструкция событие показывает, на какое из событий должен реагировать триггер, и может принимать три значения: INSERT, DELETE, UPDATE/

Триггеры сложно использовать, не имея доступа к новым записям, которые вставляются в таблицу, или старым записям, которые обновляются или удаляются. Для доступа к новым и старым записям используются префиксы NEW и OLD соответственно. То есть усли в таблице обновляется поле GOR, то получить доступ к старому значению можно по имени OLD.GOR, а к новому – NEW.GOR.

Пример. Создадим триггер, который при добавлении нового поставщика в таблицу Post будет присваивать значение 1 пользовательской переменной @tot.

mysql> CREATE TRIGGER sub_count AFTER INSERT ON Post

−> FOR EACH ROW

−> BEGIN

−> SET @tot = 1;

−> END

−> / /

mysql> SELECT @tot / /

Результат:

@tot

Отредактируем триггер таким образом, чтобы к пользовательской переменной @tot прибавлялось каждый раз число существующих поставщиков, определенных в поле PN таблицы Post.

mysql> CREATE TRIGGER sub_count AFTER INSERT ON Post

−> FOR EACH ROW

−> BEGIN

−> SET @tot = @tot + NEW.PN;

−> END

−> / /

Запрос

mysql> SELECT @tot / /

даст ответ

@tot

В результате выполнения запросов

mysql> INSERT INTO Post

−> VALUES (NULL, ‘Сидоров’, 30,’Красноярск’) / /

−> SELECT @tot / /

получим

@tot

или другое значение (все зависит от количества уже имеющихся поставщиков м таблице Post.

Часто при обновлении одних полей таблицы операторы баз данных забывают обновить связанные поля таблицы или производится попытка добавления некорректных значений. Пусть в текущей базе данных имеется таблица Pokupatel. Пусть при добавлении нового покупателя необходимо преобразовать имена и отчества покупателей в инициалы (поля nameи otch соответственно). Ниже приведен триггер для обработки этой ситуации. В поле fam вводится фамилия покупателя.

mysql> CREATE TRIGGER restrict_user BEFORE INSERT ON Pokupatel

−> FOR EACH ROW

−> BEGIN

−> SET NEW.name = LEFT (NEW.name, 1);

−> SET NEW.otch = LEFT (NEW. otch, 1);

−> END

−> / /

mysql> INSERT INTO Pokupatel

VALUES (NULL, ‘Ремизов’, ‘Сергей’, ‘Алексеевич’ / /

mysql> SELECT , name, otch FROM Pokupatel

−> WHERE id_ Pokupatel = LAST_INSERT_ID ( ) / /

Ответ будет такой

Fam name otch

Ремизов С А

Удаление триггера производится оператором DROP TRIGGER, который имеет следующий синтаксис:

DROP TRIGGER Имя_таблицы.Имя_триггера

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

Представление – это запрос на выборку, которому присваивается уникальное имя и который можно сохранять или удалять из базы данных. Представления позволяют увидеть результаты сохраненного запроса, таким образом, как будто это полноценная таблица базы данных.

Преимущества использования представлений:

· Безопасность – каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать;

· Простота запросов – с помощью представления можно извлечь данные из нескольких таблиц и представить их как одну таблицу, заменяя запрос ко многим таблицам в однотабличный запрос к представлению;

· Простота структуры – представления позволяют создать для каждого пользователя собственную «структуру» базы данных, отображая только те данные, которые ему нужны, и «не засоряя» результаты вспомогательными столбцами, которые пользователю заведомо не пригодятся;

· Защита от изменений – в связи с оптимизацией скорости, таблицы и структура могут постоянно претерпевать изменений и переименовываться. Представления позволяют создавать виртуальные таблицы со старыми именами и структурой, позволяя избежать модификации внешней прикладной программы;

Помимо преимуществ, описанных выше, представления обладают рядом недостатков:

· Производительность – представления создают лишь видимость существования соответствующей таблицы, и СУБД MySQL приходится преобразовывать запрос к представлению в запрос к исходным таблицам. Если представление отображает многотабличный запрос, то простой запрос к представлению становится сложным объединением и на его выполнение может потребоваться много времени;

· Ограничение на обновление – когда пользователь пытается обновить строки представления, СУБД MySQL необходимо обновить строки в исходных таблицах. Это возможно только для простых представлений. Сложные представления обновить нельзя, они доступны только для выборки.

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

Создание представления осуществляется оператором CREATE VIEW, который имеет следующий синтаксис:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED │MERGE

│TEMPTABLE}]

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

AS оператор_SELECT

[WITH [CASCADED │LOCAL] CHECK OPTION]

Оператор создает представление с указанным именем со столбцами, перечисленными в списке, на основании SQL-запроса.

Пример. Создадим представление по имени Post_1, в котором будем отображать имена и города поставщиков из таблицы Post.

mysql> CREATE VIEW Post_1 (PIM, GOR)

−> AS

−> SELECT PIM, GOR

−> FROM Post ;

Представление рассматривается СУБД MySQL как полноценная таблица и может быть просмотрено в списке таблиц базы данных при помощи оператора SHOW TABLES.

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

Использование вложенных запросов при формировании представлений не совсем рационально, так как это может привести к потере производительности.

Предложение ALGORITHM определяет алгоритм формирования конечного запроса с участием представления и может принимать три значения:

· MERGE – при использовании данного алгоритма запрос объединяется с представлением таким образом, что представление заменяет собой соответствующие части в запросе.

· TEMPTABLE – результирующая таблица представления помещается во временную таблицу, которая затем используется в конечном запросе.

· UNDEFINED – в данном случае СУБД самостоятельно пытается выбрать алгоритм, предпочитая использовать подход MERGE и прибегая к алгоритму TEMPTABLE (создание временной таблицы) только в случае необходимости, так как метод MERGE более эффективен.

Если ни одно из значений ALGORITHM не указано, то по умолчанию назначается UNDEFINED.

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

CREATE TABLE имя_базы_данных.имя_представления

Несмотря на то, что для создания представлений в качестве SELECT-запроса могут выступать практически любые запросы, имеется несколько ограничений:

· SELECT-запрос не может содержать подзапрос в предложении FROM;

· SELECT-запрос не может ссылаться на системную или пользовательскую переменную.

· Любые таблицы или представления, на которые в свою очередь ссылается представление, должны существовать физически.

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

· Представления не могут ссылаться на временные таблицы и сами представления не могут быть объявлены временными при помощи ключевого слова TEMPORARY.

· Триггер нельзя связывать с представлением.

Предложение WITH CHECK OPTION добавляется для представлений, к которым могут быть применены операторы INSERT и UPDATE. В этом случае происходит проверка, чтобы вставляемые данные удовлетворяли WHERE-условию SELECT-запроса, лежащего в основе представления.

Ключевые слова LOCAL и CASCADED в предложении WITH CHECK OPTION определяют область видимости ограничения применительно к другим представлениям. Ключевое слово LOCAL сообщает, что ограничение WITH CHECK OPTION распространяется только на текущее представление, а WHERE-ограничения представлений, на которые ссылается текущее представление, не влияют на процесс вставки новых данных. Тогда как ключевое слово CASCADED требует, чтобы проверка на соответствие вставляемых данных учитывала WHERE-условия и тех представлений, на которые ссылается текущее представление.

Удаление представлений осуществляется оператором

DROP VIEW [IF EXISTS]

Имя_представления [, имя_представления] . . .

Редактирование представлений осуществляется оператором

ALTER [ALGORITHM = {UNDEFINED │MERGE │TEMPTABLE}]

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

AS оператор_SELECT

[WITH [CASCADED │LOCAL] CHECK OPTION]

Оператор SHOW CREATE VIEW позволяет просмотреть структуру оператора CREATE VIEW, при помощи которого было создано представление.

Поиск по сайту: