Nessa minha primeira postagem sobre tecnologia da informação vou apresentar um trabalho sobre as Stored Procedures, eis o modelo do trabalho que foi apresentado:
Centro
Universitário Módulo
Martim de Sá
Analise e
Desenvolvimento de Sistemas
Banco de Dados Stored Procedures
|
Nilson Donizete
Ribeiro da Silva
Caraguatatuba
2014
Nilson Donizete
Ribeiro da Silva
Banco de Dados Stored Procedures
|
Trabalho apresentado
como exigência para obtenção de conceito em Banco de Dados no curso de Analise
e Desenvolvimento de Sistemas do Centro Universitário Módulo.
|
Professor especialista: Henrique Nunweiler Angelim Silva.
|
Caraguatatuba
2014
RESUMO
Stored Procedure é procedimento que visa facilitar o trabalho junto ao
SGBD, tornando mais rápidas as consultas e alterações.
Quando utilizamos SP em nosso projeto, os procedimento de dados ficará
centralizado no BD e os de aplicação na aplicação.
Poderemos fazer também o tratamento de dados na camada de BD, tornando a
aplicação mais limpa, só chamando o SP, assim o procedimento de analise,
work-flow é feito no próprio BD.
Palavras-chave:
SP, usp.
ABSTRACT
Stored
Procedure is procedure to facilitate the work with the DBMS, by speeding up
queries and updates.
When we use SP in our
project, the data will be centralized procedure in the database and the
application in the application.
We may also make the
processing of data in the BD layer, making the cleanest implementation, only
calling the SP, so the procedure of analysis, work-flow is done in the database
itself.
Keywords: SP,USP.
SUMÁRIO
Ao desenvolvermos aplicações para acessarem banco de dados é necessário
executarmos rotinas simples ou complexas de manipulação desses dados. As vezes
é necessário utilizarmos várias instruções na linguagem em sequência para obter
o resultado esperado.
Conforme a rotina a ser executada, pode requerer várias consultas e
atualizações na base, e isto demanda um maior consumo de recursos pela
aplicação, em se tratando de aplicações web uma maior quantidade de informações
que precisam trafegar pela rede e de requisições ao servidor aumenta em muito
essa demanda.
Demanda essa que pode ser atenuada transferindo parte do processamento
direto para o banco de dados, isto em se considerando que as máquinas
servidoras geralmente têm configurações de hardware mais robustas, porém o
“Stored Procedures” (ou Procedimentos Armazenados, em português) faz-se
necessário e torna -se extremamente útil.
2 STORED PROCEDURES
São rotinas definidas no banco de dados, identificadas por um nome pelo
qual podem ser chamadas. Esse tipo de procedimento pode executar uma série de
instruções, receber parâmetros e retornar valores. Quando desenvolvemos
aplicações para acessarem banco de dados é comum executarmos rotinas simples ou
complexas de manipulação desses dados a partir da linguagem/ferramenta
utilizada. Para isso, utilizamos várias instruções em sequência para obter o
resultado esperado.
Dependendo da rotina a ser executada, pode haver várias consultas e
atualizações na base, o que acarretara um maior consumo de recursos pela
aplicação. E no caso de aplicações web, é maior a quantidade de informações que
precisam trafegar pela rede e de requisições ao servidor.
Uma forma de atenuar esse consumo de recursos diretamente pela aplicação,
é uma boa prática transferir parte do processamento direto para o banco de
dados já que se Considera que as máquinas servidoras geralmente têm
configurações de hardware mais robustas.
É para isso que usamos as “Stored Procedures” (ou Procedimentos
Armazenados, em português).
Quanto as suas principais características podemos destacar:
2.1Desempenho.
É mais rápido que command sql, pois o procedimento de caminho fica
registrado no BD (Banco de dados) e assim quando se executa a stored procedure
o BD não precisa mapear o caminho.
2.2 Manutenção.
Quando tenho uma SP (stored procedure) que retorne só o campo nome de uma
lista de produtos e quero inserir o codigo do produto junto com o nome, atraves
de uma procedure eu concateno o codigo do produto no nome dele, e prontamente,
onde se exibia o nome do produto agora será exibido o codigo + nome.
2.3 Organização.
Quando utilizamos SP em nosso projeto, os procedimento de dados ficará
centralizado no BD e os de aplicação na aplicação.
2.4 Tratamento de dados.
Poderemos fazer também o tratamento de dados na camada de BD, tornando a
aplicação mais limpa, só chamando o SP, assim o procedimento de analise,
work-flow é feito no próprio BD.
2.5 Camada BD.
Quando for necessário fazer manutenções não será necessário mexer na
aplicação, bastando para tanto alterar a SP e ja estará tudo resolvido.
Dentre as varias razões para se usar um SP, a mais presente é que se
trata de uma prática ja utilizada em
grandes softwares e pode se utilizar um SP para fazer um procedimento de
Select, Insert, Delete e/ou Update, podendo inclusive ter um select e um insert
na mesma SP.
/* Para se criar uma procedure é importante que se mantenha uma semântica,
Ex. uspInserirTelefone para uma procedure que insere telefone,
onde estiver abreviado usp,
leia-se "user stored procedure" */
. /*Nílson*/
CREATE PROCEDURE
uspInserirTelefoneTipo
@IDTelefoneTipo INT,
@DesTelefoneTipo VARCHAR(30)
AS
BEGIN
INSERT INTO
tblTelefoneTipo
(
IDTelefoneTipo,
DescTelefoneTipo
)
VALUES
(
@IDTelefoneTipo,
@DescTelefoneTipo
)
END;
/*outro modelo usando o delimeter e excluíndo procedure já existente*/
--DELIMETER $$
DROP PROCEDURE IF EXISTS
CadastrarFuncionario $$ /*para excluir a procedure já existente*/
CREATE PROCEDURE
CadastrarFuncionario(uspnome varchar(34), uspsobrenome varchar(68),
uspqtdfilhos Tinyint, uspsalario float)
main: BEGIN
if(uspnome'')then
Select 'O campo
nome não pode estar vazio' as msg, 'erro de Cadastro' as msg;
leave main;
end if;
if Length(uspnome'<4 span="" then="">
Select 'O campo nome não pode ter menos de 4
letras' as msg, 'erro de Cadastro' as msg;
leave main;
end if;
if(uspsobrenome'')then
Select 'O campo sobrenome não pode estar
vazio'as msg, 'erro de Cadastro' as msg;
leave main;
end if;
insert into
Funcionario (nome, sobrenome, qtdfilhos, salario)values(uspnome, uspsobrenome,
uspqtdfilhos, uspsalario);
Select
'Funcionario cadastrado com sucesso' as msg;
END $$
--DELIMETER;
Aqui um SP bem básico, mas com
parâmetros para inserir Funcionário
CREATE PROCEDURE
uspInserirFuncionario
@nome varchar(130),
@Endereço varchar(120),
@cpf decimal(11,0).
AS
BEGIN
SELECT * FROM
Funcionario
END
/*Observe que “uspInserirFuncionario” foi o nome que eu criei para a
procedure, pode ser qualquer nome, o importante é usar um nome semântico. Por
exemplo, uma procedure que faz o login, use o nome “sp_login”, ou
“sp_faz_login”, note que é mais interessante sem underline já que SQL usa o
_underline em suas bibliotecas .*/
Aqui neste exemplo apresento um Select com parametros:
CREATE PROCEDURE
uspVeiculos
@id int
AS
BEGIN
SELECT * FROM Veículos WHERE id=@id
END
Já este exemplo é um Select
com multiplos parametros:
CREATE PROCEDURE uspVeiculos
@cor varchar(100),
@placa varchar(100)
AS
BEGIN
SELECT * FROM Veiculos WHERE cor=@cor and placa=@placa
END
Este exemplo é um Insert com
multiplos parametros:
CREATE PROCEDURE
uspVeiculos
@cor varchar(85),
@placa varchar(65),
@marca varchar(185)
AS
BEGIN
INSERT INTO Veiculos(marca, placa, cor)
VALUES(@marca,@placa,@cor)
END
Pode se fazer também um update com
multiplos parametros:
CREATE PROCEDURE
uspVeiculos
@cor varchar(85),
@placa varchar(65),
@marca varchar(185)
AS
BEGIN
UPDATE carro SET
cor=@cor, marca=@marca WHERE placa=@placa
END
Neste exemplo usei um Select e
Insert:
CREATE PROCEDURE
uspVeiculos
@placa varchar(85)
AS
BEGIN
SELECT * FROM carro
WHERE placa=@placa
INSERT INTO log_placa_pesquisada(placa)
VALUES(@placa)
END
Por vezes quando criamos uma Stored Procedure, notamos que se faz necessário
ajustá-las por algum motivo, um parâmetro ou uma simples virgula, note que não
é necessário excluir, deletar ou aplicar drop procedure, basta clicar duas
vezes sobre o CREATE e Digitar ALTER, para alterar a procedure e assim fazer
quantas alterações forem necessárias
como no exemplo a seguir:
Primeiro Criando a Procedure,
CREATE PROCEDURE uspInserirTelefoneTipo
@IDTelefoneTipo INT,
@DesTelefoneTipo VARCHAR(30)
AS
BEGIN
INSERT INTO tblTelefoneTipo
(
IDTelefoneTipo,
DescTelefoneTipo
)
VALUES
(
@IDTelefoneTipo,
@DescTelefoneTipo
)
END;
Depois Alterando a Procedure,
ALTER PROCEDURE uspInserirTelefoneTipo
@IDTelefoneTipo INT,
@DesTelefoneTipo VARCHAR(30)
AS
BEGIN
INSERT INTO tblTelefoneTipo
(
IDTelefoneTipo,
DescTelefoneTipo
)
VALUES
(
@IDTelefoneTipo,
@DescTelefoneTipo
)
END;
Apresentei aqui o que vi sobre o que é e como funciona uma Stored
Procedures, entendi que em geral essas premissas vale (para qualquer banco) e
principalmente como trabalhar com essa estrutura no banco de dados MYSQL usando
diversos parâmetros e declarando variáveis, porém é bom salientar que o código
de um procedimento pode conter inúmeras linhas e serem muito mais complexos, eu
apresentei exemplos simples e básicos.
