Uložené procedury využívající Synapse SQL ve službě Azure Synapse Analytics

Vyhrazené a bezserverové fondy Synapse SQL umožňují přenesení složité logiky zpracování dat do uložených procedur SQL. Uložené procedury představují skvělý způsob zapouzdření kódu SQL a jeho uložení blízko dat v datovém skladu. Uložené procedury pomáhají vývojářům modularizovat svá řešení zapouzdřením kódu do spravovatelných jednotek a usnadněním větší použitelnosti kódu. Každá uložená procedura může také přijímat parametry, aby byly ještě flexibilnější. V tomto článku najdete několik tipů pro implementaci uložených procedur ve fondu Synapse SQL pro vývoj řešení.

Co očekávat

Synapse SQL podporuje řadu funkcí T-SQL, které se používají na SQL Serveru. Důležitější je, že existují specifické funkce horizontálního navýšení kapacity, které můžete použít k maximalizaci výkonu vašeho řešení. V tomto článku se dozvíte o funkcích, které můžete umístit do uložených procedur.

Poznámka:

V těle procedury můžete použít pouze funkce, které jsou podporovány v oblasti plochy Synapse SQL. Projděte si tento článek a identifikujte objekty, příkaz, který lze použít v uložených procedurách. Příklady v těchto článcích používají obecné funkce, které jsou k dispozici v bezserverové i vyhrazené ploše. Podívejte se na další omezení ve zřízených a bezserverových fondech Synapse SQL na konci tohoto článku.

Kvůli zachování škálování a výkonu fondu SQL existují také některé funkce a vlastnosti, které mají odlišné chování, a jiné, které nejsou podporovány.

Uložené procedury v Synapse SQL

V následujícím příkladu můžete zobrazit postupy, které odstraňují externí objekty, pokud existují v databázi:

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

Tyto postupy lze provést pomocí příkazu EXEC , kde můžete zadat název procedury a parametry:

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 poskytuje zjednodušenou a efektivní implementaci uložené procedury. Největší rozdíl oproti SQL Serveru spočívá v tom, že uložená procedura není předem zkompilovaný kód. V datových skladech je doba kompilace malá ve srovnání s dobou potřebnou ke spouštění dotazů na velké objemy dat. Je důležitější zajistit, aby byl kód uložené procedury správně optimalizovaný pro velké dotazy. Cílem je ušetřit hodiny, minuty a sekundy, nikoli milisekundy. Proto je vhodnější uvažovat o uložených procedurách jako kontejnerech pro logiku SQL.

Když Synapse SQL spustí uloženou proceduru, příkazy SQL se analyzují, překládají a optimalizují za běhu. Během tohoto procesu se každý příkaz převede na distribuované dotazy. Kód SQL, který se provádí s daty, se liší od odeslaného dotazu.

Zapouzdření ověřovacích pravidel

Uložené procedury umožňují vyhledat logiku ověřování v jednom modulu uloženém v databázi SQL. V následujícím příkladu vidíte, jak ověřit hodnoty parametrů a změnit jejich výchozí hodnoty.

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

Logika v postupu SQL ověří vstupní parametry při volání procedury.


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.

Vnoření uložených procedur

Pokud uložené procedury volají jiné uložené procedury nebo spouštějí dynamické SQL, pak se vnitřní uložená procedura nebo vyvolání kódu označuje jako vnořené. Příklad vnořené procedury je předvedený v následujícím kódu.

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

Tento postup přijímá parametr, který představuje nějaký název, a potom volá jiné procedury, které odstraňují objekty s tímto názvem. Zpool Synapse SQL podporuje maximálně osm úrovní vnoření. Tato funkce se mírně liší od SQL Serveru. Úroveň vnoření v SQL Serveru je 32.

Volání uložené procedury na nejvyšší úrovni odpovídá úrovni zanoření 1.

EXEC clean_up 'mytest'

Pokud uložená procedura také provede další volání EXEC, úroveň vnoření se zvýší na druhou úroveň.

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

Pokud druhá procedura provede dynamické SQL, úroveň vnoření se zvýší na tři.

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

Poznámka:

Synapse SQL v současné době nepodporuje @@NESTLEVEL. Potřebujete sledovat úroveň vnoření. Je nepravděpodobné, že byste překročili limit osmi úrovní vnoření, ale pokud to uděláte, musíte kód přepracovat tak, aby odpovídal úrovním vnoření v rámci tohoto limitu.

VLOŽIT.. SPUSTIT

Zřízený fond Synapse SQL neumožňuje využívat sadu výsledků uložené procedury pomocí příkazu INSERT. Existuje alternativní přístup, který můžete použít. Příklad najdete v článku o dočasných tabulkách pro zřízený fond Synapse SQL.

Omezení

Některé aspekty uložených procedur Transact-SQL nejsou ve službě Synapse SQL implementované, například:

Funkce/možnost	Alokováno	Bezserverová architektura
Dočasné uložené procedury	Ne	Ano
Číslované uložené procedury	Ne	Ne
Rozšířené uložené procedury	Ne	Ne
Uložené procedury CLR	Ne	Ne
Možnost šifrování	Ne	Ano
Možnost replikace	Ne	Ne
Parametry s tabulkovými hodnotami	Ne	Ne
Parametry jen pro čtení	Ne	Ne
Výchozí parametry	Ne	Ano
Kontexty spuštění	Ne	Ne
Příkaz return	Ne	Ano
VLOŽIT DO .. VYKONAVATEL	Ne	Ano

Další tipy pro vývoj najdete v přehledu vývoje.

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-04-03