Курсоры. Типы курсоров. Работа с курсорами

Программирование на Transact SQL. Комментарии. Переменные. Команды управления

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

Обозначение однострочного комментария: -- Однострочный комментарий

Обозначение многострочного комментария:

/* Многострочные

** комментарии*/

Переменные могут быть двух типов – локальные (начинаются с символа @) и глобальные (начинаются с двух символов @@). Локальные переменные существуют в пределах сеанса, глобальные переменные используются для получения информации о сервере в целом (иногда называют функциями). Локальные переменные объявляются командой DECLARE и значения задаются командами SELECT и SET. Например:

DECLARE @R DATETIME, @C INT

SELECT @C=1

SET @R =getdate()

SELECT @C, @R

Обычно рекомендуется для присваивания переменных использовать команду SET, а не SELECT, так как первая работает быстрее. В одной команде SELECT можно присваивать значения нескольким переменным. Команда PRINT передает сообщение длиной 1024 байта обработчику сообщений клиента.

Команды условного выполнения:

IF <логическое выражение>

{ <команда> |< блок команд>}

ELSE

{ <команда> | <блок команд>}

Например, использования команды условного выполнения выглядит так:

IF (SELECT AVG(price) from titles WHERE type = “business”) >$19.6

PRINT ” Сообщение 1”

ELSE

PRINT “ Сообщение 2”

Блок команд – множество команд в операторных скобках BEGIN.. END

Цикл WHILE:

WHILE <логическое условие>

[ <команда> |< блок команд>]

[BREAK]

[CONTINUE]

END

Например, использование цикла с предусловием:

WHILE (SELECT AVG(price) FROM titles) < $25

BEGIN

UPDATE titles SET price =price*1.05

IF (SELECT COUNT(*) FROM titles WHERE price < $15) <10)

CONTINUE

ELSE

IF (SELECT MAX(price) FROM titles) >$50

BREAK

END

Команда WAITFOR переводит запрос в состояние ожидания в течение некоторого интервала или до наступления заданного времени (и потому называется обработчиком события).

WAITFOR {DELAY “время” | TIME “время”}

Параметр DELAY заставляет пакет или процесс сделать паузу заданной длины (максимум – 24 часа). Параметр TIME останавливает процесс до наступления заданного времени. Время задается в формате hh: mi: ss. Например: WAITFOR TIME “ 22:00:00”

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

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

Статические курсоры – курсоры, изолированные от изменений в данных. Сервер сохраняет весь итоговый набор курсора в tempdb при первой выборке.

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

Работа с курсором выполняется в следующем порядке:

1. Объявление курсора.
2. Открытие курсора.
3. Выборка записей, модификация или удаление записей из курсора (обработка записей в курсоре).
4. Закрытие курсора.
5. Освобождение ресурсов курсора.

Объявление курсора реализуется следующей командой:

Declare <имя_курсора> [ INSENSITIVE] CURSOR [LOCAL | GLOBAL ]

[ FORWARD ONLY | SCROLL ] [ STATIC | KEYSET |DYNAMIC ]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

FOR <инструкция_Select>

[FOR UPDATE [OF <список столбцов>]]

Объявление курсора с ключевым словом INSENSITIVE заставляет сервер создать копию данных во временной рабочей таблице. При работе с курсором вы действительно работаете с содержимым временной таблицы, которое не изменяется с модификацией настоящей таблицы. Если при определении курсора не используются опции Read Only и UPDATE, то способности курсора к обновлению определяются следующим.

а) если используется опция ORDER BY в предложении Select или указано INSENSITIVE, курсор будет доступен только для чтения. В противном случае курсор доступен и для обновлений.

б) если в выражении выборки используются GROUP BY, UNION, Distinct или Having курсоры доступны только для чтения и не будут обновляться изменениями. Результаты передаются во временную таблицу и выбираются оттуда.

Так как курсоры могут считывать строки в переменные, находящиеся в хранимых процедурах или пакетах команд, в инструкции Select нельзя применять маску *.

В курсорах может использоваться любая команда SELECT, удовлетворяющая условиям:

-Не может содержать секции INTO, COMPUTE, FOR BROWSE

-При использовании секций GROUP BY, HAVING, DISTINCT, UNION курсор становится статическим,

-Если все таблицы, используемые в курсоре, не имеют уникального индекса, курсор становится статическим,

-Если все столбцы, используемые в ORDER BY, не входят в уникальный индекс, динамический курсор преобразуется в ключевой или статический.

Используемые параметры имеют следующий смысл:

Local – курсор локальный по отношению к пакету,

Global – курсор доступен в течении всего подключения,

Forward_only – курсор допускает перебор записей только в прямом направлении, если ключевое слово STATIC | KEYSET |DYNAMIC не указано, то курсор является динамическим

Scroll – позволяет любые перемещения. Функция, обратная INSENSITIVE. Заставляет курсор читать завершенные изменения и удаления, выполняемые другими процессами сервера. Если вы не объявили курсор с ключевым словом Scroll, единственным методом считыванием информации является последовательный проход результирующего набора

Read Only – эта опция предотвращает любую модификацию данных курсора.

UPDATE – эта опция устанавливается по умолчанию для курсора, определенного на одной таблице.

После объявления курсора для получения возможности считывать данные, его следует открыть с помощью команды OPEN <имя курсора>. Открыть можно только объявленный курсор. Когда курсор открыт, SQL распознает все неизвестные переменные и определяет их текущие состояния.

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

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | @nvar | RELATIVE n | @nvar] FROM] имя_курсора

[INTO @имя_переменной 1, @имя_переменной 2]

Здесь используемые параметры:

NEXT – нормальный порядок считывания (возвращает след. строку)

PRIOR – если курсор определен с ключевым словом Scroll, опция PRIOR позволяет читать предыдущую запись

FIRST – считывание 1-й записи набора

LAST – последняя строка набора

ABSOLUTE n – вызывает считывание n-й строки результирующего набора. Если n – положительное число, то считывание будет с 1-й записи заданное число, если n –отрицательное, то с последней назад заданное число.

Relative n – вызывает считывание n-й записи результирующего набора относительно текущей строки.

Если указываете отрицательное число, то находится нужная строка, отсчитав от текущей назад. Причем, вместо “n” могут использоваться переменные типа int, smallint tinyint.

FROM – следом идет имя курсора из которого считываются данные.

INTO – используется в хранимых процедурах. Когда вы указываете это ключевое слово, данные, возвращаемые курсором, помещаются во временные переменные.

Закрытие курсора осуществляется с помощью команды: Close <имя курсора>

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

освобождении курсора полностью удаляются любые связанные с ним структуры данных, которые SQL Server держал в открытом состоянии. Освобождение курсора отличается от его закрытия. После освобождения вы больше не сможете открыть данный курсор. Команда освобождения памяти от курсора: Deallocate <имя курсора>

Хранимая процедура может иметь несколько уровней курсоров, которые используются для получения максимальной гибкой обработки результирующего набора. Для того, чтобы получить несколько активных курсоров, их надо открыть. Если курсор возвращает один набор данных, что является наиболее распространенным случаем, большинство языков программирования приложений клиента не различают данные в курсоре и команде select. Используются специальные функции для обращения к данным в курсоре, если он и ассоциированная с ним обработка возвращает несколько результирующих наборов. Курсоры позволяют производить индивидуальные операции с записями в наборе, созданном в команде select. Курсор может включать все таблицы базы данных. Модификация записей с помощью курсора выполняется с помощью команд UPDATE, DELETE через включение секции WHERE CURRENT OF. Модификация разрешается только однотабличных курсоров.

Дата добавления: 2014-10-15