Представления и хранимые процедуры и триггеры.

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

R1(Касса, ВидОперации, Сумма) – таблица содержит информацию о приходах и расходах денег в кассу.

Запрос будет следующий

SELECT sum(

CASE

WHEN ВидОперации=’Приход’ THEN Сумма

ELSE -1*Сумма

END)

FROM R1;

 

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

Вместо того, чтобы составлять такой запрос каждый раз, я могу создать, так называемый, SQL-просмотр или же, говорят, представление. Грубо говоря, это сохраненный запрос к базе данных.

Называют его VIEW (вью или вьюха). Это запрос, который вы сохранили в БД. Он такой же объект как таблица.

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

ПРЕДСТАВЛЕНИЕ (VIEW) - объект данных, который не содержит никаких данных его владельца. Это - тип таблицы, чье содержание выбирается из других таблиц с помощью выполнения запроса.

 

Как создается вьюха:

CREATE VIEW MyView AS

SELECT sum(

CASE

WHEN ВидОперации=’Приход’ THEN Сумма

ELSE -1*Сумма

END)

FROM R1;

 

Если после этой команды выполнить команду SHOW TABLES, то вы увидите и вашу новую таблицу MyView.

Но на самом деле это конечно не таблица, это фиктивная таблица, которая ведет себя как таблица. Можно получить данные:

SELECT * FROM MyView;

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

Применений очень много.

Пример применения. Например, есть у нас таблица ЦеныТоваров(Код, Товар, Цена). Но здесь у нас цена – закупочная. А продаем мы товар с некоей наценкой. Конечно, мы можем добавить еще один атрибут ТипЦен и посадить человечка, который будет заниматься ценообразованием. Но можем, просто указать процент наценки и тогда нам потребуется вносить данные только о закупочной цене, а данные о продажной цене будем получать запросом с учетом наценки. Если измениться закупочная цена или добавиться новый товар в исходную таблицу, ваш сайт или программа, тут же увидят этот товар по продажной цене.

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

Как в данном случае работает сервер. Сначала, естественно будет выполнен запрос вьюхи, а затем основной запрос, который обращен к вьюхе как к таблице. Кажется, что это двойная работа и двойное время. Но, тем не менее, запрос к представлениям выполняется быстрее. Почему? Потому что сервер любит все КЭШировать. Результат запроса сохраняется в КЭШе, анализируется сервером статистика запросов и результаты выстраиваются в КЭШе и поэтому работа с представлениями ускоряет процесс получения данных.

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

ХРАНИМАЯ ПРОЦЕДУРА – это такой же объект в БД, как и таблица. Мы с вами говорили о том, что БД это набор таблиц, но это что касается логического представления БД. Что же касается представления БД на сервере, то она может состоять не только из таблиц. БД с точки зрения СУБД представляет собой набор объектов разного типа данных. Это может быть таблица, представление(вьюха). Вот хранимая процедура это еще один объект БД.

Хранимая процедура – это набор действий, который может выполняться по вашей команде. Эти действия не только запросы на получение данных, это может быть несколько запросов, это могут быть др. команды, например, почистить мусор, пересчитай налоги и т.п. Во вьюхе будет 1 запрос всегда. Логика может быть очень и очень сложной.

Хранимые процедуры работают гораздо быстрее всех запросов. Почему? Когда вы отправляете обычный запрос, что происходит на сервере? Сначала ваш запрос синтаксически разбирается, затем строится план выполнения запроса, грубо говоря, запрос компилируется. И только потом этот запрос выполняется, затем происходит пересылка результата (долгий этап, как правило).

Предположим надо вставить 100 записей, 100 Insert. Что произойдет? 100 раз произойдет разбор запроса, проверится синтаксис, построится план и т.д.

Большинство хранимых процедур проверяют запросы и строят план один раз. Поэтому процедуры работают быстрее.

Хранимая процедура безопаснее. Очень много уязвимостей, особенно на веб-сайтах, связано с, так называемой, SQL-инъекцией. Когда просто в запрос что-то подставляется. Например, у нас имеется проверка логина и пароля пользователя. У нас есть БД пользователей, они вносят на сайте логин и пароль и мы их проверяем, сравнивая со значениями в нашей БД. Проверяют обычно вот таким запросом:

SELECT id

FROM USER

WHERE login = ‘ ‘ AND pwd = ‘ ‘;

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

Что может сделать хитрый пользователь? Он может в поле логин ввести вот такую штуку:

Admin ‘;--

Что получим в запросе?

SELECT id

FROM USER

WHERE login = ‘Admin‘;-- AND pwd = ‘ ‘;

Проверка пароля будет просто отключена.

Или введет пользователя admin, в поле пароль введет данные ‘ OR 1=1 --

SELECT id

FROM USER

WHERE login = ‘Admin‘;-- AND pwd = ‘ ‘ OR 1=1;

Это самые примитивные атаки. В данном случае ошибка программиста была в том, что он программно пытался изменить текст запроса, подставляя данные. Если от этого уйти – проблема решается. Хранимые процедуры это позволяют сделать. Они вызываются по-другому.

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

Это были плюсы хранимых процедур.

К минусам относится их трудность написания.

Рассмотрим, как создаются хранимые процедуры.

CREATE PROCEDURE ИмяПроцедуры

BEGIN

Запросы на языке SQL

END;

Здесь есть небольшой нюанс. Запрос заканчивается точкой с запятой, а если там несколько запросов? Да и сама процедура еще не закончена. Что же делать? Для этого мы должны указать, какой конкретно символ будет являться концом команды. Делаем это так:

delimiter //

CREATE PROCEDURE ИмяПроцедуры ()

BEGIN

Запросы на языке SQL

END;

//

Теперь символ // является концом команды. Он устанавливается командой delimiter //

После окончания процедуры возвращаем точку с запятой: delimiter;

Пример процедуры:

delimiter |

DROP PROCEDURE IF EXISTS SP_simple |

CREATE PROCEDURE SP_simple()

BEGIN

SELECT * FROM R1;

SELECT * FROM R2;

END;

|

delimiter;

 

Не все клиенты могут работать с процедурами, возвращающими два результата. Поэтому так лучше не делать.

Процедура создана. Что мы можем теперь с ней делать?

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

CALL ИмяПроцедуры;

CALL PROCEDURE SP_simple();

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

delimiter |

DROP PROCEDURE IF EXISTS SP_simple |

CREATE PROCEDURE SP_simple(IN P1 int)

BEGIN

SELECT * FROM R1 WHERE Pole1=P1;

END;

|

delimiter;

Здесь IN – направление параметра, показывает, что параметр будет входной, т.е. участвовать в фильтрации, например.

Вызов процедуры:

CALL SP_simple(52);

Здесь при вызове уже подставляем фактические параметры.

Есть еще направление OUT – направление параметра, говорит о том, что параметр будет выходным, т.е. возвращен в качестве результата.

delimiter |

DROP PROCEDURE IF EXISTS SP_simple |

CREATE PROCEDURE SP_simple(OUT P1 int)

BEGIN

SELECT count(*) INTO P1 FROM R1;

END;

|

delimiter;

Вызов процедуры:

CALL PROCEDURE SP_simple(@count);

SELECT @count;

 

Переменные в MYSQL.

Существует два вида переменных: глобальные и локальные. Глобальные переменные действуют везде, локальные только в пределах процедуры. Глобальные переменные создаются в момент присвоения им значения. Это значение хранится в переменной до завершения соединения с сервером. Разумеется, они доступны внутри хранимых процедур. Символ @ ставится перед глобальной переменной при объявлении.

SET @Var = 123;

SELECT @Var;

Локальные переменные объявляются внутри хранимой процедуры, видны только там, создаются в момент вызова процедуры и уничтожаются в момент завершения работы процедуры. При объявлении не надо указывать символ @, но надо декларировать такие переменные:

CREATE PROCTDURE PROC()

BEGIN

DECLARE Pvar INT;

SET Pvar = 2;

SELECT Pvar

END;

Если попытаться получить значение этой переменной вне процедуры, получим ошибку:

SELECT Pvar;

ERROR 1054 (42S22): Unknown column 'pvar' in 'field list'

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

Внутри процедуры можно создать локальную переменную:

CREATE PROCEDURE SP_simple()

BEGIN

DECLARE name VARCHAR(5);

DECLARE newname VARCHAR(5)= ‘BOB‘;

SET @x =1;

END;

Таким образом, внутри самой процедуры локальную переменную необходимо сначала объявить DECLARE name VARCHAR(5); А потом установить ей значение с помощью

SET name = ‘BOB‘;

Пример объявления переменных:

delimiter |

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 ='I am a string';

SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5;

 

END;

|

delimiter;