Оптимизация хранимых процедур в SQL Server. Хранимые процедуры в среде ms sql Server

Определяется понятие хранимых процедур. Приводятся примеры создания, изменения и использования хранимых процедур с параметрами. Дается определение входных и выходных параметров. Приводятся примеры создания и вызова хранимых процедур.

Понятие хранимой процедуры

Хранимые процедуры представляют собой группы связанных между собой операторов SQL, применение которых делает работу программиста более легкой и гибкой, поскольку выполнить хранимую процедуру часто оказывается гораздо проще, чем последовательность отдельных операторов SQL. Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:

• необходимые операторы уже содержатся в базе данных;
• все они прошли этап синтаксического анализа и находятся в исполняемом формате; перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения , выполняет ее оптимизацию и компиляцию;
• хранимые процедуры поддерживают модульное программирование , так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;
• хранимые процедуры могут вызывать другие хранимые процедуры и функции;
• хранимые процедуры могут быть вызваны из прикладных программ других типов;
• как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;
• хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры . Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.

Хранение процедур в том же месте, где они исполняются, обеспечивает уменьшение объема передаваемых по сети данных и повышает общую производительность системы. Применение хранимых процедур упрощает сопровождение программных комплексов и внесение изменений в них. Обычно все ограничения целостности в виде правил и алгоритмов обработки данных реализуются на сервере баз данных и доступны конечному приложению в виде набора хранимых процедур , которые и представляют интерфейс обработки данных. Для обеспечения целостности данных, а также в целях безопасности, приложение обычно не получает прямого доступа к данным – вся работа с ними ведется путем вызова тех или иных хранимых процедур .

Подобный подход делает весьма простой модификацию алгоритмов обработки данных, тотчас же становящихся доступными для всех пользователей сети, и обеспечивает возможность расширения системы без внесения изменений в само приложение: достаточно изменить хранимую процедуру на сервере баз данных. Разработчику не нужно перекомпилировать приложение, создавать его копии, а также инструктировать пользователей о необходимости работы с новой версией. Пользователи вообще могут не подозревать о том, что в систему внесены изменения.

Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Они вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре , разрешая или запрещая ее выполнение . Изменять код хранимой процедуры разрешается только ее владельцу или члену фиксированной роли базы данных. При необходимости можно передать права владения ею от одного пользователя к другому.

Хранимые процедуры в среде MS SQL Server

При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.

Типы хранимых процедур

В SQL Server имеется несколько типов хранимых процедур .

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
• Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

• определение типа создаваемой хранимой процедуры : временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру , назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;
• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
• определение параметров хранимой процедуры . Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;
• разработка кода хранимой процедуры . Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур .

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

<определение_процедуры>::= {CREATE | ALTER } PROC имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Номер в имени – это идентификационный номер хранимой процедуры , однозначно определяющий ее в группе процедур. Для удобства управления процедурами логически однотипные хранимые процедуры можно группировать, присваивая им одинаковые имена, но разные идентификационные номера.

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

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры . Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру . Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра . Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров .

Ключевое слово VARYING применяется совместно с параметром OUTPUT , имеющим тип CURSOR . Оно определяет, что выходным параметром будет результирующее множество.

Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию . Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра .

Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

Ключевое слово AS размещается в начале собственно тела хранимой процедуры , т.е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры . Выход из хранимой процедуры можно осуществить посредством команды RETURN .

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры} [,...n]

Выполнение хранимой процедуры

Для выполнения хранимой процедуры используется команда:

[[ EXEC [ UTE] имя_процедуры [;номер] [[@имя_параметра=]{значение | @имя_переменной} |][,...n]

Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров , которые были объявлены при создании процедуры с ключевым словом OUTPUT .

Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT , то будет использовано значение по умолчанию . Естественно, указанное слово DEFAULT разрешается только для тех параметров , для которых определено значение по умолчанию .

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры . Присвоить параметру значение по умолчанию , просто пропустив его при перечислении нельзя. Если же требуется опустить параметры , для которых определено значение по умолчанию , достаточно явного указания имен параметров при вызове хранимой процедуры . Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.

Отметим, что при вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени параметра . Их комбинирование не допускается.

Пример 12.1. Процедура без параметров . Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым.

CREATE PROC my_proc1 AS SELECT Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия FROM Клиент INNER JOIN (Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента WHERE Клиент.Фамилия=’Иванов’ Пример 12.1. Процедура для получения названий и стоимости товаров, приобретенных Ивановым.

Для обращения к процедуре можно использовать команды:

EXEC my_proc1 или my_proc1

Процедура возвращает набор данных.

Пример 12.2. Процедура без параметров . Создать процедуру для уменьшения цены товара первого сорта на 10%.

Для обращения к процедуре можно использовать команды:

EXEC my_proc2 или my_proc2

Процедура не возвращает никаких данных.

Пример 12.3. Процедура с входным параметром . Создать процедуру для получения названий и стоимости товаров, которые приобрел заданный клиент.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Товар.Название, Товар.Цена*Сделка.Количество AS Стоимость, Клиент.Фамилия FROM Клиент INNER JOIN (Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара) ON Клиент.КодКлиента=Сделка.КодКлиента WHERE Клиент.Фамилия=@k Пример 12.3. Процедура для получения названий и стоимости товаров, которые приобрел заданный клиент.

Для обращения к процедуре можно использовать команды:

EXEC my_proc3 "Иванов" или my_proc3 @k="Иванов"

Пример 12.4. . Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

Для обращения к процедуре можно использовать команды:

EXEC my_proc4 "Вафли",0.05 или EXEC my_proc4 @t="Вафли", @p=0.05

Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

CREATE PROC my_proc5 @t VARCHAR(20)=’Конфеты`, @p FLOAT=0.1 AS UPDATE Товар SET Цена=Цена*(1-@p) WHERE Тип=@t Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

Для обращения к процедуре можно использовать команды:

EXEC my_proc5 "Вафли",0.05 или EXEC my_proc5 @t="Вафли", @p=0.05 или EXEC my_proc5 @p=0.05

В этом случае уменьшается цена конфет (значение типа не указано при вызове процедуры и берется по умолчанию).

В последнем случае оба параметра (и тип, и проценты) не указаны при вызове процедуры, их значения берутся по умолчанию.

Пример 12.6. Процедура с входными и выходными параметрами . Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Товар.Цена*Сделка.Количество) FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Month(Сделка.Дата) HAVING Month(Сделка.Дата)=@m Пример 12.6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.

Для обращения к процедуре можно использовать команды:

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Этот блок команд позволяет определить стоимость товаров, проданных в январе (входной параметр месяц указан равным 1).

Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Сначала разработаем процедуру для определения фирмы, где работает сотрудник.

Пример 12.7. Использование вложенных процедур . Создать процедуру для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Затем создадим процедуру, подсчитывающую общее количество товара, который закуплен интересующей нас фирмой.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Сделка.Количество) FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента GROUP BY Клиент.Фирма HAVING Клиент.Фирма=@firm Пример 12.7. Создание процедуры для определения общего количества товаров, приобретенных фирмой, в которой работает заданный сотрудник.

Вызов процедуры осуществляется с помощью команды:

DECLARE @k INT EXEC my_proc8 ‘Иванов’,@k OUTPUT SELECT @k

В 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 инструкции, после ее удаления они будут завершаться с ошибкой, так как процедуры, на которую они ссылаются, больше нет.

У меня все, надеюсь, материал был Вам интересен и полезен, пока!

Хранимая процедура (англ. stored procedure) – это именованный программный объект БД. В SQL Server есть хранимые процедуры нескольких типов.

Системные хранимые процедуры (англ. system stored procedure) поставляются разработчиками СУБД и используются для выполнения действий с системным каталогом или получения системной информации. Их названия обычно начинаются с префикса "sp_". Запускаются хранимые процедуры всех типов с помощью команды EXECUTE, которую можно сократить до ЕХЕС. Например, хранимая процедура sp_helplogins, запущенная без параметров, формирует два отчета об именах учетных записей (англ. logins) и соответствующих им в каждой БД пользователях (англ. users).

EXEC sp_helplogins;

Чтобы дать представление о действиях, выполняемых с помощью системных хранимых процедур, в табл. 10.6 приведены некоторые примеры. Всего же системных хранимых процедур в SQL Server более тысячи.

Таблица 10.6

Примеры системных хранимых процедур SQL Server

Пользователю доступно создание хранимых процедур в пользовательских БД и в БД для временных объектов. В последнем случае хранимая процедура будет являться временной. Так же как в случае с временными таблицами, название временной хранимой процедуры должно начинаться с префикса "#", если это локальная временная хранимая процедура, или с "##" – если глобальная. Локальная временная процедура может использоваться только в рамках соединения, в котором ее создали, глобальная – и в рамках других соединений.

Программируемые объекты SQL Server могут создаваться как с использованием средств Transact-SQL, так и с помощью сборок (англ. assembly) в среде CRL (Common Language Runtime) платформы Microsoft.Net Framework . В данном учебнике будет рассматриваться только первый способ.

Для создания хранимых процедур используется оператор CREATE PROCEDURE (можно сократить до PROC), формат которого приведен ниже:

CREATE {PROC I PROCEDURE) proc_name [ ; number ]

[{gparameter data_type }

[“default] |

[ WITH [ ,...n ] ]

[ FOR REPLICATION ]

AS {[ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

Если хранимая процедура (или триггер, функция, представление) создается с опцией ENCRYPTION, ее код преобразуется таким образом, что текст становится нечитаемым. В то же время, как отмечается в , используемый алгоритм перенесен из ранних версий SQL Server и не может рассматриваться в качестве надежного алгоритма защиты – существуют утилиты, позволяющие быстро выполнить обратное преобразование.

Опция RECOMPILE указывает на то, что при каждом вызове процедуры система будет перекомпилировать текст. В обычном случае скомпилированная при первом запуске процедура сохраняется в кэше, что позволяет увеличить быстродействие.

EXECUTE AS определяет контекст безопасности, в котором должна быть выполнена процедура. Далее указывается одно из значений f CALLER | SELF | OWNER | " user_name"). CALLER является значением по умолчанию и означает, что код будет выполняться в контексте безопасности пользователя, вызывающего этот модуль. Соответственно, пользователь должен иметь разрешения не только на сам программируемый объект, но и на другие затрагиваемые им объекты БД. EXECUTE AS SELF означает использование контекста пользователя, создающего или изменяющего программируемый объект. OWNER указывает, что код будет выполняться в контексте текущего владельца процедуры. Если для нее не определен владелец, то подразумевается владелец схемы, к которой она относится. EXECUTE AS "user_name" позволяет явно указать имя пользователя (в одинарных кавычках).

Для процедуры могут указываться параметры. Это локальные переменные, используемые для передачи значений в процедуру. Если параметр объявлен с ключевым словом OUTPUT (или сокращенно OUT), он является выходным: заданное ему в процедуре значение после ее окончания может быть использовано вызвавшей процедуру программой. Ключевое слово READONLY означает, что значение параметра не может быть изменено внутри хранимой процедуры.

Параметрам могут быть назначены значения но умолчанию, которые будут использованы, если при вызове процедуры значение параметра не будет указано в явном виде. Рассмотрим пример:

CREATE PROC surma (@а int, @b int=0,

©result int OUTPUT) AS

SET @result=0a+0b

Мы создали процедуру с тремя параметрами, причем у параметра @b значение по умолчанию =0, а параметр @result – выходной: через него возвращается значение в вызвавшую программу. Выполняемые действия достаточно просты – выходной параметр получает значение суммы двух входных.

При работе в SQL Server Management Studio созданную хранимую процедуру можно найти в разделе программируемых объектов БД (англ. Programmability) в подразделе для хранимых процедур (рис. 10.2).

При вызове процедуры в качестве входных параметров можно использовать как переменные, так и константы. Рассмотрим два примера. В первом входные параметры процедуры явно заданы константами, для выходного параметра в вызове указано ключевое слово OUTPUT. Во втором варианте в качестве первого входного параметра используется значение переменной, а для второго параметра с помощью ключевого слова DEFAULT указано, что должно быть использовано значение по умолчанию:

Рис. 10.2.

DECLARE @с int;

EXEC summa 10,5,@c OUTPUT;

PRINT 0c; – будет выведено 15

DECLARE Gi int = 5;

– при вызове используем значение по умолчанию

EXEC summa Gi,DEFAULT , 0с OUTPUT;

PRINT 0c; – будет выведено 5

Рассмотрим теперь пример с анализом кода возврата, с которым заканчивается процедура. Пусть надо подсчитать, сколько в таблице Bookl книг, изданных в заданном диапазоне лет. При этом если начальный год оказался больше конечного, процедура возвращает "1" и подсчет не проводит, иначе – считаем количество книг и возвращаем 0:

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear) ;

Рассмотрим вариант вызова данной процедуры, в котором код возврата сохраняется в целочисленной переменной 0ret, после чего анализируется его значение (в данном случае это будет 1). Используемая в операторе PRINT функция CAST служит для преобразования значения целочисленной переменной Gres к строковому типу:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Начальный год больше конечного"

PRINT "Число книг "+ CAST(Gres as varchar(20))

Хранимые процедуры могут не только считывать данные из таблицы, но и изменять данные и даже создавать таблицы и ряд других объектов БД.

Однако создавать схемы, функции, триггеры, процедуры и представления из хранимой процедуры нельзя.

Следующий пример иллюстрирует как эти возможности, так и вопросы, связанные с областью видимости временных объектов. Приведенная ниже хранимая процедура проверяет наличие временной таблицы #ТаЬ2; если этой таблицы нет, то создает ее. После этого в таблицу #ТаЬ2 заносятся значения двух столбцов, и содержимое таблицы выводится оператором SELECT:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 –№1

Перед первым вызовом хранимой процедуры создадим используемую в ней временную таблицу #ТаЬ2. Обратите внимание на оператор ЕХЕС. В предыдущих примерах параметры передавались в процедуру "по позиции", а в данном случае используется другой формат передачи параметров – "по имени", явно указывается имя параметра и его значение:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

В приведенном примере оператор SELECT отработает дважды: первый раз – внутри процедуры, второй раз – из вызывающего фрагмента кода (отмечен комментарием "№ 2").

Перед вторым вызовом процедуры удалим временную таблицу #ТаЬ2. Тогда одноименная временная таблица будет создана из хранимой процедуры:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

В этом случае данные выведет только оператор SELECT, находящийся внутри процедуры (с комментарием "Ха 1"). Выполнение SELECT "№ 2" приведет к ошибке, так как созданная в хранимой процедуре временная таблица на момент возврата из процедуры будет уже удалена из базы tempdb.

Удалить хранимую процедуру можно с помощью оператора DROP PROCEDURE. Его формат представлен ниже. Одним оператором можно удалить несколько хранимых процедур, перечислив их через запятую:

DROP (PROC I PROCEDURE) { procedure } [

Например, удалим ранее созданную процедуру summa:

DROP PROC summa;

Внести изменения в существующую процедуру (а фактически – переопределить ее) можно с помощью оператора ALTER PROCEDURE (допу

стимо сокращение PROC). За исключением ключевого слова ALTER, формат оператора практически совпадает с форматом CREATE PROCEDURE. Например, изменим процедуру dbo. rownum, установив ей опцию выполнения в контексте безопасности владельца:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner – устанавливаемая опция

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

В некоторых случаях может возникнуть необходимость в динамическом формировании команды и выполнении ее на сервере БД. Эта задача также может решаться с помощью оператора ЕХЕС. В приведенном ниже примере выполняется выборка записей из таблицы Bookl по условию равенства атрибута Year значению, задаваемому с помощью переменной:

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

Выполнение динамически сформированных инструкций создает предпосылки для реализации компьютерных атак типа "SQL-инъекция" (англ. SQL injection). Суть атаки заключается в том, что нарушитель внедряет в динамически формируемый запрос собственный код на SQL. Обычно это происходит, когда подставляемые параметры берут из результатов ввода данных пользователем.

Несколько изменим предыдущий пример:

DECLARE 0у varchar(100);

SET 0у="2ООО"; – это мы получили от пользователя

Если предположить, что присваиваемое в операторе SET строковое значение мы получили от пользователя (неважно каким образом, например, через веб-приложение), то пример иллюстрирует "штатное" поведение нашего кода.

DECLARE 0у varchar(100);

SET 0у="2000; DELETE FROM dbo.Book2"; – инъекция

EXEC ("SELECT * FROM dbo.Book2 WHERE ="+0y);

В рекомендуется по возможности использовать в подобных случаях системную хранимую процедуру sp_executcsql, которая позволяет контролировать тип параметров, что является одним из барьеров на пути SQL- инъекций. Не рассматривая в подробностях ее формат, разберем пример, аналогичный представленному ранее:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

Здесь явно указывается тип используемого в запросе параметра, и SQL Server при выполнении будет его контролировать. Буква "N" перед кавычками указывает, что это литерная константа в формате Unicode, как того требует процедура. Параметру можно присвоить не только постоянное значение, но и значение другой переменной.

хранимой процедуры возможен, только если он осуществляется в контексте той базы данных , где находится процедура.

Типы хранимых процедур

В SQL Server имеется несколько типов хранимых процедур .

• Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_ , хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.
• Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры – полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.
• Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа # . Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ## . Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

• определение типа создаваемой хранимой процедуры : временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру , назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;
• планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;
• определение параметров хранимой процедуры . Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами ;
• разработка кода хранимой процедуры . Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур .

Создание новой и изменение имеющейся хранимой процедуры осуществляется с помощью следующей команды:

<определение_процедуры>::= {CREATE | ALTER } имя_процедуры [;номер] [{@имя_параметра тип_данных } [=default] ][,...n] AS sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_ , # , ## , создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

Номер в имени – это идентификационный номер хранимой процедуры , однозначно определяющий ее в группе процедур. Для удобства управления процедурами логически однотипные хранимые процедуры можно группировать, присваивая им одинаковые имена, но разные идентификационные номера.

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

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры , годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры , т.е. с указанием ключевого слова OUTPUT .

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры . Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру . Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра . Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров .

Ключевое слово VARYING применяется совместно с

1. Включай в свои процедуры строку - SET NOCOUNT ON: С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей. Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Здесь код процедуры
SELECT column1 FROM dbo.TblTable1
--Перключение SET NOCOUNT в исходное состояние
SET NOCOUNT OFF;
GO

2. Используй имя схемы с именем объекта: Ну тут думаю понятно. Данная операция подсказывает серверу где искать объекты и вместо того чтобы беспорядочно шарится по своим закромам, он сразу будет знать куда ему нужно пойти и что взять. При большом колличестве баз, таблиц и хранимых процедур может значительно сэкономить наше время и нервы.

SELECT * FROM dbo.MyTable --Вот так делать хорошо
-- Вместо
SELECT * FROM MyTable --А так делать плохо
--Вызов процедуры
EXEC dbo.MyProc --Опять же хорошо
--Вместо
EXEC MyProc --Плохо!

3. Не используй префикс «sp_» в имени своих хранимых процедур: Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

4. Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *): Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")

5. Используй TRY-Catch для отлова ошибок: До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server"ом появился более правильный и удобный способ решения этой проблемы:

BEGIN TRY
--код
END TRY
BEGIN CATCH
--код отлова ошибки
END CATCH

Заключение
В принципе на сегодня у меня всё. Еще раз повторюсь, что здесь лишь те приёмы, которые использовал лично я в своей практике, и могу ручаться за их эффективность.

P.S.
Мой первый пост, не судите строго.