Использование курсоров и циклов в Transact-SQL. Курсоры в хранимых процедурах MySQL Курсоры в sql server

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

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

Курсор создается командой DECLARE. Курсор открывается командой OPEN.

Операции с курсором производятся при помощи команды FETCH. Курсор закрывается командой CLOSE.

В команде DECLARE CURSOR указывается инструкция SELECT. Каждую строку, возвращаемую инструкцией SELECT, можно извлекать и обрабатывать индивидуально. В следующем примере для Oracle курсор объявляется в блоке объявлений вместе с несколькими другими переменными. После этого в последующем блоке BEGIN…END курсор открывается, по нему производится выборка, и курсор закрывается.

CURSOR title_price_cursor IS SELECT title, price FROM titles

WHERE price IS NOT NULL; title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);

OPEN title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

(title_price_val.title, new_price) CLOSE title_price_cursor; END;

Поскольку в этом примере используется PL/SQL, большую часть кода мы в этой книге разъяснять не будем. Однако в блоке DECLARE ясно видно объявление курсора. В исполняемом блоке PL/SQL курсор инициализируется командой OPEN, значения извлекаются командой FETCH и, наконец, курсор закрывается командой CLOSE.

Инструкция SELECT- это основа курсора, так что хорошей практикой является ее тщательное тестирование перед включением в инструкцию DECLARE CURSOR. Инструкция SELECT может работать с базовой таблицей или представлением. Поэтому курсоры «только для чтения» могут работать с необновляемыми представлениями. Инструкция SELECT может содержать такие предложения, как ORDER BY, GROUP BY и HAVING, если эти предложения не обновляют исходную таблицу. Если курсор определен как FOR UPDATE, то рекомендуется удалять такие предложения из инструкции SELECT.

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

В следующем простом примере для DB2 мы объявим курсор, который просматривает номера департаментов, названия департаментов и номера менеджеров в admin_group "ХО1".

DECLARE dept_cursor CURSOR

FOR SELECT dept_nbr, dept_name, mgr_nbr

WHERE admin_group="X01"

ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

В следующем примере для Microsoft SQL Server объявляется и открывается курсор для таблицы publishers. Курсор отбирает из таблицы publishers первую запись, соответствующую инструкции SELECT, и вставляет ее в другую таблицу. Затем он переходит к следующей записи, потом к следующей - до тех пор, пока все записи не будут обработаны. И наконец, курсор закрывается и высвобождает память (команда DEALLOCATE используется только в Microsoft SQL Server).

DECLARE @publisher_name VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"

FETCH NEXT FROM pub_cursor INTO publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO foreign_publishers VALUES(«j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

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

ПРИМЕНЯЕТСЯ К: 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;

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

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

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

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

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

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

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

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

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

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

Declare cursor_name cursor for select_statement ]}]

В данном выражении объявляется курсор 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 for SELECT_оператор ]]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Освобождение памяти, 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;

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

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

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

Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Если текущий оператор – запрос к базе данных, в памяти сохраняется и строка данных запроса, называемая текущим значением, или текущей строкой курсора . Указанная область в памяти поименована и доступна для прикладных программ.

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

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

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

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

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

Реализация курсоров в среде MS SQL Server

SQL Server поддерживает три вида курсоров :

• курсоры SQL применяются в основном внутри триггеров, хранимых процедур и сценариев;
• курсоры сервера действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB, DB_Library;
• курсоры клиента реализуются на самом клиенте. Они выбирают весь результирующий набор строк из сервера и сохраняют его локально, что позволяет ускорить операции обработки данных за счет снижения потерь времени на выполнение сетевых операций.

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

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

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

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

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

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

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

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

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

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

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

Управление курсором в среде MS SQL Server

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

• DECLARE – создание или объявление курсора ;
• OPEN – открытие курсора , т.е. наполнение его данными;
• FETCH – выборка из курсора и изменение строк данных с помощью курсора;
• CLOSE – закрытие курсора ;
• DEALLOCATE – освобождение курсора , т.е. удаление курсора как объекта.

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

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

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

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

SELECT-оператор задает тело запроса SELECT , с помощью которого определяется результирующий набор строк курсора .

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

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

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

<создание_курсора>::= DECLARE имя_курсора CURSOR FOR SELECT_оператор ]]

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

Если указано ключевое слово GLOBAL , создается глобальный курсор ; он существует до закрытия текущего соединения.

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

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

При указании STATIC создается статический курсор .

При указании KEYSET создается ключевой курсор.

При указании DYNAMIC создается динамический курсор .

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

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

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

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

Для открытия курсора и наполнения его данными из указанного при создании курсора запроса SELECT используется следующая команда:

После открытия курсора происходит выполнение связанного с ним оператора SELECT , выходные данные которого сохраняются в многоуровневой памяти.

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

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

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

При указании LAST возвращается самая последняя строка курсора . Она же становится текущей строкой.

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

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

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

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

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

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

Изменение и удаление данных

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

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

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

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

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

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

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

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

Для контроля достижения конца курсора рекомендуется применять функцию: @@FETCH_STATUS

Функция @@FETCH_STATUS возвращает:

0 , если выборка завершилась успешно;

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

2 , если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.

DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT " Список покупок" DECLARE klient_cursor CURSOR LOCAL FOR SELECT КодКлиента, Фирма, Фамилия FROM Клиент WHERE Город="Москва" ORDER BY Фирма, Фамилия OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Клиент "+@fam+ " Фирма "+ @firm PRINT @message SELECT @message="Наименование товара Дата покупки Стоимость" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Товар.Название, Сделка.Дата, Товар.Цена*Сделка.Количество AS Стоимость FROM Товар INNER JOIN Сделка ON Товар. КодТовара=Сделка.КодТовара WHERE Сделка.КодКлиента=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT " Нет покупок" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Общая стоимость "+ CAST(@s AS CHAR(6)) PRINT @message -- переход к следующему клиенту-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.

Пример 13.7. Разработать прокручиваемый курсор для клиентов из Москвы. Если номер телефона начинается на 1, удалить клиента с таким номером и в первой записи курсора заменить первую цифру в номере телефона на 4.

DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT " Список клиентов" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Фирма, Фамилия, Телефон FROM Клиент WHERE Город="Москва" ORDER BY Фирма, Фамилия FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Клиент "+@fam+ " Фирма "+@firm " Телефон "+ @tel PRINT @message -- если номер телефона начинается на 1, -- удалить клиента с таким номером IF @tel LIKE ‘1%’ DELETE Клиент WHERE CURRENT OF klient_cursor ELSE -- переход к следующему клиенту FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- в первой записи заменить первую цифру в -- номере телефона на 4 UPDATE Клиент SET Телефон=’4’ + RIGHT(@tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Клиент "+@fam+" Фирма "+ @firm " Телефон "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Пример 13.7. Прокручиваемый курсор для клиентов из Москвы.

Пример 13.8. Использование курсора как выходного параметра процедуры . Процедура возвращает набор данных – список товаров.

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

DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n END CLOSE @my_cur DEALLOCATE @my_cur