create table prueba(
cod1 char(9),
cod2 char(8),
nombre varchar(10)
);
DELIMITER $
CREATE PROCEDURE cargar_datos(IN nnombre VARCHAR(20)
)
BEGIN
DECLARE ncod1 CHAR(9);
DECLARE ncod2 Char(8);
DECLARE NUM INT;
DECLARE FECHA CHAR(9);
SET ncod1=(SELECT DATE_FORMAT(NOW(),'%Y%m%d'));
SET NUM = (SELECT LTRIM(RIGHT(MAX(cod2),7)) from prueba);
SET FECHA=(SELECT MAX(cod1) FROM PRUEBA);
IF FECHA=ncod1 THEN
IF NUM >= 1 AND NUM <= 8 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T000000',CAST(NUM AS CHAR)));
ELSEIF NUM >=9 AND NUM <=98 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T00000',CAST(NUM AS CHAR)));
ELSEIF NUM >=99 AND NUM <=998 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T0000',CAST(NUM AS CHAR)));
ELSEIF NUM >=999 AND NUM <=9998 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T000',CAST(NUM AS CHAR)));
ELSEIF NUM >=9999 AND NUM <=99998 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T00',CAST(NUM AS CHAR)));
ELSEIF NUM >=99999 AND NUM <=99998 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T0',CAST(NUM AS CHAR)));
ELSEIF NUM >=999999 AND NUM <=999998 THEN
SET NUM = NUM + 1;
SET ncod2= (SELECT CONCAT('T',CAST(NUM AS CHAR)));
ELSE
SET ncod2= (SELECT 'T0000001');
END IF;
ELSE
SET ncod2= (SELECT 'T0000001');
END IF;
INSERT INTO PRUEBA VALUES(ncod1,ncod2,nnombre);
END $
call cargar_datos('LUIS');
SELECT * FROM PRUEBA;
Comentarios sobre la versión: versión 1.0 (0)
No hay comentarios