Home / Questions / 8584
Accepted
dddenis
Asked: 2020-04-28 01:48:21 +0800 CST 2020-04-28 01:48:21 +0800 CST

Optimization of the transactional procedure in MySQL

  • 772

Hi, I'm doing some exercises and I have solved the following one but I can't optimize it anymore, I would like to do the 6 INSERTS in a more generic way, I must use prepare statements yes or yes.

This is the statement.

Exercise DCL_42: Create a procedure named reward that, given a date and the 7 numbers (the 6 of the award plus the complementary number), enter them into the corresponding table by year (if the table does not exist, make it create it by calling the previous procedure) . Keep in mind that if the draw is not held on a Thursday or Saturday, the error 'ERROR_DI' is passed to us as an output parameter and nothing is done. Execution example: CALL reward('2016/4/23',8,12,21,25,45,49,3,@error);

The table is this.

CREATE TABLE IF NOT EXISTS sorteig_2016 (
            data            DATE                    NOT NULL,
            nombre          TINYINT                 NOT NULL,
            complementari   ENUM('SI','NO')         NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;);

This is my code.

SET @error = null;

DROP PROCEDURE IF EXISTS premiat;
DELIMITER // 
CREATE PROCEDURE premiat(IN par_data DATE, IN par_nombre1 TINYINT, IN par_nombre2 TINYINT, IN par_nombre3 TINYINT, IN par_nombre4 TINYINT,
                    IN par_nombre5 TINYINT, IN par_nombre6 TINYINT, IN par_complementari TINYINT, OUT par_error VARCHAR(8))
BEGIN

# ERRORES -------------------------------------------------------------------------------------------------------------------------------

DECLARE EXIT HANDLER FOR 1048 BEGIN END; -- El parámetro no puede ser nulo.
DECLARE EXIT HANDLER FOR 1064 BEGIN END; -- Algun/nos parámetros son incorrectos.

DECLARE EXIT HANDLER FOR 1146 -- La tabla no existe.
    BEGIN 
        CALL taula(YEAR(par_data)); -- Crea la tabla.
        /* Activo recursividad */ SET @@max_sp_recursion_depth = 254;
        CALL premiat(par_data, par_nombre1, par_nombre2, par_nombre3, par_nombre4, par_nombre5, par_nombre6, par_complementari, par_error);
        /* Desactivo recursividad */ SET @@max_sp_recursion_depth = 0;
    END;

# CÓDIGO --------------------------------------------------------------------------------------------------------------------------------

IF (WEEKDAY(par_data) = 3 OR WEEKDAY(par_data) = 5) THEN -- Si la fecha cae en jueves o sábado.
    SET par_error := 'ERROR_DI';
ELSE
    START TRANSACTION;
        SET @_sentencia_base =  CONCAT(CONCAT('INSERT INTO sorteig_',YEAR(par_data),' '), 'VALUES (', CONCAT("'", par_data, "'"));

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre1,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre2,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre3,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre4,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre5,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_nombre6,',\'NO\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;

        SET @sentencia = CONCAT(@_sentencia_base,',',par_complementari, ',\'SI\');');
        PREPARE consulta FROM @sentencia;   EXECUTE consulta;   DEALLOCATE PREPARE consulta;
    COMMIT;
END IF;

END //
DELIMITER ;

CALL premiat('2016/4/22', 8, 12, 21, 25, 45, 49, 3, @error);
CALL premiat('2016/2/21', 8, 12, 30, 40, 45, 60, 2, @error);
CALL premiat('2016/3/20', 10, 12, 30, 40, 45, 60, 1, @error);
CALL premiat('2016/2/19', 10, 20, 30, 40, 50, 60, 5, @error);
mysql

1 Answers

  1. Best Answer

    I hope it works for you. Is it mandatory that you create your table with that data? Perhaps it would be interesting to create it this way:

    CREATE TABLE IF NOT EXISTS sorteig_2016 (
    data    DATE NOT NULL,
    nombre  TINYINT NOT NULL,
    num1 INT NOT NULL,
    num2 INT NOT NULL,
    num3 INT NOT NULL,
    num4 INT NOT NULL,
    num5 INT NOT NULL,
    num6 INT NOT NULL,
    complementari INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    and in your procedure at the moment of inserting you would only do 1 and not the 7 that consume a little more time, and you know that the complementary number is the field with the complementary name. reduce 7 inserts to 1 I hope it helps you a bit. 7 rows of information to 1.

    • 1