Создание курсоров
Первым шагом в использовании курсора является его создание. Курсоры Transact-SQL создаются с помощью оператора DECLARE CURSOR.
Внимание! SQL Server поддерживает два различных метода создания курсоров: с использованием синтаксиса SQL-92 и с использованием синтаксиса Transact-SQL. Синтаксис SQL-92 соответствует стандарту ANSI, но имеет меньшие функциональные возможности, чем синтаксис Transact-SQL, который и рассматривается здесь.
Оператор DECLARE CURSOR имеет следующий синтаксис:
DECLARE имя_курсора CURSOR [видимость] [прокрутка] [тип] [блокировка] [TYPE_WARNING] FOR оператор_выборки [FOR UPDATE [OF имена_столбцов]]
Обратите внимание, что все параметры, определяющие характеристики курсора, – видимость, тип и т.д. – являются необязательными. Значениями по умолчанию для этих параметров являются сложными и могут указывать, либо не указывать способы взаимодействия с исходными записями или представлениями, а также опции работы с базой данных. Чтобы сделать восприятие оператора долее удобным, лучше явно задавать все необходимые вам параметры. При этом вы будете точно знать, что получите.
Видимость курсора определяется с помощью ключевых слов LOCAL или GLOBAL, которые имеют тот же эффект, что и ключевые слова @local_table или @@global_table при объявлении временных таблиц.
Совет. SQL Server будет закрывать и освобождать локальный курсор при выходе за пределы его области действия (видимости), но лучше всегда делать это явно.
Параметр прокрутка допускает использование ключевых слов FORWARD_ONLY и SCROLL, которые задают, соответственно, возможность перемещения только от начала к концу, или в любом направлении.
Параметр тип определяет тип создаваемого курсора. Здесь допустимы ключевые слова STATIC, KEYSET, DYNAMIC и FAST_FORWARD. Параметр типа FAST_FORWARD и параметр прокрутки FORWARD_ONLY являются взаимно исключающими.
Параметр блокировка определяет, могут ли строки модифицироваться курсором, и если да, то могут ли их модифицировать другие пользователи. Если используется ключевое слово READ_ONLY, курсор не может вносить никаких изменений в исходные данные. Тем не менее, другие пользователи могут модифицировать данные, либо это можете делать вы сами с помощью оператора UPDATE. Если в качестве параметра блокировка задано SCROLL_LOCKS, обновления могут быть выполнены только курсором. Все другие операторы UPDATE, как внутри этого же пакета, так и предоставляемые другими пользователями, выполняться не будут.
Последняя опция блокировки, OPTIMISTIC, позволяет осуществлять обновления строк как внутри курсора, так и вне его. Это наиболее гибкая опция, но при этом всегда существует вероятность, что модификация, выполненная курсором, окончится неудачей, если строка была изменена после ее чтения курсором.
Параметр TYPE_WARNING предписывает SQL Server отправлять предупреждающее сообщение клиенту, если тип курсора преобразуется от заданного к другому типу. Это возможно, если вы объявляете курсор, который не поддерживает заданный оператор SELECT.
Параметр оператор_выборки, указываемый в фразе FOR, является обязательным. Он задает строки, которые будут включены во множество курсора.
Фраза FOR UPDATE является необязательной. По умолчанию курсоры являются модифицируемыми, если не задан параметр READ_ONLY, однако и в этом случае лучше все-таки использовать эту фразу, чтобы быть уверенным в полученном результате. Вы можете использовать раздел OF имена_столбцов, чтобы указать определенные строки, для которых вы допускаете модификацию. Если вы опускаете раздел OF имена_столбцов, модификация может быть выполнена для всех столбцов, указанных в операторе SELECT.
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils DECLARE @myCursorVariable CURSOR SET @myCursorVariable = myCursor
Этот синтаксис полезен, если вы хотите создать переменные, которые могут быть назначены различным курсорам. Это может потребоваться, если вы создаете обобщенную процедуру для работы с различными результирующими множествами.
Вы можете объявить курсорную переменную, а затем использовать ее для непосредственного создания курсора.
DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils
При использовании такого синтаксиса курсор не имеет идентификатора, и ссылка на него может быть осуществлена только через переменную. Подобный синтаксис больше подходит для хранимых процедур, которые мы рассмотрим в уроке 28.
Открытие курсора
Объявление курсора создает объект курсора, но не создает набор записей, которыми курсор будет манипулировать (множество курсора). Множество курсора не создается, пока вы не откроете курсор. После достаточно сложного синтаксиса оператора DECLARE CURSOR, синтаксис оператора кажется вполне прозрачным:
OPEN [GLOBAL] курсор_или_переменная
Ключевое слово GLOBAL помогает избежать конфликтов: если курсор, объявленный с ключевым словом LOCAL, и курсор, объявленный с ключевым словом GLOBAL, имеют одинаковый идентификатор, ссылки на курсор будут по умолчанию отнесены к локальному курсору, если вы не использовали ключевое слово GLOBAL. Как и в других подобных случаях, лучше явно указывать ключевое слово, если вы открываете глобальный курсор.
Закрытие курсора
Закончив использование курсора, вы должны его закрыть. Оператор CLOSE освобождает ресурсы, используемые для обслуживания множества курсора, а также освобождает все блокировки, наложенные на строки, если вы использовали параметр SCROLLOCKS в операторе DECLARE. Синтаксис команды CLOSE почти идентичен синтаксису оператора OPEN – меняется только ключевое слово:
CLOSE [GLOBAL] курсор_или_переменная
Освобождение курсора
Последним оператором в последовательности действий, связанных с созданием курсора, является оператор DEALLOCATE. Синтаксис его также прост:
DEALLOCATE [GLOBAL] курсор_или_переменная
Однако здесь есть одна тонкость: оператор DEALLOCATE удаляет идентификатор или курсорную переменную, но он не обязательно удаляет сам курсор. Сам курсор не удаляется до тех пор, пока все ссылающиеся на него идентификаторы будут либо освобождены, либо перестанут действовать (при выходе за пределы области действия). Рассмотрим следующие примеры:
-- Создание курсора DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Создание курсорной переменной DECLARE @cursorVariable CURSOR -- Создание множества записей курсора OPEN myCursor -- Назначение переменной курсору SET @cursorVariable = myCursor -- Освобождение курсора DEALLOCATE myCursor
После освобождения курсора идентификатор myCursor больше не ассоциируется с множеством курсора, но поскольку на множество курсора еще ссылается переменная @cursorVariable, курсор и множество курсора не освобождаются. Если вы явно не освободите также и курсорную переменную, курсор и множество курсора будут существовать, пока переменная не утратит свое действие.
