В прошлый раз я немного рассказал про оператор INSERT (куда же без него – без первоначальных данных применять остальные операторы DML не имеет смысла).
Сейчас речь пойдет про UPDATE – оператор, позволяющий модифицировать существующие данные.
Модификация одной строки
Если для оператора INSERT существует разный синтаксис для вставки одной строки и множества строк, то для UPDATE синтаксис одинаковый и в простейшем случае выглядит так:В данном случае, мы знаем значение первичного ключа той строки, которую мы хотим изменить. Как вы помните, первичный ключ – это столбец (или набор столбцов), идентифицирующих каждую строку в таблице. По этой причине обновляется только одна строка.
Модификация нескольких строк происходит, когда мы делаем фильтр не по первичному/уникальному ключу, или по ключу, но по нескольким значениям. К этой теме я вернусь позже.
В принципе, скрипт выше повторно запускаемый. Но когда лишний раз обновлять значение в поле нежелательно (например, из-за триггера) можно добавить дополнительную проверку (только помните, что не стоит плодить обращения к данным без надобности). Можно сделать так:
UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1 AND LastName != 'Петров'
IF NOT EXISTS(SELECT * FROM Persons WHERE PersonID = 1 AND LastName = 'Петров') UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1
Обработка ошибок
Обработка ошибок применима к любым операторам (и не только DML). Начиная с версии MS SQL 2005 появилась возможность использовать оператор TRY…CATCH. Познакомиться с его описанием можно по ссылке выше, а я остановлюсь на более “древнем” варианте обработки ошибок.Все-таки, с одной стороны, СУБД – это, на мой взгляд, очень редко изменяемое ПО в инфраструктуре большинства компаний. А с другой стороны – хочется заранее рассказать о стандартной ошибке, встречающейся при использовании старого способа (а он вам вполне может встретиться, особенно при доработке существующих БД).
Насколько я помню, ошибки обрабатывались с помощью конструкции @@ERROR, как минимум, в MS SQL Server 6.5. Вряд ли вам придется столкнуться с более старым вариантом, поэтому исследования на тему предыдущих версий не проводил. В @@ERROR содержится код ошибки (подробный список можно посмотреть в MSDN) от последней выполненной инструкции SQL или ноль, в случае отсутствия ошибки. Здесь-то и разложены грабли :)
Под инструкцией понимается любая инструкция. Поэтому, если вы сделаете UPDATE, потом проверите @@ERROR на ноль, и внутри блока IF захотите получить код ошибки, то получите ноль – “IF @@ERROR != 0” – это тоже инструкция и она выполнилась успешно!
Поэтому, если нужно использовать значение @@ERROR далее, необходимо сразу после проверяемой инструкции сохранить ее в переменную. Такой подход, к примеру, используется, чтобы обработать одновременно и @@ERROR и @@ROWCOUNT (с помощью нее можно получить число строк, обработанных последней выполненной инструкцией, в общем, с ней та же песня).
Допустим, мы хотим проверить, что UPDATE прошел без ошибок и затронул только одну строку:
UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 3 IF @@ERROR != 0 OR @@ROWCOUNT != 1 PRINT 'Ошибка.'
Чтобы получить ошибку, можно попробовать нарушить целостность внешнего ключа:
UPDATE Phones SET PersonID = 3 IF @@ERROR != 0 OR @@ROWCOUNT != 1 PRINT 'Ошибка.'
Но, если мы захотим проверить @@ERROR и @@ROWCOUNT в разных блоках IF без дополнительной переменной, мы получим некорректное поведение:
UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1 IF @@ERROR = 0 BEGIN IF @@ROWCOUNT != 1 PRINT 'Ошибка.' END
DECLARE @error INT, @rowcount INT UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1 SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT IF @error = 0 BEGIN IF @rowcount != 1 PRINT 'Ошибка.' END
Блокировки
Блокировки обновлений предназначены для обеспечения корректной параллельной работы пользователей. Их, как правило, разделяют на три типа:- Пессимистические – обычно реализуются средствами СУБД – блокируются таблица или строки, чтобы параллельно работающий пользователь не нарушил целостность данных, пока их не обновит тот, кто первый запросил блокировку.
При использовании пессимистических блокировок нужно заботиться о том, чтобы они как можно меньше зависели от действий пользователя. Чтобы не было, к примеру, ситуации, когда пользователь открыл форму, начал ее заполнять, а потом ушел на обед, оставив остальных ждать, пока он не вернется и не доделает свою работу, освободив заблокированный ресурс. - Оптимистические – обычно реализуются разработчиком для того, чтобы обнаружить, что обновляемые данные изменились. СУБД может помочь ему в этом, предоставив возможность автоматически обновлять “номер версии” строки.
Здесь пользователь, открывший форму и ушедший на обед, уже не подложит “свинью” другим. Однако он может быть несколько расстроен тем, что, потратив потом время на внесение данных и попытавшись их сохранить, увидит сообщение “Эти данные уже изменил другой пользователь.”. Правда, в более продвинутых системах есть вариант, когда ему покажут изменения и предложат все равно сохранить/сделать merge. - Логические – реализуются разработчиком. Это некий компромисс между двумя первыми вариантами. Как правило, когда пользователь начинает работу с критическими данными, они блокируются (но не средствами СУБД, а с помощью специально подготовленных для этого таблиц) на некоторое время.
Если, к примеру, наш пользователь пообедает быстрее, чем пройдет это время – у него есть шанс спокойно закончить начатое. Если же нет – приложение извинится и предложит ему обедать быстрее :)
Об оптимистических блокировках скажу чуть больше. Используется ли столбец rowversion или проверка по всем полям, в любом случае проверяется, что обновление (или удаление) одной записи затронуло ровно одну запись. Сделать это можно с помощью рассмотренной выше конструкции @@ROWCOUNT. Упрощенный пример выглядит так:
UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1 AND LastName = 'Иванов' IF @@ROWCOUNT = 0 PRINT 'Ошибка оптимистической блокировки.'
Оптимистические блокировки обычно реализуются на уровне приложения и в .NET, кстати, есть поддержка оптимистических блокировок на уровне фрэймворка, так что если она устраивает – можно ее и использовать.
Модификация нескольких строк
Как уже говорилось ранее, модификация нескольких строк аналогична модификации одной строки но с другим фильтром или вообще без оного. К ней точно также применимы обработка ошибок и получение количества задействованных в обновлении строк.Важно: не забывайте о проверке корректности фильтра, перед запуском обновления или удаления нескольких строк! С фильтром по первичному ключу такая ошибка тоже может быть, но обычно встречается значительно реже.
Поэтому, особенно если вам срочно нужно что-то поменять в БД, которая запущена в эксплуатацию, полезно бывает открыть транзакцию, проверить, что после обновления с данными все в порядке, а потом уже зафиксировать ее (подробнее о транзакциях расскажу в следующих статьях).
Знаете ли вы, что?
Оператор UPDATE можно использовать для получения данных. Это, быть может, не очень уместно в рамках серии “Основы SQL”, однако больно уж факт интересный.Приведу пример, который не очень жизненный, но хорошо иллюстрирует принцип. Сначала добавим к таблице Persons столбец Number с нулевым значением по умолчанию:
IF COLUMNPROPERTY(OBJECT_ID('dbo.Persons'), 'Number', 'ColumnId') IS NULL ALTER TABLE dbo.Persons ADD Number INT NOT NULL CONSTRAINT DF_Persons_Number DEFAULT(0)
DECLARE @lastNumber INT UPDATE Persons SET @lastNumber = Number = Number + 1 WHERE PersonID = 1 SELECT @lastNumber
За кадром
Поскольку серия статей называется “Основы SQL” а не “Пересказ MSDN”, то, разумеется, полностью синтаксис UPDATE не раскрыт. За подробностями можно обратиться к статье MSDN про UPDATE (там довольно много расширений).Немного рассказал про блокировки вообще, но почти ничего не рассказал про блокировки в MS SQL Server. Если придумаю, как рассказать это простым и понятным языком – напишу.
Анонс
В следующих сериях:- DML - DELETE
- DML - SELECT
- Рефакторинг с учетом данных
- Подробнее о запросах
- Управляем доступом и транзакциями
Комментариев нет:
Отправить комментарий