[sql] Come creare un ID con AUTO_INCREMENT su Oracle?


Answers

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, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

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, è possibile 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,
  <<other columns>>
)
Question

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?




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, è possibile creare la propria tabella e definire la colonna che deve essere generata come identità.

Il seguente articolo spiega come usarlo:

Colonne identità: una nuova voce in Oracle Database 12c




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, e.g. 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, e.g. 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.




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.




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;



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;
/



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;



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;



Related