你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

使用 Azure Synapse Analytics 中的 Synapse SQL 实现存储过程

项目
06/01/2023

借助 Synapse SQL 预配池和无服务器池，可以将复杂的数据处理逻辑放入 SQL 存储过程中。存储过程很适合用于封装 SQL 代码并将其存储在数据仓库中的数据附近。通过将代码封装成可管理的单位，促使代码有更大的可重复使用性，存储过程帮助开发人员将其解决方案模块化。每个存储过程还可接受参数，使其更具弹性。在本文中，你将了解一些在 Synapse SQL 池中实现存储过程以开发解决方案的技巧。

期望

Synapse SQL 支持 SQL Server 中使用的许多 T-SQL 功能。更重要的是，可使用特定的横向扩展功能将解决方案的性能最大化。本文将介绍可以放入存储过程的功能。

注意

在过程主体中，只能使用 Synapse SQL 外围应用支持的功能。查看此文，以识别可在存储过程中使用的对象、语句。这些文章中的示例使用的是无服务器外围应用和专用外围应用都可用的通用功能。请参阅本文末尾的 Synapse SQL 预配池和无服务器池中的其他限制。

为了保持 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 代码与提交的查询不同。

封装验证规则

使用存储过程可以在 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.

嵌套存储过程

如果存储过程调用其他存储过程或执行动态 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 池最多支持 8 个嵌套级别。此功能与 SQL Server 略有不同。 SQL Server 中的嵌套级别为 32。

最上层存储过程调用等同于嵌套级别 1。

EXEC clean_up 'mytest'

如果存储过程还调用另一个 EXEC，则嵌套级别将增加到 2。

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

如果第二个过程随后执行某种动态 SQL，则嵌套级别将增加到 3。

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。需要跟踪嵌套级别。不太可能超过 8 个嵌套级别的限制，但如果超过了，则需要重新修改代码，以使它符合限制内的嵌套级别。

INSERT..EXECUTE

Synapse SQL 预配池不允许通过 INSERT 语句使用存储过程的结果集。你可以使用替代方法。有关示例，请参阅介绍 Synapse SQL 预配池的临时表的文章。

限制

Synapse SQL 中未实现 Transact-SQL 存储过程的某些方面，比如：

功能/选项	已预配	无服务器
临时存储过程	否	是
带编号的存储过程	否	否
扩展的存储过程	否	否
CLR 存储过程	否	否
加密选项	否	是
复制选项	否	否
表值参数	否	否
只读参数	否	否
默认参数	否	是
执行上下文	否	否
Return 语句	否	是
INSERT INTO .. EXEC	否	是

后续步骤

有关更多开发技巧，请参阅开发概述。

通过