ChatGPT解决这个技术问题 Extra ChatGPT

How to create id with AUTO_INCREMENT on Oracle?

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.

How can I create a column that behaves like auto increment in Oracle 11g?

You can create a BEFORE INSERT trigger on the table and pull values out of a sequence to create auto-increment
Identity columns are now available in Oracle 12c docs.oracle.com/cd/E16655_01/gateways.121/e22508/…
Are you using using Oracle RAC? Using CACHED at the end of the statement can improve performance. If you are doing a lot of inserts in a short period (and the ordering is not important to you), consider staggered sequence insert trigger for additional performance benefits. See: dba-oracle.com/t_rac_proper_sequence_usage.htm

a
a_horse_with_no_name

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

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;

Trigger definition:

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

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

UPDATE:

IDENTITY column is now available on Oracle 12c:

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

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

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

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

I'm a n00b, can you please tell me where dept_seq came from!
CREATE SEQUENCE dept_seq; creates dept_seq ... like a table .. but in this case its only a number that you can increase with dept_seq.NEXTVAL ... see the trigger.
As was mentioned, the original code would fail when encountering a line with ID specified. But how about this case: The trigger would assign the id (automatically) only if there was no id specified explicitly in INSERT. This would fail, right? And what is the proper way to do this?
For oracle newbies like me, the 'id' part of 'new.id' refers to the column 'id' in the table above. 'new' is a reserved word referring to the new row created
You do not need to use SELECT .. INTO in the trigger you can just do :new.id := dept_seq.NEXTVAL;.
J
Justin Cave

SYS_GUID returns a GUID-- a globally unique ID. A SYS_GUID is a RAW(16). It does not generate an incrementing numeric value.

If you want to create an incrementing numeric key, you'll want to create a sequence.

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

You would then either use that sequence in your INSERT statement

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

Or you can define a trigger that automatically populates the primary key value using the sequence

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;

If you are using Oracle 11.1 or later, you can simplify the trigger a bit

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

If you really want to use SYS_GUID

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

What does CACHE 100; in CREATE SEQUENCE name_of_sequence START WITH 1 INCREMENT BY 1 CACHE 100; do?
CACHE 100: keyword fetches next 100 numbers to memory. Normally a SEQUENCE is saved into database whenever its value changed, if you cache it, it will be saved and retrieved only if cached ones exhausted. Gives you a significant performance gain, but if database fails, you lose all cached values you didn't even use.
A SYS_GUID() is a RAW(16), not 32.
@turbanoff - Good catch. Updated my answer. The SYS_GUID documentation declares a raw(32) which confused me.
@JustinCave I have used your approach in creating the sequence and trigger. What if I delete a row programmatically (java), will the ID(primary key) gets adjusted as well?
J
Jon Heller

In Oracle 12c onward you could do something like,

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

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

@JonHeller I personally say the IDENTITY example is much clearer in this answer.
The WHEN (new.MAP_ID IS NULL) is not in the accepted answer. Upvoted.
@dcsohl, WHEN ( new.MAP_ID is null) is not a good code in this case and is already explained in the comment section by @A.B.Cade under accepted answer.. have a read;)
When I run this from CREATE OR REPLACE TRIGGER to END;, I get an "Enter Binds" window. If I click "Apply" and don't do anything else in that window, and then run the ALTER TRIGGER command, all is good, but wish there was a way to programmatically get rid of that pop-up and run everything together. If you try it altogether, you get PLS-00103: Encountered the symbol 'ALTER' and it doesn't like EXECUTE IMMEDIATE, either (same error, just says it Encountered the symbol 'EXECUTE' instead).
I got [42000][907] ORA-00907: missing right parenthesis when running the version for Oracle 12c onward. Any idea ?
M
Mark Harrison

Here are three flavors:

numeric. Simple increasing numeric value, e.g. 1,2,3,.... GUID. globally univeral identifier, as a RAW datatype. GUID (string). Same as above, but as a string which might be easier to handle in some languages.

x is the identity column. Substitute FOO with your table name in each of the examples.

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

update:

Oracle 12c introduces these two variants that don't depend on triggers:

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

The first one uses a sequence in the traditional way; the second manages the value internally.


P
Phil Sandler

Assuming you mean a column like the SQL Server identity column?

In Oracle, you use a SEQUENCE to achieve the same functionality. I'll see if I can find a good link and post it here.

Update: looks like you found it yourself. Here is the link anyway: http://www.techonthenet.com/oracle/sequences.php


u
user272735

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column. In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger. From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.


While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.
1
124

Trigger and Sequence can be used when you want serialized number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like emp_id) by this way, and value of this column is not much considerable, you can use SYS_GUID() at Table Creation to get Auto Increment like this.

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

Now your emp_id column will accept "globally unique identifier value". you can insert value in table by ignoring emp_id column like this.

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

So, it will insert unique value to your emp_id Column.


What happens when a row is deleted? Will the SYS_GUID() its id values as well?
N
Nate Zaugg

Starting with Oracle 12c there is support for Identity columns in one of two ways:

Sequence + Table - In this solution you still create a sequence as you normally would, then you use the following DDL: CREATE TABLE MyTable (ID NUMBER DEFAULT MyTable_Seq.NEXTVAL, ...) Table Only - In this solution no sequence is explicitly specified. You would use the following DDL: CREATE TABLE MyTable (ID NUMBER GENERATED AS IDENTITY, ...)

If you use the first way it is backward compatible with the existing way of doing things. The second is a little more straightforward and is more inline with the rest of the RDMS systems out there.


s
sam

it is called Identity Columns and it is available only from oracle Oracle 12c

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

example of insert into Identity Columns as below

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

1 row created.

you can NOT do insert like below

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

ERROR at line 1: ORA-32795: cannot insert into a generated always identity column

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

ERROR at line 1: ORA-32795: cannot insert into a generated always identity column

useful link


e
emkays

Here is complete solution w.r.t exception/error handling for auto increment, this solution is backward compatible and will work on 11g & 12c, specifically if application is in production.

Please replace 'TABLE_NAME' with your appropriate table name

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

e
ether6

This is how I did this on an existing table and column (named id):

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;

k
kumar venkatesan
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;

s
sowmya dantuluri

Query to create auto increment in oracle. In below query incrmnt column value will be auto incremented wheneever a new row is inserted

CREATE TABLE table1(
id RAW(16) NOT NULL ENABLE,
incrmnt NUMBER(10,0) GENERATED ALWAYS AS IDENTITY
MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOORDER NOCYCLE NOT NULL ENABLE,
CONSTRAINT PK_table1 PRIMARY KEY (id) ENABLE);

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

only I have to just change the table name (AUDITLOGS) with your table name and new.id with new.column_name


k
kumar venkatesan
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;

It would be helpful to describe what this function is supposed to be doing; I can't imagine it working correctly for any possible NUMBER passed in.
M
Mohammad Ali Abdullah

Creating a Sequence:

CREATE SEQUENCE SEQ_CM_LC_FINAL_STATUS
MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

Adding a Trigger

CREATE OR REPLACE TRIGGER CM_LC_FINAL_STATUS_TRIGGER
BEFORE INSERT
ON CM_LC_FINAL_STATUS
FOR EACH ROW
BEGIN
:NEW.LC_FINAL_STATUS_NO := SEQ_CM_LC_FINAL_STATUS.NEXTVAL;
END;

The first step is to create a SEQUENCE in your database, which is a data object that multiple users can access to automatically generate incremented values. As discussed in the documentation, a sequence in Oracle prevents duplicate values from being created simultaneously because multiple users are effectively forced to “take turns” before each sequential item is generated. –

Finally, we’ll create our SEQUENCE that will be utilized later to actually generate the unique, auto incremented value. –

While we have our table created and ready to go, our sequence is thus far just sitting there but never being put to use. This is where TRIGGERS come in. Similar to an event in modern programming languages, a TRIGGER in Oracle is a stored procedure that is executed when a particular event occurs. Typically a TRIGGER will be configured to fire when a table is updated or a record is deleted, providing a bit of cleanup when necessary. –

In our case, we want to execute our TRIGGER prior to INSERT into our CM_LC_FINAL_STATUS table, ensuring our SEQUENCE is incremented and that new value is passed onto our primary key column.


K
Kalpesh Soni

oracle has sequences AND identity columns in 12c

http://www.oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1.php#identity-columns

I found this but not sure what rdb 7 is http://www.oracle.com/technetwork/products/rdb/0307-identity-columns-128126.pdf


Helpful information and interesting performance comparison. Using IDENTITY columns is better than using a TRIGGER for a SEQUENCE.
@ygoe is right; any trigger has a lot of overhead in general; and sequences (which are what are hiding behind IDENTITY columns anyhow), are very lightweight.
M
Martin Hlavaj

Maybe just try this simple script:

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

Result is:

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;

How is this different than eugnio's answer? Plus: you don't need the select in modern Oracle versions. You can simply use:new.pk := TABLE_PK_SEQ.NEXTVAL