Хранимые процедуры с помощью Synapse SQL в Azure Synapse Analytics

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

Чего следует ожидать

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

Примечание.

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

Для поддержания масштаба и производительности пула SQL существуют также некоторые функции и возможности, которые имеют поведенческие различия и другие, которые не поддерживаются.

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

В следующем примере можно увидеть процедуры, которые сбрасывают внешние объекты, если они существуют в базе данных:

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_tables WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL TABLE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_file_format_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_file_formats WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL FILE FORMAT ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END
GO
CREATE PROCEDURE drop_external_data_source_if_exists @name SYSNAME
AS BEGIN
    IF (0 <> (SELECT COUNT(*) FROM sys.external_data_sources WHERE name = @name))
    BEGIN
        DECLARE @drop_stmt NVARCHAR(200) = N'DROP EXTERNAL DATA SOURCE ' + @name; 
        EXEC sp_executesql @tsql = @drop_stmt;
    END
END

Эти процедуры можно выполнить с помощью EXEC инструкции, в которой можно указать имя и параметры процедуры:

EXEC drop_external_table_if_exists 'mytest';
EXEC drop_external_file_format_if_exists 'mytest';
EXEC drop_external_data_source_if_exists 'mytest';

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

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

Encapsulate validation rules

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

CREATE PROCEDURE count_objects_by_date_created 
                            @start_date DATETIME2,
                            @end_date DATETIME2
AS BEGIN 

    IF( @start_date >= GETUTCDATE() )
    BEGIN
        THROW 51000, 'Invalid argument @start_date. Value should be in past.', 1;  
    END

    IF( @end_date IS NULL )
    BEGIN
        SET @end_date = GETUTCDATE();
    END

    IF( @start_date >= @end_date )
    BEGIN
        THROW 51000, 'Invalid argument @end_date. Value should be greater than @start_date.', 2;  
    END

    SELECT
         year = YEAR(create_date),
         month = MONTH(create_date),
         objects_created = COUNT(*)
    FROM
        sys.objects
    WHERE
        create_date BETWEEN @start_date AND @end_date
    GROUP BY
        YEAR(create_date), MONTH(create_date);
END

Логика в процедуре SQL проверяет входные параметры при вызове процедуры.


EXEC count_objects_by_date_created '2020-08-01', '2020-09-01'

EXEC count_objects_by_date_created '2020-08-01', NULL

EXEC count_objects_by_date_created '2020-09-01', '2020-08-01'
-- Error
-- Invalid argument @end_date. Value should be greater than @start_date.

EXEC count_objects_by_date_created '2120-09-01', NULL
-- Error
-- Invalid argument @start_date. Value should be in past.

Nesting stored procedures

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

CREATE PROCEDURE clean_up @name SYSNAME
AS BEGIN
    EXEC drop_external_table_if_exists @name;
    EXEC drop_external_file_format_if_exists @name;
    EXEC drop_external_data_source_if_exists @name;
END

Эта процедура принимает параметр, представляющий некоторое имя, а затем вызывает другие процедуры для удаления объектов с таким именем. Synapse SQL pool supports a maximum of eight nesting levels. Эта возможность немного отличается от SQL Server. The nest level in SQL Server is 32.

The top-level stored procedure call equates to nest level 1.

EXEC clean_up 'mytest'

If the stored procedure also makes another EXEC call, the nest level increases to two.

CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

If the second procedure then executes some dynamic SQL, the nest level increases to three.

CREATE PROCEDURE drop_external_table_if_exists @name SYSNAME
AS BEGIN
    /* See full code in the previous example */
    EXEC sp_executesql @tsql = @drop_stmt;  -- This call is nest level 3
END
GO
CREATE PROCEDURE clean_up @name SYSNAME
AS
    EXEC drop_external_table_if_exists @name  -- This call is nest level 2
GO
EXEC clean_up 'mytest'  -- This call is nest level 1

Примечание.

Synapse SQL в настоящее время не поддерживает @@NESTLEVEL. Необходимо отслеживать уровень гнезда. It is unlikely for you to exceed the eight nest level limit, but if you do, you need to rework your code to fit the nesting levels within this limit.

INSERT..EXECUTE

Выделенный пул Synapse SQL не позволяет вам использовать результирующий набор хранимой процедуры с оператором INSERT. Существует альтернативный подход, который можно использовать. См. пример в статье о временных таблицах для выделенного пула SQL Synapse.

Ограничения

Существуют некоторые аспекты Transact-SQL хранимых процедур, которые не реализованы в Synapse SQL, например:

Компонент или параметр	Provisioned	Бессерверные системы
Temporary stored procedures	нет	Да
Numbered stored procedures	нет	нет
Extended stored procedures	нет	нет
CLR stored procedures	нет	нет
Параметр шифрования	нет	Да
Параметр репликации	нет	нет
Параметры с табличным значением	нет	нет
Параметры только для чтения	нет	нет
Параметры по умолчанию	нет	Да
Контексты выполнения	нет	нет
Return statement	нет	Да
INSERT INTO .. EXEC	нет	Да

Дополнительные советы по разработке приведены в обзоре разработки.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2025-04-12