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


 

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

66652. Олигополия. Отличительные черты олигополии 129.5 KB
  Такой тип олигополии в условиях которой действуют фирмы производящие однородный продукт называется чистой олигополией. Олигополия в условиях которой фирмы выпускают дифференцированную продукцию называется дифференцированной олигополией.
66654. Гай Смит и Soil Taxonomy 232.86 KB
  Гай Смит – одна из наиболее значимых фигур в истории не только американского, но и мирового почвоведения. Его ключевая работой является классификация Soil Taxonomy. Разработка почвенной таксономии часто вызывала очень сильную критику.
66655. Управление качеством в сфере здравоохранения 82.5 KB
  В настоящее время в РФ сложилась система обеспечения качества медицинской помощи основанная на процедурах вневедомственного и внутриведомственного контроля качества медицинской помощи. Внутриведомственный контроль предусматривает применение мер административного воздействия...
66656. Процедура и методы оценки качества услуг 71.5 KB
  По физико-статистическим признакам и процедурам методы контроля и оценки качества подразделяют на пять групп: инструментальный, органолептический, модельно-расчетный, экспертный и социологический. В силу неосязаемости и несохраняемости услуг наибольшее распространение получили методы, относящиеся к последним трем группам.
66657. Методы управления качеством в процессе обслуживания 44 KB
  Метод диаграммного проектирования или структурирования сервисного процесса был предложен американским консультантом Линн Шостак. В сфере услуг потребительские выгоды и само удовлетворение требований потребителей в основном происходят в момент двухстороннего взаимодействия покупателя и продавца в процессе оказания услуги.
66658. Управление качеством туристско-экскурсионных услуг 59 KB
  Предоставлением туристских услуг занимаются организации двух видов тур-операторы и тур-агентства. Они приобретают туристские продукты места в самолетах номера в гостиницах и другие услуги формируют пакеты услуг туризма и продают их прямо или косвенно...
66659. Потребительские свойства услуг 62 KB
  Показатели назначения услуг характеризуют набор свойств услуги определяющий качество выполнения функций для которых она предназначена. Эта группа показателей делится на четыре подгруппы: показатели применения; показатели совместимости; показатели предприятия...