Rekursif UDF ( User Defined Function) Di dalam SQL Server 2000

By: pieter_edison@yahoo.com

Contoh Kasus.

Misalkan dalam suatu database anda, perusahaan anda memiliki kasus seperti contoh dibawah ini.

Perusahaan anda memiliki background penjualan spare part katakanlah pensil.

Untuk Pensil tersebut, perusahaan anda memiliki rekanan supplier dari luar negeri misalkan PT.X dari Korea, PT.Y dari Jepang, dan PT. Z dari supplier lokal.

PT.X yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil korea yang hendak anda jual kembali kepada customer anda.

PT.Y yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil jepang yang hendak anda jual kembali kepada customer anda.

PT.Z yang menjadi supplier anda mengeluarkan bermacam-macam jenis pensil buatan lokal yang hendak anda jual kembali kepada customer anda.

Dan kebetulan juga, perusahaan anda mendevelop aplikasi sendiri, dimana database anda yang anda simpan menggunakan SQL Server tentunya.

Untuk kasus ini, saya ambil contoh sederhana database anda menggunakan table master Pensil atau disingkat TblPensil yang isinya ada field Kode_Supplier, Kode_Pensil dan Nama_Pensil

Kode_Supplier : X à dimana kode_supplier ini memiliki reference ke table supplier

Kode_Pensil : A1

Nama_Pensil: PensilKorea1

Kode_Supplier : Y

Kode_Pensil : Y1

Nama_Pensil: PensilJepang1

Kode_Supplier : Z

Kode_Pensil : Z1

Nama_Pensil: PensilLokal1

Kemudian suatu saat, karena kebijaksaan internal supplier dari Jepang anda, untuk pensil dengan kode_pensil : Y1 dalam database anda, produk tersebut telah tidak diproduksi lagi, dan digantikan dengan produk baru Y2.

Tetapi karena di gudang anda dan stock masih ada di dalam database anda, produk Y1 ini masih tetap dijual kepada customer anda, dan dengan kondisi tertentu anda juga ingin meyakinkan kepada customer anda bahwa produk Y2 ini sebenarnya sama dengan Y1 atau istilah kerennya “common part” atau barang pengganti.

Untuk keperluan ini, anda terpaksa me-redesign database master anda, dan menambahkan field baru after part dengan design sebagai berikut :

Kode_Supplier : X

Kode_Pensil : A1

Nama_Pensil: PensilKorea1

Common_Part : NULL

Kode_Supplier : Y

Kode_Pensil : Y1

Nama_Pensil: PensilJepang1

Common_Part: Y2

Kode_Supplier : Y

Kode_Pensil : Y2

Nama_Pensil: PensilNewJepang2

Common_Part: NULL

Kode_Supplier : Z

Kode_Pensil : Z1

Nama_Pensil: PensilLokal1

Common_Part : NULL

Nah, dari kasus diatas, mungkin saja untuk kasus tertentu, ada banyak common-part yang terjadi dalam kasus anda.

Misalkan seperti dibawah ini :

Kode_Pensil Common_Part

Y1 Y2

Y2 Null

Z1 Z2

Z2 Z3

Z3 Null

Dari kasus diatas, anda ingin menampilkan dalam kode program anda ( client rules ), baik menggunakan program asp atau vb atau sebagainya.

Anda ingin menampilkan ketika user mengetik kode_pensil Z1 maka akan terdapat common-part apa saja yang berhubungan dengan kode pensil Z1 tersebut.

Untuk kasus seperti ini maka, di dalam SQL Server hal ini dapat kita atasi dengan menggunakan User Defined Function SQL Server secara rekursif.

UDF SQL Server secara rekursif maksudnya adalah ketika dalam suatu kasus kita memerlukan pemanggilan kembali fungsi tersebut hal ini dapat kita lakukan dengan memanggil kembali fungsi yang telah kita buat tersebut disertai dengan parameter input yang digunakan juga dalam SQL Server. Dan sebagai tambahan, ketika UDF rekursif tersebut dalam posisi “looping terus-menerus” dalam hal ini, tidak ditemukan data yang sama atau ada isi data yang salah, maka secara otomatis pada looping tertentu, SQL Server akan men-stop hal ini.

Isi table TblPensil Anda :

Kemudian untuk menampilkan User Defined Function yang menampilkan Common-Part Anda :

ALTER function Rekursif(

@Kode_Pensil varchar(10)

)

RETURNS @Tbl TABLE (

Common_Part varchar(10)

)

AS

BEGIN

DECLARE @Common_Part varchar(10)

SET @Common_Part=(SELECT Common_Part FROM TBLPENSIL WHERE Kode_Pensil=@Kode_Pensil)

IF @Common_Part IS NOT NULL AND (@Common_Part >'') and (@Common_Part <> @Kode_Pensil)

INSERT INTO @Tbl

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

UNION

SELECT Common_Part

FROM Rekursif(@Common_Part)

RETURN

END

Saya akan jelaskan logikanya sebagai berikut :

1.Pertama buat suatu fungsi yang isinya mengembalikan suatu table yang tentunya berisi nama common_partnya, dimana fungsi ini memiliki parameter input berupa nama kode_pensil.

n Potongan scriptnya

ALTER function Rekursif(

@Kode_Pensil varchar(10)

)

RETURNS @Tbl TABLE (

Common_Part varchar(10)

)

AS

BEGIN

2. Cari Common-Part dari parameter input yang dimasukkan user tersebut, kemudian tampung hasilnya kedalam variable local tsql anda.

Potongan scriptnya

DECLARE @Common_Part varchar(10)

SET @Common_Part=(SELECT Common_Part FROM TBLPENSIL WHERE Kode_Pensil=@Kode_Pensil)

3. Kemudian cek, jika varibel local common_part tersebut tidak bernilai null atau tidak memiliki nilai karakter kosong atau variable local common_part tersebut tidak sama dengan kode_pensil parameter input kita, maka dipastikan bahwa common_part untuk kode_pensil parameter input kita pasti ada nilainya.

Potongan scriptnya

IF @Common_Part IS NOT NULL AND (@Common_Part >'') and (@Common_Part <> @Kode_Pensil)

4. Jika memiliki nilai, maka kita masukkan ke dalam table kembalian kita common part dimana common part yang kita seleksi adalah common part yang memiliki nilai yang sama dengan variable local common part tersebut.

Potongan scriptnya

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

5. Kemudian kita memanggil fungsi yang telah kita buat tersebut sekali lagi ( disinilah teknik rekursif tersebut, untuk mengecek common part selanjutnya dimana parameter input kita telah berubah dari kode_pensil yang pertama sekali menjadi variable local common_part.

Potongan scriptnya

SELECT Common_Part

FROM Rekursif(@Common_Part)

6. Kemudian untuk teknik terakhir, hasil dari step 4 dan 5 kita gabungkan ke dalam table kembalian kita untuk menghasilkan jumlah record yang ada.

Potongan scriptnya

INSERT INTO @Tbl

SELECT Common_Part

FROM TblPensil

WHERE Common_Part=@Common_Part

UNION

SELECT Common_Part

FROM Rekursif(@Common_Part)

Sehingga kita mendapatkan keluaran sebagai berikut :

Untuk Parameter input Kode_Pensil Y1

SELECT * FROM Rekursif(‘Y1’)

maka akan menghasilkan output :

Common_Part

Y2

Sedangkan Untuk Parameter input Kode_Pensil Z1

SELECT * FROM Rekursif('Z1')

maka akan menghasilkan output :

Common_Part

Z2

Z3

Demikian hasil yang diperoleh dan akhir dari isi tutorial ini.

Best Regards,

Pieter