17233

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

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

Информатика, кибернетика и программирование

Лабораторная работа № 3. Использование языка манипулирования данными TransactSQL для создания курсоров Цель работы Изучение возможностей программирования элементов поддержки БД под управлением курсоров. Задание на лабораторную работу Ознакомиться с метод

Русский

2013-06-30

89.5 KB

5 чел.

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

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

Цель работы

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

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

  1.  Ознакомиться с методическими материалами по выполнению лабораторной работы.
  2.  Пример создания курсоров:
    1.  
    2.  Курсор сервера.
    3.  Курсор клиента.Смешанный курсор.
    4.  Курсор для последовательной обработки строк.
  3.  Выполнить задания для самостоятельной работы.
  4.  Оформить отчет о выполнении лабораторной работе.

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

(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

as

/* Шаг 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, @sTeh_harakteristika, @sZakazchik, @sKol_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Изделие’ = @sIzdelie,

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

 ‘Заказчик’ = @sZakazchik,

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

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

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

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

 fetch cur_stores

into @sIzdelie, @sTeh_harakteristika, @sZakazchik, @sKol_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

AS

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

as

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='Телефон многоканальный'

    insert into Поставка values (@a, @b, @c)

 ELSE

    delete from Поставка where Код_модели=@a and

                                                                    Номер_заказа=@b and

                                                                    Количество=@c

 END

CLOSE cur_1

DEALLOCATE cur_1

Для удаления одновременно несколько строк можно сформулировать запрос типа

Delete from Поставка where Количество < 10

(3)

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

  1.  Разработать курсор клиента (текст в редакторе приложения SQL Server) для вывода Заказчика, Адреса и Суммарную_стоимостьSUM(Количество*Цена) ). (Для суммирования стоимости заказов можно использовать представление, разработанное в лаб. раб. № 1).
  2.  Разработать курсор сервера (в виде хранимой процедуры) для создания временной таблицы заказчиков из Харькова. В таблицу вносить строки по мере накопления количества поставок. Если суммарное количество поставок превысит 30 единиц заполнение таблицы остановить. Процедура должна вернуть количество строк и сумму поставок.

Структура временной таблицы: #VTable (Заказчик char(20), Дата_заказа datetime, Количество int).

  1.  Разработать смешанный курсор (как выходной параметр процедуры) для вывода Наименований изделий, Адрес заказчика и Тех_характеристики поставляемые в количестве более 10 единиц. На стороне клиента необходимо проверить выходные данные и отобразить только те данные, заказчики которых находятся в Харькове (то есть реализовать построчную обработку на стороне клиента).
  2.  Разработать курсор сервера (для триггера), воспользовавшись формулировкой 3-го триггера лаб. раб. № 1 и реализовать возможность построчной обработки данных удовлетворяющих требованиям при изменении одновременно нескольких строк.

(4)

Оформление отчета

  1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
  2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
  3.  Ход выполнения работы должен содержать все тексты курсоров реализованные в лабораторной работе.
  4.  Выводы.

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

  1.  Понятие курсора.
  2.  Использование курсоров при программировании приложений.
  3.  Отличие реализация курсора сервера, клиента и смешанного.
  4.  Типы курсора.
  5.  Последовательность действий при работе курсора.
  6.  Параметры создания курсора.
  7.  Параметры открытия курсора.
  8.  Параметры выборки данных из курсора.
  9.  Параметры закрытия курсора.
  10.  Параметры очистки курсора.

СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

  1.  Дейт К. Введение в системы баз данных.: Пер. с англ. – К: Диалектика, 1998. –    6-е издание – 848 с.
  2.  Шкарина Л. Язык SQL: учебный курс. – СПб.: Питер, 2001.
  3.  Тихомиров Ю. MS SQL Server в подлиннике. – СПб.: БХВ, 2000.
  4.  Дж. Грофф, П. Вайньерг SQL: Полное руководство. – 2-е изд., перераб. и доп. – К.: Издательская группа BHV, 2001 – 816 с., ил.


 

А также другие работы, которые могут Вас заинтересовать

52666. Формування мотиваційної готовності вчителя до інноваційної діяльності 92 KB
  А вчителі – перейняти головні ідеї інноваційного навчання усвідомити їх корисність для навчального процесу і стати активними учасниками процесу реформування освіти. Допомогти учасникам сформувати мотиваційну готовність до сприйняття нового. Інструкція учасникам: як нам потрібно поводитись у групі щоб нам було комфортно і ми досягли поставленої мети Спільно з групою виводимо основні правила: бути активними толерантними пунктуальними говорити по суті і по черзі можливість сказати ні. Інструкція учасникам: по черзі висловитися...