Данный материал является переводом оригинальной статьи "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.
RSS - Записи
Добавить комментарий