Projeto de Banco de Dados Stored Procedures e Funções

Projeto de Banco de Dados
Prof. Marcelo Siedler
Objetivos do documento:
•
Apresentar os conceitos de stored procedutes e funções.
•
Exercícios.
Referência: http://dev.mysql.com/doc/refman/4.1/pt/stored-procedures.html
Stored Procedures e Funções
Stored procedures e funções são recursos novos no MySQL versão 5.0. Uma stored procedure é um
conjunto de comandos SQL que podem ser armazenados no servidor. Uma vez que isto tenha sido feito,
os clientes não precisam reenviar os comandos, mas podem fazer referência às stored procedures.
Stored procedures podem fornecer um aumento no desempenho já que um conjunto menor de
informação precisa ser enviada entre o servidor e o cliente. O lado negativo é que isto aumenta a carga
no sistema do servidor de banco de dados, já que a maior parte do trabalho é feita no servidor e menor
parte é feita do lado do cliente (aplicação) e geralmente existem muitas máquinas e poucos servidores
de banco de dados.
Stored procedures também permitem que você tenha bibliotecas de funções no servidor de banco de
dados. No entanto, linguagens de aplicações modernas já permitem que isto seja feito internamente
com classes, por exemplo, e usar estes recursos das linguagens de aplicações clientes é benéfico para o
programador mesmo fora do escopo do banco de dados usado.
Situações onde stored procedures fazem sentido:
•
•
Quando várias aplicações clientes são escritas em diferentes linguagens ou funcionam em
diferentes plataformas, mas precisam realizar as mesmas operações de banco de dados.
Quando a segurança é prioritária. Bancos, por exemplo, usam stored procedures para todas as
operações comuns. Isto fornece um ambiente consistente e seguro, e procedures podem
assegurar que cada operação seja registrada de forma apropriada. Neste tipo de condiguração,
aplicações e usuários não conseguiriam nenhuma acesso as tabelas do banco de dados
diretamente, mas apenas podem executar stored procedures específicas.
Stored procedures e funções são rotinas criadas com as instruções CREATE PROCEDURE e CREATE
FUNCTION. Um procedimento é chamado usando uma instrução CALL e só pode passar valores de
retorno usando variáveis de saída. Funções podem retornar um valor escalar e pode ser chamadas de
dentro de uma instrução como qualquer outra função (isto é, chamando o nome da funcao). Rotinas
armazenadas podem chamar outras rotinas armazenadas. Uma rotina pode ser tanto um procedimento
como uma função.
CRIANDO STORED PRODECURES
Para criarmos STORED PROCEDURES no MySQL utilizamos a seguinte sinxtaxe básica:
DELIMITER $$
CREATE PROCEDURE nomeprocedure ()
BEGIN
/* Aqui vai o código da procedure */
END
Criaremos agora uma stored procedure que irá atualizar o salário de um determinado fincionário,
aumentando-0 em 300 reais.
DELIMITER $$
CREATE PROCEDURE AumentarSalario(vcpf varchar(20))
BEGIN
UPDATE funcionario SET salario = salario + 300 where funcionario.cpf=vcpf;
END
Para realizarmos a chamada a procedure utilizar o comando CALL.
CALL AumentarSalario ('02396423487');
Exercício 1
Vamos agora criar uma stored procedure para diminuir o salário. Para isso criar uma procedure
chamada DiminuirSalario que receba o Cpf do funcionário e reduza seu salário em 500 reais.
Comandos de maipulação de dados são comuns de serem encapsulados em Stored Procedores(SP). Por
exemplo a inclusão de registros na base de dados pode ser feita diretamente em SP trazendo vantagens
de performace e segurança.
O seguinte exemplo realiza uma inclusão na tabela localizacao_dep
DELIMITER $$
CREATE PROCEDURE `INSERIRLOCALIZACAO`(vDnumero INT, vDlocal VARCHAR(100))
BEGIN
INSERT INTO `empresa`.`localizacao_dep` (`Dnumero`, `Dlocal`) VALUES (vDnumero, vDlocal);
END
Realizando a chamada:
CALL INSERIRLOCALIZACAO(32,'João Pessoa');
Exercício 2
Criar stored procedures para inclusão das seguintes tabelas:
•
•
•
Projeto
Dependente
Trabalha_Em
Inserir registros através da chamada a essas tabelas.
Exercício 3
Criar stored procedure para excluir registro da tabela Funcionario.
Exercício 4
Criar uma stored procedure para excluir um registro da tabela Projeto. Esta exclusão deve obedecer a
seguinte regra:
•
Antes da exclusão do projeto deve-se excluir todas as referências de trabalhos realizados no
mesmo.
Utilizando variáveis em Stored Procedures
Sintaxe básica:
DECLARE nome TIPO;
Exemplo:
DECLARE nome VARCHAR(30);
Podemos definir um valor padrão para nossa variável na inicialização:
DECLARE valor INT DEFAULT 0;
Declarar várias variáveis em uma mesma linha:
DECLARE vidgrupo , vnomeCientifico varchar(255);
Para atribuir um valor para minha variável, utiliza-se o comando SET.
Exemplo:
SET valor = 300;
SET nome = ‘João’;
Operador Condicional
Sintaxe básica:
if condição then
/*comandos*/
end if;
exemplo:
if salario > 5000 then
set cargo =’Gerente de Projeto’;
end if;
Exemplo com else:
if strtipo='s' then
set vidunidadedispersao = vidsementeud;
else
set vidunidadedispersao = vidfrutoud;
end if;
Exercício 5
Criar uma store procedure que receba o cpf do funcionário e uma variável de opcão. Se opção for 1,
deve aumentar em 300 reais o salário do funcionário, caso contrário reduzir o mesmo em 500 reais.
Exercício 6
Cria uma store procedure que deve atualizar o salário de todos os funcionários que são gerentes.
Sabendo que o salário dos gerentes é composto de:
•
•
•
Vencimento Base: 3500,00;
Auxílio Viagem: 1200,00;
Bônus: 1000,00.
Armazenar cada valor em uma variável dentro da procedure realizando a alteração para todos os
funcionários que são gerentes sempre que a procedure for executada.
CRIANDO FUNCTIONS(Funções)
Sintaxe Básica:
CREATE FUNCTION nomefunction () RETURNS tipoRETORNO
BEGIN
RETURN valor_retorno;
END
Chamando a function:
SELECT nomefunction();
Exemplo:
DELIMITER $$
CREATE FUNCTION exemploFunc () RETURNS varchar(50) DETERMINISTIC
BEGIN
RETURN 'HELLO!!!!’;
END
Chamando a function:
SELECT exemploFunc();
Agora vamos o exemplo anterior para receber um nome e retornar a frase como o exemplo:
SELECT exemploFunc(‘Marcelo’);
HELLO!!!! Marcelo
Como apresentado no comando acima, as functions são chamadas em comando SQL, podendo estar
inseridas dentro de comandos SQL mais complexos, vejamos o exemplo relacionado a tabela empresa:
SELECT empresa.exemplo(Pnome) as OlaFuncionario from funcionario;
Executando o comando acima temos uma pequena amostra das possibilidades de soluções que temos
utilizando functions em nosso banco de dados.
Exercícios:
1) Criar uma consulta que retorne o nome do funcionário e o número médio de horas que o
mesmo trabalhou por projeto. O cálculo da média deve ser realizado em uma function chamada
mediaProjeto. Se o funcionário não trabalhou em nenhum projeto deve ser retornado zero.
2) Considerando que a empresa gasta 200 reais por dependente, criar uma function que retorne o
total de gasto da empresa para um determinado funcionário. Utilizar a function em uma
consulta que retorne o nome de cada funcionário e o total gasto por funcionário.
3) Adaptar o exercício 1 para desconsiderar o projeto 2.
4) Criar uma function que retorne os anos passados entre a data atual e uma determinada data
recebida. Criar uma consulta que retorne o nome do funcionário e a idade do mesmo.
Referência: http://dev.mysql.com/doc/refman/4.1/pt/date-and-time-functions.html