Оформление отчета. 6. Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента

6. Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.

7. Содержание должно включать Тему лабораторной работы и Ход ее выполнения.

8. Ход выполнения работы должен содержать все тексты хранимых процедур и функций реализованные в лабораторной работе.

9. Выводы

Контрольные вопросы

1. Понятие хранимой процедуры.

2. Типы хранимых процедур.

3. Описание входных и выходных параметров хранимой процедуре.

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

5. Табличные переменные.

6. Типы функций пользователя.

7. Возможности использования функций.

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

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

Использование языка манипулирования данными Transact-SQL для создания курсоров

Цель работы

Изучение возможностей программирования элементов поддержки БД под управлением курсоров.

Задание на лабораторную работу

4. Ознакомиться с методическими материалами по выполнению лабораторной работы.

5. Пример создания курсоров:

a. Курсор сервера.

b. Курсор клиента.

c. Смешанный курсор.

d. Курсор для последовательной обработки строк.

6. Выполнить задания для самостоятельной работы.

7. Оформить отчет о выполнении лабораторной работе.

Методические рекомендации по выполнению работы

(1)

Создание и модификация курсора

Для реализации курсора средствами MS SQL Server воспользуемся хранимой процедурой для создания непосредственно курсора и демонстрации передачи множества данных клиенту.

Выберите в дереве Проводника пункт Stored Procedures и, активизируя всплывающее меню, дайте команду New Stored Procedure. В появившемся окне Stored Procedure Properties в рабочую область выводится шаблон текста процедуры:

DECLARE имя курсора CURSOR

FOR

SELECT поля

FROM таблицы

WHERE условия

и т.д.

OPEN имя курсора

FETCH FROM имя курсора INTO переменные, в которых фиксируются значения возвращаемые конструкцией SELECT

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

BEGIN

/* Считывание очередной строки из курсора */

FETCH FROM имя курсора INTO переменные, в которых фиксируются значения возвращаемые конструкцией SELECT

END

CLOSE имя курсора

DEALLOCATE имя курсора

Параметры, определяющие поведение триггеров

- AFTER. Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Можно определить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER-триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами.

- INSTEAD OF. Триггер вызывается вместо выполнения команд. В отличие от AFTER-триггера INSTEAD OF – триггер может быть определен как для таблицы, так и для представления (VIEW). Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD OF-триггер.

(2)

(a)

Пример хранимой процедуры Proc_Stores реализующей работу курсора сервера.

Создать курсор, который должен возвращать технические характеристики и наименование изделия, которое поставляет заказчик (имя заказчика) в количестве более (указать количество) единиц, а также суммировать полученный результат (суммировать по полю “количество” таблица ПОСТАВКА)

/* Шаг 0. Объявление процедуры */

CREATE procedure proc_stores

/* Шаг 1. Объявление некоторых рабочих переменных */

declare @nOrderCount int,

@nStorCount int,

@sKol_vo int,

@sIzdelie char(50),

@sTeh_harakteristika char(50),

@sZakazchik char(50)

/* Шаг 2. Отключение результирующего счетчика. Отключаем необязательное

сообщение "O row affected messages", выдаваемое на экран клиента */

set NoCount ON

/* Шаг 3. Создает ресурсные структуры,

которые требуются для управления курсором. */

declare cur_stores cursor

For

SELECT Наименование, Тех_характеристики, Заказчик, Количество

FROM (ИЗДЕЛИЕ inner join ПОСТАВКА

on ИЗДЕЛИЕ.Код_модели = ПОСТАВКА.Код_модели)

inner join ЗАКАЗ on ПОСТАВКА.Номер_заказ = ЗАКАЗ.Номер_заказ

WHERE Количество > 10

/* Шаг 4. Открытие курсора.

Создается первоначальный результирующий

набор и готовятся данные для передачи. */

open cur_stores

/* Шаг 5. Первое считывание.

Считываются данные из курсора в переменные для обработки и оценки. */

fetch cur_stores

into @sIzdelie, @s Teh_harakteristika, @sZ akazchik, @s Kol_vo

/* Шаг 6. Инициализация счетчиков */

/* Здесь SELECT выполняет роль оператора присваивания */

select @nStorCount = 0

/* Шаг 7. Цикл считывания.

Обрабатываются данные, пока значение переменной @@fetch_status = 0

(это значит, что строка была считана из курсора). */

while @@fetch_status = 0

Begin

/* Шаг 8. Увеличение счетчика. */

select @nStorCount = @nStorCount + 1

/* Шаг 9. Определение общего количества изделий заказов превышающих заданный параметр (>10) */

select @nOrderCount = @nOrderCount + @sKol_vo

/* Шаг 10. Возвращение результата клиенту */

select ‘ Изделие’ = @s Izdelie,

‘ Техническая характеристика’ = @s Teh_harakteristika,

‘Заказчик’ = @sZ akazchik,

‘ Количество изделий’ = @s Kol_vo

/* Шаг 11. Продолжение считывания.

Если больше не найдено ни одной строки, значение переменной

@@fetch_status будет установлено не равным нулю и цикл завершается */

fetch cur_stores

into @ sIzdelie, @s Teh_harakteristika, @sZ akazchik, @s Kol_vo

End

/* Шаг 12. Закрытие и освобождения курсора.

Следует отметить, что для хранимой процедуры это необязательно,

так как курсор прекращает свое существование сразу же после

завершения хранимой процедуры. Однако лучше это проделывать. */

close cur_stores

deallocate cur_stores

/* Шаг 13. Оформление общего результата. */

select 'Количество изделий' = @nStorCount

select 'Суммарное количество изделий' = @nOrderCount

/* Шаг 14. Повторное включение результирующего счетчика */

set NoCount ON

/* Шаг 15. Завершение процедуры

0 показывает, что процедура выполнилась успешно, от –1 до –99 показывает причины неудачного завершения. */

return 0

Вызов процедуры Proc_Stores

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

Для процедуры Proc_Stores это можно выполнить с помощью фрагмента программы следующего вида

USE <имя БД> /* Связь с базой данных */

IF (SELECT object_id('proc_stores')) IS NOT NULL

BEGIN

PRINT 'Процедура существует, ожидайте результат...'

PRINT ''

EXECUTE proc_stores /* Выполнение курсора */

END

ELSE

PRINT 'Процедура в базе данных отсутствует'

Здесь функция select(object_id('proc_stores')) возвращает номер объекта в базе данных, если объект существует. Если объект 'proc_stores' в базе данных не существует, то select(object_id('proc_stores')) вернет значение NULL.

Для идентификации базы данных можно использовать либо окно DB на панели инструментов Query Analyzer, либо команду USE <имя базы> данных как показано в примере.

(b)

Пример (тот же) реализующий работу курсора клиента.

Для этого необходимо воспользоваться синтаксическими конструкциями шагов 1 – 14, и активизировать их в окне Query Analyzer. При этом вызов процедуры, не используется.

(c)

Пример (тот же) реализующий работу смешанного курсора.

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

Общий синтаксис смешанного курсора приведен ниже.

Хранимая процедура на сервере.

CREATE PROC my_proc @cur CURSOR VARYING OUTPUT

SET @cur = CURSOR FORWARD_ONLY STATIC

FOR

SELECT Название FROM Товар

OPEN @cur

Вызов процедуры в приложении.

DECLARE @my_cur CURSOR

DECLARE @n varchar(20)

EXEC my_proc @cur=@my_cur OUTPUT

FETCH NEXT FROM @my_cur INTO @n

SELECT @n

WHILE (@@FETCH_STATUS=0)

BEGIN

FETCH NEXT FROM @my_cur INTO @n

SELECT @n

END

CLOSE @my_cur

DEALLOCATE @my_cur

(d)

Пример реализующий работу курсора для последовательной обработки строк

Воспользовавшись формулировкой 1-го триггера лаб. раб. № 1 реализовать возможность построчной обработки данных удовлетворяющих требованиям при удалении одновременно нескольких строк.

create trigger [dbo].[del_kur] on [dbo].[Поставка]

INSTEAD OF delete

declare cur_1 cursor

for select * from deleted

declare @a int, @b int, @c int, @aa char(50)

OPEN cur_1

FETCH cur_1 INTO @a, @b, @c

WHILE @@fetch_status = 0

BEGIN

FETCH cur_1 INTO @a, @b, @c

select @aa=Наименование from Изделие where Код_модели=@a

IF @aa='Эл. двигатель' OR @aa='Телефон многоканальный'