Функции vs Хранимые процедуры в SQL Server
Введение
Обычно администраторы баз данных предпочитают хранимые процедуры вместо функций в SQL Server. Хорошая ли это практика?
В этой статье мы расскажем, как создавать хранимые процедуры и функции в SQL Server, и покажем преимущества и недостатки каждой из них. В наших примерах мы будем использовать скалярные функции, определяемые пользователем, или UDF. В дальнейшем мы покажем некоторые функции, возвращающие таблицы. Функции CLR здесь рассматриваться не будут.
Мы рассмотрим следующие темы:
- Создание Hello World в хранимой процедуре и в функции
- Вызов хранимой процедуры и вызов функции
- Использование переменных в хранимой процедуре и функции
- Возможность повторного использования
- Вызов функций/процедур внутри функций/процедур
Начинаем
- Создание Hello World в хранимой процедуре и в функции
Давайте создадим "Hello world" в хранимой процедуре и функции, чтобы проверить, какую из них легче создать.
Сначала мы создадим простую хранимую процедуру с помощью оператора print в SSMS:
CREATE PROCEDURE HelloWorldprocedure AS PRINT 'Hello World'
Выполните код, а затем вызовите хранимую процедуру в SQL:
exec HelloWorldprocedure
Выполнив код, вы увидите сообщение "Hello World":
Теперь попробуем сделать то же самое с функцией:
CREATE FUNCTION dbo.helloworldfunction() RETURNS varchar(20) AS BEGIN RETURN 'Hello world' END
Мы можем вызвать функцию с помощью SELECT.
Функция вернет следующее:
Если сравнить код, то функция требует больше кода для выполнения одного и того же действия. Блоки BEGIN и END являются обязательными в функции, в то время как хранимая процедура не требует их, если она состоит всего из одной строки. В функции обязательно использование аргументов RETURNS и RETURN, в то время как в хранимой процедуре это не обязательно.
В двух словах, хранимая процедура является более гибкой для написания нужного кода, в то время как функции имеют жесткую структуру и функциональность.
2. Вызов хранимой процедуры и вызов функции
Хранимую процедуру можно вызывать разными способами:
exec HelloWorldprocedure execute HelloWorldprocedure execute dbo.HelloWorldprocedure HelloWorldprocedure
Можно вызвать с помощью exec или execute, и даже можно вызвать хранимую процедуру без оператора execute. При этом не обязательно указывать имя схемы.
Функции менее гибкие. Для их вызова необходимо указать схему (это хорошая практика, чтобы избежать конфликтов с другими объектами с тем же именем и другой схемой).
Давайте вызовем функцию без указания схемы:
select helloworldfunction() as regards
На экране появится следующее сообщение:
Msg 195, Level 15, State 10, Line 20 ‘helloworldfunction’ is not a recognized built-in function name
Как вы видите, имя схемы является обязательным для вызова функции:
select dbo.helloworldfunction() as regards
3. Использование переменных в хранимой процедуре и функции
Давайте преобразуем градусы Цельсия в градусы Фаренгейта с помощью хранимых процедур и функций, чтобы увидеть различия. Начнем с хранимой процедуры:
CREATE PROCEDURE CONVERTCELSIUSTOFAHRENHEIT @celsius real as select @celsius*1.8+32 as Fahrenheit
Цельсий является входным параметром, и мы выполняем вычисления в операторе select для преобразования в градусы Фаренгейта.
Если мы вызовем хранимую процедуру, мы получим в результате преобразованный 0°C:
exec CONVERTCELSIUSTOFAHRENHEIT 0
Результат будет равен 32 °F:
Давайте попробуем сделать то же самое с функцией:
CREATE FUNCTION dbo.f_celsiustofahrenheit(@celcius real) RETURNS real AS BEGIN RETURN @celcius*1.8+32 END
Вы можете вызвать созданную функцию следующим образом:
select dbo.f_celsiustofahrenheit(0) as fahrenheit
Мы конвертируем 0 °C в °F. Как видите, код очень прост в обоих случаях.
4. Возможность повторного использования
Главное преимущество функции заключается в том, что ее можно повторно использовать в коде. Например, вы можете сделать следующее:
select CONCAT(dbo.helloworldfunction(),', welcome to sqlshack') Regards
В этом примере мы объединяем функцию из примера 1 со строкой. В результате получается следующее:
Как видите, вы можете легко объединить функцию со строкой. Чтобы сделать нечто подобное с хранимой процедурой в SQL, нам понадобится выходная переменная (output variable) в хранимой процедуре для объединения такой переменной со строкой. Давайте посмотрим на хранимую процедуру:
create procedure outputparam @paramout varchar(20) out as select @paramout='Hello world'
Эта процедура назначает строку Hello Word выходному параметру. Вы можете использовать слово out или output.
Код может показаться простым, но вызов процедуры для использования выходного параметра для конкатенации немного сложнее, чем функция:
declare @message varchar(20) exec outputparam @paramout=@message out select @message as regards select CONCAT(@message,', welcome to sqlshack')
Как вы видите, необходимо объявить новую переменную с именем @message или любым другим именем по вашему усмотрению. Когда вы вызываете хранимую процедуру, вам нужно указать, что это выходной параметр. Преимуществом хранимых процедур является то, что вы можете иметь несколько параметров, в то время как в функциях вы можете вернуть только одну переменную (скалярная функция) или одну таблицу (таблично-значные функции).
5. Вызов функций/процедур внутри функций/процедур
Можем ли мы вызывать хранимые процедуры внутри функции?
Давайте посмотрим:
CREATE FUNCTION dbo.procedureinsidefunction() RETURNS varchar(22) AS BEGIN execute HelloWorldprocedure Declare @hellovar varchar(22)=', welcome to sqlshack' RETURN @hellovar END
Функция будет вызывать процедуру HelloWorld, созданную в разделе 1.
Если мы вызовем функцию, то получим следующее сообщение:
Msg 557, Level 16, State 2, Line 65 Only functions and some extended stored procedures can be executed from within a function.
Как вы видите, вы не можете вызвать функцию из хранимой процедуры. Можете ли вы вызвать функцию из процедуры?
Здесь используется процедура:
END create procedure functioninsideprocedure select dbo.helloworldfunction()
Если мы вызовем хранимую процедуру в SQL, мы сможем проверить, работает она или нет:
exec functioninsideprocedure
Результат отображается следующим образом:
Как вы видите, вы можете вызывать функции внутри хранимой процедуры и не можете вызывать хранимую процедуру внутри функции.
Вы можете вызывать функцию внутри функции. В следующем коде показан простой пример:
CREATE FUNCTION dbo.functioninsidefunction() RETURNS varchar(50) AS BEGIN RETURN dbo.helloworldfunction() END
Мы можем вызвать функцию как обычно:
select dbo.functioninsidefunction() as regards
Можно ли вызывать процедуры внутри других процедур?
Да, можно. Вот пример на эту тему:
create procedure procedureinsideprocedure as execute dbo.HelloWorldprocedure
Вы можете запускать процедуру как обычно:
exec dbo.procedureinsideprocedure
Выводы
Хранимые процедуры в SQL проще создавать, а функции имеют более жесткую структуру и поддерживают меньше функциональности. С другой стороны, вы можете легко использовать результаты работы функции в T-SQL. Мы показали, как объединить функцию со строкой. Манипулировать результатами хранимой процедуры сложнее.
В рамках скалярной функции вы можете вернуть только одну переменную, а в хранимой процедуре - несколько. Однако, чтобы вызвать выходные переменные в хранимой процедуре, необходимо объявить переменные вне процедуры для ее вызова.
Кроме того, вы не можете вызывать процедуры внутри функции. С другой стороны, в процедуре можно вызывать функции и хранимые процедуры.
Наконец, важно упомянуть о некоторых проблемах с производительностью при использовании функций.
Оригинал статьи: https://www.sqlshack.com/functions-vs-stored-procedures-sql-server/#:~:text=In%20a%20function%2C%20it%20is,a%20rigid%20structure%20and%20functionality.