Расширения возможностей реляционных баз данных в СУБД SQL Server 2000

Лабораторная работа №6

Тема 6: Расширения возможностей реляционных

баз данных в СУБД SQL Server 2000. Использование функций  пользователя, триггеров типа INSTEAD OF

Цель работы: Изучить приемы написания и особенности использования функций пользователя и триггеров средствами T-SQL.

6.1 Пользовательские функции

В СУБД SQL Server 2000 появилась поддержка пользовательских функций (User Defined Functions или UDF). Transact-SQL расширен операторами CREATE FUNCTION, ALTER FUNCTION и DROP FUNCTION. Пользовательские функции сходны с хранимыми процедурами. Однако, в отличие от хранимых процедур, они могут использоваться в запросах так же, как и системные функции. Как и хранимые процедуры, они могут иметь один или несколько параметров, но возвращают скалярные типы данных, например, int, decimal, varchar, sql_variant – или тип данных table. В SQL Server 2000 появилось три новых типа данных – bigint, sql_variant и table.

Тип данных bigint – это "большой" integer, использующий для представления числа 8 байт. Для сравнения, в SQL Server 7.0 и более ранних версиях максимальным являлся тип данных int, использующий 4 байта. Если тип данных int используется для столбцов-счетчиков, то при работе с большими базами данных вполне вероятна ситуация, когда значения счетчика приблизятся к предельному значению этого типа данных. Применение же типа данных bigint отодвигает эту проблему. Тип данных sql_variant способен хранить любой стандартный тип данных, кроме text, ntext, image, timestamp и себя самого. Этот тип данных очень напоминает тип данных variant в VB, позволяя хранить значения других типов данных. Каждый экземпляр sql_variant содержит значение данных и метаданные – тип данных, максимальный размер и т.д. Применяя этот тип данных, следует помнить, что хранимые данные нужно преобразовывать к оригинальному типу с помощью функции CAST или CONVERT.

DECLARE @my_var sql_variant

SET @my_var = 3.142

DECLARE @my_chr VARCHAR(12), @my_dec DECIMAL(5,4)

SET @my_chr = CAST(@my_var AS VARCHAR(12))

SET @my_dec = CAST(@my_var AS DECIMAL(5,4))

Тип данных table – особый тип данных, позволяющий хранить результат выполнения SQL-запроса для дальнейшей обработки. Главная его задача – временное хранение набора строк, который может использоваться в SQL-запросах как таблица или возвращаемое значение пользовательской функции. Этот тип данных может использоваться вместо временных таблиц, хранящихся в базе данных tempdb. Определив локальную переменную этого типа, ее можно использовать так же, как временную таблицу.

Чтобы определить локальную таблицу, нужно использовать обычное выражение DECLARE:

DECLARE @local_variable TABLE <table_defintion>

Покажем на примере. Определим и заполним локальную переменную, содержащую данные о территориях из БД Northwind:

DECLARE @territory TABLE (territory_id INT, territory_desc NVARCHAR(50))

INSERT INTO @territory

SELECT TerritoryID, TerritoryDescription

FROM Territories

  SELECT * from @territory

Над переменными типа table можно выполнять операции SELECT, INSERT, UPDATE, DELETE, но операции ALTER, DROP, TRANCATE TABLE недопустимы. Основные отличия табличного типа от временных таблиц состоят в области видимости (локальная табличная переменная видна только внутри процедуры или функции, где была объявлена, но может возвращаться как out-параметр или в качестве возвращаемого значения функции) и в том, что для переменных типа table нельзя создавать неуникальные индексы.

Тип данных table крайне полезен при использовании в сочетании с пользовательскими функциями.

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

Пользовательская функция, возвращающая тип данных table может объявить внутреннюю табличную переменную и вернуть содержимое этой переменной. Такие функции называют rowset-функциями. Они могут использоваться в запросах вместо таблиц и представлений там, где Transact-SQL это допускает.

Если пользовательская функция возвращает скалярный тип данных, то нужно определить тип возврата и указать значение в выражении RETURN.

Существует тип пользовательских функций, возвращающих тип table, и называемых in-line функциями. Такие функции возвращают результат работы  единичного выражения select, без объявления табличных переменных и без определения таблицы в операторе RETURNS.

In-line функции подчиняются следующим правилам:

       Выражение RETURNS содержит только ключевое слово table.

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

Примечание: Возвращаемые функциями значения – только скаляры или таблицы. Например, пользовательская функция не может вернуть курсор. В теле функции не разрешен вызов процедур, использование операторов работы с транзакциями и операторов DDL, нельзя модифицировать данные в таблицах, не являющихся локальными переменными. Нельзя также создавать временные хранимые функции.

(?) Задание:

1)      Написать пользовательскую функцию, возвращающую целое число, представляющее количество студентов, сдавших экзамен в указанный день (для БД EDUCATION). Продемонстрировать применение функции для заданной даты.

DECLARE @count INT

SET @count = dbo.SpecifiedDate('12/12/97')

select @count

2)      Написать пользовательскую функцию, возвращающую таблицу (на основе БД EDUCATION), содержащую фамилию преподавателя – поле TFAM таблицы TEACHER и количество принятых им экзаменов у заданного студента. Продемонстрировать применение функции для заданного студента с номером 4012. Применение: SELECT * FROM CountOfExem(4012)

3)      Выполнить задание №2 с использованием in-line функции.

6.2 Триггеры INSTEAD OF

В SQL Server 2000 появился новый вид триггеров – триггер INSTEAD OF. Триггер INSTEAD OF выполняется вместо какого-либо действия (INSERT, UPDATE, DELETE). Такие триггеры могут быть определены и для представлений, что позволяет изменить логику обновления представлений (view)  или делать обновляемыми не обновляемые представления.  Вы можете изменять данные таблиц БД, реализуя логику любой сложности. Триггеры INSTEAD OF работают взамен (instead) команды пользователя, приведшей к запуску триггера. Таким образом, команда пользователя не выполняется, а вместо нее будет обработан набор команд, представляющий тело триггера INSTEAD OF. Вообще SQL Server 2000 разрешает только изменение данных представления, принадлежащих одной таблице. Если же необходимо модифицировать данные, принадлежащие двум и более таблицам, то вся операция изменения должна быть разбита на соответствующее число отдельных команд. Этот же подход должен применяться и в триггерах INSTEAD OF. Однако разбиение изменения на множество отдельных команд будет скрыто от пользователя.

(?) Задание:

4)      Средствами T-SQL создать таблицу BaseTable1 (сведения о деталях)  со следующими полями:

PrimaryKey – поле-счетчик, автоматически увеличивающее свое значение на 1, не редактируется пользователем, первичный ключ  

Color – код цвета детали, текстовая информация переменной длины (до 10 символов), обязательное поле  для заполнения

Material – код материала детали, текстовая информация переменной длины (до 10 символов),  обязательное для заполнения поле

ComputedCol – комплексная информация о детали, (ComputedCol AS (Color + Material)), не редактируется пользователем

UnitPrice – цена за штуку

Quantity – количество на складе

5)      Создать таблицу BaseTable2 (сведения о краске детали)  со следующими полями:

Color – код цвета детали (первичный ключ)  

ColorBuy – наименование фирмы поставщика

Price – цена за единицу

BuyMin – минимальный (возможный) объем покупки

6)      Создать представление InsteadView, содержащее всю информацию из таблиц.

7)      Создать триггер INSTEAD OF для представления, который распространяет операцию вставки записей и на базовые таблицы, игнорируя  поля PrimaryKey и ComputedCol.

8)      Создать триггер INSTEAD OF для операции обновления представления, который разрешает обновлять  только столбцы UnitPrice, Quantity и выполняет откат  обновления в других случаях.  Продемонстрировать работу триггера, выполнив соответствующие запросы через утилиту Query Analyzer.