Sequências auto-incremento banco dados ORACLE

Por certo, as sequências (SEQUENCES) são objetos dentro do banco de dados ORACLE que proporciona o processo de criação de identificadores únicos nos registros em tabelas, Sequências podem ser definidas como auto-incremento em banco dados ORACLE.

Dessa forma, as sequências podem ser definidas como sendo um contador automático que é disparada toda vez que é acionada.

Assim, este número pode ser utilizado em uma coluna do tipo código identificador de uma tabela, garantindo assim que não existam duas linhas de registros com o mesmo número. Portanto, as sequências podem ser usadas para gerar automaticamente valores de chave primária.

Sintaxe

A saber, o comando para criar uma sequência é o CREATE SEQUENCE:

CREATE SEQUENCE nome
START WITH inteiro
INCREMENT BY inteiro
NOMINVALUE/MINVALUE inteiro
NOMAXVALUE/MAXVALUE inteiro
CYCLE/NOCYCLE
NOCACHE/CACHE inteiro
ORDER/NOORDER;

• nome

  • dessa maneira, um nome para o objeto criado, por boas práticas se adota iniciar pelo sufixo SEQ_ (SEQ = SEQUENCE), mas pode ser qualquer nome até 30 caracteres (máximo de caracteres para um objeto  criado no ORACLE), desde que tenha letras e números, inicie com uma letra e tenha apenas o caractere especial undeline.

• START WITH

  • dessa forma, é o valor inicial que a sequência deverá ter, ou seja, a primeira vez que for acionada retornará esse valor inicial.

• INCREMENT BY 

  • valor que representa o incremento ou decremento no valor da sequence a partir do seu segundo acionamento.

• MINVALUE

  • Especifica o valor mínimo para as sequências que estiverem sendo decrementadas. É mutuamente exclusiva ao MAXVALUE. MINVALUE deve ser menor ou igual a START WITH e deve ser menor que MAXVALUE.

• MAXVALUE

  • valor máximo que a sequências poderá ter, se omitida poderá assumir o maior valor permitido para um número pelo ORACLE. Essa cláusula pode ser omitida ou definida como NOMAXVALUE.

• CACHE

  • indica o número de sequências que devem ser criadas na memória cache da respectiva sessão, isso pode ajudar a gerar menos acesso a disco, mas em contra partida se não forem usadas essas sequências serão perdidas. a cláusula NOCACHE indica que não serão reservadas sequências em memória cache. Padrão do CAHCE é 20.

• CYCLE

  • Ativada essa cláusula, quando a sequência chegar ao valor máximo indicado no MAXVALUE a sequência deve voltar ao valor inicial. Por sua vez a cláusula NOCYCLE impedirá a volta ao início.

• ORDER

  • Use ORDER para garantir que o Oracle gere os números de sequência na ordem do pedido.
  • Use NOORDER se não desejar garantir que o Oracle gere números de sequência na ordem do pedido.
  • É necessário apenas para garantir a geração ordenada se você estiver usando o Oracle com Real Application Clusters. Se você estiver usando o modo exclusivo, os números de sequência serão sempre gerados em ordem. Esta opção é o padrão.

Características de uso

Antes de mais nada, é importante saber algumas características do uso de sequências:

• Gerando um número de sequência, a sequência é incrementada, independentemente da transação ser confirmada ou não.
• Se dois usuários incrementarem simultaneamente a mesma sequência, os números de sequência que cada usuário adquirir nunca será a mesma e poderão ter lacunas.
• Os números de sequência são gerados independentemente das tabelas, portanto, a mesma sequência pode ser usada para uma ou várias tabelas.

Experimento Prático

A princípio, para o experimento prático será utilizada a mesma tabela PESSOA do artigo Inserir registros banco dados ORACLE.

Configurando ambiente

Acima de tudo, para gerar a autonumeração o script abaixo será aplicado para cria a SEQUENCE que será utilizada para gerar identificadores para a tabela PESSOA:

CREATE SEQUENCE SEQ_PESSOA
START WITH 10
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

Assim que, a sequence for criada já é possível fazer seu uso, para isso deve-se fazer uso de duas pseudocolunas: CURRVAL (retorna o valor atual da sequence) e NEXTVAL retorna o próximo valor da sequence).

Todavia, para acionar a sequence com o uso das pseudocolunas, é preciso colocar o nome, ponto, a pseudocoluna. Exemplificando: SEQ_PESSOA.CURRVAL; ou SEQ_PESSOA.NEXTVAL;. No caso de estar em uma área diferente da que o usuário está trabalhando é necessáio colocar o nome do esquema: ESQUEMA.SEQ_PESSOA.CURRVAL; ou ESQUEMA.SEQ_PESSOA.NEXTVAL;.

Salvo, a primeira vez que uma sequence é acionada com a pseudocoluna NEXTVAL, a mesma incrementa o número antes de retornar o valor, pegando assim o próximo valor. Já na primeira vez, pega o valor do STRAT WITH.

Usando sequence no comando INSERT

Para usar a squence para gerar a autonumeração em um comando INSERT é só trocar o valor da coluna identificadora, no caso a ID para a chamada da sequence com NEXTVAL.

INSERT INTO PESSOA 
  VALUES (SEQ_PESSOA.NEXTVAL, 'MARIA', 'F', TO_DATE('12/03/1989','DD/MM/YYYY'));
-- 1 row(s) inserted
INSERT INTO PESSOA 
  VALUES (SEQ_PESSOA.NEXTVAL, 'PEDRO', 'X', TO_DATE('05/10/1972','DD/MM/YYYY'));
-- erro relacionado a constraint de CHECK - CK_GENERO_PESSOA
INSERT INTO PESSOA 
  VALUES ('PEDRO', SEQ_PESSOA.NEXTVAL, 'M', TO_DATE('05/10/1972','DD/MM/YYYY'));
-- erro relacionado a inversão de valores entre ID e NOME
INSERT INTO PESSOA 
  VALUES (SEQ_PESSOA.NEXTVAL, 'CLAUDIA', 'F', '12/03/2001');
-- erro nem todas as instalação do ORACLE 
-- (depende da regionalização) aceita a 
-- data direta
INSERT INTO PESSOA 
  VALUES (SEQ_PESSOA.NEXTVAL, 'CLAUDIA', 'F'); 
-- ORA-00947: not enough values 
INSERT INTO PESSOA 
  (ID, NOME, GENERO, DATA_NASCIMENTO) 
  VALUES (SEQ_PESSOA.NEXTVAL, 'PAULO', 'M', TO_DATE('12/03/2006','DD/MM/YYYY'));
-- 1 row(s) inserted

Explicação do SCRIPT acima

• Linha 1: o registro é inserido, como é a primeira vez que a SEQUENCE é utilizada o valor utilizado foi o de sua inicialização, no caso o valor 10.
• Já as Linhas 4, 7, 10 e 15: os INSERTs não são bem sucedidos por erros, mas como usaram a chamada NEXTVAL da sequence em questão, os números foram acionados e utilizados, dessa forma foram perdidas as sequências de valor: 11, 12 e 13.
• Finalizando a Linha 18: insere o registro e quando aciona a SEQUENCE pega o próximo valor no caso o número 14.

Assim, a tabela PESSOA fica como segue:

Nota

Em contrapartida, dentro da estrutura do banco de dados ORACLE existem outras pseudocolunas que atuam como colunas extras em uma tabela criada. 

• ROWID
  • Identifica cada linha através de uma localização ou endereço, o ROWID existe enquanto exisitir o registro. Constitui-se por ser um identificador exclusivo de cada registro no banco de dados.
• ROWNUM
  • Para cada linha retornada por uma consulta, a pseudocoluna ROWNUM retorna um número indicando a ordem na qual o Oracle seleciona a linha de uma tabela ou conjunto de linhas unidas. A primeira linha selecionada tem um ROWNUM de 1, a segunda tem 2 e assim por diante.

Conclusão Sequências auto-incremento banco dados ORACLE

Por fim, o uso do objeto SEQUENCE é o recurso para se gerar autonumeração no banco de dados ORACLE, este artigo tratou sobre Sequências auto-incremento banco dados ORACLE.

Do mesmo modo, é importante citar os artigos relacionados:

Inserir registros banco dados ORACLE.

Alterar registros banco dados ORACLE.