Модифицирование курсоров

Курсоры могут также быть использованы, чтобы выбирать группу строк из таблицы, которые могут быть затем модифицированы или удалены одна за другой. Это дает вам возможность обходить некоторые ограничения предикатов, используемых в командах UPDATE и DELETE. Вы можете ссылаться на таблицу, задействованную в предикате запроса курсора или любом из его подзапросов, которые вы не можете выполнить в предикатах самих этих команд. Как подчеркнуто в Главе 16, стандарт SQL отклоняет попытку удалить всех пользователей с рейтингом ниже среднего, в следующей форме:

EXEC SQL DELETE FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);

Однако, вы можете получить тот же эффект, используя запрос для выбора соответствующих строк, запомнив их в курсоре, и выполнив DELETE с использованием курсора. Сначала вы должны объявить курсор:

EXEC SQL DECLARE Belowavg CURSOR FOR
SELECT *
FROM Customers
WHERE rating < (SELECT AVG (rating)
FROM Customers);

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

EXEC SQL WHENEVER SQLERROR GOTO Error_handler;
EXEC SQL OPEN CURSOR Belowavg;
while not SOLCODE = 100 do
begin
EXEC SOL FETCH Belowavg INTO:a,:b,:c,:d,:e;
EXEC SOL DELETE FROM Customers
WHERE CURRENT OF Belowavg;
end;
EXEC SOL CLOSE CURSOR Belowavg;

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

Курсор должен также быть модифицируемым. Являясь модифицируемым, курсор должен удовлетворять тем же условиям что и представления (см. Главу 21). Кроме того, ORDER BY и UNION, которые не разрешены в представлениях, в курсорах — разрешаются, но предохраняют курсор от модифицируемости. Обратите внимание в вышеупомянутом примере, что мы должны выбирать строки из курсора в набор переменных, даже если мы не собирались использовать эти переменные. Этого требует синтаксис команды FETCH. UPDATE работает так же.

Вы можете увеличить значение комиссионных всем продавцам, которые имеют заказчиков с оценкой=300, следующим способом. Сначала вы объявляете курсор:

EXEC SOL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300);

Затем вы выполняете модификации в цикле:

EXEC SQL OPEN CURSOR High_cust;
while SQLCODE = 0 do
begin
EXEC SOL FETCH High_cust
INTO:id_num,:salesperson,:loc,:comm;
EXEC SQL UPDATE Salespeople
SET comm = comm +.01
WHERE CURRENT OF High_cust;
end;
EXEC SQL CLOSE CURSOR High_cust;

Обратите внимание: что некоторые реализации требуют, чтобы вы указывали в определении курсора, что курсор будет использоваться для выполнения команды UPDATE на определенных столбцах. Это делается с помощью заключительной фразы определения курсора — FOR UPDATE <column list>. Чтобы объявить курсор High_cust таким способом, так чтобы вы могли модифицировать командой UPDATE столбец comm, вы должны ввести следующее предложение:

EXEC SQL DECLARE CURSOR High_Cust AS
SELECT *
FROM Salespeople
WHERE snum IN (SELECT snum
FROM Customers
WHERE rating = 300)
FOR UPDATE OF comm;

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