Séquences

Résumé

CREATE SEQUENCE dept_deptno
    INCREMENT BY 1
    START WITH 91
    MAXVALUE 100
    NOCACHE
    NOCYCLE;

INSERT INTO dept(deptno, dname, loc)
    VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO');

SELECT dept_deptno.CURRVAL FROM dual;

SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

Séquence commune à 2 tables

drop table employe;
drop table stagiaire;

create table employe(
    id_employe int,
    nom_employe varchar2(20)
);

create table stagiaire(
    id_stagiaire int,
    nom_stagiaire varchar2(20),
    statut_stagiaire char(1)
);

create sequence personne_seq;

insert into employe values(personne_seq.nextval, 'Employé 1');
insert into stagiaire values(personne_seq.nextval, 'Stagiaire 1', 'A');
insert into stagiaire values(personne_seq.nextval, 'Stagiaire 2', 'B');
insert into employe values(personne_seq.nextval, 'Employé 2');

select * from employe;

select * from stagiaire;

select id_employe "id", nom_employe "nom"
from employe
union
select id_stagiaire, nom_stagiaire
from stagiaire;

Auto increment

drop table books;
CREATE TABLE books (
  id      NUMBER        GENERATED BY DEFAULT ON NULL AS IDENTITY,
  title   VARCHAR2(100) NOT NULL
);

-- Ne marche pas
-- (mais marcherait sous postgres ou mysql)
insert into books values('test');

insert into books (title) values('test1');
select * from books;
insert into books (id,title) values(null, 'test2');
select * from books;
insert into books (id,title) values(3, 'test3');
select * from books;

-- Insere de nouveau un livre id = 3
insert into books (title) values('test4');
select * from books;