Firebird: Sincronizando Generators pelo total de registros das tabelas

0

Posted on : 18-10-2012 | By : Paulo H Oliveira | In : Firebird

Segue abaixo uma rotina em SQL – EXECUTE BLOCK em Firebird para sincrinizar os GENERATORS conforme o total de registros nas tabelas

EXECUTE BLOCK
AS
DECLARE VARIABLE VTABELA VARCHAR(50);
DECLARE VARIABLE VCAMPO  VARCHAR(50);
DECLARE VARIABLE VSQL    VARCHAR(3000);
DECLARE VARIABLE VHANDLE INTEGER;
BEGIN
  FOR
    SELECT RDB$RELATIONS.RDB$RELATION_NAME
            FROM RDB$GENERATORS,
                 RDB$RELATIONS,
                 RDB$RELATION_FIELDS
           WHERE RDB$GENERATORS.RDB$GENERATOR_NAME = 'GEN_'||TRIM(RDB$RELATIONS.RDB$RELATION_NAME)
             AND RDB$RELATIONS.RDB$RELATION_NAME = SUBSTR(RDB$GENERATORS.RDB$GENERATOR_NAME,5,CHAR_LENGTH(RDB$GENERATORS.RDB$GENERATOR_NAME))
             AND RDB$RELATION_FIELDS.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATION_NAME
             AND RDB$RELATION_FIELDS.RDB$FIELD_NAME = 'HANDLE'
            INTO :VTABELA
  DO
  BEGIN
    SELECT RDB$FIELD_NAME
      FROM RDB$RELATION_FIELDS
     WHERE RDB$FIELD_NAME = 'HANDLE'
       AND RDB$RELATION_NAME = :VTABELA
      INTO :VCAMPO;
 
    IF ((CHAR_LENGTH(:VCAMPO) > 0) OR (:VCAMPO IS NULL))  THEN
    BEGIN
      VSQL = 'SELECT MAX(HANDLE) FROM '||TRIM(:VTABELA);
      EXECUTE STATEMENT VSQL INTO :VHANDLE;
 
      IF (:VHANDLE IS NULL) THEN
        VHANDLE = 0;
 
      VSQL = 'ALTER SEQUENCE GEN_'||TRIM(:VTABELA)||' RESTART WITH '||:VHANDLE;
      EXECUTE STATEMENT VSQL;
 
    END
 
  END
END

Compartilhe :

  • Stumble upon
  • twitter

Escreva um comentário

Security Code: