Campo auto incremento no Banco de Dados Oracle
Autor: Prof. Paulo Kaupa
Veja neste artigo como
implementar valores auto incremento em um campo de uma tabela do banco de dados
Oracle. Os tipos de dados auto numéricos são usados quando existe a necessidade
de obter numeros sequenciais (ascendente ou decrescentes). Este tipo de valor é
muito usado em campos do tipo PRIMARY KEY.
O Oracle não possui um tipo de
dados auto incremental como pode ser encontrado em outros bancos de dados. O
SQL Server possue o tipo IDENTITY que incrementa valores de forma automática,
definindo-se apenas o valor inicial e o valor a ser incrementado. Outro Banco
que tambem conta com um tipo de dados auto incremento é o PostgreeSQL, o tipo
SERIAL
Exemplo de código para criação de
uma tabela com um campo auto incremento no SQL Server:
CREATE
TABLE TB_ALUNO
(
COD_ALUNO INT IDENTITY (1,1)
)
Exemplo de código para criação
de uma tabela com um campo auto incremento no PostgreeSQL:
CREATE TABLE TB_ALUNO
(
COD_ALUNO SERIAL
);
Criando um campo auto incremento
no Oracle
No Oracle ten-se um objeto
chamado SEQUENCE que é utilizado em campos em que existe a necessidade de gerar
valores incrementáveis, como um campo chave primária. Além do objeto SEQUENCE
que será tratado mais adiante neste artigo, pode-se criar um campo auto incremento
com o seguinte script:
SELECT NVL(MAX(COD_ALUNO),0)+1;
Onde:
·
SELECT:
Comando de consulta de dados do SQL.
·
NVL(): Função
que retorna um valor específicado caso seja encontrado um valor nullo. No caso,
se o campo COD_ALUNO contiver um valor nulo, a função irá retornar o valor 0
(zero).
·
MAX(): Retorna o maior valor já inserido no campo COD_ALUNO.
Com o uso do script anterior, consulta-se o maior
valor já inserido no campo COD_ALUNO e então acrecenta-se 1(um), gerando assim
uma sequencia numérica para o campo.
O script anterior é usado no momento da inserção de
dados na tabela, ou seja, deve ser usado em conjunto com o comando INSERT da
linguagem SQL.
Criando uma tabela para inserção de dados
autonuméricos:
CREATE TABLE
TB_ALUNO
(
COD_ALUNO NUMBER(38)
);
COMMIT;
Observação: Sempre
após a criação ou alteração de um objeto execute o comando commit para efetivar
a operação no banco de dados. Lembrando que este comando não é necessário caso
seu banco de dados esteja configurado para auto-commit.
A seguir o script para inserir dados na tabela
TB_ALUNO:
INSERT INTO TB_ALUNO
(COD_ALUNO
) SELECT NVL(MAX(COD_ALUNO), 0) + 1
FROM TB_ALUNO;
COMMIT;
Observação: Sempre
após a inclusão ou alteração de dados execute o comando commit para efetivar a
operação no banco de dados. Lembrando que este comando não é necessário caso
seu banco de dados esteja configurado para auto-commit.
Para consultar os dados inseridos na TB_ALUNO, execute
o script a seguir:
SELECT
COD_ALUNO
FROM
TB_ALUNO;
Deverá ser retornado apenas uma linha com um único
valor conforme mostrado na Figura 1:
Figura 1: Mostra uma única
linha retornada pela consulta.
Execute o script para a inserção de mais alguns
registros e então consulte nomente. Verifique se os valores inseridos para a coluna
COD_ALUNO são sequenciais conforme mostrado na Figura 2:
Figura 2: Mostra quatro linhas
retornadas pela consulta.
As SEQUENCES são objetos criados com o objetivo de
gerar automaticamente números inteiros sequenciais em campos como as chaves
primárias, onde os valores não podem ser repetidos e devem ser gerados
sequancialmente.
Para a criação de um objeto SEQUENCE usa-se o comando
a seguir:
CREATE SEQUENCE SEQ_ALUNOS
MINVALUE
1
START
WITH 1
INCREMENT
BY 1
CACHE
20;
Onde:
- MINVALUE: Valor mínimo gerado pela
sequencia. Na ausência de um valor para este parâmetro o valor padrão é 1
(um).
·
START WITH: Define o
primeiro valor gerado pela sequencia. Na ausência de um valor para este
parâmetro o valor padrão é 1 (um).
·
INCREMENT BY: Especifica
o valor a ser incrementado cada vez que a sequencia for chamada. Na ausência de
um valor para este parâmetro o valor padrão é 1 (um). Se for especificado um
valor negativo a ordem será gerada de maneira inversa (Descendente), o padrão é
a geração ascendente.
·
CACHE: Especifica quantos valores
serão armazenados previamente na memória. Gerando antecipadamente os próximos
valores da sequencia, melhorando o desempenho das operações que envolverem a
SEQUENCE. Na ausência de um valor para este parâmetro o valor padrão é 20
(vinte).
Também
podem ser usados os seguintes atributos na criação da SEQUENCE:
- MAXVALUE: mínimo gerado pela sequencia. Na ausência de um valor para este
parâmetro o valor padrão é 10^27 (dez elevado a vigégima sétima potência).
- CYCLE: Define o comportamento da SEQUENCE após atingir seu valor máximo
definido na propriedade MAXVALUE. Se continuará a gerar valores após alcançar
seu valor máximo, reiniciando o contador. O valor padrão é nocycle.
O que fazer quando o Objeto Sequence Alcança
seu valor Máximo?
Consultando as SEQUENCES criadas no Banco de Dados:
As SEQUENCEs criadas no banco de dados podem ser
consultadas na tabela USER_SEQUENCES com o seguinte script SQL:
SELECT
SEQUENCE_NAME,
INCREMENT_BY,
MAX_VALUE
FROM
USER_SEQUENCES;
Com o script anterior todas as SEQUENCES são
retornadas, a figura 3 exibe uma amostra do retorno do script anterior:
Figura 3: SEQUENCES criadas no
Banco de Dados.
Inserindo valores
auto-numéricos em uma tabela com o uso de uma SEQUENCE.
Para testes de insersão de
dados auto-numéricos, cria-se a tabela que irá receber os valores da SEQUENCE;
CREATE TABLE TB_NACIONALIDADE
(
COD_NACIONALIDADE
NUMBER(38),
DES_NACIONALIDADE VARCHAR2(40)
);
COMMIT;
Com o script anterior criou-se
uma tabela chamada TB_NACIONALIDADE que contem dois campos: COD_NACIONALIDADE
do tipo NUMBER, este campo irá armazenar os valores sequenciais e um segundo
campo DES_NACIONALIDADE do tipo VARCHAR2, que irá armazenar a descrição da
nacionalidade (Brasileiro, Alemão, Canadense...).
Para a criação da SEQUENCE que
será usasa na tabela TB_NACIONALIDADE, execute o script a seguir:
CREATE SEQUENCE SEQ_NACIONALIDADE
MINVALUE
1
START
WITH 1
INCREMENT
BY 1
CACHE
20;
COMMIT;
Com o script a seguir,
insere-se o valor da SEQUENCE criada no campo COD_NACIONALIDADE da
TB_NACIONALIDADE:
INSERT INTO TB_NACIONALIDADE
(COD_NACIONALIDADE,
DES_NACIONALIDADE) VALUES(
SEQ_ALUNOS.NEXTVAL,
‘Brasileiro’
);
COMMIT;
Mesmo com o uso de um objeto
SEQUENCE o valor do campo poderá se duplicar, para garantir a não duplicidade
dos valores inseridos é necessário defini-lo como chave primária ou atribuir
uma CONSTRAINT do tipo UNIQUE.
O uso dos objetos SEQUENCES no
Oracle é aplicavel sempre que existir a necessidade de gerar números sequencias
para um determinado campo. Seu uso é amplamente visto para campos de chave
primária (PRIMARY KEY).
Para aprofundar-se mais neste
assunto consulte as referências utilizadas como base para o desenvolvimento do
presente artigo.
