ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2008)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

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

ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [; ] Transact-SQL Extended Syntax 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 ] ] ] [; ]

cursor_name
cursor_name

INSENSITIVE
tempdb ; таким образом, изменения базовых таблиц не отражаются в возвращенных выборками этого курсора данных, и этот курсор не допускает изменения. При использовании синтаксиса ISO, если не указан параметр INSENSITIVE, зафиксированные обновления и удаления, сделанные в базовых таблицах, отображаются в последующих выборках.

SCROLL
Указывает, что доступны все параметры выборки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Если в инструкции DECLARE CURSOR стандарта ISO не указан параметр SCROLL, то поддерживается только параметр выборки NEXT. Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD.

select_statement
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова FOR BROWSE и INTO недопустимы в select_statement объявление курсора.

select_statement конфликт с курсором запрошенного типа.

READ ONLY

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

cursor_name
Имя Transact-SQL определенного серверного курсора. cursor_name должны соответствовать правилам для идентификаторов.

LOCAL
Указывает, что курсор является локальным по отношению к пакету, хранимой процедуре или триггеру, в котором он был создан. Имя курсора допустимо только внутри этой области. На курсор могут ссылаться локальные переменные пакета, хранимые процедуры, триггеры или выходной параметр хранимой процедуры. Параметр OUTPUT используется для передачи локального курсора вызывающему его пакету, хранимой процедуре или триггеру, который затем может присвоить параметр переменной курсора с целью последующего обращения к курсору после завершения хранимой процедуры. Курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера, за исключением случая, когда курсор был передан параметру OUTPUT. Если курсор был передан параметру OUTPUT, то курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из области видимости.

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

FORWARD_ONLY
Указывает, что курсор может просматриваться только от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT. Если параметр FORWARD_ONLY указан без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC. Если не указаны ни аргумент FORWARD_ONLY, ни аргумент SCROLL, по умолчанию используется аргумент FORWARD_ONLY, если нет ключевых слов STATIC, KEYSET или DYNAMIC. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL. В отличие от таких интерфейсов API баз данных, как ODBC и ADO, режим FORWARD_ONLY поддерживается следующими курсорами языка Transact-SQL: STATIC, KEYSET и DYNAMIC.

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

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

Изменения неключевых значений в базовых таблицах, сделанные владельцем курсора или зафиксированные другими пользователями, отображаются при просмотре курсора владельцем. Изменения, сделанные другими пользователями, не отображаются (изменения не могут быть сделаны с помощью серверного курсора языка Transact-SQL). Если удаляется строка, попытка выборки строк возвращает @@FETCH_STATUS -2. Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями не видна и попытки выборки строки со старыми значениями возвращают @@FETCH_STATUS -2. Обновления видимы сразу, если они сделаны через курсор с помощью предложения WHERE CURRENT OF.

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

FAST_FORWARD
Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.

READ_ONLY
Предотвращает изменения, сделанные через этот курсор. Предложение WHERE CURRENT OF не может иметь ссылку на курсор в инструкции UPDATE или DELETE. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.

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

OPTIMISTIC
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, не будут выполнены, если с момента считывания в курсор строка была обновлена. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого используются сравнения timestamp значения столбца или контрольных сумм, если в таблице нет timestamp столбец, чтобы определить, изменялась ли строка после считывания в курсор. Если строка была изменена, то попытки позиционированного обновления или удаления будут безрезультатными. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.

TYPE_WARNING
Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.

select_statement
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова COMPUTE, COMPUTE BY, FOR BROWSE и INTO недопустимы в select_statement объявление курсора.

SQL Serverнеявно преобразует курсор в другой тип, если предложения в select_statement конфликт с курсором запрошенного типа. Дополнительные сведения см. в разделе «Неявные преобразования курсора».

ДЛЯ обновления ]
Определяет обновляемые столбцы в курсоре. If OF column_name [, ... n ] предоставляется, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов, за исключением случая, когда был указан параметр параллелизма READ_ONLY.

Инструкция DECLARE CURSOR определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор. Инструкция OPEN заполняет результирующий набор, а оператор FETCH возвращает из него строку. Инструкция CLOSE очищает текущий результирующий набор, связанный с курсором. Инструкция DEALLOCATE освобождает ресурсы, используемые курсором.

Первая форма инструкции DECLARE CURSOR использует синтаксис ISO для задания параметров работы курсора. Вторая форма инструкции DECLARE CURSOR использует расширения языка Transact-SQL, позволяющие определять курсоры с помощью таких же типов, как типы, используемые в курсорных функциях API баз данных, таких как ODBC и ADO.

Нельзя смешивать две эти формы. Если указать SCROLL или без УЧЕТА ключевые слова перед ключевым словом CURSOR, нельзя использовать ключевые слова между КУРСОРА, а также для select_statement ключевые слова. При указании ключевые слова между КУРСОРА, а также для select_statement ключевые слова, нельзя указать SCROLL или INSENSITIVE перед ключевым словом CURSOR.

Если при использовании синтаксиса языка Transact-SQL для инструкции DECLARE CURSOR не указываются параметры READ_ONLY, OPTIMISTIC или SCROLL_LOCKS, то принимается следующее значение по умолчанию.

Если инструкция SELECT не поддерживает обновления (или недостаточно разрешений, или при доступе к удаленным таблицам, не поддерживающим обновление, и т. п.), то курсору присваивается параметр READ_ONLY.

Курсоры STATIC и FAST_FORWARD по умолчанию имеют значение READ_ONLY.

Курсоры DYNAMIC и KEYSET по умолчанию имеют значение OPTIMISTIC.

Ссылки на курсоры могут производиться только другими инструкциями языка Transact-SQL. Функции API баз данных не могут ссылаться на курсоры. Например, после объявления курсора функции и методы OLE DB, ODBC или ADO не могут ссылаться на его имя. Строки курсора не могут быть выбраны с помощью соответствующих функций и методов API; для этой цели необходимо использовать инструкции FETCH языка Transact-SQL.

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

Переменные могут использоваться как часть select_statement , в котором объявлен курсор. Значения переменных курсора после его объявления не изменяются.

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

Нельзя использовать курсоры или триггеры в таблице с кластеризованным индексом columnstore. Это ограничение не применяется к некластеризованные индексы; можно использовать курсоры и триггеры в таблице с некластеризованным индексом columnstore.

A. Использование простого курсора и синтаксиса

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

DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;

Б. Использование вложенных курсоров для вывода отчета

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

SET NOCOUNT ON ; DECLARE @vendor_id int , @vendor_name nvarchar (50 ), @message varchar (80 ), @product nvarchar (50 ); PRINT "-------- Vendor Products Report --------"; DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: " + @vendor_name PRINT @message -- Declare an inner cursor based -- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS <> 0 PRINT " <>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END CLOSE vendor_cursor; DEALLOCATE vendor_cursor;

Курсор – это объект, позволяющий по отдельности обрабатывать строки из результирующего набора, возвращенного оператором 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

Курсор - ссылка на контекстную область памяти. В некоторых реализациях языка программирования 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 .

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

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

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

Что такое курсор?

Курсор не может использоваться в MySQL сам по себе. Он является важным компонентом хранимых процедур. Я бы сравнил курсор с «указателем » в C / C + + или итератором в PHP-операторе foreach .

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

Такая операция по обработке записи может быть также исполнена на PHP-уровне, что значительно уменьшает объем передаваемых на PHP-уровень данных, так как мы можем просто вернуть обработанный сводный / статистический результат обратно (тем самым устраняя процесс обработки select – foreach на стороне клиента).

Поскольку курсор реализуется в хранимой процедуре, он имеет все преимущества (и недостатки), присущие ХП (контроль доступа, пре-компиляция, трудность отладки и т.д.)

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

Пример практического применения

На моем персональном сайте есть страница с результатами игр моей любимой команды НБА: Лейкерс .

Структура таблицы этой страницы довольно проста:

Рис 1. Структура таблицы результатов игр Лейкерс

Я заполняю эту таблицу с 2008 года. Некоторые из последних записей с результатами игр Лейкерс в сезоне 2013-14 приведены ниже:

Рис. 2. Данные таблицы результатов игр Лейкерс (частичные) в сезоне 2013-2014

(Я использую MySQL Workbench в качестве GUI-инструмента для управления базой данных MySQL. Вы можете использовать другой инструмент по своему выбору).

Что ж, должен признать, что баскетболисты Лейкерс в последнее время играют не очень здорово. 6 поражений подряд по состоянию на 15 января. Я определил эти «6 поражений подряд », посчитав вручную, сколько матчей подряд, начиная с текущей даты (и вниз к более ранним играм) имеют в колонке winlose значение «L » (поражение).

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

Можем ли мы сделать то же самое с помощью одного оператора SQL? Я не являюсь экспертом SQL, потому не смог придумать, как достичь нужного результата («6 поражений подряд ») через один оператор SQL. Мнения гуру будут для меня очень ценными — оставьте их в комментариях ниже.

Можем ли мы сделать это через PHP? Да, конечно. Мы можем получить данные по играм (конкретно, столбец winlos ) этого сезона и перебрать записи для вычисления длительности текущей серии побед / поражений подряд.

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

Тем не менее, мы не знаем наверняка, сколько записей должно быть извлечено в PHP для определения серии. Так что нам не обойтись без напрасного извлечения ненужных данных. И, наконец, если текущее количество выигрышей /поражений подряд это единственное, что мы хотим узнать из этой таблицы, зачем нам тогда извлекать все строки данных?

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

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

Так как же можно сделать это лучше?

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

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

Давайте создадим в MySQL Workbench первую ХП:

DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1)) BEGIN declare current_win char(1); declare current_streak int; declare current_status char (1); declare cur cursor for select winlose from lakers where year=cur_year and winlose<>"" order by id desc; set current_streak=0; open cur; fetch cur into current_win; set current_streak = current_streak +1; start_loop: loop fetch cur into current_status; if current_status <> current_win then leave start_loop; else set current_streak=current_streak+1; end if; end loop; close cur; select current_streak into longeststreak; select current_win into `status`; END

В этой ХП у нас есть один входящий параметр и два исходящих. Это определяет подпись ХП.

В теле ХП мы также объявили несколько локальных переменных для серии результатов (выигрышей или проигрышей, current_win ), текущей серии и текущего статуса выигрыш /проигрыш конкретного матча:

Эта строка является объявлением курсора. Мы объявили курсор с именем cur и набор данных, связанных с этим курсором, который является статусом победа /поражение для тех матчей (значение столбца winlose может быть либо «W », либо «L », но не пустое) в конкретном году, которые упорядочены по идентификатору id (последние сыгранные игры будут иметь более высокий ID) в порядке убывания.

Хотя это не видно наглядно, но мы можем себе представить, что этот набор данных будет содержать последовательность значений «L » и «W ». На основании данных, приведенных на рисунке 2, она должна быть следующей: «LLLLLLWLL… » (6 значений «L », 1 «W » и т.д.)

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

После того, как первые данные загружены, курсор перемещается к следующей записи. Таким образом, поведение курсора похоже на очередь, перебирающую набор данных по системе FIFO (First In First Out). Это именно то, что нам нужно.

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

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

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

Чтобы проверить работу этой ХП, мы можем написать короткий PHP-скрипт:

exec("call streak(2013, @longeststreak, @status)"); $res=$cn->query("select @longeststreak, @status")->fetchAll(); var_dump($res); //Dump the output here to get a raw view of the output $win=$res["@status"]="L"?"Loss":"Win"; $streak=$res["@longeststreak"]; echo "Lakers is now $streak consecutive $win.n";

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

Вывод набора данных из хранимой процедуры

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

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

YEAR	Win/Lose	Streak
2013	L	6
2012	L	4
2011	L	2

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

Хранимые процедуры MySQL могут возвращать только скалярные значения (целое число, строку, и т.д.), в отличие от операторов select … from … (результаты преобразуются в набор данных). Проблема в том, что таблица, в которой мы хотим получить результаты, в существующей структуре базы данных не существует, она составляется из результатов обработки хранимой процедуры.

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

Сначала мы создадим вторую ХП, код которой показан ниже:

DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`() begin declare cur_year, max_year, min_year int; select max(year), min(year) from lakers into max_year, min_year; DROP TEMPORARY TABLE IF EXISTS yearly_streak; CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1)); set cur_year=max_year; year_loop: loop if cur_year

Несколько существенных замечаний к приведенному выше коду:

1. Мы определяем самый ранний и самый поздний года для выборки из таблицы lakers ;
2. Мы создаем временную таблицу для хранения исходящих данных с необходимой структурой (season, streak, win );
3. В цикле мы сначала выполняем ранее созданную ХП с необходимыми параметрами (call streak(cur_year, @l, @s );), затем захватываем возвращаемые данные и вставляем их во временную таблицу (insert into yearly_streak values (cur_year, @l, @s); );
4. Наконец, мы выбираем из временной таблицы и возвращаем набор данных, после чего делаем некоторую настройку (DROP TEMPORARY TABLE IF EXISTS yearly_streak; ).

Чтобы получить результаты, мы создаем еще один небольшой PHP-скрипт, код которого показан ниже:

query("call yearly_streak")->fetchAll(); foreach ($res as $r) { echo sprintf("In year %d, the longest W/L streaks is %d %sn", $r["season"], $r["streak"], $r["win"]); }

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

Явный курсор представляет собой команду 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.