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 |
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 |
Procurando códigos faltantes no pedido.
1 2 3 | 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 |
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.
