Reset de sequências

Há já algum tempo precisei de fazer um reset a uma sequência.
Não tendo encontrado nenhum método da Oracle que cumprisse o propósito, escrevi um procedimento.

Para quem tiver a mesma necessidade e ainda não possua um método melhor de o fazer, deixo aqui uma cópia do procedimento:

/**
 * Altera o valor de uma sequência
 *
 * @criacao 2006-05-15 Adriano Teixeira
 * @param pv_nome_seq      Nome da sequência a ser alterada
 * @param pi_valor_inicial Valor que desejamos colocar na sequência
**/

PROCEDURE seq_reset (
  pv_nome_seq        IN   VARCHAR2,
  pi_valor_inicial   IN   PLS_INTEGER
)
AS
  li_val      PLS_INTEGER;
  lv_inc_by   VARCHAR2 (15);
  li_increment_by PLS_INTEGER;
BEGIN
  -- Guarda o valor do INCREMENT BY
  EXECUTE IMMEDIATE 'SELECT increment_by FROM user_sequences WHERE sequence_name = ' 
                    || pv_nome_seq INTO li_increment_by;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || ' MINVALUE 0';
  
  EXECUTE IMMEDIATE 'SELECT ' || pv_nome_seq || '.NEXTVAL FROM dual'
               INTO li_val;
               
  -- Verificar qual o deslocamento necessário para se chegar ao valor desejado
  li_val := li_val - pi_valor_inicial + 1;
  
  -- Tratar a possibilidade de ser necessário colocar valores negativos
  IF li_val < 0
  THEN
     lv_inc_by := ' INCREMENT BY ';
     li_val := ABS (li_val);
  ELSE
     lv_inc_by := ' INCREMENT BY -';
  END IF;
  
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || lv_inc_by
                    || li_val;
  
  EXECUTE IMMEDIATE 'SELECT ' || pv_nome_seq || '.NEXTVAL FROM dual'
               INTO li_val;
               
  -- Colocar o Increment no valor desejado de novo
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pv_nome_seq || ' INCREMENT BY ' 
                    || TO_CHAR(li_increment_by);
END seq_reset;

Desmistificar comportamentos: NOT IN

Há uns dias, ocorreu, com um colega meu, uma situação aparentemente estranha.

Vamos ter como base as seguintes tabelas:

CREATE TABLE t1
(c11 NUMBER NOT NULL,
c12 NUMBER)
/
ALTER TABLE t1
ADD CONSTRAINT t1_pk PRIMARY KEY (c11)
/

CREATE TABLE t2
(c21 NUMBER NOT NULL,
c22 NUMBER)
/
ALTER TABLE t2
ADD CONSTRAINT t2_pk PRIMARY KEY (c21)
/

Com os seguintes dados:

INSERT INTO T1(C11, C12) VALUES(11, 1);
INSERT INTO T1(C11, C12) VALUES(12, 2);
INSERT INTO T1(C11, C12) VALUES(13, 3);
INSERT INTO T1(C11, C12) VALUES(15, 5);

INSERT INTO T2(C21, C22) VALUES(21, 1);
INSERT INTO T2(C21, C22) VALUES(22, 2);
INSERT INTO T2(C21, C22) VALUES(23, 3);
INSERT INTO T2(C21, C22) VALUES(24, 4);
INSERT INTO T2(C21, C22) VALUES(25, NULL);

A situação estranha passou-se com uma query que vou simplificar, na forma de:

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(Devemos ignorar a existência de melhores formas de atingir os objectivos desta query)

O problema é que a execução desta query resulta em.. zero resultados.
Este é, de facto, um resultado estranho; esperar-se-ia um registo: (15, 5).

Pelos vistos esta situação está documentada, pelo que, quem ler a documentação, esperará que, se na sub-query houver registos NULL, então o NOT IN retorne sempre zero registos.

Situação análoga, não aconteceria se a query fosse:

SELECT *
FROM t1
WHERE c12 IN (SELECT c22
FROM t2);

Nesta, os resultados obtidos coincidem com os esperados: ((11, 1), (12, 2), (13, 3)).

Tudo bem, mas o que interessa agora é: Porquê?

A suspeita aponta para a noção de NULL misturada com os conceitos associados à execução das operações IN e NOT IN.

Assim, temos os seguintes princípios:

- A comparação de qualquer elemento com NULL, nunca retorna TRUE em Oracle;
- Y IN (X1, X2) => Y = X1 OR Y = X2;
- Y NOT IN (X1, X2) => NOT (Y = X1 OR Y = X2) => Y <> X1 AND Y <> X2;
- X1 OR X2 = TRUE, X1 = TRUE, X2 = NULL
- X1 AND X2 = FALSE, X1 = TRUE, X2 = NULL

Ou seja, se aplicarmos os valores das querys aos princípios enumerados, temos que:
- Na versão NOT IN, basta que exista um NULL para que a condição em que ele ocorre se torne falsa, o que corresponde a um FALSE em toda a expressão (uma vez que FALSE é absorvente para o AND).
- Na versão IN, basta que exista uma condição que avalie TRUE, para que a expressão avalie TRUE também (já que TRUE é absorvente para o OR).

Assim, o resultado da query inicial

SELECT *
FROM t1
WHERE c12 NOT IN (SELECT c22
FROM t2);

(que é, portanto, zero resultados) deixa de ser estranho e passa a ser lógico.

Como começou...

Era uma vez...
Um conjunto de pessoas que trabalhavam com Oracle e que um dia acordaram que podia ser interessante registarem algum conhecimento de forma persistente: este blog tenta ser o local onde todos possam ser professores e alunos.
E espero que sejam felizes para sempre...