É possível realizar operações matemáticas simples nos valores de uma coluna e retornar resultados em uma coluna calculada. Para isso usamos os operadores comuns matemáticos (+, -, *, / e %). Os módulos podem ser calculados também com mod e a divisão inteira é feita com o operador div.
Veja um exemplo:
select nome, carga * 2 as 'Carga Dobrada' from cursos;
PS: Essa coluna Carga Dobrada não existe, ela aparecerá apenas pra esse select. O operador as (alias) que permite criar ela, é como um "apelido" pra coluna.
Podemos mostrar as mensagens diretamente no MySQL sem vincular à nenhum banco ou tabela:
select 'Olá Mundo';
Ou assim:
select 'Olá Mundo' as 'Mensagem';
Dessa forma, podemos fazer operações diretas no MySQL, por exemplo:
select 2 * 9 / 3;
Ou assim:
select 2 * 9 / 3 as 'Resultado';
Outro exemplo:
select nome, totaulas / 3 as 'Total de 1/3 das Aulas' from cursos;
Outro cálculo direto, onde retornamos uma divisão inteira e seu resto:
select 10 div 3 as 'Divisão de 10 por 3', 10 mod 3 as 'Resto de 10 por 3';
Também temos funções matemáticas no MySQL, por exemplo:
| Método | Significado |
|---|---|
| ceiling(x) | Arredonda x para o inteiro acima |
| floor(x) | Arredonda x para o inteiro abaixo |
| round(x) | Arredonda x para o inteiro mais próximo |
| pi() | Retorna o valor de PI |
| pow(x, y) | Retorna x elevado a y |
| sqrt(x) | Retorna a raiz quadrada de x |
| sin(x) | Seno de um ângulo específico x, em radianos |
| concat(x, y) | Concatena dois conteúdos, como textos e números |
| bin(x) | Retorna a representação binária de x |
| oct(x) | Retorna a representação octal de x |
| hex(x) | Retorna a representação hexadecimal de x |
| md5(x) | Retorna o hash MD5 de x |
| sha1(x) | Retorna o hash SHA1 de x |
Essas funções também podem ser usadas nos valores de uma coluna. Por exemplo:
select nome, ceiling(totaulas / 3) as 'Total de 1/3 das Aulas' from cursos;
Funções e Procedimentos são dos tipos de rotinas armazenadas, parte da especificação SQL. São um pouco similares, mas com aplicações diferentes. São invocadas de formas diferentes também.
Uma função é usada para gerar um valor que pode ser usado em uma expressão. O valor é geralmente baseado em um ou mais parâmetros fornecidos à função. É executada como parte de uma expressão.
Para criar uma função, fazemos assim:
create function multi(a decimal(10, 2), b int)
returns int return a * b;
PS: Note que as variáveis a e b são locais.
E para executar ela:
select multi(2.5, 4) as 'Resultado';
E da mesma forma, podemos usar essa função em dados armazenados, assim:
select nome, multi(carga, 3) as 'Carga Triplicada' from cursos where idcurso = 2;
Para excluir uma função, basta usar o comando drop, assim:
drop function multi;
Basicamente, as funções retornam valores, diferente dos procedimentos, que não retornam nada.
Um procedimento armazenado é uma sub-rotina disponível para aplicações que acessam sistemas de bancos de dados relacionais. Podem ser usadas para validação e dados, controle de acesso, execução de declarações SQL complexas e outras situações.
Para isso, criamos uma variável local chamada varId, que pegará como parâmetro a id do curso, assim:
create procedure ver_carga(varid smallint)
select nome, concat('A Carga é ', carga) as 'Carga Total' from cursos where idcurso = varid;
PS: O concat fará a concatenação da frase escrita com o dado do banco.
Para invocar o procedimento, fazemos assim:
call ver_carga(3);
E para excluir, fazemos assim:
drop procedure ver_carga;
Os blocos begin e end são contêineres usados para delimitar blocos de comandos a serem executados pela função ou store procedure. Cada declaração aninhada possuí um delimitador (;), Um bloco begin pode ser aninhado dentro de outros blocos.
Porém, o delimitador de ponto e vírgula pode ser problemático pois, ao ser encontrado em um procedimento ou função, ela finaliza imediatamente. É uma espécie de alias para o comando go. Devemos então mudar esse "atalho" e, para isso, usamos o comando delimiter para criar rotinas com declarações compostas.
Veja um exemplo abaixo:
delimiter $$
create function aumenta_carga(car decimal(10, 2), porc decimal(10, 2))
returns decimal(10, 2)
begin
return (car + car) * porc / 100;
end$$
delimiter ;
No código acima, o delimiter define que ele deve substituir o ponto e vírgula pelos dois cifrões (ou qualquer outro caracter definido por nós). As declarações da função ou procedimento vão entre o begin e o end (este definido pelos ois cifrões). No final o delimitador volta a ser o ; pela nova execução do delimiter.
E para invocar normalmente, fazemos assim:
select aumenta_carga(40, 10) as 'Resultado';
O mesmo vale para procedimentos:
delimiter //
create procedure ver_carga(varid smallint)
begin
select nome, concat('A Carga é ', carga) as 'Carga Total' from cursos where idcurso = varid;
end//
delimiter ;
E para executar, use o mesmo modo:
call ver_carga(4);
PS: Exclua todas as funções e procedimentos não utilizados, assim:
drop function aumenta_carga;
drop procedure ver_carga;
O escopo de uma variável diz respeito aos locais onde a variável "existe", ou seja, onde ela pode ser acessada.
Os níveis de escopo são esses:
Podemos criar variáveis locais em um procedimento ou função usando uma declaração declare dentro de um bloco begin. A variável pode ser criada e inicializada com um valor, se desejado. Ficam disponíveis apenas dentro do bloco onde foram criadas, e em blocos que existem dentro do bloco onde a variável foi criada. Após o bloco ter sido executado e encerrado, a variável é desalocada da memória.
Veja um exemplo abaixo:
delimiter //
create function aumenta_aulas(ident decimal(10, 2), porc decimal(10, 2))
returns decimal(10, 2)
begin
declare total decimal(10, 2);
select totaulas from cursos where idcurso = ident into total;
return (total + total) * porc / 100;
end//
delimiter ;
No caso acima, o into faz a atribuição a variável local.
E para executar, normalmente:
select * from cursos where idcurso = 4;
select aumenta_aulas(4, 10) as 'Aumento das Aulas';
Depois pode excluir essa função também.
Há dois tipos básicos de blocos condicionais, com if, elseif, else e endif, e con case, when, then, else e end case.
Veja um exemplo básico de uso:
delimiter //
create function calcula_aula(carg decimal(8, 2))
returns decimal(8, 2)
begin
declare aultot decimal(8, 2);
if carg < 20 then
set aultot = 30;
elseif carg < 50 then
set aultot = 60;
elseif carg < 80 then
set aultot = 100;
else
set aultot = 150;
end if;
return aultot;
end//
delimiter ;
PS: O Else if e o Else não são obrigatórios.
E para executar normalmente, fazemos assim:
select calcula_aula(50) as 'Total de Aulas';
Podemos fazer com case também, dessa forma:
-- Primeiro exclua a função anterior:
drop function calcula_aula;
-- E crie novamente com o case, assim:
delimiter //
create function calcula_aula(carg decimal(8, 2))
returns decimal(8, 2)
begin
declare aultot decimal(8, 2);
case
when carg < 20 then
set aultot = 30;
when carg < 50 then
set aultot = 60;
when carg < 80 then
set aultot = 100;
else
set aultot = 150;
end case;
return aultot;
end//
delimiter ;
O Case geralmente é utilizado quando existem muitas condições.
Exclua a função caso não a utilize mais.
Um bloco iterativo é um bloco de código que é executado repetidamente por um comando especial até que uma condição de parada o interrompa. Um bloco iterativo pode ser aninhado com outros.
O MySQL possui três tipos básicos de blocos iterativos, loop, repeat e while
Veja um exemplo básico de uso de loop:
delimiter //
create procedure acumula(limite int)
begin
declare contador int default 0;
declare soma int default 0;
laco_teste: loop
set contador = contador + 1;
set soma = soma + contador;
if contador >= limite then
leave laco_teste;
end if;
end loop laco_teste;
select soma as 'Soma Total do Loop';
end//
delimiter ;
PS: O leave interrompe o laço, é como o break.
E para executar:
call acumula(10);
Depois exclua o procedimento criado.
Também podemos usar o repeat para criar laços, dessa forma:
delimiter //
create procedure acumula_repita(limite tinyint unsigned)
begin
declare contador tinyint unsigned default 0;
declare soma int default 0;
repeat
set contador = contador + 1;
set soma = soma + contador;
until contador >= limite
end repeat;
select soma as 'Soma Total do Repeat';
end//
delimiter ;
E para executar:
call acumula_repita(10);
PS: Como o contador é incrementado antes do teste condicional, um valor como 0, ao ser passado, resulta em valor errado. Na verdade, ele garante pelo menos a execução uma vez, independente da condição.
Podemos usar um rótulo no bloco begin, para podermos "tratar" esse erro num if, e usar o leave com o nome desse rótulo para sair do bloco, assim:
-- Primeiro exclua o procedimento anterior:
drop procedure acumula_repita;
-- E crie novamente, assim:
delimiter //
create procedure acumula_repita(limite tinyint unsigned)
main: begin
declare contador tinyint unsigned default 0;
declare soma int default 0;
if limite < 1 then
select 'O valor deve ser maior que zero!' as 'Erro';
leave main;
end if;
repeat
set contador = contador + 1;
set soma = soma + contador;
until contador >= limite
end repeat;
select soma as 'Soma Total do Repeat';
end//
delimiter ;
Exclua o procedimento se não usar mais.