Курсоры могут также быть использованы, чтобы выбирать группу строк из таблицы, которые могут быть затем модифицированы или удалены одна за другой. Это дает вам возможность, обходить некоторые ограничения предикатов, используемых в командах 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 . Для объявления курсора 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;
Это обеспечит вас определенной защитой от случайных модификаций, которые могут разрушить весь порядок в базе данных.