СУБД MS SQL Server: разработка пользовательских функций, хранимых процедур, триггеров, курсоров, транзакций

Цель: изучение технологий создания основных объектов БД, разработки программных модулей в СУБД MS SQL Server.

Задания:

1. Разработка пользовательских функций

В разработанной средствами СУБД MS SQL Server по заданию лабораторной работы 6 БД "Платежи студентов" создать пользовательские функции – по одной функции каждого из типов:

- скалярные функции;

Создать пользовательскую скалярную функцию, которая будет возвращать:сумму платежей для конкретного города (параметр – код города);

Для создания скалярной функции требуется перейти папку хранения скалярных функций ([Database]-Программирование-Функции-Скалярные функции), затем в контекстном меню выбрать пункт «Создать скалярную функцию». Для созданной функции сгенерируется шаблон (Рисунок 1).

Рисунок 1 – Шаблон скалярной функции

Далее на основе шаблона (либо с нуля) создаем требуемую скалярную функцию:

Функция получает входные данные (КодГорода) и выводит сумму платежей студентов по заданному городу (Рисунок 2).

Рисунок 2 – Результат работы скалярной функции

- табличные функции (Inline, Multistatement).

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

Данная функция получает в качестве параметра строку (Факультет) и возвращает  сумму оплат студентов заданного факультета (Рисунок 3).

Рисунок 3 – Пример работы Inline функции

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

Рисунок 4 – Пример работы Multistatement функции

2. Разработка хранимых процедур

В разработанной средствами СУБД MS SQL Server по заданию лабораторной работы 6 БД "Платежи студентов" создать хранимые процедуры.

Разработать хранимую процедуру для определения даты рождения студента  (выходной параметр), входные параметры: ФИО, группа.

Хранимые процедуры создаются аналогично функциям и также имеют шаблон (Рисунок 5).

Рисунок 5 – Данные о студенте до использования хранимой процедуры

3. Разработка триггеров

В разработанной средствами СУБД MS SQL Server по заданию лабораторной работы 6 БД "Платежи студентов" создать триггеры.

1. DML-триггер типа AFTER

Отправка клиенту сообщения при изменении данных в таблице Улицы.

Рисунок 6 – программная часть

Функция создает триггер для таблицы Улицы, который будет срабатывать после обновления и выводить сообщение о том, что данные изменены (функция RAISERROR). Пример работы триггера представлены на (Рисунках 6-7).

Проверка тригера на работоспособность по заданию

Рисунок 7 – Сообщение пользователю

2. DML-триггер типа INSTEAD OF

Отменить изменение данных в таблице Студенты и вывести сообщение об ошибке.

Пример работы триггера представлен на рисунке 8.

Рисунок 9 – Пример работы триггера

3. DDL-триггер - Создать DDL триггер, протоколирующий работу пользователей с таблицей Города.

Для того чтобы проверить правильность работы ddl тригера создадим дополнительно таблицу(ddl log). Куда будут заноситься данные о работе над нужной нам таблицей. Evendata возвращает xml документ содержащий информацию о пользователи.

Пример работы представлен на рисунке 10.

Рисунок 11 – Пример работы DDL-триггера

4. Создание курсоров

В разработанной средствами СУБД MS SQL Server по заданию лабораторной работы 6 БД "Платежи студентов" создать курсоры: статический, динамический, для выборки данных, для модификации данных. Самостоятельно задать исходные данные, назначение и выполняемые операции создаваемых курсоров; протестировать их работу.

Статический курсор

Пример работы представлен на рисунке 12.

Динамический курсор

Используется для вывода фамилии , имя и отчевтсва первого студента из списка. Для начала описываем курсор инструкцией DECLARE, задаём атрибут LOCAL. Описываем переменные, которые будут получать данные. Открываем курсор. Забираем значения из курсора и помещаем их в объявленные переменные. Выводим сформированную строку.

Рисунок 13 – Пример работы динамического курсора

Курсор для выборки данных

Используется для вывода ФИО, заданного студента из списка. Для начала описываем курсор инструкцией DECLARE, задаём атрибут LOCAL. Выбираем из таблицы «Студенты» значения полей ФИО. Открываем курсор. Забираем значения из курсора. Выводим сформированную строку.

Рисунок 15 – Пример работы курсора для выборки

Курсор для изменения данных

Используется для изменения Фамилии . Для начала описываем курсор инструкцией DECLARE, задаём атрибут LOCAL. Открываем курсор. Забираем значения из курсора и обновляем полученное значение. Выводим новую строку.

Рисунок 16 – Пример работы курсора для изменения

Выводы: В ходе лабораторной работы были изучение технологии создания основных объектов БД, разработки программных модулей в СУБД MS SQL Server.