Манипулирование строками с помощью курсора

Урок: Курсоры в Transact-SQL

Курсор – это особый временный объект SQL, предназначенный для использования в программах и хранимых процедурах. С его помощью можно в цикле пройти по результирующему набору строк запроса, по отдельности считывая и обрабатывая каждую его строку. В хранимых процедурах с помощью курсоров можно выполнять сложные вычисления, которые трудно выразить с помощью синтаксиса инструкции SELECT. Большой теоретический материал урока дополнен очень хорошими примерами. В частности, рассматривается применение функции CURSOR_STATUS, описание переменных @@CURSOR_ROWS и @@FETCH_STATUS, и многое другое.

Вы научитесь:

• объявлять курсор;
• открывать курсор;
• закрывать курсор;
• освобождать курсор;
• использовать простую команду FETCH;
• осуществлять выборку строки в переменные;
• осуществлять выборку строки по ее абсолютной позиции;
• осуществлять выборку строки по ее относительной позиции;
• выполнять позиционную модификацию;
• выполнять позиционное удаление;
• использовать глобальную переменную @@CURSOR_ROWS для определения количества строк во множестве курсора;
• использовать глобальную переменную @@FETCH_STATUS для определения результатов выполнения команды FETCH;
• использовать функцию CURSOR_STATUS для запроса статуса курсора.

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

Например, поскольку нет возможности указать на определенную строку во множестве, представление пользователю строк по одной за раз может вызвать затруднения. Даже несмотря на то, что предоставляемые Transact-SQL расширения к стандартному языку SQL позволяют реализовать гораздо большие возможности для программирования, тем не менее остаются операции, которые затруднительно, трудоемко или даже вообще невозможно выполнить на основе принципов работы с множествами.

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

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

Microsoft SQL Server реально поддерживает два различных типа курсоров: курсоры Transact-SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DB-Library. Каждое из этих API поддерживает несколько отличающиеся функциональные возможности и использует различный синтаксис. Здесь мы не будем подробно обсуждать курсоры API; если вы планируете использовать их, обратитесь к соответствующей документации на API и языку программирования, который вы собираетесь применить.

Курсоры Transact-SQL создаются с помощью команды DECLARE CURSOR. Как объект курсора, так и множество строк, на которое он указывает, должны существовать на сервере. Подобные курсоры называются серверными курсорами. Если вы используете серверный курсор из приложения, соединенного с SQL Server через сеть, каждая операция с курсором требует двустороннего сетевого взаимодействия. Библиотеки API-курсоров, поддерживающие серверные курсоры, поддерживают также клиентский курсор, который существует в клиентской системе и кэширует строки, которые он обрабатывает на клиенте.

Множество строк, на которое указывает курсор, определяется с помощью команды SELECT. При создании курсора Transact-SQL на команду SELECT накладываются несколько ограничений:

• команда SELECT не может возвращать несколько результирующих множеств;
• команда SELECT не может содержать фразу INTO для создания новой таблицы;
• команда SELECT не может содержать фразу COMPUTE или COMPUTE BY, используемые для агрегирования результатов. (Однако, она может содержать функции агрегирования, например, AVG.)

Характеристики курсоров

Transact-SQL поддерживает несколько различных типов курсоров. Выяснение различных характеристик каждого из курсоров является довольно утомительной задачей, но ее можно облегчить, если принять во внимание для каждого типа курсора три более или менее независимых характеристики: способность отражать изменения в исходных данных, способность осуществлять прокрутку во множестве строк, а также способность модифицировать множество строк.

Отражение изменений

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

База данных Aromatherapy вернет четыре строки, как показано на рис. 27.1. Если в процессе использования вами курсора кто-либо добавит значение Description для элемента Bergamot, либо добавит строку для элемента Bayberry, что произойдет с множеством строк, на которое указывает ваш курсор?

увеличить изображение
Рис. 27.1. База данных Aromatherapy содержит четыре строки, начинающиеся с буквы В.

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

Прокрутка

Второй характеристикой курсора является способность осуществления прокрутки как вперед, так и назад, либо только вперед. Здесь имеет место извечная для программирования дилемма: скорость против гибкости. Последовательные курсоры (forward-only) работают значительно быстрее, но имеют меньшую гибкость.

Обновление

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

Типы курсоров

Transact-SQL поддерживает четыре различных типа курсоров: статические, ключевые, динамические и курсоры быстрого доступа, или "пожарные" (firehose). Каждый тип курсора хранит различные данные относительно строк, на которые он указывает, кроме того, каждому типу курсора свойственны различные сочетания характеристик, рассмотренных в предыдущем разделе.

Статические курсоры

Статический курсор делает как бы моментальный снимок данных, задаваемых оператором SELECT, и хранит их в базе данных tempdb. Он "не чувствует" изменений в структуре или в значениях данных, а поскольку любые модификации будут отражены только в копии, этот курсор всегда открывается в режиме "только чтение". Статические курсоры, однако, могут быть объявлены как последовательные или как прокручиваемые.

Ключевые курсоры

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

Ключевые курсоры могут быть как модифицируемыми, так и иметь режим "только для чтения". Они также могут быть прокручиваемыми или последовательными.

Членство в ключевом курсоре фиксируется на момент объявления курсора. Если в процессе открытого состояния курсора добавляется строка, удовлетворяющая условию отбора, она не будет добавлена во множество. В нашем предыдущем примере, где в качестве условия отбора использовалось LEFT(OilName, 1) = 'B', новая строка со значением поля OilName 'Bayberry' не будет добавлена к строкам, относящимся к области действия курсора.

Аналогично, если изменение вносится в строку, которая после этого не будет удовлетворять условию членства во множестве, например, замена 'Basil' на 'Kumquat', строка все же останется членом множества. Даже если строка удаляется, она по-прежнему остается членом множества, но SQL Server возвращает NULL для всех значений столбцов.

Хотя членство во множестве курсора после открытия курсора остается фиксированным, тем не менее, изменения значений данных, вносимые в исходные таблицы, находят отражение. Например, изменение значения поля Description для строки Bergamot будет возвращено курсором. Однако изменения значений множества ключей отражаются в курсорах только в том случае, если они осуществляются внутри курсора. В продолжение предыдущего примера, если значение поля OilName было изменено с 'Basil' на 'Kumquat' внутри курсора, курсор вернет 'Kumquat'. Если же изменение было внесено другим пользователем, курсор по-прежнему будет возвращать 'Basil'.

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

Динамические курсоры

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

Для динамических курсоров действует одно ограничение: используемый для определения курсора оператор SELECT может содержать фразу ORDER BY только в том случае, если имеется индекс, включающий в себя столбцы, используемые в фразе ORDER BY. Если вы объявляете ключевой курсор с использованием фразы ORDER BY, не оперирующей индексом, SQL Server преобразует курсор в ключевой.

Курсоры быстрого доступа

SQL Server поддерживает специальную оптимизированную форму не прокручиваемого курсора, допускающего только чтение. Этот вид курсора объявляется с использованием ключевого слова FAST_FORWARD, и чаще всего его называют "пожарным" курсором (firehose).

"Пожарные" курсоры очень эффективны, но при их использовании имеются два важных ограничения. Во-первых, если в операторе определения SELECT курсора вы использовали столбцы с типом данных text, ntext или image, а также фразу TOP, SQL Server преобразует курсор в ключевой.

Во-вторых, если оператор SELECT, который вы использовали для определения курсора, содержит таблицы, имеющие триггеры, и таблицы, не имеющие триггеров, курсор преобразуется в статический. Триггеры представляют собой сценарии Transact-SQL, которые автоматически исполняются сервером при выполнении для таблицы операторов Data Manipulation Language (DML). Подробнее мы рассмотрим триггеры в уроке 29, сейчас же обратим внимание на следующий момент: если кто-либо добавляет триггер к одной из таблиц, используемых курсором, ваше приложение может внезапно остановить выполнение, поскольку SQL Server преобразует более быстрый курсор в менее быстрый.

Использование курсоров

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

Создание курсоров

Первым шагом в использовании курсора является его создание. Курсоры Transact-SQL создаются с помощью оператора DECLARE CURSOR.

Внимание! SQL Server поддерживает два различных метода создания курсоров: с использованием синтаксиса SQL-92 и с использованием синтаксиса Transact-SQL. Синтаксис SQL-92 соответствует стандарту ANSI, но имеет меньшие функциональные возможности, чем синтаксис Transact-SQL, который и рассматривается здесь.

Оператор DECLARE CURSOR имеет следующий синтаксис:

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

Видимость курсора определяется с помощью ключевых слов 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.

Курсорные переменные

Transact-SQL позволяет объявлять переменные типа CURSOR. В этом случае стандартный синтаксис DECLARE не создает курсор; вы должны явно установить переменную для курсора с помощью ключевого слова SET.

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

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

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

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

Объявление курсора создает объект курсора, но не создает набор записей, которыми курсор будет манипулировать (множество курсора). Множество курсора не создается, пока вы не откроете курсор. После достаточно сложного синтаксиса оператора DECLARE CURSOR, синтаксис оператора кажется вполне прозрачным:

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

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

Закончив использование курсора, вы должны его закрыть. Оператор CLOSE освобождает ресурсы, используемые для обслуживания множества курсора, а также освобождает все блокировки, наложенные на строки, если вы использовали параметр SCROLLOCKS в операторе DECLARE. Синтаксис команды CLOSE почти идентичен синтаксису оператора OPEN – меняется только ключевое слово:

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

Последним оператором в последовательности действий, связанных с созданием курсора, является оператор DEALLOCATE. Синтаксис его также прост:

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

После освобождения курсора идентификатор myCursor больше не ассоциируется с множеством курсора, но поскольку на множество курсора еще ссылается переменная @cursorVariable, курсор и множество курсора не освобождаются. Если вы явно не освободите также и курсорную переменную, курсор и множество курсора будут существовать, пока переменная не утратит свое действие.

Манипулирование строками с помощью курсора

Курсоры сами по себе не вызвали бы никакого интереса, если бы вы не могли осуществлять с их помощью определенные действия. Transact-SQL поддерживает три различные команды для работы с курсорами: FETCH, UPDATE и DELETE.

Команда FETCH извлекает указанную строку из множества строк курсора. В своем простейшем варианте команда FETCH имеет следующий синтаксис:

В этом формате записи возвращается строка в позиции курсора (текущая строка).