在 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 集區最多支援八個巢狀層級。這項功能與 SQL Server 稍有不同。 SQL Server 中的巢狀層級為 32。

最上層預存程序呼叫等同於巢狀層級 1。

EXEC clean_up 'mytest'

如果預存程序也會進行另一個 EXEC 呼叫，這會將巢狀層級提高到二。

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，巢狀層級會提高到三。

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。您需要追蹤巢狀層級。您不太可能會超過八個巢狀層級限制，但如果會，則需要修改您的程式碼，以讓巢狀層級符合這項限制。

INSERT..EXECUTE

已佈建的 Synapse SQL 集區不允許您透過 INSERT 陳述式取用預存程序的結果集。您可以使用另一個方法。如需範例，請參閱已佈建 Synapse SQL 集區暫存資料表上的文章。

限制

在 Synapse SQL 中不會實作 TRANSACT-SQL 預存程序的部分層面，例如：

功能/選項	已佈建	無伺服器
暫存預存程序	否	是
編號的預存程序	否	否
擴充預存程序	否	否
CLR 預存程序	否	否
加密選項	否	是
複寫選項	否	否
資料表值參數	否	否
唯讀參數	否	否
預設參數	否	是
執行內容	否	否
Return 陳述式	否	是
INSERT INTO .. EXEC	否	是

後續步驟

如需更多開發秘訣，請參閱開發概觀。

共用方式為