Файл: Министерство науки.docx

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»)
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.

2. 
   ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
   

1. 
   Уточнить задание применительно к сформированным отно- шениям.
   
2. 
   Схематично описать необходимые запросы по заданию для своей БД.
   

3. 
   Утвердить у преподавателя эскиз лабораторной работы.
   
4. 
   Создать хранимые процедуры и функции с использованием описанных операторов в методичке (также использовать по одной из стандартных функций по выбору преподавателя).
   
5. 
   Подготовить и предоставить контрольные примеры с ис- пользованием хранимых процедур.
   

4. ЗАДАНИЯ

1. 
   Создать хранимую процедуру, в которой будет выполняться гибкий запрос на группировку по различным полям в зависимости от входного параметра
   
2. 
   Создать пользовательскую функцию, которая возвращает значение, вычисленное, на основе одного или нескольких атрибутов отношения по заданным критериям поиска.
   
3. 
   Создать хранимую процедуру, использующую созданную пользовательскую функцию.
   
4. 
   Создать хранимые процедуры для вставки, изменения и удаления данных, переданных через входные параметры.
   

5. 
   КОНТРОЛЬНЫЕ ВОПРОСЫ
   

1. 
   Что такое хранимая процедура, общий формат описания и вызов?
   
2. 
   Каков формат описания переменных в хранимых процеду-
   

---

---

РАБОТА №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 (ПОЛЕ)]] и тд…

Изменеие триггера

ALTER TRIGGER имяТриггера

On ………
Удаление триггера:

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

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

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

Работа триггера строится на предварительном контроле вво-

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

Пример1

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

Команда вставки записи в таблицу Сделка может быть, на- пример, такой:
INSERT INTO Сделка VALUES (3,1,-299,»01/08/2002»)
Создаваемый триггер должен отреагировать на ее выполнение следующим образом: необходимо отменить команду, если в таблице Склад величина остатка товара оказалась меньше продаваемого ко- личества товара с введенным кодом (в примере код товара=3). Во вставляемой записи количество товара указывается со знаком «+», если товар поставляется, и со знаком «-», если он продается.

---