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


 

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

43820. Физико-химические свойства никелевых покрытий, полученных из электролитов с наноуглеродными добавками 486.37 KB
  В работе было проведено исследование влияния добавок УДАТАН и АСМ на кинетику процесса никелирования на микротвердость и износостойкость покрытия. Анализ полученных результатов показал эффективность применения наноуглеродных добавок для повышения твердости и износостойкости никелевого покрытия.
43821. Проект реконструкции участка по улице Октябрьской Советского района г.Орла 3.78 MB
  Орёл расположен в центральной части Среднерусской возвышенности в пределах степной и лесостепной зон. Климат умеренно - континентальный. Средняя температура января - минус 8 - 10 градусов. Ноябрь, декабрь и январь являются самыми пасмурными месяцами.
43822. Оцінка і узагальнення інформації щодо використання методів прогнозування в процесі прийняття управлінських рішень 774.66 KB
  У давнину люди приймали рішення покладаючись на інтуїтивні почуття прогнози астрологів. Для здійснення функцій регулювання економічними процесами необхідно знати майбутнє багатьох економічних явищ з тим щоб прийняти правильне рішення у теперішньому. У ході оцінки альтернатив і прийнятті рішення менеджер повинен прогнозувати можливі результати в різних обставинах. Менеджерська діяльність базується на підготовці та здійсненні необхідного управлінського рішення на основі його економічного обґрунтування і подальшій послідовній систематизації...
43823. Расчет схемы лазерной охранной системы 135.77 KB
  Но в любом случае охранное устройство на какой бы элементной базе не было построено состоит из двух модулей: приемник и передатчик. Схемное построения этих модулей могут быть различны, я в качестве темы для разработки взял схему охранной сигнализации которая обеспечивает контроль окружающего пространства с помощью лазерного луча.
43824. Разработка автоматизированной системы по работе с клиентами для технического отдела ОАО «ЮТК», г. Ессентуки 832.39 KB
  Услуги связи по передаче данных за исключением услуг связи по передаче данных для целей передачи голосовой информации. Основная ценность и полезность данных документов заключается в том что они регламентируют ключевые работы и процессы. Ессентукский филиал ОАО ЮТК оказывает клиентам следующие виды услуг: услуги местной телефонной связи; дополнительные виды обслуживания ДВО; услуги внутризоновой телефонной связи; услуги телеграфной связи; услуги проводного вещания; услуги передачи данных телематические...
43825. Исследование состояния малого предпринимательства и финансового состояния ИП «Шарипова А.Г. 1.03 MB
  С переходом экономики Российской Федерации на рыночные отношения, а в связи с этим и началом развития малого бизнеса, в научную литературу и официальные документы прочно стали входить такие понятия как «малый бизнес», «малые предприятия», «предприниматели малого бизнеса», «предпринимательство и малый бизнес» и другие. К сожалению, многие эти понятия не унифицированы и поэтому в научной литературе они трактуются далеко не однозначно.
43826. Изучение государственного управления в сфере образования 67.7 KB
  Социально-экономическая характеристика образования в РФ 6 Состояние и развитие Российской системы образования Организация системы государственного управления в сфере образования Правовое регулирование образования в РФ
43827. Розрахунок електрозабезпечення приватного акціонерного товариства Комвольно суконна компанія Чексіл 551.57 KB
  Електромонтажні роботи в даний час ведуться на високому рівні інженерної підготовки, з максимальним перенесенням цих робіт із будівельних майданчиків в майстерні монтажно-заготовительних ділянок і на заводи електромонтажних організацій.
43828. Проектирование участка ТО – 2 легковых автомобилей 1.13 MB
  Предприятие неоднократно занимало призовые места в Областном и Республиканском соревнованиях и имело одни из самых высоких показателей по выработке на один таксомотор среди предприятий Министерства автомобильного транспорта России.