Store Procedure

Estrutura procedural simples

Cada banco de dados decidiu criar uma liguagem extra para ter um mellhor controle de fluxo, e a porta para tudo isso foi as Store Procedures.


                        CREATE PROCEDURE nome_procedure
    Parametros

    BEGIN
    
    DECLARE
    
        Variaveis, cursores, constantes, tabelas, estruturas e exceptions
        PL/SQL statements
    
    END;

Regras de nomenclatura

O nome da SP deve conter apenas letras e numeros, $ e _ para demarcar espaços
Um tamanho máximo de 64 caracteres
Deve ser nome único
É case sensitive deve ser referenciada de maneira idêntica

Usando a Store Procedure

CALL nome_Sp;

Modelo pratico


                    USE `nomeBD`;
    DROP procedure IF EXISTS `primeiro_procedure`;
                
                          
    DELIMITER $$ define delimitador para $$
    USE `nomeBD`$$

    CREATE PROCEDURE `primeiro_procedure` ()
    BEGIN
    SELECT 'hello world' as Mensagem; note que o delimitador interno continua sendo o ;
    END$$
                    
    DELIMITER ;

Usando    

    CALL primeiro_procedure;

O Mesmo serve para alterar o comando, já que se existir ele apaga.
Para apagar é só usar o DROP procedure sem delimitar outras configurações.

VARIAVEIS

DECLARE fica responsável por iniciar a variável sem seguida o nome da mesma, o tipo de dados e um valor padrão(opcional).

Regras de nomenclatura

O nome da SP deve conter apenas letras e numeros, $ e _ para demarcar espaços
Deve ser nome único
Um tamanho máximo de 255 caracteres
É case sensitive
Se não declarar Default o valor da variável recebe NULL
Termina com ;
É possível declarar varias variáveis desde que tenham o mesmo tipo

Tipos

TIPO	definição
`VARCHAR(n)`	Caracteres tipo texto com tamanho de n.
`INTEGER`	Variaveis tipo Inteiro.
`DECIMAL(i,f)`	Variavies decimais 'i' e a fração em 'f'.
`DATE`	Guarda data AAAA-MM-DD.
`TIMESTAMP`	Guarda data e hora AAAA-MM-DD HH-MM-SS.

Modelo uso de Declare


                    DELIMITER $$
USE 'nome_bd'$$

    CREATE procedure 'ver_variavel'()
    BEGIN

    declare texto char(15) default 'Hellow World';
    declare numero decimal(6,3) default 999.666;
    declare tempo datetime default localtimestamp;


    SELECT texto;
    SELECT numero;
    SELECT tempo;
    

    END $$

DELIMITER;

Múltiplas Variaveis


                    ...
    CREATE procedure 'ver_multiplos'()
    BEGIN
    declare num_1, num_2 int default 369;
    SELECT num_1, num_2;

    END$$
DELIMITER;

Alterando Valores


                    ...
    CREATE procedure 'ver_valor'()
    BEGIN
    declare num_1 int default 369;
    SELECT num_1;

    SET num_1 = 963;
    SELECT num_1;

    END$$
DELIMITER;

Entrada por procedure


                    DELIMITER $$
use `nome_bd`$$
    create procedure `inclui_produto`()
    begin
    declare vNome varchar(45) default 'produto';
    declare vPreco decimal(5,2) default '36.90';
                        
    insert into nome_tabela
    (nome, preco)
    value
    (vNome, vPreco);
    end$$
    DELIMITER ;
                        
                        
    call inclui_produto;

Parametros

Os parametros no PL/SQL se assemelha muito a funções em POO


                    ...
    CREATE PROCEDURE `produto_parametro`(vNome varchar(45), vPreco decimal(5,2))
    BEGIN
    INSERT INTO tabela
    (nome, preco)
    VALUES
    (vNome, vPreco);
    END$$
...

CALL produto_parametro( 'produto', 9.99);

Tratando erros e salvando retorno

Tratando erros

É possível ter um retorno mais amigável através da tratava erros.
O comando DECLARE EXIT libera a execução do programa como um "break", HANDLER FOR escuta o retorno, então referencie o codigo do erro logo após a declaração.


                            ...
        BEGIN
        DECLARE aviso varchar(36);
        DELCARE EXIT HANDLER FOR 1062
            begin
            set aviso = 'Chave repetida'
            select aviso;
        end;
    
        INSERT INTO nome_tabela
        (id_produto, nome, preco)
        VALUES
        (vId_produto, vNome, vPreco)
        SET aviso = 'Produto incluido!';
        SELECT aviso;
    
        END$$
    ...

Salvando retorno

Para salvar um retorno basta referenciar a resposta com um INTO seguido do nome da variavel que vai receber o valor, devidamente declarada.


                            ...
        CREATE PROCEDURE 'recebe_select'( vId_produto varchar (40))
        BEGIN
        DECLARE vProduto VARCHAR(45);
        SELECT produto INTO vProduto FROM tabela WHERE id_produto = vId_produto;
        SELECT vProduto ;
        END$$
    ...

Controle de Fluxo

IF

Controle de fluxo com duas saídas possíveis, caso atenda os requisitos da condição e outro para caso não atenda.Estrutura básica;


                        IF condition THEN
    if_statements;
    ELSE
    else_statements;
    END IF

Modelo Pratico


                            create procedure `curso_nivel`(vId int)

    begin
    declare vResultado varchar (20);
    declare vNivel int;

    select NIVEL INTO vNivel from habilidades
    where id_plano = vId;
                            
    if vNivel > 1 then
        set vResultado = 'Intermediario';
    else
        set vResultado = 'Iniciante';
    end if;

    select vResultado;
    end$$
                        
                            create procedure `curso_nivel`(vId int)
                        
    begin
    declare vResultado varchar (20);
    declare vNivel int;
                            
    select NIVEL INTO vNivel from habilidades
    where id_plano = vId;
                                                    
    if vNivel = 1 then
        set vResultado = 'Iniciante';
    
    elseif vNivel = 2 then
        set vResultado = 'Intermediario';
    
    else
        set vResultado = 'Avançado';
    end if;
                        
    select vResultado;
    end$$

CASE

Controle de fluxo do de uma variavel versus uma lista de opções, atendido o requisito para algum dos itens listados um comando correspondente será disparado.

Estrutura básica;


                        CASE selector
    WHEN selector_value_1 THEN then_statements_1;
    WHEN selector_value_2 THEN then_statements_2;
    WHEN ...
    
    [ELSE else_statements]
    END CASE;

Modelo pratico


                            create procedure `class_memoria`(vMATERIA varchar(36))
    begin
    declare vMemoria tinyint;
    select SITUACAO_MEMORIA into vMemoria from metas
    where MATERIA = vMATERIA;
    CASE vMemoria
    when 1 then select 'Boa' as condição de memoria;
    when 2 then select 'Moderada' as condição de memoria;       
    when 3 then select 'Decaindo' as condição de memoria;
    when 4 then select 'Esquecimento' as condição de memoria;
    when 5 then select 'Vaga Lembrança' as condição de memoria;
    else select 'Ainda não começou' as Sem memoria;
    end case;

CASE ERRO

Podemos tratar um case sem ELSE e comando alheio. O mesmo pode ter um retorno sem interromper o processo como no tratando erros.

Modelo pratico


                            create procedure `class_memoria`(vMATERIA varchar(36))
    begin
    declare vMemoria tinyint;
    declare msgErro varchar (45);
    declare continue handler for 1339 set msgErro = ''O case não tem retorno previsto para condição evidenciada.
    select SITUACAO_MEMORIA into vMemoria from metas
    where MATERIA = vMATERIA;
    CASE vMemoria
    when 1 then select 'Boa' as condição de memoria;
    when 2 then select 'Moderada' as condição de memoria;       
    when 3 then select 'Decaindo' as condição de memoria;
    when 4 then select 'Esquecimento' as condição de memoria;
    when 5 then select 'Vaga Lembrança' as condição de memoria;
    else select 'Ainda não começou' as Sem memoria;
    end case;
    SELECT msgErro;
    end $$

Observação

O CASE ainda pode ser utilizado no lugar do ELSEIF para caso de grande linha de testes, uma vez que o ELSEIF testa a variável com linha a linha e o CASE testa apenas a condição correspondente.

Looping

Comandos que vão ser repetidos até que a condição seja satisfeita.


                        WHILE condition
    DO statements;
    END WHILE;

Modelo pratico


                                CREATE PROCEDURE `looping_while` (vNumInicial INT , vNumFinal INT)
            BENGIN
                DECLARE vContador INT;
                DELETE FROM TAB_LOOPING;
                SET vContador = vNumInicial;
área de rep      WHILE vContador <= vNumFinal
                DO
                    INSERT INTO TAB_LOOPING (ID) VALUES (vContador);
                    SET vContador = vContador + 1;
                END WHILE;
                SELECT * FROM TAB_LOOPING;
            END

Cursores

Comandos que vão retornar uma array com todos os resultados.


                        DECLARE @nome;
    DECLARE CURSOR_X CURSOR FOR;
    SELECT NOME FROM TABELA;
    OPEN CURSOR_X;
    FETCH CURSOR_X; INTO @nome;
    FETCH CURSOR_X; INTO @nome;
    FETCH CURSOR_X; INTO @nome;
    ...
    CLOSE CURSOR_X;

Modelo pratico


                        CREATE PROCEDURE `looping_cursor` ()
    BENGIN
        DECLARE limite_cursor INT DEFAULT 0;
        DECLARE vNome VARCHAR(45);
        DECLARE c CURSOR FOR SELECT COLUNA FROM tabela LIMIT 3;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET limite_cursor= 1;
        OPEN c;
        WHILE limite_cursor = 0
        DO
            FETCH c INTO vNome;
            SELECT vNome;
        END WHILE;
        CLOSE c;

Retornando mais de um campo e concatenando resposta.

Modelo pratico


                        CREATE PROCEDURE `retorno_cursor` ()
    BENGIN
        DECLARE limite_cursor INT DEFAULT 0;
        DECLARE vNome, vPlataforma VARCHAR(45);
        DECLARE vNivel, vMemoria TINYINT;
        DECLARE c CURSOR FOR SELECT COLUNA, COLUNA1, COLUNA2, COLUNA3 FROM tabela;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET limite_cursor= 1;
        OPEN c;
        WHILE limite_cursor = 0
        DO
            FETCH c INTO vNome, vPlataforma, vNivel, vMemoria;
            IF limite_cursor = 0 then
            SELECT concat(vNome, '-', vPlataforma, 'NV: ', vNivel, 'Memoria: ', vMemoria);
            END IF;
        END WHILE;
        CLOSE c;

        END $$

Funções

Função é uma serie de comandos bem estruturado que diferente de rotina no final me retorna um valor. A usabilidade das funções são como as nativas do MySQL (avg, concat, sum) é possível criar uma aplicabilidade infinita.

Normalmente as instalações MySQL não permitem a criação de funções, para isso é necessário executar a linha de comando SET GLOBAL log_bin_trust_function_creators = 1; Estrutura básica;


                        CREATE FUNCTION `name_function`(parameters)
    RETURNS datatype;

    BEGIN

    DECLARE declaration_statement;
    executable_statement;
    RETURN statement;
    END;

Diferente de uma procedure a function é chamada por SELECT.

Modelo pratico


                        ...
    CREATE FUNCTION `f_memoria`(vMemoria tinyint) 
    RETURNS varchar(27) CHARSET utf8
    BEGIN
    declare vRetorno varchar(27) default "";
    CASE vMemoria
    when 1 then set vRetorno = 'Boa';
    when 2 then set vRetorno = 'Moderada';
    when 3 then set vRetorno = 'Decaindo';
    when 4 then set vRetorno = 'Esquecimento';
    when 5 then set vRetorno = 'Vaga Lembrança';
    else set vRetorno = 'Ainda não começou';
    end case;
    RETURN vRetorno;
    END $$

utilizando
    SELECT COLUNA, f_memoria(COLUNA1) FROM tabela;

Gerando números aleatórios.


                        CREATE FUNCION `num_aleatorio`(min int, max int)
    RETURNS INT
    BEGIN
        DECLARE vRetorno INT;
        SELECT FLOOR((RAND() * (max - min + 1))+ min) INTO vRetorno;
        RETURN vRetorno;
    END

Triggers

É um gatilho ou um escutador de processos DML. Estrutura básica;


                        CREATE TRIGGER nome TIME EVENT
    ON tabela
    FOR EACH ROW
    declarações

O mesmo pode ser separado nos tempos BEFORE, AFTER.
E nas operações DML INSERT, UPDATE, DELETE.

Modelo pratico


                        CREATE TRIGGER tr_nome BEFORE INSERT
    ON tabela
    FOR EACH ROW
    SET NEW.coluna3 = (NEW.coluna1 * x);

Disparado
    INSERT INTO tabela (coluna, coluna1)
    VALUES ('exemplo', 9);