Declare Cursor

Реализация курсора в базе данных напоминает класс Java, имеющий набор данных и методы для их обработки. При этом sql cursor использует данные как обычный массив. Курсоры могут быть использованы в триггерах, хранимых процедурах и функциях.

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

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

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

В отдельных случаях без применения курсора не обойтись. Однако по возможности следует избегать использование курсора и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Это связано с тем, что курсоры не позволяют проводить операции изменения над всем объемом данных и скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.

Если программа может изменить данные, загруженные в cursor, то он называется модифицируемым. Говоря о курсорах, не следует забывать об изолированности транзакций. Один пользователь изменяет запись при помощи курсора, в то время, как другой пользователь читает эту запись при помощи собственного курсора. Более того, он может изменить ту же запись, что обуславливает необходимость соблюдения целостности данных.

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

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

declare cursor_name 
    [INSENSITIVE][SCROLL] cursor
  for select_statement
  [for { read_only | update 
    [of имя_столбца[,...n]]}]

В данном выражении объявляется курсор declare cursor c именем "cursor_name".

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

При использовании ключевого слова SCROLL созданный курсор можно прокручивать в любом направлении, что позволяет применять любые команды выборки. Если этот аргумент опускается, то cursor окажется последовательным, т.е. его просмотр будет возможен только в одном направлении – от начала к концу.

Выражение select_statement указывает на конструкцию чтения информации типа select ... from ... . Оно не должно содержать оператор into, поскольку cursor имеет свой оператор fetch для заполнения переменных данными курсора.

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

Создание курсора с аргументом FOR UPDATE позволяет выполнять в курсоре изменение данных либо в указанных столбцах, либо, при отсутствии аргумента OF имя_столбца, во всех столбцах.

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

Открытие курсора, cursor open

В SQL определен следующий синтаксис открытия курсора "cursor open"" :

open cursor_name;

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

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

fetch cursor_name 
   into var_name [, var_name] ...;

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

Закрытие курсора, cursor close

Оператор close закрывает cursor. Если оператор явно не указан, то курсор закрывается автоматически при закрытии соответствующего программного блока.

close cursor_name;

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

В каждой СУБД имеются свои особенности использования курсора.

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

В PL/SQL имеется четыре курсорных атрибута %FOUND, %NOTFOUND, %ISOPEN и %ROWCOUNT. Атрибуты курсора объявляются подобно операторам %TYPE и %ROWTYPE, справа от имени курсора.

Атрибут %FOUND

Атрибут %NOTFOUND

Атрибут %NOTFOUND является полной противоположностью %FOUND.

Атрибут %ISOPEN

Атрибут %ISOPEN указывает только на то, открыт ли курсор или нет.

Атрибут %ROWCOUNT

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

Пример SQL курсора в СУБД Oracle

declare
   v_id     managers.id  %TYPE;
   v_name   managers.name%TYPE;
   v_comm   managers.comm%TYPE; 
   crs cursor for
      select id, name, sum(comm) as comm
         from managers
            where data between '2014-11-01' and '2014-11-30'
          group by id, name;
begin
   open crs;
 
   loop
      EXIT WHEN crs%NOTFOUND;
      FETCH crs into v_id, v_name, v_comm;

      insert into bonus( id, name, comm )
         values (crs.id, crs.name, crs.comm);
   end loop;

   commit;
 
   close crs;
end;

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

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

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

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

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

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

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

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

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

declare cursor_name cursor [LOCAL | GLOBAL]
  [FORWARD_ONLY | SCROLL]
  [STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
  [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
  [TYPE_WARNING]
  for SELECT_оператор
  [for UPDATE [OF имя_столбца[,...n]]]

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

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

Тип курсора определяют операторы :

• STATIC - создание статического курсора;
• DYNAMIC - создание динамического курсора;
• KEYSET - создание ключевого курсора.

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

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

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

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

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

fetch [[ FIRST | LAST | NEXT | PRIOR
       | ABSOLUTE {номер_строки | @переменная_номера_строки}
       | RELATIVE {номер_строки | @переменная_номера_строки}]
     from ] {{[GLOBAL ] cursor_name } | @имя_переменной_курсора }
       [into @имя_переменной [,...n]]

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

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

При указании оператора PRIOR будет возвращена строка, находящаяся перед текущей. Эта строка становится текущей.

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

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

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

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

Изменение и удаление данных с использованием курсора

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

update table_name
  set { column_name = { DEFAULT | NULL | выражение }}
      [,...n]
     where current of {{[GLOBAL] cursor_name} | @имя_переменной курсора}

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

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

delete имя_таблицы
  where current of {{[GLOBAL] cursor_name} | @имя_переменной курсора}

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

Освобождение памяти, deallocate

Для удаления курсора из памяти используется команда

deallocate cursor_name;

Атрибут @@FETCH_STATUS

Для определения наличия строк в курсоре следует использовать глобальную переменную @@FETCH_STATUS, которая принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю.

Пример курсора в SQL сервере

declare  @company    varchar( 50),
         @manager    varchar( 50),
         @message    varchar(256);
declare crs_clients cursor local 
   for select company, manager
    from customers
      where city = 'Moscow'
       order by company, manager;

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

open crs_clients;
fetch next from crs_clients 
   into @company, @manager;
while @@FETCH_STATUS = 0
  begin
    select @message = 'Компания ' + @company +
                      ' менеджер ' + @manager;
                      
    print @message;

     -- переход к следующей записи

    fetch next from crs_clients 
       into @company, @manager;
  end;
close crs_clients;
deallocate crs_clients;