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


 

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

75191. Техника сравнения языков. Сравнительно-исторический метод 43.5 KB
  Техника сравнения языков Этот метод очень важен. На его основе бала создана генеалогическая классификация языков. Объединение языков в семьи группы подгруппы основывается на нем. устанавливает родственные связи языков.
75192. Системы грамматических категорий, форм 21.64 KB
  Во многих языках существует 3 рода и они никак не связаны с животным миром. В тюркских языках тоже нет деления на роды. Основным падежом особенно в индоевропейских языках является именительный или абсолютный. В индоевроп языках процесс исчезновения падежной системы происходил с древнейших времен процесс синкретизма объединение нескольких значений в одно.
75195. Искусственные языки 27 KB
  Искусственные языки Система изучающая данную область называется Интерлингвистика Она изучает аналоги человеческого языка и языки используемые в узколокальных целях например машинные языки Эсперанто окциденталь или интерлингве идо волапюк это искусственные языки которые изначально создавались с целью преодоления языкового барьера который существует в человечестве легенда о языковом барьере появилась ещё в дохристианское время во время строительства Вавилонской башни. в Варшаве появился проект языка эсперанто составленный...
75196. Язык и культура 22.01 KB
  Язык и культура. Язык самым тесным образом связан с культурой. Вопросом связи языка и культуры начали заниматься в конце 18 начале 19 в. Вильгельм фон Гумбольдт Гумбольдт утверждал что язык и материальная культура связаны.
75197. Просторечие и жаргон 30.5 KB
  Литературное просторечие это когда в речи образованного человека встречается фамильярная лексика: братан земляк пацан батя к пожилому мужчине. Следует различать салонные жаргоны социальной верхушки которые возникают из ложной моды как стилистический нарост на нормальном языке; практической ценности в них нет; особенно опасно их проникновение в литературу и практические жаргоны исходящие из профессиональной речи и преследующие цели языкового обособления данной группы и тайноречия для осуществления своего ремесла и засекречивания...
75198. Функциональная классификация языков 19.85 KB
  Существует несколько видов социолингвистической классификации языков: Существуют языки-гиганты. Эти языки являются рабочими языками ООН: английский немецкий франц. Дело в том что носители малых языков просто переходят на языки более крупные более распространенные так как малые языки неэффективны. Существует Красная книга языков России в которой перечисляются языки наиболее редкие.
75199. Общенациональный язык как система форм существования языка 43.5 KB
  Синтагматические связи и парадигматические отношения. Иерархические отношения в системе языка. Существует 3 типа системных отношений в языке: Синтагматические связи Парадигматические отношения Иерархические отношения Синтагматические связи связи и зависимости между языковыми элементами единицами любой сложности одновременно сосуществующими в линейном ряду тексте речи например между соседними звуками. На синтагматических отношениях строятся слова как совокупность морфем и слогов словосочетания и аналитические наименования...