Create stored Procedure with Mysql

Question

I do bank EAD and have very little material to help me do my activities. I am with this question to resolve, if anyone can help me or help me with some way of study material I am very grateful.

Create a stored Procedure calling for sp_generate_quartos which generates a load of 10 new rooms from each of the 4 existing room types. For room numbers, it shall be random according to the following rule:

Tipo de Quarto Número do Quarto
1              Entre 1000 e 1999
2              Entre 2000 e 2999
3              Entre 3000 e 3999
4              Entre 4000 e 4999

In the script, in the meantime, it must be done in such a way that, if it is created a fifth type of room, the script would generate 10 rooms for he also, between the ranges of 5000 and 5999, and so on before.

TABLE TYPE ROOM

CREATE TABLE tb_tipo_quarto
  (
    cd_tipo_quarto        TINYINT NOT NULL AUTO_INCREMENT,
    nm_tipo_quarto        VARCHAR(50) NOT NULL ,
    vl_tipo_quarto_diaria NUMERIC(7,2) NOT NULL,
    PRIMARY KEY(cd_tipo_quarto)
  ) ;

Answer 1

Good come on, first you will have to create a table of rooms to store this information:

CREATE TABLE tb_quarto(
    cd_quarto INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    cd_tipo_quarto INT NOT NULL,
    nm_quarto INT
);

Then we create the Precedent where we will pass the number of the room 1,2,3 etc... and it will insert in the tb_rooms 10 random rooms among the numbers you passed(thousand of each number), if the rooms already have 10 rooms the Precedent does nothing, follows:

DROP PROCEDURE IF EXISTS sp_gera_carga_quarto;
DELIMITER |
CREATE PROCEDURE sp_gera_carga_quarto(
    p_cd_tipo_quarto INT
)
BEGIN

    DECLARE v_n_quarto INT;
    DECLARE v_qtd_quarto INT;
    DECLARE v_cont INT DEFAULT 1;
    DECLARE v_quarto_existe TINYINT;
    DECLARE v_inicio INT;
    DECLARE v_fim INT;

    WHILE v_cont <= 10 DO

        SET v_inicio = p_cd_tipo_quarto * 1000;
        SET v_fim = v_inicio + 999;

        SELECT
                COUNT(*)
        INTO v_qtd_quarto
        FROM tb_quarto
        WHERE cd_tipo_quarto = p_cd_tipo_quarto;

        IF v_qtd_quarto = 10 THEN

            SET v_cont = 11;

        ELSE 

            SET v_n_quarto = (select floor(v_inicio+(rand()*(v_fim-v_inicio))));

            SELECT
                    IF(COUNT(*) > 0,1,0)
            INTO v_quarto_existe
            FROM tb_quarto
            WHERE nm_quarto = v_n_quarto;

            IF v_quarto_existe = 0 THEN
                INSERT INTO tb_quarto(cd_tipo_quarto,nm_quarto)
                VALUES(p_cd_tipo_quarto,v_n_quarto);
                SET v_cont = v_cont + 1;
            END IF;


        END IF;

    END WHILE;

    SELECT 'carga de quartos geradas';

END
|
DELIMITER ;

Ex charge generated from room 1 from number 1000 to 1999;

CALL sp_gera_carga_quarto(1);

Returning to the rooms:

SELECT
    *
FROM tb_quarto
WHERE cd_tipo_quarto = 1;

Upshot: