Сегодня мы с Вами рассмотрим пример реализации достаточно простой задачи, но которая все равно у начинающих программистов SQL вызывает некоторые вопросы, а именно как написать процедуру, которая при выполнении может изменять данные как по условию, т.е. отобранные данные, так и по всем данным.
На этом сайте мы неоднократно затрагивали тему процедур реализованных на Transact-SQL, например, в материалах: Использование курсоров и циклов в Transact-SQL, Журналирование изменений данных в таблице на Transact-SQL. Поэтому заострять внимание на основах мы не будем, так как данный материал ориентирован на начинающих программистов, которые уже умеют писать простенькие процедуры, функции, табличные функции, но им еще не хватает опыта и знаний в реализации своей какой-то задачи. Поэтому сейчас мы рассмотрим одну небольшую хитрость, назовем ее так, которая, я надеюсь, облегчит Вашу работу по написанию процедур.
Итак, приступим, и начнем мы с рассмотрения условия задания.
Задание для написания процедуры на Transact-SQL
Очень часто программистам, которые работают в организации и сопровождают какое-то приложение, которое работает в связке SQL сервером, приходиться писать различные запросы на обновление, удаление, вставку данных в таблицу. И, конечно же, самые часто требуемые операции они выносят в процедуры (если нет, то советую всем так делать), для того чтобы их просто вызвать и все. Но бывают случаи когда, например, необходимо обновить данные не все, а только для какой-то отдельной записи, и естественно на ум приходит написать процедуру с параметрами, для того чтобы передать в нее нужное значение, которое в дальнейшем в процедуре будет использоваться как условие. И, как обычно делают начинающие программисты, пишут отдельную процедуры для обновления всех данных, и отдельную процедуру (с параметром) для обновления конкретной записи. Но согласитесь, в данном случае происходит дублирование кода, т.е. практически одинаковые процедуры (код в этих процедурах идентичен, за исключением условия) разделены на две процедуры, не проще бы сделать одну, универсальную процедуру, которая могла работать, как с массовом обновлением данных, так и по условию. Вот как раз этим мы сейчас и займемся, за основу возьмем конструкцию update, т.е. мы будем именно обновлять данные в тестовой таблице.
Примечание! Пример будем писать на СУБД MS SQL 2008 с использованием Management Studio. Кстати, не давно мы рассматривали установку MS SQL Server Express и Management Studio Express.
Написание универсальной процедуры на Transact-SQL
Допустим, у нас есть таблица test_table
CREATE TABLE [dbo].[test_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[column1] [float] NULL,
[column2] [float] NULL
) ON [PRIMARY]
GO
Со следующими тестовыми данными
Теперь допустим, что все эти данные нам раз в месяц необходимо обновлять на значение 1 (для простого примера) или в случае необходимости только у какой-то одной записи в течение месяца. Поэтому мы пишем процедуру с одним параметром, а для того чтобы обновлять все записи в условии мы будем использовать всем известную встроенную функции COALESCE, ну хватит, теории все сами увидите на примере
CREATE PROCEDURE test_proc
(@id INT)
AS
BEGIN
UPDATE test_table SET column1 = 1, column2 = 1
WHERE id = COALESCE(@id, id)
END
GO
И что здесь происходит, как видите, мы принимаем один параметр, пускаем запрос update, и ставим условие, в котором указываем что если наш входящий параметр пустой, то обновляем все, т.е. функция coalesce проверяет первый параметр @id, и если он пуст, то подставляет просто id, для примера id=id или 1=1, соответственно, под это условие попадают все записи в нашей таблице, а если нет, то только ту запись, которая указанна во входящем параметре.
Вот с помощью такой простой операции мы сделали универсальную процедуру, надеюсь, объяснять что из себя представляет функция coalesce не стоит, а если в вкратце, то она просто анализирует все свои входящие параметры на наличие значения null т.е. пустоту, и анализ начинает с первого параметра и так далее, и когда доходит до параметра со значением не null, то возвращает его, если все значение null, то и вернет null.
Теперь осталось проверить работу нашей процедуры, для этого вызываем ее, допустим, с параметром 2, т.е. обновить значения только для записи с идентификатором 2, и сразу для проверки пустим запрос select
EXEC dbo.test_proc 2 SELECT * FROM test_table
Как видите, все отработало нормально, обновилась только одна запись, которую мы и указывали, теперь запустим со значением NULL
EXEC dbo.test_proc null SELECT * FROM test_table
А в этом случае обновились все записи, так как мы передали параметр со значением null.
Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.
Вот такой простой совет, который, надеюсь, Вам в чем-нибудь да поможет. Удачи!
