Хранимые процедуры в SQL server
Введение
Использование хранимых процедур позволяет организовать бизнес-логику и логику управления данными на стороне базы данных.
В чем плюсы такого подхода?
Во-первых, SQL Server оптимизирует и компилирует хранимые процедуры, поэтому они выполняются быстро без необходимости повторять эти шаги каждый раз.
Во-вторых, они выполняются на стороне базы данных, а не в коде приложения. Т.е. минимум тратится ресурсов на дополнительные обращения к базе данных.
В-третьих, использование хранимых процедур позволяет сэкономить траффик, так как клиент посылает серверу только запрос, сервер его обрабатывает и возвращает только результат, который обычно значительно меньше, чем полный набор данных.
Видео по основам SQL. Работа с хранимыми процедурами https://rutube.ru/video/private/76421f9ddb4dbf55e96c203371940c27/?p=4VCdgB5jfZ8dB6hhXOJGgQ
Создание хранимой процедуры в 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
Результат ее выполнения:
Дополнительные материалы
Что еще посмотреть по SQL Server
Введение в базы данных MS SQL Server
Установка MS SQL Server 2019 Express Edition
Установка SQL Server Management Studio
SQL Server. Создание базы данных, таблиц и связей между ними
SQL Server. Работа с SELECT. Операции удаления, вставки и обновления данных
Изучение SQL с нуля самостоятельно
Пример создания структуры базы данных (на примере социальной сети)
Разработчик SQL, нужны клиенты и заказы?
Выгода от использования Falcon Space
В 2-3 раза экономнее и быстрее, чем заказная разработка
Более гибкая, чем коробочные решения и облачные сервисы
Используйте готовые решения и изменяйте под свои потребности
- Шаг 1. Создать концепт проекта
- Шаг 2. Получить оценку бюджета (КП)
- Шаг 3. Заключить договор
- Шаг 4. Создать совместно техническое задание
- Шаг 5. Поэтапная реализация проекта