Uma sequência é um contador, o que significa que emite números em uma série específica, sempre mantendo o controle de qualquer que seja o próximo número. Se você já foi a um estabelecimento que emite senha para atendimento e espera em uma fila, a sequence serve para o mesmo objetivo. O objetivo principal da sequence é oferecer suporte ao processo de inserção de linhas a uma tabela específica e fornecer o próximo valor exclusivo para uma PRIMARY KEY.
Não há nada inerente a um objeto sequence que o vincule a uma tabela específica. Não há nada automático no objeto sequence que necessariamente que diga que é referente a uma tabela. Cabe ao desenvolvedor do software saber como usar o objeto sequence corretamente.
Uma sequência é um objeto usado predominantemente para uma finalidade: gerar dados para colunas de chave primária em tabelas. Embora esse seja o objetivo principal de uma sequência, você pode usar para outro objetivo. Você pode usar uma sequência para gerar sequências numéricas por qualquer motivo. Mas tudo o que uma sequência faz é emitir números sequencialmente crescentes (ou decrescentes), de acordo com as regras que você define quando cria a sequência.
Criando uma Sequence
Aqui está uma amostra da instrução SQL para criar uma sequência:
CREATE SEQUENCE SEQ_ORDER_ID;
Este exemplo é uma instrução completa e representa a forma mais simples de uma instrução CREATE SEQUENCE. A sintaxe é a seguinte:
- A palavra chave obrigatória
CREATE SEQUENCE - Um nome obrigatório da sequência que você especificar, de acordo com as regras de nomeação de objetos de banco de dados.
Segue a instrução completa:
CREATE SEQUENCE [schema] sequence_name [sequence_option]
Existem várias opções de sequência que podem ser especificadas, separadas por espaços, conforme desejado. As sequências podem ser configuradas para iniciar em qualquer número e aumentar – ou diminuir – em qualquer número. Eles podem gerar sequencialmente sem cessar ou receber um intervalo dentro do qual geram continuamente novos números. Eles podem receber uma série fixa de números para gerar, depois deixam de gerar números. Logo abaixo vemos todas opções que podemos utilizar ao se criar uma sequence.
Todas opções possíveis para uma sequence
CREATE SEQUENCE [ schema. ] sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { KEEP | NOKEEP }
| { SESSION | GLOBAL }
]...
;
Na primeira linha podemos informar o schema que irá ser criado a sequence, caso não informe será criado em seu próprio schema. Especifique o nome da sequence a ser criada. O nome deve atender aos requisitos listados em “Regras de Nomenclatura de Objetos de Banco de Dados”.
Se você omitir as cláusulas seguintes, criará uma sequência crescente que começa com 1 e aumenta em 1 em 1 sem limite superior. Especificar apenas INCREMENT BY -1 cria uma sequência descendente que começa com -1 e diminui sem limite inferior caso não informe as cláusulas de limite.
- Para criar uma sequência que incrementa sem limite, para sequências ascendentes, omita o parâmetro MAXVALUE ou especifique NOMAXVALUE. Para sequências descendentes, omita o parâmetro MINVALUE ou especifique NOMINVALUE.
- Para criar uma sequência que pare em um limite predefinido, em uma sequência crescente, especifique um valor para o parâmetro MAXVALUE. Para uma sequência descendente, especifique um valor para o parâmetro MINVALUE. Se especificar NOCYCLE em sequências que há limites de geração de sequência então qualquer tentativa de gerar um número de sequência depois que a sequência atingir seu limite resultará em erro.
INCREMENT BY número = Cada novo número de sequência solicitado será incrementado por esse número. Esse valor pode ser qualquer número inteiro positivo ou negativo, mas não pode ser 0, se for um número negativo indica que a sequência diminuirá, se positivo irá aumentar. Se omitido, o incremento será de 1 em 1.
START WITH número = Isso especifica o primeiro número que iniciará a sequência. Se omitido, START WITH assume, caso informe, como padrão MINVALUE para sequências ascendentes ou MAXVALUE para sequências descendentes, a menos que NOMINVALUE ou NOMAXVALUE sejam especificados explícita ou implicitamente (por padrão), sendo assim START WITH assume como padrão 1 para sequência positiva e -1 para sequência negativa.
MAXVALUE número = Isso especifica o número máximo para a sequência. Se omitido, NOMAXVALUE é assumido.
NOMAXVALUE = Isso especifica que não há MAXVALUE especificado.
MINVALUE número = Isso especifica o número mínimo para a sequência. Se omitido, NOMINVALUE será assumido, a menos que um MINVALUE seja exigido pela presença de CYCLE, nesse caso o padrão é 1. MINVALUE deve ser menor ou igual a START WITH e deve ser menor que MAXVALUE.
NOMINVALUE = Especifique NOMINVALUE para indicar um valor mínimo de 1 para uma sequência ascendente ou -99999999999999999999999999 (27 noves) para uma sequência descendente. NOMINVALUE é o padrão.
CYCLE = Quando o gerador de sequência atingir uma extremidade de seu intervalo, reinicie na outra extremidade. Em outras palavras, em uma sequência crescente, quando o valor gerado atingir o MAXVALUE, o próximo número gerado será o MINVALUE. Em uma sequência descendente, quando o valor gerado atingir MINVALUE, o número gerado será MAXVALUE.
NOCYCLE = Especifique NOCYCLE para indicar que a sequência não pode gerar mais valores depois de atingir seu valor máximo ou mínimo. NOCYCLE é o padrão.
CACHE = Especifique quantos valores da sequência o banco de dados pré-aloca e mantém na memória para acesso mais rápido. Esse valor inteiro pode ter 28 ou menos dígitos. O valor mínimo para esse parâmetro é 2. Para seqüências que alternam, esse valor deve ser menor que o número de valores no ciclo. Você não pode armazenar em cache mais valores do que cabem em um determinado ciclo de números de sequência. Portanto, o valor máximo permitido para o CACHE deve ser menor que o valor determinado pela seguinte fórmula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
NOCACHE = Especifique NOCACHE para indicar que os valores da sequência não estão pré-alocados. Se você omitir CACHE e NOCACHE, o banco de dados armazenará em cache 20 números de sequência por padrão.
ORDER = Especifique ORDER para garantir que os números de sequência sejam gerados na ordem do pedido. Esta cláusula é útil se você estiver usando os números de sequência como registros de data e hora. A garantia da ordem geralmente não é importante para as sequências usadas para gerar chaves primárias. ORDER é necessário apenas para garantir a geração ordenada se você estiver usando o Oracle Real Application Clusters. Se você estiver usando o modo exclusivo, os números de sequência serão sempre gerados em ordem.
NOORDER = Especifique NOORDER se não desejar garantir que os números de sequência sejam gerados na ordem do pedido. NOORDER é o padrão.
KEEP = Especifique KEEP se desejar que o NEXTVAL retenha seu valor original durante a reprodução para Continuidade de aplicativo. Esse comportamento ocorrerá apenas se o usuário que está executando o aplicativo for o proprietário do esquema que contém a sequência. Esta cláusula é útil para fornecer consistência de variável de ligação na reprodução após erros recuperáveis.
NOKEEP = Especifique NOKEEP se não desejar que o NEXTVAL retenha seu valor original durante a reprodução para Continuidade de aplicativo. NOKEEP é o padrão.
SESSION = Especifique SESSION para criar uma sequência de sessões, que é um tipo especial de sequência projetado especificamente para ser usado com tabelas temporárias globais que possuem visibilidade de sessão. Diferentemente das sequências regulares existentes (chamadas de sequências “globais” para fins de comparação), uma sequência de sessões retorna um intervalo exclusivo de números de sequências somente dentro de uma sessão, mas não entre as sessões. Outra diferença é que as sequências de sessões não são persistentes. Se uma sessão desaparecer, o estado das sequências da sessão que foram acessadas durante a sessão também mudará. As sequências de sessão devem ser criadas por um banco de dados de leitura e gravação, mas podem ser acessadas em qualquer banco de dados de leitura-gravação ou somente leitura (um banco de dados regular temporariamente aberto somente para leitura ou um banco de dados em espera). As cláusulas CACHE, NOCACHE, ORDER ou NOORDER são ignoradas quando especificadas com a cláusula SESSION.
GLOBAL = Especifique GLOBAL para criar uma sequência global ou regular. GLOBAL é o padrão. Quer dizer que outras sessões visualizam a sequence criada.
Excluindo uma sequência
Para excluir uma sequência do banco de dados use o comando abaixo:
DROP SEQUENCE SEQ_ORDER_ID;
Exemplos de criação de sequências
Vejamos um novo exemplo de criação de SEQUENCES:
CREATE SEQUENCE SEQ_ORDER_ID START WITH 1 INCREMENT BY 1;
Essas opções passadas são opções padrão, ou seja, passando dessa maneira ou omitindo vai funcionar da mesma forma, a opção padrão é START WITH 1 INCREMENT BY 1. Você pode alterar os parâmetros padrões se desejar:
CREATE SEQUENCE SEQ_ORDER_ID START WITH 10 INCREMENT BY 5;
Esta declaração começará com o número 10 e aumentará cada número sucessivo de 5 em 5.
Usando sequências
Agora que criamos uma sequência, o que fazemos com ela? Aqui está um exemplo:
INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, CUSTOMER_ID) VALUES (SEQ_ORDER_ID.NEXTVAL, SYSDATE, 28);
Nesta instrução INSERT de exemplo, inserimos uma linha na tabela ORDERS que consiste em três valores. O primeiro valor é SEQ_ORDER_ID.NEXTVAL. Esta referência é ao gerador de sequência SEQ_ORDER_ID junto com sua pseudocoluna NEXTVAL, que executa as duas tarefas a seguir:
- Avança o gerador de sequência para o próximo número disponível.
- Retorna esse valor
Se o gerador de sequência SEQ_ORDER_ID acabou de ser criado e se ele foi criado com os valores padrão para START WITH e INCREMENT BY, a chamada inicial para NEXTVAL obterá o valor inicial de 1. Se a próxima chamada para o gerador de sequência também for uma chamada para a pseudocoluna NEXTVAL, ela será avançada novamente em 1 para um valor de 2. Todos os geradores de sequência têm duas pseudocolunas NEXTVAL e CURRVAL:
NEXTVAL Isso incrementa a sequência para o próximo número, de acordo com as diretivas originais CREATE SEQUENCE da sequência. Ele também retorna o número recém-incrementado.
CURRVAL Exibe o número atual que a sequência está mantendo. No entanto, essa chamada é válida apenas dentro de uma sessão na qual a pseudocoluna NEXTVAL já foi chamada. Você não pode usar CURRVAL na sua chamada inicial para qualquer gerador de sequência dentro de uma sessão.
Aqui estão alguns pontos importantes a serem lembrados sobre sequências:
- Você não pode chamar CURRVAL em sua primeira referência a uma sequência dentro de uma determinada sessão.
- NEXTVAL deve ser a primeira referência. Se você tentar executar uma instrução como um INSERT que inclua a referência de sequência NEXTVAL, o gerador de sequência avançará para o próximo número, mesmo se a instrução INSERT falhar.
- Você não pode chamar CURRVAL ou NEXTVAL na cláusula DEFAULT de uma instrução CREATE TABLE ou ALTER TABLE.
- Você não pode chamar CURRVAL ou NEXTVAL na subconsulta de uma instrução CREATE VIEW ou de uma instrução SELECT, UPDATE ou DELETE.
- Em uma instrução SELECT, você não pode combinar CURRVAL ou NEXTVAL com um operador DISTINCT.
- Você não pode chamar CURRVAL ou NEXTVAL na cláusula WHERE de uma instrução SELECT.
- Você não pode usar CURRVAL ou NEXTVAL em uma restrição CHECK.
- Você não pode combinar CURRVAL ou NEXTVAL com os operadores definidos UNION, INTERSECT e MINUS.
- Você pode chamar uma pseudocoluna de sequência de qualquer lugar dentro de uma instrução SQL e usar qualquer expressão.
Esse último ponto é importante – uma referência a uma sequência deve incluir sua pseudocoluna, e essa referência é considerada uma expressão válida ou um componente de uma expressão. Portanto, supondo que você esteja trabalhando com uma tabela PROJECTS que possui uma coluna PROJECT_COST, você pode chamar uma sequência SEQ_PROJ_COST assim:
SELECT PROJECT_COST / (3 * SEQ_PROJ_COST.NEXTVAL) FROM PROJECTS;
Essa é uma sintaxe válida. Se é útil ou não, depende de suas regras comerciais. Mas o SQL reconhece essa sintaxe e a executará com sucesso. A conclusão é que as referências às pseudocolunas de sequências são expressões válidas.
Qualquer instrução SQL executada com uma chamada para a pseudocoluna NEXTVAL de uma sequência avançará a sequência, mesmo se a instrução SQL falhar. Nem mesmo um ROLLBACK redefinirá uma sequência; mesmo o valor de CURRVAL do gerador de sequência permanecerá o mesmo após um ROLLBACK.
Exemplos práticos
Essa é uma sequência positiva, pois ao omitir INCREMENT BY ele terá o valor default 1, ou seja aumenta de 1 em 1, quando uma sequência é positiva então se não informarmos MINVALUE ou MAXVALUE teremos o valor para ambos 1 até 999999999…. não foi possível criar a sequence acima pois o START WITH é um valor que não está compreendido no intervalo entre MINVALUE e MAXVALUE. Para contornar esse problema veja o exemplo abaixo:
O comando acima cria uma sequência que inicia em -1 com um valor mínimo de -3 que é necessário informar um MINVALUE. Quando executar cont.nextval irá gerar o resultado = -1, pois é o valor do START WITH (inicia com), ok.. iniciou com -1 e ao executarmos novamente vai pra qual número? vai pra -2? A resposta é NÃO vai pra Zero número 0, isso ocorre pois não foi informado a opção INCREMENT BY ao criar a sequence, e quando isso ocorre o INCREMENT BY gera o valor padrão 1, sendo assim incrementa o número de 1 em 1, por isso o -1 vai para zero.
No exemplo das imagens abaixo temos na primeira figura a tentativa de criação de uma sequence, depois a sequence criada e suas respectivas execuções:
No exemplo abaixa criamos uma sequence com o nome cont_negativo com valor inicial negativo -1 e incrementa por -1, a primeira vez que executa o cont_negativo usando CURRVAL dá erro, pois não podemos usar CURRVAL da primeira vez de uma sequence, depois executamos com NEXTVAL, dessa forma o valor foi de -1, que é o valor definido por START WITH, ao executar novamente foi para -2, pois o INCREMENT BY informado incrementa em -1.
Smell sequence
Referência
OCA Oracle Database SQL Exam. Páginas: 517 – 522
Oracle Database SQL Reference – Create Sequence
desenvoracle.home.blog 