Firebird
Conteúdos relacionados ao banco Firebird (Configuração, SQL's de apoio, etc.)
- Instalação / Configuração
- Alguns comandos Gfix
- Alterar a senha do usuário SYSDBA através do Prompt de comando
- Alterar memória cache
- Backup e Restauração da Base de Dados
- Conectando com ISQL
- Configurar base Firebird no IBExpert
- Consultar versão do banco
- Melhorar desempenho
- Resolução do erro "connection lost to database"
- Sobre os modos de Instalação
- Coalesce
- Criação de View
- Operações Join
- Order by e Having
- Pacote de Funções DOX (Package) - Firebird
- Procedure - Data Hora Útil Futuro - Adicionar minutos
- Procedure - Primeira letra da palavra com letra maiúscula (Initcap)
- Procedure - Retorna data futura ao informar número de dias úteis
- Procedure - Retorna datas de um intervalo de dias
- Procedure - Retorna lista
- Procedure - Retorna Meses Sequência
- Procedure - Retorna número ordinal por extenso
- Procedure - Retorna tempo (dias/horas/minutos/segundos) entre datas (dias uteis)
- Procedure - Retorna último dia do mês
- Procedure - Retorna valor por extenso
- Retorna primeiro e último dia do mês
- Retorna tamanho de consumo do campo da tabela
- Union e Union All
- Utilização de Data e Conversões (Current_date, timestamp, cast, etc)
Instalação / Configuração
Instalação do banco de dados Firebird, configuração da base de dados, ferramenta de banco, backup, etc...
Alguns comandos Gfix
Verificar se a base de dados está com corrupção
gfix -v -full -user SYSDBA -password senha_banco caminho_para_o_banco_de_dados
Corrigir a corrupção na base de dados
gfix -mend -user SYSDBA -password senha_banco caminho_para_o_banco_de_dados
Após a conclusão deste item realizar o procedimento 1 novamente, Caso apresentar corrupção efetuar o próximo passo.
Colocar o BD em modo online
gfix -online caminho_base -user nome_usuario -password senha_usuario
**Esses comandos são para situações específicas, é recomendado que seja contatado o TI da empresa.
Alterar a senha do usuário SYSDBA através do Prompt de comando
Alterando a senha do usuário SYSDBA através Prompt de Comandos
- Acessar o Prompt de Comandos no Windows: Iniciar -> executar (Windows+R) -> cmd
- Use o comando cd para ir até o diretório do firebird: "cd C:\Program Files\Firebird\Firebird_3_0"
- Execute a seguinte sintaxe: gsec -user sysdba -pass senha_do_banco
- A seguir, alterar a senha do usuário, usando a seguinte sintaxe: modify sysdba -pw nova_senha*
***Essa alteração só é válida no Ema configurador, ela não altera do seu banco de dados.
Alterar memória cache
Em algumas situações, o uso de memória do firebird fica muito elevado, mesmo o banco de dados sendo pequeno, para isso precisamos limitar o uso de memória cache do mesmo. Aqui, há um passo a passo de como fazer.
- No servidor, acesse a pasta de configuração do firebird (C:\Program Files\Firebird\Firebird_3_0).
- Localize o arquivo firebird.conf e edite o mesmo. (Você precisará conceder permissão de administrador para este arquivo).
- Busque pelo parâmetro FileSystemCacheSize.
- Altere para FileSystemCacheSize = 20, remova o carácter '#' da linha.
- Pare e inicie novamente os serviços do firebird.
Backup e Restauração da Base de Dados
Exportação da Base de Dados
- Para acessar o prompt de comando do windows, basta usar o atalho (windows + R) e digitar 'cmd.exe'.
- Assim que o abrir, siga até o diretório do Firebird. Use o comando 'cd' e inclua o diretório em seguida: cd C:\Program Files\Firebird\Firebird_3_0\
- Dentro do diretório do Firebird, você precisa usar a seguinte sintaxe:
gbak -V -B "caminho_origem_banco.fdb" -user sysdba -password senha_banco "caminho_arquivo_destino.fbk"
**Para a sintaxe funcionar corretamente, você deve informar o caminho e o arquivo na parte 'caminho_origem_banco.fdb', exemplo:
**Em seguida, altere a 'senha_banco' para a sua senha padrão do Firebird.
**O 'caminho_arquivo_destino.fbk' é o caminho onde deve ser inserida a exportação, junto com o nome que ela receberá.'
**O teste foi feito no mesmo caminho que o arquivo origem, mas pode ser exportado para onde desejar.
Restauração da Base de Dados
- Para acessar o prompt de comando do windows, basta usar o atalho (windows + R) e digitar 'cmd.exe'.
- Assim que o abrir, siga até o diretório do Firebird. Use o comando 'cd' e inclua o diretório em seguida: cd C:\Program Files\Firebird\Firebird_3_0\
- Dentro do diretório do Firebird, utilizar a seguinte sintaxe para realização do backup:
gbak -V -R "Arquivo_origem.fbk" -user sysdba -password senha_banco "arquivo_destino.fdb"
**Toda formatação e o passo a passo deve ser efetuado igual a exportação, muda apenas o comando final. O arquivo origem também é o caminho junto do nome do arquivo que deve ser feito backup, e o destino é o nome onde esse backup irá cair.
Conectando com ISQL
Essa ferramenta fica no diretório raiz de instalação do Firebird na pasta bin, o nome do aplicativo é “isql” , com ela podemos executar consultas, inserções, updates e exclusões contra uma base de dados.
- O ISQL é um utilitário de linha de comando disponível no diretório da instalação do Firebird (Win e Linux).
- NOTA, para realização deste processo é indicado que você faça uma cópia de segurança da base.
- Abra o CMD e vá até o diretório bin do Firebird e digite: isql.exe;
- Será apresentado o Prompt do FB. Efetue a conexão com o BD:
- SQL> connect 'c:\teste.fdb' user 'sysdba' password 'masterkey';
- *Se a conexão for estabelecida o cursor irá para a próxima linha, sem exibir alguma mensagem. Caso contrário será exibida uma mensagem de erro.
Configurar base Firebird no IBExpert
- Baixe, descompacte e abra a aplicação IBExpert.
- Vá em Database > Register Database. (Ou Shift+Alt+R)
- Ao clicar ele irá para a seguinte tela:
Preencha as informações de acordo com as instruções abaixo:
- Server:
- Local: Se o banco está instalado no servidor local.
- Remote: Se o banco está instalado em outro servidor da rede.
- Server name: IP do servidor onde está instalado o banco de dados.
- Protocol: Protocolo utilizado. Normalmente: TCP/IP.
- Server version: Versão do Firebird que está instalado.
- Database File: Caminho do arquivo de banco de dados que será usado.
- Database Alias: Nome para visualização da base na barra de ferramentas.
- User name: Usuário do banco de dados.
- Password: Senha do usuário do banco de dados.
O restante dos campos deixar em branco e clicar em “Test Connect, que a imagem a seguir será mostrada
- Clicar em Cancel.
- Acessar a base no canto superior esquerdo e pronto, está configurada a ligação da sua base de dados com o banco Firebird.
Consultar versão do banco
Este tópico informa como você pode consultar a versão do Firebird através do Prompt de comando do seu computador.
- Abrir o Prompt de comando (cmd) e usar os comandos a seguir:
- cd C:\Program Files\Firebird\Firebird_3_0
- gsec -user sysdba -pass senha_banco
- Digite 'z' e pressione o enter.
Não esqueça de alterar a senha para a sua padrão, em seguida você dá enter, e na próxima linha digita 'z'. A mensagem aparecerá em seguida.
Melhorar desempenho
As ações abaixo foram comprovadas diretamente em cliente, porém não temos garantia do eficácia em todas as situações.
- Efetuar backup do banco.
- Efetuar restore do banco.
- No ibexpert, com o botão direto em cima do registro do banco de dados, efetua na sequencia:
- Recompute selectivity of all indices
- Recompile all stored procedures
- Recompile all triggers
- gfix -sweep localhost:c:\dados\meubanco.fdb -user sysdba -password senhabanco
- Pare/Inicie o serviço do firebird
- Considere avaliar este link.
Resolução do erro "connection lost to database"
Caso a conexão com o banco de dados venha cair, após um determinado número de conexões, ou simplesmente após a segunda conexão do banco dados, emitindo a mensagem de erro "connection lost to database", é necessário executar o seguinte comando no Prompt do Windows:
Gfix –o multi ip_servidor:caminho_bd –user sysdba –pass senha
Nota, Você pode também usar o ibexpert e usar a função:
**Essa é apenas uma das alternativas, é recomendado que caso aconteça esse tipo de erro, seja contatado o TI para resolver.
Sobre os modos de Instalação
O Firebird possui alguns modos de instalação, de acordo com o aprendizado em clientes faz-se necessário avaliar os benefícios de cada modo para resolver alguns incidentes já catalogados.
Processos
- Classic Server usa um processo separado para cada conexão.
- Super Classic e o Super Server usam apenas um processo.
Portanto, se um processo “quebrar”, durante sua execução, no Classic, as outras conexões não serão afetadas. No Super Classic e no Super Server, um processo “quebrado” pode trazer abaixo todas as conexões.
Exemplo do arquivo firebird.conf
- # Controls the method Firebird engine uses to work with databases and
- # related Firebird server startup parameters.
- # The values are:
- # Super / ThreadedDedicated - databases are opened exclusive by single server process,
- #attachments share single DB pages cache inside process
- # SuperClassic / ThreadedShared - databases are opened by single server process,
- #but it does not prevent opening them in other processes (embedded access),
- #each attachment has its own DB pages cache
- # Classic / MultiProcess - for each attachment to server a separate process is started,
- #each database may be opened by multiple processes (including local ones for
Guardian ou guardião
- Super server pode rodar sob o controle do Firebird Guardian, que automaticamente o reinicia no caso de um crash.
- SuperClassic só oferece a opção Guardian para instalações profissionais com Linux.
- Classic, simplesmente, não a oferece.
Uso de recursos do sistema
- Super Classic e o Super Server, são single-process, usam os recursos do sistema com mais eficiência do que o Classic, à medida em que o número de conexões simultâneas cresce.
- Super é o mais eficiente dos 3 porque dispõe, ainda, de um espaço de cache compartilhado.
Conexões simultâneas
- Apenas o Classic e o Super Classic permitem conexões simultâneas ao banco de dados do servidor regular e de um ou dois servidores embarcados.
Multiprocessamento
- No Windows, o padrão do Super Server é usar apenas o primeiro processador ou núcleo (core) em seu computador.
- Para fazê-lo usar a capacidade plena da CPU, é preciso alterar a variável CpuAffinityMask no arquivo de configuração firebird.conf.
- Todos os outros servidores (Classic e Super Classic) (o que inclui o Super Server no Linux), suportam naturalmente o multi processamento e ignoram o valor de CpuAffinityMask.
CpuAffinityMask
Como configurar o Firebird para utilizar mais de um núcleo do processador:
- 3 para 2 CPUs/cores
- 15 para 4 CPUs/cores
- 255 para 8 CPUs/cores
Coalesce
Com a função COALESCE, é possível definir que, quando o campo retornar nulo, outro valor será retornado no lugar.
TABELA EXEMPLO
EX_PRODUTOS
| IDPRODUTO | DESCRICAO | VALOR_CUSTO | VALOR_VENDA |
-----------------------------------------------------------
| 1 | 'Arroz 1KG' | 0.75 | (null) |
| 2 | 'Feijão 1KG' | 1.07 | 2.99 |
| 3 | 'Macarrão 500G' | (null) | (null) |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_produtos (
idproduto INTEGER,
descricao VARCHAR(200),
valor_custo DECIMAL(15,2),
valor_venda DECIMAL(15,2));
INSERT INTO ex_produtos VALUES (1, 'Arroz 1KG', 0.75, NULL);
INSERT INTO ex_produtos VALUES (2, 'Feijão 1KG', 1.07, 2.99);
INSERT INTO ex_produtos VALUES (3, 'Macarrão 500G', NULL, NULL);
COALESCE
A função COALESCE retorna o valor do parâmetro seguinte quando o anterior ser nulo. Pode ser usado quantos parâmetros de entrada for necessário.
SELECT DESCRICAO, COALESCE(VALOR_VENDA, VALOR_CUSTO, 0) AS VALOR FROM EX_PRODUTOS
Retorno:
| DESCRICAO | VALOR |
---------------------------
| 'Arroz 1KG' | 0.75 |
| 'Feijão 1KG' | 2.99 |
| 'Macarrão 500G' | 0 |
Criação de View
A view é útil para ter uma consulta pré-organizada que é executada frequentemente.
Exemplo
Neste exemplo, temos uma tabela chamada CIDADE com informações de várias cidades de alguns estados, incluindo um campo que informa se o registro está inativo.
| IDCIDADE | DESCRICAO | UF | INATIVO |
----------------------------------------------
| 0 | Desconhecido | SC | S |
| 1 | Criciúma | SC | N |
| 2 | São Paulo | SP | N |
| 3 | Balneário Rincão | SC | N |
| 4 | Porto Alegre | RS | N |
| 5 | Araranguá | SC | N |
| 6 | Içara | SC | N |
| 7 | Curitiba | PR | N |
| 8 | Torres | RS | N |
| 9 | Florianópolis | SC | N |
Precisamos que na consulta, seja mostrado apenas cidades que não estão inativas com o código e a descrição concatenada com a UF (Ex: Florianópolis - SC). Além disso, só mostrar as cidades do estado SC e ordenado pela descrição.
CREATE OR ALTER VIEW VW_CIDADE_SC AS
SELECT IDCIDADE AS CODIGO,
DESCRICAO||' - '||UF AS DESCRICAO
FROM CIDADE
WHERE UF = 'SC'
AND INATIVO = 'N'
ORDER BY DESCRICAO
Chamada da view:
SELECT *
FROM vw_cidade_sc
Retorno:
| CODIGO | DESCRICAO |
----------------------------------
| 5 | Araranguá - SC |
| 3 | Balneário Rincão - SC |
| 1 | Criciúma - SC |
| 9 | Florianópolis - SC |
| 6 | Içara - SC |
Operações Join
Usamos as operações JOIN para relacionar dados de duas ou mais tabelas em uma consulta, utilizando igualdade de colunas em comum ou não.
Será mostrado um exemplo de situação onde pode ser usado os diferentes tipos de JOIN e seus resultados entre duas tabelas.
TABELAS EXEMPLO
EX_ESTADO
| UF | DESCRICAO |
-----------------------
| SC | Santa Catarina |
| SP | São Paulo |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_estado (uf VARCHAR(2), descricao VARCHAR(100));
INSERT INTO ex_estado VALUES ('SC', 'Santa Catarina');
INSERT INTO ex_estado VALUES ('SP', 'São Paulo');
EX_CIDADE
| IDCIDADE | NOME | UF |
---------------------------------
| 1 | Criciúma | SC |
| 2 | Florianópolis | SC |
| 3 | Curitiba | PR |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_cidade (idcidade INTEGER, nome VARCHAR(200), uf VARCHAR(2));
INSERT INTO ex_cidade VALUES (1, 'Criciúma', 'SC');
INSERT INTO ex_cidade VALUES (2, 'Florianópolis', 'SC');
INSERT INTO ex_cidade VALUES (3, 'Curitiba', 'PR');
INNER JOIN
É usado para relacionar e mostrar os dados de ambas as tabelas quando houver igualdade nos campos em comum.]
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
INNER JOIN EX_CIDADE CI ON ES.UF = CI.UF
Usos alternativos que retornam o mesmo resultado:
Não é necessário informar a operação INNER.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
JOIN EX_CIDADE CI ON ES.UF = CI.UF
Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES NATURAL JOIN EX_CIDADE CI
Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
JOIN EX_CIDADE CI USING (UF)
Utilizando com a cláusula WHERE.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES, EX_CIDADE CI
WHERE ES.UF = CI.UF
Retorno:
| ESTADO | CIDADE |
----------------------------------
| Santa Catarina | Criciúma |
| Santa Catarina | Florianópolis |
LEFT OUTER JOIN
É usado para relacionar e mostrar os dados de ambas as tabelas, preservando somente os dados da primeira tabela mesmo que não haja igualdade nos campos em comum.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
LEFT OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF
Usos alternativos que retornam o mesmo resultado:
Não é necessário informar a operação OUTER.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES LEFT JOIN EX_CIDADE CI ON ES.UF = CI.UF
Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES NATURAL LEFT JOIN EX_CIDADE CI
Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES LEFT JOIN EX_CIDADE CI USING (UF)
Retorno:
| ESTADO | CIDADE |
----------------------------------
| Santa Catarina | Criciúma |
| Santa Catarina | Florianópolis |
| São Paulo | (null) |
RIGHT OUTER JOIN
É usado para relacionar e mostrar os dados de ambas as tabelas, preservando somente os dados da segunda tabela mesmo que não haja igualdade nos campos em comum.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES RIGHT OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF
Usos alternativos que retornam o mesmo resultado:
Não é necessário informar a operação OUTER.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES RIGHT JOIN EX_CIDADE CI ON ES.UF = CI.UF
Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES NATURAL RIGHT JOIN EX_CIDADE CI
Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES RIGHT JOIN EX_CIDADE CI USING (UF)
Retorno:
| ESTADO | CIDADE |
----------------------------------
| Santa Catarina | Florianópolis |
| Santa Catarina | Criciúma |
| (null) | Curitiba |
FULL OUTER JOIN
É usado para relacionar e mostrar os dados de ambas as tabelas, preservando os dados mesmo que não haja igualdade nos campos em comum.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES FULL OUTER JOIN EX_CIDADE CI ON ES.UF = CI.UF
Usos alternativos que retornam o mesmo resultado:
Não é necessário informar a operação OUTER.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES FULL JOIN EX_CIDADE CI ON ES.UF = CI.UF
Utilizando com a operação NATURAL. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES NATURAL FULL JOIN EX_CIDADE CI
Utilizando com a cláusula USING. É necessário que as colunas em comum tenham o mesmo nome em ambas as tabelas
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
FULL JOIN EX_CIDADE CI USING (UF)
Retorno:
| ESTADO | CIDADE |
----------------------------------
| Santa Catarina | Criciúma |
| Santa Catarina | Florianópolis |
| (null) | Curitiba |
| São Paulo | (null) |
CROSS JOIN
É usado para mostrar os dados de ambas as tabelas sem relacionar por campos em comum. Essa operação relaciona todos registros de uma tabela com todos registros da outra tabela.
SELECT ES.DESCRICAO AS ESTADO,
CI.NOME AS CIDADE
FROM EX_ESTADO ES
CROSS JOIN EX_CIDADE CI
Retorno:
| ESTADO | CIDADE |
----------------------------------
| Santa Catarina | Criciúma |
| Santa Catarina | Florianópolis |
| Santa Catarina | Curitiba |
| São Paulo | Criciúma |
| São Paulo | Florianópolis |
| São Paulo | Curitiba |
Order by e Having
Com os operadores ORDER BY, GROUP BY e HAVING podemos organizar nossas consultas mais dinamicamente.
Tabela Exemplo
EX_ITENS
| CODIGO | DESCRICAO | TIPO | QUANTIDADE |
---------------------------------------------------
| 1 | 'Biscoito' | 'Comida' | 20 |
| 2 | 'Água' | 'Bebida' | 47,95 |
| 3 | 'Suco' | 'Bebida' | 23,4 |
| 4 | 'Pão' | 'Comida' | 10 |
| 5 | 'Refrigerante' | 'Bebida' | 13,75 |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_itens (
codigo INTEGER,
descricao VARCHAR(200),
tipo VARCHAR(20),
quantidade DECIMAL(15,2));
INSERT INTO ex_itens VALUES (1, 'Biscoito', 'Comida', 20);
INSERT INTO ex_itens VALUES (2, 'Água', 'Bebida', 47.95);
INSERT INTO ex_itens VALUES (3, 'Suco', 'Bebida', 23.4);
INSERT INTO ex_itens VALUES (4, 'Pão', 'Comida', 10);
INSERT INTO ex_itens VALUES (5, 'Refrigerante', 'Bebida', 13.75);
ORDER BY
Ordenar crescente pela coluna TIPO.
SELECT *
FROM ex_itens
ORDER BY tipo
Retorno:
| CODIGO | DESCRICAO | TIPO | QUANTIDADE |
---------------------------------------------------
| 5 | 'Refrigerante' | 'Bebida' | 13,75 |
| 3 | 'Suco' | 'Bebida' | 23,4 |
| 2 | 'Água' | 'Bebida' | 47,95 |
| 4 | 'Pão' | 'Comida' | 10 |
| 1 | 'Biscoito' | 'Comida' | 20 |
Ordenar crescente pela coluna TIPO e decrescente pela coluna QUANTIDADE. (a ordenação respeita a sequência inserida)
SELECT *
FROM ex_itens
ORDER BY tipo, quantidade desc
Retorno:
| CODIGO | DESCRICAO | TIPO | QUANTIDADE |
---------------------------------------------------
| 2 | 'Água' | 'Bebida' | 47,95 |
| 3 | 'Suco' | 'Bebida' | 23,4 |
| 5 | 'Refrigerante' | 'Bebida' | 13,75 |
| 1 | 'Biscoito' | 'Comida' | 20 |
| 4 | 'Pão' | 'Comida' | 10 |
GROUP BY
Agrupar dados pela coluna TIPO contando a quantidade de itens (linhas) e volumes (coluna QUANTIDADE) para cada tipo de item.
SELECT TIPO, COUNT(1) AS QTD_ITEM, SUM(QUANTIDADE) AS QTD_VOLUME
FROM EX_ITENS
GROUP BY TIPO
Retorno:
| TIPO | QTD_ITEM | QTD_VOLUME |
------------------------------------
| 'Bebida' | 3 | 85,1 |
| 'Comida' | 2 | 30 |
HAVING
Agrupar pela coluna TIPO mostrando somente quando a soma da coluna QUANTIDADE é menor do que 40.
SELECT TIPO, SUM(QUANTIDADE) AS QTD_VOLUME
FROM EX_ITENS
GROUP BY TIPO HAVING SUM(QUANTIDADE) < 40
Retorno:
| TIPO | QTD_VOLUME |
-------------------------
| 'Comida' | 30 |
Agrupar pela coluna TIPO mostrando somente quando a contagens de linhas é maior ou igual à 3.
SELECT TIPO, COUNT(1) AS QTD_ITEM
FROM EX_ITENS
GROUP BY TIPO HAVING COUNT(1) >= 3
Retorno:
| TIPO | QTD_ITEM |
-----------------------
| 'Bebida' | 3 |
Pacote de Funções DOX (Package) - Firebird
Pensando em facilitar a busca de informações dentro do software por meio de instruções SQL, ao longo do tempo foram criadas várias funções e procedures que simplificaram ainda mais a busca de informações, seja por nossos consultores internos, ou por nossos clientes em suas automações.
A Ema disponibiliza um Pacote para criar no banco de dados que executará todas as funções e procedures instantaneamente e de uma só vez.
Para isso, basta executar os scripts abaixo para o banco especificado:
-
Primeiramente, executar a criação da package
create or alter package pkg_ema as
begin
--variaveis
procedure retorna_valor_variavel(xidprocesso integer, xidvariavel integer) returns (valor varchar(4000));
procedure retorna_valor_variavel_texto(xidprocesso integer, xidvariavel integer) returns (valor varchar(4000));
procedure retorna_valor_variavel_int(xidprocesso integer, xidvariavel integer) returns (valor numeric(15,0));
procedure retorna_valor_variavel_valor(xidprocesso integer, xidvariavel integer) returns (valor numeric(15,2));
procedure retorna_valor_variavel_data(xidprocesso integer, xidvariavel integer) returns (valor date);
procedure retorna_valor_variavel_datahr(xidprocesso integer, xidvariavel integer) returns (valor timestamp);
--grade de dados
procedure retorna_coluna_grade(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura varchar(2000));
procedure retorna_coluna_grade_texto(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura varchar(2000));
procedure retorna_coluna_grade_int(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura numeric(15,0));
procedure retorna_coluna_grade_valor(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura numeric(15,2));
procedure retorna_coluna_grade_data(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura date);
procedure retorna_coluna_grade_datahr(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura timestamp);
end
-
Em seguida, executar a criação do body
recreate package body pkg_ema as
begin
--variaveis
procedure retorna_valor_variavel(xidprocesso integer, xidvariavel integer) returns (valor varchar(4000))
as
begin
select valoratual
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
procedure retorna_valor_variavel_texto(xidprocesso integer, xidvariavel integer) returns (valor varchar(4000))
as
begin
select valoratual--decode(upper(valoratual),'.','',replace(upper(valoratual),',','.'))
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
procedure retorna_valor_variavel_int(xidprocesso integer, xidvariavel integer) returns (valor numeric(15,0))
as
begin
select cast(decode(valoratual,'.','0',valoratual) as numeric(15))
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
procedure retorna_valor_variavel_valor(xidprocesso integer, xidvariavel integer) returns (valor numeric(15,2))
as
begin
select cast(decode(valoratual,'.','0',valoratual) as numeric(15,2))
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
procedure retorna_valor_variavel_data(xidprocesso integer, xidvariavel integer) returns (valor date)
as
begin
select cast(replace(decode(valoratual,'.','01/01/1900',substring(valoratual from 1 for 10)),'/','.') as date )
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
procedure retorna_valor_variavel_datahr(xidprocesso integer, xidvariavel integer) returns (valor timestamp)
as
begin
select cast(replace(decode(valoratual,'.','01/01/1900',substring(valoratual from 1 for 16)),'/','.') as timestamp)
from crm_processo_variavel
where crm_processo_variavel.idprocesso = :xidprocesso
and crm_processo_variavel.idvariavel = :xidvariavel
into :valor;
suspend;
end
--grade de dados
procedure retorna_coluna_grade(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura varchar(2000))
as
begin
select resposta
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
procedure retorna_coluna_grade_texto(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura varchar(2000))
as
begin
select decode(resposta,'.','',resposta)
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
procedure retorna_coluna_grade_int(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura numeric(15,0))
as
begin
select respostainteiro
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
procedure retorna_coluna_grade_valor(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura numeric(15,2))
as
begin
select cast(replace(replace(resposta,'.',''),',','.') as decimal(15,2))
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
procedure retorna_coluna_grade_data(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura date)
as
begin
select respostadatahora
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
procedure retorna_coluna_grade_datahr(xidprocesso integer, xidatividade integer, xidformulario integer, xidgrade integer, xidvalor integer) returns (leitura timestamp)
as
begin
select respostadatahora
from crm_processo_grade_valor
where idprocesso = :xidprocesso and
idformulario = :xidformulario and
idatividade = :xidatividade and
idgrade = :xidgrade and
idvalor = :xidvalor and
idrepeticao = (select max(idrepeticao)
from crm_processo_grade_valor xx
where xx.idatividade = crm_processo_grade_valor.idatividade
and xx.idprocesso = crm_processo_grade_valor.idprocesso)
into leitura;
suspend;
end
end
Chamando as Funções
-
Variável:
select (select * from pkg_ema.retorna_valor_variavel(idprocesso, 1)) as padrao,
(select * from pkg_ema.retorna_valor_variavel_texto(idprocesso, 2)) as texto,
(select * from pkg_ema.retorna_valor_variavel_int(idprocesso, 3)) as inteiro,
(select * from pkg_ema.retorna_valor_variavel_valor(idprocesso, 4)) as valor,
(select * from pkg_ema.retorna_valor_variavel_data(idprocesso, 5)) as data,
(select * from pkg_ema.retorna_valor_variavel_datahr(idprocesso, 6)) as datahr
from crm_processo where idprocesso = 0/*IDPROCESSO*/
-
Grade:
select (select * from pkg_ema.retorna_coluna_grade(x.idprocesso, x.idatividade, x.idformulario, 1, x.idvalor)) as padrao,
(select * from pkg_ema.retorna_coluna_grade_texto(x.idprocesso, x.idatividade, x.idformulario, 2, x.idvalor)) as texto,
(select * from pkg_ema.retorna_coluna_grade_int(x.idprocesso, x.idatividade, x.idformulario, 3, x.idvalor)) as inteiro,
(select * from pkg_ema.retorna_coluna_grade_valor(x.idprocesso, x.idatividade, x.idformulario, 4, x.idvalor)) as valor,
(select * from pkg_ema.retorna_coluna_grade_data(x.idprocesso, x.idatividade, x.idformulario, 5, x.idvalor)) as data,
(select * from pkg_ema.retorna_coluna_grade_datahr(x.idprocesso, x.idatividade, x.idformulario, 6, x.idvalor)) as datahr
from crm_processo_grade_valor x
where idprocesso = 0/*IDPROCESSO*/ and idatividade = 0/*IDATIVIDADE*/ and idformulario = 0/*IDFORMULARIO*/
and idrepeticao = (select max(idrepeticao) from crm_processo_grade_valor where idprocesso = x.idprocesso and idatividade = x.idatividade)
and idgrade = 1 order by idvalor
Procedure - Data Hora Útil Futuro - Adicionar minutos
SQL:
CREATE OR ALTER PROCEDURE ADD_HORA_UTIL (ADDMINUTOS integer, UTIL_INI time, UTIL_FIM time)
RETURNS (datahora timestamp)
as
declare variable minutos integer;
declare variable atualtime time;
BEGIN
datahora=current_timestamp;
minutos = 0;
while (minutos<=addminutos) do
begin
if ((extract(weekday from datahora) not in (0,6)) and (extract(hour from datahora)<>12)) then
begin
datahora = datahora + 0.000695; /* aprox. 1 min */
atualtime= extract(hour from datahora)||':'||extract(minute from datahora)||':'||extract(second from datahora);
if ((atualtime <= :util_fim) and (atualtime >= :util_ini)) then
begin
minutos = minutos + 1;
end
end
else
begin
datahora = datahora + 0.000695; /* aprox. 1 min */
end
end
suspend;
END
A procedure esta sendo compartilhada e possui o funcionamento de adicionar minutos a data hora ATUAL, neste caso, sempre sobre o current_timestamp, a mesma desconsidera sábados e domingos além de não considerar os minutos das 12:00 as 12:59;
Essas definições básicas podem ser facilmente personalizadas antes da criação da procedure.
Parâmetros da procedure:
Entrada:
- ADDMINUTOS(integer) = Número inteiro de minutos a serem adicionados.
- UTIL_INI (TIME) = Hora útil inicial - Exemplo: '08:00:00'
- UTIL_FIM (TIME) = Hora útil final - Exemplo: '18:00:00'
Saída
- DATAHORA = Data atual incrementada do numero de minutos uteis solicitados.
Chamada da procedure:
SELECT *
FROM ADD_HORA_UTIL(180, '08:00:00', '18:00:00') -- Adiciona 3 hs a data hora atual.
Retorno:
26/12/2019 15:23:57
Procedure - Primeira letra da palavra com letra maiúscula (Initcap)
SQL:
create or alter procedure INITCAP (ENTRADA varchar(4000)) returns (SAIDA varchar(4000)) as
declare variable LI_TAMANHO integer;
declare variable I integer;
begin
I = 1;
LI_TAMANHO = char_length(:ENTRADA);
ENTRADA = lower(ENTRADA);
while (I <= LI_TAMANHO) do
begin
if (I = 1) then
begin
SAIDA = upper(substring(ENTRADA from I for 1)) || substring(ENTRADA from I + 1 for LI_TAMANHO);
end
else
begin
if (substring(ENTRADA from I for 1) = ' ') then
begin
SAIDA = substring(SAIDA from 1 for I) || upper(substring(ENTRADA from I + 1 for 1)) || substring(ENTRADA from I + 2 for LI_TAMANHO);
end
end
I = I + 1;
end
suspend;
end;
Chamada da procedure:
select * from INITCAP('nome sobrenome')
Retorno:
Nome Sobrenome
Procedure - Retorna data futura ao informar número de dias úteis
SQL:
CREATE OR ALTER PROCEDURE RETORNA_SOMA_DIAS_UTEIS (XINICIO VARCHAR(20), XNDIA INTEGER)
RETURNS (RETORNO VARCHAR(20))
AS
declare variable i integer;
DECLARE variable DATA date;
BEGIN
i = 0;
XINICIO = replace(XINICIO,'/','.');
XINICIO = replace(XINICIO,'-','.');
DATA = CAST(XINICIO AS DATE);
WHILE (i < XNDIA) DO
BEGIN
DATA = DATA +1;
IF (EXTRACT(WEEKDAY FROM DATA) NOT IN (0,6)) THEN i = i + 1;
END
RETORNO = DATA;
suspend;
END
Parâmetros da procedure:
Entrada:
- XINICIO(varchar(20)) = Data inicial para cálculo dos dias. Exemplo: '28/11/2019'
- XNDIA (integer) = Numero de dias uteis a serem incrementados na data. Exemplo: 2
***OBSERVAÇÕES: Existe um tratamento para aceite da XINICIO com separadores ponto, traço ou barra. A função não está preparada para considerar feriados.
Saída
- RETORNO = Data incrementada do numero de dias uteis informados.
Chamada da procedure:
SELECT RETORNO
FROM DOX_RETORNA_DATA_DIAUTIL('28.11.2019', 2)
Retorno:
2019-12-02
Explicação do retorno:
Ao informar uma data (Exemplo '28.11'2019') a função irá somar o numero de dias uteis informados, ou seja, irá desconsiderar os sábados e domingos para definição da data futura.
Detalhamento do resultado:
- 28.11.2019 - Data inicial - Sempre será desconsiderada, faremos o incremento sempre a partir do próximo dia.
- 29.11.2019 - Houve o primeiro incremento, ou seja, considera-se o primeiro dia útil - Sexta-feira
- 30.11.2019 - Data ignorada - Não houve incremento - Sábado
- 01.12.2019 - Data ignorada - Não houve incremento - Domingo
- 02.12.2019 - Houve o segundo incremento, ou seja, considera-se o segundo dia útil - Segunda-feira
- - Como informamos XNDIA como 2, o retorno será 2019-12-02 - 2 dias úteis após a XINICIO
Outro exemplo de uso:
SELECT RETORNO
FROM DOX_RETORNA_DATA_DIAUTIL(current_date, 15)
Procedure - Retorna datas de um intervalo de dias
SQL:
create or alter procedure RETORNA_LISTA_DIAS (XINICIO date, XFIM date) returns (DATA data_do) as
declare variable DIFERENCA integer;
declare variable I integer;
begin
i = 0;
select :XFIM - :XINICIO from rdb$database into :DIFERENCA;
while (i <= :DIFERENCA) DO
begin
data = :xinicio + i;
i = i +1;
suspend;
end
end
Chamada da procedure:
SELECT *
FROM RETORNA_LISTA_DIAS('2019.07.12', current_date)
Retorno:
| DATA |
--------------
| 12.07.2019 |
| 13.07.2019 |
| 14.07.2019 |
| 15.07.2019 |
| 16.07.2019 |
Procedure - Retorna lista
SQL:
create or alter procedure retorna_lista (texto blob, delimitador varchar(20)) returns (lista varchar(4000)) as
declare variable posicao integer = 1;
declare variable prox_pos integer;
declare variable tamanho integer;
begin
tamanho = char_length(:texto);
while (posicao <= tamanho) do
begin
prox_pos = position(delimitador, :texto, :posicao);
if (:prox_pos = 0) then
prox_pos = tamanho+1;
lista = trim(substring(:texto from :posicao for (:prox_pos - :posicao)));
suspend;
posicao = prox_pos+1;
end
end
Chamada da procedure:
SELECT *
FROM retorna_lista('1,2,3', ',')
Retorno:
| LISTA |
---------
| 1 |
| 2 |
| 3 |
Procedure - Retorna Meses Sequência
Estaremos compartilhando com vocês uma procedure que retorna de forma sequencial os meses, esta procedure permite definirmos se queremos meses abreviados ou não. Exemplo: Jan ou Janeiro, nos permite definir um numero de meses a retornar, e o numero de meses que serão exibidos:
create or alter procedure RETORNA_MESES_SEQUENCIA (
ABREVIAR varchar(1) not null,
RECUAR integer,
NUMEROMESES integer not null)
returns (
IDMES INTEGER,
MES varchar(20),
ANO integer)
as
declare variable COMPARAMES integer;
declare variable CONTADOR integer;
declare variable DATAINICIO date;
begin
DATAINICIO = dateadd(RECUAR month to current_date);
CONTADOR = 0;
while (NUMEROMESES > 0) do
begin
COMPARAMES = extract(month from (dateadd(CONTADOR month to DATAINICIO)));
ANO = extract(year from (dateadd(CONTADOR month to DATAINICIO)));
IDMES = COMPARAMES;
MES = case
when COMPARAMES = 1 and ABREVIAR = 'S' then 'Jan'
when COMPARAMES = 2 and ABREVIAR = 'S' then 'Fev'
when COMPARAMES = 3 and ABREVIAR = 'S' then 'Mar'
when COMPARAMES = 4 and ABREVIAR = 'S' then 'Abr'
when COMPARAMES = 5 and ABREVIAR = 'S' then 'Mai'
when COMPARAMES = 6 and ABREVIAR = 'S' then 'Jun'
when COMPARAMES = 7 and ABREVIAR = 'S' then 'Jul'
when COMPARAMES = 8 and ABREVIAR = 'S' then 'Ago'
when COMPARAMES = 9 and ABREVIAR = 'S' then 'Set'
when COMPARAMES = 10 and ABREVIAR = 'S' then 'Out'
when COMPARAMES = 11 and ABREVIAR = 'S' then 'Nov'
when COMPARAMES = 12 and ABREVIAR = 'S' then 'Dez'
when COMPARAMES = 1 and ABREVIAR = 'N' then 'Janeiro'
when COMPARAMES = 2 and ABREVIAR = 'N' then 'Fevereiro'
when COMPARAMES = 3 and ABREVIAR = 'N' then 'Mar?o'
when COMPARAMES = 4 and ABREVIAR = 'N' then 'Abril'
when COMPARAMES = 5 and ABREVIAR = 'N' then 'Maio'
when COMPARAMES = 6 and ABREVIAR = 'N' then 'Junho'
when COMPARAMES = 7 and ABREVIAR = 'N' then 'Julho'
when COMPARAMES = 8 and ABREVIAR = 'N' then 'Agosto'
when COMPARAMES = 9 and ABREVIAR = 'N' then 'Setembro'
when COMPARAMES = 10 and ABREVIAR = 'N' then 'Outubro'
when COMPARAMES = 11 and ABREVIAR = 'N' then 'Novembro'
when COMPARAMES = 12 and ABREVIAR = 'N' then 'Dezembro'
end;
CONTADOR = CONTADOR + 1;
NUMEROMESES = NUMEROMESES - 1;
suspend;
end
end
CÓDIGO ATUALIZADO POR ARTUR SILVA EM 15/05/2020: Adicionado mais dois campos no retorno, IDMES e ANO.
Abaixo exemplo de uso/resultado:
SELECT MES
FROM RETORNA_MESES_SEQUENCIA ('N', -3, 6)
No exemplo acima, estou fazendo a chamada para não abreviar, voltar 3 meses e trazer um total de 6 meses, abaixo o resultado obtido: (Executado 19/02/2020)
Resultado:
---- Novembro
---- Dezembro
---- Janeiro
---- Fevereiro
---- Março
---- Abril
Exemplo de uso abreviado:
SELECT MES
FROM RETORNA_MESES_SEQUENCIA ('S', -3, 6)
No exemplo acima, estou fazendo a chamada para abreviar, voltar 3 meses e trazer um total de 6 meses, abaixo o resultado obtido: (Executado 19/02/2020)
Resultado:
---- Nov
---- Dez
---- Jan
---- Fev
---- Mar
---- Abr
Exemplo de uso sem voltar:
SELECT MES
FROM RETORNA_MESES_SEQUENCIA ('N', 0, 6)
No exemplo acima, estou fazendo a chamada para não abreviar, não voltar e não incrementar meses e trazer um total de 6 meses, abaixo o resultado obtido: (Executado 19/02/2020)
Resultado:
---- Fevereiro
---- Março
---- Abril
---- Maio
---- Junho
---- Julho
Exemplo de uso com incremento de mês:
SELECT MES
FROM RETORNA_MESES_SEQUENCIA ('N', 2, 6)
No exemplo acima, estou fazendo a chamada para não abreviar, avançar 2 meses e trazer um total de 6 meses, abaixo o resultado obtido: (Executado 19/02/2020)
Resultado:
---- Abril
---- Maio
---- Junho
---- Julho
---- Agosto
---- Setembro
Exemplo de uso com incremento de mês:
SELECT IDMES,MES,ANO, IDMES || '/' || ANO AS MESANO
FROM RETORNA_MESES_SEQUENCIA ('S', -12, 13)
ORDER BY ANO, IDMES;
No exemplo acima, estou fazendo a chamada para buscar os últimos 12 meses (a partir da data atual), conforme resultado abaixo:
Resultado:
IDMES|MES |ANO |MESANO |
-----|---------|----|-------|
5|Mai |2019|5/2019 |
6|Jun |2019|6/2019 |
7|Jul |2019|7/2019 |
8|Ago |2019|8/2019 |
9|Set |2019|9/2019 |
10|Out |2019|10/2019|
11|Nov |2019|11/2019|
12|Dez |2019|12/2019|
1|Jan |2020|1/2020 |
2|Fev |2020|2/2020 |
3|Mar |2020|3/2020 |
4|Abr |2020|4/2020 |
5|Mai |2020|5/2020 |
Procedure - Retorna número ordinal por extenso
SQL:
create or alter procedure retorna_extenso_ordinais (numero integer) returns (RETORNO varchar(200)) as
begin
select case :numero
when 1 then 'Primeiro'
when 2 then 'Segundo'
when 3 then 'Terceiro'
when 4 then 'Quarto'
when 5 then 'Quinto'
when 6 then 'Sexto'
when 7 then 'Sétimo'
when 8 then 'Oitavo'
when 9 then 'Nono'
when 10 then 'Décimo'
when 11 then 'Décimo primeiro'
when 12 then 'Décimo segundo'
when 13 then 'Décimo terceiro'
when 14 then 'Décimo quarto'
when 15 then 'Décimo quinto'
when 16 then 'Décimo sexto'
when 17 then 'Décimo sétimo'
when 18 then 'Décimo oitavo'
when 19 then 'Décimo nono'
when 20 then 'Vigésimo'
when 21 then 'Vigésimo primeiro'
when 22 then 'Vigésimo segundo'
when 23 then 'Vigésimo terceiro'
when 24 then 'Vigésimo quarto'
when 25 then 'Vigésimo quinto'
when 26 then 'Vigésimo sexto'
when 27 then 'Vigésimo sétimo'
when 28 then 'Vigésimo oitavo'
when 29 then 'Vigésimo nono'
when 30 then 'Trigésimo'
when 31 then 'Trigésimo primeiro'
when 32 then 'Trigésimo segundo'
when 33 then 'Trigésimo terceiro'
when 34 then 'Trigésimo quarto'
when 35 then 'Trigésimo quinto'
when 36 then 'Trigésimo sexto'
when 37 then 'Trigésimo sétimo'
when 38 then 'Trigésimo oitavo'
when 39 then 'Trigésimo nono'
when 40 then 'Quadragésimo'
when 41 then 'Quadragésimo primeiro'
when 42 then 'Quadragésimo segundo'
when 43 then 'Quadragésimo terceiro'
when 44 then 'Quadragésimo quarto'
when 45 then 'Quadragésimo quinto'
when 46 then 'Quadragésimo sexto'
when 47 then 'Quadragésimo sétimo'
when 48 then 'Quadragésimo oitavo'
when 49 then 'Quadragésimo nono'
when 50 then 'Quinquagésimo'
when 51 then 'Quinquagésimo primeiro'
when 52 then 'Quinquagésimo segundo'
when 53 then 'Quinquagésimo terceiro'
when 54 then 'Quinquagésimo quarto'
when 55 then 'Quinquagésimo quinto'
when 56 then 'Quinquagésimo sexto'
when 57 then 'Quinquagésimo sétimo'
when 58 then 'Quinquagésimo oitavo'
when 59 then 'Quinquagésimo nono'
when 60 then 'Sexagésimo'
when 61 then 'Sexagésimo primeiro'
when 62 then 'Sexagésimo segundo'
when 63 then 'Sexagésimo terceiro'
when 64 then 'Sexagésimo quarto'
when 65 then 'Sexagésimo quinto'
when 66 then 'Sexagésimo sexto'
when 67 then 'Sexagésimo sétimo'
when 68 then 'Sexagésimo oitavo'
when 69 then 'Sexagésimo nono'
when 70 then 'Septuagésimo '
when 71 then 'Septuagésimo primeiro'
when 72 then 'Septuagésimo segundo'
when 73 then 'Septuagésimo terceiro'
when 74 then 'Septuagésimo quarto'
when 75 then 'Septuagésimo quinto'
when 76 then 'Septuagésimo sexto'
when 77 then 'Septuagésimo sétimo'
when 78 then 'Septuagésimo oitavo'
when 79 then 'Septuagésimo nono'
when 80 then 'Octogésimo'
when 81 then 'Octogésimo primeiro'
when 82 then 'Octogésimo segundo'
when 83 then 'Octogésimo terceiro'
when 84 then 'Octogésimo quarto'
when 85 then 'Octogésimo quinto'
when 86 then 'Octogésimo sexto'
when 87 then 'Octogésimo sétimo'
when 88 then 'Octogésimo oitavo'
when 89 then 'Octogésimo nono'
when 90 then 'Nonagésimo'
when 91 then 'Nonagésimo primeiro'
when 92 then 'Nonagésimo segundo'
when 93 then 'Nonagésimo terceiro'
when 94 then 'Nonagésimo quarto'
when 95 then 'Nonagésimo quinto'
when 96 then 'Nonagésimo sexto'
when 97 then 'Nonagésimo sétimo'
when 98 then 'Nonagésimo oitavo'
when 99 then 'Nonagésimo nono'
when 100 then 'Centésimo'
end
from rdb$database INTO :RETORNO;
/*
200.º ducentésimo
300.º trecentésimo ou tricentésimo
400.º quadringentésimo
500.º quingentésimo
600.º sexcentésimo ou seiscentésimo
700.º septingentésimo ou setingentésimo
800.º octingentésimo ou octogentésimo
900.º noningentésimo ou nongentésimo
1.000.º milésimo
10.000.º décimo milésimo
100.000.º centésimo milésimo
1.000.000.º milionésimo
1.000.000.000.º bilionésimo
1.000.000.000.000.º trilionésimo
1.000.000.000.000.000.º quatrilionésimo
1.000.000.000.000.000.000.º quintilionésimo
1.000.000.000.000.000.000.000.º Sextilionésimo
1.000.000.000.000.000.000.000.000.º Septilionésimo
1.000.000.000.000.000.000.000.000.000.º Octilionésimo
1.000.000.000.000.000.000.000.000.000.000.º Nonilionésimo
1.000.000.000.000.000.000.000.000.000.000.000.º Decilionésimo*/
suspend;
end
Chamada da procedure:
SELECT *
FROM retorna_extenso_ordinais(23)
Retorno:
Vigésimo terceiro
Procedure - Retorna tempo (dias/horas/minutos/segundos) entre datas (dias uteis)
SQL:
CREATE OR ALTER PROCEDURE RETORNA_TEMPO_DIAS_UTEIS (DATA_INICIO TIMESTAMP, DATA_FIM TIMESTAMP, HR_INICIO_EXPEDIENTE VARCHAR(8), HR_FIM_EXPEDIENTE VARCHAR(8))
RETURNS (
DIAS INTEGER,
HORAS INTEGER,
MINUTOS NUMERIC(15,2),
SEGUNDOS NUMERIC(15,2)
)
AS
declare variable DIFERENCA integer;
declare variable I integer;
declare variable dif integer;
declare variable temp_dt_ini TIMESTAMP;
declare variable temp_dt_fim TIMESTAMP;
begin
i = 0;
DIFERENCA = 0;
DIAS = 0;
HORAS = 0;
MINUTOS = 0;
SEGUNDOS = 0;
dif = 0;
if (:DATA_INICIO is not null and :DATA_FIM is not null and :DATA_INICIO <= :DATA_FIM) THEN
BEGIN
dif = datediff(SECOND from :DATA_INICIO to :DATA_FIM);
if (Extract(Weekday from :DATA_INICIO) IN (1,2,3,4,5) and Extract(Weekday from :DATA_FIM) IN (1,2,3,4,5) AND dif <= 86400) THEN
begin
SEGUNDOS = :SEGUNDOS + :dif;
MINUTOS = :SEGUNDOS / 60;
HORAS = cast(:SEGUNDOS / 3600 as integer);
DIAS = cast(:SEGUNDOS / 86400 as integer);
suspend;
END
ELSE
BEGIN
DIFERENCA = cast(:DATA_FIM as date) - cast(:DATA_INICIO as date);
if (:DIFERENCA > 0) then
BEGIN
WHILE (i <= :DIFERENCA) DO
BEGIN
if (i = 0) then
begin
temp_dt_ini = :DATA_INICIO;
end
ELSE
begin
temp_dt_ini = cast(cast(:DATA_INICIO + i as date) || ' ' || :hr_inicio_expediente as TIMESTAMP);
end
if (Extract(Weekday from :temp_dt_ini) not in (0,6)) THEN
begin
if (cast(cast(:temp_dt_ini as date) || ' ' || :hr_fim_expediente as TIMESTAMP) > :DATA_FIM) then
temp_dt_fim = :DATA_FIM;
else
temp_dt_fim = cast(cast(:temp_dt_ini as date) || ' ' || :hr_fim_expediente as TIMESTAMP);
if (Extract(Weekday from :temp_dt_fim) not in (0,6)) then
begin
dif = datediff(SECOND from :temp_dt_ini to :temp_dt_fim);
:SEGUNDOS = :SEGUNDOS + :dif;
END
END
i = i +1;
END
MINUTOS = :SEGUNDOS / 60;
HORAS = cast(:SEGUNDOS / 3600 as integer);
DIAS = cast(:SEGUNDOS / 86400 as integer);
Suspend;
END
end
END
ELSE
BEGIN
SEGUNDOS = :SEGUNDOS;
MINUTOS = :SEGUNDOS;
HORAS = :SEGUNDOS;
DIAS = :SEGUNDOS;
suspend;
END
END;
Parâmetros da procedure:
Entrada:
- DATA_INICIO (timestamp) = Data inicial para cálculo do tempo. Exemplo: 2019-08-13 18:04:20
- DATA_FIM (timestamp) = Data final para cálculo do tempo. Exemplo: 2019-08-14 23:04:20
- HR_INICIO_EXPEDIENTE (varchar(5)) = Horário de início do expediente em formato texto. Exemplo: '08:00'
- HR_FIM_EXPEDIENTE (varchar(5)) = Horário de fim do expediente em formato texto. Exemplo: '18:30'
***OBSERVAÇÕES: Lembrar de passar os campos esse HR_INICIO_EXPEDIENTE e HR_FIM_EXPEDIENTE campo entre aspas simples. Caso queria calcular as horas totais do dia, sem considerar o expediente, bastas digitar passar '00:00' e '23:59' nos campos HR_INICIO_EXPEDIENTE e HR_FIM_EXPEDIENTE .
Saída
- DIAS (integer)= Tempo em dias do período informado entre DATA_INICIO e DATA_FIM.
- HORAS (integer) = Tempo em horas do período informado entre DATA_INICIO e DATA_FIM.
- MINUTOS (numeric) = Tempo em minutos do período informado entre DATA_INICIO e DATA_FIM.
- SEGUNDOS (numeric) = Tempo em segundos do período informado entre DATA_INICIO e DATA_FIM.
***OBSERVAÇÕES: Os campos de saída DIAS e HORAS são arredondados (inteiros) para facilitar o uso em Painéis, relatórios, cubos, entre outros. Caso queira um valor em formato numérico. Mas usar o campo MINUTOS ou SEGUNDOS convertendo para horas e ou dias.
Chamada da procedure:
SELECT dias,
horas,
minutos,
segundos
FROM RETORNA_TEMPO_DIAS_UTEIS ('2019-08-09 08:21:16', '2019-08-14 13:21:16','00:00:00','23:59:59')
Retorno:
DIAS|HORAS|MINUTOS|SEGUNDOS | ----|-----|-------|---------| 3| 77|4619.95|277197.00|
Explicação do retorno:
O tempo entre 09/08/2019 08:21:16 até 14/08/2019 13:21:16 é de 277.197 de segundos que é igual a 4619,95 de minutos que é igual a 77 de horas que é igual a 3 dias.
Detalhamento do cálculo:
- 09/08/2019 08:21:16 até 09/08/2019 23:59:59 = 56.323 segundos
- 10/08/2019 00:00:00 até 10/08/2019 23:59:59 = 0 segundos (não tem calculo pois é sábado)
- 11/08/2019 00:00:00 até 11/08/2019 23:59:59 = 0 segundos (não tem calculo pois é domingo)
- 12/08/2019 00:00:00 até 12/08/2019 23:59:59 = 86.399 segundos
- 13/08/2019 00:00:00 até 13/08/2019 23:59:59 = 86.399? segundos
- 14/08/2019 00:00:00 até 14/08/2019 13:21:16 = 48.076? segundos
- Totalizando 277.197 segundos
Procedure - Retorna último dia do mês
SQL:
create or alter procedure ULTIMODIAMES (MES integer, ANO integer) returns (ULTIMO_DIA smallint) as
declare variable VPROXMES integer;
declare variable DATA date;
begin
if (:mes is null) then
mes=EXTRACT(month FROM CURRENT_DATE);
if (:ano is null) then
ano=EXTRACT(YEAR FROM CURRENT_DATE);
vproxmes = :mes+1;
if (:mes=12) then
begin
ano= :ano+1;
vproxmes = 1;
end
DATA = '01.'||:vproxmes||'.'||ANO;
ultimo_dia = EXTRACT(DAY FROM (CAST(DATA AS DATE)-1));
suspend;
end
Chamada da procedure:
SELECT *
FROM ULTIMODIAMES(07, 2019)
Retorno:
31
Procedure - Retorna valor por extenso
SQL:
create or alter procedure retorna_valor_por_extenso(valor numeric(15, 2)) returns(valor_extenso varchar(240)) as
declare extenso varchar(240);
declare b1 integer;
declare b2 integer;
declare b3 integer;
declare b4 integer;
declare b5 integer;
declare b6 integer;
declare b7 integer;
declare b8 integer;
declare b9 integer;
declare b10 integer;
declare b11 integer;
declare b12 integer;
declare b13 integer;
declare b14 integer;
declare l1 varchar(12);
declare l2 varchar(3);
declare l3 varchar(9);
declare l4 varchar(3);
declare l5 varchar(6);
declare l6 varchar(8);
declare l7 varchar(12);
declare l8 varchar(3);
declare l9 varchar(9);
declare l10 varchar(3);
declare l11 varchar(6);
declare l12 varchar(8);
declare l13 varchar(12);
declare l14 varchar(3);
declare l15 varchar(9);
declare l16 varchar(3);
declare l17 varchar(6);
declare l18 varchar(8);
declare l19 varchar(12);
declare l20 varchar(3);
declare l21 varchar(9);
declare l22 varchar(3);
declare l23 varchar(6);
declare l24 varchar(16);
declare l25 varchar(3);
declare l26 varchar(9);
declare l27 varchar(3);
declare l28 varchar(6);
declare l29 varchar(17);
declare virgula_bi char(3);
declare virgula_mi char(3);
declare virgula_mil char(3);
declare virgula_cr char(3);
declare valor1 varchar(14);
/*-- tabela de centenas --*/
declare centenas char(108) = 'cento duzentos trezentos quatrocentosquinhentos seiscentos setecentos oitocentos novecentos ';
/*-- tabela de dezenas --*/
declare dezenas char(79) = 'dez vinte trinta quarenta cinquentasessenta setenta oitenta noventa ';
/*-- tabela de unidades --*/
declare unidades char(54) = 'um dois tres quatrocinco seis sete oito nove ';
/*-- tabela de unidades da dezena 10 --*/
declare unid10 char(81) = 'onze doze treze quatorze quinze dezesseisdezessetedezoito dezenove ';
declare v_implementar integer;
declare i integer;
declare v_vl_inteiro varchar(14);
begin
extenso = '';
l1 = '';
l2 = '';
l3 = '';
l4 = '';
l5 = '';
l6 = '';
virgula_bi = '';
l7 = '';
l8 = '';
l9 = '';
l10 = '';
l11 = '';
l12 = '';
virgula_mi = '';
l13 = '';
l14 = '';
l15 = '';
l16 = '';
l17 = '';
l18 = '';
virgula_mil = '';
l19 = '';
l20 = '';
l21 = '';
l22 = '';
l23 = '';
l24 = '';
virgula_cr = '';
l25 = '';
l26 = '';
l27 = '';
l28 = '';
l29 = '';
i = 1;
v_implementar = 0;
v_vl_inteiro = 0;
v_vl_inteiro = valor;
v_implementar = char_length(v_vl_inteiro);
valor1 = '';
while (i <= v_implementar) do
begin
if (substring(v_vl_inteiro from i for 1) <> '.') then
begin
valor1 = valor1 || substring(v_vl_inteiro from i for 1);
end
i = (i+1);
end
v_implementar = (14 - char_length(trim(valor1)));
i = 0;
while (i <= v_implementar) do
begin
if (char_length(valor1) < 14) then
begin
valor1 = '0' || valor1;
end
i = (i + 1);
end
b1 = substring(valor1 from 1 for 1);
b2 = substring(valor1 from 2 for 1);
b3 = substring(valor1 from 3 for 1);
b4 = substring(valor1 from 4 for 1);
b5 = substring(valor1 from 5 for 1);
b6 = substring(valor1 from 6 for 1);
b7 = substring(valor1 from 7 for 1);
b8 = substring(valor1 from 8 for 1);
b9 = substring(valor1 from 9 for 1);
b10 = substring(valor1 from 10 for 1);
b11 = substring(valor1 from 11 for 1);
b12 = substring(valor1 from 12 for 1);
b13 = substring(valor1 from 13 for 1);
b14 = substring(valor1 from 14 for 1);
if (valor <> 0) then
begin
if (b1 <> 0) then
begin
if (b1 = 1) then
begin
if ((b2 = 0) and (b3 = 0)) then
begin
l5 = 'cem';
end
else
begin
l1 = substring(centenas from (b1 * 12-11) for 12);
end
end
else
begin
l1 = substring(centenas from (b1 * 12-11) for 12);
end
end
if (b2 <> 0) then
begin
if (b2 = 1) then
begin
if (b3 = 0) then
begin
l5 = 'dez';
end
else
begin
l3 = substring(unid10 from (b3 * 9-8) for 9);
end
end
else
begin
l3 = substring(dezenas from (b2 * 9-8) for 9);
end
end
if (b3 <> 0) then
begin
if (b2 <> 1) then
begin
l5 = substring(unidades from (b3 * 6-5) for 6);
end
end
if ((b1 <> 0) or (b2 <> 0) or (b3 <> 0)) then
begin
if ((b1 = 0 and b2 = 0) and (b3 = 1)) then
begin
l5 = 'um';
l6 = ' bilhão';
end
else
begin
l6 = ' bilhões';
end
if (valor > 999999999) then
begin
/* virgula_bi = ' e ';*/
virgula_bi = '';
if ((b4+b5+b6+b7+b8+b9+b10+b11+b12) = 0) then
begin
virgula_bi = ' de ' ;
end
end
l1 = trim(l1);
l3 = trim(l3);
l5 = trim(l5);
if ((b2 > 1) and (b3 > 0)) then
begin
l4 = ' e ';
end
if ((b1 <> 0) and ((b2 <> 0) or (b3 <> 0))) then
begin
l2 = ' e ';
end
end
/*-- rotina dos milhoes --*/
if (b4 <> 0) then
begin
if (b4 = 1) then
begin
if ((b5 = 0) and (b6 = 0)) then
begin
l7 = 'cem';
end
else
begin
l7 = substring(centenas from (b4 * 12-11) for 12);
end
end
else
begin
l7 = substring(centenas from (b4 * 12-11) for 12);
end
end
if (b5 <> 0) then
begin
if (b5 = 1) then
begin
if (b6 = 0) then
begin
l11 = 'dez';
end
else
begin
l9 = substring(unid10 from (b6 * 9-8) for 9);
end
end
else
begin
l9 = substring(dezenas from (b5 * 9-8) for 9);
end
end
if (b6 <> 0) then
begin
if (b5 <> 1) then
begin
l11 = substring(unidades from (b6 * 6-5) for 6);
end
end
if ((b4 <> 0) or (b5 <> 0) or (b6 <> 0)) then
begin
if (((b4 = 0) and (b5 = 0)) and (b6 = 1)) then
begin
l11 = ' um';
l12 = ' milhão';
end
else
begin
l12 = ' milhões';
end
if (valor > 999999) then
begin
/* virgula_mi = ' e ';*/
virgula_mi = '';
if ((b7+b8+b9+b10+b11+b12) = 0) then
begin
virgula_mi = ' de ';
end
end
l7 = trim(l7);
l9 = trim(l9);
l11 = trim(l11);
if ((b5 > 1) and (b6 > 0)) then
begin
l10 = ' e ';
end
if ((b4 <> 0) and ((b5 <> 0) or (b6 <> 0))) then
begin
l8 = ' e ';
end
end
/*-- rotina dos milhares --*/
if (b7 <> 0) then
begin
if (b7 = 1) then
begin
if ((b8 = 0) and (b9 = 0)) then
begin
l17 = 'cem';
end
else
begin
l13 = substring(centenas from (b7 * 12-11) for 12);
end
end
else
begin
l13 = substring(centenas from (b7 * 12-11) for 12);
end
end
if (b8 <> 0) then
begin
if (b8 = 1) then
begin
if (b9 = 0) then
begin
l17 = 'dez';
end
else
begin
l15 = substring(unid10 from (b9 * 9-8) for 9);
end
end
else
begin
l15 = substring(dezenas from (b8 * 9-8) for 9);
end
end
if (b9 <> 0) then
begin
if (b8 <> 1) then
begin
l17 = substring(unidades from (b9 * 6-5) for 6);
end
end
if ((b7 <> 0) or (b8 <> 0) or (b9 <> 0)) then
begin
if (((b7 = 0) and (b8 = 0)) and (b9 = 1)) then
begin
l17 = 'um';
l18 = ' mil';
end
else
begin
l18 = ' mil';
end
if ((valor > 999) and ((b10+b11+b12) <> 0)) then
begin
virgula_mil = ' e ';
end
l13 = trim(l13);
l15 = trim(l15);
l17 = trim(l17);
if ((b8 > 1) and (b9 > 0)) then
begin
l16 = ' e ';
end
if ((b7 <> 0) and ((b8 <> 0) or (b9 <> 0))) then
begin
l14 = ' e ';
end
end
/*-- rotina dos reais --*/
if (b10 <> 0) then
begin
if (b10 = 1) then
begin
if ((b11 = 0) and (b12 = 0)) then
begin
l19 = 'cem';
end
else
begin
l19 = substring(centenas from (b10 * 12-11) for 12);
end
end
else
begin
l19 = substring(centenas from (b10 * 12-11) for 12);
end
end
if (b11 <> 0) then
begin
if (b11 = 1) then
begin
if (b12 = 0) then
begin
l23 = 'dez';
end
else
begin
l21 = substring(unid10 from (b12 * 9-8) for 9);
end
end
else
begin
l21 = substring(dezenas from (b11 * 9-8) for 9);
end
end
if (b12 <> 0) then
begin
if (b11 <> 1) then
begin
l23 = substring(unidades from (b12 * 6-5) for 6);
end
end
if ((b10 <> 0) or (b11 <> 0) or (b12 <> 0)) then
begin
if ((valor > 0) and (valor < 2)) then
begin
l23 = 'um';
end
l19 = trim(l19);
l21 = trim(l21);
l23 = trim(l23);
if ((b11 > 1) and (b12 > 0)) then
begin
l22 = ' e ';
end
if ((b10 <> 0) and ((b11 <> 0) or (b12 <> 0))) then
begin
l20 = ' e ';
end
end
if ((valor > 0) and (valor < 2)) then
begin
if (b12 <> 0) then
begin
l24 = ' real';
end
end
else
begin
if (valor > 1) then
begin
l24 = ' reais';
end
end
/*-- trata centavos --*/
if ((b13 <> 0) or (b14 <> 0)) then
begin
if (valor > 0) then
begin
if ((b12 <> 0) or ((b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) <> 0)) then
begin
l25 = ' e ';
end
end
if (b13 <> 0) then
begin
if (b13 = 1) then
begin
if (b14 = 0) then
begin
l28 = 'dez';
end
else
begin
l26 = substring(unid10 from b14*9-8 for 9);
end
end
else
begin
l26 = substring(dezenas from b13*9-8 for 9);
end
end
if (b14 <> 0) then
begin
if (b13 <> 1) then
begin
l28 = substring(unidades from b14*6-5 for 6);
end
end
if ((b13 <> 0) or (b14 <> 0)) then
begin
if (valor = 1) then
begin
l28 = 'um';
end
l26 = trim(l26);
l28 = trim(l28);
if ((b13 > 1) and (b14 > 0)) then
begin
l27 = ' e ';
end
end
if ((b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) > 0) then
begin
if ((b13 = 0) and (b14 = 1)) then
begin
l29 = ' centavo';
end
else
begin
l29 = ' centavos';
end
end
else
begin
if ((b13 = 0) and (b14 = 1)) then
begin
l29 = ' centavo';
end
else
begin
l29 = ' centavos';
end
end
end
/*-- concatenar o literal --*/
if ((l29 = ' centavo') or (l29 = ' centavos')) then
begin
virgula_mil = '';
end
extenso = iif(trim(l1)='','',trim(l1)||' ')||
iif(trim(l2)='','',trim(l2)||' ')||
iif(trim(l3)='','',trim(l3)||' ')||
iif(trim(l4)='','',trim(l4)||' ')||
iif(trim(l5)='','',trim(l5)||' ')||
iif(trim(l6)='','',trim(l6)||' ')||
iif(trim(virgula_bi)='','',trim(virgula_bi)||' ')||
iif(trim(l7)='','',trim(l7)||' ')||
iif(trim(l8)='','',trim(l8)||' ')||
iif(trim(l9)='','',trim(l9)||' ')||
iif(trim(l10)='','',trim(l10)||' ')||
iif(trim(l11)='','',trim(l11)||' ')||
iif(trim(l12)='','',trim(l12)||' ')||
iif(trim(virgula_mi)='','',trim(virgula_mi)||' ')||
iif(trim(l13)='','',trim(l13)||' ')||
iif(trim(l14)='','',trim(l14)||' ')||
iif(trim(l15)='','',trim(l15)||' ')||
iif(trim(l16)='','',trim(l16)||' ')||
iif(trim(l17)='','',trim(l17)||' ')||
iif(trim(l18)='','',trim(l18)||' ')||
iif(trim(virgula_mil)='','',trim(virgula_mil)||' ')||
iif(trim(l19)='','',trim(l19)||' ')||
iif(trim(l20)='','',trim(l20)||' ')||
iif(trim(l21)='','',trim(l21)||' ')||
iif(trim(l22)='','',trim(l22)||' ')||
iif(trim(l23)='','',trim(l23)||' ')||
iif(trim(l24)='','',trim(l24)||' ')||
iif(trim(virgula_cr)='','',trim(virgula_cr)||' ')||
iif(trim(l25)='','',trim(l25)||' ')||
iif(trim(l26)='','',trim(l26)||' ')||
iif(trim(l27)='','',trim(l27)||' ')||
iif(trim(l28)='','',trim(l28)||' ')||
trim(l29);
extenso = trim(extenso);
end
else
begin
extenso = 'zero';
end
valor_extenso = extenso;
suspend;
end
Chamada da procedure:
SELECT *
FROM retorna_valor_por_extenso(65489.64)
Retorno:
| VALOR_EXTENSO |
-----------------------------------------------------------------------------------------
| sessenta e cinco mil quatrocentos e oitenta e nove reais e sessenta e quatro centavos |
Retorna primeiro e último dia do mês
SQL:
select dateadd(1 - extract(day from current_date) day to current_date ) primeiro_dia,
dateadd(-extract(day from dateadd(1 month to current_date )) day to dateadd(1 month to current_date)) ultimo_dia,
dateadd(month, -12, dateadd(1 - extract(day from current_date) day to current_date )) primeiro_dia_12meses_antes
from rdb$database
Retorno:
| PRIMEIRO_DIA | ULTIMO_DIA | PRIMEIRO_DIA_12MESES_ANTES |
----------------------------------------------------------
| 01.07.2019 | 31.07.2019 | 01.07.2018 |
Retorna tamanho de consumo do campo da tabela
SQL:
execute block returns (tamanho numeric(18), TABELA varchar(100), CAMPO varchar(100)) as
declare variable xsql varchar(8000);
begin
for select trim(rdb$field_name), trim(rdb$relation_name) from rdb$relation_fields into :campo, :tabela do
begin
:xsql = 'select sum(bit_length('||:campo||')) from '||:tabela;
execute statement :xsql into :tamanho;
if (:tamanho > 0 ) then
begin
suspend;
end
end
end
Retorno:
| TAMANHO | TABELA | CAMPO |
----------------------------------------------------------------------
| 192 | NOMETABELA | COLUNADATABELA |
| 256 | NOMETABELA | COLUNA2DATABELA |
| 8 | NOMETABELA2 | COLUNADATABELA2 |
| 160 | NOMETABELA3 | COLUNADATABELA3 |
| ... | ... | ... |
Union e Union All
É possível combinar os resultados de duas ou mais consultas através do operador Union.
Será mostrado um exemplo de situação onde esse operador pode ser usado e os resultados entre duas tabelas.
TABELA EXEMPLO
EX_FILIAL
| DESCRICAO |
--------------
| 'Matriz' |
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_filial (descricao VARCHAR(20));
INSERT INTO ex_filial VALUES ('Matriz');
INSERT INTO ex_filial VALUES ('Filial 1');
INSERT INTO ex_filial VALUES ('Filial 2');
INSERT INTO ex_filial VALUES ('Filial 3');
EX_PONTOS_DE_VENDA
| DESCRICAO |
---------------
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |
| 'Unidade 1' |
| 'Unidade 2' |
SQL de CRIAÇÃO/INSERÇÃO:
CREATE TABLE ex_pontos_de_venda (descricao VARCHAR(20));
INSERT INTO ex_pontos_de_venda VALUES ('Filial 1');
INSERT INTO ex_pontos_de_venda VALUES ('Filial 2');
INSERT INTO ex_pontos_de_venda VALUES ('Filial 3');
INSERT INTO ex_pontos_de_venda VALUES ('Unidade 1');
INSERT INTO ex_pontos_de_venda VALUES ('Unidade 2');
UNION
O operador UNION une o retorno de duas consultas fazendo DISTINCT.
SELECT DESCRICAO
FROM EX_FILIAL
UNION
SELECT DESCRICAO
FROM EX_PONTOS_DE_VENDA
Retorno:
| DESCRICAO |
---------------
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |
| 'Matriz' |
| 'Unidade 1' |
| 'Unidade 2' |
UNION ALL
O operador UNION ALL une o retorno de duas consultas sem fazer DISTINCT.
SELECT DESCRICAO
FROM EX_FILIAL
UNION ALL
SELECT DESCRICAO
FROM EX_PONTOS_DE_VENDA
Retorno:
| DESCRICAO |
---------------
| 'Matriz' |
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |
| 'Filial 1' |
| 'Filial 2' |
| 'Filial 3' |
| 'Unidade 1' |
| 'Unidade 2' |
Utilização de Data e Conversões (Current_date, timestamp, cast, etc)
CURRENT_DATE
Seleciona data atual.
select current_date from rdb$database --> Retorno: "2019-09-13"
CURRENT_TIMESTAMP
Seleciona data e hora atual.
select current_timestamp from rdb$database --> Retorno: "2019-09-13 08:56:29.6490"
***OBSERVAÇÃO: Importante saber que o banco estará parametrizado para mostrar um valor de data/hora com um único formato para todos. Se a intenção é mostrar a data/hora em um formato diferente do parametrizado, é necessário converter a data/hora para um valor do tipo texto, definindo o formato desejado nas funções de conversão. Veja abaixo
CAST
Função para fazer conversão de valores de diversos tipos. Neste caso, de texto para data (A data precisa estar informada no mesmo formato parametrizado no banco de dados).
select cast('13-09-2019' as date) from rdb$database --> Retorno: "2019-09-13"
select cast('2019-09-13 08:56' as timestamp) from rdb$database --> Retorno: "2019-09-13 08:56"
select cast(current_date as varchar(10)) from rdb$database --> Retorno: "2019-09-13"
select cast(current_timestamp as varchar(24)) from rdb$database --> Retorno: "2019-09-13 08:56:29.6490"
EXTRACT
Pode ser usado para extrair uma informação específica de um campo de Data ou Data/hora.
select extract(year from current_date) from rdb$database --> Retorno: 2019 <-- (ano atual)
select extract(month from current_date) from rdb$database --> Retorno: 9 <-- (mês atual)
select extract(day from current_date) from rdb$database --> Retorno: 13 <-- (dia do mês atual)
select extract(hour from current_time)||':'||
extract(minute from current_time)||':'||
trunc(extract(second from current_time)) from rdb$database --> Retorno: 14:43:46 <-- (hora atual)