Как изменить хранимую процедуру ms sql

Данный материал является переводом оригинальной статьи «MSSQLTips : Rick Dobson : Create, Alter, Drop and Execute SQL Server Stored Procedures».

У начинающих осваивать SQL Server могут присутствовать навыки проектирования и ручного запуска сценариев T-SQL, однако не все начинающие DBA понимают, как упаковать свои сценарии T-SQL для удобного повторного использования. В этой статье мы приведём примеры, иллюстрирующие основы создания, изменения и запуска хранимых процедур, чтобы упростить повторное использование кода T-SQL. Кроме этого, мы кратко опишем использование входных и выходных параметров, а также значений кодов возврата, связанных с хранимыми процедурами.

Обзор хранимых процедур SQL Server

Хранимая процедура — это сохраненный блок кода T-SQL, например запрос для вывода списка строк в таблице. Блок кода T-SQL можно сохранить в файле сценария T-SQL. Вы также можете сохранить код из файла сценария в хранимой процедуре.

Сохранение кода в хранимой процедуре, а не в файле сценария дает несколько преимуществ. Вот несколько примеров:

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

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

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

После создания или изменения хранимой процедуры, содержащей один или несколько операторов SELECT, вы можете вызвать хранимую процедуру с помощью оператора EXEC. Следовательно, вы можете думать о хранимой процедуре как о контейнере, который облегчает повторное использование в нем кода T-SQL.

Создание новой хранимой процедуры SQL Server

Многие администраторы баз данных знакомы с созданием таблицы с помощью оператора CREATE TABLE. Точно так же администраторы-разработчики могут создать хранимую процедуру с помощью оператора CREATE PROC или CREATE PROCEDURE. Так же, как оператор CREATE TABLE добавляет таблицу в базу данных, оператор CREATE PROC добавляет хранимую процедуру в базу данных. Итак, вам нужно начать с новой или существующей базы данных, когда вы хотите создать хранимую процедуру, поскольку хранимая процедура фактически хранится в базе данных.

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

Следующий сценарий создает базу данных с именем CodeModuleTypes. Его первый оператор определяет главную базу данных как базу данных по умолчанию. Его второй оператор создает базу данных. В зависимости от ваших потребностей могут быть гораздо более сложные версии оператора CREATE DATABASE. Если не указано иное, простой оператор создания БД, подобный приведенному ниже, будет использовать настройки по умолчанию из БД model, которая является одной из стандартных баз данных, устанавливаемых вместе с SQL Server.

use master;
GO
create database CodeModuleTypes;

После того, как у вас появилась база данных, такая как CodeModuleTypes, вы можете вызвать оператор CREATE PROC в этой базе данных.

Следующий сценарий демонстрирует синтаксис, который можно использовать для создания вашей первой хранимой процедуры. Хранимая процедура в приведенном ниже коде отображает набор результатов со всеми столбцами для каждой строки из таблицы Employee в схеме HumanResources демонстрационной базы данных AdventureWorks2014. Ранее мы описали, как загрузить копию этой демонстрационной БД.

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

Приведенный ниже оператор CREATE PROC состоит из трех частей.

• Оператор CREATE PROC называет хранимую процедуру (и её схему, если вы явно указываете её).
• Ключевое слово as действует как маркер, обозначающий, что код определения хранимой процедуры вот-вот начнется.
• Код T-SQL, определяющий работу хранимой процедуры. В этом примере определяющим кодом является оператор SELECT для таблицы Employee в схеме HumanResources базы данных AdventureWorks2014.

use CodeModuleTypes;
go   create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee;

После создания хранимой процедуры ее можно запустить с помощью оператора EXEC, подобно примеру, приведенному ниже. Именно этот оператор возвращает набор результатов со всеми столбцами для каждой строки из таблицы Employee.

exec dbo.uspMyFirstStoredProcedure

Вот отрывок из вывода, созданного предыдущим скриптом.

• На панели результатов показаны первые одиннадцать столбцов из первых семнадцати строк с данными о 290 сотрудниках компании AdventureWorks.
• Если вы хотите обработать строки, отображаемые оператором SELECT в хранимой процедуре, вам нужно будет сохранить строки набора результатов в какой-либо другой таблице или объекте SQL Server. Затем обработайте результаты в этом объекте.

Удаление хранимой процедуры SQL Server

Предыдущий сценарий создания хранимой процедуры завершится ошибкой, если хранимая процедура uspMyFirstStoredProcedure в схеме dbo уже существует. Одним из способов решения этой проблемы является удаление предыдущей версии хранимой процедуры, а затем повторный запуск сценария для создания новой версии хранимой процедуры. Вы можете удалить предыдущую версию хранимой процедуры с помощью оператора DROP PROC или DROP PROCEDURE.

drop proc dbo.uspMyFirstStoredProcedure

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

-- conditionally drop a stored proc
if object_id('dbo.uspMyFirstStoredProcedure') is not null 
     drop proc dbo.uspMyFirstStoredProcedure
go

Изменение существующей хранимой процедуры SQL Server

Следующий блок кода демонстрирует оператор ALTER PROC. Этот оператор отличается от оператора CREATE PROC тем, что он может работать только с существующей хранимой процедурой.

Этот сценарий предназначен для запуска сразу после предыдущего сценария, удаляющего uspMyFirstStoredProcedure, если он уже существует.

Первые два оператора в следующем блоке кода — это операторы CREATE PROC и EXEC, которые могут создавать новую копию хранимой процедуры и запускать uspMyFirstStoredProcedure. Поскольку весь код представлен в виде одного блока, требуются три ключевых слова GO, которые не потребовались бы, если бы весь блок кода был сегментирован на четыре отдельных блока кода:

• Начальный оператор CREATE PROC должен сопровождаться ключевым словом GO, чтобы оператор CREATE PROC завершился перед первым оператором EXEC.
• Затем начальный оператор EXEC должен сопровождаться ключевым словом GO, чтобы оператор ALTER PROC был первым оператором в его пакете.
• Наконец, за оператором ALTER PROC должно следовать ключевое слово GO, чтобы оператор ALTER PROC завершился до последнего оператора EXEC.

Оператор ALTER PROC состоит из трех частей:

• Имя объекта после ALTER PROC должно совпадать с именем существующей хранимой процедуры, которую вы хотите изменить.
• Ключевое слово as действует как разделитель, отделяющий объявления ALTER PROC от нового кода T-SQL, определяющего измененную версию хранимой процедуры.
• Новый код внутри оператора ALTER PROC соединяет таблицу Person из схемы Person с таблицей Employee из схемы HumanResources в базе данных AdventureWorks2014.

-- create a new stored proc
create proc dbo.uspMyFirstStoredProcedure
as
select * 
from AdventureWorks2014.HumanResources.Employee
go   -- run stored proc
exec dbo.uspMyFirstStoredProcedure
go   -- alter stored proc
alter proc dbo.uspMyFirstStoredProcedure
as
select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
go   -- run altered stored proc
exec dbo.uspMyFirstStoredProcedure

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

• Столбцы BusinessEntityID и JobTitle взяты из таблицы Employee.
• Столбцы FirstName и LastName взяты из таблицы Person.

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

Начиная с SQL Server 2016 SP1, Microsoft представила новую инструкцию CREATE или ALTER для модулей кода, таких как хранимые процедуры, представления и определяемые пользователем функции. Вы можете получить представление о функциональности этого нового оператора из предыдущей статьи MSSQLTips.com.

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

Входной параметр позволяет разработчику изменять способ работы хранимой процедуры во время выполнения. Обычно входной параметр в предложении where оператора SELECT используется для управления строками, отображаемыми при запуске хранимой процедуры. Можно заглянуть на страницу руководства MSSQLTips.com для демонстрации того, как использовать входные параметры в предложении where.

Вот простой пример, основанный на изменении uspMyFirstStoredProcedure, который демонстрирует использование входного параметра.

Входной параметр с именем @jobtitle назван непосредственно перед ключевым словом as.

На входной параметр имеется ссылка в предложении where оператора SELECT.

Оператор EXEC, который следует за оператором ALTER PROC, присваивает значение входному параметру @jobtitle во время выполнения. Значением параметра является строка nvarchar («Production Supervisor»).

-- alter a stored proc-- this alteration has one select statement with a where clause
-- and a criterion set by an input parameter
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   select
 Employee.BusinessEntityID
,Person.FirstName
,Person.LastName
,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'
go   -- run altered stored proc with 
-- @jobtitle parameter value is passed without naming the parameter
exec uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка «Results«, на которой показаны выходные данные оператора SELECT внутри uspMyFirstStoredProcedure. На вкладке показан 21 сотрудник, чьи должности начинаются с «Production Supervisor». Вы можете изменить содержимое вкладки «Results», используя другое строковое значение в операторе EXEC.

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

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

Если вы просто хотите передать одно значение, такое как сумма или количество, из хранимой процедуры, вы можете сделать это с помощью выходного параметра. Следующий оператор ALTER PROC иллюстрирует один из способов реализации такого рода задач.

В нашем примере оператор ALTER PROC снова изменяет uspMyFirstStoredProcedure.

Входной параметр @jobtitle из предыдущей версии хранимой процедуры сохраняется.

Кроме того, перед ключевым словом as добавляется спецификация выходного параметра:

• Имя выходного параметра — @jobtitlecount.
• Тип данных для параметра — int, потому что он предназначен для хранения значения счетчика, но вы также можете использовать bigint в качестве типа данных, если это необходимо.
• Ключевое слово out завершает спецификацию параметра, чтобы указать, что этот параметр возвращает значение после запуска хранимой процедуры.

Оператор SELECT состоит из вложенного внутреннего оператора выбора внутри внешнего операторе выбора:

• Оператор внутреннего выбора возвращает строку для каждого сотрудника, JobTitle которого начинается со значения входного параметра.
• Внешний оператор select подсчитывает количество строк, возвращаемых внутренним оператором select, и присваивает счет выходному параметру @jobtitlecount.

-- alter a stored proc
-- this alteration computes an aggregate function value
-- based, in part, on an input parameter (@jobtitle)
-- and saves the computed value in an output parameter (@jobtitlecount)
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50), @jobtitlecount int out
as   select @jobtitlecount = count(*)
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

После выполнения оператора ALTER PROC можно вызвать недавно измененную версию uspMyFirstStoredProcedure и отобразить значение выходного параметра. Следующий сценарий показывает, как этого добиться.

Перед вызовом инструкции EXEC для запуска uspMyFirstStoredProcedure объявите локальную переменную @jobtitlecount для получения значения выходного параметра из хранимой процедуры.

• Значение параметра вывода появляется в операторе EXEC с завершающим ключевым словом вывода OUTPUT. Это ключевое слово указывает, что значение параметра передается из хранимой процедуры в инструкцию EXEC.
• Оператор присваивания (=) передает значение выходного параметра в локальную переменную @jobtitlecount.

Оператор SELECT после оператора EXEC отображает значение локальной переменной @jobtitlecount, которая получила значение выходного параметра.

-- run an altered stored proc with -- @jobtitle input parameter value and
-- save the returned output parameter in a local variable   declare @jobtitlecount int   exec uspMyFirstStoredProcedure N'Production Supervisor',@jobtitlecount = @jobtitlecount OUTPUT   select @jobtitlecount [Job Title Count]

Значения кода возврата хранимой процедуры SQL Server

Хранимые процедуры могут иметь значения кода возврата, которые всегда имеют тип данных int.

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

• Входной параметр имеет имя @jobtitle.
• Критерий предложения where в операторе SELECT: Employee.JobTitle, например ‘%’ + @jobtitle + ‘%’.
• Когда инструкция SELECT с предложением where возвращает хотя бы одну строку, возвращаемое значение устанавливается равным единице. В противном случае возвращаемое значение устанавливается равным нулю.
• Условие EXISTS определяет, будет ли возвращена хотя бы одна строка из оператора SELECT.
• Предложение return возвращает значение кода возврата и завершает хранимую процедуру.

-- alter a stored proc
-- this alteration verifies if a search string value
-- is in a set of column values
-- @jobtitle input parameter contains the search string value
-- JobTitle is the column of values searched
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- does at least one JobTitle contain @jobtitle?
if exists(
   select top 1 Employee.JobTitle
   from AdventureWorks2014.HumanResources.Employee
   inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
   where Employee.JobTitle like '%' + @jobtitle + '%'
)
begin
   return(1)
end
else
begin
   return(0)
end

Следующий сценарий демонстрирует синтаксис для сбора значения кода возврата из хранимой процедуры и показывает некоторый образец кода для обработки значения кода возврата. Скрипт вызывает uspMyFirstStoredProcedure для двух разных значений @jobtitle — sals или sales. Ни одно значение столбца JobTitle не содержит sals, но хотя бы одно значение JobTitle содержит sales.

Сначала в коде объявляются две локальные переменные с именами @jobtitle и @exists.

Локальная переменная @jobtitle используется в инструкции EXEC для передачи строки поиска в uspMyFirstStoredProcedure.

Локальная переменная @exists используется для сбора значения кода возврата из uspMyFirstStoredProcedure. Оператор присваивания внутри оператора EXEC заполняет локальную переменную @exists значением кода возврата.

Оператор потока управления if … else после оператора EXEC обрабатывает возвращаемое значение из хранимой процедуры.

• Если @exists равно нулю, инструкция SELECT сообщает, что нет JobTitle со значением строки поиска во входном параметре.
• Если @exists равно единице, оператор SELECT сообщает, что существует по крайней мере одно значение JobTitle с входным параметром.

Ниже, хранимая процедура выполняется дважды. Первоначальное выполнение предназначено для поисковой строки со значением sals. Второе выполнение — для значения sales в строке поиска.

-- run an altered stored proc with 
-- @jobtitle is an input parameter
-- @exists equals 1 for at least 1 JobTitle containing @jobTitle
-- @exists equals 0 for no JobTitle containing @jobtitle
declare @jobtitle nvarchar(50), @exists int   set @jobtitle = 'sals'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end   set @jobtitle = 'sales'
exec @exists = uspMyFirstStoredProcedure @jobtitle
if @exists = 0
begin
   select 'No JobTitle values with ' + @jobtitle [search outcome]
end
else
begin
   select 'At least one JobTitle value with ' + @jobtitle [search outcome]
end

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

Несколько наборов результатов из хранимой процедуры SQL Server

Следующий сценарий снова демонстрирует, как использовать входной параметр в операторе ALTER PROC. Имя входного параметра перед ключевым словом as — @jobtitle. Эта демонстрация отличается тем, что включает в себя два отдельных оператора SELECT. Первый оператор SELECT возвращает набор результатов, состоящий из всех строк, JobTitle которых начинается со значения входного параметра. Вторая инструкция SELECT возвращает скалярное значение, которое представляет собой количество сотрудников в таблице Employee, JobTitle которых начинается со значения входного параметра.

Оператор EXEC после оператора ALTER PROC вызывает uspMyFirstStoredProcedure. Литеральное строковое значение nvarchar («Production Supervisor») после имени хранимой процедуры является значением входного параметра.

-- alter a stored proc-- this alteration has two select statements
-- and an input parameter
alter proc dbo.uspMyFirstStoredProcedure
@jobtitle nvarchar(50)
as   -- 1st select statement returns a set of row values
select
   Employee.BusinessEntityID
  ,Person.FirstName
  ,Person.LastName
  ,Employee.JobTitle
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'   -- 2nd select statement returns a scalar value
select count(*) as JobTitleCount
from AdventureWorks2014.HumanResources.Employee
inner join AdventureWorks2014.Person.Person on Employee.BusinessEntityID = Person.BusinessEntityID
where Employee.JobTitle like @jobtitle + '%'

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

-- run altered stored proc
-- @jobtitle parameter value is passed without naming the parameter   
exec dbo.uspMyFirstStoredProcedure N'Production Supervisor'

Вот вкладка «Results», на которой показаны выходные данные двух операторов SELECT внутри хранимой процедуры с именем uspMyFirstStoredProcedure.

На верхней панели отображается двадцать один сотрудник, чьи должности начинаются с «Production Supervisor».

На нижней панели отображается скалярное значение с количеством сотрудников, чьи должности начинаются с «Production Supervisor».

Как вы понимаете, можно изменить содержимое вкладки «Результаты», используя другое буквальное строковое значение в операторе EXEC.

Для освоения программирования хранимых процедур используем при-
мер базы данных c названием DB_Books, которая была создана в лабора-
торной работе №1. При выполнении примеров и заданий обращайте вни-
мание на соответствие названий БД, таблиц и других объектов проекта.

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

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

Пользовательские хранимые процедуры реализуют те или иные дейст-
вия. Хранимые процедуры – полноценный объект базы данных. Вследствие
этого каждая хранимая процедура располагается в конкретной базе дан-
ных, где и выполняется.

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

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

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

{CREATE | ALTER } PROC[EDURE] имя_процедуры [;номер]
[{@имя_параметра тип_данных } [VARYING ] [=DEFAULT][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

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

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

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

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

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

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

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

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

Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] имя_процедуры [;номер]
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]

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

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

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

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

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

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

Пример создания процедуры без параметров:

CREATE PROCEDURE Count_Books AS
SELECT COUNT(Code_book) FROM Books
GO

Задание 1. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
GO

Задание 2. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT, @Title AS
CHAR(10)
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
AND Title_book LIKE @Title
GO

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC Count_Books_Title 100, 'П%'

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT, @Title
CHAR(10) , @Itogo INT OUTPUT
AS
SELECT @Itogo = COUNT(Code_book) FROM Books WHERE
Pages>=@Count_pages AND Title_book LIKE @Title
GO

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите с помощью набора команд:

 sql>
Declare @q As int
EXEC Count_Books_Itogo 100, 'П%', @q output
select @q

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT
AS
IF (SELECT Name_author FROM authors WHERE Code_author = @param) =
'Пушкин А.С.'
RETURN 1
ELSE
RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

DECLARE @return_status INT
EXEC @return_status = checkname 1
SELECT 'Return Status' = @return_status

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

CREATE PROC update_proc
AS
UPDATE Purchases SET Code_purchase = Code_purchase*2

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

Задание 6.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин-
формации о конкретном авторе:

CREATE PROC select_author @k CHAR(30)
AS
SELECT * FROM Authors WHERE name_author=@k

Задание 7.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

EXEC select_author 'Пушкин А.С.' или
select_author @k='Пушкин А.С.' или
EXEC select_author @k='Пушкин А.С.'

Пример создания процедуры с входным параметром и значением по
умолчанию для увеличения значения ключевого поля в таблице Purchases
в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2
AS
UPDATE Purchases SET Code_purchase = Code_purchase *@p

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

Задание 8.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

EXEC update_proc 4 или
EXEC update_proc @p = 4 или
EXEC update_proc --будет использовано значение по умолчанию.

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

CREATE PROC count_purchases
@d1 SMALLDATETIME, @d2 SMALLDATETIME,
@c INT OUTPUT
AS
SELECT @c=COUNT(Code_purchase) FROM Purchases WHERE Date_order
BETWEEN @d1 AND @d2
SET @c = ISNULL(@c,0)

Задание 9.
Создайте данную процедуру в разделе Stored Procedures ба-
зы данных DB_Books через утилиту SQL server Management Studio. Запус-
тите ее с помощью команд:

DECLARE @c2 INT
EXEC count_purchases ’01-jun-2006’, ’01-jul-2006’, @c2 OUTPUT
SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения.

В утилите SQL Server Management Studio создать новую страницу для
кода (кнопка «Создать запрос»). Программно сделать активной созданную
БД DB_Books с помощью оператора Use. Создать хранимые процедуры c
помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL
запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно
было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком-
паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых
название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = 'ОАО МИР'

*/
–В данной работе будет создана процедура:

CREATE PROC select_name_company @comp CHAR(30)
AS
SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = @comp

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

EXEC select_name_company 'ОАО МИР'

Сохранить файл программы с названием ФамилияСтудента_ЛАб_4.
В SQL Server Management Studio в разделе хранимых процедур БД
DB_Books проверить наличие процедур.

Список заданий

В утилите SQL Server Management Studio создать новую программу.
Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Сохранить файл программы с названием Фамилия Студента_Лаб_4_№варианта. В SQL Server Management Studio в разделе хранимых процедур индивидуальной БД проверить наличие процедур.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

4. Вывести информацию о подарках со стоимостью больше указанного
числа, отсортированных по дате.

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений,
отсортированный по количеству обращений по убыванию.

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

Вариант 3

1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов продаж, по которым продано цветов на сумму
больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и
название лекарства по коду поступления больше указанного числа.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства
меньше определенного значения.

Вариант 5

1. Вывести список сотрудников с указанной должностью.

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный
период.

4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

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

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

4. Вывести порядок приготовления блюда и название блюда с количеством углеводов больше определенного значения или количеством
калорий больше указанного значения.

Вариант 7

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

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

4. Вывести список зарегистрированных документов с определенным
типом документа или с датой регистрации больше указанного значения.

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа
в указанном диапазоне.

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа
в указанном диапазоне.

Вариант 10

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

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

4. Вывести список зарегистрированных документов с указанным типом
документа или с кодом документа меньше определенного значения.

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

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

4. Вывести список зарегистрированных документов с кодом документа
в указанном диапазоне.

Вариант 12

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный
клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество
их обращений, отсортированный по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 13

1. Вывести список оборудования с указанным типом.
2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по
типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу
больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов поступлений, по которым продано цветов на
суммы больше определенного значения.

3. Вывести дату поступления, сумму, названия поставщика и цветов по
определенному коду поставщика.

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

1. Вывести список клиентов, заехавших в номера в указанный период.

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный
клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество
их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

1. Вывести список ценностей с закупочной стоимостью больше определенного значения или сроком гарантии больше указанного числа.

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

4. Вывести список материально ответственных лиц с датой приема на
работу в указанном диапазоне.

Вариант 18

1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с
указанным номером.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства
меньше определенного значения.

Вариант 20

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

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

4. Вывести список зарегистрированных документов с указанным типом
документа или с кодом документа в определенном диапазоне.

Назад: СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

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

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

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

• планирование
  прав доступа. При создании хранимой
  процедуры следует
  учитывать, что она будет иметь те же
  права доступа к объектам базы данных,
  что и создавший ее пользователь;

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

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

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

<определение_процедуры>::=

{CREATE
| ALTER } [PROCEDURE] имя_процедуры

[;номер]

[{@имя_параметра
тип_данных } [VARYING ]

[=default][OUTPUT]
][,…n]

[WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE,

ENCRYPTION
}]

[FOR
REPLICATION]

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]

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Что такое хранимая процедура SQL Server?

Хранимая процедура — это готовый код T-SQL, который может повторно использоваться снова и снова. Основные преимущества использования хранимых процедур перечислены ниже:

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

Повторное использование кода: Хранимая процедура SQL помогает избежать неоднократного переписывания одного и того же кода.

Упрощение обслуживания: Использование хранимых процедур упрощает обслуживание кода. Поскольку хранимая процедура может широко использоваться в различных приложениях, то изменение её в одном месте сразу скажется на всех приложениях.

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

Когда компилируется хранимая процедура?

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

• Динамические административные представления — это специальные системные представления, которые содержат различную информацию о счетчиках производительности SQL Server и другие метрики.
• Расширенные события — очень продвинутый инструмент, который позволяет захватить и вывести различные события, происходящие в SQL Server. Расширенные события используют меньше системных ресурсов, имеют развитые возможности фильтрации и группировки и предоставляют огромное число событий для мониторинга. Поэтому мы создадим расширенное событие для наблюдения за тем, что происходит за сценой процесса компиляции плана запроса хранимых процедур в SQL server. Расширенное событие, которое мы создадим, будет включать следующие события:
  • query_post_compilation_showplan: Это событие захватывает изначально скомпилированный план. В то же время, это событие возвращает предварительный план, когда запрос скомпилирован.
  • sp_cache_hit: Это событие происходит, когда план хранимой процедуры извлекается из кэша процедурных планов оптимизатором запросов.
  • sp_cache_insert: Это событие происходит, когда хранимая процедура помещается в процедурный кэш.
  • sp_cache_miss: Это событие происходит, когда хранимая процедура не обнаруживается в процедурном кэше.
  • sp_cache_remove Это событие происходит, когда хранимая процедура удаляется из процедурного кэша.

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

CREATE EVENT SESSION [TrackStoredProcedureEvents] ON SERVER 

ADD EVENT sqlserver.query_post_compilation_showplan(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_hit(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_insert(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_miss(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC')),

ADD EVENT sqlserver.sp_cache_remove(

    ACTION(sqlserver.client_app_name,sqlserver.sql_text)

    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2017') AND [object_type]='PROC'))

WITH (STARTUP_STATE=ON)

GO

ALTER EVENT SESSION TrackStoredProcedureEvents ON SERVER   STATE = START

После создания и запуска расширенного события щелкнем на опции “Watch Live Data” созданного расширенного события. Так мы сможем мониторить захваченные события вживую в SQL Server Management Studio (SSMS).

Создадим теперь простую хранимую процедуру в базе данных Adventureworks.

CREATE OR ALTER PROCEDURE GetProductionList

@ProductIdNumber AS INT

AS

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 

    ON pm.[ProductModelID] = pmx.[ProductModelID] 

    INNER JOIN [Production].[ProductDescription] pd 

    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]

WHERE p.ProductID =@ProductIdNumber

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

В то же время, план запроса процедуры GetProductionList не помещается в кэш планов запросов.

SELECT * FROM (

SELECT cp.objtype AS [Plan Type],

OBJECT_NAME(st.objectid,st.dbid) AS [Object Name],

cp.refcounts AS [Reference Counts],

cp.usecounts AS [Use Counts],

st.TEXT AS [SQL Batch],

qp.query_plan AS [Query Plan]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE cp.objtype='Proc'

) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%'

На этом шаге мы выполним нашу тестовую хранимую процедуру со случайным параметром и посмотрим на расширенное событие.

EXEC GetProductionList @ProductIdNumber = 757

После первого выполнения хранимой процедуры было захвачено два события:

Событие query_post_compilation_showplan показывает, что хранимая процедура была скомпилирована после первого выполнения. В то же время, query_post_compilation_showplan захватывает предварительный план выполнения, и мы можем увидеть этот план на вкладке планов запросов. Событие sp_cache_insert показывает, что план выполнения был помещен в кэш планов запросов. Эти два события доказывают, что хранимые процедуры SQL Server компилируются при своем первом выполнении. Кроме того, мы видим, что после первого выполнения хранимой процедуры её план выполнения помещается в кэш планов запросов.

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

Другой вопрос, относящийся к перекомпиляции плана выполнения хранимых процедур SQL Server, связан с опциями соединения. SQL Server позволяет менять некоторые установки сессионных подключений с помощью опций SET. Однако эти опции могут привести к созданию нового плана выполнения. Например, изменим перед выполнением нашего тестового запроса NUMERIC_ROUNDABORT в значение ON, а затем выполним его.

SET NUMERIC_ROUNDABORT ON

GO

EXEC GetProductionList  @ProductIdNumber = 757

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

SELECT * FROM (

SELECT pa.attribute,pa.value,cp.objtype AS [Plan Type],

OBJECT_NAME(st.objectid,st.dbid) AS [Object Name],

cp.refcounts AS [Reference Counts],

cp.usecounts AS [Use Counts],

st.TEXT AS [SQL Batch],

qp.query_plan AS [Query Plan]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st 

OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa

WHERE cp.objtype='Proc' and   pa.attribute = 'set_options'

) AS TMP_TBL WHERE [SQL Batch] LIKE '%GetProductionList%'

Как ясно показано на рисунке, значения опции SET различны, и эта разница вызывает генерацию нового плана выполнения.

Хранимая процедура SQL и sp_recompile

sp_recompile является системной процедурой, которая используется для перекомпиляции хранимых процедур. Использование этой процедуры очень простое — достаточно только передать имя процедуры в качестве параметра.

EXEC sp_recompile N’Имя процедуры’

При выполнении этой системной процедуры план запроса процедуры, которую мы передаем в качестве параметра, будет сразу удален из кэша. Давайте выполним sp_recompile для нашей тестовой процедуры.

EXEC sp_recompile N'GetProductionList'

После выполнения sp_recompile будет возвращено сообщение “Object ‘GetProductionList’ was successfully marked for recompilation” (“Объект ‘GetProductionList’ был успешно помечен для перекомпиляции”. Фактически это означает, что план выполнения процедуры был удален из кэша планов. Это событие можно увидеть в расширенном событии.

Теперь мы повторно выполним нашу тестовую хранимую процедуру и проанализируем её поведение с помощью расширенного события.

EXEC GetProductionList @ProductIdNumber = 757

После выполнения тестовой хранимой процедуры произошли два события:

query_post_compilation_showplan было выполнено, поскольку кэшированный план выполнения хранимой процедуры был удален, когда мы выполнили sp_recompile. В этом случае оптимизатор запросов не нашел никакого подходящего плана выполнения в кэше планов и заново создал новый план запроса для выполняемой хранимой процедуры. sp_cache_insert произошло, поскольку план выполнения процедуры был помещен в кэш планов.

Как использовать хинт WITH RECOMPILE с хранимыми процедурами SQL Server?

В некоторых случаях (parameter sniffing — прослушивание параметра) нам требуется генерировать свежий план запроса для каждого выполнения хранимой процедуры. В этих случаях мы можем добавить хинт WITH RECOMPILE в хранимую процедуру, чтобы оптимизатор запросов генерировал новый план выполнения при каждом выполнении хранимой процедуры. В следующем запросе мы можем добавить хинт WITH RECOMPILE и изменить хранимую процедуру.

CREATE OR ALTER PROCEDURE GetProductionList

@ProductIdNumber AS INT

WITH RECOMPILE

AS

SELECT 

    p.[ProductID] 

    ,p.[Name] 

    ,pm.[Name] AS [ProductModel] 

    ,pmx.[CultureID] 

    ,pd.[Description] 

FROM [Production].[Product] p 

    INNER JOIN [Production].[ProductModel] pm 

    ON p.[ProductModelID] = pm.[ProductModelID] 

    INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx 

    ON pm.[ProductModelID] = pmx.[ProductModelID] 

    INNER JOIN [Production].[ProductDescription] pd 

    ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID]

WHERE p.ProductID =@ProductIdNumber

Изменение хранимой процедуры вызывает удаление записи плана запроса для хранимой процедуры из кэша планов. Эту ситуацию можно наблюдать в событии sp_cache_remove.

При каждом выполнении хранимой процедуры GetProductionList оптимизатор запросов будет перекомпилировать процедуру из-за хинта WITH RECOMPILE. В результате использование хинта RECOMPILE внутри хранимой процедуры приводит к:

• Перекомпиляции при каждом выполнении хранимой процедуры.
• План запроса хранимой процедуры не помещается в кэш планов запросов.

Если включить фактический план выполнения, а затем посмотреть на свойства оператора SELECT, то увидим, что атрибут RetrievedFromCache (взято из кэша) имеет значение false. При выполнении любого запроса с хинтом RECOMPILE этот атрибут будет иметь значение false в плане запроса.

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

EXEC GetProductionList @ProductIdNumber = 757 WITH RECOMPILE

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

Заключение

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

• Использоване системной процедуры sp_recompile.
• Использование хинта WITH RECOMPILE.
• Изменение хранимой процедуры.