User-Defined Function (UDF) pada Basis Data Relasional

Membuat, menggunakan, memodifikasi dan menghapus UDF disertai dengan contoh kasus menggunakan SQL Server

Published in

Telematika

7 min readJun 6, 2023

Daftar Isi

⦁ Pendahuluan
⦁ Keuntungan UDF
⦁ UDF pada SQL Server
▹ Membuat atau Mengubah UDF
▹ Menghapus UDF
⦁ Contoh Kasus
⦁ Latihan
⦁ Penutup
⦁ Daftar Pustaka

Pendahuluan

User-defined function (UDF) adalah fungsi yang dibuat oleh pengguna dalam basis data relasional. UDF memungkinkan pengguna untuk membuat fungsi kustom yang dapat digunakan dalam pernyataan SQL untuk memanipulasi data, melakukan perhitungan khusus, atau mengembalikan hasil yang diinginkan.

Berikut adalah beberapa hal yang perlu diketahui tentang UDF:

Pembuatan fungsi kustom. UDF memungkinkan pengguna untuk membuat fungsi kustom sesuai dengan kebutuhan mereka. Fungsi ini bisa berupa fungsi skalar yang menghasilkan nilai tunggal, fungsi tabel yang menghasilkan set data, atau fungsi agregat yang menghasilkan nilai agregat dari sekelompok data.
Penggunaan dalam pernyataan SQL. Setelah UDF dibuat, pengguna dapat menggunakannya dalam pernyataan SQL seperti pernyataan SELECT, INSERT, UPDATE, atau DELETE. UDF dapat digunakan sebagai bagian dari ekspresi, klausa WHERE, klausa HAVING, atau bagian lain dari pernyataan SQL yang memerlukan pemrosesan tambahan atau perhitungan khusus.
Kustomisasi dan reusabilitas. Dengan UDF, pengguna dapat memperluas kemampuan basis data relasional dengan menambahkan logika bisnis atau perhitungan yang kompleks. UDF juga memungkinkan pengguna untuk mengelompokkan logika yang sering digunakan ke dalam satu fungsi yang dapat digunakan ulang dalam berbagai pernyataan SQL.
Bahasa pemrograman. UDF biasanya ditulis menggunakan bahasa pemrograman seperti SQL, PL/SQL (untuk Oracle), T-SQL (untuk Microsoft SQL Server), atau PL/pgSQL (untuk PostgreSQL). Bahasa pemrograman ini menyediakan sintaksis dan fitur yang diperlukan untuk membuat logika kustom dalam fungsi.
Keamanan dan izin. Ketika membuat UDF, penting untuk memperhatikan keamanan dan izin. Administrator basis data dapat mengatur izin akses ke UDF, membatasi penggunaan, dan mengontrol bagaimana fungsi dapat berinteraksi dengan data dalam basis data.

Dengan adanya UDF, pengguna memiliki fleksibilitas lebih dalam memanfaatkan dan memanipulasi data dalam basis data relasional sesuai dengan kebutuhan bisnis atau aplikasi yang mereka kembangkan.

Keuntungan UDF

Menggunakan UDF memberikan beberapa keuntungan, antara lain:

Modularitas dan reusabilitas. Dengan UDF, Anda dapat membagi logika bisnis atau perhitungan khusus ke dalam fungsi terpisah yang dapat digunakan ulang dalam berbagai pernyataan SQL. Ini memungkinkan pengembangan basis data yang modular, dengan kemampuan untuk memperluas dan memodifikasi fungsionalitas basis data dengan mudah tanpa harus mengulangi kode yang sama.
Peningkatan keterbacaan dan pemeliharaan. Dengan menggunakan UDF, kode SQL Anda menjadi lebih bersih, terorganisir, dan mudah dibaca. Logika kompleks atau perhitungan yang berulang dapat ditempatkan dalam fungsi yang jelas dan deskriptif, membuat pernyataan SQL lebih ringkas. Hal ini mempermudah pemeliharaan kode dan pemecahan masalah, serta memungkinkan pengembang lain untuk dengan mudah memahami dan menggunakan fungsi yang telah dibuat.
Pengurangan redundansi dan kesalahan. Dengan UDF, Anda dapat menghindari duplikasi kode yang tidak perlu. Ketika ada perubahan yang diperlukan dalam logika atau perhitungan, Anda hanya perlu memperbarui fungsi UDF yang sesuai, dan perubahan tersebut akan terpengaruh di seluruh pernyataan SQL yang menggunakan fungsi tersebut. Hal ini mengurangi kemungkinan kesalahan manusia dan memastikan konsistensi data di seluruh basis data.
Peningkatan kinerja. Dalam beberapa kasus, penggunaan UDF dapat mengoptimalkan kinerja basis data. Dengan memindahkan logika bisnis kompleks ke dalam fungsi, Anda dapat mengurangi jumlah perhitungan yang dilakukan di pernyataan SQL utama. Ini dapat mengurangi beban pemrosesan basis data dan meningkatkan kecepatan eksekusi query.
Ekspresivitas yang lebih tinggi. UDF memungkinkan Anda untuk melakukan perhitungan yang lebih kompleks, operasi pemrosesan khusus, atau logika bisnis yang tidak mungkin dilakukan dengan perintah SQL standar. Dengan UDF, Anda dapat mengekspresikan kebutuhan bisnis yang lebih spesifik dan menghasilkan yang diinginkan.

UDF pada SQL Server

Tulisan ini membahas bagaimana mendefinisikan, memodifikasi dan menghapus UDF disertai dengan beberapa contoh menggunakan SQL Server.

Membuat dan Mengubah UDF

Untuk fungsi skalar, gunakan sintaks berikut.

-- Transact-SQL Scalar Function Syntax
CREATE [OR ALTER] FUNCTION [schema_name.]function_name
([{@parameter_name [AS] [type_schema_name.]parameter_data_type [NULL]
   [ = default] [READONLY]}
   [ ,...n ]
   ]
)
RETURNS return_data_type
    [WITH <function_option> [ ,...n]]
    [AS]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[;]

Sedangkan sintaks berikut untuk fungsi tabel.

-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [OR ALTER] FUNCTION [schema_name.]function_name
([{@parameter_name [AS] [type_schema_name.]parameter_data_type [NULL]
   [ = default] [READONLY]}
   [ ,...n ]
   ]
)
RETURNS TABLE
    [WITH <function_option> [ ,...n]]
    [AS]
    RETURN [(] select_stmt [)][;]

Arti argumen selengkapnya dapat dibaca di sini.

Berikut adalah contoh bagaimana membuat fungsi skalar untuk mengetahui nama hari suatu tanggal. Fungsi sama sekali tidak memerlukan basis data.

CREATE FUNCTION dbo.Hari (@Tgl Date)
RETURNS VarChar(6) AS 
BEGIN
   DECLARE @HariKe TinyInt
   DECLARE @Hari VarChar(6)
   SET @HariKe = DatePart(dw,@Tgl)
   IF @HariKe = 1
      SET @Hari='Ahad'
   ELSE IF @HariKe = 2
      SET @Hari='Senin'
   ELSE IF @HariKe = 3
      SET @Hari='Selasa'
   ELSE IF @HariKe = 4
      SET @Hari='Rabu'
   ELSE IF @HariKe = 5
      SET @Hari='Kamis'
   ELSE IF @HariKe = 6
      SET @Hari='Jumat'
   ELSE
      SET @Hari='Sabtu'
   RETURN (@Hari)
END

Bila ingin mengubah fungsi, gunakan pernyataan ALTER atau CREATE OR ALTER. Misalnya fungsi di atas diubah menjadi sebagai berikut:

ALTER FUNCTION dbo.Hari (@Tgl Date)
RETURNS VarChar(6) AS
BEGIN
   DECLARE @NoHari TinyInt
   DECLARE @NamaHari VarChar(6)
   SET @NoHari = DatePart(dw,@Tgl)
   SET @NamaHari = CASE @NoHari
      WHEN 1 THEN 'Ahad'
      WHEN 2 THEN 'Senin'
      WHEN 3 THEN 'Selasa'
      WHEN 4 THEN 'Rabu'
      WHEN 5 THEN 'Kamis'
      WHEN 6 THEN 'Jumat' 
      ELSE        'Sabtu'
   END
   RETURN (@NamaHari)
END

Menggunakannya boleh memakai pernyataan PRINT atau SELECT.

SELECT dbo.Hari('1928-10-28') AS Hari
SELECT GetDate() AS Tanggal, dbo.Hari(GetDate()) AS Hari
PRINT dbo.Hari('1945-08-17')

Bila dieksekusi menghasilkan keluaran berikut.

Hari
------
Ahad

(1 row affected)

Tanggal                 Hari
----------------------- ------
2023-06-04 20:25:10.283 Ahad

(1 row affected)

Jumat

Anda boleh menyimpan nilai fungsi ke dalam sebuah variabel.

DECLARE @NamaHari VarChar(6)
SET @NamaHari = dbo.Hari('1945-08-17')
PRINT 'Proklamasi dilaksanakan pada hari ' + @NamaHari

Bila dieksekusi memberikan hasil berikut.

Proklamasi dilaksanakan pada hari Jumat

Anda tidak perlu membuat UDF untuk nama hari dalam bahasa Inggris. Anda dapat menggunakan fungsi bawaan: DATENAME(weekday, date). Misalnya, Anda ingin mengetahui hari tahun baru 2024.

SELECT DateName(weekday,'2024-01-01') AS Day

Bila dieksekusi, hasilnya sebagai berikut.

Day
------------------------------
Monday

(1 row(s) affected

Menghapus UDF

UDF yang ada dapat dihapus dengan menggunakan DROP.

DROP FUNCTION [IF EXISTS] {[schema_name.]function_name} [,...n][;]

Misalnya, menghapus UDF dbo.Udf1 dan dbo.Udf2:

DROP FUNCTION dbo.Udf1, dbo.Udf2

Bila UDF yang akan dihapus tidak ada, muncul pesan kesalahan berikut.

Msg 3701, Level 11, State 5, Line 50
Cannot drop the function 'dbo.Udf1', because it does not exist or you do not have permission.
Msg 3701, Level 11, State 5, Line 50
Cannot drop the function 'dbo.Udf2', because it does not exist or you do not have permission.

Tambahkan IF EXISTS untuk meniadakan pesan kesalahan.

DROP FUNCTION IF EXISTS dbo.Udf1, dbo.Udf2

Contoh Kasus

Berikut adalah model relasional untuk penjualan.

Diagram tersebut merupakan contoh pada artikel sebelumnya.

Fungsi skalar

Menghitung jumlah pelanggan yang belanja pada tanggal tertentu

CREATE OR ALTER FUNCTION JumlahPlg (@Tgl Date)
RETURNS Integer AS 
BEGIN
   DECLARE @Jumlah Integer
   SELECT @Jumlah = Count(Pelanggan.NoHP)
   FROM   Pelanggan INNER JOIN
          Faktur ON Pelanggan.NoHP = Faktur.NoHP
   WHERE  Tgl = @Tgl
   RETURN @Jumlah
END

Kalau ada seorang pelanggan yang belanja dua kali dalam sehari, akan dihitung sebagai dua pelanggan. Jika menginginkan dihitung hanya satu pelanggan, maka kodenya diubah dengan menambahkan DISTINCT ke dalam parameter fungsi Count().

CREATE OR ALTER FUNCTION JumlahPlg (@Tgl Date)
RETURNS Integer AS 
BEGIN
   DECLARE @Jumlah Integer
   SELECT @Jumlah = Count(DISTINCT Pelanggan.NoHP)
   FROM   Pelanggan INNER JOIN
          Faktur ON Pelanggan.NoHP = Faktur.NoHP
   WHERE  Tgl = @Tgl
   RETURN @Jumlah
END

Untuk mengetahui jumlah pelanggan yang belanja kemarin:

SELECT dbo.JumlahPlg (GetDate()-1)

2. Menghitung total rupiah penjualan pada bulan dan tahun tertentu

CREATE OR ALTER FUNCTION dbo.RpJual (@Bulan TinyInt, @Th SmallInt)
RETURNS Integer AS 
BEGIN
   DECLARE @Rp Integer
   SELECT @Rp = sum(Jumlah*HargaSatuan)
   FROM   Faktur INNER JOIN
          DetailFaktur ON Faktur.NoFaktur = DetailFaktur.NoFaktur
   WHERE  Month(Tgl) = @Bulan AND Year(Tgl) = @Th
   RETURN @Rp
END

Nilai yang dikembalikan bertipe integer dengan menganggap tidak ada lagi sen dalam rupiah.

Untuk mengetahui total rupiah penjualan pada April 2023:

PRINT dbo.RpJual (4,2023)

Fungsi tabel

3. Daftar pelanggan yang belanja pada tanggal tertentu

CREATE OR ALTER FUNCTION dbo.Pembeli (@Tgl Date)
RETURNS Table AS 
RETURN
   SELECT Pelanggan.*
   FROM   Pelanggan INNER JOIN
          Faktur ON Pelanggan.NoHP = Faktur.NoHP
   WHERE  Tgl = @Tgl

CREATE OR ALTER FUNCTION dbo.Pembeli (@Tgl Date)
RETURNS Table AS 
RETURN
   SELECT DISTINCT Pelanggan.*
   FROM   Pelanggan INNER JOIN
          Faktur ON Pelanggan.NoHP = Faktur.NoHP
   WHERE  Tgl = @Tgl

Berikut adalah pernyataan untuk mengetahui daftar pelanggan yang belanja pada hari ini.

SELECT *
FROM dbo.Pembeli (GetDate())

4. Daftar hari, tanggal, total rupiah hasil penjualan di antara dua tanggal.

CREATE OR ALTER FUNCTION dbo.RpPenjualan (@TglA Date, @TglB Date)
RETURNS Table AS 
RETURN
   SELECT dbo.Hari(Tgl) AS Hari, Tgl, Sum(Jumlah*HargaSatuan) AS Rp
   FROM   Faktur INNER JOIN
          DetailFaktur ON Faktur.NoFaktur =  DetailFaktur.NoFaktur
   WHERE  Tgl Between @TglA AND @TglB
   GROUP  BY Tgl

Fungsi ini memanggil fungsi dbo.Hari(Tgl). Misalnya, kita ingin mengetahui daftar hari, tanggal, dan total rupiah penjualan antara tanggal 16 April 2023 dan 15 Mei 2023.

SELECT * from dbo.RPPenjualan('2023-04-16','2023-05-15')

Untuk 15 hari terakhir:

SELECT * from dbo.RpPenjualan(GetDate()-15,GetDate())

Latihan

Tuliskan UDF untuk memperoleh:

Daftar barang yang tidak laku dijual pada bulan dan tahun tertentu.
Nama pelanggan yang paling banyak rupiah belanjaannya di antara dua tanggal tertentu.
Daftar nama barang, berapa banyak yang terjual, satuan, dan total rupiah pemasukan dari barang tersebut pada tahun tertentu.
Daftar nama pelanggan, berapa kali ia belanja, dan total rupiah belanjaannya pada tahun tertentu.
Daftar pelanggan yang tidak belanja pada bulan dan tahun tertentu.
Nama barang yang paling laris terjual di antara dua tanggal tertentu.
Daftar nomor HP, nama pelanggan, total rupiah belanjaannya pada bulan dan tahun tertentu.
Daftar nama barang, berapa kali barang itu terjual, dan total rupiah penjualannya pada tahun tertentu.

Penutup

UDF memungkinkan pengguna untuk membuat fungsi kustom sesuai dengan kebutuhan. Fungsi bisa berupa fungsi skalar yang menghasilkan nilai tunggal, fungsi tabel yang menghasilkan set data, atau fungsi agregat yang menghasilkan nilai agregat dari sekelompok data.

Daftar Pustaka

CREATE FUNCTION (Transact-SQL) - SQL Server

Berlaku untuk: Konvensi sintaks Transact-SQL SQL Server Azure SQL Database Azure SQL Managed Instance Membuat fungsi…

learn.microsoft.com

DROP FUNCTION (Transact-SQL) - SQL Server

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform…