Come creare un ID con AUTO_INCREMENT su Oracle?

Sembra che non ci sia alcun concetto di AUTO_INCREMENT in Oracle, fino alla versione 11g inclusa.

Come posso creare una colonna che si comporta come l’incremento automatico in Oracle 11g?

Non esistono colonne “auto_increment” o “identity” in Oracle a partire da Oracle 11g . Tuttavia, puoi modellarlo facilmente con una sequenza e un trigger:

Definizione tabella:

 CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq START WITH 1; 

Definizione trigger:

 CREATE OR REPLACE TRIGGER dept_bir BEFORE INSERT ON departments FOR EACH ROW BEGIN SELECT dept_seq.NEXTVAL INTO :new.id FROM dual; END; / 

AGGIORNARE:

IDENTITY colonna IDENTITY è ora disponibile su Oracle 12c:

 create table t1 ( c1 NUMBER GENERATED by default on null as IDENTITY, c2 VARCHAR2(10) ); 

oppure specificare i valori iniziali e incrementali, evitando anche qualsiasi inserimento nella colonna Identity ( GENERATED ALWAYS ) (di nuovo, solo Oracle 12c +)

 create table t1 ( c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1), c2 VARCHAR2(10) ); 

SYS_GUID restituisce un GUID, un ID univoco globale. Un SYS_GUID è un RAW(16) . Non genera un valore numerico incrementale.

Se si desidera creare un tasto numerico incrementale, è necessario creare una sequenza.

 CREATE SEQUENCE name_of_sequence START WITH 1 INCREMENT BY 1 CACHE 100; 

Dovresti quindi utilizzare quella sequenza nella tua istruzione INSERT

 INSERT INTO name_of_table( primary_key_column, <> ) VALUES( name_of_sequence.nextval, <> ); 

Oppure puoi definire un trigger che popola automaticamente il valore della chiave primaria usando la sequenza

 CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN SELECT name_of_sequence.nextval INTO :new.primary_key_column FROM dual; END; 

Se si utilizza Oracle 11.1 o versione successiva, è ansible semplificare un po ‘il trigger

 CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN :new.primary_key_column := name_of_sequence.nextval; END; 

Se vuoi davvero usare SYS_GUID

 CREATE TABLE table_name ( primary_key_column raw(16) default sys_guid() primary key, <> ) 

In Oracle 12c in poi potresti fare qualcosa del tipo,

 CREATE TABLE MAPS ( MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, MAP_NAME VARCHAR(24) NOT NULL, UNIQUE (MAP_ID, MAP_NAME) ); 

E in Oracle (Pre 12c).

 -- create table CREATE TABLE MAPS ( MAP_ID INTEGER NOT NULL , MAP_NAME VARCHAR(24) NOT NULL, UNIQUE (MAP_ID, MAP_NAME) ); -- create sequence CREATE SEQUENCE MAPS_SEQ; -- create tigger using the sequence CREATE OR REPLACE TRIGGER MAPS_TRG BEFORE INSERT ON MAPS FOR EACH ROW WHEN (new.MAP_ID IS NULL) BEGIN SELECT MAPS_SEQ.NEXTVAL INTO :new.MAP_ID FROM dual; END; / 

Ecco tre gusti:

  1. numerico . Semplice valore numerico crescente, ad es. 1,2,3, ….
  2. GUID . identificatore universale globale, come un tipo di dati RAW .
  3. GUID (stringa) . Come sopra, ma come una stringa che potrebbe essere più facile da gestire in alcune lingue.

x è la colonna Identity. Sostituire FOO con il nome della tabella in ciascuno degli esempi.

 -- numerical identity, eg 1,2,3... create table FOO ( x number primary key ); create sequence FOO_seq; create or replace trigger FOO_trg before insert on FOO for each row begin select FOO_seq.nextval into :new.x from dual; end; / -- GUID identity, eg 7CFF0C304187716EE040488AA1F9749A -- use the commented out lines if you prefer RAW over VARCHAR2. create table FOO ( x varchar(32) primary key -- string version -- x raw(32) primary key -- raw version ); create or replace trigger FOO_trg before insert on FOO for each row begin select cast(sys_guid() as varchar2(32)) into :new.x from dual; -- string version -- select sys_guid() into :new.x from dual; -- raw version end; / 

aggiornare:

Oracle 12c introduce queste due varianti che non dipendono dai trigger:

 create table mytable(id number default mysequence.nextval); create table mytable(id number generated as identity); 

Il primo usa una sequenza nel modo tradizionale; il secondo gestisce internamente il valore.

Supponendo che tu intenda una colonna come la colonna Identity di SQL Server?

In Oracle, si utilizza SEQUENCE per ottenere la stessa funzionalità. Vedrò se riesco a trovare un link valido e pubblicarlo qui.

Aggiornamento: sembra che tu l’abbia trovato da solo. Ecco il link comunque: http://www.techonthenet.com/oracle/sequences.php

Oracle Database 12c ha introdotto Identity, una colonna auto-incrementale (generata dal sistema). Nelle precedenti versioni del database (fino a 11g), di solito si implementa un’Ident creando una sequenza e un trigger. Da 12c in poi, è ansible creare la propria tabella e definire la colonna che deve essere generata come identity framework.

Il seguente articolo spiega come usarlo:

Colonne identity framework: una nuova voce in Oracle Database 12c

Trigger e Sequence possono essere utilizzati quando si desidera il numero serializzato che chiunque può facilmente leggere / ricordare / comprendere. Ma se non si vuole gestire la colonna ID (come emp_id) in questo modo, e il valore di questa colonna non è molto considerevole, è ansible utilizzare SYS_GUID() in Creazione tabella per ottenere Incremento automatico come questo.

 CREATE TABLE  (emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY, name VARCHAR2(30)); 

Ora la tua colonna emp_id accetterà “valore identificatore univoco globale”. puoi inserire un valore nella tabella ignorando la colonna emp_id come questa.

 INSERT INTO  (name) VALUES ('name value'); 

Quindi, inserirà un valore univoco per la tua colonna emp_id .

A partire da Oracle 12c è disponibile il supporto per le colonne Identity in due modi:

  1. Sequenza + Tabella – In questa soluzione crei ancora una sequenza come faresti normalmente, quindi utilizzi il seguente DDL:

    CREATE TABLE MyTable (ID NUMERO PREDEFINITO MyTable_Seq.NEXTVAL , …)

  2. Solo tabella – In questa soluzione non viene specificata alcuna sequenza esplicitamente. Utilizzerai il seguente DDL:

    CREATE TABLE MyTable (ID NUMERO GENERATO COME IDENTITÀ , …)

Se si utilizza il primo modo, è retrocompatibile con il modo di fare esistente. Il secondo è un po ‘più semplice ed è più in linea con il resto dei sistemi RDMS.

è chiamato Identity Columns ed è disponibile solo da Oracle Oracle 12c

 CREATE TABLE identity_test_tab ( id NUMBER GENERATED ALWAYS AS IDENTITY, description VARCHAR2 (30) ); 

esempio di inserimento nelle Identity Columns come sotto

 INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION'); 

1 riga creata.

NON puoi fare inserzioni come sotto

 INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION'); 

ERRORE alla riga 1: ORA-32795: imansible inserire in una colonna di identity framework sempre generata

 INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION'); 

ERRORE alla riga 1: ORA-32795: imansible inserire in una colonna di id quadro sempre generata

link utile

Ecco la soluzione completa per la gestione delle eccezioni / degli errori per l’incremento automatico, questa soluzione è retrocompatibile e funzionerà su 11g e 12c, in particolare se l’applicazione è in produzione.

Sostituisci “TABLE_NAME” con il nome della tabella appropriata

 --checking if table already exisits BEGIN EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME'; EXCEPTION WHEN OTHERS THEN NULL; END; / --creating table CREATE TABLE TABLE_NAME ( ID NUMBER(10) PRIMARY KEY NOT NULL, . . . ); --checking if sequence already exists BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE TABLE_NAME_SEQ'; EXCEPTION WHEN OTHERS THEN NULL; END; --creating sequence / CREATE SEQUENCE TABLE_NAME_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCYCLE CACHE 2; --granting rights as per required user group / GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_NAME TO USER_GROUP; -- creating trigger / CREATE OR REPLACE TRIGGER TABLE_NAME_TS BEFORE INSERT OR UPDATE ON TABLE_NAME FOR EACH ROW BEGIN -- auto increment column SELECT TABLE_NAME_SEQ.NextVal INTO :New.ID FROM dual; -- You can also put some other required default data as per need of your columns, for example SELECT SYS_CONTEXT('USERENV', 'SESSIONID') INTO :New.SessionID FROM dual; SELECT SYS_CONTEXT('USERENV','SERVER_HOST') INTO :New.HostName FROM dual; SELECT SYS_CONTEXT('USERENV','OS_USER') INTO :New.LoginID FROM dual; . . . END; / 

Ecco come ho fatto su una tabella e una colonna esistenti (id nominate):

 UPDATE table SET id=ROWNUM; DECLARE maxval NUMBER; BEGIN SELECT MAX(id) INTO maxval FROM table; EXECUTE IMMEDIATE 'DROP SEQUENCE table_seq'; EXECUTE IMMEDIATE 'CREATE SEQUENCE table_seq START WITH '|| TO_CHAR(TO_NUMBER(maxval)+1) ||' INCREMENT BY 1 NOMAXVALUE'; END; CREATE TRIGGER table_trigger BEFORE INSERT ON table FOR EACH ROW BEGIN :new.id := table_seq.NEXTVAL; END; 
  create trigger t1_trigger before insert on AUDITLOGS for each row begin select t1_seq.nextval into :new.id from dual; end; 

solo devo cambiare il nome della tabella (AUDITLOGS) con il nome della tabella e new.id con new.column_name

 FUNCTION GETUNIQUEID_2 RETURN VARCHAR2 AS v_curr_id NUMBER; v_inc NUMBER; v_next_val NUMBER; pragma autonomous_transaction; begin CREATE SEQUENCE sequnce START WITH YYMMDD0000000001 INCREMENT BY 1 NOCACHE select sequence.nextval into v_curr_id from dual; if(substr(v_curr_id,0,6)= to_char(sysdate,'yymmdd')) then v_next_val := to_number(to_char(SYSDATE+1, 'yymmdd') || '0000000000'); v_inc := v_next_val - v_curr_id; execute immediate ' alter sequence sequence increment by ' || v_inc ; select sequence.nextval into v_curr_id from dual; execute immediate ' alter sequence sequence increment by 1'; else dbms_output.put_line('exception : file not found'); end if; RETURN 'ID'||v_curr_id; END; 
 FUNCTION UNIQUE2( seq IN NUMBER ) RETURN VARCHAR2 AS i NUMBER := seq; s VARCHAR2(9); r NUMBER(2,0); BEGIN WHILE i > 0 LOOP r := MOD( i, 36 ); i := ( i - r ) / 36; IF ( r < 10 ) THEN s := TO_CHAR(r) || s; ELSE s := CHR( 55 + r ) || s; END IF; END LOOP; RETURN 'ID'||LPAD( s, 14, '0' ); END; 

Forse prova questo semplice script:

http://www.hlavaj.sk/ai.php

Il risultato è:

 CREATE SEQUENCE TABLE_PK_SEQ; CREATE OR REPLACE TRIGGER TR_SEQ_TABLE BEFORE INSERT ON TABLE FOR EACH ROW BEGIN SELECT TABLE_PK_SEQ.NEXTVAL INTO :new.PK FROM dual; END;