Aula 2 Extensoes procedurais SQL

Prévia do material em texto

Aula 2 Extensoes procedurais SQL
Prof. Idney Cordão
Slides elaborados a partir de apostila do 
Professor Eduardo Xavier 
Introdução
Quando iniciamos nosso aprendizado da linguagem SQL 
na disciplina Banco de Dados I, aprendemos comandos 
que servem para criar estruturas de armazenamento (por 
exemplo: CREATE TABLE) e para manipular dados 
nestas estruturas (por exemplo: SELECT).
Mas quem está habituado a lidar com linguagens de 
programação completas certamente sentiu falta de 
outras ferramentas presentes nestas linguagens, como 
controles de loops, estruturas condicionais e declarações 
de variáveis.
Extensões Procedurais
A linguagem SQL nasceu não procedural (declarativa). 
Isso significa que seus principais comandos foram 
projetados visando descrever o resultado desejado e não 
na forma de obtê-los.
Já nas linguagens procedurais, o programador tem 
meios para definir como determinada tarefa dever ser 
feita. Toda a lógica de programação é construída 
justamente para definir a sequência de etapas 
necessárias para se obter um determinado resultado.
Embora não possa ser considerada uma linguagem de 
programação completa, a SQL possui diversos 
comandos que atendem necessidades procedurais de 
programação, que veremos no decorrer desta aula.
Estruturas Condicionais
 Comandos que controlam o fluxo de execução, criando desvios de acordo 
com certas condições. 
IF
 A estrutura condicional mais comum é o comando “IF”. Com essa estrutura 
é possível definir a execução de ações de acordo com o resultado lógico da 
condição testada. Sintaxe:
 IF <CONDIÇÃO> THEN
 < CÓDIGO A SER EXECUTADO se CONDIÇÃO-01 for Verdadeira>
 ELSE
 <CÓDIGO A SER EXECUTADO se a CONDIÇÃO-01 for Falsa >
 END IF;
Estruturas Condicionais
Vejamos um exemplo:
DELIMITER $$
CREATE PROCEDURE teste1()
IF (SELECT COUNT(*) FROM empregado ) > 0 THEN 
SELECT EmpNome, EmpMatric FROM empregado;
ELSE 
SELECT EmpFuncao FROM empregado;
END IF;$$
DELIMITER ;
Call teste();
No exemplo anterior, a quantidade de empregados é testada. Se houver 
algum empregado cadastrado, serão exibidos os nomes e a matrícula de 
todos, porém, caso nenhum empregado cadastrado, serão exibidos apenas a 
função.
Estruturas Condicionais
Exercício: Crie um procedimento armazenado chamado 
ListaProjeto no Banco de Projetos para receber um 
parâmetro. Este parâmetro conterá 1 para mostrar a lista 
de projetos e 2 para mostrar a lista de projeto/empregados.
DELIMITER $$
CREATE PROCEDURE ListaProjeto(In cod INT)
IF cod = 1 THEN 
SELECT * FROM projeto;
ELSE 
SELECT * FROM projetoemp;
END IF;$$DELIMITER ;
Call ListaProjeto (1);
Estruturas Condicionais
 Outra forma de interferir em um fluxo de resultados é utilizando a cláusula 
CASE em comandos SELECT:
 Sintaxe:
CASE <VARIÁVEL>
 WHEN VALOR1 THEN
 <CÓDIGO A SER EXECUTADO se VALOR1 for igual ao conteúdo da VARIÁVEL>
 WHEN VALOR2 THEN
 <CÓDIGO A SER EXECUTADO se VALOR2 for igual ao conteúdo da VARIÁVEL>
 ELSE --Opcional
 <CÓDIGO A SER EXECUTADO se o conteúdo da VARIÁVEL não for igual aos 
valores 1 e 2>
 END CASE;
Estruturas Condicionais
 Exemplo:
SELECT nome, limite_credito,
CASE
WHEN limite_credito < 1000 THEN limite_credito * 3;
WHEN (limite_credito >= 1000) AND (limite_credito < 5000) THEN limite_credito * 2;
ELSE limite_credito;
END novo_limite
FROM CLIENTES;
 O comando anterior exibiu o nome e o limite de crédito atual de cada cliente, além de 
um cálculo sobre o limite que dá resultados diferentes a depender do valor original de 
cada cliente.
Sandra 3000 6000
Pedro 500 1500
 O CASE também pode ser usado em comandos UPDATE ou independente de Select 
ou update.
Estruturas Condicionais
Exercício: Refaça o exercício abaixo usando case.
Crie um procedimento armazenado chamado ListaProjetoCase no 
Banco de Projetos para receber um parâmetro. Este parâmetro 
conterá 1 para mostrar a lista de projetos e 2 para mostrar a lista de 
projeto/empregados.
DELIMITER $$
CREATE PROCEDURE ListaProjetoCase(In cod INT)
CASE cod
WHEN 1 THEN 
SELECT * FROM projeto;
ELSE 
SELECT * FROM projetoemp;
END CASE;$$DELIMITER ;
Call ListaProjetoCase (1);
Repetições
 Outra característica procedural muito útil para elaborar soluções mais 
complexas de manipulação de dados é criar e controlar laços de repetição, 
onde um bloco de comandos é executado por uma quantidade controlada 
de vezes.
 A quantidade de vezes é controlada por meio de uma condição que é 
testada a cada vez que o bloco de comandos é executado. Os controles de 
laços mais comuns são os comandos LOOP e WHILE.
Sintaxe :
[<rótulo>:] LOOP
declarações
END LOOP [<rótulo>];
Repetições
 Exemplo:
DELIMITER //
CREATE PROCEDURE acumula (limite INT)
BEGIN
DECLARE contador INT DEFAULT 0;
DECLARE soma INT DEFAULT 0;
loop_teste: LOOP
 SET contador = contador + 1;
 SET soma = soma + contador;
 IF contador >= limite THEN
 LEAVE loop_teste;
 END IF;
END LOOP loop_teste;
SELECT soma;
END//
DELIMITER ;
CALL acumula(10);
Repetições
Exercício 1: faça o exercício abaixo usando LOOP.
Crie um procedimento armazenado chamado Fatorial no Banco de 
Projetos para receber um parâmetro e devolver outro como resposta. 
O primeiro parâmetro indica um número para calculo do seu fatorial. 
Calcular o fatorial do número e devolver o resultado.
Executar o procedimento e mostrar o resultado.
Repetições
 Exercicio 1:
DELIMITER $$
 CREATE PROCEDURE Fatorial(IN num INT, OUT tot INT)
BEGIN
 SET tot = 1;
 loop_teste: LOOP
 IF num < 2 THEN
 LEAVE loop_teste; 
END IF;
SET tot = tot * num;
 SET num =num - 1; 
END LOOP loop_teste;
END$$
DELIMITER ;
CALL Fatorial(5,@result);
Select @result;
Repetições
Exercício 2: faça o exercício abaixo usando LOOP.
Crie um procedimento armazenado chamado ListaProjetoLoop1 no 
Banco de Projetos para receber dois parâmetros. O primeiro 
parâmetro indica o primeiro Código de Empregado a ser contado e o 
segundo indica o último. Mostre a quantidade de empregados da 
tabela Empregados neste intervalo.
Repetições
 Exercicio 2:
DELIMITER //
CREATE PROCEDURE ListaProjetoLoop1 (IN ini INT, fim INT)
BEGIN
DECLARE contador INT DEFAULT 0;
DECLARE soma INT DEFAULT 0;
DECLARE qtde INT DEFAULT 0;
SET contador = ini;
loop_teste: LOOP
 IF contador > fim THEN
 LEAVE loop_teste;
 END IF;
Select count(*) into qtde from emp where CodEmp = contador;
IF qtde = 1 THEN 
Set soma = soma + 1;
END IF;
SET contador = contador + 1;
Repetições
 Exercicio 2:
END LOOP loop_teste;
Select soma;
END//
DELIMITER ;
call listaprojetoloop1(2146,8191);
Repetições
Exercício 3: faça o exercício abaixo usando LOOP.
Inclua mais 5 linhas na tabela de projetos, com códigos de projetos 
PRJ00x, onde x cresce de um em um. 
Crie um procedimento armazenado chamado ListaProjetoLoop no 
Banco de Projetos para receber um parâmetro. Este parâmetro 
conterá a quantidade de projetos a atualizar. Atualize o tipo para 
‘Loop’ nos projetos de PRJ001 a PRJ00x, onde x é o parâmetro 
informado.
Utilize concat(‘PRJ00',numero)
Repetições
DELIMITER //
CREATE PROCEDURE ListaProjetoLoop (limite INT)
BEGIN
DECLARE contador INT DEFAULT 0;
set contador = limite;
loop_teste: LOOP
 UPDATE projeto SET tipo = ‘LOOP’ where codproj = concat(‘PRJ00',contador);
 SET contador = contador - 1;
 IF contador = 0 THEN
 LEAVE loop_teste;
 END IF;
END LOOP loop_teste;
END//
DELIMITER ;
Call ListaProjetoLoop (4);
Repetições
 Outra forma de fazer repetições:
WHILE.
Sintaxe :
[<rótulo>:] WHILE condição DO
 declarações
END WHILE [<rótulo>];Repetições
 Exemplo While:
DELIMITER //
CREATE PROCEDURE acumula_while (limite INT )
BEGIN
DECLARE contador INT DEFAULT 0;
DECLARE soma INT DEFAULT 0;
WHILE contador < limite DO
 SET contador = contador + 1;
 SET soma = soma + contador;
END WHILE;
SELECT soma;
END//
DELIMITER ;
CALL acumula_while (10);
Existe ainda o comando de repetição REPEAT
Repetições
Exercício 4: Refaça o exercício abaixo usando While.
Crie um procedimento armazenado chamado Fatorial no Banco de 
Projetos para receber um parâmetro e devolver outro como resposta. 
O primeiro parâmetro indica um número para calculo do seu fatorial. 
Calcular o fatorial do número e devolver o resultado.
Executar o procedimento e mostrar o resultado.
Repetições
 Exercicio 4:
DELIMITER $$
 CREATE PROCEDURE FatorialWhile(IN num INT, OUT tot INT)
BEGIN
 DECLARE I INTEGER; 
 SET tot = 1;
 SET I =1;
calculo: WHILE (I <= num) DO
SET tot = tot * I; 
SET I = I + 1; 
END WHILE calculo;
END$$
DELIMITER ;
CALL FatorialWhile(5,@result);
Select @result;
Repetições
Exercício 5: Refaça o exercício abaixo usando While.
Crie um procedimento armazenado chamado ListaProjetoLoop1 no 
Banco de Projetos para receber dois parâmetros. O primeiro 
parâmetro indica o primeiro Código de Empregado a ser contado e o 
segundo indica o último. Mostre a quantidade de empregados da 
tabela Empregados neste intervalo.
Repetições
 Exercicio 5:
DELIMITER //
CREATE PROCEDURE ListaProjetoLoop1While (IN ini INT, fim INT)
BEGIN
DECLARE contador INT DEFAULT 0;
DECLARE soma INT DEFAULT 0;
DECLARE qtde INT DEFAULT 0;
SET contador = ini;
while (contador < = fim) DO 
Select count(*) into qtde from emp where CodEmp = contador;
IF qtde = 1 THEN 
Set soma = soma + 1;
END IF;
SET contador = contador + 1;
Repetições
 Exercicio 5:
END WHILE;
Select soma;
END//
DELIMITER ;
call listaprojetoloop1While(2146,8191);
Repetições
Exercício 6: Refaça o exercício abaixo usando While.
Crie um procedimento armazenado chamado ListaProjetoWhile no 
Banco de Projetos para receber um parâmetro. Este parâmetro 
conterá a quantidade de projetos a atualizar. Atualize o tipo para 
‘While’ nos projetos de PRJ001 a PRJ00x, onde x é o parâmetro 
informado.
Utilize concat(‘PRJ00',numero)
Repetições
DELIMITER //
CREATE PROCEDURE ListaProjetoWhile (limite INT)
BEGIN
DECLARE contador INT DEFAULT 0;
set contador = limite;
WHILE contador > 0 DO
 UPDATE projeto SET tipo = ‘While’ where codproj = concat(‘PRJ00',contador);
 SET contador = contador - 1;
END WHILE;
END//
DELIMITER ;
Call ListaProjetoWhile (2);
Manipulação de strings
LEN ou LENGTH: Usada para determinar o tamanho em 
bytes de uma determinada cadeia de caracteres.
SELECT nome, LENGTH(nome) FROM CLIENTES
LEFT/RIGHT: extrai os “n” caracteres à esquerda/direita 
de uma cadeia de caracteres.
SELECT nome, LEFT (nome,2) FROM CLIENTES
SUBSTRING: extrai os “n” caracteres a partir de uma 
determinada posição em uma cadeia de caracteres, 
contando-se da esquerda para a direita.
SELECT nome, SUBSTRING(nome,3,2) FROM 
CLIENTES
Manipulação de strings
TRIM, LTRIM e RTRIM: retiram espaços à esquerda 
e/ou à direita de uma cadeia de caracteres.
COMANDO: RESULTADO:
SELECT LTRIM ‘ Ana Paula’ Ana Paula
TRANSLATE: substitui caracteres de uma cadeia 
tomando como base uma outra cadeia de caracteres
Sintaxe:
TRANLATE (‘string a ser traduzida’, ‘o que procurar’, o 
que substituir’)
COMANDO: RESULTADO:
SELECT TRANSLATE(‘ANA PAULA’,’P’,’S’) ANA SAULA
Manipulação de datas
Data e Hora Corrente
SELECT CURRENT_DATE() Exibe a data corrente
SELECT CURRENT_TIME() Exibe a hora corrente
 SELECT DATEDIFF (CURRENT_DATE(), data_nascimento) 
FROM CLIENTES
Exibe a diferença EM DIAS entre a data corrente e a coluna 
“data_nascimento” para cada linha da tabela CLIENTES
 SELECT ADDDATE (data_nascimento, 5) 
FROM CLIENTES Exibe a coluna “data_nascimento” para cada linha da 
tabela CLIENTES acrescida de 5 DIAS
	Slide 1
	Slide 2
	Slide 3
	Slide 4
	Slide 5
	Slide 6
	Slide 7
	Slide 8
	Slide 9
	Slide 10
	Slide 11
	Slide 12
	Slide 13
	Slide 14
	Slide 15
	Slide 16
	Slide 17
	Slide 18
	Slide 19
	Slide 20
	Slide 21
	Slide 22
	Slide 23
	Slide 24
	Slide 25
	Slide 26
	Slide 27
	Slide 28
	Slide 29
	Slide 30