четверг, 27 января 2011 г.

Основы SQL – DML – UPDATE

Продолжаю серию статей “Основы SQL”. В предыдущих сериях:
В прошлый раз я немного рассказал про оператор INSERT (куда же без него – без первоначальных данных применять остальные операторы DML не имеет смысла).
Сейчас речь пойдет про UPDATE – оператор, позволяющий модифицировать существующие данные.

Модификация одной строки

Если для оператора INSERT существует разный синтаксис для вставки одной строки и множества строк, то для UPDATE синтаксис одинаковый и в простейшем случае выглядит так:
UPDATE Persons SET LastName = 'Петров' WHERE PersonID = 1
В данном случае, мы знаем значение первичного ключа той строки, которую мы хотим изменить. Как вы помните, первичный ключ – это столбец (или набор столбцов), идентифицирующих каждую строку в таблице. По этой причине обновляется только одна строка.

Модификация нескольких строк происходит, когда мы делаем фильтр не по первичному/уникальному ключу, или по ключу, но по нескольким значениям. К этой теме я вернусь позже.

В принципе, скрипт выше повторно запускаемый. Но когда лишний раз обновлять значение в поле нежелательно (например, из-за триггера) можно добавить дополнительную проверку (только помните, что не стоит плодить обращения к данным без надобности). Можно сделать так:
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 'Ошибка.'
Выполнив этот скрипт, вы увидите “Ошибка.” за счет того, что записи с идентификатором 3 не существует. Кстати, если выполняете запрос в SQL Management Studio, не забудьте посмотреть вкладку Messages в результатах – PRINT, как и количество обработанных строк выводятся туда.

Чтобы получить ошибку, можно попробовать нарушить целостность внешнего ключа:
UPDATE Phones SET PersonID = 3
IF @@ERROR != 0 OR @@ROWCOUNT != 1
    PRINT 'Ошибка.'
Правда, в этом случае, MS SQL Server сначала сам скажет нам, что о нас думает, и гораздо более развернуто :)

Но, если мы захотим проверить @@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
Почему бы не присвоить значения @error и @rowcount последовательно? Правильно – потому что первое присвоение обнулит @@ROWCOUNT.

Блокировки

Блокировки обновлений предназначены для обеспечения корректной параллельной работы пользователей. Их, как правило, разделяют на три типа:
  • Пессимистические – обычно реализуются средствами СУБД – блокируются таблица или строки, чтобы параллельно работающий пользователь не нарушил целостность данных, пока их не обновит тот, кто первый запросил блокировку.

    При использовании пессимистических блокировок нужно заботиться о том, чтобы они как можно меньше зависели от действий пользователя. Чтобы не было, к примеру, ситуации, когда пользователь открыл форму, начал ее заполнять, а потом ушел на обед, оставив остальных ждать, пока он не вернется и не доделает свою работу, освободив заблокированный ресурс.
  • Оптимистические – обычно реализуются разработчиком для того, чтобы обнаружить, что обновляемые данные изменились. СУБД может помочь ему в этом, предоставив возможность автоматически обновлять “номер версии” строки.

    Здесь пользователь, открывший форму и ушедший на обед, уже не подложит “свинью” другим. Однако он может быть несколько расстроен тем, что, потратив потом время на внесение данных и попытавшись их сохранить, увидит сообщение “Эти данные уже изменил другой пользователь.”. Правда, в более продвинутых системах есть вариант, когда ему покажут изменения и предложат все равно сохранить/сделать merge.
  • Логические – реализуются разработчиком. Это некий компромисс между двумя первыми вариантами. Как правило, когда пользователь начинает работу с критическими данными, они блокируются (но не средствами СУБД, а с помощью специально подготовленных для этого таблиц) на некоторое время.

    Если, к примеру, наш пользователь пообедает быстрее, чем пройдет это время – у него есть шанс спокойно закончить начатое. Если же нет – приложение извинится и предложит ему обедать быстрее :)
Тема пессимистических блокировок слишком обширна и на ней я здесь и сейчас останавливаться не буду. Логические блокировки имеют слабое отношение к SQL – это скорее некоторая надстройка над ним.

Об оптимистических блокировках скажу чуть больше. Используется ли столбец 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)
Теперь можно увеличить значение на 1 и получить его в рамках одного оператора:
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
  • Рефакторинг с учетом данных
  • Подробнее о запросах
  • Управляем доступом и транзакциями
Если у кого-нибудь есть пожелания или даже новые темы к статьям из этой серии – пишите в комментариях. Постараюсь принять к сведению.

Комментариев нет:

Отправить комментарий