Stored Procedures em Sistemas de Informação

Prévia do material em texto

Profa. Adriana Zanella Martinhago
Curso: Sistemas de Informação
UFV-CRP
2
Na aula anterior
Outros objetos. 
Na aula de hoje: Stored 
Procedures
 Procedimentos Armazenados;
 É uma biblioteca de comandos SQL para utilização junto ao
SGBD;
 Encapsula tarefas repetitivas, aceita parâmetros de entrada e
retorna um valor de status (para indicar aceitação ou falha de
execução);
3
 O uso de Stored Procedures pode:
 Reduzir o tráfego na rede;
 Melhorar o desempenho de um banco de dados;
 Criar tarefas agendadas;
 Diminuir riscos;
 Criar rotinas de processamento;
 Etc.
4
 Por que utilizar?
 Método para encapsular tarefas repetitivas;
 Indicado quando um procedimento é realizado com 
frequência;
 Toda vez que uma consulta é executada, o otimizador de 
consultas gera um plano otimizado contendo o método mais 
rápido para acessar os dados; e em seguida, é gerado um 
plano de consulta para a execução da consulta;
 Isso não ocorre com uma stored procedure;
5
4/25/2017 6
Sintaxe:
CREATE PROCEDURE <nome_procedimento> 
(parametros1, ..., n) AS ComandosSQL
OBS: “ComandosSQL” pode ser consulta/chamada a outro 
procedimento, linguagem procedural do SGBD, etc.
7
Sintaxe:
ALTER PROCEDURE <nomeProcedimento> AS 
<comandoSQL>
OBS: Não pode alterar apenas parte do comando 
SQL, assim, tem que reescrever todo o comando 
SQL
8
 Sintaxe:
DROP PROCEDURE <nomeProcedimento>
 Sintaxe para executar:
EXEC <nomeProcedimento>
9
10
 Criar um procedimento para retornar os dados do pedido com a 
maior quantidade de peças no ano de 2003.
11
CREATE PROCEDURE MaiorPedido2003 ( ) AS
SELECT f.NomeFornecedor, p.NomePeca, pe.Qtdade
FROM Fornecedor as f, Peca as p, Pedido as pe
WHERE f.codigo= pe.codigo_fornecedor AND
p.codigo = pe.codigo_peca AND
Qtdade = (SELECT MAX(Qtdade)
FROM Pedido
WHERE ano = 2003);
 Criar um procedimento para retornar os dados do pedido com 
a maior quantidade de peças em um ano qualquer.
12
CREATE PROCEDURE MaiorPedidoAno(@ano integer)
AS
SELECT f.NomeFornecedor, p.NomePeca, 
pe.Qtdade
FROM Fornecedor as f, Peca as p, Pedido as pe
WHERE f.codigo= pe.Codigo_fornecedor AND
p.codigo = pe.Codigo_peca AND
Qtdade = (SELECT MAX(Qtdade)
FROM Pedido
WHERE ano = @ano)
 Criar um procedimento para retornar a data de venda da maior 
quantidade de peças pedida em um ano qualquer.
13
CREATE PROCEDURE MaiorPedidoAno
(@ano integer, @dataPedido date OUTPUT)
AS
SELECT @dataPedido = data
FROM pedido
WHERE Qtdade = (SELECT MAX(Qtdade)
FROM Pedido
WHERE ano = @ano);
SELECT 'A data do Maior Pedido foi:', @dataPedido
17:22:39 14
17:22:39 15
 Stored Procedures estão disponíveis a partir da versão 5.0;
 Procedimentos armazenados 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;
16
 O MySQL suporta uma extensão muito útil que permite o uso 
da instrução regular SELECT (isto é, sem usar cursores ou 
variáveis locais) dentro de uma stored procedure;
 O resultado de tal consulta é simplesmente enviado 
diretamente para o cliente.
17
CREATE PROCEDURE nome_procedimento
([parametros, ...]) [caracteristicas] 
[BEGIN] corpo_da_rotina; [END];
Onde:
parametros:
nome_parametro tipo
caracteristicas:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT string
1817:22:39
DELIMITER // 
CREATE PROCEDURE sp_correntistaInsert(v_nome
VARCHAR(60), v_cpf VARCHAR(20))
BEGIN 
IF ((v_nome != '') && (v_cpf != ''))THEN 
INSERT INTO tbl_correntista (correntista_nome, 
correntista_cpf) VALUES (v_nome, v_cpf); 
ELSE 
SELECT 'NOME e CPF devem ser fornecidos para o 
cadastro!' AS Msg; 
END IF; 
END; //
DELIMITER ;
1917:22:39
CREATE FUNCTION nome_funcao
([parametros[,...]]) [RETURNS tipo] 
[caracteristicas...] 
corpo_da funcao
2017:22:39
DELIMITER // 
CREATE FUNCTION media (nome VARCHAR(10)) 
RETURNS FLOAT
BEGIN
DECLARE n1,n2,n3,n4 INT; 
DECLARE med FLOAT;
SELECT nota1,nota2,nota3,nota4 INTO
n1,n2,n3,n4 FROM notas WHERE aluno = nome;
SET med = (n1+n2+n3+n4)/4;
RETURN med;
END // 
DELIMITER ;
2117:22:39
ALTER PROCEDURE | FUNCTION nome_sp
[caracteristica ...]caracteristica:
NAME novo_nome
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT string
Este comando pode ser usado para renomear
uma stored procedure ou function, e para alterar
suas características
2217:22:39
DROP PROCEDURE | FUNCTION [IF EXISTS] 
nome_procedimento;
A cláusula IF EXISTS é uma extensão do MySQL. 
Ela previne que um erro ocorra se o procedimento
ou função não existe.
2317:22:39
 PL/MySQL;
 Não é muito difundida e existe pouco material a respeito.
24
17:22:39 25
 O PostgreSQL trata os procedimentos armazenados como 
Funções (Function);
 O PostgreSQL conta com quatro tipos de funções:
 Funções Escritas em SQL;
 Funções Internas;
 Funções em Liguagem C;
 Funções de Linguagens Procedurais (PL/PGSQL);
25/04/2017 26
CREATE [ OR REPLACE ] FUNCTION nome ( tipo
[, ...] ] ) [ RETURNS tipo_retorno ] AS
$$
corpo da função
$$
LANGUAGE nome_linguagem;
2717:22:39
DROP FUNCTION nome_função;
2817:22:39
 Funções em SQL executam uma lista arbitrária de comandos 
SQL e o último comando da lista deve ser obrigatoriamente um 
SELECT;
 Os comandos SQL devem ser separados por ponto-e-vírgula;
 A função SQL pode retornar uma única linha ou um conjunto de 
linhas;
 O último SELECT da função funciona como o retorno da mesma;
2917:22:39
Crie uma função que some dois números inteiros.
CREATE OR REPLACE FUNCTION 
soma(INTEGER,INTEGER)RETURNS INTEGER AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
SELECT soma(2, 3);
30
Crie uma função que retorne os aniversariantes da data atual na 
tabela funcionários.
CREATE FUNCTION aniversariantes()
RETURNS SETOF funcionario AS $$
SELECT * FROM funcionario WHERE aniversario = 
current_date;
$$ LANGUAGE SQL;
SELECT pnome FROM aniversariantes();
31
 Crie uma função que receba o código do fornecedor e retorne 
seus dados.
32
 Funções internas são escritas em C e linkadas estaticamente
com o servidor PostgreSQL;
 O corpo da função interna somente especifica o nome da
função na linguagem C;
 Normalmente, todas as funções internas presentes no
PostgreSQL são declaradas durante a inicialização do cluster
(initdb), entretanto, um usuário pode usar o CREATE
FUNCTION para dar um apelido (alias) para uma função
interna;
33
CREATE FUNCTION raiz_quadrada (double) RETURNS
double
AS $$ dsqrt $$
LANGUAGE internal STRICT;
SELECT raiz_quadrada (27.9);
34
 Estas funções são compiladas enquanto as demais linguagens
procedurais são interpretadas;
 As funções em C devem ser compiladas como shared object e
são carregadas sob demanda no servidor;
 Para utilizar os tipos existentes no PostgreSQL é necessário dar
include em alguns headers do código fonte do PostgreSQL;
35
 Além do PL/pgSQL, é possível criar funções em outras 
linguagens procedurais como:
 PL/Tcl
 PL/Perl
 PL/Python
 PL/sh
36
 Linguagem procedural semelhante à PL/SQL do Oracle;
 Pode ser utilizada para agrupar comandos SQL e armazená-los 
no servidor de banco de dados, reduzindo o overhead de rede 
e comunicaçãoentre a aplicação cliente e o servidor;
 Os comandos do bloco são processados de uma só vez ao invés 
do comportamento padrão de processar um comando SQL por 
vez;
37
CREATE OR REPLACE FUNCTION nome_função (...) 
RETURNS tipo_dado AS $$
DECLARE --para declaração de variáveis
BEGIN
corpo da função/comandos
END
$$ LANGUAGE plpgsql;
38
 Todos os tipos de dados existentes no SQL do PostgreSQL
podem ser associados a variáveis PL/pgSQL;
 As variáveis utilizadas no código devem ser declaradas no 
bloco DECLARE;
 Caso uma variável não seja instanciada na declaração, o seu 
valor padrão é NULL;
 A sintaxe para declaração de variáveis é:
nome tipo [NOT NULL] [ {DEFAULT | := } valor];
39
 Pode ser feito de 3 maneiras:
curso varchar(30) := ‘PostgreSQL’ ; --no DECLARE
ou
--no bloco de comando
curso := ‘PostgreSQL’
ou
SELECT INTO nome nome_emp FROM emp WHERE n_emp = 
110;
40
CREATE FUNCTION GetNomeFunc(integer) RETURNS
text AS $$
DECLARE
nome text;
BEGIN
SELECT INTO nome pnome FROM funcionario
WHERE codigo = $1;
RETURN nome;
END;
$$ LANGUAGE plpgsql;
41
 Funções em PL/pgSQL aceitam a passagem de argumentos do 
usuário para o código;
 Os argumentos passados devem seguir a ordem (e os tipos) 
definidos no momento da criação;
 Cada argumento é associado a um identificador posicional que 
começa com o símbolo “$”. O primeiro argumento é sempre 1;
 É possível nomear os parâmetros de entrada na declaração da 
função;
 Uma função aceita até 32 argumentos de entrada;
42
CREATE FUNCTION irrf(valor_nota real) RETURNS
real AS $$
BEGIN
RETURN valor_nota * 0.015;
END;
$$ LANGUAGE plpgsql;
SELECT irrf (55.9);
43
 O PL/pgSQL oferece modificadores para facilitar o trabalho 
com objetos de banco de dados;
 O modificador %TYPE é usado para extrair o tipo de dados de 
uma coluna de tabela;
 O %ROWTYPE é uma variável que armazena a estrutura de um 
registro de uma tabela;
44
CREATE FUNCTION GetNomeCargo (int) RETURNS text
AS $$
DECLARE
v_func funcionario%ROWTYPE;
v_cargo funcionario.cargo%TYPE;
BEGIN
SELECT INTO v_func * FROM funcionario
WHERE codigo = $1;
RETURN v_func.pnome || ' : ' || v_func.cargo;
END;
$$ LANGUAGE plpgsql;
45
 Uma função deve retornar um valor compatível com o tipo 
declarado no cabeçalho. Sintaxe do valor de retorno:
RETURN {variável | valor | void}
 Quando uma função é declarada como SETOF, as linhas 
retornadas são acumuladas com o comando RETURN NEXT;
 Um último comando RETURN sem argumento indica que a execução 
da função deve ser encerrada;
46
CREATE FUNCTION ExemploNext ( ) RETURNS SETOF
integer AS $$
BEGIN
RETURN NEXT 1;
RETURN NEXT 2;
RETURN NEXT 3;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM ExemploNext( );
47
 A linguagem PL/pgSQL oferece vários recursos de controle de 
fluxo, tais como:
 IF/ THEN/ ELSE/ IF
 LOOP
 WHILE
 FOR
48
IF condicao THEN
comando;
[...]
ELSE
IF condicao THEN
comando;
[...]
END IF;
comando;
[...]
END IF;
49
LOOP
[...];
EXIT [WHEN condicao];
END LOOP;
50
CREATE FUNCTION fatorial(int) RETURNS bigint AS $$
DECLARE
valor ALIAS FOR $1;
varaux bigint := 1;
fatorial bigint := 1;
BEGIN
LOOP
fatorial := fatorial * varaux;
varaux := varaux + 1;
EXIT WHEN varaux > valor;
RETURN fatorial;
END;
$$ LANGUAGE plpgsql;
51
WHILE condicao LOOP
comando;
[...]
END LOOP;
52
CREATE FUNCTION numerador(int) RETURNS SETOF int AS $$
DECLARE
maximo ALIAS FOR $1;
i integer := 0;
BEGIN
WHILE (i <= maximo) LOOP
RETURN NEXT i;
i := i + 1;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
53
FOR identificador IN [REVERSE] expr1 .. expr2 
LOOP
comando;
[...]
END LOOP;
54
CREATE FUNCTION lista_sal( ) RETURNS SETOF text AS $$
DECLARE
row_func funcionario%ROWTYPE;
BEGIN
FOR i IN 1...1000 LOOP
SELECT INTO row_func * FROM funcionario WHERE codigo = i;
IF FOUND THEN
RETURN NEXT row_func.pnome || ' : ' || 
(row_func.salario::text);
END IF;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
55
56
 Próxima aula: 02/05/2014 (Aula prática PVA227)
 Contato: adriana.martinhago@ufv.br
 Horários de atendimento/tira-dúvidas:
Quinta (14:00 – 17:00 hs)
 Sala: BBT 221
 ELMASRI, R; NAVATHE, S. B. Sistemas de Bancos de Dados. 
São Paulo. Addison Wesley, 4ed, 2005.
 RAMAKRISHNAN, R. Sistema de Banco de Dados, McGraw-
Hill, 1999
Profa. Adriana Zanella Martinhago
Curso: Sistemas de Informação
UFV-CRP