[Sql] Wie erstelle ich ID mit AUTO_INCREMENT auf Oracle?


Answers

Spalten wie "auto_increment" oder "identity" gibt es in Oracle ab Oracle 11g nicht . Sie können es jedoch leicht mit einer Sequenz und einem Trigger modellieren:

Tabellendefinition:

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;

Triggerdefinition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

AKTUALISIEREN:

IDENTITY Spalte ist jetzt auf Oracle 12c verfügbar:

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

oder geben Sie Start- und Inkrementwerte an, und verhindern Sie außerdem das Einfügen in die Identitätsspalte ( GENERATED ALWAYS ) (nur Oracle 12c +)

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

Es scheint, dass es in Oracle bis einschließlich Version 11g kein AUTO_INCREMENT-Konzept gibt.

Wie kann ich eine Spalte erstellen, die sich in Oracle 11g wie Autoinkrement verhält?




Vielleicht versuchen Sie einfach dieses einfache Skript:

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

Ergebnis ist:

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;



In Oracle 12c könntest du so etwas wie

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

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



Es heißt Identity Columns und ist nur von Oracle Oracle 12c verfügbar

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

Beispiel für das Einfügen in Identity Columns wie folgt

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

1 Reihe erstellt.

Sie können NICHT wie folgt einfügen

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

FEHLER in Zeile 1: ORA-32795: kann nicht in eine Spalte mit generierter Identität eingefügt werden

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

FEHLER in Zeile 1: ORA-32795: kann nicht in eine Spalte mit generierter Identität eingefügt werden

nützlicher Link




Hier ist eine vollständige Lösung für Ausnahme / Fehlerbehandlung für automatisches Inkrement, diese Lösung ist abwärtskompatibel und funktioniert auf 11g & 12c, speziell wenn die Anwendung in Produktion ist.

Ersetzen Sie "TABLE_NAME" durch den entsprechenden Tabellennamen

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



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;






SYS_GUID gibt eine GUID zurück - eine global eindeutige ID. Eine SYS_GUID ist ein RAW(16) . Es generiert keinen steigenden numerischen Wert.

Wenn Sie einen inkrementierenden numerischen Schlüssel erstellen möchten, sollten Sie eine Sequenz erstellen.

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

Sie würden dann entweder diese Sequenz in Ihrer INSERT Anweisung verwenden

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

Oder Sie können einen Auslöser definieren, der automatisch den Primärschlüsselwert mithilfe der Sequenz auffüllt

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;

Wenn Sie Oracle 11.1 oder höher verwenden, können Sie den Trigger ein wenig vereinfachen

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

Wenn Sie wirklich SYS_GUID verwenden SYS_GUID

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



  create trigger t1_trigger
  before insert on AUDITLOGS
  for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

nur ich muss nur den Tabellennamen (AUDITLOGS) mit Ihrem Tabellennamen und new.id mit new.column_name ändern




Angenommen, Sie meinen eine Spalte wie die SQL Server-Identitätsspalte?

In Oracle verwenden Sie eine SEQUENCE, um die gleiche Funktionalität zu erreichen. Ich werde sehen, ob ich einen guten Link finde und hier posten kann.

Update: sieht so aus als hättest du es selbst gefunden. Hier ist der Link trotzdem: http://www.techonthenet.com/oracle/sequences.php




Ab Oracle 12c gibt es Unterstützung für Identity-Spalten auf zwei Arten:

  1. Sequenz + Tabelle - In dieser Lösung erstellen Sie immer noch eine Sequenz wie gewohnt, dann verwenden Sie die folgende DDL:

    CREATE TABLE MyTable (ID- Nummer DEFAULT MyTable_Seq.NEXTVAL , ...)

  2. Nur Tabelle - In dieser Lösung ist keine Sequenz explizit angegeben. Sie würden die folgende DDL verwenden:

    CREATE TABLE MyTable (ID NUMMER ALS IDENTITÄT ERZEUGT , ...)

Wenn Sie den ersten Weg verwenden, ist es rückwärtskompatibel mit der bestehenden Vorgehensweise. Die zweite ist ein wenig einfacher und ist besser mit den übrigen RDMS-Systemen kompatibel.




So habe ich das für eine vorhandene Tabelle und Spalte (benannte ID) getan:

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;



Trigger und Sequence können verwendet werden, wenn Sie eine serialisierte Nummer wünschen, die jeder leicht lesen / merken / verstehen kann. Wenn Sie die ID-Spalte (wie emp_id) auf diese Weise nicht verwalten möchten und der Wert dieser Spalte nicht sehr hoch ist, können Sie SYS_GUID() bei der Tabellenerstellung verwenden, um die automatische Erhöhung wie SYS_GUID() zu erhalten.

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

Jetzt akzeptiert Ihre emp_id Spalte "global unique identifier value". Sie können den Wert in die Tabelle einfügen, indem Sie die Spalte emp_id ignorieren.

INSERT INTO <table_name> (name) VALUES ('name value');

Also wird ein eindeutiger Wert in Ihre Spalte emp_id .




Oracle Database 12c hat Identity eingeführt, eine automatisch inkrementelle (systemgenerierte) Spalte. In den vorherigen Datenbankversionen (bis 11g) implementieren Sie normalerweise eine Identität, indem Sie eine Sequenz und einen Trigger erstellen. Ab 12c können Sie Ihre eigene Tabelle erstellen und die Spalte definieren, die als Identität generiert werden soll.

Der folgende Artikel erklärt, wie man es benutzt:

Identitätsspalten - Ein neuer Eintrag in Oracle Database 12c




Hier sind drei Geschmacksrichtungen:

  1. numerisch . Einfacher steigender numerischer Wert, zB 1,2,3, ....
  2. GUID . global universal identifier als RAW Datentyp.
  3. GUID (Zeichenfolge) Wie oben, aber als String, der in manchen Sprachen einfacher zu handhaben ist.

x ist die Identitätsspalte. Ersetzen Sie FOO durch Ihren Tabellennamen in jedem der Beispiele.

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

aktualisieren:

Oracle 12c führt diese zwei Varianten ein, die nicht von Triggern abhängig sind:

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

Der erste verwendet eine Sequenz auf traditionelle Weise; Die zweite verwaltet den Wert intern.