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

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

DECLARE <имя курсора> [{SENSITIVE|INSENSITIVE|ASENSITIVE}] [[NO] SCROLL] CURSOR [{WITH|WITHOUT} HOLD] FOR <запрос> [FOR {READ ONLY|UPDATE [OF <список столбцов>]}]

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

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

DECLARE <имя курсора> CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] FOR <запрос> [FOR UPDATE [OF <список столбцов>]]

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

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

OPEN CURSOR <имя курсора>

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

FETCH [{NEXT|PRIOR|FIRST|LAST|{ABSOLUTE|RELATIVE <значение>}}]
FROM <имя курсора> INTO <список переменных>

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

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

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

CLOSE CURSOR <имя курсора>

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

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

exec sql declare cursor Cur1 for select * from Customer
where Rating < (select avg(Rating) from Customer); - Определяем курсор exec sql whenever not found goto not_done; - Определяем условие выхода exec sql open cursor Cur1; - Выполняем курсор for (;;) { - Создаем цикл для удаления всех покупателей, выбранных с помощью курсора exec sql fetch Cur1 into :a, :b, :c, :d, :e; // В SQL Server: fetch next from Cur1 into @f1,@f2 < обработка переменных a, b, c, d, e >
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; } – Данные для удаления взять из курсора
not_done:
exec sql close cursor Cur1; — Закрываем курсор
exit();

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

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

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

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

Комментарии запрещены.