Пример использования курсора в ms sql. Использование курсоров и циклов в Transact-SQL

Курсор – это объект, позволяющий по отдельности обрабатывать строки из результирующего набора, возвращенного оператором SELECT. Далее будут рассматриваться курсоры, поддерживаемые в языке Transact- SQL. Это серверные курсоры, существующие как объекты на стороне сервера БД. Бывают также клиентские курсоры, с которыми работают при создании клиентских приложений БД.

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

Работа с курсором обычно включает следующие шаги:

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

Объявление курсора выполняется с помощью оператора DECLARE, формат которого представлен ниже. Надо отметить, что в SQL Server этот оператор поддерживает как синтаксис стандарта ISO SQL (версия стандарта в документации нс уточняется), так и синтаксис, использующий набор расширений языка Transact-SQL CURSOR

FOR select_statement

Расширенный синтаксис Transact-SQL:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

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

"Локальный" курсор, создаваемый по умолчанию или при явном указании LOCAL, доступен только в пакете заданий, хранимой процедуре или триггере, в которых он был создан. Такой курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера. Исключение составляет случай, когда курсор передается через выходной параметр (OUTPUT) хранимой процедуры. Тогда курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из "области видимости".

FORWARD_ONLY означает, что "передвигаться" по курсору можно только вперед (доступна только команда FETCH NEXT, см. далее), т.е. каждая запись в курсоре может быть обработана не более одного раза. Если FORWARD ONLY указано без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC (см. далее). Если не указан ни один из параметров FORWARD_ONLY или SCROLL, а также не указано ни одно из ключевых слов STATIC, KEYSET или DYNAMIC, то по умолчанию задается параметр FORWARD_ONLY.

SCROLL означает, что "передвигаться" по курсору можно в любом направлении (в операторе FETCH доступно FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL.

STATIC означает, что курсор необновляемый. Результирующий набор данных такого курсора извлекается из БД и сохраняется в базе для временных объектов tempdb. Изменения таблиц, служащих основой для курсора, после этого отображаться в курсоре не будут.

KEYSET – у данного типа курсора набор значений ключей, идентифицирующих отобранные записи, сохраняется во временной таблице. При движении по курсору значения неключевых атрибутов извлекаются из соответствующих таблиц, поэтому изменения в неключевых столбцах будут видны при работе с курсором. Если попавшая в курсор строка к моменту выборки ее оператором FETCH уже удалена из таблицы, служебная переменная @@ FETCH_STATUS вернет значение -2. Строки, добавленные в таблицы после открытия курсора, в курсоре не видны. Если формирующий курсор запрос задействует хотя бы одну таблицу, не имеющую уникального индекса, курсор типа KEYSET преобразуется в тип STATIC.

DYNAMIC – самый "затратный" по потребляемым ресурсам тип курсора, отображающий все изменения данных, сделанные в строках результирующего набора, включая вновь вставленные строки. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. С динамическими курсорами нельзя использовать FETCH ABSOLUTE.

FAST_FORWARD – самый быстродействующий тип курсора, позволяющий перемещаться от одной строки к другой только "вперед". Это тип курсора, принятый по умолчанию (когда необязательные ключевые слова опущены). Он эквивалентен курсору, объявленному с параметрами FORWARD_ONLY и READ_ONLY.

READ_ONLY – определяет курсор "только для чтения": изменения в БД через подобный курсор сделать не удастся.

SCROLL_LOCKS означает, что SQL Server блокирует строки по мере их считывания в курсор, что гарантирует возможность их обновления или удаления через курсор данного типа.

Курсор, объявленный с ключевым словом OPTIMISTIC, не запрашивает блокировку строк и позволяет изменять данные. Если изменения в базовой таблице произошли после считывания данных в курсор, попытка модификации этих данных через курсор приводит к ошибке.

TYPE_WARNING указывает, что при неявном преобразовании курсора из запрашиваемого типа к другому (например, описанное выше преобразование курсора KEYSET в STATIC при отсутствии уникального индекса в таблице), клиенту будет отправлено предупреждение.

Select_statement – оператор SELECT, формирующий результирующий набор курсора.

Инструкция FOR UPDATE, определяет обновляемые столбцы в курсоре. Если указано OF column_name [, . . . n], то для изменений будут доступны только перечисленные столбцы. Если списка столбцов нет, обновление возможно для всех столбцов, кроме случая объявления курсора с параметром READ_ONLY.

Чтобы открыть и заполнить курсор, используется команда

OPEN {{ cursor_name} I @cursor_variable)

При открытии, курсор может указываться по имени (cursor_name) или через переменную типа CURSOR (@cursor_variable). Параметр GLOBAL указывает, что cursor_name – это глобальный курсор.

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

FETCH [

{{ cursor_name] I @cursor_variable]

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

Важно отметить, что если курсор только что был открыт, первое выполнение FETCH NEXT приводит к переходу на первую запись курсора.

Таблица 10.10

Навигация по набору данных курсора

Глобальная переменная @@FETCH_STATUS позволяет узнать результат последнего выполнения оператора FETCH:

О – действие выполнено успешно;

  • -1 – выполнение оператора завершилось неудачно, или строка оказалась вне пределов результирующего набора (курсор закончился);
  • -2 – выбираемая строка отсутствует, например если за время работы с курсором "чувствительного к изменениям" типа текущая запись была удалена из БД.

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

CLOSE {{ cursor_name}|@cursor_variable }

Оператор DEALLOCATE удаляет связь между курсором и его именем или переменной. Если это последнее имя или переменная, ссылающаяся на курсор, сам курсор удаляется и освобождаются все используемые им ресурсы:

DEALLOCATE {{ cursor_name] | @cursor_variable) Рассмотрим несложный пример использования курсора. Здесь из таблицы выбираются авторы и названия книг, изданных не ранее 2000 г., после чего данные в цикле выводят операторам SELECT – каждый раз одна запись с собственным заголовком. Дополнительные пояснения даются комментариями в коде:

/*объявляем переменные*/

DECLARE @auth varchar(50), @title varchar(50)

WHERE >= 2000

/*открываем курсор и "пробегаем" его, выводя автора и название отдельным оператором SELECT*/

FETCH NEXT FROM cursorl INTO @auth, @title

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM cursorl INTO @auth, Stitle

/*закрываем курсор и освобождаем его*/

DEALLOCATE cursorl

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

DECLARE Sauth varchar(50), Stitle varchar(50)

/*объявляем переменную типа курсор*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECT Author, Title FROM dbo.Bookl

WHERE >= 2000

/*присваиваем переменной типа курсор значение*/

SET Scurl = cursorl

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM Scurl INTO Sauth, Stitle

Явный курсор представляет собой команду SELECT , явно определенную в разделе объяв­лений программы. При объявлении явного курсора ему присваивается имя. Для команд INSERT, UPDATE, MERGE и DELETE явные курсоры определяться не могут.

Определив команду SELECT как явный курсор, программист получает контроль над основными стадиями выборки информации из базы данных Oracle. Он определяет, когда открыть курсор (OPEN), когда выбрать из него строки (FETCH), сколько выбрать строк и когда закрыть курсор с помощью команды CLOSE . Информация о текущем состоянии курсора доступна через его атрибуты. Именно высокая детализация контроля делает явные курсоры бесценным инструментом для программиста.

Рассмотрим пример:

1 FUNCTION jealousy_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR jealousy_cur 5 IS 6 SELECT location FROM friends 7 WHERE NAME = UPPER (NAME_IN); 8 8 jealousy_rec jealousy_cur%ROWTYPE; 9 retval NUMBER; 10 BEGIN 11 OPEN jealousy_cur; 13 12 FETCH jealousy_cur INTO jealousy_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 CLOSE jealousy_cur; 26 22 RETURN retval; 23 EXCEPTION 24 WHEN OTHERS THEN 25 IF jealousy_cur%ISOPEN THEN 26 CLOSE jealousy_cur; 27 END IF; 28 END;

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

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

Чтобы получить возможность использовать явный курсор, его необходимо объявить в разделе объявлений блока PL/SQL или пакета:

CURSOR имя_курсора [ ([ параметр [, параметр...]) ] [ RETURN спецификация_геЕигп ] IS команда_SELECT ];

Здесь имя курсора - имя объявляемого курсора; спеиифишция_те?ит - необязательная секция RETURN; KOMaHdaSELECT - любая допустимая SQL -команда SELECT . Курсору также могут передаваться параметры (см. далее раздел «Параметры курсора»). Нако­нец, после команды SELECT...FOR UPDATE можно задать список столбцов для обновления (также см. далее). После объявления курсор открывается командой OPEN , а выборка строк из него осуществляется командой FETCH .

Несколько примеров объявлений явных курсоров.

  • Курсор без параметров . Результирующим набором строк этого курсора является набор идентификаторов компаний, выбранных из всех строк таблицы:
CURSOR company_cur IS SELECT company_id FROM company;
  • Курсор с параметрами. Результирующий набор строк этого курсора содержит единственную строку с именем компании, соответствующим значению переданного параметра:
CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;
  • Курсор с предложением RETURN . Результирующий набор строк этого курсора содержит все данные таблицы employee для подразделения с идентификатором 10:
CURSOR emp_cur RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE department_id = 10;

Имя курсора

Имя явного курсора должно иметь длину до 30 символов и соответствовать тем же правилам, что и остальные идентификаторы PL/SQL . Имя курсора не является пере­менной - это идентификатор указателя на запрос. Имени курсора не присваивается значение, его нельзя применять в выражениях. Курсор используется только в командах OPEN, CLOSE и FETCH , а также для уточнения атрибута курсора.

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

Явные курсоры объявляются в разделе объявлений блока PL/SQL . Курсор может объявляться на уровне пакета, но не в конкретной процедуре или функции пакета. Пример объявления двух курсоров в пакете:

PACKAGE book_info IS CURSOR titles_cur IS SELECT title FROM books; CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

Первый курсор titles_cur возвращает только названия книг. Второй, books_cur , воз­вращает все строки таблицы books, в которых названия книг соответствуют шаблону, заданному в качестве параметра курсора (например, «Все книги, содержащие строку " PL/SQL "»). Обратите внимание: во втором курсоре используется секция RETURN , которая объявляет структуру данных, возвращаемую командой FETCH .

В секции RETURN могут быть указаны любые из следующих структур данных:

  • Запись, определяемая на основе строки таблицы данных с помощью атрибута %ROWTYPE .
  • Запись, определяемая на основе другого, ранее объявленного курсора, также с по­мощью атрибута %rowtype .
  • Запись, определенная программистом.

Количество выражений в списке выборки курсора должно соответствовать количеству столбцов записи имя_таблицы%ROWTYPE, Kypcop%ROWTYPE или тип записи. Типы данных элементов тоже должны быть совместимы. Например, если второй элемент списка вы­борки имеет тип NUMBER , то второй столбец записи в секции RETURN не может иметь тип VARCHAR2 или BOOLEAN .

Прежде чем переходить к подробному рассмотрению секции RETURN и ее преимуществ, давайте сначала разберемся, для чего вообще может понадобиться объявление курсоров в пакете? Почему не объявить явный курсор в той программе, в которой он использу­ется - в процедуре, функции или анонимном блоке?

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

Также стоит рассмотреть возможность создания функции, возвращающей курсорную переменную на базе REF CURSOR . Вызывающая программа осуществляет выборку строк через курсорную переменную. За дополнительной информацией обращайтесь к разделу «Курсорные переменные и REF CURSOR ».

Объявляя курсоры в пакетах для повторного использования, следует учитывать одно важное обстоятельство. Все структуры данных, в том числе и курсоры, объ­являемые на «уровне пакета» (не внутри конкретной функции или процедуры), сохраняют свои значения на протяжении всего сеанса. Это означает, что пакетный курсор будет оставаться открытым до тех пор, пока вы явно не закроете его, или до завершения сеанса. Курсоры, объявленные в локальных блоках, автоматически закрываются при завершении этих блоков.

А теперь давайте разберемся с секцией RETURN . У объявления курсора в пакете имеется одна интересная особенность: заголовок курсора может быть отделен от его тела. Такой заголовок, больше напоминающий заголовок функции, содержит информацию, которая необходима программисту для работы: имя курсора, его параметры и тип возвращаемых данных. Телом курсора служит команда SELECT . Этот прием продемонстрирован в новой версии объявления курсора books_cur в пакете book_info:

PACKAGE book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; END; PACKAGE BODY book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

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

  • Сокрытие информации . Курсор в пакете представляет собой «черный ящик». Это удобно для программистов, потому что им не нужно ни писать, ни даже видеть команду SELECT . Достаточно знать, какие записи возвращает этот курсор, в каком порядке и какие столбцы они содержат. Программист, работающий с пакетом, ис­пользует курсор как любой другой готовый элемент.
  • Минимум перекомпиляции . Если скрыть определение запроса в теле пакета, то изменения в команду SELECT можно будет вносить, не меняя заголовок курсора в спецификации пакета. Это позволяет совершенствовать, исправлять и повторно компилировать код без перекомпиляции спецификации пакета, благодаря чему за­висящие от этого пакета программы не будут помечены как недействительные и их также не нужно будет перекомпилировать.

Открытие явного курсора

Использование курсора начинается с его определения в разделе объявлений. Далее объявленный курсор необходимо открыть. Синтаксис оператора OPEN очень прост:

OPEN имя_курсора [ (аргумент [, аргумент...]) ];

Здесь имякурсора - это имя объявленного ранее курсора, а аргумент - значение, передаваемое курсору, если он объявлен со списком параметров.

Oracle также поддерживает синтаксис FOR при открытии курсора, который ис­пользуется как для курсорных переменных (см. раздел «Курсорные переменные и REF CURSOR »), так и для встроенного динамического SQL .

Открывая курсор, PL/SQL выполняет содержащийся в нем запрос. Кроме того, он идентифицирует активный набор данных - строки всех участвующих в запросе таблиц, соответствующие критерию WHERE и условию объединения. Команда OPEN не извлекает данные - это задача команды FETCH .

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

Более того, если команда SELECT содержит секцию FOR UPDATE , все идентифицируемые курсором строки блокируются при его открытии.

При попытке открыть уже открытый курсор PL/SQL выдаст следующее сообщение об ошибке:

ORA-06511: PL/SQL: cursor already open

Поэтому перед открытием курсора следует проверить его состояние по значению атри­бута %isopen:

IF NOT company_cur%ISOPEN THEN OPEN company_cur; END IF;

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

Если в программе выполняется цикл FOR с использованием курсора, этот курсор не нуждается в явном открытии (выборке данных, закрытии). Ядро PL/SQL делает это автоматически.

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

Команда SELECT создает виртуальную таблицу - набор строк, определяемых условием WHERE со столбцами, определяемыми списком столбцов SELECT . Таким образом, курсор представляет эту таблицу в программе PL/SQL . Основным назначением курсора в программах PL/SQL является выборка строк для обработки. Выборка строк курсора выполняется командой FETCH:

FETCH имя_курсора INTO запись_или_список_переменных;

Здесь имя курсора - имя курсора, из которого выбирается запись, а запись или список переменных - структуры данных PL/SQL , в которые копируется следующая строка активного набора записей. Данные могут помещаться в запись PL/SQL (объявленную с атрибутом %ROWTYPE или объявлением TYPE) или в переменные (переменные PL/SQL или переменные привязки - как, например, в элементы Oracle Forms).

Примеры явных курсоров

Следующие примеры демонстрируют разные способы выборки данных.

  • Выборка данных из курсора в запись PL/SQL:
DECLARE CURSOR company_cur is SELECT ...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec;
  • Выборка данных из курсора в переменную:
FETCH new_balance_cur INTO new_balance_dollars;
  • Выборка данных из курсора в строку таблицы PL/SQL, переменную и переменную привязки Oracle Forms:
FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

Данные, выбираемые из курсора, всегда следует помещать в запись, объявленную на основе того же курсора с атрибутом %ROWTYPE ; избегайте выборки в списки переменных. Выборка в запись делает код более компактным и гибким, позволяет изменять список выборки без изменения команды FETCH .

Выборка после обработки последней строки

Открыв курсор, вы по очереди выбираете из него строки, пока они все не будут исчер­паны. Однако и после этого можно выполнять команду FETCH .

Как ни странно, в этом случае PL/SQL не инициирует исключение. Он просто ничего не делает. Поскольку выбирать больше нечего, значения переменных в секции INTO команды FETCH не изменяются. Иначе говоря, команда FETCH не устанавливает значения этих переменных равными NULL .

Псевдонимы столбцов явного курсора

Команда SELECT в объявлении курсора определяет список возвращаемых им столбцов. Наряду с именами столбцов таблиц этот список может содержать выражения, называ­емые вычисляемыми, или виртуальными столбцами.

Псевдоним (alias) столбца представляет собой альтернативное имя, указанное в ко­манде SELECT для столбца или выражения. Задав подходящие псевдонимы в SQL*Plus , можно вывести результаты произвольного запроса в удобочитаемом виде. В подоб­ных ситуациях псевдонимы не являются обязательными. С другой стороны, при использовании явных курсоров псевдонимы вычисляемых столбцов необходимы в следующих случаях:

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

Рассмотрим следующий запрос. Команда SELECT выбирает названия всех компаний, заказывавших товары в течение 2001 года, а также общую сумму заказов (предпола­гается, что для текущего экземпляра базы данных по умолчанию используется маска форматирования DD-MON-YYYY):

SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

При выполнении этой команды в SQL*Plus будет получен следующий результат:

COMPANY_NAME SUM (INV_AMT)
ACME TURBO INC. 1000
WASHINGTON HAIR CO. 25.20

Как видите, заголовок столбца SUM (INV_AMT) плохо подходит для отчета, но для простого просмотра данных он вполне годится. Теперь выполним тот же запрос в программе PL/ SQL с использованием явного курсора и добавим псевдоним столбца:

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM company C, invoice I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; END;

Без псевдонима я не смогу сослаться на столбец в структуре записи comp_rec . При на­личии псевдонима с вычисляемым столбцом можно работать точно так же, как с любым другим столбцом запроса:

IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE (" You have exceeded your credit limit of $5000 by " || TO_CHAR (comp_rec.total_sales - 5000, "$9999")); END IF;

При выборке строки в запись, объявленную с атрибутом %ROWTYPE , доступ к вычис­ляемому столбцу можно будет получить только по имени - ведь структура записи определяется структурой самого курсора.

Закрытие явного курсора

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

Синтаксис команды CLOSE:

CLOSE имя_курсора;

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

  • Если курсор объявлен и открыт в процедуре, не забудьте его закрыть после завер­шения работы с ним; в противном случае в вашем коде возникнет утечка памяти. Теоретически курсор (как и любая структура данных) должен автоматически закры­ваться и уничтожаться при выходе из области действия. Как правило, при выходе из процедуры, функции или анонимного блока PL/SQL действительно закрывает все от­крытые в нем курсоры. Но этот процесс связан с определенными затратами ресурсов, поэтому по соображениям эффективности PL/SQL иногда откладывает выявление и закрытие открытых курсоров. Курсоры типа REF CURSOR по определению не могут быть закрыты неявно. Единственное, в чем можно быть уверенным, так это в том, что по завершении работы «самого внешнего» блока PL/SQL , когда управление будет возвращено SQL или другой вызывающей программе, PL/SQL неявно закроет все открытые этим блоком или вложенными блоками курсоры, кроме REF CURSOR . В статье «Cursor reuse in PL/SQL static SQL » из Oracle Technology Network приво­дится подробный анализ того, как и когда PL/SQL закрывает курсоры. Вложенные анонимные блоки - пример ситуации, в которой PL/SQL не осуществляет неяв­ное закрытие курсоров. Интересная информация по этой теме приведена в статье Джонатана Генника « Does PL/SQL Implicitly Close Cursors ?».
  • Если курсор объявлен в пакете на уровне пакета и открыт в некотором блоке или программе, он останется открытым до тех пор, пока вы его явно не закроете, или до завершения сеанса. Поэтому, завершив работу с курсором пакетного уровня, его следует немедленно закрыть командой CLOSE (и кстати, то же самое следует делать в разделе исключений):
BEGIN OPEN my_package.my_cursor; ... Работаем с курсором CLOSE my_package.my_cursor; EXCEPTION WHEN OTHERS THEN IF mypackage.my_cursor%ISOPEN THEN CLOSE my_package.my_cursor; END IF; END;
  • Курсор можно закрывать только в том случае, если ранее он был открыт; в про­тивном случае будет инициировано исключение INVALID_CURS0R . Состояние курсора проверяется с помощью атрибута %ISOPEN:
IF company_cur%ISOPEN THEN CLOSE company_cur; END IF;
  • Если в программе останется слишком много открытых курсоров, их количество может превысить значение параметра базы данных OPEN_CURSORS . Получив сообще­ние об ошибке, прежде всего убедитесь в том, что объявленные в пакетах курсоры закрываются после того, как надобность в них отпадет.

Атрибуты явных курсоров

Oracle поддерживает четыре атрибута (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) для полу­чения информации о состоянии явного курсора. Ссылка на атрибут имеет следующий синтаксис: курсор%атрибут

Здесь курсор - имя объявленного курсора.

Значения, возвращаемые атрибутами явных курсоров, приведены в табл. 1.

Таблица 1. Атрибуты явных курсоров

Значения атрибутов курсоров до и после выполнения различных операций с ними указаны в табл. 2.

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

  • При попытке обратиться к атрибуту %FOUND, %NOTFOUND или %ROWCOUNT до открытия курсора или после его закрытия Oracle инициирует исключение INVALID CURSOR (ORA-01001).
  • Если после первого выполнения команды FETCH результирующий набор строк ока­жется пустым, атрибуты курсора возвращают следующие значения: %FOUND = FALSE , %NOTFOUND = TRUE и %ROWCOUNT = 0.
  • При использовании BULK COLLECT атрибут %ROWCOUNT возвращает количество строк, извлеченных в заданные коллекции.

Таблица 2. Значения атрибутов курсоров

Операция %FOUND %NOTFOUND %ISOPEN %ROWCOUNT
До OPEN Исключение
ORA-01001 		Исключение
ORA-01001 		FALSE Исключение
ORA-01001
После OPEN NULL NULL TRUE 0
До первой выборки FETCH NULL NULL TRUE 0
После первой выборки
FETCH 		TRUE FALSE TRUE 1
Перед последующими
FETCH 		TRUE FALSE TRUE 1
После последующих FETCH TRUE FALSE TRUE Зависит от данных
Перед последней выборкой FETCH TRUE FALSE TRUE Зависит от данных
После последней выборки FETCH TRUE FALSE TRUE Зависит от данных
Перед CLOSE FALSE TRUE TRUE Зависит от данных
После CLOSE Исключение Исключение FALSE Исключение

Использование всех этих атрибутов продемонстрировано в следующем примере:

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

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

  • Расширение возможности многократного использования курсоров . Вместо того чтобы жестко кодировать в предложении WHERE значения, определяющие условия отбора данных, можно использовать параметры для передачи в это предложение новых значений при каждом открытии курсора.
  • Решение проблем, связанных с областью действия курсоров . Если вместо жестко закодированных значений в запросе используются параметры, результирующий набор строк курсора не привязан к конкретной переменной программы или бло­ка. Если в программе имеются вложенные блоки, курсор можно определить на верхнем уровне и использовать его во вложенных блоках с объявленными в них переменными.

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

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

CURSOR joke_cur IS SELECT name, category, last_used_date FROM Jokes;

В результирующий набор курсора включаются все записи таблицы joke . Если же нам нужно только некоторое подмножество строк, в запрос включается секция WHERE:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM jokes WHERE category = "HUSBAND";

Для выполнения этой задачи мы не стали использовать параметры, да они и не нужны. В данном случае курсор возвращает все строки, относящиеся к конкретной категории. Но как быть, если при каждом обращении к этому курсору категория изменяется?

Курсоры с параметрами

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

PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Курсор со списком параметров, состоящим || из единственного строкового параметра. */ CURSOR joke_cur (category_in IN VARCHAR2) IS SELECT name, category, last_used_date FROM Joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Теперь при открытии курсора ему передается аргумент */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec;

Между именем курсора и ключевым словом IS теперь содержится список параметров. Жестко закодированное значение HUSBAND в предложении WHERE заменено ссылкой на параметр UPPER (category_in). При открытии курсора можно будет задать значение HUSBAND , husband или HuSbAnD - курсор все равно будет работать. Название категории, для которой курсор должен вернуть строки таблицы joke, задается в операторе OPEN (в скобках) в виде литерала, константы или выражения. В момент открытия курсора производится разбор команды SELECT , а параметр связывается со значением. Затем определяется результирующий набор строк - и курсор готов к выборке.

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

Новый курсор можно открывать с указанием любой категории:

OPEN joke_cur (Jokes_pkg.category); OPEN joke_cur ("husband"); OPEN joke_cur ("politician"); OPEN joke_cur (Jokes_pkg.relation || "-IN-LAW");

Параметры курсора чаще всего используются в условии WHERE , но ссылаться на них можно и в других местах команды SELECT:

DECLARE CURSOR joke_cur (category_in IN ARCHAR2) IS SELECT name, category_in, last_used_date FROM joke WHERE category = UPPER (category_in);

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

Область действия параметра курсора

Область действия параметра курсора ограничивается этим курсором. На параметр курсо­ра нельзя ссылаться за пределами команды SELECT , связанной с курсором. Приведенный ниже фрагмент PL/SQL не компилируется, потому что идентификатор program_name не является локальной переменной в блоке. Это формальный параметр курсора, который определен только внутри курсора:

DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_name:= "THE BREATHING MUMMY"; /* Недопустимая ссылка */ OPEN scariness_cur (program_name); .... CLOSE scariness_cur; END;

Режимы параметра курсора

Синтаксис параметров курсоров очень похож на синтаксис процедур и функций - за исключением того, что параметры курсоров могут быть только параметрами IN . Для параметров курсоров нельзя задавать режимы OUT или IN OUT . Эти режимы позволяют передавать и возвращать значения из процедур, что не имеет смысла для курсора. Существует только один способ получения информации от курсора: выборка записи и копирование значений из списка столбцов в секции INTO

Значения параметров по умолчанию

Параметрам курсоров могут присваиваться значения по умолчанию. Пример курсора со значением параметра по умолчанию:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;

Поскольку для параметра emp_id_in определено значение по умолчанию, в команде FETCH его значение можно не указывать. В этом случае курсор вернет информацию о сотруднике с кодом 0.


Курсор - ссылка на контекстную область памяти. В некоторых реализациях языка программирования SQL (Oracle, Microsoft SQL Server) - получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи. бы сказал, что курсор - это виртуальная таблица которая представляет собой альтернативное хранилище данных. При этом курсор, позволяет обращаться к своим данным, как к данным обычного массива.
Используются курсоры в хранимых процедурах. Довольно теории давайте рассмотрим пример:
У нас есть база данных (база немного не хорошая, это одна из моих лабораторных работ, но наш препод по базам данных настаивал на такой структуре)
/*данные о банке */
CREATE TABLE `bank` (

`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,


PRIMARY KEY (`BankId`)

)ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*данные о вкладах */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*данные о вкладчиках*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`)

)ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"

Допустим нам нужно получать по очереди каждый банк и производить с ним какие то действия, помочь в этом нам мог бы такой вот запрос

Select `bank`.* FROM `bank` LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1
. Таким образом используя LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 мы извлекаем в цикле из таблицы bank по очереди каждую запись и производим с ней нужные нам действия, при этом увеличивая значение НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Теперь сделаем то же самое но используя курсор
Begin
/* переменные куда мы извлекаем данные */
Declare vBankId integer ;
Declare vBankName VARCHAR (50);
Declare vAddress VARCHAR (50);
Declare vPhone VARCHAR (50);
/* переменная hadler - a*/
Declare done integer default 0;
/*Объявление курсора*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER назначение, которого поясним чуть ниже*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* открытие курсора */
Open BankCursor;
/*извлекаем данные */
WHILE done = 0 DO

делаем нужные нам действия
END WHILE ;
/*закрытие курсора */
Close BankCursor;
END ;

* This source code was highlighted with Source Code Highlighter .

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data - zero rows fetched, selected, or processed

SQLSTATE: 02000 срабатывает когда достигнут конец курсора, или когда select или update возвращяет пустую строку.

Следующей строкой мы объявили курсор DECLARE cursor_name CURSOR FOR select_statement;
Открываем курсор Open cursor_name;
Дальше пока не достигаем конец курсора (WHILE done = 0 DO) извлекаем данные и обрабатываем их.
Перед выходом из хранимой процедуры необходимо курсор закрыть. Close cursor_name;

Вроде ничего сложного. Но с SQLSTATE "02000" связанно много подводных камней.

WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;

Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .


Всё хорошо, и правильно с точки зрения синтаксиса. Но с логической точки зрения нет. Может случиться так что вкладчики не открыли счетов в каком то банке, тогда для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */


if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */

end if ;
делаем какие то действия
END WHILE ;

* This source code was highlighted with Source Code Highlighter .


первым запросом мы проверили а есть ли вклады (если их нет то vContributeAmountSUM == 0) и только если таковые имеются мы извлекаем данные.

Теперь допустим нам нужно излечь общую сумму на счетах в разных банках у каждого клиента
Declare ClientSummCursor Cursor for Select sum

Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`.`ClientId`;

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;


делаем какие то действия.
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Может возникнуть та же ситуация, когда данные в курсоре ClientSummCursor, закончатся раньше чем данные в BankCursor, сработает SQLSTATE: 02000, переменная done установится в 1, и цикл while закончиться раньше чем мы ожидали. Этого можно избежать поступив следующим образом

Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* извлечем для банка сумму любого из его вкладов */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* проверим действительно ли есть вклады в этом банке */
if (vContributeAmountSUM > 0) then
/* извлечем для банка сумму любого из его вкладов */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* до извлечения данных из второго курсора запомним состояние sqlstate */
SET old_status = done;
/* извлекаем нужные нам данные */
FETCH ClientSummCursor INTO vSum,vClientId;
/* проверяем были ли извлечены данные, не стработал ли sqlstate 0200 */
if (done = 0) then
делаем какие то действия.
end if ;
/* перед окончанием while восттановим значение переменной done */
set done = old_status;
END WHILE ;

* This source code was highlighted with Source Code Highlighter .

Всем дочитавшим до этого места спасибо, надеюсь это статься покажется кому то полезной.

Здравствуй, человек-читатель блогов на Community.

Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.
Первое, что нужно знать, курсор – это не хорошо, а плохо . Где есть возможность заменить курсор на INSERT SELECT или на использование временной таблицы, там нужно это сделать (за редким исключением). Курсор – это почти всегда дополнительные ресурсы сервера и резкое падение производительности по сравнению с другими решениями.
Второе, иногда без курсора не обойтись – там где не обойтись без построчного прохода по результату выборки. Вот в таких случаях очень важно правильно создать нужный тип курсора – тот, который соответствует решаемой задаче. Общий синтаксис объявления курсора имеет вид:

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,... n ] ] ] [ ;]

Остановлюсь на первых трех строчках ключевых параметров.
LOCAL или GLOBAL: если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL . Внимание, по умолчанию создается именно GLOBAL курсор!
FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY . Внимание, по умолчанию создается SCROLL курсор!
STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т.е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC (самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.

Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen t

для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE ... CURSOR LOCAL FOR...
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял... внимание... 10 минут 5 секунд !!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант - это все же не использовать курсоры вообще - для СУБД MS SQL намного роднее реляционный, а не навигационный подход.

1) Понятие курсора
В интерактивном SQL однострочные и многострочные запросы не различаются. Во встроенном SQL эти запросы выполняются по-разному. Однострочные запросы возвращают одну строку и нами уже рассмотрены. Когда результатом выполнения запроса является не одна строка, встроенный SQL должен обеспечить прикладной программе возможность построчного получения результатов запроса. Для этого используются курсоры. Под курсором понимается переменная, связанная с запросом. Ее значением является каждая строка, удовлетворяющая запросу. Как и переменные, курсоры должны быть описаны перед их использованием. В отличие от представлений курсоры предназначены для построчной обработки.

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

DECLARE [{}] [[NO ] SCROLL ] CURSOR [{WITH|WITHOUT } HOLD ] FOR [FOR {READ ONLY|UPDATE [OF ]}]

3) Ключевые слова
. SENSITIVE|INSENSITIVE|ASENSITIVE – изменения в результирующем наборе видны | запрещены (зафиксированы с помощью копии набора данных)|СУБД само принимает решение о необходимости сделать копию(действует по умолчанию).
. WITH|WITHOUT HOLD – оставляет открытым | закрывает курсор, если встретился оператор COMMIT.
. SCROLL – [запрещает] извлекать строки результата в произвольном порядке.
. FOR READ ONLY – определяет курсор только для чтения.
. FOR UPDATE OF – блокирует от обновления только указанные столбцы.

4) Объявление курсора в SQL Server

DECLARE CURSOR [LOCAL|GLOBAL ] [FORWARD_ONLY|SCROLL ] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD ] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC ] FOR [FOR UPDATE [OF ]]

. STATIC – Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в базе данных tempdb, поэтому изменения базовых таблиц не влияют на данные, возвращаемые выборками для данного курсора, а сам курсор не позволяет производить изменения.
. KEYSET – Указывает, что членство или порядок строк в курсоре не изменяются после его открытия. Набор ключей, однозначно определяющих строки, встроен в таблицу в базе данных tempdb с именем keyset.
. DYNAMIC – Определяет курсор, отображающий все изменения данных, сделанные в строках результирующего набора при просмотре этого курсора. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE динамическими курсорами не поддерживается.
. FAST_FORWARD – Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.
. SCROLL_LOCKS – Указывает, что позиционированные обновления или удаления, осуществленные через курсор, гарантированно будут успешными. SQL Server блокирует строки по мере считывания в курсор для обеспечения их доступности для последующих изменений. Параметр SCROLL_LOCKS не может указываться вместе с параметром FAST_FORWARD или STATIC.
. OPTIMISTIC – Указывает, что позиционированные обновления или удаления, осуществленные через курсор, не будут выполнены, если строка была обновлена со времени считывания в курсор. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого, чтобы определить, изменялась ли строка после считывания в курсор, выполняется сравнение значений столбца timestamp (или контрольных сумм, если в таблице нет столбца timestamp). Если строка была изменена, то ее позиционированное изменение или удаление невозможно. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.

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

6) Извлечение строк из курсора

FETCH [{NEXT|PRIOR|FIRST|LAST |{ABSOLUTE|RELATIVE }}]
FROM INTO

7) Варианты позиционирования курсора
. NEXT|PRIOR|FIRST|LAST – на следующую|предыдущую|первую|последнюю строку результирующего множества.
. RELATIVE ±N – на строку с положительным или отрицательным смещением относительно текущей строки.
. ABSOLUTE ±N – на строку с явно указанным абсолютным номером позиции от начала или конца курсора.

Примечание : В SQL Server вместо N допускается целочисленная переменная @N.

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

9) Примечания по курсорам
. Если курсор содержит более одной строки, необходимо организовать цикл извлечения данных из него с периодической проверкой достижения последней строки.
. В отличие от таблиц и представлений строки курсора упорядочены либо явным образом с помощью раздела ORDER BY , либо в соответствии с соглашениями, принятыми в конкретной СУБД.
. Курсоры применяются также для выборки из таблиц групп строк, которые можно обновлять или удалять по одной.
. Для того, чтобы курсор был обновляемым, он должен удовлетворять тем же критериям, что и представление, то есть не содержать разделов UNION, ORDER BY, GROUP BY, DISTINCT .

10) Пример на удаление данных из курсора

exec sql declare cursor Cur1 for select * from Customer
where Rating
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; } – Данные для удаления взять из курсора
not_done:
exec sql close cursor Cur1; — Закрываем курсор
exit();

11) Пример на увеличение комиссионных

exec sql declare cursor CurCust for select * from SalesPeople
where SNum in (select SNum from Customer where Rating=300); — Определяем курсор
exec sql open cursor CurCust; — Выполняем курсор
while (sqlca.sqlcode==0) { — Создаем цикл для обновления данных в таблице
exec sql fetch CurCust into:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople set Comm=Comm+.01 where current
of CurCust; } – Данные для обновления взять из курсора
exec sql close cursor CurCust; — Закрываем курсор

SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2

DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
FETCH NEXT FROM Cur1
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1
DEALLOCATE Cur1