Oracle 19c Sequance yaratmadan Auto Number Verme

Eskiden bir kolona sequence üretip her defasında nextval ile alıp kullanıyorduk.

Oracle bu tip kolonu database seviyesinde üretme özelliğini getirdi. Yani sequence yaratmanıza gerek kalmıyor.

 

1 ) GENERATED ALWAYS AS IDENTITY  è alttaki gibi kendi sequenceten üretiyor sen kolona değer atamıyorsun

CREATE TABLE identity_demo (id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2(100) NOT NULL);

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with GENERATED ALWAYS');

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with GENERATED ALWAYS');

-- INSERT INTO identity_demo(id,description) VALUES(2, 'Oracle identity column example with GENERATED ALWAYS ');

 

1	Oracle identity column demo with GENERATED ALWAYS
2	Oracle identity column demo with GENERATED ALWAYS

 

2 ) GENERATED BY DEFAULT AS IDENTITY è kendi sequenceten üretiyor ama sen değer de atayabiliyorsun

CREATE  TABLE identity_demo (id NUMBER GENERATED BY DEFAULT AS IDENTITY,  description VARCHAR2(100) not null);

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with GENERATED BY DEFAULT');

INSERT INTO identity_demo(id,description) VALUES(2, 'Oracle identity column example with GENERATED BY DEFAULT');

--INSERT INTO identity_demo(id,description) VALUES(NULL, 'Oracle identity column demo with GENERATED BY DEFAULT, NULL value');

 

1	Oracle identity column demo with GENERATED BY DEFAULT
2	Oracle identity column example with GENERATED BY DEFAULT

 

3 ) GENERATED DEFAULT ON NULL AS IDENTITY è kendi sequenceten üretiyor ama sen değer de atayabiliyorsun. NULL atarsan kendi ürettiğini atıyor

CREATE  TABLE identity_demo  (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, description VARCHAR2(100) not null);

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with no value');

INSERT INTO identity_demo(id,description) VALUES(NULL, 'Oracle identity column demo with null');

INSERT INTO identity_demo(id,description) VALUES(3, 'Oracle identity column demo with null');

 

1	Oracle identity column demo with no value
2	Oracle identity column demo with null
3	Oracle identity column demo with null

 

4 ) GENERATED DEFAULT ON NULL AS IDENTITY START WITH è Belli bir sayıdan başlayarak kendi sequenceten üretiyor ama sen değer de atayabiliyorsun. NULL atarsan kendi ürettiğini atıyor

CREATE  TABLE identity_demo  (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 100, description VARCHAR2(100) not null  );

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with START WITH option');

INSERT INTO identity_demo(id,description) VALUES(null,'Oracle identity column demo with START WITH option');

INSERT INTO identity_demo(id,description) VALUES(102,'Oracle identity column demo with START WITH option');

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo with START WITH option');

 

100	Oracle identity column demo with START WITH option
101	Oracle identity column demo with START WITH option
102	Oracle identity column demo with START WITH option
102	Oracle identity column demo with START WITH option

 

5 ) GENERATED DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10è Belli bir sayıdan başlayarak kendi sequenceten belli bir aralıkla değer üretiyor ama sen değer de atayabiliyorsun.NULL atarsan kendi ürettiğini atıyor.

CREATE  TABLE identity_demo (id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY START WITH 10 INCREMENT BY 10, description VARCHAR2(100) not null);

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo 1 with INCREMENT BY option');

INSERT INTO identity_demo(id,description) VALUES(null,'Oracle identity column demo 2 with INCREMENT BY option');

INSERT INTO identity_demo(id,description) VALUES(11,'Oracle identity column demo 2 with INCREMENT BY option');

INSERT INTO identity_demo(description) VALUES('Oracle identity column demo 3 with INCREMENT BY option');

 

10	Oracle identity column demo 1 with INCREMENT BY option
20	Oracle identity column demo 2 with INCREMENT BY option
11	Oracle identity column demo 2 with INCREMENT BY option
30	Oracle identity column demo 3 with INCREMENT BY option