| Dependent Objects |
| seq$ |
|
|
|
dba_sequences |
all_sequences |
user_sequences |
|
| Privilegi di sistema richiesti (meglio provare come
utente SYSTEM) |
create sequence
create any sequence
alter any sequence
drop any sequence
select any sequence |
|
Metodo 1: prevede una tabella che genera i numeri. Non usa le funzionalità delle
sequenze di ORACLE, ma l'idea di bloccare la tabella fino all'incremento successivo
è interessante: ci sarà sempre un valore nuovo, anche in casi di elevata concorrenza. |
Esempio di tabella:
CREATE TABLE seqnum (
next_number NUMBER(1);
1. Blocco della tabella seqnum per la durata della transazione.
2. SELECT next_number FROM seqnum;
3. UPDATE seqnum SET next_number=next_number+1;
4. Sblocco della tabella seqnum per la prossima transazione. |
| Creazione scenario |
Creare queste quattro tabelle per avere lo scenario su cui testare gli esempi di
questo documento. |
CREATE TABLE campus_site (
site_id NUMBER(4),
organization_name VARCHAR2(40),
campus_name VARCHAR2(30),
address_id NUMBER(10));
CREATE TABLE division (
division_id NUMBER(5),
site_id NUMBER(4),
division_name VARCHAR2(40),
address_id NUMBER(10));
CREATE TABLE department (
department_id NUMBER(5),
division_id NUMBER(5),
department_name VARCHAR2(40),
address_id NUMBER(10));
CREATE TABLE seq_test (
test NUMBER(10)); |
| |
| Create Sequence |
Sintassi Create Sequence |
CREATE SEQUENCE <sequence_name>
INCREMENT BY <integer>
START WITH <integer>
MAXVALUE <integer> / NOMAXVALUE
MINVALUE <integer> / NOMINVALUE
CYCLE / NOCYCLE
CACHE <#> / NOCACHE
ORDER / NOORDER; |
Create Sequence Simplest Form
(Sintassi e query di esempio) |
CREATE SEQUENCE <sequence_name>; |
CREATE SEQUENCE seq_campus_site_id;
SELECT seq_campus_site_id.NEXTVAL FROM dual;
/
/ |
Utilizzo dell'Autonumber con Sequence |
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.NEXTVAL); |
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of
Washington', 'Main Seattle');
SELECT *
FROM campus_site;
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_campus_site_id.NEXTVAL, 'Univ. of Washington', 'Bothell');
SELECT *
FROM campus_site; |
Utilizzo dell'Autonumber con Sequence in presenza di due tabelle
-
Viene creata la sequenza;
-
Viene inserito un nuovo campus utilizzando la sequenza seq_division_id.
NEXTVAL restituisce il numero successivo della sequenza e
lo ritiene utilizzato. Alla prossima richiesta restituirà un nuovo valore.
-
Viene inserita una nuova divisione utilizzando CURRVAL per avere il valore ottenuto
dalla precedente chiamata a NEXTVAL.
|
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.CURRVAL); |
CREATE SEQUENCE seq_division_id;
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_division_id.NEXTVAL, 'Università di Napoli', 'CampoVerde');
INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_division_id.NEXTVAL, seq_campus_site_id.CURRVAL, 'Informatica');
SELECT *
FROM campus_site;
SELECT *
FROM division; |
Esempio di utilizzo di START WITH e un problema risolto con
CURRVAL.
|
CREATE SEQUENCE <sequence_name> START WITH <integer>; |
CREATE SEQUENCE seq_audit_tx START WITH 297;
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Università di Salerno', 'CampoBlu');
INSERT INTO division
(division_id, site_id, division_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Scienze');
INSERT INTO department
(department_id, division_id, department_name)
VALUES
(seq_audit_tx.NEXTVAL, seq_audit_tx.CURRVAL, 'Matematica');
SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department;
ROLLBACK;
INSERT INTO campus_site
(site_id, organization_name, campus_name)
VALUES
(seq_audit_tx.NEXTVAL, 'Università di Salerno', 'CampoBlu');
INSERT INTO division
(site_id, division_id, division_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Scienze');
INSERT INTO department
(division_id, department_id, department_name)
VALUES
(seq_audit_tx.CURRVAL, seq_audit_tx.NEXTVAL, 'Matematica');
SELECT * FROM campus_site;
SELECT * FROM division;
SELECT * FROM department; |
INCREMENT BY |
CREATE SEQUENCE <sequence_name> INCREMENT BY <positive integer>; |
CREATE SEQUENCE seq_inc_by_two INCREMENT BY 2;
INSERT INTO seq_test VALUES (seq_inc_by_two.NEXTVAL);
/
/
SELECT * FROM seq_test;
CREATE SEQUENCE seq_inc_by_ten INCREMENT BY 10;
INSERT INTO seq_test VALUES (seq_inc_by_ten.NEXTVAL);
/
/
SELECT * FROM seq_test;
ALTER TABLE seq_test ADD test2 NUMBER(10);
desc seq_test
INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.NEXTVAL);
SELECT * FROM seq_test;
INSERT INTO seq_test
(test, test2)
VALUES
(seq_inc_by_ten.NEXTVAL, seq_inc_by_ten.CURRVAL);
SELECT * FROM seq_test; |
DECREMENT BY |
CREATE SEQUENCE <sequence_name>
MAX VALUE <integer value>
INCREMENT BY <negative integer>; |
CREATE SEQUENCE seq_reverse INCREMENT BY -5;
ALTER TABLE seq_test DROP COLUMN test2;
INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/
SELECT *
FROM seq_test;
DROP SEQUENCE seq_reverse;
CREATE SEQUENCE seq_reverse MAXVALUE 150
START WITH 150 INCREMENT BY -5;
INSERT INTO seq_test VALUES (seq_reverse.NEXTVAL);
/
/
/
SELECT *
FROM seq_test; |
MAXVALUE. Valore massimo che può raggiungere un valore ad incremento. |
CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer>; |
CREATE SEQUENCE seq_maxval START WITH 1 MAXVALUE 5;
INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
/
/
/
SELECT *
FROM seq_test;
INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL);
SELECT *
FROM seq_test;
INSERT INTO seq_test VALUES (seq_maxval.NEXTVAL); |
CACHE |
CREATE SEQUENCE <sequence_name> CACHE <integer>; |
CREATE SEQUENCE seq_cache CACHE
100;
SELECT sequence_name, last_number
FROM user_sequences;
SELECT seq_cache.NEXTVAL FROM dual;
SELECT sequence_name, last_number
FROM user_sequences;
SELECT seq_cache.NEXTVAL FROM dual;
/
SELECT sequence_name, last_number
FROM user_sequences;
conn / as sysdba
shutdown abort;
startup
conn uwclass/uwclass
SELECT sequence_name, last_number
FROM user_sequences;
SELECT seq_cache.NEXTVAL FROM dual; |
CYCLE |
CREATE SEQUENCE <sequence_name> START WITH <integer>
MAXVALUE <integer> CYCLE; |
CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE;
-- default cache is 20
CREATE SEQUENCE seq_cycle START WITH 1 MAXVALUE 5 CYCLE CACHE 4;
TRUNCATE TABLE seq_test;
INSERT INTO seq_test VALUES (seq_cycle.NEXTVAL);
/
/
/
/
/
/
SELECT * FROM seq_test; |
ORDER |
CREATE SEQUENCE <sequence_name> START WITH 1 ORDER; |
|
CREATE SEQUENCE seq_order START WITH 1 ORDER; |
| |
| Alter Sequence |
| Change Increment (Cambio dell'incremento) |
ALTER SEQUENCE
<sequence_name> INCREMENT BY <integer>; |
| ALTER SEQUENCE seq_inc_by_ten
INCREMENT BY 20; |
|
Cambio Max Value |
ALTER SEQUENCE <sequence_name> MAX VALUE <integer> |
| ALTER SEQUENCE
seq_maxval MAXVALUE
10; |
|
Cambio Cycle |
ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE> |
| ALTER SEQUENCE
seq_cycle NOCYCLE; |
|
Cambio Cache |
ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE |
| ALTER SEQUENCE seq_cache NOCACHE; |
|
Cambio Order |
ALTER SEQUENCE <sequence_name> <ORDER | NOORDER> |
| ALTER SEQUENCE
seq_order NOORDER; |
| |
| Drop Sequence |
| Drop Sequence |
DROP SEQUENCE <sequence_name>; |
| DROP SEQUENCE seq_cache; |
| |
| Utilizzo di Sequence |
In una query di inserimento |
CREATE TABLE t (
col1 NUMBER(5),
col2 NUMBER(5));
CREATE SEQUENCE seq;
INSERT INTO t
(col1, col2)
VALUES
(seq.NEXTVAL,
seq.CURRVAL);
/
/
SELECT * FROM t; |
PL/SQL standard |
set serveroutput on
DECLARE
i NATURAL;
j NATURAL;
BEGIN
SELECT seq.NEXTVAL, seq.CURRVAL
INTO i, j
FROM dual;
dbms_output.put_line(i);
dbms_output.put_line(j);
END;
/
/ |
PL/SQL 11g |
set serveroutput on
BEGIN
dbms_output.put_line(seq.NEXTVAL);
dbms_output.put_line(seq.CURRVAL);
END;
/
/ |
| |
| Query sulle Sequence |
|
Ultimo
numero selezionato dalla sequenza |
SELECT sequence_name, last_number
FROM user_sequences; |
|
Prossimo numero della sequenza |
SELECT sequence_name, (last_number + increment_by) NEXT_VALUE
FROM user_sequences; |
