Baixe Sequence no Oracle e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity!
Coluna do tipo auto-incremento no Oracle? Abordando o uso de sequências no
Oracle...
Olá, Realmente, o Oracle não possui um tipo de dado "auto-incremento" como podemos ver em alguns outros bancos de dados. Por exemplo, no caso do PostgreSQL existe um tipo de dado SERIAL que, na verdade, implementa números seqüenciais através de um objeto SEQUENCE. A diferença é que o PostgreSQL permite definir esta seqüência (sequence) utilizando a cláusula DEFAULT da coluna em questão. Já no Oracle, infelizmente "ainda" o mesmo não permite definir um objeto seqüência na cláusula DEFAULT da coluna como demonstrado abaixo: SCOTT> create sequence seq_teste nocache; Seqüência criada. SCOTT> desc minha_tabela Nome Nulo? Tipo
ID NOT NULL NUMBER DESCRICAO NOT NULL VARCHAR2(100) SCOTT> alter table minha_tabela modify id default seq_teste.nextval; alter table emp modify id default seq_emp_id.nextval
ERRO na linha 1: ORA-00984: coluna não permitida aqui Neste caso teremos que criar uma trigger de banco de dados para realizar esta operação. Portanto, neste artigo irei fazer uma breve introdução sobre o objeto SEQUENCE, além de demonstrar através de um exemplo prático como poderemos criar um campo do tipo "auto- incremento" utilizando seqüências de banco de dados. Antes de começar a falar sobre o objeto SEQUENCE , irei demonstrar como gerar valores seqüenciais sem a necessidade de ter que criar uma SEQUENCE de banco de dados. -- Irei criar uma tabela para teste SCOTT> create table emp ( 2 id number constraint pk_emp primary key, 3 nome varchar2(60) not null); Tabela criada. SCOTT> insert into emp (id,nome) select nvl(max(id),0)+1,'MARIA' from emp; 1 linha criada. SCOTT> select * from emp; ID NOME
1 MARIA SCOTT> insert into emp (id,nome) select nvl(max(id),0)+1,'REGINA' from emp; 1 linha criada. SCOTT> select * from emp; ID NOME
1 MARIA 2 REGINA
Podemos ver acima, que é simples gerar dados seqüências através de um comando INSERT, mas este método funcionaria perfeitamente apenas para sistemas monousuários. Se o sistema for para acesso multiusuário, aí este método não é recomendável. Bem, como eu ainda não finalizei a transação acima com (COMMIT ou ROLLBACK), o que acontecerá se eu abrir uma nova seção de banco de dados e executar o mesmo comando INSERT? SESSÃO 2 SCOTT> insert into emp (id,nome) select nvl(max(id),0)+1,'EDUARDO' from emp; [Aguardando...] Podemos perceber que a sessão acima está aguardando a finalização da transação iniciada pela SESSAO 1 , já que o registro na tabela EMP foi locado pela mesma. Abaixo irei retornar para a SESSAO 1 e finalizar a transação com COMMIT: SESSÃO 1 SCOTT> commit; Commit concluído. Ao retornar para a SESSAO 2 mostrada abaixo, podemos perceber que houve uma violação de chave primária na tabela EMP. SESSÃO 2 SCOTT> insert into emp (id,nome) select nvl(max(id),0)+1,'EDUARDO' from emp; insert into emp (id,nome) select nvl(max(id),0)+1,'EDUARDO' from emp
ERRO na linha 1: ORA-00001: restrição exclusiva (SCOTT.PK_EMP) violada Isto aconteceu pelo fato de a SESSAO 2 não ter conhecimento da transação concorrente iniciada pela SESSAO 1. Isto se refere à propriedade I ( ISOLAMENTO ) de um termo conhecido como ACID , na qual todos os bancos de dados relacionais devem atender:
- ATOMICIDADE - Tudo (commit) ou nada (rollback)
- CONSISTÊNCIA - Sem violação de restrições de integridade
- ISOLAMENTO - Alterações concorrentes invisíveis
- DURABILIDADE - Persistência das atualizações confirmadas Embora diversas transações possam ser executadas de forma concorrente, o sistema gerenciador de banco de dados deve garantir a consistência de leitura. É aí que entram em ação os segmentos de rollback ou UNDO... -- Limpando a tabela EMP SCOTT> truncate table emp; Tabela truncada. Após a demonstração acima, irei realizar abaixo uma introdução breve sobre uso do objeto SEQUENCE disponível no banco de dados Oracle. Então, o que é um objeto seqüência? Uma seqüência (sequence) é um objeto de banco de dados criado pelo usuário que pode ser compartilhado por vários usuários para gerar números inteiros exclusivos de acordo com regras especificadas no momento que a seqüência é criada. A seqüência é gerada e incrementada (ou diminuída) por uma rotina interna do Oracle. Normalmente, as seqüências são usadas para criar um valor de chave primária que deve ser exclusivo para cada linha de uma tabela. Vale a pena salientar que os números de seqüências são armazenados e gerados de modo independente das tabelas. Portanto, o mesmo objeto seqüência pode ser usado por várias tabelas e inclusive por vários usuários de banco de dados caso necessário. Geralmente, convém atribuir à seqüência um nome de acordo com o uso a que se destina; no entanto, ela poderá ser
é positivo, mas o número inteiro de increment by é negativo, o que na prática diz à seqüência para decrescer, ao invés de crescer. Quando o valor zero for atingido, a seqüência começará novamente a contagem. SCOTT> create sequence seq_decrescente_ 2 start with 5 3 increment by - 1 4 maxvalue 5 5 minvalue 0 6 nocache 7 cycle; Seqüência criada. Uma vez criada a seqüência, ela poderá ser utilizada usando-se como referência as pseudo- colunas CURRVAL e NEXTVAL. Os usuários do banco de dados podem visualizar o valor atual da seqüência usando um comando SELECT. Da mesma forma, o próximo valor da seqüência pode ser gerado com um comando SELECT. Podemos ver abaixo como a seqüência SEQ_DECRESCENTE_5 faz um ciclo quando o valor de minvalue é atingido: SCOTT> select seq_decrescente_5.nextval from dual; NEXTVAL
5 SCOTT> / NEXTVAL
4 SCOTT> / NEXTVAL
3 SCOTT> / NEXTVAL
2 SCOTT> / NEXTVAL
1 SCOTT> / NEXTVAL
0 SCOTT> / NEXTVAL
5 Uma vez referenciada a pseudo-coluna NEXTVAL , o valor em CURRVAL é atualizado para bater com o valor de NEXTVAL , e o valor anterior em CURRVAL é descartado:
SCOTT> select seq_decrescente_5.currval from dual; CURRVAL
5 SCOTT> select seq_decrescente_5.nextval from dual; NEXTVAL
4 SCOTT> select seq_decrescente_5.currval from dual; CURRVAL
4 SCOTT> exit Desconectado de Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production Podemos perceber então que a pseudo-coluna NEXTVAL é usada para extrair números sucessivos de uma seqüência especificada. Será sempre necessário qualificar NEXTVAL com o nome da seqüência. Quando fazemos referência à NEXTVAL , um novo número de seqüência é gerado e o número de seqüência atual é colocado em CURRVAL. Vale a pena salientar que a pseudo-coluna CURRVAL é usada para fazer referência a um número de seqüência que a sessão do usuário atual acabou de gerar. Portanto, NEXTVAL deve ser usada para gerar um número de seqüência na sessão do usuário atual antes que seja feita referência a CURRVAL , caso contrário, o erro abaixo será emitido: C:>sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Seg Nov 3 15:39:28 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved. Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SCOTT> select seq_decrescente.currval from dual; select seq_decrescente.currval from dual
ERRO na linha 1: ORA-08002: a seqüência SEQ_DECRESCENTE.CURRVAL ainda não foi definido nesta sessão Quer dizer que se quisermos verificar o último valor de seqüência gerado, sempre teremos que inicializar a seqüência incrementado seu valor através da paseudo-coluna NEXTVAL? Não necessariamente. Caso tenhamos criado uma seqüência com a opção NOCACHE, poderemos verificar de forma precisa, qual foi o último valor de seqüência gerado e qual será o próximo valor a ser gerado, verificando o valor da coluna LAST_NUMBER nas views de dicionário de dados DBA/ALL/USER_SEQUENCES. Uma vez criada, a seqüência é documentada no dicionário de dados. Já que uma seqüência é um objeto de banco de dados, poderemos identificá-la nas views de dicionário de dados DBA/ALL/USER_OBJETCS, como também ver as definições da mesma nas views DBA/ALL/USER_SEQUENCES. O exemplo abaixo demonstra a diferença de utilizar a opção CACHE e NOCACHE: SCOTT> create sequence seq_cache; Seqüência criada. SCOTT> create sequence seq_nocache nocache; Seqüência criada.
1 linha criada. SCOTT> insert into emp values (seq_emp_id.nextval,'GUSTAVO'); 1 linha criada. SCOTT> select * from emp; ID NOME
1 ROBERTO 2 LAURA 3 GUSTAVO -- Verificando o valor de LAST_NUMBER SCOTT> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
SEQ_EMP_ID 1 1,0000E+27 1 N N 0 4 Após toda essa demonstração, irei agora criar uma trigger de banco de dados (Before Insert) na tabela EMP de forma que a coluna ID tenha seus valores gerados de forma automática (simulando um tipo de dado auto-incremento) ao inserir dados na mesma. -- Criando a trigger de banco de dados SCOTT> create or replace trigger gera_emp_id 2 before insert on emp 3 for each row 4 begin 5 select seq_emp_id.nextval into :new.id from dual; 6 end; 7 / Gatilho criado. Irei abaixo realizar algumas inserções na tabela EMP sem referenciar a coluna ID. SCOTT> insert into emp (nome) values ('RENATA'); 1 linha criada. SCOTT> insert into emp (nome) values ('CARLOS'); 1 linha criada. SCOTT> select * from emp; ID NOME
1 ROBERTO 2 LAURA 3 GUSTAVO 4 RENATA 5 CARLOS -- Verificando o valor de LAST_NUMBER SCOTT> select * from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
SEQ_EMP_ID 1 1,0000E+27 1 N N 0 6
E se quisermos alterar o valor atual da seqüência? Vale a pena salientar que esta ação somente será possível se a seqüência for dropada e recriada , pois não é possível alterar a propriedade START WITH com o comando ALTER SEQUENCE ... : SCOTT> alter sequence seq_emp_id start with 1; alter sequence seq_emp_id start with 1
ERRO na linha 1: ORA-02283: não é possível alterar o número inicial da seqüência Para quem interessar, foi disponibilizada uma stored procedure criada por Trevor Fairhurst que pode facilitar muito este trabalho. Abaixo, irei demonstrar como utilizá-la: -- Criando a stored procedure SET_SEQUENCE SCOTT> create or replace procedure set_sequence 2 (seqname in varchar2, newnumber in integer) as 3 curr_val integer; 4 curr_inc integer; 5 curr_min integer; 6 begin 7 select increment_by, min_value into curr_inc, curr_min from user_sequences 8 where sequence_name = seqname; 9 execute immediate 10 'alter sequence '||seqname||' minvalue '||least((newnumber-curr_inc- 1),curr_min); 11 execute immediate 'select '||seqname ||'.nextval from dual' into curr_val; 12 if (newnumber - curr_val - curr_inc) != 0 then 13 execute immediate 14 'alter sequence '||seqname||' increment by '||(newnumber-curr_val-curr_inc); 15 end if; 16 execute immediate 'select ' ||seqname ||'.nextval from dual' into curr_val; 17 execute immediate 'alter sequence ' ||seqname||' increment by ' || curr_inc; 18 end set_sequence; 19 / Procedimento criado. -- Alterando o valor atual para 1 SCOTT> exec set_sequence('SEQ_EMP_ID',1); Procedimento PL/SQL concluído com sucesso. SCOTT> select sequence_name,last_value from user_sequences; SEQUENCE_NAME LAST_NUMBER
SEQ_EMP_ID 1 -- Alterando o valor atual para 10
