使用 Azure Synapse Analytics 中的 Synapse SQL 的儲存程序

Tip

Microsoft Fabric Data Warehouse 是一個企業規模的關聯式倉庫，建立在資料湖基礎上，具備未來準備架構、內建 AI 及新功能。如果你是資料倉儲新手，建議先從Fabric Data Warehouse開始。現有的專用 SQL 工作負載可升級至 Fabric，以取得資料科學、即時分析與報告等多項新功能。

Synapse 的 SQL 配置與無伺服器池讓你能將複雜的資料處理邏輯放入 SQL 儲存程序中。儲存程序是封裝你的 SQL 程式碼並把它存放在資料倉儲中靠近資料的好方法。儲存程序幫助開發者模組化解決方案，將程式碼封裝成易於管理的單元，並促進程式碼的重用性。每個儲存程序也能接受參數，使其更具彈性。本文將提供一些在 Synapse SQL 池中實作儲存程序以開發解決方案的建議。

預期內容

Synapse SQL 支援許多 SQL Server 中使用的 T-SQL 功能。更重要的是，有專門的擴展功能可用來最大化解決方案的效能。在本文中，您將了解可以在儲存程序中加入哪些功能。

Note

在程序主體中，你只能使用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 池最多支援八個巢狀層級。此功能與 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

Note

Synapse SQL 目前不支援 @@NESTLEVEL。你需要追蹤巢穴的等級。你不太可能超過八個巢穴等級的限制，但如果超過了，你需要重新設計程式碼，讓巢狀等級符合這個限制。

插入......執行

Provisioned Synapse SQL pool 不允許你用 INSERT 語句來消耗儲存程序的結果集。你也可以用另一種方法。舉例來說，請參閱有關 provisioned Synapse SQL pool 的暫存資料表文章。

局限性

Transact-SQL 儲存程序中有些面向並未用 Synapse SQL 實作，例如：

功能/選項	已佈建	Serverless
暫存儲存程序	No	Yes
有編號的預存程序	No	No
擴展預存程序	No	No
CLR 儲存程序	No	No
加密選項	No	Yes
複製選項	No	No
資料表值參數	No	No
唯讀參數	No	No
預設參數	No	Yes
執行上下文	No	No
回傳敘述	No	Yes
插入...... EXEC	No	Yes

更多開發建議，請參閱開發概覽。

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-05-03