Laboratório de Banco de Dados II
Aula 1
Stored Procedures
Stored procedures
Uma seqüência de comandos SQL pode ser agrupada, formando uma stored procedure. A sua execução é
comandada, invocando-se o nome da stored procedure. No entanto, é mais do que simplesmente um substituto
ao agrupamento de comandos SQL.
1. Considerações sobre stored procedure
a) Se a seqüência de comandos é submetida várias vezes, não há a necessidade de submeter toda a
seqüência novamente, bastando invocar o nome da procedure. É mais econômico do ponto de vista de
codificação. Quando é preciso alterar alguma coisa, bastar alterar num único ponto da procedure; Não
repetir as correções em todos os pontos em que a seqüência se repete dentro de uma aplicação.
b) Os comandos são pré-compilados e armazenados no banco de dados em formato de execução pelo banco
de dados. Ou seja, não há a necessidade de re-compilação do código fonte sempre que a execução for
comandada. O gerenciador já fez uma análise do código fonte no momento da criação da procedure e
não precisa repeti-la em todas as execuções. A parte que precisa ser re-avaliada na execução é o plano
de acesso, pois este pode mudar de acordo com os dados.
c) Diminui a quantidade de mensagens trocadas entre o requisitante e o servidor, porque toda a seqüência é
executada no escopo do servidor. Do contrário, cada comando da seqüência é enviada ao servidor e
retornada ao requisitante antes do envio do próximo comando. Entre estes comandos podem ser
transferidos dados em volume significativo em termos de resultados intermediários ou mesmo finais a
serem aplicados definitivamente no banco de dados. Dependendo do número de comandos, isto impacta
no tempo de resposta.
d) Existe um ganho na segurança. Você não concede acesso às tabelas, mas concede somente o direito de
execução sobre as procedures, estabelecendo com mais rigor quais operações o usuário pode executar.
e) Os comandos de controle transacional podem ser deslocados para dentro do SGBD. Quando comandado
pela aplicação, eventos de comunicação podem produzir bloqueios demorados sobre os recursos, por
conexões aparentemente inativas.
f) Podem ser passados parâmetros de execução.
g) As aplicações tornam-se menos portáveis com a utilização de stored procedures.
2. Alguns comandos que podem fazer parte de uma procedure
Vamos considerar apenas casos simples, tendo em vista que os diversos gerenciadores apresentam códigos
proprietários e não temos interesse em cobrir muitos aspectos específicos.
As stored procedure são análogas às procedures em Pascal ou function em C. Admitem parâmetros de
entrada/saída. Permitem a utilização das construções SQL já estudadas, além de outras, como comandos de
controle do fluxo de execução e loop e declaração de variáveis.
Vamos, rapidamente, rever alguns comandos válidos no laboratório:
Declaração de variáveis
declare @var1
@var2
@var3
@var4
.
.
.
int,
char(10),
float,
varchar(32),
Controle de fluxo:
if (Boolean_condition)
begin
.
.
.
End
else
begin
.
.
.
End
Na parte de condição qualquer expressão booleana pode ser utilizada, inclusive aquelas presentes na cláusula
where, como exists, etc...
Repetição:
while (condition)
begin
.
.
.
end
Controle de fluxo numa estrutura de repetição:
1. break -- interrompe o fluxo de execução de um loop.
while (Boolean_condition)
begin
.
.
If (condition)
break
.
.
end
2. continue – reinicia o loop sem executar os comandos seguintes dentro do loop
while (Boolean_condition)
begin
.
.
If (condition)
continue
.
.
end
Abandonar a execução da stored procedure:
If (condition)
begin
...
Return
end
Atribuição de valores às variáveis:
set @var1 = 10,
@var2 = 'abcd adb',
@var3 = 123.87
Atribuição às variáveis de valores obtidos do banco de dados:
select @var1 = habitantes,
@var2 = nome,
@var3 = habitantes / 1000.0
from tb_cidades
where codigo = 'A99999'
Atribuição às variáveis de valores obtidos do banco de dados com a cláusula TOP:
declare @codigo char(15)
select top 1 @codigo = codigo
from tb_cidades
order by codigo -- nestes casos, importante a cláusula de ordernação
Concatenção de strings:
Utilizar o operador "||" (duplo símbolo pipe)
Exemplo:
select 'abc' || '123' from dual; -- Oracle
set @variavel = @variavel_1 || 'xyz'
select @variavel = nome || sobre_nome
from tb_cidades where codigo = 'A999999'
No Lab do nosso curso, você pode também, indistintamente, utilizar o símbolo "+"
select 'abc' + '123'
set @variavel = @variavel_1 + 'xyz'
select @variavel = nome + sobre_nome
from tb_cidades where codigo = 'A999999'
SQL opera conjuntos de registros/linhas
Não podemos presumir que os comandos afetam somente um registro.
Pode afetar um conjunto vazio, unitário ou com vários elementos.
Resultados podem não ser os esperados se você não levar em conta a multplicidade dos registros afetados.
Por exemplo,
declare @codigo char(20)
select @codigo = codigo from tb_cidades
Se existem várias linhas da tabela, qual o valor da variável?
Obtenção dos primeiros registros de uma consulta (Esclarecimento quanto à cláusula TOP)
Às vezes, estamos interessados somente nos primeiros registros de um conjunto que satisfazem às condições de
seleção. Para isto utilizamos a cláusula “top n”.
Exemplo:
select top 5 *
from tb_cidades
select
from
where
order
top 2 codigo, nome
tb_cidades
nome = 'Santos'
by nome
delete top 10 from tb_cidades
delete top 10 from tb_cidades where nome = 'São Pdro
update top 2 tb_cidades
set habitantes = 100
where nome = 'Santos'
Como atribuir valores às variáveis quanto se utiliza a cláusula TOP:
declare @codigo char(15)
select top 1 @codigo = codigo
from tb_cidades
order by codigo -- nestes casos, importante a cláusula de ordernação
a) A tabela é classificada em ordem crescente de código;
b) A primeira linha deste resultado é obtida;
c) O valor da coluna código desta linha obtida é atribuída à variável. Em outras palavras, é uma maneira
artificial de atribuir à variável o menor código presente na tabela.
Verificação da existência de registros selecionados ou afetados
Existem várias formas de verificação da existência de registros que satisfazem a certa condição, ou mesmo se
um comando SQL selecionou ou afetou registros. Em capítulos posteriores, veremos outras formas de
verificações.
No laboratório, o SGBD disponibiliza uma variável global que imediatamente após o comando pode ser
consultada para saber quantas linhas foram selecionadas, inseridas, alteradas ou removidas. A variável tem o
nome
@@rowcount
Por exemplo:
declare @codigo_cidade_corrente
select top 1 @codigo_cidade_corrente = codigo,
@qtd_habitantes_corrente = habitantes
from tb_cidades
where habitantes > @qtd_habitantes_corrente
order by habitantes
if (@@rowcount = 0)
begin
.
.
-- acabou...
.
.
end
.
.
.
Comando para criação de stored procedure
create procedure pr_exemplo (lista de parâmetros e respectivos tipos) as
begin ....... comandos sql .... end
Comando para remoção de stored procedure
drop procedure pr_lista_cidade
Comando para emitir uma mensagem
print "Esta é uma mensagem..."
Exemplo de criação de stored procedure
create procedure pr_lista_cidade (@cod_cid varchar(15))
as
begin
declare @nome_cid varchar(30)
select @nome_cid = nome
from tb_cidades
where codigo = @cod_cid
if (@@rowcount = 0)
begin
print "cidade não existe."
return
end
select @cod_cid, @nome_cid
select codigo,
nome,
habitantes
qtd_ruas,
estado
from tb_cidades
where codigo = @cod_cid
-- fim do código fonte
end
;
4. Observações sobre o exemplo
a) Neste exemplo específico, os resultados poderiam ser obtidos de forma mais simples. A codificação
exemplifica as formas de utilização de algumas estruturas.
b) A última linha contém somente um comentário “--”.
c) Execute um comando de cada vez. Neste caso, todo o texto refere-se a um único comando de criação de
procedure. Se você tentar executar mais que um comando, por exemplo, um drop procedure
pr_lista_cidade seguido do código fonte para a sua criação, ocorrerá erro. Será necessária a
colocação do terminador de comando que não é padrão. Se você utiliza a ferramenta do nosso ambiente,
como o msql, o terminador é “;”. Se estiver utilizando o browser, não poderá ser informado, porque o
envio indica o término da sequência de comandos. Ele depende da ferramenta cliente utilizada. Não faz
parte da procedure; Apenas informa que todo o fonte deve ser submetido ao servidor como um único
bloco de comandos.
d) Observe como a atribuição de variáveis é realizada. Esta sintaxe não é comum a todos os gerenciadores.
e) Para executar a procedure, substitua os valores dos parâmetros por algum que seja válido, nas chamadas
abaixo:
exec pr_lista_cidade 'aaa'
exec pr_lista_cidade 'bbb'
Atividade
Crie uma stored procedure de nome
pr_lista_dados_segunda_cidade
que faz o seguinte:
a)
b)
c)
d)
Recebe como parâmetro o código de uma cidade que consta da tabela tb_cidades;
Guarda em variável o número de habitantes da cidade correspondente ao código;
Se a cidade não existir, emite uma mensagem e termina a execução da procedure;
Utilizando, adequadamente, a estrutura “top n” e considerando que a cidade recebida como parâmetro
está em i-ésima posição em número de habitantes, mostra todos os dados de todas as cidades que estão
na posição i+2 em número de habitantes;
e) Seguindo raciocínio semelhante e supondo que a cidade recebida como parâmetro está em j-ésima
posição em quantidade de ruas, esta mesma procedure deve mostrar todos os dados de todas as cidades
que estão na posição j-2 em quantidade de ruas;
f) Observação: Na parte da apresentação, utilizar diretamente com “top n”, não produz o resultado
esperado porque não sabemos quantas cidades tem o mesmo número de habitantes e ruas e satisfazem à
condição de posição no “ranking”.
Por exemplo, suponha que existam as seguintes cidades apresentadas em duas ordenações diferentes.
a) Em ordem de habitantes:
codigo
--------------0002
0011
0013
0007
0008
0006
0009
0005
0004
0003
0012
0010
0001
nome
habitantes qtd_ruas
estado
------------------------------ ----------- ----------- -----BRASILIA
890
50000 DF
JK
10000
778 SP
SEGUNDA_MENOR
15000
6500 RJ
CEILANDIA
27000
6500 DF
BRASLANDIA
28000
8509 DF
SAO CARLOS
34235
501 SP
MAUA
35001
5301 SP
SAO CAETANO
47000
8600 SP
SANTO ANDRE
300001
50001 SP
TAGUATINGA
500001
32433 DF
SEGUNDA_MAIOR
500001
300 RJ
OSASCO
550001
80001 SP
SAO PAULO
9000000
500000 SP
b) Em ordem de quantidade de ruas
codigo
nome
habitantes qtd_ruas
estado
--------------- ------------------------------ ----------- ----------- -----0012
SEGUNDA_MAIOR
500001
300 RJ
0006
SAO CARLOS
34235
501 SP
0011
JK
10000
778 SP
0009
MAUA
35001
5301 SP
0007
CEILANDIA
27000
6500 DF
0013
SEGUNDA_MENOR
15000
6500 RJ
0008
BRASLANDIA
28000
8509 DF
0005
SAO CAETANO
47000
8600 SP
0003
TAGUATINGA
500001
32433 DF
0002
BRASILIA
890
50000 DF
0004
SANTO ANDRE
300001
50001 SP
0010
OSASCO
550001
80001 SP
0001
SAO PAULO
9000000
500000 SP
Executando
pr_lista_dados_segunda_cidade '0009' /* Mauá */
Os resultados são os seguintes:
codigo
--------------0003
0004
em habitantes */
0005
0006
em quantidade de
0007
0008
0009
0010
0001
0002
0011
0012
0013
nome
habitantes qtd_ruas
------------------------------ ----------- ----------TAGUATINGA
500001
32433
SANTO ANDRE
300001
50001
estado
-----DF
SP /* 2.a maior
SAO CAETANO
SAO CARLOS
ruas */
CEILANDIA
BRASLANDIA
MAUA
OSASCO
SAO PAULO
BRASILIA
JK
SEGUNDA_MAIOR
SEGUNDA_MENOR
DF
DF
SP
SP
SP
DF
SP
RJ
RJ
47000
34235
27000
28000
35001
550001
9000000
890
10000
500001
15000
8600 SP
501 SP /* 2.a menor
6500
8509
5301
80001
500000
50000
778
300
6500
Executando
pr_lista_dados_segunda_cidade '0005' /* São Caetano */
Os resultados são os seguintes:
codigo
nome
habitantes qtd_ruas
estado
--------------- ------------------------------ ----------- ----------- -----0003
TAGUATINGA
500001
32433 DF /* 2.a
em habitantes */
0004
SANTO ANDRE
300001
50001 SP
0005
SAO CAETANO
47000
8600 SP
0006
SAO CARLOS
34235
501 SP
0007
CEILANDIA
27000
6500 DF /* 2.a
em quantidade de ruas */
0008
BRASLANDIA
28000
8509 DF
0009
MAUA
35001
5301 SP
0010
OSASCO
550001
80001 SP
0001
SAO PAULO
9000000
500000 SP
0002
BRASILIA
890
50000 DF
0011
JK
10000
778 SP
0012
SEGUNDA_MAIOR
500001
300 RJ /* 2.a
em habitantes */
0013
SEGUNDA_MENOR
15000
6500 RJ /* 2.a
em quantidade de ruas */
Prof. Satoshi Nagayama
maior
menor
maior
menor