Лабораторная работа № 5. Создание и использование представлений и хранимых процедур

 

Цель работы: использование представлений для защиты данных; освоение хранимых процедур.

 

Использование представления для защиты данных

 

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

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

Представление создается с помощью команды CREATE VIEW:

а) создание представления для данных одной таблицы:

VIEW имя_представления* | столбец1 [, столбец2]имя_таблицы

[WHERE выражение1 [, выражение2]]

 

б) создание представления для данных нескольких таблиц:

VIEW имя_представления* | столбец1 [, столбец2]имя_таблицы1, имя_таблицы2 [, имя_таблицы3]

[WHERE выражение1 [, выражение2]]

 

в) создание представления на основе другого представления:

VIEW представление2* представление1

 

Использование хранимых процедур

 

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

Некоторые из преимуществ использования сохраненных процедур:- операторы процедуры уже сохранены в базе данных;- операторы процедуры уже проверены и находятся в готовом для использования виде;- при использовании процедур результат получается быстрее;- возможность сохранения процедур позволяет использовать модульное программирование;

сохраненные процедуры могут вызывать другие процедуры;- сохраненные процедуры могут вызываться другими программами.

В SQL Server процедуры создаются с помощью оператора следующего вида:

 

CREATE PROCEDURE имя_процедуры

[[(] @имя_параметра

 

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ])

[=DEFAULT][OUTPUT]]

[, @ИМЯ_ПАРАМЕТРА

ТИП_ДАННЫХ [(ДЛИНА) | (ТОЧНОСТЬ) [, МАСШТАБ])

[=DEFAULT][OUTPUT]]

[WITH RECOMPILE]операторы SQL

 

Сохраненные процедуры используются следующим образом:

[@ =] имя_процедуры

[[@ имя_параметра =] значение |

[@ имя_параметра =] @ переменная [OUTPUT]]

[WITH RECOMPILE]

база данные пользователь интерфейс

Задание на лабораторную работу

 

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

представление, содержащее только описание товара и его цену;

представление, содержащее название клиента и его адрес;

представление, содержащее номер счета, название клиента, описание товара и дату счета.

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

для добавления новых строк в таблицу PRODUCTS_TBL;

для поиска товаров, цена которых превышает определенную сумму;

для увеличения цены всех товаров на 12%;

для поиска счетов клиентов с максимальной общей суммой;

для формирования списка клиентов с их телефонами;

для формирования списка товаров с их ценами;

проверьте работу всех созданных процедур.

 

Требования к отчету

 

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

 

5.5 Контрольные вопросы

 

Что такое представление?

Что случится, если таблица, на основе которой строится представление, будет удалена?

Как представление можно использовать для защиты данных?

Как можно удалить представление?

Что такое хранимая процедура?

В чем преимущества использования процедур?

Может ли сохраненная процедура вызывать другую сохраненную процедуру?

Может ли сохраненная процедура выполнять вычисления?

Может ли сохраненная процедура выполнять сравнения вводимых пользователем значений с заранее установленными условиями?

Где хранится процедура?

 

Лабораторная работа № 6. Использование триггеров

 

Цель работы: освоение механизма триггеров, поддерживающего целостность данных в БД.

 

Определение триггера

 

Триггерами (Triggers) называется специальный класс хранимых процедур, автоматически запускаемых при добавлении, изменении и удалении данных из таблицы. Триггер срабатывает при модификации данных и запускает хранимую процедуру, выполняющую определенные действия. В зависимости от выполняемых пользователем действий, приводящих к запуску триггера, они делятся на три категории: триггеры изменения (UPDATE TRIGGER), триггеры вставки (INSERT TRIGGER) и триггеры удаления (DELETE TRIGGER). В одном триггере могут сочетаться все три типа. Очевидно, что невозможность срабатывания триггера по операции SELECT объясняется отсутствием модификации данных в этой операции.

Триггер присоединяется к определенной таблице и устанавливается на автоматический запуск в ответ на выполнение операции INSERT, DELETE или UPDATE. В отличие от хранимых процедур, триггер не может быть запущен вручную, иметь параметров и возвращать значений. Триггеры имеют множество применений, но чаще всего применяются для реализации делового регламента. Действие триггеров не ограничивается базами данных, в которых они устанавливаются. Их можно применять для модификации данных в таблицах других баз данных, даже на других серверах.

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

/*Заголовок и название триггера*/

TRIGGER Trigger_name

 

/*Имя таблицы, для которой написан триггер*/

table_name

 

/*На какие события реагирует триггер*/

 

FOR INSERT, UPDATE, DELETE

 

/* Ключевое слово */

 

 

/*Определение данных триггера*/

/*Начало тела триггера*/

@var_name type

 

/*var_name - имя переменной,@ - обязательный символ, type - тип данных переменной*/

/*Присвоение переменной значения столбца таблицы*/

 

SELECT @var_name=Table_name.Column_name

 

/*Из какой таблицы будет выбираться столбец; T,Q - псевдонимы; inserted - условное обозначение вставляемых данных*/

 

FROM table_name T, inserted Q

 

/*Сравниваются ключевые поля исходной таблицы и вставляемых данных*/

 

WHERE T.key=Q.key

 

/*Условие выполнения триггера*/

условие

 

/*Отмена транзакции*/

TRAN

 

/*Сообщение об ошибке*/

(‘Сообщение’)

 

/*Конец триггера*/

Пример создания триггера (создать триггер и проверить его работу рекомендуется после выполнения заданий 7.2.1 - 7.2.3).

Предположим необходимо отслеживать количество товара, которое мы можем продать. Это количество не должно превышать количество товара на складе; если менеджер по ошибке нарушает это условие, система должна выдать сообщение. Код триггера будет иметь вид:

 

CREATE TRIGGER CHECK_OSTATOKORDERS_TBL @ost int, @ost1 int

/*OSTATOK =

= PRODUCTS_TBL.QTY_VSEGO - ORDERS_TBL.QTY*/

@ost= ORDERS_TBL.QTY,

@ost1= PRODUCTS_TBL. OSTATOKORDERS_TBL, PRODUCTS_TBL PRODUCTS_TBL.PROD_ID= ORDERS_TBL. PROD_ID@ost>@ost1

rollback tran('недостаточно товара на складе',16,3)

 

Задание на лабораторную работу

 

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

Все задания выполняются командами Transact-SQL (не используйте графический интерфейс).

Внесите следующие изменения в структуру базы данных TradeCompany:

добавьте в таблицу PRODUCTS_TBL поле QTY_VSEGO;

добавьте в таблицу PRODUCTS_TBL поле OSTATOK;

добавьте в таблицу ORDERS_TBL поле SUMMA_К_OPLATE;

добавьте в таблицу CUSTOMER_TBL поле CITY;

добавьте в таблицу ORDERS_TBL поле OPLATA (логическое поле, в котором отмечается, проведена ли клиентом оплата за покупку).

Используя команду UPDATE, заполните следующие поля:

PRODUCTS_TBL.QTY_VSEGO, _TBL.CITY,

ORDERS_TBL.OPLATA

данными (в последнем поле для некоторых клиентов установите значение NO). В поле QTY_VSEGO таблицы PRODUCTS_TBL хранятся данные о количестве товара на складе, в поле QTY в таблице ORDERS_TBL - количество проданного товара, в поле OSTATOK - разница между значениями двух предыдущих полей.

Используя механизм сохраненных процедур, внесите следующие изменения в базу данных:

в поле PRODUCTS_TBL.OSTATOK - количество товара, оставшегося на складе (PRODUCTS_TBL. QTY_VSEGO - ORDERS_TBL.QTY);

в поле ORDERS_TBL.SUMMA_К_OPLATE - сумму, уплаченную за приобретенный товар; она рассчитывается как произведение цены товара на количество (таблица ORDERS_TBL).

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

Создайте триггеры, позволяющие контролировать следующие ситуации:

не обслуживать клиента из определенного города;

обслуживать клиентов только из определенного города;

не удалять запись о продажах, если товар не оплачен;

не закупать товары, цена которых превышает определенную сумму.

 

Требования к отчету

 

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

листинги текстов триггеров;

листинг результата работы созданных триггеров.

 

6.4 Контрольные вопросы

 

В чем преимущества использования процедур? 6.4.2 Когда выполняются триггеры - до или после выполнения команд INSERT, UPDATE и DELETE? 6.4.3 Можно ли изменить триггер?

Как можно ввести текст триггера в MS SQL 2005?

Как проверяется работа триггера?