Iterasi pada record data dengan menggunakan LOOP dan CURSOR pada Stored Procedure.

Terkadang untuk beberapa kasus, kita sangat membutuhkan untuk melakukan iterasi dan mengevaluasi terhadap kumpulan data dalam tabel. Setelah melakukan evaluasi, kemudian kita mengembalikannya dalam sebuah tabel, sehingga menjadi sebuah representasi data yang kita butuhkan sesuai kondisi yang diinginkan. Pada sampel sebelumnya di artikel Dasar-dasar Stored Procedure, saya telah memberikan contoh stored procedure dengan nama sp_laporanglobal. Pada contoh ini, saya melakukan pemanggilan berulang terhadap sp_laporan sesuai dengan kriteria kategori barang. Pada prinsipnya, tabel kategori sudah ada di database ini (Northwind), sehingga untuk efektifitas pengkodingan, seharusnya saya melakukan iterasi terhadap tabel kategori/Categories.

Saya bisa melakukan iterasi dengan dua cara, yaitu dengan WHILE LOOP dan CURSOR. Saya akan mencoba cara pertama, yaitu WHILE LOOP. Untuk melakukan iterasi dengan WHILE LOOP, saya membutuhkan sebuah tabel sementara yang datanya diambil dari tabel Categories. Saya juga membutuhkan pengkondisian agar WHILE LOOP ini bisa berjalan satu demi satu pada row yang ada di tabel sementara. Disini saya mencoba untuk melakukan SELECT dan DELETE terhadap tabel sementara.

WHILE LOOP

Kode perubahan Stored Procedure:

ALTER PROCEDURE [dbo].[sp_laporanglobal]
    AS
    BEGIN
    CREATE TABLE #laporanglobal
    
    Nmr INT IDENTITY(1,1),
    no_urut INT,
    ProductName VARCHAR(50),
    UnitPrice MONEY
    )
    
    --lakukan iterasi dengan menggunakan WHILE LOOP
    DECLARE @categoryID INT
    
    --Ambil data dari tabel Categories, masukkan ke tabel sementara
    SET ROWCOUNT 0
    SELECT CategoryID INTO #tempCategories FROM Categories
    SET ROWCOUNT 1
    select @categoryID = CategoryID from #tempCategories
    
    WHILE @@rowcount <> 0
    begin
    SET ROWCOUNT 0
    INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan @CategoryID
    DELETE #tempCategories WHERE CategoryID = @CategoryID
    SET ROWCOUNT 1
    select @categoryID = CategoryID from #tempCategories
    end
    
    SET ROWCOUNT 0
    SELECT * FROM #laporanglobal
    DROP TABLE #laporanglobal
END

Perubahan yang saya lakukan pada script di atas adalah sebagai berikut:

• Memasukkan data CategoryID ke dalam #tempCategories.
• Ada perintah SET ROWCOUNT, perintah ini sama dengan perintah TOP. Saya membuat SET ROWCOUNT 1 untuk memastikan bahwa row yang diambil adalah satu baris saja.
• Kemudian saya assign variabel @categoryID agar mempunyai nilai yang sama dengan CategoryID pada sebuah row di #tempCategories
• Saya lakukan WHILE LOOP, kondisi WHILE LOOP adalah mengevaluasi @@rowcount. Nilai dari @@rowcount ini adalah nilai yang diambil dari sebuah statement akhir. Persis di atas WHILE, ada sebuah statement: “select @categoryID = CategoryID from #tempCategories”, ini akan mengembalikan nilai 1 jika record di #tempCategories masih ada.
• Didalam body WHILE LOOP, saya lakukan INSERT statement ke dalam tabel #laporanglobal. Setelah melakukan INSERT, kemudian saya DELETE baris ini, sehingga pada pengulangan selanjutnya baris di bawahnya akan dievaluasi. Jika sudah tidak ada row/baris lagi, maka nilai @@rowcount akan 0 dan iterasi akan berhenti.

 

CURSOR

Pada CURSOR, kita bisa melakukan fetching data satu per satu terhadap kumpulan data yang kita punya. Dengan kemampuan fetching data dari CURSOR, kita tidak membutuhkan sebuah tabel temporary. Pada saat fetching, cursor menyimpan data hasil fetch ke dalam sebuah variable.

Lebih jelas nya kita lihat saja perubahan script di bawah ini:

CREATE PROCEDURE [dbo].[sp_laporanglobal2]
AS
BEGIN
      SET NOCOUNT ON;
      CREATE TABLE #laporanglobal
      (
            Nmr INT IDENTITY(1,1),
            no_urut INT,
            ProductName VARCHAR(50),
            UnitPrice MONEY
      )

      --membuat CURSOR
      DECLARE @categoryID INT
      DECLARE myCursor CURSOR
      FOR SELECT CategoryID FROM Categories
      OPEN myCursor
      FETCH NEXT FROM myCursor
      INTO @categoryID
      WHILE @@FETCH_STATUS = 0
      BEGIN
            INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan @CategoryID
            FETCH NEXT FROM myCursor
            INTO @categoryID
      END

      SELECT * FROM #laporanglobal
      DROP TABLE #laporanglobal
END

Hal pertama untuk operasi CURSOR adalah mendeklarasikan cursor itu sendiri. DECLARE myCursor CURSOR, adalah pendeklarasian sebuah Cursor dengan ID myCursor. Kemudian ada keyword FOR, disini adalah SELECT statement yang kita inginkan untuk FETCH data. Apapun SELECT statement bisa dibuat disini.  Selanjutnya kita buka Cursor tersebut dengan keyword OPEN. Setelah itu barulah kita bisa melakukan FETCH data. Pada saat melakukan WHILE, kondisi yang kita evaluasi adalah @@FETCH_STATUS. Nilai 0 dari @@FETCH_STATUS mengindikasikan statemen FETCH sukses/berhasil. Jika selain dari 0, maka kondisi WHILE sudah tidak memenuhi persyaratan dan FETCH selesai.

SOLVED CASE

Berikut beberapa case yang bisa diselesaikan dengan CURSOR/WHILE LOOP.

Case 1:

Pada database Northwind, terdapat tabel Order Details, yang mana pada tabel ini terdapat Quantity dan ProductID. Untuk membuat pelaporan yang baik, diminta untuk membuatkan saldo penjualan per transaksi. Jika pada hari ini terjadi 10 order dan kemarin ada 5 order, maka saldo jual hari ini adalah 15. Demikian seterusnya.

Pemecahan masalah:

Hasil yang diharapkan:

Sampel di atas bisa juga diselesaikan dengan keyword OVER pada SQL Server, tapi karena kita lagi berbicara masalah CURSOR, saya coba menyelesaikan dengan CURSOR.

Case 2:

Pada sebuah bisnis proses, jika terjadi sebuah transaksi yang dilakukan oleh seorang member, maka upline dari member tersebut mendapatkan rebate hasil dari transaksi si member. Demikian juga upline dari si upline, sampai posisi upline paling atas. Nilai rebate anggap saja seragam pada semua tingkatan upline. Bagaimana solusi dari permasalahan ini?

Solusi:

 

Daftar member di tblMember:

Dari gambar di atas, jika member 8 melakukan transaksi, maka member 8 akan bertambah saldo rebate untuk diri sendiri, kemudian saldo rebate juga akan bertambah untuk member 7, 6, 5, 4 dan 1. Jika member 1 melakukan transfer, maka rebate bertambah untuk diri sendiri, dikarenakan tidak ada upline dari member tersebut.

Saya membutuhkan bantuan trigger pada tabel tblTransaksi. Trigger ini akan memicu pemanggilan Stored Procedure di atas, berikut script untuk trigger nya:

Ketika saya memasukkan data pada tblTransaksi, maka trigger akan aktif, memanggil SP, kemudian SP akan menyimpan rebate dari si member dan upline2 nya. Berikut hasil pada tabel saldo, jika saya memasukkan transaksi untuk member id 8:

Dari gambar di atas, bisa dilihat jika SP kita sudah sesuai dan bisa menjadi solusi untuk permasalahan ini. Biasanya case seperti ini terdapat pada bisnis Multi Level Marketing.

Demikianlah tutorial dengan contoh kasus kali ini. Semoga bermanfaat.