Операторы update, delete и insert

Кроме операторов SELECT, предложения WHERE могут быть и в операторах UPDATE, DELETE и INSERT. А в этих предложениях, в свою очередь, могут быть такие же подзапросы, как и в предложениях WHERE, используемых в операторе SELECT.

Например, Zetec только что заключила с Olympic Sales соглашение о партнерстве, согласно которому Zetec "задним числом" предоставляет Olympic Sales десятипроцентную скидку на весь прошлый месяц. Информацию об этой скидке можно ввести в базу данных, используя оператор UPDATE:

UPDATE TRANSMASTERSET NET_AMOUNT = NET_AMOUNT * 0.9WHERE CUSTID =(SELECT CUSTIDFROM CUSTOMERWHERE COMPANY = 'Olympic Sales')

В операторе UPDATE можно также использовать и коррелированный подзапрос. Предположим, что в таблице CUSTOMER имеется столбец LAST_MONTHS_MAX (максимум за последние месяцы), а руководство Zetec хочет предоставить скидку для всех сделок, которые превышают значение LAST_MONTHS_MAX данного клиента:

UPDATE TRANSMASTER ТМSET NET__AMOUNT = NET_AMOUNT * 0.9WHERE NET_AMOUNT >(SELECT LAST_MONTHS_MAXFROM CUSTOMER СWHERE C.CUSTID = TM.CUSTID);

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

Подзапрос в операторе UPDATE может обращаться и к обновляемой таблице. Предположим, что руководство Zetec хочет дать десятипроцентную скидку покупателям, купившим товаров на сумму более 10 000 долларов:

UPDATE TRANSMASTER TM1SET NET_AMOUNT = NET_AMOUNT * 0.9WHERE 10000 < (SELECT SUM(NET_AMOUNT)FROM TRANSMASTER TM2WHERE TM1.CUSTID = TM2.CUSTID);

Во внутреннем подзапросе для всех строк таблицы TRANSMASTER, которые относятся к одному и тому же покупателю, вычисляется (с помощью функции SUM) сумма значений из столбца NET_AMOUNT. Что это означает? Предположим, что в таблице TRANSMASTER к покупателю со значением CUSTID, равным 37, относятся четыре строки, в которых столбец NET_ AMOUNT имеет такие значения: 3000, 5000, 2000 и 1000. Для этого значения CUSTID сумма значений NET_AMOUNT равна 11000.

Обратите внимание, что порядок, в котором оператор UPDATE обрабатывает строки, определяется конкретной реализацией и обычно является непредсказуемым. Этот порядок может зависеть от того, каким образом строки хранятся на диске. Предположим, что в имеющейся реализации для значения столбца CUSTID, равного 37, строки таблицы TRANSMASTER обрабатываются в следующем порядке. Первой – строка со значением NET_AMOUNT, равным 3000, затем – с NET_AMOUNT, равным 5000, и т.д. После обновления первых трех строк со значением CUSTID, равным 37, у них в столбце NET_AMOUNT будут такие значения: 2700 (90% от 3000), 4500 (90% от 5000) и 1800 (90% от 2000). А затем, когда в TRANSMASTER идет обработка последней строки, в которой значение CUSTID равно 37, a NET_AMOUNT равно 1000, то значение функции SUM, возвращенное подзапросом, должно быть равно 10000.

Это значение получается как сумма новых значений NET_AMOUNT из первых трех строк со значением CUSTID, равным 37, а также старого значения из последней строки, имеющей то же значение CUSTID. Таким образом, может показаться, что последняя строка для значния CUSTID, равного 37, не должна обновляться – ведь сравнение с этим значением SUM не будет истинным (10000 не меньше SELECT SUM(NET_AMOUNT)). Но при обращении подзапроса к обновляемой таблице оператор UPDATE работает уже по-другому. В этом операторе при всех проверках подзапросов используются старые значения обновляемой таблицы. В предыдущем операторе UPDATE для столбца CUSTID, равного 37, подзапрос возвращает 11000, т.е. первоначальное значение SUM.

Подзапрос в предложении WHERE работает точно так же, как оператор SELECT или UPDATE. To же самое верно для DELETE или INSERT. Чтобы удалить записи обо всех сделках Olympic Sales, используйте такой оператор:

DELETE TRANSMASTERWHERE CUSTID =(SELECT CUSTIDFROM CUSTOMERWHERE COMPANY = 'Olympic Sales');

Как и в случае с UPDATE, подзапросы DELETE также могут быть коррелированными и также могут обращаться к изменяемой таблице (у которой в данном случае удаляют строки). Здесь действуют правила, похожие на те, что используются для подзапросов оператора UPDATE. Предположим, вы хотите удалить из таблицы CUSTOMER все строки тех пользователей, для которых итог NET_AMOUNT больше 10000 долларов:

DELETE TRANSMASTER TM1WHERE 10000 < (SELECT SUM(NET_AMOUNT))FROM TRANSMASTER TM2WHERE TM1.CUSTID = TM2.CUSTID);

Этот запрос удаляет из таблицы TRANSMASTER все строки, в которых столбец CUSTID содержит 37, а также строки, относящиеся к другим пользователям, сумма покупок которых превышает 10000 долларов. Все обращения к TRANSMASTER, имеющиеся в подзапросе, указывают на содержимое этой таблицы, которое было перед любыми удалениями, уже выполненными текущим оператором. Поэтому даже при удалении из таблицы TRANSMASTER последней строки, в которой значение столбца CUSTID равно 37, подзапрос все равно выполняется на этой таблице таким образом, как если бы не было никаких удалений. В итоге подзапрос возвращает значение 11000.

При обновлении, удалении или вставке записей базы данных есть риск сделать так, что данные в изменяемой таблице не будут соответствовать данным в других таблицах из этой базы. Такое несоответствие называется аномалией изменения (см. главу 5). Если из таблицы TRANSMASTER удаляются записи, а от нее зависит другая таблица, TRANSDETAIL (подробности сделок), то записи, соответствующие удаленным записям из первой таблицы, необходимо удалять и из второй. Эта операция называется каскадным удалением, поскольку удаление родительской записи должно вызывать каскад удалений связанных с ней дочерних записей. В противном случае неудаленные дочерние записи становятся "записями-призраками".

В операторе INSERT может находиться предложение SELECT. Такие операторы применяются для заполнения таблиц с текущей информацией. Ниже приведен запрос для создания таблицы с содержимым TRANSMASTER за 27 октября.

CREATE TABLE TRANSMASTER_1027(TRANSID INTEGER, TRANSDATE DATE,…);INSERT INTO TRANSMASTER_1027(SELECT * FROM TRANSMASTERWHERE TRANSDATE = 2000-10-27);

Если требуется информация лишь о крупных сделках, то запрос будет таким:

INSERT INTO TRANSMASTER_102 7(SELECT * FROM TRANSMASTER TMWHERE TM.NET_AMOUNT > 10000AND TRANSDATE 2000-10-27);

 

Рекурсивные запросы

В этой главе…

· Управление рекурсией

· Как определять рекурсивные запросы

· Способы применения рекурсивных запросов

SQL-92 и более ранние версии часто критиковали за отсутствие реализации рекурсивной обработки. Многие важные задачи, которые трудно решить другими средствами, легко решаются с помощью рекурсии. В SQL: 1999 появились расширения, позволяющие создавать рекурсивные запросы. Благодаря этим расширениям мощь языка SQL существенно возрастает. Если ваша реализация SQL включает в себя расширения для рекурсии, то вы можете эффективно решать новый большой класс задач. Впрочем, в основной части стандарта SQL:2003 поддержка рекурсии не предусмотрена. Поэтому во многих используемых реализациях этой поддержки может и не быть.

Что такое рекурсия

Это довольно старая возможность таких языков программирования, как Logo, LISP и C++. В этих языках можно определить функцию (совокупность одной или множества команд), которая выполняет заданную операцию. Главная программа вызывает функцию, выполняя для этого команду, которая называется вызовом функции. В процессе своей работы функция вызывает сама себя – это самая простая форма рекурсии.

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

В ее состав входят три функции.

· Функция line(n) чертит отрезок длины n.

· Функция Jeft_turn(d) поворачивает "чертежный инструмент" на d градусов против часовой стрелки.

· Функция spiral(segment), которая определяется следующим образом:

· void spiral(int segment)· {· line(segment);· left_turn(90);· spiral(seement + 1);· }

Если из главной программы вызвать spiral(1), то будут выполняться такие действия:

· spiral(1) чертит единичный отрезок (т.е. единичной длины), направленный вверх;

· spiral(1) выполняет поворот на 90 градусов против часовой стрелки;

· spiral(1) вызывает spiral(2);

· spiral(2) чертит отрезок, равный по длине двум единичным и направленный влево;

· spiral(2) выполняет поворот на 90 градусов против часовой стрелки;

· spiral(2) вызывает spiral(3);

· и т.д.

Постепенно благодаря программе появляется спиральная кривая, изображенная на рис. 12.1.

Рис. 12.1. Результат вызова spiral(1)

Маленькие трудности

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

Сбой недопустим

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

void spiral2(int segment){if (segment <= 10){line(segment);left_turn(90),spiral2(segment+ 1);}}

При вызове программа spiral2(l) выполняется и затем рекурсивно вызывает сама себя до тех пор, пока значение segment не превысит 10. Как только значение segment станет равным 11, конструкция if (segment <= 10) возвратит значение False, и код, находящийся во внутренних скобках, будет пропущен. Управление снова передается предыдущему вызову spiral2(l), а оттуда постепенно возвращается к самому первому вызову, после которого программа завершается.

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

Рекурсия – это мощный инструмент для повторного выполнения кода. Она идеально подходит для поиска в древовидных структурах, например, в файловых системах, сложных электронных схемах или многоуровневых распределенных сетях.