Belajar SQL: CASE Clause – Logika Percabangan

Belajar SQL Logika Percabangan Case clause

Belajar SQL Logika Percabangan Case clauseSalah satu fungsi atau clause yang kurang begitu populer di SQL adalah CASE clause. Salah satu sebabnya adalah umunya SQL lebih dikenal hanya untuk proses SELECT, INSERT, UPDATE dan DELETE saja, dan karena CASE clause punya fungsi yang sama dengan IF di semua bahasa pemrograman yang ada, misalkan PHP, Java, dan sebagainya. Untuk itu saya akan berikan contoh sederhana penggunaan SQL untuk melakukan perhitungan dengan sedikit logika IF sederhana.

Misalkan kita mempunya contoh data seperti berikut ini

mysql> select * from penggunaan;
+----------+-------+--------+-------+-------+-------+-------+--------+
| kegiatan | senin | selasa | rabu  | kamis | jumat | sabtu | minggu |
+----------+-------+--------+-------+-------+-------+-------+--------+
| olahraga | ya    | tidak  | ya    | tidak | ya    | tidak | tidak  |
| diskusi  | tidak | ya     | tidak | tidak | tidak | ya    | tidak  |
| rapat    | ya    | tidak  | tidak | tidak | tidak | tidak | tidak  |
| ulangan  | tidak | ya     | ya    | ya    | tidak | tidak | tidak  |
+----------+-------+--------+-------+-------+-------+-------+--------+
4 rows in set (0.00 sec)

ketika kita ingin tahu berapa banyak dalam seminggu kegiatan-kegiatan tersebut di atas dilakukan atau tidak. Sebagai contoh untuk kegiatan OLAHRAGA dalam satu minggu dilakukan sebanyak 3 kali dan tidak dilakukan sebanyak 4 kali. jika kita bisa dengan mudah menggunakan PHP dan looping untuk menghitungnya menggunakan logika percabangan IF, misalkan.

if ($row['senin']=='ya')
    $dilakukan += 1;
else
    $tidak_dilakukan += 1;

Kalau menggunakan SQL caranya adalah seperti ini

mysql> select penggunaan.*
    -> , case when senin='ya' then 1 else 0 end
    -> + case when selasa='ya' then 1 else 0 end
    -> + case when rabu='ya' then 1 else 0 end
    -> + case when kamis='ya' then 1 else 0 end
    -> + case when jumat='ya' then 1 else 0 end
    -> + case when sabtu='ya' then 1 else 0 end
    -> + case when minggu='ya' then 1 else 0 end as dilakukan
    -> , case when senin='tidak' then 1 else 0 end
    -> + case when selasa='tidak' then 1 else 0 end
    -> + case when rabu='tidak' then 1 else 0 end
    -> + case when kamis='tidak' then 1 else 0 end
    -> + case when jumat='tidak' then 1 else 0 end
    -> + case when sabtu='tidak' then 1 else 0 end
    -> + case when minggu='tidak' then 1 else 0 end as tidak_dilakukan
    -> from penggunaan ;

Saat menghitung jumlah "dilakukan" kita berikan nilai 1 jika field berisikan 'ya', selain itu beli nilai 0. Dan saat menghitung jumlah "tidak_dilakukan" kita berikan nilai 1 jika field berisikan 'tidak', selain itu beli nilai 0. Setelah itu jumlahkan semua CASE clause tersebut sehingga kita dapatkan hasil seperti ini

+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
| kegiatan | senin | selasa | rabu  | kamis | jumat | sabtu | minggu | dilakukan | tidak_dilakukan |
+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
| olahraga | ya    | tidak  | ya    | tidak | ya    | tidak | tidak  |         3 |               4 |
| diskusi  | tidak | ya     | tidak | tidak | tidak | ya    | tidak  |         2 |               5 |
| rapat    | ya    | tidak  | tidak | tidak | tidak | tidak | tidak  |         1 |               6 |
| ulangan  | tidak | ya     | ya    | ya    | tidak | tidak | tidak  |         3 |               4 |
+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
4 rows in set (0.00 sec)

Tadaaaa….. Bagaimana? Luar biasa, bukan?

Dengan satu perintah SQL, kita bisa mendapatkan data yang matang dan ketika membuat program PHP-nya kita cukup menmpilkannya saja dalam bertuk <table></table> dan tidak perlu menghitung-hitung lagi. Mudah dan Cepat (y)

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.

Dasar-dasar Stored Procedure Pada SQL SERVER 2005 ke atas

Dasar-dasar Stored Procedure Pada SQL SERVER 2005 ke atas

A stored procedure is a subroutine available to applications that access a relational database system. A stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, sp or SP) is actually stored in the database data dictionary. (From Wikipedia)

Fungsi/subrutin yang tersimpan dalam database. Di Microsoft SQL Server 2005, kita bisa memanggil semua listing stored procedure dalam database kita dengan menggunakan perintah query berikut:

select *

from information_schema.routines

where routine_type = ‘PROCEDURE’

 

Perintah di atas akan menampilkan semua informasi mengenai SP yang ada di database. Termasuk tanggal buat, tanggal di ubah dan lain-lain. Tetapi perintah di atas hanya berlaku untuk login sysadmin.

Membuat Stored Procedure.

Query di atas adalah langkah sederhana untuk membuat sebuah stored procedure. Kita bisa membuat sebuah SP tanpa parameter dan dengan parameter. Kata kunci untuk mengingat nya adalah Create…As…Begin. Untuk melakukan modifikasi terhadap SP, gantilah kata kunci CREATE menjadi ALTER. Di bagian BEGIN… END inilah kita menulis sintaks programming logic. Bisa hanya memanggil sebuah query terhadap tabel, view, memanggil sp lainnya, membuat cursor dan lain-lain. Menulis sintaks stored procedure tidak semudah menulis di visual studio sebuah program. Disini tidak ada intellisense maupun debug. Murni dengan berlatih, maka kita akan membuatnya menjadi lebih baik.

Jika tidak hafal perintah creat SP atau takut terjadi kesalahan, maka dari sql server management studio, kita bisa membuat SP dari object explorer. Perhatikan gambar di bawah. Ini adalah sebuah contoh membuat sebuah SP baru dari Object Explorer.

 

Sample 1, Stored Procedure untuk memanggil tabel.

Untuk sample ini, saya menggunakan database Northwind. Di atas adalah query sederhana untuk membuat sebuah SP. Diharapkan SP ini dapat memanggil semua data dari tabel Products. Eksekusilah perintah query di atas, jika berhasil maka dari Object Explorer akan bertambah sebuah Stored Procedure seperti gambar di bawah:

 

Lihatlah gambar di atas, telah terbentuk sebuah Stored Procedure dengan nama sp_getproducts. Sampai dengan tahapan ini, dari sisi sintaks query, apa yang kita tulis sudah benar.  Jika tidak benar, maka akan muncul exception dari sql server, misalnya kesalahan deklarasi variabel, query yang salah dan lain-lain.

Selanjutnya adalah memanggil SP tersebut. Gunakan keyword EXEC  diikuti dengan nama Stored Procedure untuk melakukan pemanggilan atau eksekusi terhadap SP yang kita buat. Perhatikan gambar di bawah:

 

Ketika kita melakukan eksekusi sp_getproducts, maka SP tersebut akan mengembalikan data dari tabel Products. Hal ini cocok dengan logic kita di bagian body dari SP, yaitu SELECT * FROM Products. Jika anda memiliki sebuah View, maka anda dapat memanggil View tersebut dengan mengubah query menjadi SELECT * FROM <NamaView>.

Kita juga bisa memfilter data dengan kata kunci WHERE dengan memodifikasi query menjadi SELECT * FROM Products WHERE CategoryID = 1. Tetapi cara ini sangat statis, tidak dinamis. Sangat tidak baik untuk membuat 1 buah SP untuk 1 buah Category. Jika menginginkan SP yang dinamis dan reusable, gunakanlah parameter pada SP anda.

Sample 2, Stored Procedure dengan Parameter.

Hal yang paling penting disini adalah pendeklarasian variabel pada sql server. Gunakan keyword DECLARE untuk mendeklarasikan variabel, misalnya kita ingin membuat variabel dengan tipe data Int:

DECLARE @angka INT

Berbeda dengan tipe data int, tipe data yang bersifat string seperti char, varchar dll, harus dibuatkan panjang karakternya, misal:

DECLARE @kalimat VARCHAR(20)

Nah, sekarang kita akan melakukan modifikasi terhadap SP yang kita buat sebelumnya. Melakukan modifikasi bisa dari object explorer, klik kanan pada nama SP, kemudian klik Modify.

Selanjutnya lakukan seperti pada gambar:

Setelah nama SP, yaitu sp_getproducts, sisipkan sebuah variabel atau argument @categoryID dengan tipe data INT. Langkah selanjutnya adalah melakukan passing variabel @categoryID tersebut ke dalam body Stored Procedure kita. Jika telah selesai, eksekusi query tersebut, jika tidak ada exception, maka langkah yang kita buat sudah benar.

Selanjutnya adalah cara memanggil stored procedure tersebut. Panggil SP seperti biasa dengan keyword EXEC, kemudian tambahkan argumen di belakang nama SP sesuai tipe data:

EXEC sp_getproducts 1

Perintah di atas akan memfilter products dengan category ID = 1. Jika kita mengganti angka 1 dengan angka lain, maka SP akan memfilter data dari Products sesuai dengan angka Category yang di passing ke argumen.

Biar lebih clear lagi, saya akan ubah sampel kita menjadi lebih kompleks. Misal saya ingin melakukan join terhadap tabel Categories dan kemudian membuat SP yang mempassing nama kategori, jadi bukan ID dari kategori. Perhatikan query di bawah:

ALTER PROCEDURE [dbo].[sp_getproducts]
@categoryName VARCHAR(50)
AS
BEGIN
    SELECT P.ProductName, C.CategoryName, P.UnitPrice  FROM Products P
    INNER JOIN Categories C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName = @categoryName
END

Untuk memanggilnya lakukan perintah seperti ini:

EXEC sp_getproducts ’Beverages’

Jika ingin menggunakan keyword LIKE, modifikasi SP kita menjadi:

 

ALTER PROCEDURE [dbo].[sp_getproducts]
@categoryName VARCHAR(50)
AS
BEGIN
    SELECT P.ProductName, C.CategoryName, P.UnitPrice  FROM Products P
    INNER JOIN Categories C ON P.CategoryID = C.CategoryID
    WHERE C.CategoryName LIKE '%'+@categoryName+'%'
END

Menggunakan Temporary Tabel.

Sebenarnya dari beberapa BLOG (yang ane dah lupa blog nya), tidak disarankan untuk menggunakan temp table. Tapi tidak ada salahnya kita mencoba, toh teknologi ini telah disertakan di berbagai macam vendor database. Temp tabel tidak bisa terdeteksi pada object explorer. Temp tabel akan terhapus secara otomatis.

Nama dari temporary tabel biasanya dimulai dengan tanda ‘#’. Berikut adalah sintaks untuk membuat temp table:

    CREATE TABLE #tabelku     
     ( id int,         
       Kolom1 varchar(20),   
       Kolom2 varchar(20),
       Kolom3 datetime,        
       Kolom4 char(1))

Oke, langsung saja kita ke sampel, ceritanya saya akan mengurutkan data pada tabel products. Pengurutan data saya lakukan dengan cara mengelompokkan berdasarkan CategoryID. Jika CategoryID berubah, maka nomor urut akan di reset kembali menjadi 1.

Ada banyak pendekatan untuk menyelesaikan masalah ini. Pilihan saya adalah menggunakan temp tabel. Saya akan melakukan query dengan filter berdasarkan CategoryID, kemudian saya akan memasukkan ke dalam temp tabel dengan menggunakan keyword SELECT * INTO. Sekedar catatan, jika menggunakan SELECT * INTO, kita tidak perlu membuat temp tabel dengan keyword CREATE lagi.

Sintax SQL:

CREATE PROCEDURE sp_laporan
@categoryid INT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ProductName, UnitPrice INTO #laporan FROM Products
      WHERE CategoryID = @categoryid
      ORDER BY ProductName ASC
      ALTER TABLE #laporan
      ADD no_urut INT IDENTITY(1,1)
      SELECT no_urut, ProductName, UnitPrice FROM #laporan
END

Jalan cerita dari sintaks di atas:

  • membuat SP dengan nama sp_laporan
  • mempassing parameter @categoryid, parameter ini nanti dibutuhkan untuk filter data pada SELECT query.
  • Query dengan keyword SELECT … INTO, disini kita memasukkan data langsung ke dalam temp tabel #laporan. Pada case ini belum dibutuhkan looping terhadap data.
  • Kita lakukan ALTER terhadap tabel #laporan, menambahkan field no_urut dengan tipe data INT dan bersifat IDENTITY, sehingga menghasilkan auto number.
  • Ketika saya melakukan pemanggilan terhadap SP, maka akan dihasilkan bentuk data seperti gambar di bawah.

Pengurutan data bisa terjadi juga pada field UnitPrice. Jika dikehendaki pengurutan data dari UnitPrice dari harga paling murah ke harga paling mahal dan menambahkan nomor urut, maka modifikasi saja SP tersebut, jika sebelumnya pada keyword ORDER BY kita urutkan berdasarkan ProductName, maka gantilah ProductName menjadi UnitPrice. Maka akan menghasilkan seperti gambar di bawah ini:

Menggabungkan hasil dari beberapa SP

Kita bisa juga menggabungkan hasil dari beberapa SP kedalam sebuah temp tabel. Masih dengan contoh sebelumnya dimana kita concern terhadap no_urut. Coba perhatikan query di bawah ini:

CREATE PROCEDURE sp_laporanglobal
AS
BEGIN
      SET NOCOUNT ON;
      CREATE TABLE #laporanglobal
      (
            Nmr INT IDENTITY(1,1),
            no_urut INT,
            ProductName VARCHAR(50),
            UnitPrice MONEY
      )
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 1
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 2
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 3
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 4
      INSERT INTO #laporanglobal (no_urut, ProductName, UnitPrice) EXEC sp_laporan 5
      SELECT * FROM #laporanglobal
      DROP TABLE #laporanglobal
END
GO

 

Jalan ceritanya:

  • Saya membuat temp tabel bernama #laporanglobal, dengan 4 buah field. Field Nmr adalah identity, sementara field lainnya adalah field yang kita ambil dari Stored Procedure sp_laporan.
  • Selanjutnya saya lakukan INSERT ke dalam temp tabel, sambil melakukan eksekusi terhadap SP. Teknik yang cukup akrobatik sebenarnya hehehe. Untuk sementara saya kasi contoh yang seperti ini, karena kita belum membicarakan looping.

Sepertinya pembicaraan kita mengenai dasar-dasar SP, cukup sampai disini. Mungkin ke depan kita bicarakan masalah CURSOR dan LOOPING. Apa yang anda pelajari sampai di topik ini saya rasa sudah cukup banyak. Jika masih belum puas, saya akan berikan beberapa keyword untuk googling:

  • JOIN multiple Stored Procedure
  • Temp Table on Stored Procedure
  • ALTER and CREATE Table.

Percayalah, saya hanya googling keyword di atas untuk membuat artikel ini.

 

Renumbering Record Data Dengan VB.net dan SQL Server

Salah seorang member di salah satu grup di facebook pernah bertanya, bagaimana mengurutkan nomor kode jika data record dihapus. Sederhana sebenarnya, tapi kita memerlukan pendekatan yang sedikit rumit dan kemungkinan akan memakan resource dari sisi server (apabila data sangat banyak).

Ilustrasi dari pertanyaan tersebut adalah sebagai berikut:

Original data:
001    Budi
002    Rudi
003    Fahmi

Pada suatu saat, user akan menghapus data dengan kode 002. Tetapi aplikasi harus dapat mengubah nama Fahmi mempunyai kode 002 (menggantikan Rudi yang telah dihapus), dan seterusnya untuk record yang dibawahnya. Intinya, aplikasi ini harus dapat menangani sequensial nomor kode.

Sebenarnya dengan berbagai pendekatan logika dan lain-lain, hal ini bisa saja diwujudkan. Tetapi ada permasalahan lain, bagaimana jika data ini mempunyai relasi ke tabel lain. Misal, Rudi mempunyai histori di sebuah tabel, sedangkan Fahmi tidak mempunyai histori di tabel tersebut. Jika kode Fahmi (003) diganti menjadi kode nya Rudi (002), maka apakah selanjutnya Fahmi mempunyai histori di tabel tersebut?

Bisa ya bisa tidak. Jika anda seorang DB Admin yang cool, maka anda akan memikirkan masalah relasi antar tabel. Anda bisa menentukan apakah aktifitas UPDATE dan DELETE akan mempunyai efek ke tabel lain yang berelasi. Pada SQL Server nya Microsoft sudah ada fitur untuk membuat relasi antar tabel, dan pada relasi itu kita bisa mengatur Cascade Update dan Cascade Delete. Misalkan kita atur menjadi Cascade, maka perubahan data dari tabel master akan mempunyai efek ke tabel lain. Di tabel master, saya menghapus kode 002, maka pada tabel yang berhubungan, record dengan kode 002 akan terhapus.

Pada gambar di atas, dapat dilihat bahwa tabel siswa dan history berelasi melalui field idsiswa. Selanjutnya perhatikan gambar di bawah ini:

Perhatikan properties dari relasi pada gambar. Untuk Insert and Update Specification, saya buat rule Cascade untuk masing-masing Delete Rule dan Update Rule. Sampai tahap ini, pendekatan saya untuk membuat data itu terintegritas sudah cukup. Selanjutnya kita selesaikan permasalahan pengurutan nomor kode jika terjadi penghapusan data dan kode tidak menjadi urut. Urutan yang dikehendaki adalah 001, 002, 003, 004 dan seterusnya. Ingat, tipe data yang diurutkan adalah varchar, bukan Int. Ini menjadi lebih sulit lagi lho. #trust me.

 

Logika dan pemecahan masalah.

Jika anda berpikir untuk melakukan pengurutan dengan tipe angka adalah hal yang paling simpel, maka saya sepakat. Ada banyak pendekatan untuk langkah ini, tapi saya hanya mengambil satu pendekatan saja untuk solving masalah ini.

Pertama, saya melakukan query untuk menghapus sebuah record. Dan saya tidak perlu kuatir untuk tabel relasinya 🙂

Kedua, saya melakukan ALTER Tabel, untuk membuat sebuah id bayangan yang bertipe data INT, dan mempunyai Identity(1,1).

"ALTER TABLE siswa ADD idbayangan INT IDENTITY(1,1) "

Ketiga, setelah idbayangan terbentuk (yang harapan saya adalah mempunyai nomor yang urut), maka melalui program VB, saya melakukan update terhadap field idsiswa (pada contoh ini), dengan reformating untuk menambahkan leading zero, yang pastinya update terhadap idsiswa ini, akan berefek juga terhadap tabel histori.

Coba perhatikan gambar di bawah adalah kondisi sebelum delete:

Pada gambar terlihat, id 001 dimiliki oleh orang bernama Koko, mempunyai dua buah history di tabel history. Id 002 adalah seseorang bernama Amir, mempunyai 2 record di tabel history. Id 003 bernama Linda, memiliki sebuah record di tabel history. Saya akan mencoba menghapus ID 001, selanjutnya ID 002 akan menjadi 001, ID 001 dengan nama koko di tabel history akan hilang, dan ID 001 di history menjadi milik Amir.

Cek gambar di bawah setelah di delete:

yak, sampai disini sudah sesuai dengan yang diinginkan. File dari project ini dapat di download di : http://www.mediafire.com/?91qvfv72ba6ij2z

Catatan:

sampel ini belum di test untuk data dengan jumlah record yang banyak. Bisa di optimalkan selanjutnya dengan memakai triger maupun cursor. Belum dites juga untuk concurency. This is just a basic sample.