por ·0 comentários

Quando uma transação no banco de dados exigir uma avaliação de registros já existentes no banco para decidir se o que precisa fazer é um UPDATE ou um INSERT em geral exige no mínimo duas chamadas para o banco de dados… uma para fazer o SELECT (toma decisão sobre o comando a aplicar) e então envia outra chamada com um UPDATE ou com um INSERT;

Para otimizar estes passos é possível utilizar “MERGE” ou “UPDATE OR INSERT”. Quando o desejo é passar os dados de uma tabela para outro o MERGE pode ser o mais adequado para a operação, mas se deseja atualizar os dados com entradas de parâmetros, sem vincular a uma tabela, o mais adequado será usar o comando “UPDATE OR INSERT”.

 

  1. Usando MERGE para fazer INSERT OR UPDATE com um único comando com base em uma tabela

O comando MERGE permite submeter uma única chamada para o banco de dados, quando as condições “where” coinciderem executa um UPDATE se não coincidirem executa um INSERT;

Exemplo da documentação:

[code language=”sql”]
MERGE INTO customers c 
USING    (SELECT * FROM customers_delta WHERE id > 10) cd    
ON (c.id = cd.id) 
WHEN MATCHED THEN   
UPDATE SET name = cd.name 
WHEN NOT MATCHED THEN   
INSERT (id, name)    VALUES (cd.id, cd.name)
[/code]
No Firebird3, é possível utilizar DELETE também – ou seja, agora é possível utilizar UPDATE,INSERT ou DELETE;

No exemplo, para que funcione, há uma premissa básica que faz parte do MERGE… é obrigatório que “customers_delta” tenha pelo menos 1 linha… Se o resultado de “customers_delta” for nenhuma linha, NADA será feito (nem UPDATE nem INSERT)…
De outro lado, existindo pelo menos 1 linha em customers_delta, ocorrerá a comparação ON (c.id = cd.id)…  em encontrando o mesmo código “WHEN MATCHED” será executado o UPDATE… caso não encontre o código será efetuado o INSERT (WHEN NOT MATCHED)…

 

Avançando um pouco mais… digamos que queremos avaliar uma única tabela… assim:   procurar se no cadastro de produto existe um determinado código…. caso exista UPDATE, caso não exista INSERT…

Para que funcione, lembra da premissa básica – o SELECT de USING, é obrigatório retornar pelo menos 1 linha. Quando o código procurado existir, o UPDATE será executado com sucesso, mas…..   se não existir o código, o USING SELECT  quebra a premissa básica (não existe a linha) – com isto o INSERT não funciona.

Vamos fazer uma adaptação para que o MERGE funcione usando RDB$DATABASE (o que o deixa igual ao UPDATE OR INSERT)

Exemplo:
[code language=”sql”]
MERGE INTO fb_prod c
USING
(SELECT ‘999999’ codigo, ‘nome do produto’ nome FROM rdb$database) cd
ON (c.codigo = cd.codigo)
WHEN MATCHED THEN
UPDATE SET nome = cd.nome
WHEN NOT MATCHED THEN
INSERT (codigo, nome,id) VALUES (cd.codigo, cd.nome,1)
[/code]

2. Usando UPDATE OR INSERT :

Ao usar UPDATE OR INSERT não é necessário vincular o comando a uma tabela associada o que torna o comando mais flexível em relação ao MERGE… veja um exemplo de como fazer:
[code language=”sql”]
UPDATE OR INSERT INTO fb_prod (codigo, nome, id)
values (‘999998’, ‘nome usando update or insert’, 2)
MATCHING (codigo)

[/code]

Em resumo, cada situação há várias formas de fazer a mesma coisa… escolha a que for mais eficiente

por ·0 comentários

No Firebird 3 passou a ser possível criar exception com parâmetros que adicionam texto à mensagem retornada para o usuário.

create exception e_invalid_val ‘Valor invalido @1  para a coluna @2’;

…
if (val < 1000) then
thing = val;
else

exception e_invalid_val using (val, ‘thing’);
end

 

Este recurso na prática não adiciona nenhum ganho, já que no 2.5 era possível adicionar um texto à exceção..

Exception erro ‘Valor invalido ‘||val||’ para a coluna xxxx’;

 

 

por ·0 comentários

Outro dia precisava montar um RANK de produtos, ou seja, precisava saber qual a classificação no RANK de cada produto com possibilidade de apresentar somente os primeiros 100 produtos.
Fazer RANK de dados não é um recurso disponível até o firebird 2.5 – o que irá ocorrer somente no firebird 3.0 – como ainda não é uma versão de produção, então o caminho ainda é resolver com o 2.5;Captura de tela 2016-03-02 21.13.57

Depois de ler muitas sugestões de como fazer, conclui que tinha que montar uma própria – eram todas confusas e levavam a resultados com muito tempo de respostas.

Estratégia:
Montar um select que ordene em ordem crescente os valores de venda em ordem decrescente de cada um dos produtos, depois criar um loop que marque com uma coluna na resposta com a posição do produto (o RANK);

Código Delphi:

      
       query1 := TFDQuery.create(nil);
       try
           with query1 do
           begin
              connection := xxx;
              sql.text := '....'
              open;  
           end;
           ....
       finally
          query1.free;
       end;

Select no Firebird:

execute block 
returns (codigo varchar(18),
         valor double precision,
         conta integer, 
         rank integer, 
         acumulado double precision)
as
declare variable n integer;
begin
   n = 0;
   acumulado = 0;

   for select codigo,sum(valor) valor, count(*) ct from sigcaut2
       where valor&gt;0 and 
             data between '01/01/2015' and '12/31/2015'
       group by codigo
       order by valor desc
       rows 100
       into :codigo,:valor,:conta
   do
     begin
       n = n +1;
       rank = n;
       acumulado = acumulado + valor;
       suspend;
   end
end

 

por ·0 comentários

Já notou que tem algumas coisas que são diferentes de um banco de dados para outro…

Ex: Um UpperCase se escreve diferente dependendo do banco de dados que você irá utilizar;

Esquece…  o FireDAC resolve quase tudo para você…..

 

Escreve:

Select   {uCase(Nome)} from clientes     ///o FireDAC irá converter para UpperCase o nome do cliente;

Select   {LCase(Nome)} from clientes     ///o FireDAC irá converter para LowerCase o nome do cliente;

 

Ver no manual do FireDAC: Preprocessing Macros

por ·0 comentários

Até a versão 2.5 do Firebird não existe a figura da coluna Auto-Incremento que existe em outros banco de dados.

No Firebird, você deve criar um GENERATOR que ira cuidar de gerar a sequência e associar uma Trigger para incluir o sequencial na tabela.

Exemplo para criar uma tabela de cliente com código Auto-Incremento:
[code]
// criar o recurso para armazenar o auto-incremento
CREATE GENERATOR CODIGO_CLIENTE; (formato mais antigo)
ou
CREATE SEQUENCE CODIGO_CLIENTE; (formato novo)

// criar a tabela
CREATE TABLE FB_CLIENTE (
CODIGO INTEGER NOT NULL,
NOME VARCHAR(50),
ENDERECO VARCHAR(128),
CIDADE VARCHAR(32),
ESTADO VARCHAR(5)
);

// para marcar o valor inicial (é opcional)
ALTER SEQUENCE CODIGO_CLIENTE RESTART WITH 0;

// Trigger para gerar o próximo código
SET TERM ^ ;
CREATE OR ALTER TRIGGER FB_CLIENTE_GERAR_CODIGO FOR FB_CLIENTE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.codigo =  gen_id(CODIGO_CLIENTE,1);
end
^
SET TERM ; ^
[/code]

Usando insert para retornar o valor o código inserido:
[code]
insert into fb_cliente
(nome,endereco,cidade,estado)
values( :nome,:endereço,:cidade,:estado)
returning codigo into :codigo_cliente
[/code]
 

Com FireDAC, utilizar o método FDQuery1.OPEN e irá retornar o parâmetro INTO como na lista de FIELDS (código cliente);