Function Nomor Otomatis MySQL MySQL Autonumeric

mysql_autonumber.sql

CREATE TABLE `mahasiswa` (
  `nim` INT(8) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000000',
	`nama` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	PRIMARY KEY (`nim`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `id_auto_mahasiswa`(`kodejurusan` INT, `kodekonsentrasi` INT, `kodekampus` INT)
  RETURNS int(10) unsigned
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
zerofill
BEGIN

  DECLARE cStop INT DEFAULT 0;
  DECLARE cIDX INT DEFAULT 0;
  DECLARE xIDX INT DEFAULT 0;

  DECLARE cID CURSOR FOR
    SELECT MAX(nim) FROM mahasiswa
    WHERE ((SUBSTRING(nim, 1,2)=DATE_FORMAT(NOW(), '%y')) 
	 AND SUBSTRING(nim, 3,1)=kodejurusan 
	 AND SUBSTRING(nim, 4,1)=kodekonsentrasi
	 AND SUBSTRING(nim, 5,1)=kodekampus
	 );

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cStop = 1;

  OPEN cID;
    FETCH cID INTO cIDX;
  CLOSE cID;

  IF cIDX IS NULL THEN
    SET xIDX = CONCAT(DATE_FORMAT(NOW(), '%y'),kodejurusan,kodekonsentrasi,kodekampus,'001');
  ELSE
    SET xIDX = cIDX+1;
  END IF;

  RETURN xIDX;

END

-- execute
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'suhendra');
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'yohana');
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'putra');
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'hendra');
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'komar');
INSERT mahasiswa VALUES ( (SELECT `id_auto_mahasiswa`(4, 3, 1)), 'satriani');


SELECT MAX(nim) FROM mahasiswa
    WHERE ((SUBSTRING(nim, 1,2)=DATE_FORMAT(NOW(), '%y')) 
   AND SUBSTRING(nim, 3,1)=4 
	 AND SUBSTRING(nim, 4,1)=3
	 AND SUBSTRING(nim, 5,1)=1
	 );