segunda-feira, 22 de janeiro de 2007

Stored Procedures em Firebird

Introdução

  • "Stored Procedures", ou SP, são rotinas que rodam no servidor e podem ser chamadas pela aplicação cliente.
  • Stored Procedures são pre-compiladas. Portanto, não é necessário mandar o comando pela rede para ser validado e executado. Elas são simplesmente executadas.
  • Procedures podem possuir parâmetros e, assim como o SELECT, retornar dados de uma tabela.

Vantagens

  • A aplicação cliente conterá apenas parte do código. Assim sua manutenção será simples e rápida.
  • Fácil manutenção. Aplicações cliente não precisam ser recompiladas ou redistribuídas caso alguma alteração nas SP's seja feita.
  • Ganho de performance pela diminuição do tráfego na rede.

Chamando Stored Procedures

  • Stored Procedures podem executar uma ação e não retornar nenhum dado
  • Procedures selecionáveis podem retornar tabelas, assim como o SELECT, ou Views. Pode ser usado como uma tabela em um comando SELECT:
    • SELECT a, b FROM procedurename (params) ...
  • Para habilitar a chamada de procedure o usuário deve antes atribuir direitos a ela:
    • EXECUTE rights (granted by GRANT/REVOKE)

Declarando Stored Procedures

CREATE PROCEDURE name [(param1 datatype1, param2 datatype2, ...)] 
[RETURNS (param3 datatype3, param4 datatype4, ...)]
AS BEGIN
 <corpo>
END;

Sintaxe para declaração de uma variável

DECLARE VARIABLE variable datatype 

(Os parâmetros de Entrada e Saída de uma SP são usados como variáveis)

SET TERM

Todo comando dentro da Stored Procedure deve terminar com um ; (Ponto e vírgula), a Procedure também. Para distinguir o ponto-e-vígula dentro da Procedure do ponto-e-vírgula de terminação da Procedure pode ser usado outro terminador. Isso pode ser feito usando o comando SET TERM:

SET TERM !! ; 
CREATE PROCEDURE x AS BEGIN ... END !!
SET TERM ; !!

O primeiro "SET TERM" substitui o terminador padrão (;) pelo terminador (!!). A procedure contém declarações utilizando o terminador padrão (;). A procedure termina a sua própria declaração usando o "novo" terminador (!!). Depois disso, o símbolo de terminação volta a ser o padrão (;).

Criando, alterando e destruindo Stored Procedures

  • Criar: CREATE PROCEDURE nome ...
  • Alterar: ALTER PROCEDURE nome ... (o restante é igual ao comando CREATE PROCEDURE)
  • Criar ou Alterar, dependendo da existência da SP: CREATE OR ALTER ...
  • Apagar: DROP PROCEDURE nome. Só pode ser executada pelo done (owner) da SP.
    Você só pode apagar SP, quando elas não são usadas pode outras SP's, Trigger's ou View's.

Exceções

  • Criar: CREATE EXCEPTION nome “mensagem“
  • Alterar: ALTER EXCEPTION nome “mensagem“
  • Apagar: DROP EXCEPTION nome

Exemplos

/* --- Retornando um valor simples –----------------------------------- */ 
CREATE PROCEDURE Mul (a INTEGER, b INTEGER)
RETURNS (Result INTEGER)
AS BEGIN
Result = a * b;
END

/* --- Retornando uma tabela –--------------------------------- */ 
CREATE PROCEDURE CountTo10
RETURNS (Cnt INTEGER)
AS BEGIN
Cnt = 1;
WHILE (Cnt <= 10) DO BEGIN
SUSPEND; /* Return next line */
Cnt = Cnt + 1;
END;
END

Comandos

Comando Descrição Versão
BEGIN <statements> END
Compound Statement like in PASCAL
variable = expression
Assignment. "variable" can be a local variable, an "in" or an "out" parameter.
compound_statement
A single command or a BEGIN/END block
select_statement
Normal SELECT statement. The INTO clause must be present at the end of the statement. Variable names can be used with a colon preceding them. Example
SELECT PRICE FROM ARTICLES
WHERE ARTNO = :ArticleNo
INTO :EPrice

/* Comment */
Comment, like in C
-- Comment
Single line SQL comment 1.5.0
DECLARE VARIABLE name datatype [= startval]
Variable declaration. After AS, before the first BEGIN. 1.5.0 (startval)
EXCEPTION
Re-fire the current exception. Only makes sense in WHEN clause 1.5.0
EXCEPTION name [message]
Fire the specified exception. Can be handled with WHEN. 1.5.0 (message)
EXECUTE PROCEDURE name arg, arg
RETURNING_VALUES arg, arg
Calling a procedure. arg's must be local variables. Nesting and recursion allowed.
EXIT
Leaves the procedure (like in PASCAL)
FOR select_statement DO
compound_statement
Executes "compound_statement" for every line that is returned by the SELECT statement
IF (condition)
THEN compound_statement
[ELSE compound_statement]
IF statement, like in PASCAL
POST_EVENT name
Posts the specified event
SUSPEND
Only for SELECT procedures which return tables: Waits for the client to request the next line. Returns the next line to the client.
WHILE (condition) DO
compound_statement
WHILE statement. Like in PASCAL.
WHEN {EXCEPTION a | SQLCODE x | ANY} DO 
 compound_statement
Exception handling. WHEN statements must be at the end of the procedure, directly before the final END.
EXECUTE STATEMENT stringvalue
Executes the DML statement in stringvalue 1.5.0
EXECUTE STATEMENT stringvalue 
INTO variable_list
Executes Statement and returns variables (singleton) 1.5.0
FOR EXECUTE STATEMENT stringvalue 
INTO variable_list DO 
 compound_statement
Executes Statement and iterates through the resulting lines 1.5.0

 

6 comentários:

Thiago disse...

foi dito que o SUSPEND espera o cliente pedir o próximo registro para que a SP o devolva...

como que peço o próximo registro usando o delphi 7, usando os componentes da aba interbase ?

estou bem encrencado com isso, uma vez que o .next, etc do tibstoredproc não da certo (da um erro dizendo que o tdataset naum está aberto ou algo assim)

gostaria muito de uam resposta quanto a isso: tognado@gmail.com

Erick Almeida disse...

Thiago, eu não sei como você está executando o componente citado... mas não é preciso fazer nada especial...

VocÊ pode conseguir os resultados de uma SP mesmo usando uma TQuery. As SP's do firebird são selecionáveis ou seja, você pode usá-las como uma tabela.

Ex:
Select * from MySP;
ou
Select * from MySp2(:param1, :param2);

Espero ter ajudado.

Berdam disse...

Ai blz, estou com um erro muito estranho, tenho uma atualizador, que possui a base de dados contendo as atualizações, nele tenho algumas alterações de procedures, campos,tabelas do banco de dados do cliente.
Quando executo o atualizador ele percorre a base de atualizações, e executa os scripts na base de dados da empresa, este problema só está ocorrendo quando crio ou atualizo uma procedure.
Quando executo a alteração de procedure uma exceção é lançada, na exceção eu pego o comando SQL e insiro no ibexpert e ele passa sem problemas.

Abaixo em negrito segue a exceção, claro que eu retiro os caracteres que fazem parte da exceção e executo apenas o SQL, fazendo isso no ibexpert funciona, já no atualizador não. Repetindo que somente ocorre isso na atualização das stored procedures, as outras atualizações correm normalmente.

A exceção ocorre no primeiro select, "CAUSA : Token unknown - line 40, column 8
?", mas colocando no ibexpert, nenhum erro aparece.


Alguém possui alguma explicação plausível para isso????


---------------------------
Warning
---------------------------
Não foi possível a inclusão/alteração do comando:
1019:ALTER PROCEDURE COTFICHA_FORNECEDOR (
IN_COTACAO CHAR(10),
IN_COD_EMPRESA CHAR(6))
AS
declare variable var_contafichaserv smallint;
declare variable var_codfornecedor char(5);
declare variable var_contafichaprod smallint;
declare variable var_coditem varchar(20);
declare variable var_sequencia smallint;
declare variable var_quantidade double precision;
declare variable var_servico varchar(40);
declare variable var_total smallint;
declare variable var_contap smallint;
declare variable var_contas smallint;
declare variable var_seqfornecedor smallint;
declare variable var_contador integer;
declare variable var_numfornecedor smallint;
declare variable var_nfichaspreenchidas smallint;
declare variable var_achoufichaforn smallint;
begin
/*----------------------------------------------------------------------------
- Essa SP verifica se o fornecedor já tem a respectiva ficha de Cotação -
- gerada. Se ainda não existir, o sistema preenche automaticamente -

Versão: 1.0.3

----------------------------------------------------------------------------*/

var_contafichaprod = 0;
var_contafichaserv = 0;
var_contap = 0;
var_contas = 0;
var_contador = 0;
var_numfornecedor = 0;

/* Verifica o número de fornecedores e registra as fichas de cabeçalho */

select count(z.cd_empresa)
from cotacao_fornece z
where (z.codigo = :in_cotacao) and (z.cd_empresa = :in_cod_empresa)
into :var_numfornecedor;

select count(x.cd_empresa)
from cotacao_fornecedor_header x
where (x.codigo = :in_cotacao) and (x.cd_empresa = :in_cod_empresa)
into :var_nfichaspreenchidas;


if (var_numfornecedor > var_nfichaspreenchidas) then
begin
for select m.cd_fornece, m.seq_fornece
from cotacao_fornece m
where (m.codigo = :in_cotacao) and (m.cd_empresa = :in_cod_empresa)
into :var_codfornecedor, :var_seqfornecedor
do begin
select count(n.codigo)
from cotacao_fornecedor_header n
where (n.cd_fornece = :var_codfornecedor) and
(n.seq_fornece = :var_seqfornecedor) and
(n.cd_empresa = :in_cod_empresa) and (n.codigo = :in_cotacao)
into :var_achoufichaforn;

if (var_achoufichaforn = 0) then
begin
insert into cotacao_fornecedor_header (codigo, cd_empresa, cd_fornece,
seq_fornece, valortotal, vlrredespacho, valoreajuste, valordesconto)
values
(:in_cotacao, :in_cod_empresa, :var_codfornecedor,
:var_seqfornecedor, 0, 0, 0, 0);
end

end
end

-- Conta o número de produtos
select count(a.codigo)
from cotacao_itens a
where (a.codigo = :in_cotacao) and (a.cd_empresa = :in_cod_empresa)
into :var_contap;

-- Conta o número de Serviços
select count(b.codigo)
from cotacao_servicos b
where (b.codigo = :in_cotacao) and (b.cd_empresa = :in_cod_empresa)
into :var_contas;

-- Corrigido em 14-12-07 - Deixa-se de contar os itens dos fornecedores
-- e passa a contar os itens gerais em servicos e produtos
--var_total = var_contafichaprod + var_contafichaserv;

var_total = var_contap + var_contas;

--Verifica se foram inseridos itens na cotação

if (var_total > 0) then
begin
-- Procura todos os fornecedores listados na cotação
for select cf.cd_fornece, cf.seq_fornece
from cotacao_fornece cf
where (cf.codigo = :in_cotacao) and (cf.cd_empresa = :in_cod_empresa)
into :var_codfornecedor, :var_seqfornecedor
do
begin

--Conta o número de itens registrados para o forncedor
select count(cfi.codigo)
from cotacao_fornece_itens cfi
where (cfi.codigo = :in_cotacao) and
(cfi.cd_empresa = :in_cod_empresa) and
(cfi.cd_fornece = :var_codfornecedor) and
(cfi.seq_fornece = :var_seqfornecedor)
into :var_contafichaprod;

-- se a quantidade de itens for menor, ele irá inserir os itens faltantes
-- para aquele fornecedore
if (var_contafichaprod < var_contap) then
begin
-- localiza todos os itens da cotação e insere todos os faltantes
for select a.cd_item, a.seq_item, a.quantidade
from cotacao_itens a
where (a.codigo = :in_cotacao) and (a.cd_empresa = :in_cod_empresa)
into :var_coditem, :var_sequencia, :var_quantidade
do
begin
select count(b.cd_item)
from cotacao_fornece_itens b
where (b.codigo = :in_cotacao) and
(b.cd_empresa = :in_cod_empresa) and
(b.cd_fornece = :var_codfornecedor) and
(b.seq_fornece = :var_seqfornecedor) and
(b.cd_item = :var_coditem) and
(b.seq_item = :var_sequencia)
into :var_contador;

if (var_contador = 0) then
begin
insert into cotacao_fornece_itens (cd_empresa, codigo, cd_fornece,
seq_fornece, cd_item, valor_unit, ipi, valor_ipi, valor_total,
seq_item, quantidade, valorfinal, valor_kgm)
Values
(:in_cod_empresa, :in_cotacao, :var_codfornecedor,
:var_seqfornecedor, :var_coditem, 0, 0, 0, 0,
:var_sequencia, :var_quantidade, 0, 0);
end
end
end
-- Fim do processo de inserção de itens/materiais-------------------------
-- Verifica o número de servicos na cotação-------------------------------
select count(cfs.codigo)
from cotacao_fornece_serv cfs
where (cfs.codigo = :in_cotacao) and
(cfs.cd_empresa = :in_cod_empresa) and
(cfs.cd_fornece = :var_codfornecedor) and
(cfs.seq_fornece = :var_seqfornecedor)
into :var_contafichaserv;

if (var_contafichaserv < var_contas) then
begin
for select cs.seq_item, cs.servico, cs.quantidade
from cotacao_servicos cs
where (cs.codigo = :in_cotacao) and (cs.cd_empresa = :in_cod_empresa)
into :var_sequencia, :var_servico, :var_quantidade
do
begin
select count(k.codigo)
from cotacao_fornece_serv k
where (k.codigo = :in_cotacao) and
(k.cd_empresa = :in_cod_empresa) and
(k.cd_fornece = :var_codfornecedor) and
(k.seq_fornece = :var_seqfornecedor) and
(k.descricao = :var_servico) and
(k.seq_item = :var_sequencia)
into :var_contador;

if (var_contador = 0) then
begin
insert into cotacao_fornece_serv (cd_empresa, codigo, cd_fornece,
seq_fornece, valor, seq_item, descricao, quantidade)
Values
(:in_cod_empresa, :in_cotacao, :var_codfornecedor,
:var_seqfornecedor, 0, :var_sequencia, :var_servico,
:var_quantidade);
end
end
end
-- -----------------------------------------------------------------------
end
end
end


CAUSA : Token unknown - line 40, column 8
?


Confirmar a Atualização do Parâmetro?
---------------------------
Yes No
---------------------------

Fernando Medeiros disse...
Este comentário foi removido pelo autor.
Anônimo disse...

Isso mesmo que o erick disse, as storeds procedures sao selecionaveis como se fossem uma query, mas as sps devem ter o comando suspend para que o resultado seja selecionado.

alguma dica complementar você pode encontrar:

Jason Mello disse...

Como faço pra fazer uma cópia de uma SP com todas as suas variáveis?

Obrigado

Neobux