AutoNumber in Oracle

2:18 AM database, oracle No Comments

Basically, Oracle does not have facilities like Numer Auto AutoIncrement in SQLServer or mySQL. But could diakalin using a combination of sequence and trigger. And the following example:

1. CREATE SEQUENCE nama-sequence;
2. CREATE OR REPLACE TRIGGER nama-trigger
3. BEFORE INSERT ON nama-tabel
4. FOR EACH ROW
5. WHEN (NEW.nama-field IS NULL OR NEW.nama-field = ‘’)
6. BEGIN
7. SELECT ‘PR-’ || nama-sequence.NEXTVAL INTO :NEW.nama-field FROM DUAL;
8. END;
9. /

Or it could be in the following ways:

1. CREATE TABLE contoh (
2. latihan_id INTEGER PRIMARY KEY,
3. );
4.
5. CREATE SEQUENCE contoh_seq;
6. CREATE TRIGGER contoh_seq_trigger
7. BEFORE INSERT ON contoh FOR EACH ROW
8.
9. BEGIN
10. IF (:new.latihan_id IS NULL) THEN
11. SELECT contoh_seq.nextval INTO :new.latihan_id
12. FROM DUAL;
13. END IF;
14. END;
15. /