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 с., ил.


 

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

21397. ИНДИВИДУАЛИЗАЦИЯ ГРАЖДАН 18.03 KB
  он не может от этого отказаться; для граждан не достигших 14 летнего возраста местом жительства признается место жительства их родителей усыновителей или опекунов; безвестное отсутствие и его последствия: отсутствие на месте жительства гражданина порождает неопределенность в ГО; в устранении неопределенности заинтересованы многие также как и сам гражданин; статья 42: суд может признать гражданина безвестно отсутствующим если в течение одного года в месте его постоянного места жительства нет сведений о месте его пребывания т. Невозможность...
21398. ЮРИДИЧЕСКИЕ ЛИЦА 28.28 KB
  Поэтому необходимо некое срво которое способно обеспечить реальность юрлица. юрлица в отличие от граждан не существуют реально. Эта теория впервые противопоставила юрлица и физлица как субъектов ГП. Недостатком является то что теория фикции объясняет почему создаются юрлица но не объясняет что такое юрлицо.
21399. ИНДИВИДУАЛИЗАЦИЯ ЮЛ 19.46 KB
  на практике он просто запутывает ГО когда регистрировали кооперативы то надо было его место нахождения тогда и появился юридический адрес они абонировали почтовый ящик Третий способ: не пользуются граждане Товарный знак знак обслуживания: в соответствии со статьёй 1477 товарный знак это обозначение служащее для индивидуализации товаров ЮЛ а знак обслуживания это обозначение служащее для индивидуализации выполнения работ и оказываемых услуг ЮЛ т. сочетание слов Раковая шейка Изобразительные шестеренка с буквами КЗ Объемный знак...
21400. СПОСОБЫ ОБРАЗОВАНИЯ ЮЛ 20 KB
  если все требования закона соблюдены отказать нельзя Если он случится можно обжаловать в суде УЧРЕДИТЕЛЬНЫЕ ДОКУМЕНТЫ ЮЛ Статья 52 виды: Устав: наиболее распространенный только на основе его действуют кооперативы и АО Учредительный договор: полное товарищество Учредительный договор и устав: ООО но теперь изменение что только на основе устава Общее положение о данном виде ЮЛ: закон о среднем специальном образовании эти учреждения действуют только на основании его Единственным документом д. на одной стороне активы а на другой пассивы; если...
21401. ОРГАНИЗАЦИОННО ПРАВОВЫЕ ФОРМЫ КО 17.16 KB
  Три формы: ООО Общество с дополнительной ответственностью АО Объединение в первую очередь имущества а вовторую лиц Не требует личного участия участника в деятельности общества Личность не имеет особого значения Участниками м. любые субъекты ГП кроме гос органов Участники общества не несут ответственности по долгам общества За рубежом общества тоже получили широкое распространение Система хоз тов и обв построена таким образом что чем...
21402. ПОЛНОЕ ТОВАРИЩЕСТВО 20.06 KB
  Полное товарищество это переходная форма от индивидуальной к коллективной предпринимательской деятельности т. участником только одного полного товарищества Фирменное наименование должно содержать либо имена наименования всех товарищей и слова полное товарищество либо имена наименования нескольких участников и слова компания и слова полное товарищество В качестве учредительного документа...
21403. ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ 22.83 KB
  Риск предпринимательской деятельности уменьшается до величины вклада который вносится пр создании ЮЛ; другое имущество риску не подвергается Возможность реально влиять на деятельность этого ЮЛ и его результаты Круг участников ограничен лицами которые как правило знают и доверяют друг другу До последнего времени: любой участник мог в любое время выйти без согласия и потребовать выдел своей доли Недостаток: нестабильная имущественная база т. весьма не удобно для кредиторов и остальных участников ГО что может в любое время участники...
21404. АКЦИОНЕРНОЕ ОБЩЕСТВО 23.22 KB
  Понятие: это КО созданная в результате объединения имущества нескольких лиц которые не несут ответственности по обязательствам этой организации и имеют в собственности акции удостоверяющие их обязательственное право требования по отношению к этой КО
21405. ПРОИЗВОДСТВЕННЫЙ КООПЕРАТИВ 18.52 KB
  Число членов не меньше 5 не допускается ситуация когда 1 член т. выделяется имущество из которого нельзя выделять паи Прибыль ПК распределяется в соответствии с трудовым участием членов Все основывается на трудовом участии Законодатель учитывает также и в размере паев: уставом м. учитывается и размер пая Организационное единство: обеспечивается уставом ППК; органы управления: общее собрание членов ПК исключительная компетенция: те вопросы которые...