Stored Procedure

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

*
*
*
Banco de Dados
Procedimentos Armazenados (Stored Procedures)
Luciana Vieira
*
*
*
Conceito de procedimentos armazenados (stored Procedures)
Uma stored procedure é um conjunto de comandos SQL (inclusive com extensões) que são compilados e armazenados no servidor.
Stored procedures podem receber parâmetros de entrada e retornar valores.
*
*
*
Vantagens de usar 
Stored Procedures (SP)
Maior performance já que os programas podem conter diversos comandos SQL que já ficam compilados no servidor.
Utilização de um código comum pois várias aplicações clientes podem compartilhar a procedure sem ter que reproduzir seu código.
Facilidade de manutenção do código pois as procedures precisam ser alteradas e recompiladas apenas no servidor.
*
*
*
Criando um procedimento armazenado (stored procedure)
Para criar um procedimento armazenado utilize o comando CREATE PROCEDURE.
Sintaxe:
CREATE PROCEDURE nome [(parâmetro <tipo> [,...])]
[RETURNS var <tipo> [,...])]
AS
[DECLARE VARIABLE var <tipo> ; …]
BEGIN
<corpo do procedimento>
END;
*
*
*
Exemplo 1: Uma variável de entrada
Crie uma procedure que receba como parâmetro um código do atleta e zere seus pontos e valor de penalidade.
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Expirar`(in v_idatleta integer)
BEGIN
update penalidade
set valor = null, pontos = null where atleta_idatleta = v_idatleta;
END
*
*
*
Exemplo 1: Uma variável de entrada
Crie uma procedure que receba como parâmetro um código do atleta e zere seus pontos e valor de penalidade.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_expirar`(in v_idatleta integer)
BEGIN
update penalidade
set valor = null where atleta_idatleta = v_idatleta;
update penalidade
set pontos = null where atleta_idatleta = v_idatleta;
END $$
DELIMITER ;
*
*
*
Exemplo 1: MYSQL Administrator
*
*
*
Exemplo 1: MYSQL Administrator
*
*
*
Exemplo 1: MYSQL Administrator
*
*
*
Exemplo 1: Executando no Query Browser
*
*
*
Exemplo 1: Resultado
*
*
*
Exemplo 2: Uma variável de Saída
Crie uma procedure que repasse quantos penalidades de atletas estão com seus pontos a expirar, sendo que são necessários 1000 dias para expirar.
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_tempo1`(in v_tempo integer, out v_id integer)
BEGIN
 select count(idpenalidade) into v_id from penalidade
 where datediff(curdate(),data) > v_tempo;
END 
*
*
*
Exemplo 2: MYSQL Administrator
*
*
*
Exemplo 2: MYSQL Administrator
*
*
*
Exemplo 2: MYSQL Administrator
*
*
*
Exemplo 2: Executando no Query Browser
*
*
*
Exemplo 3: Uma variável de Entrada e Uma de Saída
Crie uma stored procedure que entre com o idpenalidade e retorna o idatleta
*
*
*
Exemplo 3: Uma variável de Entrada e Uma de Saída
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_inOut`(in vin_idPenalidade integer, out vout_idatleta integer)
BEGIN
 select atleta_idatleta into vout_idatleta from penalidade where idpenalidade = vin_idPenalidade;
END
*
*
*
Exemplo 4:
Crie um stored procedure que o usuário informa um cidade de um atleta e pede para substituir no banco de dados
*
*
*
Criando um stored procedure
Exemplo:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_teste1`(out v text)
BEGIN
 select 'Hello World' into v;
END
*
*
*
Modificando a tabela Empregado
Crie uma coluna nova na tabela atleta com o nome login; 
*
*
*
Criando um stored procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_login`(in v_idAtleta integer, in v_nome text, in v_login text, out Msg text)
BEGIN
 IF ((select login from atleta where login = v_login) != "") THEN
 SELECT 'O Login já existe!' into Msg;
 ELSE
 INSERT INTO atleta(idAtleta, nomeatleta, login) values (v_idatleta, v_nome, v_login);
 END IF;
END
*
*
*
*
*
Excluindo um procedimento armazenado
Para excluir um procedimento armazenado utilize o comando DROP PROCEDURE.
Sintaxe:
DROP PROCEDURE <nome da procedure>
Exemplo:
DROP PROCEDURE sp_nomeAtleta;
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*