9 xp_autokode Posted on : 4/30/2009 03:31:00 PM

Barusan baca-baca blog-nya dendie tentang Stored Procedure. Ada yg comment, nanya, pengen nge-convert stored-procedure yg dia bikin dari SQL Server ke SP di MySQL. Dia co-pas-kan script SP di SQL Server-nya sebagai berikut :

============================================
CREATE PROCEDURE [dbo].[AutoNumber]
@NoMember varchar(10) OUTPUT
AS
DECLARE @NoTertinggi numeric,
@Indek int
Select @NoTertinggi=MAX(CAST(RIGHT([No Member],8)As Numeric))
From Member
Set @NoMember = 'NM'
If @NoTertinggi is null
set @NoTertinggi = 0
set @NoTertinggi = @NoTertinggi + 1
set @Indek = LEN(@NoTertinggi)
ulang:
if @Indek <> 8
begin
set @NoMember = @NoMember + '0'
set @Indek = @Indek + 1
goto ulang
end
set @NoMember = @NoMember + cast(@NoTertinggi as varchar)

GO
==============================

Setelah gw tracing singkat, ntu SP tujuannya adalah untuk nge-generate auto-number otomatis dari sebuah tabel. Hmm..., gw jadi sedikit tergelitik untuk nge-share xp_autokode buatan-ku di masa-lalu nih utk nanganin kasus serupa. Begini script-nya :

/* Procedure "xp_autokode_out" DDL */

delimiter $$
create procedure `xp_autokode_out`(
  depan varchar(20),
  digit tinyint,
  tabel varchar(255),
  kolom varchar(255),
  out newvalue varchar(255)
)
begin
 if digit > 0 then
  if length(trim(depan)) = 0 then
   set @sql = concat(' select concat("',trim(depan),'",lpad(ifnull(cast(max(substr(',kolom,',length("',trim(depan),'")+1,', digit,')) as signed),0)+1,',digit,',0)) as newkode into @value from ',tabel,' where left(',kolom,',length(',kolom,')-',digit,') = "',trim(depan),'" AND (',kolom,'+0)/',kolom,' = 1');
  else
   set @sql = concat(' select concat("',trim(depan),'",lpad(ifnull(cast(max(substr(',kolom,',length("',trim(depan),'")+1,', digit,')) as signed),0)+1,',digit,',0)) as newkode into @value from ',tabel,' where left(',kolom,',length(',kolom,')-',digit,') = "',trim(depan),'"');
  end if;
  prepare query from @sql;
  execute query;
  set newvalue = @value;
 else
  select 'Digit tidak boleh lebih kecil dari 1 !!!' as pesan;
 end if;
end $$
delimiter ;


ntu xp ~ extended stored procedure -- gw menamakan xp-xp generic gw begitu ~ adalah routine yg bisa meng-generate autonumber untuk *sembarang tabel* dengan primary key berupa varchar atau char.  Dan bisa dalam formatting tertentu, pula.

Misalnya :
mo bikin nomor nota B-00001 dari sebuah tabel penjualan dengan kolom primary key namanya "kode", loe tinggal rumusin dulu format2 autonumber yg mo di-generate sbb :

Prefix : "B-"
Jumlah Digit : 5 ~> 00001 terdiri 5 digit angka, bukan?
Nama Tabel : penjualan
Nama Kolom : kode

Cara pemanggilan SP :

mysql > call xp_autokode_out("B-",5,"penjualan","kode",@new_kode);
mysql> select @new_kode as `kode_baru`;
+-----------+
| kode_baru |
+-----------+
| B-00001   |
+-----------+


cobain ndiri deh...

Dan loe bisa ekspansikan penggunaan xp ini di dalam stored procedure loe. Misalnya, sp untuk simpan nota, seperti ini contohnya :

delimiter $$
create procedure `sp_penjualan_simpan`(
  v_kode_barang varchar(20),
  v_jumlah int,
  v_harga double
)
begin
 declare new_kuitansi varchar(20);
 call xp_autokode_out("B-",5,"penjualan","kode",new_kuitansi);
 insert into penjualan values (new_kuitansi, v_kode_barang, v_jumlah, v_harga);
end $$
delimiter ;

gimana-gimana? keren kan?
_______________________


Rizky Prihanto
Software Architect PT Cinox Media Insani