Уроки sql server создание хранимых процедур. Синтаксис объявления переменной выглядит так
При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.
Типы хранимых процедур
В SQL Server имеется несколько типов хранимых процедур.
Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.
Триггеры
Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.
Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.
Триггеры – особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.
Триггер представляет собой специальный тип хранимых процедур, запускаемых сервером автоматически при попытке изменения данных в таблицах, с которыми триггеры связаны. Каждый Триггер привязывается к конкретной таблице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности данных происходит откат этой транзакции. Тем самым внесение изменений запрещается. Отменяются также все изменения, уже сделанные триггером.
Создает триггер только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.
Триггер представляет собой весьма полезное и в то же время опасное средство. Так, при неправильной логике его работы можно легко уничтожить целую базу данных, поэтому триггеры необходимо очень тщательно отлаживать.
В отличие от обычной подпрограммы, триггер выполняется неявно в каждом случае возникновения триггерного события, к тому же он не имеет аргументов. Приведение его в действие иногда называют запуском триггера. С помощью триггеров достигаются следующие цели:
проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;
выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;
накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;
поддержка репликации.
Основной формат команды CREATE TRIGGER показан ниже:
<Определение_триггера>::=
CREATE TRIGGER имя_триггера
BEFORE | AFTER <триггерное_событие>
ON <имя_таблицы>
<список_старых_или_новых_псевдонимов>]
<тело_триггера>
Триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (Триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).
Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).
Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.
Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположенным на удаленном сервере.
Рассматривается ситуация, когда хранимые процедуры могут ухудшить производительность запросов.
При компиляции хранимых процедур в MS SQL Server 2000 хранимые процедуры помещаются в процедурный кэш, что может способствовать увеличению производительности при их выполнении за счет исключения необходимости в синтаксическом разборе, оптимизации и компиляции кода хранимых процедур.
С другой стороны в хранении откомпилированного кода хранимой процедуры кроются подводные камни, которые могут иметь обратный эффект.
Дело в том, что при компиляции хранимой процедуры компилируется план выполнения тех операторов, которые составляют код процедуры, соответственно, если откомпилированная хранимая процедура кэшируется, то кэшируется и ее план выполнения, а следовательно, хранимая процедура не будет оптимизироваться под конкретную ситуацию и параметры запросов.
Проведет небольшой эксперимент, чтобы продемонстрировать это.
ШАГ 1
. Создание БД.
Для эксперимента создадим отдельную базу данных.
CREATE DATABASE test_sp_perf
ON (NAME="test_data", FILENAME="c:\temp\test_data", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
LOG ON (NAME="test_log", FILENAME="c:\temp\test_log", SIZE=1, MAXSIZE=10,FILEGROWTH=1Mb)
ШАГ 2.
Создание таблицы.
CREATE TABLE sp_perf_test(column1 int, column2 char(5000))
ШАГ 3. Заполнение таблицы тестовыми строками. В таблицу намеренно добавляются дублирующиеся строки. 10000 строк с номерами от 1 до 10000, и 10000 строк с номерами 50000.
DECLARE @i int
SET @i=1
WHILE(@i<10000)
BEGIN
INSERT INTO sp_perf_test(column1, column2) VALUES(@i,"Test string #"+CAST(@i as char(8)))
INSERT INTO sp_perf_test(column1, column2) VALUES(50000,"Test string #"+CAST(@i as char(8)))
SET @i= @i+1
END
SELECT COUNT(*) FROM sp_perf_test
GO
ШАГ 4. Создание некластерного индекса. Поскольку план выполнения кэшируется вместе с процедурой, индекс будет при всех вызовах использоваться одинаково.
CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test(column1)
GO
ШАГ 5. Создание хранимой процедуры. Процедура просто выполняет оператор SELECT с условием.
CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE column1=@param
GO
ШАГ 6. Запуск хранимой процедуры. При запуске ранимой процедуры специально используется селективный параметр. В результате выполнения процедуры получаем 1 строку. План выполнения показывает на использование некластерного индекса, т.к. запрос селективный и это наилучший способ извлечь строку. Процедура, оптимизированная на выборку одной строки, сохраняется в процедурном кэше.
EXEC proc1 1234
GO
ШАГ 7. Запуск хранимой процедуры с неселективным параметром. В качестве параметра используется значение 50000. Строк с таким значением первого столбца около 10000, соответственно, пользоваться некластерным индексом и операцией bookmark lookup неэффективно, но поскольку откомпилированный код с планом выполнения храниться в процедурном кэше, именно он и будет использоваться. План выполнения показывает это, а так же то, что для 9999 строк выполнялась операция bookmark lookup.
EXEC proc1 50000
GO
ШАГ 8. Выполнение выборки строк с первым полем равным 50000. При выполнении отдельного запроса выполняется оптимизация и компиляция запроса с конкретным значением первого столбца. В результате оптимизатор запросов определяет, что поле много раз дублируется и принимает решение использовать операцию table scan, что в данном случае намного эффективнее, чем использование некластерного индекса.
SELECT column1, column2 FROM sp_perf_test WHERE column1=50000
GO
Таким образом, можно заключить, что использование хранимых процедур не всегда может повысить производительность запросов. Следует крайне внимательно подходить к тем хранимым процедурам, которые работают с результатами с переменным количеством строк и использующие различные планы выполнения.
Можете использовать скрипт для повторения эксперимента на свеем MS SQL сервере.
Объявление процедуры
CREATE PROCEDURE [({IN|OUT|INOUT } [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC ]
END
Ключевые слова
. IN (Input) – входной параметр
. OUT (Output) – выходной параметр
. INOUT – входной и выходной, а также поле (без параметров)
. DYNAMIC RESULT SET показывает, что процедура может открыть указанное число курсоров, которые останутся открытыми после возврата из процедуры
Примечания
Не рекомендуется использовать много параметров в хранимых процедурах (в первую очередь больших чисел и символьных строк) из-за перегрузки сети и стека. На практике в существующих диалектах Transact-SQL, PL/SQL и Informix наблюдается существенное отличие от стандарта, как в объявлении и использовании параметров, объявлении переменных, так и в вызове подпрограмм. Microsoft рекомендует применять следующую аппроксимацию для оценки размера КЭШа хранимых процедур:
=(максимальное количество одновременно работающих пользователей)*(размер самого большого плана выполнения)*1.25. Определение размера плана выполнения в страницах можно сделать с помощью команды: DBCC MEMUSAGE.
Вызов процедуры
Во многих существующих СУБД вызов хранимых процедур выполняется с помощью оператора:
EXECUTE PROCEDURE [(][)]
Примечание : вызов хранимых процедур может быть сделан из приложения, другой хранимой процедуры или в интерактивном режиме.
Пример объявления процедуры
CREATE PROCEDURE Proc1 AS //объявляем процедуру
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //объявляем курсор
OPEN Cur1 //открываем курсор
FETCH NEXT FROM Cur1 //считываем данные из курсора
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //закрываем курсор
DEALLOCATE Cur1
EXECUTE Proc1 //запускаем процедуру
Полиморфизм
Две подпрограммы с одним и тем же именем могут быть созданы в одной и той же схеме, если параметры этих двух подпрограмм являются в такое мере отличными друг от друга, чтобы их можно было различать. Для того, чтобы различать две подпрограммы с одним и тем же именем в одной схеме, каждой из них дается альтернативное и уникальное имя (specific name). Такое имя может быть явно указано, когда определяется подпрограмма. При вызове подпрограмм при наличии нескольких одинаковых имен определение нужной подпрограммы осуществляется в несколько шагов:
. Первоначально определяются все процедуры с указанным именем, а если таковых нет, то все функции с заданным именем.
. Для дальнейшего анализа оставляются только те подпрограммы, по отношению к которым данный пользователь обладает привилегией на исполнение (EXECUTE).
. Для них отбираются те, у которых число параметров соответствует числу аргументов вызова. Проверяются указанные типы данных у параметров и их позиции.
. Если осталось более одной подпрограммы, то выбирается та, квалификационное имя которой короче.
На практике в Oracle полиморфизм поддерживается для функций, объявленных только в пакете, DB@ — в разных схема, а в Sybase и MS SQL Server перегрузка запрещена.
Удаление и изменение процедур
Для удаления процедуры используется оператор:
Для изменения процедуры используется оператор:
ALTER PROCEDURE [([{IN|OUT|INOUT }])]
BEGIN [ATOMIC ]
END
Привилегии на выполнение процедур
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION ]
Системные процедуры
Многие СУБД (включая SQL Server) имеют определенный набор встроенных системных хранимых процедур, которые можно использовать в своих целях.
В Microsoft SQL Server для реализации и автоматизации своих собственных алгоритмов (расчётов ) можно использовать хранимые процедуры, поэтому сегодня мы с Вами поговорим о том, как они создаются, изменяются и удаляются.
Но сначала немного теории, чтобы Вы понимали, что такое хранимые процедуры и для чего они нужны в T-SQL.
Примечание! Начинающим программистам рекомендую следующие полезные материалы на тему T-SQL:
- Для более подробного изучения языка T-SQL также рекомендую почитать книгу - Путь программиста T-SQL. Самоучитель по языку Transact-SQL .
Что такое хранимые процедуры в T-SQL?
Хранимые процедуры – это объекты базы данных, в которых заложен алгоритм в виде набора SQL инструкций. Иными словами, можно сказать, что хранимые процедуры – это программы внутри базы данных. Хранимые процедуры используются для сохранения на сервере повторно используемого кода, например, Вы написали некий алгоритм, последовательный расчет или многошаговую SQL инструкцию, и чтобы каждый раз не выполнять все инструкции, входящие в данный алгоритм, Вы можете оформить его в виде хранимой процедуры. При этом, когда Вы создаете процедуру SQL, сервер компилирует код, а потом, при каждом запуске этой процедуры SQL сервер уже не будет повторно его компилировать.
Для того чтобы запустить хранимую процедуру в SQL Server, необходимо перед ее названием написать команду EXECUTE, также возможно сокращенное написание данной команды EXEC. Вызвать хранимую процедуру в инструкции SELECT , например, как функцию уже не получится, т.е. процедуры запускаются отдельно.
В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: UNSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).
Хранимая процедура может иметь входные параметры и выходные параметры, она может возвращать табличные данные, может не возвращать ничего, только выполнять заложенные в ней инструкции.
Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен ), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL » представлен пример реализации данной возможности в виде хранимой процедуры.
Примеры работы с хранимыми процедурами в Microsoft SQL Server
Исходные данные для примеров
Все примеры ниже будут выполнены в Microsoft SQL Server 2016 Express . Для того чтобы продемонстрировать, как работают хранимые процедуры с реальными данными, нам нужны эти данные, давайте их создадим. Например, давайте создадим тестовую таблицу и добавим в нее несколько записей, допустим, что это будет таблица, содержащая список товаров с их ценой.
Инструкция создания таблицы CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, 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 инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.
У меня все, надеюсь, материал был Вам интересен и полезен, пока!
Включай в свои процедуры строку - SET NOCOUNT ON:
С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.
Transact-SQL
Используй имя схемы с именем объекта:
Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.
Transact-SQL
SELECT * FROM dbo.MyTable --Вот так делать хорошо -- Вместо SELECT * FROM MyTable --А так делать плохо --Вызов процедуры EXEC dbo.MyProc --Опять же хорошо --Вместо EXEC MyProc --Плохо!
Не используй префикс «sp_» в имени своих хранимых процедур:
Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.
Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):
Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже.