SQL-Ex blog

Что необходимо для выполнения примеров

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

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

В простейшем виде хранимая процедура - это не более чем набор операторов Transact-SQL (T-SQL), который хранится в базе данных и может выполняться при вызове хранимой процедуры по имени. Имя присваивается хранимой процедуре при ее создании. Для краткости хранимую процедуру часто называют хп (SP).

Особенности хранимых процедур

• Хранимые процедуры могут принимать входные параметры.


• Хранимые процедуры могут возвращать выходные параметры.


• Хранимые процедуры содержат программные операторы.


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


• Нет ограничения на размер хранимой процедуры


• Имеется четыре типа хранимых процедур:
  
  
  1. Определяемые пользователем
  
  
  2. Временные
  
  
  3. Системные
  
  
  4. Расширенные определяемые пользователем
  
  

Определения четырех типов хранимых процедур

Определяемая пользователем хранимая процедура

Наиболее часто используются пользовательские хранимые процедуры. Такая процедура может создаваться в пользовательской базе данных или в любой системной базе данных за исключением базы данных Resource. Мы подробней поговорим о пользовательских хранимых процедурах позже в этой статье и рассмотрим ряд примеров.

Временная хранимая процедура

Временная хранимая процедура - это вид пользовательской хранимой процедуры, которая очень похожа на временные таблицы. Временные хранимые процедуры хранятся в базе данных "tempdb", видной на вкладке "System Databases". Эти временные хранимые процедуры могут использоваться как локальные или глобальные.

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

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

Поскольку они являются "временными", эти хранимые процедуры пропадают, когда закрывается подключение SQL.

Системные хранимые процедуры

Вы обнаружите, что довольно часто используете системные хранимые процедуры. Полезно познакомиться с ними, чтобы не изобретать колесо. Эти хранимые процедуры включены по умолчанию в установку SQL Server и всегда будут начинаться с префикса "sys.sp_". Вам могут пригодиться самые распространенные системные хранимые процедуры, включая sys.sp_addUser, sys.sp_addLogin, sys.sp_addRole, sys.sp_change_users_login и т.д.

Вы можете увидеть полный список предустановленных системных хранимых процедур в браузере объектов SQL Server Management Studio (SSMS), развернув папки в следующем порядке:

1. Databases (базы данных)


2. System Databases (системные базы данных)


3. Tempdb


4. Programmability (программирование)


5. Stored Procedures (хранимые процедуры)


6. System Stored Procedures (системные хранимые процедуры)

Вот скриншот:

Расширенные хранимые процедуры пользователя

Расширенные хранимые процедуры пользователя используются для создания внешних процедур на языках типа C, C#, VB и т.д. Они реализуются как DLL, которые SQL Server может загрузить и выполнять динамически.

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

Зачем использовать хранимые процедуры

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

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

Мы можем предоставить пользователям разрешение на использование (вызов) хранимой процедуры или же запретить пользователям ее вызывать. Замечание: пользователь не должен иметь разрешение или доступ к таблице, чтобы вызвать хранимую процедуру, которая будет менять данные в этой таблице. Это позволит пользователям добавлять данные в таблицу для создания записей данных, не имея к ним доступа на просмотр, удаление или изменение любых данных в таблице, помимо тех, на которые вы дали им разрешения.

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

Создание простой хранимой процедуры

Теперь, когда мы знаем немного больше о преимуществах хранимых процедур, давайте создадим базовый тестовый пример для работы. В коде примера, приведенного ниже, мы создаем хранимую процедуру с именем uspGetEmployees. Я уверен, что вы можете догадаться, что подразумевает часть "Get Employees", но что такое "usp" в имени? Вы можете именовать хранимые процедуры как вам нравится, но отраслевым стандартом является использование соглашения, которое мы здесь применили. "usp" - это сокращение от "User Stored Procedure" (пользовательская хранимая процедура).

USE [AdventureWorks2019]

GO

CREATE PROCEDURE uspGetEmployees

AS

SELECT

   BusinessEntityID

   , FirstName

   , LastName

FROM Person.Person;

GO

Видно, что блок кода выше очень похож на создание представления (VIEW) в SQL Server. Вы просто предваряете оператор SELECT текстом "CREATE PROCEDURE <ИмяХранимойПроцедуры>".

Замечание. При создании хранимой процедуры вы можете сократить слово "PROCEDURE" до "PROC"; любой вариант будет работать.

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

При выполнении хранимой процедуры вам просто нужно напечатать команду EXECUTE (или EXEC) с последующим именем хранимой процедуры, как в примере ниже.

EXEC uspGetEmployees;

Результаты: (фрагмент)



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

Изменение/модификация хранимой процедуры

Вы можете использовать команду "ALTER PROC" или "ALTER PROCEDURE" для изменения функциональности хранимой процедуры. Скажем, мы хотим также получать отчество из таблицы "Person.Person" в базе данных AdventureWorks. Вместо создания новой хранимой процедуры мы можем модифицировать имеющуюся, как в примере ниже.

ALTER PROC uspGetEmployees

AS

SELECT

   BusinessEntityID

   , FirstName

   , MiddleName

   , LastName

FROM Person.Person;

GO

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

EXEC uspGetEmployees;

Результаты: (фрагмент)



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

ОК, довольно просто. Давайте продолжим. Теперь мы добавим предложение WHERE и будем использовать предикат IS NOT NULL для фильтрации сотрудников, у которых присутствует отчество (не стоит NULL).

ALTER PROC uspGetEmployees

AS

SELECT

   BusinessEntityID

   , FirstName

   , MiddleName

   , LastName

FROM Person.Person

WHERE MiddleName IS NOT NULL;

GO

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

EXEC uspGetEmployees;

Результаты: (фрагмент)

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

Удаление хранимой процедуры в SQL подобно удалению таблицы, представления и т.д. Вы просто вызываете команду "DROP PROCEDURE" или "DROP PROC" с именем процедуры.

DROP PROC uspGetEmployees;

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

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

Что такое входной параметр? В простейшем виде это переменная. Когда мы добавляем переменную к хранимой процедуре, мы считаем ее входным параметром, а не переменной, хотя это одно и то же. Просто считайте это соглашением о наименовании, чтобы отделить одно от другого.

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

ALTER PROCEDURE uspGetEmployees 

  @lastname VARCHAR(40) = 'abel'

AS

SELECT

   BusinessEntityID

   , FirstName

   , LastName

FROM Person.Person

WHERE LastName = @lastname;

GO

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

EXEC uspGetEmployees;

Результаты:



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

EXEC uspGetEmployees 'Akers';

Результаты:



Что если вы не уверены, как пишется имя? Хорошо, тогда опять изменим нашу хранимую процедуру. Теперь мы заменим равенство "=" предикатом LIKE в предложении WHERE.

ALTER PROCEDURE uspGetEmployees

  @lastname VARCHAR(40) = 'abel'

AS

SELECT

   BusinessEntityID

   , FirstName

   , LastName

FROM Person.Person

WHERE LastName LIKE @lastname;

GO

Это позволит нам использовать подстановочные знаки в параметре и вернуть, например, каждого, чья фамилия начинается с "Ab".

EXEC uspGetEmployees 'Ab%';

Результаты:

Хранимые процедуры с несколькими входными параметрами

Добавление нескольких параметров следует тем же принципам, что и команда с единственным параметром, только они должны разделяться запятой. В примере ниже мы добавляем параметр "FirstName" и ссылку на него в предложении WHERE. Мы начинаем с удаления значений по умолчанию, которые присваивались параметрам в предыдущих примерах. Значения параметров будут передаваться пользователем при вызове хранимой процедуры.

ALTER PROCEDURE uspGetEmployees

  @lastname VARCHAR(40)

, @firstname VARCHAR(40)

AS

SELECT

   BusinessEntityID

   , FirstName

   , LastName

FROM Person.Person

WHERE LastName LIKE @lastname AND FirstName LIKE @firstname;

GO

Теперь при выполнении хранимой процедуры нам нужно включать в вызов значения обоих параметров. В этом примере мы вернем все строки, у которых фамилия начинается с "Ab", а имя - с "K".

EXEC uspGetEmployees 'Ab%', 'K%';

Результаты:



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

EXEC uspGetEmployees @FirstName = 'Kim', @LastName = 'Ab%';

Результаты:



Обычно вы не будете этого делать. Но если вы не уверены в порядке перечисления параметров, вы можете использовать этот метод, чтобы гарантировать получение желаемых результатов. В противном случае, если вы поменяете порядок параметров при вызове хранимой процедуры без указания имен параметров, запрос не вернет правильный результат. Поскольку сначала будет выполняться поиск по фамилии, а затем по имени - в порядке нашего примера, то запрос ничего не вернет, т.к. нет сотрудника в таблице с фамилией Kim и именем Abercrombie.

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

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

1) CREATE PROC myRowCount

2) @lname VARCHAR(40), 

3) @numrows INT = 0 OUTPUT 

4) AS

5) SELECT LastName

6) FROM Person.Person 

7) WHERE LastName LIKE @lname

 SET @numrows = @@ROWCOUNT; 

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

1. Мы создаем новую хранимую процедуру с именем "myRowCount".


2. Добавляем входной параметр с именем "@lname" и типом данных "VARCHAR(40)".


3. Теперь давайте добавим выходной параметр с именем "@numrows", т.к. мы хотим подсчитать число строк, возвращаемых хранимой процедурой. Тип данных переменной - INT, значение по умолчанию - 0, и нам необходимо указать, что это выходной параметр, с помощью ключевого слова OUTPUT. Это даст знать SQL, что эта переменная будет использоваться для вывода данных.


4. Ключевое слово "AS" говорит создать хранимую процедуру, содержащую последующий код.


5. Наш основной оператор SELECT.


6. Предложение FROM указывает таблицы, откуда будут извлекаться данные.


7. Предложение WHERE устанавливает фильтры для оператора SELECT.


8. На этой строке мы устанавливаем (присваиваем) для "@numrows" значение "@@ROWCOUNT". Когда вы видите двойной символ @, это признак системной переменной, которых имеется несколько. Познакомиться с системными переменными можно в документации. Поскольку @@ROWCOUNT получает значение при каждом выполнении запроса, мы захватим число, сгенерированное при выполнении нашей хранимой процедуры, и загрузим его в выходной параметр @numrows.

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

1) DECLARE @retrows INT

2) EXEC myRowCount 'B%', @numrows = @retrows OUTPUT

3) SELECT @retrows AS 'Rows'; 

1. Здесь мы объявляем переменную, чтобы принять значение из выходного параметра процедуры.
   В этом примере мы называем новую переменную "@retrows", сокращение от "return rows". Мы должны назначить ей тип данных, который соответствует выходной переменной, которую мы создали в хранимой процедуре.


2. Здесь мы выполняем нашу хранимую процедуру с помощью команды "EXEC myRowCount", и нам нужно предоставить значение входному параметру. В данном примере мы хотим вернуть все строки, для которых фамилия начинается с "B". Далее на той же строке мы хотим получить значение в нашу переменную "@numrows", которую мы создали в хранимой процедуре, и передать это значение в нашу новую переменную, которую мы описали в операторе DECLARE выше. Опять же нам нужно добавить ключевое слово OUTPUT, чтобы напомнить компилятору об этой переменной.


3. Теперь мы создаем последний оператор SELECT, в котором получаем значение (число строк) из переменной "@retrows" и именуем результирующий набор "Rows".

При выполнении запроса вы должны увидеть два результирующих набора. Первый вернет все фамилии из таблицы, которые начинаются с буквы "B"; их должно быть 1205. Второй результирующий набор покажет это число.

Результаты:

Обработка ошибок в хранимых процедурах

Здесь мы опять изменим хранимую процедуру uspGetEmployees. С помощью функции @@ROWCOUNT, о которой говорилось в предыдущем разделе, мы будем возвращать сообщение "No Records Found", если ничего не будет возвращено. Это упрощенный, но эффективный взгляд на обработку ошибок в хранимых процедурах.

Здесь мы выполним хранимую процедуру дважды. Сначала вернем все строки, фамилии которых начинаются с "Ak"; потом выполним хранимую процедуру, которая вернет сообщение об ошибке. Но первое, что нужно сделать, - это изменить процедуру.

ALTER PROCEDURE uspGetEmployees

@lastname VARCHAR(40)

AS

SELECT

   BusinessEntityID

   , FirstName

   , LastName

FROM Person.Person

WHERE LastName LIKE @lastname

IF @@ROWCOUNT < 1

BEGIN

   RAISERROR('No Records Found', 10,1)

END;

GO

Теперь давайте выполним нашу хранимую процедуру для поиска фамилий, которые, как мы знаем, есть в таблице Person.Person.

EXEC uspGetEmployees 'Ak%';

Результаты:



Теперь выполним ту же процедуру с именем, которого нет в таблице Person.Person.

EXEC uspGetEmployees 'zz%';

Результаты:



Несмотря на то, что это был примитивный пример, он даст вам направление работы в тестовом и рабочем окружении. Экспериментируйте и вносите изменения. Делайте обработку ошибок интуитивно понятной.

Безопасность хранимых процедур

Хранимые процедуры предлагают много преимуществ в плане безопасности по сравнению с обычными запросами T-SQL. Когда вы записываете запрос T-SQL в хранимой процедуре, пользователь не может манипулировать кодом, а только выпонять его в том виде, в котором он создан. Ниже приводится список преимуществ хранимых процедур по сравнению с обычным кодом (ad hoc).

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


• Шифрование: Мы можете зашифровать код в хранимой процедуре после ее создания.


• Предотвращение SQL-инъекции: Все передаваемые в хранимую процедуру параметры могут быть проверены до передачи их в таблицу или другой объект базы данных.


• Согласованное манипулирование данными: Хранимая процедура выполняет всякий раз один и тот же код в одном и том же порядке.


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


• Обработка ошибок: Хранимые процедуры обеспечивают возможность использовать последовательную и эффективную обработку ошибок и создание отчетов.

Ссылки по теме:

1. Переменные SQL в скриптах, функциях, хранимых процедурах, SQLCMD и т.д.
2. Реализация безопасности SQL Server с помощью хранимых процедур и представлений
3. Опции перекомпиляции хранимых процедур в SQL Server
4. Вызов исключений и обработка ошибок с помощью THROW в SQL Server
5. Что такое ad hoc запрос?