Membuat fungsi bernilai tabel

Fungsi bernilai tabel memungkinkan Anda merangkum logika kueri yang kompleks menjadi komponen yang dapat digunakan kembali yang mengembalikan tataan hasil. Anda dapat memanggil fungsi-fungsi ini secara langsung dalam kueri, seperti tabel atau tampilan, membuat kode Anda lebih modular dan dapat dipertahankan.

Saat membuat aplikasi database, Anda sering kali perlu mengambil himpunan data yang difilter atau dihitung berdasarkan parameter input. Fungsi bernilai tabel menyelesaikan masalah ini dengan mengemas logika kueri ke dalam fungsi yang menerima parameter dan mengembalikan tabel. Tidak seperti prosedur tersimpan, Anda dapat menggunakan fungsi bernilai tabel dalam JOIN klausa dan SELECT pernyataan, memberi Anda fleksibilitas untuk memperlakukan hasil fungsi sebagai sumber data.

Memahami jenis fungsi bernilai tabel

SQL Server menyediakan dua jenis fungsi bernilai tabel, masing-masing cocok untuk skenario yang berbeda.

Fungsi bernilai tabel sebaris

Berisi satu SELECT pernyataan dan mengembalikan hasil secara langsung. Dengan fungsi sebaris, Anda tidak menentukan struktur tabel—SQL Server menyimpulkannya dari pernyataan Anda SELECT . Pengoptimal kueri memperlakukan fungsi bernilai tabel sebaris seperti tampilan dengan parameter, sering menghasilkan rencana eksekusi yang lebih baik.

Fungsi nilai tabel multi-pernyataan

Menggunakan blok BEGIN...END dan mendeklarasikan secara eksplisit struktur tabel yang dikembalikan. Jenis ini memberi Anda lebih banyak kontrol saat Anda perlu menjalankan beberapa pernyataan, melakukan perhitungan kompleks, atau membangun tataan hasil secara berulang. Namun, fleksibilitas ini memiliki kompromi performa, karena pengoptimal memperlakukan fungsi-fungsi ini secara berbeda.

Pilihan antara fungsi-fungsi ini tergantung pada persyaratan spesifik Anda. Untuk kueri sederhana dengan parameter, fungsi sebaris memberikan performa yang lebih baik. Saat Anda memerlukan logika prosedural atau beberapa langkah untuk membangun kumpulan hasil Anda, fungsi multi-pernyataan menjadi diperlukan.

Membuat fungsi bernilai tabel sebaris

Fungsi bernilai tabel sebaris menawarkan cara ringkas untuk membuat parameter kueri. Anda menentukannya dengan satu pernyataan RETURN diikuti oleh kueri SELECT.

Contoh berikut menunjukkan fungsi inline yang mengambil pesanan untuk pelanggan tertentu:

CREATE FUNCTION dbo.GetCustomerOrders
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Sekarang Anda dapat menggunakan fungsi ini dalam kueri seperti tabel:

SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';

Fungsi ini menerima parameter ID pelanggan dan hanya mengembalikan pesanan pelanggan tersebut. Anda dapat memfilter lebih lanjut, JOIN, atau menggabungkan hasil sesuai kebutuhan. Pendekatan ini menjaga kueri utama Anda tetap bersih saat mengenkapsulasi logika pemfilteran pelanggan.

Membuat fungsi bernilai tabel dengan variasi pernyataan

Fungsi bernilai tabel dengan banyak pernyataan memberikan fleksibilitas lebih saat Anda perlu melakukan berbagai operasi untuk membangun set hasil.

Pertimbangkan fungsi yang menghitung ringkasan penjualan produk dengan beberapa agregasi:

CREATE FUNCTION dbo.GetProductSalesSummary
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @SalesSummary TABLE
(
    ProductID INT,
    ProductName NVARCHAR(100),
    TotalQuantity INT,
    TotalRevenue DECIMAL(18,2),
    AveragePrice DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @SalesSummary
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) AS TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
        AVG(od.UnitPrice) AS AveragePrice
    FROM Production.Products p
    INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
    INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

Perhatikan bagaimana Anda secara eksplisit mendeklarasikan variabel @SalesSummary tabel dengan kolom dan jenis data tertentu. Isi fungsi menyisipkan data ke dalam variabel tabel ini, lalu mengembalikannya. Struktur ini memungkinkan Anda menambahkan logika pemrosesan tambahan, penanganan kesalahan, atau pernyataan kondisional sesuai kebutuhan.

Menggunakan fungsi bernilai tabel dalam kueri

Fungsi bernilai tabel terintegrasi dengan mulus ke dalam kueri Anda, memungkinkan penerapan pola pengambilan data yang kuat.

Anda dapat menggabungkan hasil fungsi dengan tabel lain:

SELECT 
    c.CustomerName,
    s.ProductName,
    s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;

Operator CROSS APPLY memanggil fungsi untuk setiap baris dari tabel Pelanggan, meskipun dalam contoh ini, parameter fungsi adalah konstanta. Saat Anda meneruskan nilai kolom sebagai parameter, CROSS APPLY menjadi sangat berguna:

SELECT 
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';

Kueri ini mengambil semua pesanan yang telah diselesaikan untuk setiap pelanggan, memperlihatkan bagaimana fungsi bernilai tabel memungkinkan pemrosesan baris demi baris dalam kueri Anda. Fungsi ini bertindak sebagai subkueri yang berkorelasi tetapi dengan keterbacaan dan penggunaan kembali yang lebih baik.

Untuk fungsi bernilai tabel sebaris yang tidak memerlukan evaluasi baris demi baris, sintaks INNER JOIN juga dapat digunakan.

SELECT 
    c.CustomerName,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;

Dengan teknik ini, Anda dapat membangun kueri kompleks dari komponen fungsi yang lebih sederhana dan teruji, meningkatkan pemeliharaan kode dan efisiensi pengembangan.