В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
- Справочник Transact-SQL;
- Основы программирования на T-SQL;
- SQL код – самоучитель по SQL для начинающих программистов;
- Если Вы хотите освоить язык SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL.
- Что такое хранимые процедуры в T-SQL?
- Примеры работы с хранимыми процедурами в Microsoft SQL Server
- Исходные данные для примеров
- Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
- Запуск хранимой процедуры на T-SQL – команда EXECUTE
- Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
- Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT, например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.
Заметка! Назначение хранимых процедур в языке T-SQL (Microsoft SQL Server).
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express. Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
--Инструкция создания таблицы CREATE TABLE TestTable( [ProductId] INT IDENTITY(1,1) NOT NULL, [CategoryId] INT NOT NULL, [ProductName] VARCHAR(100) NOT NULL, [Price] MONEY NULL ) GO -- Инструкция добавления данных INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1, 'Мышь', 100), (1, 'Клавиатура', 200), (2, 'Телефон', 400) GO --Запрос на выборку SELECT * FROM TestTable
Данные есть, теперь давайте переходить к созданию хранимых процедур.
Создание хранимой процедуры на T-SQL – инструкция CREATE PROCEDURE
Хранимые процедуры создаются с помощью инструкции CREATE PROCEDURE, после данной инструкции Вы должны написать название Вашей процедуры, затем в случае необходимости в скобочках определить входные и выходные параметры. После этого Вы пишите ключевое слово AS и открываете блок инструкций ключевым словом BEGIN, закрываете данный блок словом END. Внутри данного блока Вы пишите все инструкции, которые реализуют Ваш алгоритм или какой-то последовательный расчет, иными словами, программируете на T-SQL.
Для примера давайте напишем хранимую процедуру, которая будет добавлять новую запись, т.е. новый товар в нашу тестовую таблицу. Для этого мы определим три входящих параметра: @CategoryId – идентификатор категории товара, @ProductName — наименование товара и @Price – цена товара, данный параметр будет у нас необязательный, т.е. его можно будет не передавать в процедуру (например, мы не знаем еще цену), для этого в его определении мы зададим значение по умолчанию. Эти параметры в теле процедуры, т.е. в блоке BEGIN…END можно использовать, так же как и обычные переменные (как Вы знаете, переменные обозначаются знаком @). В случае если Вам нужно указать выходные параметры, то после названия параметра указывайте ключевое слово OUTPUT (или сокращённо OUT).
В блоке BEGIN…END мы напишем инструкцию добавления данных, а также в завершении процедуры инструкцию SELECT, чтобы хранимая процедура вернула нам табличные данные о товарах в указанной категории с учетом нового, только что добавленного товара. Также в этой хранимой процедуре я добавил обработку входящего параметра, а именно удаление лишних пробелов в начале и в конце текстовой строки с целью исключения ситуаций, когда случайно занесли несколько пробелов.
Вот код данной процедуры (его я также прокомментировал).
--Создаем процедуру CREATE PROCEDURE TestProcedure ( --Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0 ) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Возвращаем данные SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Запуск хранимой процедуры на T-SQL – команда EXECUTE
Запустить хранимую процедуру, как я уже отмечал, можно с помощью команды EXECUTE или EXEC. Входящие параметры передаются в процедуры путем простого их перечисления и указания соответствующих значений после названия процедуры (для выходных параметров также нужно указывать команду OUTPUT). Однако название параметров можно и не указывать, но в этом случае необходимо соблюдать последовательность указания значений, т.е. указывать значения в том порядке, в котором определены входные параметры (это относится и к выходным параметрам).
Параметры, которые имеют значения по умолчанию, можно и не указывать, это так называемые необязательные параметры.
Вот несколько разных, но эквивалентных способов запуска хранимых процедур, в частности нашей тестовой процедуры.
--1. Вызываем процедуру без указания цены EXECUTE TestProcedure @CategoryId = 1, @ProductName = 'Тестовый товар 1' --2. Вызываем процедуру с указанием цены EXEC TestProcedure @CategoryId = 1, @ProductName = 'Тестовый товар 2', @Price = 300 --3. Вызываем процедуру, не указывая название параметров EXEC TestProcedure 1, 'Тестовый товар 3', 400
Изменение хранимой процедуры на T-SQL – инструкция ALTER PROCEDURE
Внести изменения в алгоритм работы процедуры можно с помощью инструкции ALTER PROCEDURE. Иными словами, для того чтобы изменить уже существующую процедуру, Вам достаточно вместо CREATE PROCEDURE написать ALTER PROCEDURE, а все остальное изменять по необходимости.
Допустим, нам необходимо внести изменения в нашу тестовую процедуру, скажем, параметр @Price, т.е. цену, мы сделаем обязательным, для этого уберём значение по умолчанию, а также представим, что у нас пропала необходимость в получении результирующего набора данных, для этого мы просто уберем инструкцию SELECT из хранимой процедуры.
--Изменяем процедуру ALTER PROCEDURE TestProcedure ( --Входящие параметры @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY ) AS BEGIN --Инструкции, реализующие Ваш алгоритм --Обработка входящих параметров --Удаление лишних пробелов в начале и в конце текстовой строки SET @ProductName = LTRIM(RTRIM(@ProductName)); --Добавляем новую запись INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
Удаление хранимой процедуры на T-SQL – инструкция DROP PROCEDURE
В случае необходимости можно удалить хранимую процедуру, это делается с помощью инструкции DROP PROCEDURE.
Например, давайте удалим созданную нами тестовую процедуру.
DROP PROCEDURE TestProcedure
При удалении хранимых процедур стоит помнить о том, что, если на процедуру будут ссылаться другие процедуры или SQL инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
Приглашаю всех желающих пройти мои онлайн-курсы по изучению языка T-SQL – https://self-learning.ru/courses/t-sql
На курсах используется моя авторская последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL. Каждый курс включает огромное количество материалов: видео, текстовый материал, тесты, домашние задания, скрипты, а также сертификат о прохождении.
На курсах Вы можете заниматься в комфортном для себя темпе не выходя из дома в любое удобное для Вас время.
Спасибо, отличная статья! Только в первой части с ошибкой написан INSERT(у вас UNSERT)
Спасибо, исправил!