Bagikan melalui

Prosedur tersimpan menggunakan Synapse SQL di Azure Synapse Analytics

Kumpulan Synapse SQL yang disediakan dan tanpa server memungkinkan Anda 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 mereka dengan merangkum kode ke dalam unit yang dapat dikelola, dan memfasilitasi penggunaan kembali 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.

Apa yang diharapkan

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

Nota

Dalam isi prosedur, Anda hanya dapat menggunakan fitur yang didukung di area permukaan Synapse SQL. Tinjau artikel ini untuk mengidentifikasi objek dan pernyataan yang dapat digunakan dalam prosedur tersimpan. Contoh dalam artikel ini menggunakan fitur-fitur generik yang tersedia baik di lingkungan tanpa server maupun khusus. Lihat batasan lain dalam 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 lainnya 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 EXEC pernyataan 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 tersimpan yang disederhanakan dan efisien. Perbedaan terbesar dibandingkan dengan SQL Server adalah bahwa prosedur tersimpan bukan kode yang telah dikompilasi sebelumnya. Di gudang data, waktu kompilasi kecil dibandingkan dengan waktu yang diperlukan untuk menjalankan kueri terhadap volume data besar. Lebih penting untuk memastikan kode prosedur tersimpan dioptimalkan dengan benar untuk kueri besar. Tujuannya adalah untuk menghemat jam, menit, dan detik, bukan milidetik. Oleh karena itu lebih membantu untuk memikirkan prosedur tersimpan sebagai kontainer untuk logika SQL.

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

Merangkum 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.

Penyusunan prosedur tersimpan bersarang

Ketika prosedur tersimpan memanggil prosedur tersimpan lainnya, atau menjalankan SQL dinamis, prosedur tersimpan dalam atau pemanggilan kode 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 tingkat bersarang. Kemampuan ini sedikit berbeda dari SQL Server. Tingkat sarang di SQL Server adalah 32.

Panggilan prosedur tersimpan tingkat atas sama dengan tingkat sarang 1.

EXEC clean_up 'mytest'

Jika prosedur tersimpan juga melakukan panggilan EXEC lain, tingkat sarang 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 sarang 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

Nota

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

MASUKKAN.. EKSEKUSI

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

Keterbatasan

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

Fitur/opsi Disediakan Tanpa server
Prosedur tersimpan sementara Tidak Ya
Prosedur tersimpan bernomor Tidak Tidak
Prosedur tersimpan yang diperluas Tidak Tidak
Prosedur tersimpan CLR Tidak Tidak
Opsi enkripsi Tidak Ya
Opsi replikasi Tidak Tidak
Parameter bertipe tabel Tidak Tidak
Parameter baca-saja Tidak Tidak
Parameter standar Tidak Ya
Konteks eksekusi Tidak Tidak
Pernyataan pengembalian Tidak Ya
SISIPKAN KE DALAM .. EXEC Tidak Ya

Konten terkait

Untuk tips pengembangan selengkapnya, lihat ringkasan pengembangan.

  • Last updated on