Хранимые процедуры в SQL server

Введение

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

В чем плюсы такого подхода? 

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

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

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

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

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

выбираем базу данных, переходим на вкладуку "Программирование/Хранимые процедуры"

Создаем хранимую процедуру через контекстное меню:

Видим вот такой код:

Рассмотрим пример создания процедуры. Очищаем все и вставляем в поле следующий код:

CREATE PROCEDURE GetStudents
AS
     SELECT * FROM Students
GO

После этого нажимаем "Выполнить" (F5) и видим слева нашу хранимую процедуру:

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

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

Вызов нашей хранимой процедуры осуществляется с помощью кода (после чего нажимаем "выполнить" или F5):

USE sampledb;

EXEC GetStudents

Результатом выполнения данного кода будет выбор всех студентов из таблицы Students:

1	str@gmail.com	Иван	Иванов	г. Рязань, ул. Ленина 54/2	50000,00
2	str@yandex.ru	Петр	Петров	г. Рязань, ул. Ленина 54/3	50000,00
3	ilya@gmail.com	Илья	Ильин	г. Рязань, ул. Ленина 54/4	40000,00
4	vp@gmail.com	Иван	Прохоров	г. Рязань, ул. Ленина 57/8	40000,00
5	bak@gmail.com	Борис	Акунин	г. Москва, ул. Лебедева 23/21	60000,00
6	el@gmail.com	Екатерина	Ларина	г. Шахты, ул. Пражская 4/9	60000,00
7	eb@gmail.com	Елизавета	Бродская	г. Рязань, ул. Ленина 54/2	90000,00

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

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

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

CREATE PROCEDURE proc1
   @s1 nvarchar(128),
   @s2 int
AS
BEGIN
   select @s1 + cast(@s2 as nvarchar)

END
GO

Вызов процедуры: 

exec proc1 @s1='123', @s2 = 0

Параметры также могут быть выходными - т.е. их значение изменено в процедуре и возвращено в вызывающую сторону. 

CREATE PROCEDURE proc2
   @s1 nvarchar(128),
   @s2 int,
   @s3 nvarchar OUTPUT
AS
BEGIN
   set @s3 = @s1 + cast(@s2 as nvarchar)

END
GO

Вызов процедуры: 

declare @test nvarchar(max)=''
exec proc1 @s1='123', @s2 = 0, @s3 = @test

print @test

Использование if

Пример хранимой процедуры с условием if:

CREATE PROCEDURE checkMaxAward
AS
BEGIN
	DECLARE @maxAward money
 
SELECT @maxAward = MAX(award) FROM Students
 
IF (@maxAward > 100000) begin
    PRINT 'максимальная сумма премии больше 100000';
end ELSE begin
    PRINT 'максимальная сумма премии меньше 100000';
end

END
GO

В данной процедуре выбираем максимальную сумму премии у студентов, в зависимости от результата выводим нужную строку. Также следует обратить внимание на выражение:

DECLARE @maxAward money

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

В нашем случае результат будет такой:

максимальная сумма премии меньше 100000

Циклы в хранимых процедурах SQL Server

Дальше разберем использование циклов. Разберем классический пример: вычисление факториала числа. Используем такой код:

DECLARE @number INT, @factorial INT
SET @factorial = 2;
SET @number = 10;
 
WHILE @number > 0
    BEGIN
        SET @factorial = @factorial * @number
        SET @number = @number - 2
    END;
 
PRINT @factorial

Пояснения к коду: пока переменная @number не будет равна 0, будет продолжаться цикл WHILE. Каждый проход цикла называется итерацией. В каждой итерации будет переустанавливаться значение переменных @factorial и @number.

Результатом выполнения данного кода будет:

7680

Также следует обратить внимание на ключевое слово "PRINT", которое  выводит результат нашего кода:

Инструкция OUTPUT

OUTPUT – это инструкция, возвращающая изменившиеся строки в результате выполнения инструкций INSERT, UPDATE, DELETE или MERGE.

OUTPUT может помочь в тех случаях, когда нужно проверить или узнать какие именно строки (записи) были добавлены, удалены или изменены, без дополнительных запросов на выборку (SELECT). Данная инструкция позволяет сохранить все изменения в отдельном месте, например, в таблице, благодаря этому мы можем узнать и работать, например, со списком всех идентификаторов, которые были сгенерированы и добавлены в таблицу.

Принцип работы OUTPUT: все изменения, которые производят инструкции INSERT, UPDATE, DELETE и MERGE, фиксируются, условно говоря, во временных таблицах Inserted и Deleted. Они имеют такую же структуру, как и целевая таблица. Для того чтобы посмотреть изменения, нам необходимо в инструкции OUTPUT указать соответствующий префикс и название нужного столбца, примерно так же, как мы это делаем в инструкции SELECT, перечисляя названия столбцов, тем самым мы извлечем данные из этих таблиц.

Преобразование типов данных для переменных

Функция CAST преобразует выражение одного типа к другому и имеет следующую форму:

CAST(выражение AS тип_данных)

Пример. Есть такой код:

SELECT 'Средняя премия = '+ CAST(AVG(award) AS CHAR(15)) 
FROM Students;

Преобразуем числовое значение "award".

Результатом данного кода будет:

Средняя премия =   55714.29

Также есть функция TRY_CAST для преобразования данных. Функция TRY_CAST пытается преобразовать выражение из одного типа данных в другой тип данных. Если преобразование не удалось, функция вернет NULL. В противном случае вернет преобразованное значение.

Пример, следующий код пытается преобразовать строку "test" к типу float:

SELECT   
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL   
    THEN 'Cast failed'  
    ELSE 'Cast succeeded'  
END AS Result;  
GO  

Результатом данного кода будет:

Cast failed

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

Конкатенация строк

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

SELECT  email, CONCAT(firstName, lastName) from Students

Результат данного кода будет следующий:

str@gmail.com	ИванИванов
str@yandex.ru	ПетрПетров
ilya@gmail.com	ИльяИльин
vp@gmail.com	ИванПрохоров
bak@gmail.com	БорисАкунин
el@gmail.com	ЕкатеринаЛарина
eb@gmail.com	ЕлизаветаБродская

Данная функция склеивает firstName и lastName в один столбец.

Стандартные функции для работы с датой и временем

Рассмотрим стандартную функцию GETDATE(), которая возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime:

SELECT  getdate()

Вернет результат:

2021-11-08 21:19:58.843

Функция dateadd добавляет к дате некое значение (месяцы, дни, недели, минуты т.д.)

select dateadd(day, 1, getdate())

Работа с NULL через ISNULL, NULLIF

В условиях вы можете проверить равно ли какое то выражение через такую конструкцию: 

select * from table1 where a1 is null

Для замены NULL на какое-то значение используйте функцию ISNULL. Если оно равно NULL, то функция возвращает значение, которое передается в качестве второго параметра:

ISNULL(выражение, значение)

Выберем всех студентов из таблицы Students, а у которых значение email NULL, заменим на надпись "неизвестно":

SELECT firstName, lastName,
              ISNULL(email, 'неизвестно') AS Email
FROM Students

Результат этого запроса ниже:

Иван	Иванов	str@gmail.com
Петр	Петров	str@yandex.ru
Илья	Ильин	ilya@gmail.com
Иван	Прохоров	vp@gmail.com
Борис	Акунин	bak@gmail.com
Екатерина	Ларина	el@gmail.com
Елизавета	Бродская	eb@gmail.com
Семен	Зюзин	неизвестно

Последняя строка поле email было заменено на "неизвестно", т.к. имеет значение NULL.

Рассмотрим другую функцию: NULLIF. Она возвращает нулевое значение, если два указанных выражения равны. Например:

SELECT NULLIF (4,4) AS Same, NULLIF (5,7) AS Different;

 Результат:

NULL	5

возвращает NULL для первого столбца (4 и 4), потому что два входных значения одинаковы. Второй столбец возвращает первое значение (5), потому что два входных значения различны.

Полезно знать о некоторых важных системных процедурах. А именно: 

  • sp_help SP_Name : используется для получения информации о названиях параметров процедуры, их типах и т.д. Эта процедура может быть применена к любому объекту БД (таблица, триггер и т.п.)
  • sp_helptext SP_Name : используется для получения текста хранимой процедуры

Пример первая функция sp_help. Используем вот такой код:

sp_help Students

Результат выполнения данной функции ниже:

Здесь мы видим подробную информацию о таблице Students.

Пример использования второй функции: 

sp_helptext GetStudents

Результат ее выполнения:

Дополнительные материалы

  1. Кеширование результата выдачи хранимой процедуры
  2. Поиск строки во всех хранимых процедурах
  3. Безопасное приведение типов
  4. Как привести дату в строку в нужный формат
  5. Как работать с датами
Насколько полезной была статья?

Что еще посмотреть по SQL Server

Выгода от использования Falcon Space

В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
Нужна бесплатная консультация?
Планируете делать веб-проект?
Сайт использует Cookie. Правила конфиденциальности OK