Create stored procedure with MySQL

Question

Create stored procedure with MySQL

Navigation

#1 by (0 votes)

-1

I do EAD for a bank and have very little material to help me do my activities. I have this question to solve, if anyone can help me or help me with some study material path I am very grateful.

Create a stored procedure call sp_generate_quartos which generates a load of 10 new rooms from each of the 4 existing room types. As for room numbers, they should 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 script , in the meantime, it should be done in such a way that if it is created a fifth room type, script would generate 10 rooms for it also, between the intervals of 5000 and 5999, and thus on.

TABLE ROOM TYPE

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)
  ) ;

mysql stored-procedures

asked by anonymous 30.11.2017 / 14:25

1 answer

Images with content-type like text / html, how to solve? [closed] Pusho notification Web App progressive web app

score 0 · Answer 1

Well come on, first you will have to create a room table 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 procedure where we will pass the room number 1,2,3 etc ... and it will insert in the tb_quartos 10 random rooms between the numbers that you have passed (thousand of each number), if the rooms already have 10 the procedure does nothing, it 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 cargo generated from room 1 from number 1000 to 1999;

CALL sp_gera_carga_quarto(1);

Returning rooms:

SELECT
    *
FROM tb_quarto
WHERE cd_tipo_quarto = 1;

Result: