17242

Понятие курсора

Лекция

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

Лекция №6 Понятие курсора Устно. Запрос к реляционной базе данных обычно возвращает несколько записей данных но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими строками например выводит данные в форме электр

Русский

2013-06-30

76.5 KB

5 чел.

Лекция №6

Понятие курсора

Устно. Запрос к реляционной базе данных обычно возвращает несколько записей данных, но приложение за один раз обрабатывает лишь одну запись. Даже если оно имеет дело одновременно с несколькими строками (например, выводит данные в форме электронных таблиц), их количество по-прежнему ограничено. Кроме того, при модификации, удалении или добавлении данных рабочей единицей является строка. В этой ситуации на первый план выступает концепция курсора, и в таком контексте курсор – указатель на строку.

Курсор в SQL – это область в памяти БД, которая предназначена для хранения результата оператора выборки SQL. Указанная область в памяти поименована и доступна для прикладных программ.

В каждый момент времени прикладной программой может быть проверена одна строка курсора. Курсоры часто применяются в операторах, встроенных в написанные на языках процедурного типа прикладные программы.

В соответствии со стандартом SQL при работе с курсорами можно выделить следующие основные действия:

  •  создание или объявление курсора;
  •  открытие курсора, т.е. наполнение его данными, которые сохраняются в памяти;
  •  выборка из курсора и изменение с его помощью строк данных;
  •  закрытие курсора, после чего он становится недоступным для пользовательских программ;
  •  освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память.

Устно. В разных реализациях определение курсора может иметь некоторые отличия. Так, например, иногда разработчик должен явным образом освободить выделяемую для курсора память. После освобождения курсора ассоциированная с ним память также освобождается. При этом становится возможным повторное использование его имени. В других реализациях при закрытии курсора освобождение памяти происходит неявным образом. Сразу после восстановления она становится доступной для других операций: открытие другого курсора и т.д.

Недостатки:

  •  не позволяют проводить операции изменения над всем объемом данных;
  •  скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.

Таким образом, при манипулировании данными следует избегать работы с курсорами и использовать командами обработки данных: UPDATE, INSERT, DELETE.

Реализация курсоров в среде MS SQL Server

SQL Server поддерживает три вида курсоров:

  •  курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
  •  курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
  •  курсоры клиента реализуются на самом клиенте (курсоры API). Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.

Курсоры делятся на две категории: последовательные и прокручиваемые. Последовательные позволяют выбирать данные только в одном направлении – от начала к концу. Прокручиваемые же курсоры предоставляют большую свободу действий – допускается перемещение в обоих направлениях и переход к произвольной строке результирующего набора курсора.

SQL Server поддерживает курсоры статические, динамические, быстрые последовательные и управляемые набором ключей (ключевые курсоры).

В схеме со статическим курсором информация читается из базы данных один раз и хранится в виде моментального снимка (по состоянию на некоторый момент времени), поэтому изменения, внесенные в базу данных другим пользователем, не видны. На время открытия курсора сервер устанавливает блокировку на все строки, включенные в его полный результирующий набор. Статический курсор не изменяется после создания и всегда отображает тот набор данных, который существовал на момент его открытия.

Динамический курсор поддерживает данные в "живом" состоянии, но это требует затрат сетевых и программных ресурсов. При использовании динамических курсоров не создается полная копия исходных данных, а выполняется динамическая выборка из исходных таблиц только при обращении пользователя к тем или иным данным. На время выборки сервер блокирует строки, а все изменения, вносимые пользователем в полный результирующий набор курсора, будут видны в курсоре. Однако если другой пользователь внес изменения уже после выборки данных курсором, то они не отразятся в курсоре.

Курсор, управляемый набором ключей (ключевые курсоры), находится посередине между статическим и динамическим курсором. Записи идентифицируются на момент выборки, и тем самым отслеживаются изменения. Такой тип курсора полезен при реализации прокрутки назад – тогда добавления и удаления рядов не видны, пока информация не обновится, а процедура выбирает новую версию записи, если в нее были внесены изменения.

Быстрые последовательные курсоры не разрешают выполнять выборку данных в обратном направлении. Пользователь может выбирать строки только от начала к концу курсора. Быстрый последовательный курсор не хранит набор всех строк. Они считываются из базы данных, как только выбираются в курсоре, что позволяет динамически отражать все изменения, вносимые пользователями в базу данных с помощью команд INSERT, UPDATE, DELETE. В курсоре видно самое последнее состояние данных.

Управление курсором в среде MS SQL Server

Управление курсором реализуется путем выполнения следующих команд:

  •  DECLARE – создание или объявление курсора;
  •  OPENоткрытие курсора, т.е. наполнение его данными;
  •  FETCHвыборка из курсора и изменение строк данных с помощью курсора;
  •  CLOSEзакрытие курсора;
  •  DEALLOCATEосвобождение курсора, т.е. удаление курсора как объекта.

Объявление курсора

В среде MS SQL Server принят следующий синтаксис команды создания курсора:

 DECLARE имя_курсора CURSOR [LOCAL | GLOBAL]

 [FORWARD_ONLY | SCROLL]

 [STATIC | KEYSET | DYNAMIC | FAST_FORWAR]

 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

      [TYPE_WARNING]

 FOR SELECT_оператор

      [FOR UPDATE [OF имя_столбца]]

При использовании ключевого слова LOCAL будет создан локальный курсор, который виден только в пределах создавшего его пакета, триггера, хранимой процедуры или пользовательской функции. По завершении работы пакета, триггера, процедуры или функции курсор неявно уничтожается.

Чтобы передать содержимое курсора за пределы создавшей его конструкции, необходимо присвоить его параметру аргумент OUTPUT.

Если указано ключевое слово GLOBAL, создается глобальный курсор; он существует до закрытия текущего соединения.

При указании FORWARD_ONLY создается последовательный курсор; выборку данных можно осуществлять только в направлении от первой строки к последней.

При указании SCROLL создается прокручиваемый курсор; обращаться к данным можно в любом порядке и в любом направлении.

При указании STATIC создается статический курсор.

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор.

Если для курсора READ_ONLY указать аргумент FAST_FORWARD, то созданный курсор будет оптимизирован для быстрого доступа к данным. Этот аргумент не может быть использован совместно с аргументами FORWARD_ONLY и OPTIMISTIC.

В курсоре, созданном с указанием аргумента OPTIMISTIC, запрещается изменение и удаление строк, которые были изменены после открытия курсора.

При указании аргумента TYPE_WARNING сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT.

Открытие курсора

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

OPEN {{[GLOBAL]имя_курсора }

    |@имя_переменной_курсора}

После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в памяти.

Ключевое слово GLOBAL используется для избежания конфликтов: если курсор, объявленный с ключевым словом LOCAL, и курсор, объявленный с ключевым словом GLOBAL, имеют одинаковый идентификатор, ссылки на курсор будут по умолчанию отнесены к локальному курсору, если вы не использовали ключевое слово GLOBAL. Как и в других подобных случаях, лучше явно указывать ключевое слово, если вы открываете глобальный курсор.

Выборка данных из курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

FETCH [[NEXT | PRIOR | FIRST | LAST

 | ABSOLUTE {номер_строки

 | @переменная_номера_строки}

 | RELATIVE {номер_строки |

   @переменная_номера_строки}]

 FROM ]{{[GLOBAL ]имя_курсора }|

   @имя_переменной_курсора }

 [INTO @имя_переменной [,...n]]

При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой.

При указании NEXT возвращается строка, находящаяся сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. В дальнейшем она и становится текущей.

Аргумент ABSOLUTE {номер_строки | @переменная_номера_строки} возвращает строку по ее абсолютному порядковому номеру курсора. Номер строки можно задать с помощью константы или как имя переменной, в которой хранится номер строки. Переменная должна иметь целочисленный тип данных. Указываются как положительные, так и отрицательные значения. При указании положительного значения строка отсчитывается от начала набора, отрицательного – от конца. Выбранная строка становится текущей. Если указано нулевое значение, строка не возвращается.

Аргумент RELATIVE {кол_строки | @переменная_кол_строки} возвращает строку, находящуюся через указанное количество строк после текущей. Если указать отрицательное значение числа строк, то будет возвращена строка, находящаяся за указанное количество строк перед текущей. При указании нулевого значения возвратится текущая строка. Возвращенная строка становится текущей.

Чтобы открыть глобальный курсор, перед его именем требуется указать ключевое слово GLOBAL. Имя курсора также может быть указано с помощью переменной.

В конструкции INTO @имя_переменной [,...n] задается список переменных, в которых будут сохранены соответствующие значения столбцов возвращаемой строки. Порядок указания переменных должен соответствовать порядку столбцов в курсоре, а тип данных переменной – типу данных в столбце курсора. Если конструкция INTO не указана, то поведение команды FETCH будет напоминать поведение команды SELECT – данные выводятся на экран.

Изменение и удаление данных

Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:

UPDATE имя_таблицы SET {имя_столбца={

 DEFAULT | NULL | выражение}}[,...n]

 WHERE CURRENT OF {{[GLOBAL] имя_курсора}

 |@имя_переменной курсора}

За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.

Для удаления данных посредством курсора используется команда DELETE в следующем формате:

DELETE имя_таблицы

 WHERE CURRENT OF {{[GLOBAL] имя_курсора}

 |@имя_переменной курсора}

В результате будет удалена строка, установленная текущей в курсоре.

Закрытие курсора

CLOSE {имя_курсора | @имя_переменной_курсора}

После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.

Освобождение курсора

Закрытие курсора не освобождает ассоциированную с ним память, нужно явным образом освободить ее с помощью оператора DEALLOCATE. После освобождения курсора освобождается и память, при этом становится возможным повторное использование имени курсора.

DEALLOCATE { имя_курсора |

 @имя_переменной_курсора }

Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS

Функция @@FETCH_STATUS возвращает:

0, если выборка завершилась успешно;

-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора;

-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.

Пример 13.1. Объявление курсора

DECLARE abc CURSOR SCROLL FOR

SELECT * FROM Клиент

Пример 13.2. Использование переменной для объявления курсора.

DECLARE @MyCursor CURSOR

SET @MyCursor=CURSOR LOCAL SCROLL FOR

SELECT * FROM Клиент

Пример 13.3. Объявление и открытие курсора.

DECLARE abc CURSOR GLOBAL SCROLL FOR

SELECT * FROM Клиент

OPEN abc

Пример 13.4. Использование переменной для переприсваивания курсора.

DECLARE @MyCursor CURSOR

SET @MyCursor=abc

Пример 13.5. Разработать курсор для вывода списка фирм и клиентов из Москвы.

DECLARE  @firm    VARCHAR(50),

        @fam     VARCHAR(50),

        @message VARCHAR(80)

PRINT ' Список клиентов'

DECLARE klient_cursor CURSOR LOCAL FOR

   SELECT Фирма, Фамилия

   FROM Клиент

   WHERE Город='Москва'

   ORDER BY Фирма, Фамилия

OPEN klient_cursor

FETCH NEXT FROM klient_cursor INTO @firm, @fam

WHILE @@FETCH_STATUS=0

BEGIN

   SELECT @message='Клиент '+@fam+

                   ' Фирма '+ @firm

   PRINT @message

-- переход к следующему клиенту--

   FETCH NEXT FROM klient_cursor

     INTO @firm, @fam

END

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.6. Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.

DECLARE @id_kl    INT,

       @firm     VARCHAR(50),

       @fam      VARCHAR(50),       

       @message  VARCHAR(80),

       @nam      VARCHAR(50),

       @d        DATETIME,

       @p        INT,

       @s        INT

SET @s=0

PRINT '  Список покупок'

DECLARE klient_cursor CURSOR LOCAL FOR

   SELECT КодКлиента, Фирма, Фамилия

   FROM Клиент

   WHERE Город='Москва'

   ORDER BY Фирма, Фамилия

OPEN klient_cursor

FETCH NEXT FROM klient_cursor

INTO @id_kl, @firm, @fam

WHILE @@FETCH_STATUS=0

BEGIN

   SELECT @message='Клиент '+@fam+

       ' Фирма '+ @firm

   PRINT @message

   SELECT @message='Наименование товара Дата

       покупки Стоимость'

   PRINT @message

   DECLARE tovar_cursor CURSOR FOR

       SELECT Товар.Название, Сделка.Дата,

           Товар.Цена*Сделка.Количество AS

   Стоимость

       FROM Товар INNER JOIN Сделка ON Товар.

   КодТовара=Сделка.КодТовара

       WHERE Сделка.КодКлиента=@id_kl

   OPEN tovar_cursor

   FETCH NEXT FROM tovar_cursor

     INTO @nam, @d, @p

   IF @@FETCH_STATUS<>0

       PRINT ' Нет покупок'

   WHILE @@FETCH_STATUS=0

   BEGIN

       SELECT @message='   '+@nam+'   '+

          CAST(@d AS CHAR(12))+'  '+

          CAST(@p AS CHAR(6))

       PRINT @message

       SET @s=@s+@p

       FETCH NEXT FROM tovar_cursor

       INTO @nam, @d, @p  

   END

   CLOSE tovar_cursor

   DEALLOCATE tovar_cursor

 

   SELECT @message='Общая стоимость '+

      CAST(@s AS CHAR(6))

   PRINT @message    

-- переход к следующему клиенту--

   FETCH NEXT FROM klient_cursor

   INTO @id_kl, @firm, @fam

END

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.

DECLARE @firm     VARCHAR(50),

       @fam      VARCHAR(50),

       @tel      VARCHAR(8),

       @message  VARCHAR(80)

PRINT '  Список клиентов'

DECLARE klient_cursor CURSOR GLOBAL SCROLL

KEYSET FOR

       SELECT Фирма, Фамилия, Телефон

       FROM Клиент

       WHERE Город='Москва'

       ORDER BY Фирма, Фамилия

FOR UPDATE

OPEN klient_cursor

FETCH NEXT FROM klient_cursor

   INTO  @firm, @fam, @tel

WHILE @@FETCH_STATUS=0

BEGIN         

   SELECT @message='Клиент '+@fam+

       '  Фирма  '+@firm '  Телефон '+ @tel

   PRINT @message

-- если номер телефона начинается на 1,

-- удалить клиента с таким номером

   IF @tel LIKE ‘1%’

      DELETE Клиент

      WHERE CURRENT OF klient_cursor

   ELSE

-- переход к следующему клиенту

   FETCH NEXT FROM klient_cursor

     INTO @firm, @fam, @tel

END

FETCH ABSOLUTE 1 FROM klient_cursor

   INTO @firm, @fam, @tel

-- в первой записи заменить первую цифру в

-- номере телефона на 4

  UPDATE Клиент SET Телефон=’4’ +     

  RIGHT(@tel,LEN(@tel)-1))

       WHERE CURRENT OF klient_cursor

SELECT @message='Клиент '+@fam+'  Фирма  '+

   @firm '  Телефон '+ @tel

   PRINT @message

CLOSE klient_cursor

DEALLOCATE klient_cursor

Пример 13.8. Использование курсора как выходного параметра процедуры. Процедура возвращает набор данных – список товаров.

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


 

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

4738. Конденсаційні установки. Замкненість пароводяного циклу ТЕС та АЕС 1.09 MB
  Конденсаційні установки Замкненість пароводяного циклу ТЕС та АЕС досягається конденсацією відпрацьованої пари у конденсаційній установці (конденсаторі). Цей процес відбувається при постійному тискові завдяки передачі тепла конденсації пари во...
4739. Применение уравнения Шредингера 120.5 KB
  Применение уравнения Шредингера Частица в потенциальной яме с бесконечно высокими стенками. Пусть в одномерном пространстве создано силовое поле, потенциальная энергия которого бесконечна везде, кроме области...
4740. Развитие и деловая оценка персонала на примере ФГУП Институт реакторных материалов 663.5 KB
  Введение Жизнестойкость, конкурентоспособность национальной экономики напрямую зависят от того, насколько образованы и подготовлены к профессиональной деятельности работники, насколько эффективно используются их знания и навыки, как отлажен механизм...
4741. Расчёт эксплуатационных свойств автомобиля 512 KB
  Введение Тяговый расчет автомобиля производится с целью определения его тяговых и динамических качеств. Тяговый расчет подразделяется на: тяговый расчет проектируемой машины поверочный тяговый расчет, производимый для существующей машины. Поверочны...
4742. Проектирование коробки скоростей станка 16К20 559.5 KB
  Проектирование коробки скоростей станка 16К20 Общая характеристика станка Станок предназначен для выполнения разнообразных токарных работ: нарезания правой и левой метрической, дюймовой, одно и многозаходных резьб с нормальным и увеличенным шагом н...
4743. Холодильник рыбный в г. Волгоград емкостью 4000 т 350.5 KB
  Задание на проектирование. Тип холодильника – рыбный. Место строительства – город Волгоград. Условная емкость – 4000 т. Холодильный агент – R 717 (аммиак). Технологическое оборудование: Скороморозильный аппарат 20 т всут. Система ...
4744. Реконструкция пятиэтажного двухсекционного жилого дома серии 1-447 С-39 481.5 KB
  Введение В курсовом проекте представлена реконструкция пятиэтажного двухсекционного жилого дома серии 1-447 С-39 массового строительства периода 60-х годов прошлого столетия. В виду того, что с момента постройки здания прошло уже порядка 45-50...
4745. Модернизацией производства на базе применения новейших достижений науки и техники 183.76 KB
  Введение Интенсификация производства в машиностроении неразрывно связана с техническимперевооружением и модернизацией производства на базе примененияновейшихдостижений науки и техники. Техническоеперевооружение, подготовка пр...
4746. Выбор напряжений питающих линии и расположение трансформаторных подстанций и их модернизация 966 KB
  Введение. Для обеспечения электроэнергией в необходимом количестве и соответствующего качества служат системы электроснабжения промышленных предприятий, состоящие из сетей напряжением до 1000 В и выше и трансформаторных, преобразовательных, и распре...