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
Posted on :

9 komentar:

  1. simonSenin, 05 April, 2010

    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..:(

    BalasHapus
  2. eRQeeSenin, 05 April, 2010

    @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...

    BalasHapus
  3. AnonimSenin, 03 Januari, 2011

    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

    BalasHapus
  4. AnonimRabu, 27 April, 2011

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

    maap gan, baru belajar stored procedure..

    BalasHapus
  5. AnonimSelasa, 30 Agustus, 2011

    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

    BalasHapus
  6. AnonimSelasa, 30 Agustus, 2011

    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

    BalasHapus
  7. SlankissmeSelasa, 02 Oktober, 2012

    Komentar ini telah dihapus oleh pengarang.

    BalasHapus
  8. SlankissmeSelasa, 02 Oktober, 2012

    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

    BalasHapus
  9. PemulaSenin, 31 Desember, 2012

    cara panggil "xp_autokode_out" dari vb 6 gimana om?

    BalasHapus