Лабораторная работа3 (задания по Базам Данных к лабам по SQL)
Описание файла
Файл "Лабораторная работа3" внутри архива находится в папке "задания по Базам Данных к лабам по SQL". Документ из архива "задания по Базам Данных к лабам по SQL", который расположен в категории "". Всё это находится в предмете "основы эксплуатации эвм" из 11 семестр (3 семестр магистратуры), которые можно найти в файловом архиве МАИ. Не смотря на прямую связь этого архива с МАИ, его также можно найти и в других разделах. Архив можно найти в разделе "лабораторные работы", в предмете "основы эксплуатации эвм" в общих файлах.
Онлайн просмотр документа "Лабораторная работа3"
Текст из документа "Лабораторная работа3"
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 - целый (231)
-
bit - 0/1 используется как логический тип,
-
decimal(m,n) – число с заданной точностью – дробные (1038) m – до запятой, n- после.
-
real – с плавающей точкой обычной точности (1038)
-
float - с плавающей точкой высокой точности (10308)
-
datetime – дата - время
-
char(n) – строка фиксированной длинны n. МАХ 8000
-
varchar(n) – строка переменной длинны не более n. МАХ 8000
-
text строка переменной длинны . МАХ 2 109
Присвоение значений переменным.
Явное:
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 следующим образом: если названия добавляемой детали нет в таблице "Название", добавить его в таблицу "Название", а потом добавить данные о детали в таблицу "Детали".