Stored Procedures

Hugo Pedro Proença, © 2007
Stored Procedures
À medida que a complexidade dos sistemas aumenta,
torna-se cada vez mais difícil a tarefa de integrar o SQL
com as aplicações cliente.
z Além disto, é necessário que todas as aplicações cliente
conheçam minuciosamente a estrutura da base de
dados.
z Por outro lado,
l d o elevado
l
d número
ú
d
de pedidos
did
a partir
i d
de
uma grande quantidade de clientes pode deteriorar o
desempenho do sistema.
z Os procedimentos guardados no servidor (Stored
procedures) são um mecanismo essencial na
implementação de sistemas de bases de dados
robustos.
z
Hugo Pedro Proença, © 2007
Stored Procedures
z
Os seus benefícios resultam primariamente do facto de
serem executados no ambiente do servidor.
z
z
z
z
z
São executados em processos do servidor.
Servidor contém os dados
Não será mais lógico que as rotinas de interacção
com os dados sejam executadas no mesmo
ambiente?
Aproveitam mecanismos de optimização de
desempenho disponibilizados pelo SGBD
Podem devolver ou alterar valores e receber
parâmetros de entrada.
Hugo Pedro Proença, © 2007
Stored Procedures - Vantagens
z
Desempenho
Tipicamente
Ti
i
t a máquina
á i
servidora
id
de
d bases
b
de
d
dados tem maior capacidade de processamento
em relação a um vulgar posto de trabalho.
z Elimina
Eli i
a necessidade
id d de
d múltiplas
últi l
transmissões de dados através da rede.
z São compilados aquando da primeira execução
e depois g
guardados
a dados n
numa
ma tabela de sistema
sistema. É
também anexa informação sobre o melhor
caminho para os dados.
z
Hugo Pedro Proença, © 2007
Stored Procedures - Vantagens
z
Separabilidade Cliente/Servidor
z
Facilitam
F
ilit
a identificação
id tifi
ã clara
l
entre
t
as tarefas
t
f
que devem ser executadas pelo lado do cliente
e do servidor.
z Cliente.
Cli t
z Análise e interpretação dos resultados
z Criação de pedidos
z Servidor
z Interacção com a informação
Hugo Pedro Proença, © 2007
Stored Procedures - Vantagens
z
Segurança
z
Todas
T
d
as operações
õ
usuais
i (inserção,
(i
ã
alteração, remoção, consulta) podem ser
executadas a partir de stored procedures.
z As
A aplicações
li
õ
cliente
li t deixam
d i
de
d necessitar
it
de um conhecimento completo e minucioso
da estrutura da base de dados.
Hugo Pedro Proença, © 2007
Stored Procedures
z
Definição
z
CREATE PROCEDURE <nome>[;versao]
[@<parametro1> <tipo> [=<default>] [OUT]
...
AS
<BLOCO T-SQL>
Hugo Pedro Proença, © 2007
Stored Procedures
z
Exemplo
z
CREATE PROCEDURE selecciona_empregados
AS
SELECT *
FROM Empregados;
z
Este procedimento iria devolver todos os
atributos de todas as instâncias de
“Empregados”
Hugo Pedro Proença, © 2007
Stored Procedures
z
Execução
z
Execute <nome_procedimento>
@<parametro1>,...
@<parametroN>
p
z
Dentro de stored procedures podem-se executar
todos os tipos de instruções T-SQL excepto blocos
“CREATE “
Hugo Pedro Proença, © 2007
Stored Procedures
z
Parâmetros
z
Flexibilizam a utilização de stored procedures.
Deve ter-se em atenção que:
São locais aos procedimentos onde estão
definidos
z Devem
D
ser precedidos
did
de
d “@” para iindicar
di
que se trata de um valor de um parâmetro e
não outro objecto qualquer.
z
Hugo Pedro Proença, © 2007
Stored Procedures
z
Parâmetros – EXEMPLO 1
z
Criação de um procedimento para devolver
toda a informação acerca de determinado
empregado:
z CREATE PROCEDURE infEmpregado(@id int)
AS
SELECT *
FROM Empregado
WHERE Codigo=@id
Hugo Pedro Proença, © 2007
Stored Procedures
z
Parâmetros – EXEMPLO 2
z
Criação de um procedimento para inserir
informação relativa a um empregado
z CREATE PROCEDURE insEmpregado(@id int,
int
@nome VARCHAR(80), @profissao int)
AS
INSERT INTO Empregado(@id,@nome);
E
d (@id @
)
INSERT INTO ProfEmpreg(@id,@profissao);
Hugo Pedro Proença, © 2007
Stored Procedures
z
Parâmetros – Valores p/ Omissão
z
Pode-se definir um valor de omissão para cada
um dos parãmetros:
z
z
CREATE PROCEDURE insEmpregado(@id int, @nome
VARCHAR(80) @profissao
VARCHAR(80),
@
fi
int
i
= 5)
AS
INSERT INTO Empregado(@id,@nome)
INSERT INTO ProfEmpreg(@id,@profissao)
ProfEmpreg(@id @profissao)
Neste caso, sempre que não fôr colocada a
profissão do novo empregado, é-lhe atribuida a
profissão com código “5”.
5 .
Hugo Pedro Proença, © 2007
Valores de Retorno
z
Valores de Retorno
z
Pode-se retornar informação a partir de um
procedimento de 5 formas distintas:
z SELECT Æ Deve ser utilizado exclusivamente
como resposta aos pedidos de informação
efectuados pelas aplicações cliente.
z PRINT Æ Envio de mensagens não-criticas
aos utilizadores (não aos programadores!)
z RAISEERROR Æ Envio de mensagens críticas
aos utilizadores
z Parâmetros de Saída e valor de retorno Æ
Enviados à aplicação cliente como
indicativos de estado da execução.
Hugo Pedro Proença, © 2007
Parâmetros de Saída
Correspondem a parâmetros passados por
referência. O seu valor pode ser alterado
dentro do procedimento e essa alteração terá
efeito fora do âmbito do procedimento.
z Declaração
z Na declaração do procedimento
procedimento, basta
colocar a palavra chave “OUTPUT” a seguir
ao tipo de dados do parâmetro.
z Execução
z Ao executar o procedimento é necessário
indicar que o parâmetro é de saída
z
Hugo Pedro Proença, © 2007
Parâmetros de Saída
z
Exemplo
z CREATE Procedure XYZ( @a int, @b int
OUTPUT)
AS
SELECT @b=10
RETURN
z Será necessário que, na declaração, seja
colocad a explicitamente a informação de
que o parãmetro é de saída:
í
z DECLARE @v int, @z
z EXECUTE XYZ(@z,
(
, @v OUTPUT)
)
Hugo Pedro Proença, © 2007
Valores de Retorno
z
Uso de “Return”
Serve para terminar a execução de um
procedimento, podendo disponibilizar um valor
indicativo do estado de execução.
z Sintaxe
z RETURN [<valor_inteiro>]
z O valor de retorno deve servir exclusivamente
para o p
p
programador
g
e nunca p
para o utilizador
de uma aplicação-cliente
z Pode-se utilizar em qualquer ponto de um
procedimento,, sendo q
p
que as instruções
ç
q
que se
lhe seguem não serão executadas.
z
Hugo Pedro Proença, © 2007
Valores de Retorno
z
O SGBD disponibiliza por omissão valores
indicativos do estado relativo à execução
d cada
de
d procedimento:
di
t
0 Æ Execução com sucesso
z -1Æ Falta de um objecto
j
z ...
z 99 Æ
z Estes valores são sempre devolvidos
devolvidos, mesmo que
não exista a correspondente instrução “RETURN”
dentro do procedimento.
z Cabe ao utilizador criar códigos que não
interfiram com os criados por omissão (Valores
Naturais).
z
Hugo Pedro Proença, © 2007
Valores de Retorno Implícitos
z
– Exemplo 1:
z
CREATE procedure procedimento1
as
SELECT *
FROM Empregado;
Declare @status int
z Execute @status=procedimento1
z SELECT @status
z
Hugo Pedro Proença, © 2007
Valores de Retorno Explícitos
z
– Exemplo 1:
z
CREATE procedure procedimento1
as
SELECT *
FROM Empregado;
RETURN 5;
Declare @status int
z Execute @status=procedimento1
z SELECT @status
z
Hugo Pedro Proença, © 2007
Stored Procedures
z
Regras práticas:
z
z
z
z
z
z
Atribua sempre que possível valores por omissão
Devolva valores de retorno significativos
Avalie os valores de retorno após a execução de
cada procedimento
V ifi
Verifique
@@error
@@
após
ó cada
d bloco
bl
que envolva
l
alteração da informação.
Crie os novos procedimentos off-line
Nunca devolva informação da BD através de valores
de retorno ou de procedimentos de saída
Hugo Pedro Proença, © 2007
Stored Procedures – Variáveis
z
Definição de variáveis
– Dentro de um procedimento as
variáveis são definidas da seguinte
forma:
• DECLARE @<nome> <Tipo>
– Exemplo:
• DECLARE @x VARCHAR(100);
Hugo Pedro Proença, © 2007
Stored Procedures – Variáveis
z
Atribuição de variáveis
– As variáveis são normalmente
atribuídas dentro de um bloco SQL
através da instrução SELECT:
– Exemplos:
• SELECT @nome=‘João António’
•S
SELECT
C @tota
@total=COUNT(*)
COU
( ) FROM
O
Empregados
• SELECT @x=1
Hugo Pedro Proença, © 2007
Controlo de Execução
z
A Linguagem T-SQL como extensão ao
ANSI-SQL fornece um conjunto de
instruções de controlo da execução de
blocos.
z
Estas instruções permitem transferir para
dentro de procedimentos grande parte
das operações de manuseamento e
interacção com a informação.
Hugo Pedro Proença, © 2007
Controlo de Execução
z
Execução Condicional
– Através das palavras-Chave “IF” e
“ELSE” pode-se condicionar a execução
de determinado bloco .
– Forma:
• IF <expressão>
<BLOCO>
ELSE
<BLOCO>
Hugo Pedro Proença, © 2007
Controlo de Execução
z
Execução Condicional
– Tal como na generalidade das linguagens
estruturadas, quando um bloco é composto por
mais que uma instrução deve ser delimitado
pelas palavras-chave “BEGIN” e “END”
• Exemplo:
– IF @x>1
BEGIN
INSERT INTO T(1)
INSERT INTO R(2)
END
Hugo Pedro Proença, © 2007
Controlo de Execução
z
Execução Repetida
– Pode
Pode-se
se usar a palavra-chave
palavra chave “WHILE”
WHILE para
definir uma condição que irá determinar o
número de vezes que um bloco será executado.
– Forma:
• WHILE <expressão_booleana>
<expressão booleana>
BEGIN
<bloco>
END
Hugo Pedro Proença, © 2007
Controlo de Execução
z
Execução Repetida
– A palavra-chave “BREAK” serve, como seria de
esperar, para terminar a execução dentro do
bloco iterativo onde estiver incluída.
– A palavra-chave “CONTINUE” servirá envia a
execução para a avaliação da expressão
booleana.
Hugo Pedro Proença, © 2007
Controlo de Execução
z
Variáveis Globais
– São definidas pelo Sistema Gestor de Bases de
Dados e têm alcance sobre todos os
procedimentos criados.
– Nunca se podem declarar variáveis locais com o
mesmo nome das variáveis de ambiente
(globais)
– Acede-se ao seu valor através do uso de “@@”
• @@ERROR Æ Código de erro do ultimo bloco SQL
• @@ROWCOUNT Æ Número de linhas afectadas
pelo ultimo bloco.
• @@TRANCOUNT Æ Número total de transacções
activas do utilizador.
Hugo Pedro Proença, © 2007
Tabelas Temporárias
z
È por vezes imprescindível a criação de tabelas
temporárias para registarem valor que irão ser
necessários p
posteriormente (
(mas ainda no âmbito
do procedimento actual.
– No entanto, o SGBD não permite a execução da
instrução “CREATE”
– Nestas situações, podem-se criar tabelas
temporárias simplesmente através do uso do
caracter #
– Exemplo:
• INSERT INTO #t1
SELECT * FROM Empregados
Hugo Pedro Proença, © 2007
Tabelas Temporárias
z
Da execução do bloco anterior resultaria uma
nova tabela “#t” criada internamente na base de
dados “temp” e que o utilizador pode aceder como
se de outra tabela qualquer se tratasse.
tratasse
– SELECT *
FROM #t
É imprescindível que o utilizador apague
explicitamente a tabela antes de terminar a
execução do procedimento
z Deve
e e te
ter-se
se e
em ate
atenção
ção o retorno
eto o em
e casos
imprevistos ou excepcionais.
z
– Mesmo nestes, a eliminação da tabela deve ser
efectuada.