Stored Procedure para buscar a sequência que falta de um determinado campo INTEGER de uma tabela especificada – SQL Firebird
Olá pessoal, tudo bem com vocês?
{ Contribuição de Ivan Cesar. }
Há situações em que temos um cadastro com uma numeração sequencial.
Um exemplo, pode ser a numeração da nota fiscal, ou seja
1
2
3
4
5
6
7
…
Porém, pode acontecer por diversos motivos de faltar um ou mais registros da sequência, como segue
1
2
4
7
E então ser necessário recuperar os números faltantes da sequencia, certo?
E ai, como fazer?
Por sorte a stored procedure para SQL Firebird abaixo pode fazer todo trabalho pesado para a gente!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| CREATE OR ALTER PROCEDURE MISSING_INTEGER_SEQUENCE (
ITABLE_NAME VARCHAR(31) NOT NULL,
IFIELD_NAME VARCHAR(31) NOT NULL,
ISEQ_MIN INTEGER NOT NULL DEFAULT 0,
ISEQ_MAX INTEGER NOT NULL DEFAULT 0)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE VCOUNT INTEGER;
DECLARE VARIABLE VLOOP INTEGER;
BEGIN
-- TRATA O NOME DA TABELA INFORMADO
ITABLE_NAME = UPPER(TRIM(:ITABLE_NAME));
IF (:ITABLE_NAME = '') THEN
BEGIN
RESULT = -9; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DA TABELA
SUSPEND;
EXIT;
END
-- VERIFICA SE A TABELA EXISTE
IF (NOT (EXISTS(SELECT R.RDB$RELATION_NAME FROM RDB$RELATIONS R
WHERE (UPPER(TRIM(R.RDB$RELATION_NAME)) = :ITABLE_NAME) AND (R.RDB$SYSTEM_FLAG = 0)))) THEN
BEGIN
RESULT = -9; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DA TABELA
SUSPEND;
EXIT;
END
-- TRATA O NOME DO CAMPO INFORMADO
IFIELD_NAME = UPPER(TRIM(:IFIELD_NAME));
IF (:IFIELD_NAME = '') THEN
BEGIN
RESULT = -8; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DO CAMPO
SUSPEND;
EXIT;
END
-- VERIFICA SE O CAMPO EXISTE NA TABELA PASSADA
IF (NOT (EXISTS(SELECT F.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS F
WHERE (F.RDB$RELATION_NAME = :ITABLE_NAME) AND (F.RDB$FIELD_NAME = :IFIELD_NAME)))) THEN
BEGIN
RESULT = -8; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DO CAMPO
SUSPEND;
EXIT;
END
-- SE INFORMAR ZERO OU MENOS, PEGA O VALOR MINIMO DO CAMPO NA TABELA
IF (:ISEQ_MIN <= 0) THEN
EXECUTE STATEMENT 'SELECT MIN(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME INTO :ISEQ_MIN;
-- SE INFORMAR ZERO OU MENOS, PEGA O VALOR MAXIMO DO CAMPO NA TABELA
IF (:ISEQ_MAX <= 0) THEN
EXECUTE STATEMENT 'SELECT MAX(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME INTO :ISEQ_MAX;
-- SE O VALOR MINIMO FOR MAIOR QUE O VALOR MAXIMO, IGUALA OS DOIS PARA NAO DAR ERRO
IF (:ISEQ_MIN > :ISEQ_MAX) THEN
BEGIN
RESULT = -7; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NAS SEQUENCIAS
SUSPEND;
EXIT;
END
-- FAZ O LOOP EFETUANDO A BUSCA NA TABELA
VLOOP = :ISEQ_MIN;
WHILE (VLOOP <= :ISEQ_MAX) DO
BEGIN
EXECUTE STATEMENT 'SELECT COUNT(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME || ' WHERE ' || :IFIELD_NAME || ' = ' || :VLOOP INTO :VCOUNT;
IF (:VCOUNT = 0) THEN
BEGIN
RESULT = :VLOOP;
SUSPEND;
END
VLOOP = :VLOOP + 1;
END
END |
CREATE OR ALTER PROCEDURE MISSING_INTEGER_SEQUENCE (
ITABLE_NAME VARCHAR(31) NOT NULL,
IFIELD_NAME VARCHAR(31) NOT NULL,
ISEQ_MIN INTEGER NOT NULL DEFAULT 0,
ISEQ_MAX INTEGER NOT NULL DEFAULT 0)
RETURNS (
RESULT INTEGER)
AS
DECLARE VARIABLE VCOUNT INTEGER;
DECLARE VARIABLE VLOOP INTEGER;
BEGIN
-- TRATA O NOME DA TABELA INFORMADO
ITABLE_NAME = UPPER(TRIM(:ITABLE_NAME));
IF (:ITABLE_NAME = '') THEN
BEGIN
RESULT = -9; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DA TABELA
SUSPEND;
EXIT;
END
-- VERIFICA SE A TABELA EXISTE
IF (NOT (EXISTS(SELECT R.RDB$RELATION_NAME FROM RDB$RELATIONS R
WHERE (UPPER(TRIM(R.RDB$RELATION_NAME)) = :ITABLE_NAME) AND (R.RDB$SYSTEM_FLAG = 0)))) THEN
BEGIN
RESULT = -9; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DA TABELA
SUSPEND;
EXIT;
END
-- TRATA O NOME DO CAMPO INFORMADO
IFIELD_NAME = UPPER(TRIM(:IFIELD_NAME));
IF (:IFIELD_NAME = '') THEN
BEGIN
RESULT = -8; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DO CAMPO
SUSPEND;
EXIT;
END
-- VERIFICA SE O CAMPO EXISTE NA TABELA PASSADA
IF (NOT (EXISTS(SELECT F.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS F
WHERE (F.RDB$RELATION_NAME = :ITABLE_NAME) AND (F.RDB$FIELD_NAME = :IFIELD_NAME)))) THEN
BEGIN
RESULT = -8; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NO NOME DO CAMPO
SUSPEND;
EXIT;
END
-- SE INFORMAR ZERO OU MENOS, PEGA O VALOR MINIMO DO CAMPO NA TABELA
IF (:ISEQ_MIN <= 0) THEN
EXECUTE STATEMENT 'SELECT MIN(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME INTO :ISEQ_MIN;
-- SE INFORMAR ZERO OU MENOS, PEGA O VALOR MAXIMO DO CAMPO NA TABELA
IF (:ISEQ_MAX <= 0) THEN
EXECUTE STATEMENT 'SELECT MAX(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME INTO :ISEQ_MAX;
-- SE O VALOR MINIMO FOR MAIOR QUE O VALOR MAXIMO, IGUALA OS DOIS PARA NAO DAR ERRO
IF (:ISEQ_MIN > :ISEQ_MAX) THEN
BEGIN
RESULT = -7; -- RETORNO APENAS PARA SABER QUE DEU ALGUM ERRO NAS SEQUENCIAS
SUSPEND;
EXIT;
END
-- FAZ O LOOP EFETUANDO A BUSCA NA TABELA
VLOOP = :ISEQ_MIN;
WHILE (VLOOP <= :ISEQ_MAX) DO
BEGIN
EXECUTE STATEMENT 'SELECT COUNT(' || :IFIELD_NAME || ') FROM ' || :ITABLE_NAME || ' WHERE ' || :IFIELD_NAME || ' = ' || :VLOOP INTO :VCOUNT;
IF (:VCOUNT = 0) THEN
BEGIN
RESULT = :VLOOP;
SUSPEND;
END
VLOOP = :VLOOP + 1;
END
END
Vamos a exemplos de usos:
PROCURANDO NÚMEROS FALTANTES NA NOTA FISCAL.
1
2
3
| SELECT
M.result AS CODIGO_FANTANTE
FROM missing_integer_sequence('NOTAFISCAL', 'NUMERO', 0, 0) M |
select
M.result AS CODIGO_FANTANTE
from missing_integer_sequence('NOTAFISCAL', 'NUMERO', 0, 0) M
Procurando códigos faltantes no pedido.
1
2
3
| SELECT
M.result AS CODIGO_FANTANTE
FROM missing_integer_sequence('PEDIDO', 'CODIGO', 0, 0) M |
select
M.result AS CODIGO_FANTANTE
from missing_integer_sequence('PEDIDO', 'CODIGO', 0, 0) M
Procurando códigos faltantes no pedido, mas dentro do intervalo de 100 e 200
1
2
3
| SELECT
M.result AS CODIGO_FANTANTE
FROM missing_integer_sequence('PEDIDO', 'CODIGO', 100, 200) M |
select
M.result AS CODIGO_FANTANTE
from missing_integer_sequence('PEDIDO', 'CODIGO', 100, 200) M
Veja que pelos exemplos é muito fácil recuperar os números ou códigos faltantes e mesmo para tabelas com um grande número de registros, pode se utilizar um “filtro” para ir procurando dentro de determinadas faixas.
Certo pessoal? A stores procedure me ajudou muito e espero que ajude a todos.
Um abraço.
-
Ivan Cesar
-
3.219 views
- 0 comentários
- 12 de março de 2020
Está gostando do conteúdo? Considere pagar um cafezinho para nossa equipe!
Posts Relacionados - Continue Aprendendo