{"id":551,"date":"2012-02-29T08:00:48","date_gmt":"2012-02-29T13:00:48","guid":{"rendered":"http:\/\/www.latindevelopers.com\/ivancp\/?p=551"},"modified":"2012-02-27T15:15:18","modified_gmt":"2012-02-27T20:15:18","slug":"valores-auto_increment-personalizados","status":"publish","type":"post","link":"https:\/\/www.latindevelopers.com\/ivancp\/2012\/02\/valores-auto_increment-personalizados\/","title":{"rendered":"Valores auto_increment personalizados"},"content":{"rendered":"

En el Per\u00fa los formatos num\u00e9ricos de las facturas es 001-000033 , la serie (001) cambia eventualmente pero el valor auto-incrementado es el mismo:<\/p>\n

001-000034\r\n001-000035\r\n001-000036\r\n...<\/pre>\n
Para manejar esto en MySQL he preparado una soluci\u00f3n basada en otro articulo mio<\/a>:<\/p>\n
    \n
  1. Crear la tabla para guardar los n\u00fameros actuales:\n
    create table _sequence\r\n(\r\n    seq_name varchar(50) not null primary key,\r\n    seq_group varchar(10) not null,\r\n    seq_val int unsigned not null\r\n);<\/pre>\n<\/li>\n
  2. Crear una funci\u00f3n para obtener el valor siguiente e incrementarlo:\n
    delimiter \/\/\r\ndrop function if exists getNextCustomSeq\/\/\r\ncreate function getNextCustomSeq\r\n(\r\n\u00a0\u00a0\u00a0 sSeqName varchar(50),\r\n\u00a0\u00a0\u00a0 sSeqGroup varchar(10)\r\n) returns varchar(20)\r\nbegin\r\n\u00a0\u00a0\u00a0 declare nLast_val int; \r\n\r\n\u00a0\u00a0\u00a0 set nLast_val =\u00a0 (select seq_val\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from _sequence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and seq_group = sSeqGroup);\r\n\u00a0\u00a0\u00a0 if nLast_val is null then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set nLast_val = 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into _sequence (seq_name,seq_group,seq_val)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 values (sSeqName,sSeqGroup,nLast_Val);\r\n\u00a0\u00a0\u00a0 else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set nLast_val = nLast_val + 1;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update _sequence set seq_val = nLast_val\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName and seq_group = sSeqGroup;\r\n\u00a0\u00a0\u00a0 end if; \r\n\r\n\u00a0\u00a0\u00a0 set @ret = (select concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));\r\n\u00a0\u00a0\u00a0 return @ret;\r\nend\/\/ \r\n\r\ndelimiter ;<\/pre>\n<\/li>\n
  3. Crear un procedimiento almacenado para modificar el valor actual:\n
    delimiter \/\/\r\ndrop procedure if exists sp_setSeqCustomVal\/\/\r\ncreate procedure sp_setCustomVal(sSeqName varchar(50), \u00a0\r\n              sSeqGroup varchar(10), nVal int unsigned)\r\nbegin\r\n\u00a0\u00a0\u00a0 if (select count(*) from _sequence \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName \u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and seq_group = sSeqGroup) = 0 then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert into _sequence (seq_name,seq_group,seq_val)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 values (sSeqName,sSeqGroup,nVal);\r\n\u00a0\u00a0\u00a0 else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 update _sequence set seq_val = nVal\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where seq_name = sSeqName and seq_group = sSeqGroup;\r\n\u00a0\u00a0\u00a0 end if;\r\nend\/\/\r\ndelimiter ;<\/pre>\n<\/li>\n<\/ol>\n
    Haciendo algunas pruebas:<\/strong><\/p>\n
      \n
    1. Crear una tabla:\n
      create table custom_autonums\r\n(\r\n\u00a0\u00a0 id int not null primary key auto_increment,\r\n\u00a0\u00a0 seq_1 varchar(20), -- custom sequence 1\r\n\u00a0\u00a0 seq_2 varchar(20), -- custom sequence 2\r\n\u00a0\u00a0 unique(seq_1),\r\n\u00a0\u00a0 unique(seq_2)\r\n);<\/pre>\n<\/li>\n
    2. Crear un trigger para la tabla:\n
      delimiter \/\/\r\ndrop trigger if exists custom_autonums_bi\/\/\r\n\r\ncreate trigger custom_autonums_bi before insert on custom_autonums\r\nfor each row\r\nbegin\r\n\u00a0\u00a0 set new.seq_1 = getNextCustomSeq(\"seq_1\",\"001\");\r\n\u00a0\u00a0 set new.seq_2 = getNextCustomSeq(\"seq_2\",\"DBA\");\r\nend\/\/\r\n\r\ndelimiter ;<\/pre>\n<\/li>\n
    3. Insertando algunos valores:\n
      insert into custom_autonums (id) values (null),(null),(null);\r\nselect * from custom_autonums;\r\n+----+------------+------------+\r\n| id | seq_1\u00a0\u00a0\u00a0\u00a0\u00a0 | seq_2\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------------+------------+\r\n|\u00a0 4 | 001-000001 | DBA-000001 |\r\n|\u00a0 5 | 001-000002 | DBA-000002 |\r\n|\u00a0 6 | 001-000003 | DBA-000003 |\r\n+----+------------+------------+\r\n3 rows in set (0.00 sec)<\/pre>\n<\/li>\n
    4. Alterando los valores actuales:\n
      call sp_setCustomVal('seq_1','001',675);\r\n\r\ninsert into custom_autonums (id) values (null),(null),(null);\r\nselect * from custom_autonums;\r\n+----+------------+------------+\r\n| id | seq_1\u00a0\u00a0\u00a0\u00a0\u00a0 | seq_2\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+------------+------------+\r\n|\u00a0 4 | 001-000001 | DBA-000001 |\r\n|\u00a0 5 | 001-000002 | DBA-000002 |\r\n|\u00a0 6 | 001-000003 | DBA-000003 |\r\n|\u00a0 7 | 001-000676 | DBA-000004 |\r\n|\u00a0 8 | 001-000677 | DBA-000005 |\r\n|\u00a0 9 | 001-000678 | DBA-000006 |\r\n+----+------------+------------+\r\n6 rows in set (0.00 sec)<\/pre>\n<\/li>\n<\/ol>\n
      Enjoy!<\/p>\n
      Compartelo:<\/h3>