Prosedur tersimpan menggunakan Synapse SQL di Azure Synapse Analytics

Kumpulan Synapse SQL yang tersedia dan tanpa server memungkinkan Anda untuk menempatkan logika pemrosesan data yang kompleks ke dalam prosedur tersimpan SQL. Prosedur tersimpan adalah cara yang bagus untuk merangkum kode SQL Anda dan menyimpannya dekat dengan data Anda di gudang data. Prosedur tersimpan membantu pengembang memodulasi solusi dengan merangkum kode ke dalam unit yang dapat dikelola, dan memfasilitasi penggunaan ulang kode yang lebih besar. Setiap prosedur tersimpan juga dapat menerima parameter untuk membuatnya lebih fleksibel. Dalam artikel ini Anda akan menemukan beberapa tips untuk menerapkan prosedur tersimpan di kumpulan Synapse SQL untuk mengembangkan solusi.

Hal yang diharapkan

Synapse SQL mendukung banyak fitur T-SQL yang digunakan di SQL Server. Lebih penting lagi, ada fitur khusus yang dapat Anda gunakan untuk memaksimalkan performa solusi Anda. Dalam artikel ini, Anda akan mempelajari tentang fitur yang dapat Anda tempatkan dalam prosedur tersimpan.

Catatan

Dalam badan prosedur, Anda hanya dapat menggunakan fitur yang didukung di area permukaan Synapse SQL. Tinjau artikel ini untuk mengidentifikasi objek, pernyataan yang dapat digunakan dalam prosedur tersimpan. Contoh dalam artikel ini menggunakan fitur generik yang tersedia di area permukaan tanpa server dan permukaan khusus. Lihat batasan tambahan di kumpulan Synapse SQL yang tersedia dan tanpa server di akhir artikel ini.

Untuk mempertahankan skala dan performa kumpulan SQL, ada juga beberapa fitur dan fungsionalitas yang memiliki perbedaan perilaku dan hal lain yang tidak didukung.

Prosedur tersimpan di Synapse SQL

Dalam contoh berikut, Anda bisa melihat prosedur yang menghilangkan objek eksternal jika ada di database:

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

Prosedur ini dapat dijalankan menggunakan pernyataan EXEC di mana Anda dapat menentukan nama prosedur dan parameter:

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 menyediakan implementasi prosedur yang efisien dan disederhanakan. Perbedaan terbesar dibandingkan dengan SQL Server adalah bahwa prosedur tersimpan bukan kode yang telah dikompilasi sebelumnya. Di gudang data, waktu kompilasi pendek dibandingkan dengan waktu yang diperlukan untuk menjalankan kueri terhadap volume data besar. Lebih penting untuk memastikan kode prosedur yang disimpan dioptimalkan dengan benar untuk kueri besar. Tujuannya adalah menghemat jam, menit, dan detik, bukan milidetik. Oleh karena itu, akan lebih membantu untuk memikirkan prosedur tersimpan sebagai kontainer untuk logika SQL.

Ketika Synapse SQL menjalankan prosedur tersimpan, pernyataan SQL diurai, diterjemahkan, dan dioptimalkan selama durasi. Selama proses ini, setiap pernyataan dikonversi menjadi kueri terdistribusi. Kode SQL yang dijalankan terhadap data berbeda dengan kueri yang dikirimkan.

Enkapsulasi aturan validasi

Prosedur tersimpan memungkinkan Anda menemukan logika validasi dalam satu modul yang disimpan dalam database SQL. Dalam contoh berikut, Anda dapat melihat cara memvalidasi nilai parameter dan mengubah nilai defaultnya.

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 dalam prosedur sql akan memvalidasi parameter input ketika prosedur dipanggil.

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.

Prosedur tersimpan berlapis

Ketika prosedur tersimpan memanggil prosedur tersimpan lain, atau menjalankan SQL dinamis, maka prosedur atau pemanggilan kode tersimpan bagian dalam dikatakan berlapis. Contoh prosedur berlapis diperlihatkan dalam kode berikut:

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

Prosedur ini menerima parameter yang mewakili beberapa nama lalu memanggil prosedur lain untuk menghilangkan objek dengan nama ini. Kumpulan Synapse SQL mendukung maksimum delapan level berlapis. Kemampuan ini sedikit berbeda dari SQL Server. Tingkat lapisan di SQL Server adalah 32.

Panggilan prosedur tingkat atas yang disimpan sama dengan lapis tingkat 1.

EXEC clean_up 'mytest'

Jika prosedur tersimpan juga membuat panggilan EXEC lain, tingkat lapisan meningkat menjadi dua.

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

Jika prosedur kedua kemudian menjalankan beberapa SQL dinamis, tingkat lapisan meningkat menjadi tiga.

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

Catatan

Synapse SQL saat ini tidak mendukung @@NESTLEVEL. Anda perlu melacak tingkat lapisan. Sepertinya tidak mungkin untuk melebihi batas delapan tingkat lapisan, tetapi jika Anda melakukannya, Anda perlu mengerjakan ulang kode agar sesuai dengan tingkat berlapis dalam batas ini.

INSERT..EXECUTE

Kumpulan Synapse SQL yang tersedia tidak mengizinkan Anda untuk menghabiskan serangkaian hasil prosedur tersimpan dengan pernyataan INSERT. Ada pendekatan alternatif yang dapat Anda gunakan. Misalnya, lihat artikel tentang tabel sementara untuk kumpulan Synapse SQL yang tersedia.

Batasan

Ada beberapa aspek prosedur tersimpan Transact-SQL yang tidak diterapkan dalam Synapse SQL, seperti:

Fitur/opsi	Tersedia	Tanpa server
Prosedur tersimpan sementara	Tidak	Ya
Prosedur tersimpan bernomor	Tidak	Tidak
Prosedur tersimpan diperpanjang	Tidak	Tidak
Prosedur tersimpan CLR	Tidak	Tidak
Opsi enkripsi	Tidak	Ya
Opsi replikasi	Tidak	Tidak
Parameter bernilai tabel	Tidak	Tidak
Parameter baca-saja	Tidak	Tidak
Parameter default	Tidak	Ya
Konteks eksekusi	Tidak	Tidak
Pernyataan pengembalian	Tidak	Ya
INSERT INTO .. EXEC	Tidak	Ya

Langkah berikutnya

Untuk tips pengembangan selengkapnya, buka gambaran pengembangan.