Arsip: Tipe data MySQL auto_increment??

 
user image
more 14 years ago

ibiz

Kakak2 delpier yang baik... mohon pencerahan.. tipe data apa yang pas buat filed data gabungan text dan number yang dapat auto_increment di MySql? contohnya begini: data table field1 --> Kode : NIP001 field2 --> Nama : Joni field3 --> Alamat: Purwokerto Gimana supaya data kode (NIP001) dapat auto_increment/ automotis urut dengan gabungan text dan number (NIP001, NIP002, NIP003,.....) di MySQL? Gw dah coba pake INT, BIGINIT gak bisa jalan.. Thxb4,
user image
more 14 years ago

budi_bunga

Maaf yah setahu saya sih autoincrement itu yah pake angka teman, saran saya lebih baik ada rutin append/insert gabungan bilangan dan angka setelah membaca record terakhir yang baru diinput .... :roll:
user image
more 14 years ago

jayadi888

coba pelajari ini (membuat no tiket secara otomatis) intinya count row yang ada terus gunakan CONCAT:
CREATE DEFINER=cpses_pihtn8p9al@% PROCEDURE sp_ticketin(
IN ACCNO VARCHAR(6),
IN BUYSELL CHAR(2),
IN PROBRAND VARCHAR(5),
IN RATEBRAND CHAR(1),
IN PRODTIPE VARCHAR(10),
IN HARGA DECIMAL(10,4),
IN JUMLOT INT,
IN POSNL CHAR(2),
IN OPENTIKET VARCHAR(45),
IN OPERAT VARCHAR(6),
IN KET VARCHAR(45)
)
BEGIN
DECLARE COTICK INT;
DECLARE TICNO1 VARCHAR(6);
DECLARE TICNO VARCHAR(10);
DECLARE OPKOMISI DECIMAL(10,2);
DECLARE CLKOMISI DECIMAL(10,2);
DECLARE TRDATE DATE;
DECLARE BSOPEN CHAR(2);
DECLARE TIMEOPEN TIMESTAMP;
DECLARE HARGAOPEN DECIMAL(10,4);
DECLARE QCOTICK CURSOR FOR SELECT COUNT(TICKETID) FROM ORDERMASUK;
DECLARE QTICNO  CURSOR FOR SELECT CONCAT(TICNO1,COTICK+1);
DECLARE QTICNO1 CURSOR FOR SELECT DATE_FORMAT(TRADEDATE,'%y%m%d' ) FROM TRADINGDATE ORDER BY TRADEDATE DESC LIMIT 1;
DECLARE QKOMISI CURSOR FOR SELECT OPENKOMISI,CLOSEKOMISI FROM PRODUCTRATE WHERE RATEID=RATEBRAND AND PRODUCTTYPEID=PRODTIPE;
DECLARE QTRDATE CURSOR FOR SELECT TRADEDATE FROM TRADINGDATE ORDER BY TRADEDATE DESC LIMIT 1;
DECLARE QTKOPEN CURSOR FOR SELECT BS,TIME_IN, PRICE FROM OPENPOSISI WHERE TICKETNO=OPENTIKET;
 
OPEN QTICNO1;
OPEN QCOTICK;
OPEN QKOMISI;
OPEN QTRDATE;
FETCH QKOMISI INTO OPKOMISI,CLKOMISI;
FETCH QTICNO1 INTO TICNO1;
FETCH QCOTICK INTO COTICK;
FETCH QTRDATE INTO TRDATE;
CLOSE QKOMISI;
CLOSE QCOTICK;
CLOSE QTICNO1;
IF (COTICK=0) THEN
-- IF ORDERMASUK TABLE IS EMPTY ----------------------------------------------------------------------------------------
   OPEN QTICNO;
   FETCH QTICNO INTO TICNO;
   IF (POSNL='N') THEN
       INSERT INTO ORDERMASUK(TICKETNO,ACCID,BS, LOT, PRODUCTID, RATEID, PRICE, POSITION, TIC_OPEN, COMMISSION, OPERATOR, 
                              KETERANGAN, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,JUMLOT,PROBRAND,RATEBRAND,HARGA, POSNL, OPENTIKET, OPKOMISIJUMLOT, OPERAT, KET,
                              TRDATE);
       INSERT INTO OPENPOSISI(TICKETNO,ACCID,BS, PRODUCTID, RATEID, PRICE, LOT_OPEN, COMMISSION, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,PROBRAND,RATEBRAND,HARGA, JUMLOT, OPKOMISI JUMLOT, TRDATE);
   ELSE
       INSERT INTO ORDERMASUK(TICKETNO,ACCID,BS, LOT, PRODUCTID, RATEID, PRICE, POSITION, TIC_OPEN, COMMISSION, OPERATOR, 
                              KETERANGAN, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,JUMLOT,PROBRAND,RATEBRAND,HARGA, POSNL, OPENTIKET, CLKOMISIJUMLOT, OPERAT, KET,
                              TRDATE);
       UPDATE OPENPOSISI SET LOT_OPEN=LOT_OPEN-JUMLOT, LOT_LIQUID=LOT_LIQUID+JUMLOT, COMMISSION=OPKOMISI LOT_OPEN WHERE ACCID=ACCNO AND 
                             TICKETNO=OPENTIKET;
       OPEN QTKOPEN;
       FETCH QTKOPEN INTO BSOPEN, TIMEOPEN, HARGAOPEN;
       CLOSE QTKOPEN;                                                                                                                      
       INSERT INTO LIQUIDPOSISI(TICKETNO,ACCID,BS,LOT, PRODUCTID, RATEID, PRICE, COMMISSION,TIC_OPEN, BS_OPEN, COM_OPEN, TIME_OPEN,
                               PRICE_OPEN)
                   VALUES (TICNO, ACCNO,BUYSELL, JUMLOT, PROBRAND, RATEBRAND, HARGA, CLKOMISIJUMLOT, OPENTIKET,BSOPEN,
                           CLKOMISI JUMLOT,TIMEOPEN, HARGAOPEN);
   END IF;
   CLOSE QTICNO; 
ELSE
-- IF ORDERMASUK NOT EMPTY ---------------------------------------------------------------------------------------------
   OPEN QTICNO;
   FETCH QTICNO INTO TICNO;
   IF (POSNL='N') THEN
       INSERT INTO ORDERMASUK(TICKETNO,ACCID,BS, LOT, PRODUCTID, RATEID, PRICE, POSITION, TIC_OPEN, COMMISSION, OPERATOR, 
                              KETERANGAN, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,JUMLOT,PROBRAND,RATEBRAND,HARGA, POSNL, OPENTIKET, OPKOMISI, OPERAT, KET,
                              TRDATE);
       INSERT INTO OPENPOSISI(TICKETNO,ACCID,BS, PRODUCTID, RATEID, PRICE, LOT_OPEN, COMMISSION, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,PROBRAND,RATEBRAND,HARGA, JUMLOT, OPKOMISI, TRDATE);
   ELSE
       INSERT INTO ORDERMASUK(TICKETNO,ACCID,BS, LOT, PRODUCTID, RATEID, PRICE, POSITION, TIC_OPEN, COMMISSION, OPERATOR, 
                              KETERANGAN, DATE_CLOSE) VALUES
                             (TICNO,ACCNO,BUYSELL,JUMLOT,PROBRAND,RATEBRAND,HARGA, POSNL, OPENTIKET, CLKOMISIJUMLOT, OPERAT, KET,
                              TRDATE);
       UPDATE OPENPOSISI SET LOT_OPEN=LOT_OPEN-JUMLOT, LOT_LIQUID=LOT_LIQUID+JUMLOT, COMMISSION=OPKOMISI LOT_OPEN WHERE ACCID=ACCNO AND 
                             TICKETNO=OPENTIKET;
       OPEN QTKOPEN;
       FETCH QTKOPEN INTO BSOPEN, TIMEOPEN, HARGAOPEN;
       CLOSE QTKOPEN;                                                                                                                      
       INSERT INTO LIQUIDPOSISI(TICKETNO,ACCID,BS,LOT, PRODUCTID, RATEID, PRICE, COMMISSION,TIC_OPEN, BS_OPEN, COM_OPEN, TIME_OPEN,
                               PRICE_OPEN)
                   VALUES (TICNO, ACCNO,BUYSELL, JUMLOT, PROBRAND, RATEBRAND, HARGA, CLKOMISIJUMLOT, OPENTIKET,BSOPEN,
                           CLKOMISI JUMLOT,TIMEOPEN, HARGAOPEN);
   END IF;
   CLOSE QTICNO;
END IF;
END
user image
more 14 years ago

ibiz

makasih atas sarannya kakak-kakak semua...
user image
more 14 years ago

masdi2t

aku ada contoh ni, tp syarat nya pake MySQL 5.0 minimal :D ini DDL tabel nya:

  CREATE TABLE t2 (                     
    x int(4) NOT NULL AUTO_INCREMENT,   
    y varchar(7) DEFAULT NULL,          
    PRIMARY KEY (x)                     
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
engine nya mau pake InnoDB juga gak masalah, trus bikin kan trigger untuk mengisi field 1y[/y] ini DDL trigger nya:

DELIMITER $$;
CREATE TRIGGER trig_t2 BEFORE INSERT on t2
FOR EACH ROW BEGIN
  SET NEW.y = concat('NIP', lpad(NEW.x, 4, '0'));
END$$
DELIMITER ;$$
dah selesai :) [/code]
more ...
  • Pages:
  • 1
Share to

Random Topic

Local Business Directory, Search Engine Submission & SEO Tools FreeWebSubmission.com SonicRun.com