Learn the Rules, Break The Rules, and Create the New Ones...

Hi... My name is Rizky Prihanto. You can call me RQ, or Rizky either. I am currently living on Bandung, Indonesia. Had a lot of works and research about Enterprise Information Systems (majoring on education and e-governments). I have bunch of interests (some friends call it 'freakz') about MySQL Opensource Database and now I am one of the administrator of MySQL Indonesia User Group - the opensource community initialized by Sun Microsystems Indonesia.

My Company PT Cinox Media Insani, Bandung, Indonesia. I work here since 2008 and I take responsibility as Chief of Software Architect. My job is about planning, imaginating, fantasy-ing, concepting, and build the infrastructure of the new information systems (or app engines) which going to be implemented.

This blog This is my blog that represent my current opinion, research and experiences about anything in Software Engineering. Written since 2007 (actually) and has been vaccum for a lot of while. And now I wanna ressurrect this blog (optimistically) from the long-long-hibernation with something fresh and new ideas -- still about MySQL, software engineering, development, and may be something managerial here.

About the tagline I've learned the statement above from some paper written by Kent Beck about Extreme Programming (XP) methodology -- some sort of practical software development methods which have no boundaries. That's very inspiring me a lot. I have written some article on this blog that tell my interpretation about that statement here.

My Another Blogs I have classifying my blogs into some sort of genre. The blog that you read here right now is my primary blog that tell you (majoring) about IT stuff. But if you wanna look another side of me, you can visit here, here, here,or here. Hope it'll be interesting for some of you.

Credits I would thanks to Blogger for this great blog platform. Skinpress who designed this Wordpress template (which is bloggerized by Free Blogger Templates). My appreciate is also going to you who give your generously time for visiting my blog.

xp_autokode

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

9 comments:

simon mengatakan...

mas bisa kasih nasehat 9ak saya ju9a baru mi9rasi ke mysql nih..coba di cek sp saya
DELIMITER $$
CREATE PROCEDURE tambah_desa()
begin
DECLARE @idesa varchar(5),@ndesa varchar(25),@ikecamatan varchar(5)
if exists(select * from desa where id_desa = idesa)
update desa set id_desa = idesa, nama_desa=ndesa, id_kecamatan=ikecamatan where id_desa = idesa;
else
insert into desa(id_desa,nama_desa,id_kecamatan) values(idesa,ndesa,ikecamatan);
end
DELIMITER ;
kok error mulu yak..:(

eRQee mengatakan...

@simon: deu.. selain salah syntax, nggak optimized tuh code loe. gw baikin yak:

DELIMITER $$
CREATE PROCEDURE tambah_desa(idesa varchar(5), ndesa varchar(25), ikecamatan varchar(5))
BEGIN
insert into desa(id_desa,nama_desa,id_kecamatan) values(idesa,ndesa,ikecamatan)
on duplicate key update nama_desa = ndesa, id_kecamatan = ikecamatan;
END
$$
DELIMITER ;

-- klausa insert into .. on duplicate key update CUMAN ADA di MySQL dude. jadi jangan coba2 pakae itu di DBMS lain. simple kan? hehehe...

Anonim mengatakan...

pux qhuw jg eror syntakx.....padahal delimiter yg bawah dah q ganti


CREATE PROCEDURE ProsesHitungUlang (bulan varchar(2),tahun varchar (4),kodebrg varchar(30))

AS

if not exists(select 'true' from dbstock a where bulan=@bulan and tahun=@tahun and a.kodebrg=@kodebrg)
begin
insert into dbstock(kodebrg,bulan,tahun,kodegdg)
select a.kodebrg,@bulan,@tahun,b.kodegdg from dbbarang a,dbgudang b where a.kodebrg=@kodebrg
end

IF @BULAN>1 and @tahun<@tahun+1

begin
update dbstock
set
xqntAwl=isnull((SELECT Sum(D.xQNTAWL+D.QNTPBL+D.QNTRPJ+D.QNTTRI+D.QNTUKI+D.QNTADI-D.QNTPNJ-D.QNTRPB-D.QNTTRO-D.QNTUKO-D.QNTADO) FROM DBSTOCK D WHERE d.kodegdg=dbStock.kodegdg and D.KodeBrg=dbStock.KodeBrg and D.Tahun=@Tahun and d.Bulan=@bulan-1 ),0),
xhrgAwl=isnull((SELECT Sum(D.XHRGAWL+D.HRGPBL+D.HRGRPJ+D.HRGTRI+D.HRGUKI+D.HRGADI-D.HRGPNJ-D.HRGRPB-D.HRGTRO-D.HRGUKO-D.HRGADO) FROM DBSTOCK D WHERE d.kodegdg=dbStock.kodegdg and D.KodeBrg=dbStock.KodeBrg and D.Tahun=@Tahun and d.Bulan =@bulan-1),0)
where bulan=@bulan and tahun=@tahun and kodebrg=@kodebrg
end
else
begin
update dbstock
set
xqntAwl=isnull((SELECT Sum(D.xQNTAWL+D.QNTPBL+D.QNTRPJ+D.QNTTRI+D.QNTUKI+D.QNTADI-D.QNTPNJ-D.QNTRPB-D.QNTTRO-D.QNTUKO-D.QNTADO) FROM DBSTOCK D WHERE d.kodegdg=dbStock.kodegdg and D.KodeBrg=dbStock.KodeBrg and D.Tahun=@Tahun-1 and d.Bulan='12' ),0),
xhrgAwl=isnull((SELECT Sum(D.XHRGAWL+D.HRGPBL+D.HRGRPJ+D.HRGTRI+D.HRGUKI+D.HRGADI-D.HRGPNJ-D.HRGRPB-D.HRGTRO-D.HRGUKO-D.HRGADO) FROM DBSTOCK D WHERE d.kodegdg=dbStock.kodegdg and D.KodeBrg=dbStock.KodeBrg and D.Tahun=@Tahun-1 and d.Bulan ='12'),0)
where bulan=@bulan and tahun=@tahun and kodebrg=@kodebrg
end


GO

Anonim mengatakan...

gan, kalo kode pengen di reset jadi 00001 lagi jika kondisi = closed gimana cara nya?

maap gan, baru belajar stored procedure..

Anonim mengatakan...

mas rizky, saya mohon bantuannya..
awalnya saya menggunakan firebird, firebird memang lumayan tp ada bbrp yg tdk didukung ddlmnya. seperti replication dan tidak autocommit.
jd saya berencana merubahnya mnjdi mysql.saya kesulitan convert SP dr FB ke mysql. salah 1 nya..

SP dri Firebird
============================

CREATE PROCEDURE cardstc (IN lok varchar(3), IN noitem varchar(8),
OUT item varchar(8),
OUT kode_src varchar(10),
OUT kode_stc varchar(2),
OUT dt date,
OUT tm time,
OUT debit decimal(12,2),
OUT kredit decimal(12,2),
OUT tot_stock decimal(12,2),
OUT id varchar(8))
BEGIN


SELECT NO_ITEM,ID_DATE,ID_TIME,QTY*iner,KD_ID,KD_RECEV FROM RECEVDET WHERE KD_LOK = @lok AND NO_ITEM = @noitem
into @item,@dt,@tm,@debit,@ID,@KODE_SRC;
set @KODE_STC = '02';
if (@tot_stock = NULL) then set @TOT_STOCK = 0;
set @KREDIT = 0;

===================================
saya sudah rubah ke mysql menjadi

DELIMITER $$

CREATE PROCEDURE `cardstc`(IN lok varchar(3), IN noitem varchar(8),
OUT item varchar(8),
OUT kode_src varchar(10),
OUT kode_stc varchar(2),
OUT dt date,
OUT tm time,
OUT debit decimal(12,2),
OUT kredit decimal(12,2),
OUT tot_stock decimal(12,2),
OUT id varchar(8))
BEGIN
set @KODE_STC = '02';
set @KREDIT = 0;
SELECT NO_ITEM,ID_DATE,ID_TIME,QTY*iner,KD_ID,KD_RECEV FROM RECEVDET WHERE KD_LOK = @lok AND NO_ITEM = @noitem
into @item,@dt,@tm,@debit,@ID,@KODE_SRC;


if (@tot_stock = NULL) then
set @TOT_STOCK = 0;
end if;

END

setelah di call hanya muncul kolom kosong. SP diatas saya gunakan untuk menyusun card stock. mohon saran dari mas rizky.Thanks

Anonim mengatakan...

maaf mas SP firebird nya bkn yg diatas tp dibawah ini

==================================

CREATE PROCEDURE CARD_STC (
lok varchar(3),
noitem varchar(8))
returns (
item varchar(8),
kode_src varchar(10),
kode_stc varchar(2),
dt date,
tm time,
debit decimal(12,2),
kredit decimal(12,2),
tot_stock decimal(5,2),
id varchar(8))
as
BEGIN

for SELECT NO_ITEM,ID_DATE,ID_TIME,QTY*iner,KD_ID,KD_RECEV FROM RECEVDET WHERE KD_LOK = :lok AND NO_ITEM = :noitem
into :item,:dt,:tm,:debit,:ID,:KODE_SRC do
begin
KODE_STC = '02';
if (tot_stock is null) then TOT_STOCK = 0;
KREDIT = 0;
suspend;
end

mohon sarannya mas.Thanks

Slankissme mengatakan...
Komentar ini telah dihapus oleh pengarang.
Slankissme mengatakan...

Mas Rizky, bantu saya donk...saya punya SP Sql Server 2000 yang akan saya ubah ke Mysql...tolong bantuin saya SPnya ke MySql

ini SP Sql Server 2000-nya =
--------------------------------------------
CREATE PROC spAddPenyusutan @KodeBarang Varchar(100),@Harga Varchar(100),@TglBeli Varchar(30),@JangkaWaktu Varchar(100),@Penyusutan Varchar(100)

AS
Set NoCount On

Begin
Declare @TglAwal SmallDateTime
Declare @TglAkhir SmallDateTime

Declare @Periode Int
Declare @Tahun Int
declare @dasarpenyusutan money
declare @SaldoAwal Money
declare @SaldoAkhir Money
declare @NominalPenyusutan Money
declare @Akumulasi Money
Declare @BulanPembagi Int


Set @TglAwal=@Tglbeli
Set @Tglakhir=DateAdd(Month,convert(int,@JangkaWaktu),@TglAwal)

Set @Periode=1
Set @Tahun=1
Set @SaldoAwal=convert(money,@Harga)
Set @Akumulasi=0
Set @DasarPenyusutan=convert(money,@Harga)
Set @BulanPembagi=12

While @tglAwal<@Tglakhir
begin

Set @BulanPembagi=Case When Year(@TglAwal)=Year(@TglAkhir) Then Case When Month(@TglAkhir)=12 Then 12 Else Month(@TglAkhir)-1 End Else 12 end


Set @NominalPenyusutan=Case When Year(@TglAwal)=Year(@TglAkhir) Then @DasarPenyusutan/@BulanPembagi Else (@DasarPenyusutan*(@Penyusutan/100.0))/@BulanPembagi End

Set @SaldoAkhir=@SaldoAwal-@NominalPenyusutan
Set @SaldoAkhir=Case When @SaldoAkhir<1 Then 1 Else @SaldoAkhir End
Set @Akumulasi=@akumulasi+@NominalPenyusutan


Insert SchedulePenyusutan(BarangID,Tahun ,Periode ,Schedule,Dasar,SaldoAwal,Penyusutan ,Akumulasi,SaldoAkhir)
Values (@KodeBarang,@Tahun,@Periode,@tglAwal,@DasarPenyusutan,@SaldoAwal,@NominalPenyusutan,@Akumulasi,@SaldoAkhir)

Set @DasarPenyusutan=case when Month(@TglAwal)=12 Then @SaldoAkhir Else @DasarPenyusutan End
Set @SaldoAwal=@SaldoAkhir

Set @Periode=@Periode+1
Set @Tahun =Case When Month(@TglAwal)=12 then @Tahun+1 Else @Tahun End
If @SaldoAkhir<=1 goto RightHere
Set @TglAwal=DateAdd(Month,1,@TglAwal)
End
RightHere:

Return

End

Pemula mengatakan...

cara panggil "xp_autokode_out" dari vb 6 gimana om?

Posting Komentar