Curso de Ciência da Computação

Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
Curso de Ciência da Computação
MATÉRIA: Banco de Dados II
ASSUNTO: Stored Procedures (Procedimentos Armazenados)
PROFESSOR: Ricardo Rodrigues Barcelar
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
Objetivos:
 Apresentar a linguagem Structured Query Language (SQL);
 Utilizar stored procedures em sistemas de banco de dados.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
Agenda:
1. Introdução
2.
3.
4.
5.
Vantagens do uso de Stored Procedures
Criação da Stored Procedure
Comandos extras
Manipulando Stored Procedure
7. Fonte de pesquisa
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
Nas sessões anteriores foi visto o comando SELECT mais recentemente a
utilização de Visões e seus detalhes.
Nesta sessão veremos a utilização de Stored Procedures (Procedimentos
Armazenados), na automatização de procedimentos no banco de dados.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
cd
gravadora
CP grav_id
grav_nome
grav_tel
grav_contato
autor
CP aut_id
aut_nome
CP
cd_id
cd_nome
cd_preco_venda
cd_dt_lancamento
CE1 grav_id
mus_autor
CP,CE1 mus_id
CP,CE2 aut_id
item_cd
CP,CE2 cd_id
CP,CE1 mus_id
item_faixa
musica
CP mus_id
mus_nome
mus_duracao
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
STORED PROCEDURES (SP)
 É um programa escrito numa linguagem própria que é armazenado como parte
do banco de dados.
VANTAGENS DO USO DE STORED PROCEDURES?
 Redução de tráfico de rede.
 Aumenta a performance da aplicação, particularmente em uma WAN ou em uma
conexão de baixa velocidade.
 Utilizando SP, elimina-se o processo de “parsed”, ou seja da query ter que ser
analisada gramaticalmente, submetido ao optimizador para formulação de um plano
de execução.
 SP são analisadas, optimizadas e armazenadas em uma forma executável no
momento em que são adicionadas ao banco de dados.
 É possível executar operações muito mais complexas que uma simples query.
 Pode ser usada por aplicações distintas.
 Facilita a mnt, pois é possível alterar a SP sem ter que recompilar a App.
 Proporciona mais segurança ao BD, acessando tabelas que o usuário não pode.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
QUANDO USAR STORED PROCEDURES
 Sempre que puder!
 Não existem desvantagens em usar SP, entretanto existes duas limitações:
 vc tem que ser capaz de passar qualquer informação variável para a SP
(parâmetros).
 Operações complexas podem ser limitadas.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
CRIAÇÃO DA STORED PROCEDURE
 Para criar uma SP, utilizamos o comando CREATE PROCEDURE:
SINTAXE:
CREATE PROCEDURE nome
<parâmetros de entrada>
RETURNS
<parâmetros de saída>
AS
<declaração das variáveis locais>
BEGIN
<comandos da procedure>
END
 Parâmetro de entrada são usados para passas os valores que serão usado para
modificar o comportamento da SP.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
 Parâmetro de saída ou de retorno é o meio pelo qual a SP retorna informações
para a aplicação cliente.
OBS: Um parâmetro pode ser de qualquer tipo de dados exceto BLOB ou ARRAY.
EXEMPLO:
CREATE PROCEDURE pPrecoVenda(
p_grav integer
)
RETURNS(
grav integer,
precovenda decimal(10,2)
)
AS
BEGIN
SELECT a.grav_id, AVG(a.cd_preco_venda) FROM cd a, gravadora g
WHERE a.grav_id = g.grav_id AND
a.grav_id = :p_grav
GROUP BY a.grav_id
INTO :grav, :precovenda;
SUSPEND;
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
 Tanto parâmetro de entrada como parâmetros de saída devem estar entre
parênteses.
 O comando SUSPEND pausa a SP até que o cliente busque os valores dos
parâmetros de saída.
DECLARANDO VARIÁVEIS
 Estas variáveis existem somente enquanto a SP está sendo executada e seu
escopo é local.
 São declaradas depois da palavra chave AS e antes do BEGIN.
SINTAXE:
DECLARE VARIABLE nome TIPO;
EXEMPLO:
DECLARE VARIABLE valor decimal(10,2);
 Ao usar a variável esta deve ser precedida de dois pontos „:‟;
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
EXEMPLO:
CREATE PROCEDURE mus_duracao (
p_mus INTEGER)
AS
DECLARE VARIABLE duracao_temp TIME;
DECLARE VARIABLE musica_temp VARCHAR(50);
BEGIN
SELECT m.mus_nome, m.mus_duracao FROM musica m
WHERE m.mus_id = :p_mus
INTO :musica_temp, :duracao_temp;
IF (:duracao_temp > '00:02:00') THEN
BEGIN
EXCEPTION musica_longa;
SUSPEND;
END
ELSE
BEGIN
EXCEPTION musica_curta;
END
SUSPEND;
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
ELEMENTOS DA LINGUAGEM
 Inclui todas as construções de uma linguagem de programação estruturada,
como:
 Cometários: /* Este é um comentário */
 Bloco de comandos (BEGIN-END): É possível usar construções como IF-THENELSE e loops WHILE que somente podem conter um comando.
 Comandos de atribuição: observe o exemplo
var1 = var2 * var3
var1 pode ser um parâmetro.
 Operador de concatenação: || (duas barras verticais)
 Além desses elementos é possível usar as expressões condicionais:
 BETWEEN
 LIKE
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional








IN
EXISTS
ANY
ALL
IS NULL
IS NOT NULL
CONTAINING
STARTING WITH
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
USANDO COMANDOS SQL NAS STORED PROCEDURES
 É possível usar comandos como SELECT, INSERT, UPDATE e DELETE
exatamente como em uma query apenas com algumas alterações na sintaxe.
 Para todos esses comando é possível usar variáveis ou parâmetros.
 Não é possível usar comandos da DDL em uma SP: CREATE, DROP, GRANT,
REVOKE, COMMIT e ROLLBACK
EXEMPLO:
CREATE PROCEDURE i_autor (
id INTEGER,
nome VARCHAR(50))
AS
BEGIN
INSERT INTO autor (aut_id, aut_nome) VALUES (:id, :nome);
SUSPEND;
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
USANDO FOR SELECT e DO
 Os exemplos anteriores que selecionam um ou mais valores para uma variável é
bom desde que o SELECT retorne apenas uma linha.
 Para processar várias linhas deverá ser usado o comando FOR SELECT e DO.
EXEMPLO:
CREATE PROCEDURE p_buscacd (
p_grav INTEGER)
RETURNS(
grav INTEGER,
cd VARCHAR(50))
AS
BEGIN
FOR SELECT grav_id, cd_nome FROM CD
WHERE grav_id = :p_grav
INTO :grav, :cd
DO
SUSPEND;
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
USO DO SUSPEND
 O comando SUSPEND diz para a SP suspender a execução até que uma
solicitação de dados (fetch).
 SUSPEND não é somente usado com FOR SELECT DO. É usado sempre que a
SP retorna um valor para o cliente.
EXEMPLO:
CREATE PROCEDURE p_qtdcd
RETURNS(
qtd INTEGER
)
AS
BEGIN
SELECT COUNT(*) FROM cd
INTO :qtd;
SUSPEND;
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
DILEMA DO PONTO-E-VÍRGULA ‘;’
 O console identifica o fim de cada comando em um script SQL pelo caractere de
término de comando que é por default o ponto-e-vírgula „;‟
 Isto funciona bem na maioria dos casos, mas não na criação de SP e triggers,
pois cada comando interno da SP termina com ponto-e-vírgula.
 Dessa forma ao encontrar o primeiro ponto-e-vírgula o console entende que é o
fim da criação da SP e anuncia o erro.
 A solução é trocar o caractere de término de comando por um outro diferente do
ponto-e-vírgula.
 Para isso usamos o comando SET TERM, conforme script a seguir.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
EXEMPLO:
SET TERM ^ ;
CREATE PROCEDURE p_qtdautor
RETURNS(
qtd INTEGER
)
AS
BEGIN
SELECT COUNT(*) FROM autor
INTO :qtd;
SUSPEND;
END
SET TERM ; ^
 Observe que foi substituído o caractere ponto-e-vírgula que indica o termino de
comando pelo ^ (circunflexo) logo na primeira linha do script e ao fim foi retornado
ao caractere padrão.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
APAGANDO STORED PROCEDURE
 Para remover uma SP use o comando DROP PROCEDURE;
SINTAXE:
DROP PROCEDURE nome;
EXEMPLO:
DROP PROCEDURE p_qtdcd;
 Somente o SYSDBA ou o proprietário da SP podem apagá-la
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
ALTERANDO STORED PROCEDURE
 Para alterar uma SP use o comando ALTER PROCEDURE;
 A primeira impressão é que não iremos precisar desse comando. Entretanto, não
é possível apagar uma SP que chamada por outra SP.
SINTAXE:
ALTER PROCEDURE nome
<parâmetros de entrada>
RETURNS
<parâmetros de saída>
AS
<declaração das variáveis locais>
BEGIN
<comandos da procedure>
END
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
EXEMPLO:
ALTER PROCEDURE p_qtdautor
RETURNS(
quantidade INTEGER
)
AS
BEGIN
SELECT COUNT(*) FROM autor
INTO :quantidade;
SUSPEND;
END;
 Fazendo isso o elimina-se a dependência entre SP
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
CHAMANDO STORED PROCEDURE
 SP no Firebird são divididas em dois grupos de acordo com que são chamadas.
 SP que retornam valores através de parâmetros de saída são chamadas de
select procedures, pois elas podem ser usadas no lugar do nome de uma tabela
em um comando SELECT
EXEMPLO:
SELECT * FROM p_qtdautor
 É possível também chamar SP que possuem parâmetros de entrada, passando o
parâmetro após o nome da SP no comando SELECT.
EXEMPLO:
SELECT * FROM p_buscacd(:P_GRAV)
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
CHAMANDO NON-SELECT PROCEDURE
 Non-select procedure são aquelas SP que não retornam qualquer resultado. Ela
possui apenas parâmetros e entrada e realiza inserção e atualização de dados.
 O comando usado usado nestes casos é EXECUTE PROCEDURE
EXEMPLO:
SELECT * FROM p_qtdautor
 É possível também chamar SP que possuem parâmetros de entrada, passando o
parâmetro após o nome da SP no comando SELECT.
EXEMPLO:
EXECUTE PROCEDURE i_autor(:ID, :NOME);
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
 Realizar exercício referente a matéria.
Patterns – Especificação de Casos de Uso
Modelo de Dados Relacional
ELMASRI, Ramez et all. Sistemas de Banco de Dados – Fundamentos e
Aplicações. Rio de Janeiro: LTC, 2000.
TODD, Bill. Introdução a Stored Procedures e Triggers no Firebird. Tradução
Alessandro Cunha Fernandes, 2000.