Файл: Методические указания для дисциплины Управление и автоматизация баз данных описывают содержание практических, лабораторных занятий и самостоятельной работы, перечень вопросов на защиту выполненных работ.pdf
ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 03.12.2023
Просмотров: 147
Скачиваний: 5
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
РАБОТА №5 ЛАБОРАТОРНАЯ. РАЗРАБОТКА
МЕХАНИЗМОВ СЕРВЕРА БАЗ ДАННЫХ.
ХРАМИНЫЕ ПРОЦЕДУРЫ
1. ЦЕЛЬ РАБОТЫ
Получить практических навык организации бизнес-логики на стороне сервера, на основе использования хранимых процедур и пользовательских функций.
В связи с этим задачами работы являются:
1. Изучение принципов работы и создание хранимых процедур и пользовательских функций в среде MS SQL Server.
2. Создание хранимых процедур и пользовательских функций в соответствии с заданием к лабораторной работе.
2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Для реализации бизнес логики на стороне сервера используют- ся, так называемые «механизмы сервера», включающие хранимые процедуры, пользовательские функции, триггеры, и другие объекты их состав может быть различен для СУБД различных производите- лей.
Наиболее распространенное средство реализации бизнес- логики – хранимые процедуры сервера.
Хранимые процедуры – это модули, хранящиеся непосредст- венно в базе данных в откомпилированном виде и которые могут запускаться пользователями или приложениями, работающими с базой данных. Хранимые процедуры обычно пишутся либо на спе- циальном процедурном расширении языка SQL (например, PL/SQL для ORACLE или Transact-SQL для MS SQL Server), или на некото- ром универсальном языке программирования, например, C++, с включением в код операторов SQL в соответствии со специальными правилами такого включения. Основное назначение хранимых про- цедур – реализация бизнес-процессов предметной области на сто- роне сервера.
Процедура подразумевает выполнение не только обычных за- просов SQL, но и более сложную обработку, связанную с вычисле- ниями, переходами, сравнениями ветвлениями. Для этого существу-
33 ет расширение SQL включающее описание переменных, операторы ветвления, циклы и пр.
Создание хранимой процедуры
CREATE PROCedure [владелец.]имя_процедуры
[@параметр1],
[@параметр2]
AS
Операторы_SQL где параметр –
@имя_параматра тип_данных [=default]
[OUTPUT];
[=default]
– значение по умолчанию присеваемое параметру, при отсутствии передаваемого значения;
[OUTPUT]
– ключевое слово, указывающее, что при изменении параметра в ходе выполнения процедуры новое значение возвраща- ется в переменную, используемую для вызова этой процедуры.
Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb.
Для создания временных процедур следует начинать ее имя с «#» или «##». Длина имени хранимой процедуры вместе с ## не может превышать 20 символов. Одна процедура может вызывать другую процедуру, уровень вложенности не может превышать 16, текущий уровень вложенности можно узнать из глобальной переменной
@@NESTLEVEL
Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи – в базе данных master. Таблицы, используемые в систем- ной процедуре, определяемой пользователем, также сначала оты- скиваются в текущей базе данных, и если это не удалось – в базе данных master.
Вызов хранимой процедуры осуществляется оператором
EXEC, например:
EXEC имяПроцедуры [параметр]
34
В виде параметра может выступать как переменная, так и кон- станта.
Пример:
Константа:
EXEC СводСотрудников «Инженер»
Использование переменной в качестве параметра:
DECLARE @Должность INT
SET @Должность = «Инженер»
EXEC СводСотрудников @Должность
Изменение хранимой процедуры
ALTER PROCedure [владелец.]имя_процедуры
[@параметр1],
[@параметр2]
AS
Операторы_SQL
Удаление хранимой процедуры
DROP PROCEDURE имя_процедуры
Использование переменных в хранимых процедурах
Для использования переменных их необходимо объявить с по- мощью ключевого слова DECLARE
DECLARE имяПерем типДаных где имяПерем – идентификатор переменной. Он должен быть без пробелов. В качестве первого символа должен использоваться сим- вол «@». В среде SQL server могут быть созданы глобальные пере- менные, видимые на уровне сервера, данные переменные начина- ются с двух символов «@@». Локальные переменные, начинаю- щиеся с одного символа «@» являются локальные и видимы только внутри одной транзакции.
35
Для присвоения значений переменным используется оператор
Set или Select.
Формат операторов SET и Select при присвоении значений.
SET имяПерем = значение или
SELECT имяПерем значение
Для обращения к значению переменной в запросе использует- ся та же конструкция SELECT
SELECT имяПерем as синоним.
Основные алгоритмические конструкции процедурного
расширения языка SQL
Конструкция ветвления в SQL
IF логическоеВыражение
{операторSQL | блокОпреаторовSQL}
[ ELSE
{ операторSQL | блокОпреаторовSQL }]
Конструкция цикла
WHILE <логическое_выражение>
{ операторSQL | блокОпреаторовSQL }
[ BREAK ]
{ операторSQL | блокОпреаторовSQL }
[ CONTINUE ] где блокОпреаторовSQL
– это операторные блоки, используемые для группировки операторов в скобки BEGIN END, аналогично паскалю, бейсику, или «{}» в С++
Для вывода результатов вычислений или значений перемен- ных можно использовать оператор print
Print выражение
T-SQL имеет набор функций, позволяющих выполнять набор математических, строковых, системных операций и операций пре- образования (См. приложение).
36
Преимущества использования процедур
Использовать хранимые процедуры целесообразнее, чем от- дельные операторы SQL по следующим причинам:
Операторы хранимой процедуры всегда находятся в базе данных.
Операторы хранимой процедуры уже проверены и находят- ся в готовом для использования виде.
Возможность использования процедур позволяет использо- вать модульное программирование.
Хранимые процедуры могут вызывать другие процедуры и функции.
Сохраненные процедуры могут вызываться любыми прило- жениями.
При использовании сохраненных процедур результат ответ от базы данных как правило получается быстрее.
Использование процедур принципиально упрощено в
СУБД.
Пользовательские функции UDF (user-defined function)
Основное отличие UDF от хранимых процедур заключается в том, что функция обязательно должна вернуть хотя бы какое-то значение. К сожалению, UDF имеют некоторые ограничения. Нель- зя, например, изменять данные в таблицах БД, выводить данные с помощью команд print и select. Внутри UDF нельзя использовать недетерминированные функции типа GETDATE(). В SQL Server имеется 3 типа пользовательских функций: Scalar, Multi-statement,
InLine.
Скалярная функция может возвращать значения любого ска- лярного типа данных, кроме данных типа text, image, table. Такая функция может объединять несколько команд языка T-SQL, нахо- дящихся в блоке BEGIN…END, например,
CREATE FUNCTION FunState
(@a varchar(20))
RETURNS varchar(20) as
BEGIN
37
IF @a IS NULL
SET @a = «Not Applicable»
RETURN @a
END
Обратиться к такой функции можно с помощью конструкции select, указав вместо поля таблицы значение функции с параметром:
SELECT pub_name, City, dbo.FunState(state) AS State
FROM dbo.publishers
Multi-Statement Table
Другой тип UDF – Multi-Statement Table-valued Function. Как следует из названия, этот тип функции возвращает тип данных table.
Тело такой функции может быть достаточно сложным и включать множество операторов, находящихся между ключевыми словами
BEGIN…END
. В данном простом примере в БД Pubs создается функ- ция, которая может возвращать фамилию и имя либо автора книги, либо служащего издательства.
CREATE FUNCTION FunMult
(@b nvarchar(8))
RETURNS @Fun_Auth table
([First Name] nvarchar(80) not null,
[Last Name]nvarchar(80) not null)
AS
BEGIN
IF @b = «author»
INSERT @Fun_Auth SELECT au_fname, au_lname
FROM authors
ELSE IF @b = «employee»
INSERT @Fun_Auth SELECT fname, lname
FROM employee
RETURN
END
В зависимости от входного параметра, указываемого в конст- рукции select при вызове функции, получаем разный результат, об- ращаясь к таблице author или к employee:
SELECT * FROM dbo.FunMult(«author»)
38
SELECT * FROM dbo.FunMult(«employee»)
Функция InLine тоже возвращает значение типа table, но отли- чается от Multi-Statement Table-valued тем, что может состоять только из одной команды select.
CREATE FUNCTION FunInLine
@State nvarchar(30))
RETURNS table
AS
RETURN ( SELECT pub_name, city
FROM Pubs.dbo.publishers
WHERE state = @State
)
Обращение к функции происходит в предложении from конст- рукции select:
SELECT * FROM FunInLine(«Texas»)
Удаление функции
DROP FUNCTION Имя функции
Особенностью функции InLine является то, что код функции при выполнении программы вставляется непосредственно в испол- няемый набор команд. Другими словами, происходит не вызов функции, а встраивание.
В среде SQL server хранимую процедуру или функцию можно создать в приложении Management Studio в разделе хранимых про- цедур с помощью контекстного меню. Или выполнением запроса соответствующего содержания из любого доступного приложения, например в Query Analyzer.
3. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Уточнить задание применительно к сформированным отно- шениям.
2. Схематично описать необходимые запросы по заданию для своей БД.
39 3. Утвердить у преподавателя эскиз лабораторной работы.
4. Создать хранимые процедуры и функции с использованием описанных операторов в методичке (также использовать по одной из стандартных функций по выбору преподавателя).
5. Подготовить и предоставить контрольные примеры с ис- пользованием хранимых процедур.
4. ЗАДАНИЯ
1. Создать хранимую процедуру, в которой будет выполняться гибкий запрос на группировку по различным полям в зависимости от входного параметра
2. Создать пользовательскую функцию, которая возвращает значение, вычисленное, на основе одного или нескольких атрибутов отношения по заданным критериям поиска.
3. Создать хранимую процедуру, использующую созданную пользовательскую функцию.
4. Создать хранимые процедуры для вставки, изменения и удаления данных, переданных через входные параметры.
5. КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Что такое хранимая процедура, общий формат описания и вызов?
2. Каков формат описания переменных в хранимых процеду- рах?
3. Тип параметров функций и процедур.
4. Описать типы пользовательских функций.
5. Формат описания конструкций цикла и ветвления.
6. Чем отличается хранимая процедура от пользовательской функции?
7. Что дает использование ХП при разработке БД?
40
РАБОТА №6 ЛАБОРАТОРНАЯ. РАЗРАБОТКА
МЕХАНИЗМОВ СЕРВЕРА БАЗ ДАННЫХ. ТРИГГЕРЫ
1. ЦЕЛЬ РАБОТЫ
Получить практический навык использования триггеров для организации бизнес-логики на стороне сервера.
В связи с этим задачами работы являются:
Изучить принципы работы и особенности построения триг- геров в среде MS SQL Server.
Создать триггеры, выполняющие действия, согласно инди- видуальному заданию.
2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Такими событиями являются запросы к базе данным, генерируемые операторами языка манипуляции данными –
DML (INSERT, DELETE или UPDATE). Триггер может выполнять- ся вместо или после операторов. С помощью триггеров можно от- менять транзакции, а также модифицировать данные одних таблиц и читать данные других даже из других баз данных. Таким образом, результатом работы триггеров является обеспечение целостности базы данных.
Чаще всего триггеры использовать очень удобно, однако, их использование приводит к значительному увеличению числа опера- ций ввода-вывода. Триггеры не следует использовать тогда, когда сохраненная процедура или программа может добиться тех же ре- зультатов с меньшими накладными расходами.
В Microsoft SQL Server синтаксис оператора для создания триггера выглядит следующим образом.
CREATE TRIGGER имяТриггера
ON имяТаблицыПредставления
{{FOR|AFTER|INSTEAD OF} {[INSERT], [DELETE], [UP-
DATE]}
AS операторыSQL
[IF UPDATE (ПОЛЕ) [{AND|OR} UPDATE (ПОЛЕ)]] и тд…
41
Изменеие триггера
ALTER TRIGGER имяТриггера
On ………
Удаление триггера:
DROP TRIGGER имяТриггера
Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Имя триггера должно быть уникальным в пределах базы дан- ных. Дополнительно можно указать имя владельца.
При использовании триггеров, действия, выполняемые с таб- лицами, предварительно выполняются с временными таблицами.
При вставке строк данные предварительно помещаются в таблицу inserted,
при удалении – удаляемые данные помещаются в таб- лицу deleted,
аналогично при модификации данных.
Работа триггера строится на предварительном контроле вво- димых, удаляемых или модифицируемых данных с помощью вре- менных таблиц. Данные таблицы имеют такую же структуру, как и таблицы, на которые создаются соответствующие триггеры.
Пример 1
Использование триггера для реализации ограничений на зна- чение. В добавляемой в таблицу
Сделка записи количество продан- ного товара должно быть не меньше, чем его остаток из таблицы
Склад.
Команда вставки записи в таблицу
Сделка может быть, на- пример, такой:
INSERT INTO Сделка VALUES (3,1,-299,»01/08/2002»)
Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице
Склад величина остатка товара оказалась меньше продаваемого ко- личества товара с введенным кодом (в примере код товара=3). Во вставляемой записи количество товара указывается со знаком «+», если товар поставляется, и со знаком «-», если он продается. Пред-
42 ставленный триггер настроен на обработку только одной добавляе- мой записи.
CREATE TRIGGER
Триггер_ins
ON Сделка FOR INSERT AS
IF @@ROWCOUNT=1
BEGIN
IF NOT EXISTS(SELECT * FROM inserted
WHERE
-inserted.количество<=ALL(SELECT
Склад.Остаток
FROM Склад,Сделка
WHERE Склад.КодТовара= Сделка.КодТовара))
BEGIN
ROLLBACK TRAN
PRINT «Отмена поставки: товара на складе нет» END
END
Пример 2
Создать триггер для обработки операции удаления записи из таблицы Сделка, например, такой команды:
DELETE FROM Сделка WHERE КодСделки=4
Для товара, код которого указан при удалении записи, необхо- димо откорректировать его остаток на складе. Триггер обрабатыва- ет только одну удаляемую запись.
CREATE TRIGGER Триггер_del
ON Сделка FOR DELETE AS
IF @@ROWCOUNT=1 -- удалена одна запись
BEGIN
DECLARE @y INT,@x INT
--определяется код и количество товара из удаленной
--из таблицы Склад записи
SELECT @y=КодТовара, @x=Количество
FROM deleted
--в таблице Склад корректируется количество товара
UPDATE Склад
SET Остаток=Остаток-@x
WHERE КодТовара=@y
END
МЕХАНИЗМОВ СЕРВЕРА БАЗ ДАННЫХ.
ХРАМИНЫЕ ПРОЦЕДУРЫ
1. ЦЕЛЬ РАБОТЫ
Получить практических навык организации бизнес-логики на стороне сервера, на основе использования хранимых процедур и пользовательских функций.
В связи с этим задачами работы являются:
1. Изучение принципов работы и создание хранимых процедур и пользовательских функций в среде MS SQL Server.
2. Создание хранимых процедур и пользовательских функций в соответствии с заданием к лабораторной работе.
2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Для реализации бизнес логики на стороне сервера используют- ся, так называемые «механизмы сервера», включающие хранимые процедуры, пользовательские функции, триггеры, и другие объекты их состав может быть различен для СУБД различных производите- лей.
Наиболее распространенное средство реализации бизнес- логики – хранимые процедуры сервера.
Хранимые процедуры – это модули, хранящиеся непосредст- венно в базе данных в откомпилированном виде и которые могут запускаться пользователями или приложениями, работающими с базой данных. Хранимые процедуры обычно пишутся либо на спе- циальном процедурном расширении языка SQL (например, PL/SQL для ORACLE или Transact-SQL для MS SQL Server), или на некото- ром универсальном языке программирования, например, C++, с включением в код операторов SQL в соответствии со специальными правилами такого включения. Основное назначение хранимых про- цедур – реализация бизнес-процессов предметной области на сто- роне сервера.
Процедура подразумевает выполнение не только обычных за- просов SQL, но и более сложную обработку, связанную с вычисле- ниями, переходами, сравнениями ветвлениями. Для этого существу-
33 ет расширение SQL включающее описание переменных, операторы ветвления, циклы и пр.
Создание хранимой процедуры
CREATE PROCedure [владелец.]имя_процедуры
[@параметр1],
[@параметр2]
AS
Операторы_SQL где параметр –
@имя_параматра тип_данных [=default]
[OUTPUT];
[=default]
– значение по умолчанию присеваемое параметру, при отсутствии передаваемого значения;
[OUTPUT]
– ключевое слово, указывающее, что при изменении параметра в ходе выполнения процедуры новое значение возвраща- ется в переменную, используемую для вызова этой процедуры.
Процедура может быть создана только в текущей базе данных, за исключением временных процедур, которые создаются в tempdb.
Для создания временных процедур следует начинать ее имя с «#» или «##». Длина имени хранимой процедуры вместе с ## не может превышать 20 символов. Одна процедура может вызывать другую процедуру, уровень вложенности не может превышать 16, текущий уровень вложенности можно узнать из глобальной переменной
@@NESTLEVEL
Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи – в базе данных master. Таблицы, используемые в систем- ной процедуре, определяемой пользователем, также сначала оты- скиваются в текущей базе данных, и если это не удалось – в базе данных master.
Вызов хранимой процедуры осуществляется оператором
EXEC, например:
EXEC имяПроцедуры [параметр]
34
В виде параметра может выступать как переменная, так и кон- станта.
Пример:
Константа:
EXEC СводСотрудников «Инженер»
Использование переменной в качестве параметра:
DECLARE @Должность INT
SET @Должность = «Инженер»
EXEC СводСотрудников @Должность
Изменение хранимой процедуры
ALTER PROCedure [владелец.]имя_процедуры
[@параметр1],
[@параметр2]
AS
Операторы_SQL
Удаление хранимой процедуры
DROP PROCEDURE имя_процедуры
Использование переменных в хранимых процедурах
Для использования переменных их необходимо объявить с по- мощью ключевого слова DECLARE
DECLARE имяПерем типДаных где имяПерем – идентификатор переменной. Он должен быть без пробелов. В качестве первого символа должен использоваться сим- вол «@». В среде SQL server могут быть созданы глобальные пере- менные, видимые на уровне сервера, данные переменные начина- ются с двух символов «@@». Локальные переменные, начинаю- щиеся с одного символа «@» являются локальные и видимы только внутри одной транзакции.
35
Для присвоения значений переменным используется оператор
Set или Select.
Формат операторов SET и Select при присвоении значений.
SET имяПерем = значение или
SELECT имяПерем значение
Для обращения к значению переменной в запросе использует- ся та же конструкция SELECT
SELECT имяПерем as синоним.
Основные алгоритмические конструкции процедурного
расширения языка SQL
Конструкция ветвления в SQL
IF логическоеВыражение
{операторSQL | блокОпреаторовSQL}
[ ELSE
{ операторSQL | блокОпреаторовSQL }]
Конструкция цикла
WHILE <логическое_выражение>
{ операторSQL | блокОпреаторовSQL }
[ BREAK ]
{ операторSQL | блокОпреаторовSQL }
[ CONTINUE ] где блокОпреаторовSQL
– это операторные блоки, используемые для группировки операторов в скобки BEGIN END, аналогично паскалю, бейсику, или «{}» в С++
Для вывода результатов вычислений или значений перемен- ных можно использовать оператор print
Print выражение
T-SQL имеет набор функций, позволяющих выполнять набор математических, строковых, системных операций и операций пре- образования (См. приложение).
36
Преимущества использования процедур
Использовать хранимые процедуры целесообразнее, чем от- дельные операторы SQL по следующим причинам:
Операторы хранимой процедуры всегда находятся в базе данных.
Операторы хранимой процедуры уже проверены и находят- ся в готовом для использования виде.
Возможность использования процедур позволяет использо- вать модульное программирование.
Хранимые процедуры могут вызывать другие процедуры и функции.
Сохраненные процедуры могут вызываться любыми прило- жениями.
При использовании сохраненных процедур результат ответ от базы данных как правило получается быстрее.
Использование процедур принципиально упрощено в
СУБД.
Пользовательские функции UDF (user-defined function)
Основное отличие UDF от хранимых процедур заключается в том, что функция обязательно должна вернуть хотя бы какое-то значение. К сожалению, UDF имеют некоторые ограничения. Нель- зя, например, изменять данные в таблицах БД, выводить данные с помощью команд print и select. Внутри UDF нельзя использовать недетерминированные функции типа GETDATE(). В SQL Server имеется 3 типа пользовательских функций: Scalar, Multi-statement,
InLine.
Скалярная функция может возвращать значения любого ска- лярного типа данных, кроме данных типа text, image, table. Такая функция может объединять несколько команд языка T-SQL, нахо- дящихся в блоке BEGIN…END, например,
CREATE FUNCTION FunState
(@a varchar(20))
RETURNS varchar(20) as
BEGIN
37
IF @a IS NULL
SET @a = «Not Applicable»
RETURN @a
END
Обратиться к такой функции можно с помощью конструкции select, указав вместо поля таблицы значение функции с параметром:
SELECT pub_name, City, dbo.FunState(state) AS State
FROM dbo.publishers
Multi-Statement Table
Другой тип UDF – Multi-Statement Table-valued Function. Как следует из названия, этот тип функции возвращает тип данных table.
Тело такой функции может быть достаточно сложным и включать множество операторов, находящихся между ключевыми словами
BEGIN…END
. В данном простом примере в БД Pubs создается функ- ция, которая может возвращать фамилию и имя либо автора книги, либо служащего издательства.
CREATE FUNCTION FunMult
(@b nvarchar(8))
RETURNS @Fun_Auth table
([First Name] nvarchar(80) not null,
[Last Name]nvarchar(80) not null)
AS
BEGIN
IF @b = «author»
INSERT @Fun_Auth SELECT au_fname, au_lname
FROM authors
ELSE IF @b = «employee»
INSERT @Fun_Auth SELECT fname, lname
FROM employee
RETURN
END
В зависимости от входного параметра, указываемого в конст- рукции select при вызове функции, получаем разный результат, об- ращаясь к таблице author или к employee:
SELECT * FROM dbo.FunMult(«author»)
38
SELECT * FROM dbo.FunMult(«employee»)
Функция InLine тоже возвращает значение типа table, но отли- чается от Multi-Statement Table-valued тем, что может состоять только из одной команды select.
CREATE FUNCTION FunInLine
@State nvarchar(30))
RETURNS table
AS
RETURN ( SELECT pub_name, city
FROM Pubs.dbo.publishers
WHERE state = @State
)
Обращение к функции происходит в предложении from конст- рукции select:
SELECT * FROM FunInLine(«Texas»)
Удаление функции
DROP FUNCTION Имя функции
Особенностью функции InLine является то, что код функции при выполнении программы вставляется непосредственно в испол- няемый набор команд. Другими словами, происходит не вызов функции, а встраивание.
В среде SQL server хранимую процедуру или функцию можно создать в приложении Management Studio в разделе хранимых про- цедур с помощью контекстного меню. Или выполнением запроса соответствующего содержания из любого доступного приложения, например в Query Analyzer.
3. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Уточнить задание применительно к сформированным отно- шениям.
2. Схематично описать необходимые запросы по заданию для своей БД.
39 3. Утвердить у преподавателя эскиз лабораторной работы.
4. Создать хранимые процедуры и функции с использованием описанных операторов в методичке (также использовать по одной из стандартных функций по выбору преподавателя).
5. Подготовить и предоставить контрольные примеры с ис- пользованием хранимых процедур.
4. ЗАДАНИЯ
1. Создать хранимую процедуру, в которой будет выполняться гибкий запрос на группировку по различным полям в зависимости от входного параметра
2. Создать пользовательскую функцию, которая возвращает значение, вычисленное, на основе одного или нескольких атрибутов отношения по заданным критериям поиска.
3. Создать хранимую процедуру, использующую созданную пользовательскую функцию.
4. Создать хранимые процедуры для вставки, изменения и удаления данных, переданных через входные параметры.
5. КОНТРОЛЬНЫЕ ВОПРОСЫ
1. Что такое хранимая процедура, общий формат описания и вызов?
2. Каков формат описания переменных в хранимых процеду- рах?
3. Тип параметров функций и процедур.
4. Описать типы пользовательских функций.
5. Формат описания конструкций цикла и ветвления.
6. Чем отличается хранимая процедура от пользовательской функции?
7. Что дает использование ХП при разработке БД?
40
РАБОТА №6 ЛАБОРАТОРНАЯ. РАЗРАБОТКА
МЕХАНИЗМОВ СЕРВЕРА БАЗ ДАННЫХ. ТРИГГЕРЫ
1. ЦЕЛЬ РАБОТЫ
Получить практический навык использования триггеров для организации бизнес-логики на стороне сервера.
В связи с этим задачами работы являются:
Изучить принципы работы и особенности построения триг- геров в среде MS SQL Server.
Создать триггеры, выполняющие действия, согласно инди- видуальному заданию.
2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Триггер – это откомпилированная процедура, используемая для выполнения действий, инициируемых происходящими в базе данных событиями. Такими событиями являются запросы к базе данным, генерируемые операторами языка манипуляции данными –
DML (INSERT, DELETE или UPDATE). Триггер может выполнять- ся вместо или после операторов. С помощью триггеров можно от- менять транзакции, а также модифицировать данные одних таблиц и читать данные других даже из других баз данных. Таким образом, результатом работы триггеров является обеспечение целостности базы данных.
Чаще всего триггеры использовать очень удобно, однако, их использование приводит к значительному увеличению числа опера- ций ввода-вывода. Триггеры не следует использовать тогда, когда сохраненная процедура или программа может добиться тех же ре- зультатов с меньшими накладными расходами.
В Microsoft SQL Server синтаксис оператора для создания триггера выглядит следующим образом.
CREATE TRIGGER имяТриггера
ON имяТаблицыПредставления
{{FOR|AFTER|INSTEAD OF} {[INSERT], [DELETE], [UP-
DATE]}
AS операторыSQL
[IF UPDATE (ПОЛЕ) [{AND|OR} UPDATE (ПОЛЕ)]] и тд…
41
Изменеие триггера
ALTER TRIGGER имяТриггера
On ………
Удаление триггера:
DROP TRIGGER имяТриггера
Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Имя триггера должно быть уникальным в пределах базы дан- ных. Дополнительно можно указать имя владельца.
При использовании триггеров, действия, выполняемые с таб- лицами, предварительно выполняются с временными таблицами.
При вставке строк данные предварительно помещаются в таблицу inserted,
при удалении – удаляемые данные помещаются в таб- лицу deleted,
аналогично при модификации данных.
Работа триггера строится на предварительном контроле вво- димых, удаляемых или модифицируемых данных с помощью вре- менных таблиц. Данные таблицы имеют такую же структуру, как и таблицы, на которые создаются соответствующие триггеры.
Пример 1
Использование триггера для реализации ограничений на зна- чение. В добавляемой в таблицу
Сделка записи количество продан- ного товара должно быть не меньше, чем его остаток из таблицы
Склад.
Команда вставки записи в таблицу
Сделка может быть, на- пример, такой:
INSERT INTO Сделка VALUES (3,1,-299,»01/08/2002»)
Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице
Склад величина остатка товара оказалась меньше продаваемого ко- личества товара с введенным кодом (в примере код товара=3). Во вставляемой записи количество товара указывается со знаком «+», если товар поставляется, и со знаком «-», если он продается. Пред-
42 ставленный триггер настроен на обработку только одной добавляе- мой записи.
CREATE TRIGGER
Триггер_ins
ON Сделка FOR INSERT AS
IF @@ROWCOUNT=1
BEGIN
IF NOT EXISTS(SELECT * FROM inserted
WHERE
-inserted.количество<=ALL(SELECT
Склад.Остаток
FROM Склад,Сделка
WHERE Склад.КодТовара= Сделка.КодТовара))
BEGIN
ROLLBACK TRAN
PRINT «Отмена поставки: товара на складе нет» END
END
Пример 2
Создать триггер для обработки операции удаления записи из таблицы Сделка, например, такой команды:
DELETE FROM Сделка WHERE КодСделки=4
Для товара, код которого указан при удалении записи, необхо- димо откорректировать его остаток на складе. Триггер обрабатыва- ет только одну удаляемую запись.
CREATE TRIGGER Триггер_del
ON Сделка FOR DELETE AS
IF @@ROWCOUNT=1 -- удалена одна запись
BEGIN
DECLARE @y INT,@x INT
--определяется код и количество товара из удаленной
--из таблицы Склад записи
SELECT @y=КодТовара, @x=Количество
FROM deleted
--в таблице Склад корректируется количество товара
UPDATE Склад
SET Остаток=Остаток-@x
WHERE КодТовара=@y
END