12

Лабораторная работа №3.

Transact SQL

Transact SQL это расширение языка SQL предложенное фирмой Microsoft. Программы Transact SQL состоят из набора операторов. Оператор представляет собой инструкцию, которую должен выполнить Microsoft SQL Server. Кроме операторов SQL в T-SQL входят переменные, операторы управления, функции.

Переменные

Программный объект для временного хранения значений. Различают локальные переменны, которые создает программист (@name), и глобальные, которые создает SQL Server а программист может их только использовать. Имена глобальных переменных начинаются с @@.

Объявление локальных переменных:

DECLARE @имя тип.

Например: DECLARE @var1 int, @var2 varchar(20)

Основные типы переменных

• int - целый (2³¹⁾

• bit - 0/1 используется как логический тип,

• decimal(m,n) – число с заданной точностью – дробные (10³⁸) m – до запятой, n- после.

• real – с плавающей точкой обычной точности (10³⁸)

• float - с плавающей точкой высокой точности (10³⁰⁸)

• datetime – дата - время

• char(n) – строка фиксированной длинны n. МАХ 8000

• varchar(n) – строка переменной длинны не более n. МАХ 8000

• text строка переменной длинны . МАХ 2 10⁹

Присвоение значений переменным.

Явное:

SET имя = значение

SELECT имя = значение

SELECT имя As значение

Из другой таблицы

SELECT @var1= MAX(Зарплата) From Сотрудники.

Для создания процедур Transact SQL используется Query Analyzer.

На рис.1 представлена процедура varprim, которая показывает, как объявляются и используются переменные. Выполнить и сохранить эту процедуру можно также как запросы SQL.

Управление ходом выполнения

Условный оператор

IF условие

Блок операторов1

[ELSE

Блок операторов2]

Блок операторов: Несколько операторов заключенных в операторные скобки BEGIN END

Пример1. Необходимо при добавлении записей в таблицу Поставки проверять условие: Статус поставщика должен быть больше или равен статусу детали.

Практическое задание 1: Написать процедуру, которая при добавлении записей в таблицу Поставки проверяет условие: Статус деталей изготовленных из меди должен быть больше 2.

Оператор цикла

WHILE условие выполнения цикла

BEGIN

Оператор1

[BREAK]

[Continue]

Оператор2

END

BREAK – выход из цикла

CONTINUE – переход на начало цикла. Операторы следующие за CON.. будут пропущены.

Пример 2. Увеличивать количество Винтов в таблице Товары до тех пор пока общая сумма всех товаров не станет больше или равна 5500

Имя	Цена	Кол_во
Болт	100	10
Винт	50	20
Гайка	200	15

Рис.3

После выполнения этого запроса таблица Товары будет иметь следующий вид.

Имя	Цена	Кол_во
Болт	100	10
Винт	50	30
Гайка	200	15

Практическое задание 2: Написать процедуру, которая в таблице Товары, на каждой итерации увеличивает цену всех товаров на 10%, до тех пор пока общая сумма всех товаров не станет больше или равна 6000.

Хранимые процедуры.

Представляют собой пакет операторов T-SQL, сохраняемый на сервере. Хранимые процедуры не единственное средство выполнения операторов T-SQL, однако, они обладают рядом преимуществ:

• Хранимые процедуры являются объектом БД

• позволяют принимать и передавать данные в виде параметров

• хранятся в оптимизированной форме, что обеспечивает высокую скорость выполнения.

Создание хранимых процедур.

Для создания ХР используется оператор

CREATE PROCEDURE имя

[ список параметров]

AS

операторы процедуры

Параметры процедуры перечисляются после имени процедуры в виде списка: имя тип

Имя параметра начинается с символа @, например @VOL int.

Вызов процедуры осуществляется оператором

EXEC имя список параметров.

Пример 3. Рассмотрим процедуру, которая проверяет условие: Статус поставщика должен быть больше или равен статуса детали. Если условие выполняется, в таблицу Поставки добавляется новая запись, если нет, то выводится СООБЩЕНИЕ.

Если таблица Поставки имела вид:

то после вызова процедуры оператором

в таблицу будет добавлена новая запись.

Вызов процедуры с параметрами 3,4, '03.03.2010',95,100 приведет к выдаче сообщения об ошибке

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

EXEC ADDPD @PN =4, @DN =3, @VOL =100

Вызов процедуры из тела другой процедуры.

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

В противном случае проверить статус и если он удовлетворяет условиям, то добавить в таблицу Поставки новую запись. Это правило реализуется процедурой APD, которая для добавления новой записи вызывает процедуру ADDPD.

После вызова этой процедуры оператором

таблица Поставки будет иметь следующий вид:

Практическое задание №3

Создать процедуру, которая изменяет значение статуса поставщика с заданным номером. Параметры процедуры: номер поставщика и величина изменения статуса.

Хранимые функции.

Можно создавать собственные функции. Функции могут получать параметры и возвращать скалярное значение или таблицу. Различают:

Скалярные функции – возвращает единственное значение.

Функции возвращающие таблицу – возвращает результат единичного оператора SELECT.

Создание скалярной функции.

CREATE FUNCTION имя ( список: имя параметра тип)

RETURNS тип возвращаемого значения

BEGIN

тело функции

RETURN возвращаемое значение

END

Рассмотрим скалярную функцию, которая возвращает сумму ставок определенной детали за заданную дату.

При вызове этой функции с параметрами 3,'16.01.2010', получим:

Практическое задание №4

Создать хранимую функцию, которая возвращает сумму всех поставок из заданного города. Параметр процедуры: город.

Функции возвращающие таблицу

CREATE FUNCTION имя ( список: имя параметра тип)

RETURNS TABLE

AS

RETURN ( SELECT…..)

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

Пример обращения к функции GetDt

Практическое задание 5: Написать хранимую функцию, которая будет возвращать таблицу Поставки, с вычисленным полем Вес. (Вес детали * Количество).

Триггеры

Специальный вид хранимых процедур, которые выполняются при наступлении определенного события в таблице (INSERT, UPDATE, DELETE).

Создание триггера.

CREATE TRIGGER имя тр. ON таблица

тип (AFTER, INSTEAD OF) событие (INSERT, UPDATE, DELETE)

AS

операторы SQL

тригер AFTER выполняется после наступления события

INSTEAD OF выполняется вместо события

Триггер может содержать оператор ROLLBACK TRANSACTION. Если выполняется этот оператор то все изменения в триггере и все изменения, которые вызвали срабатывание триггера, отменяются.

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

Рассмотрим триггер, который проверяет статус поставщика при добавлении записей в таблицу Поставки.

В результате выполнения запроса

В таблицу поставки будет добавлена новая запись.

А при выполнении запроса:

Появится сообщение об ошибке, так как статус детали 4 превышает статус поставщика 5.

Рассмотрим проблемы, которые могу возникнуть при удалении данных из таблицы Поставщики.

При выполнении оператора.

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

Использование триггера DLT решит эту прблему.

Практическое задание 6:Для таблицы поставки создать триггер, реализующий следующее ограничение: Поставщик, имеющий статус меньше 3, не имеет права поставлять детали, изготовленные из меди.

Пример. При заполнении таблицы Детали можно добавлять только те детали, названия которых содержаться в таблице Название.

CREATE TRIGGER INSD ON dbo.Детали

INSTEAD OF INSERT

AS

If EXISTS(SELECT * From inserted WHERE Название IN

(SELECT Название FROM Название))

INSERT INTO Детали SELECT * FROM inserted

ELSE

PRINT 'Операция отклонена, тк названия детали нет в справочнике'

Практическое задание 7. Модифицировать триггер INSD следующим образом: если названия добавляемой детали нет в таблице "Название", добавить его в таблицу "Название", а потом добавить данные о детали в таблицу "Детали".