Mysql | MariaDB create function, For Loop, Serial Number example
DROP FUNCTION IF EXISTS max_nomor_anggota; DELIMITER | CREATE FUNCTION max_serial_number(param_1 VARCHAR(255), param_2 VARCHAR(255)) RETURNS VARCHAR(10) BEGIN DECLARE nomor_anggota_var VARCHAR(255); DECLARE nomor VARCHAR(255); DECLARE urut VARCHAR(255); FOR i IN 1..9999 DO SET urut = LPAD(i, 4, "0"); SET nomor = concat(param_1, param_2, urut); IF (SELECT NOT EXISTS(SELECT * from table_name WHERE nomor_anggota = nomor AND id_wilayah=CAST(param_1 as INTEGER) AND id_daerah=CAST(param_2 as INTEGER) ORDER BY nomor_anggota)) THEN RETURN nomor; END IF; END FOR; RETURN nomor; END | DELIMITER ;