在Oracle中似乎没有AUTO_INCREMENT的概念,直到并且包括版本11g。

如何在Oracle 11g中创建一个行为像自动递增的列?


当前回答

oracle在12c中有序列和标识列

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

我发现了这个,但不确定rdb7是什么 http://www.oracle.com/technetwork/products/rdb/0307-identity-columns-128126.pdf

其他回答

在甲骨文12c以后,你可以这样做,

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

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

这是我如何在一个现有的表和列(名为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;

假设您指的是类似SQL Server标识列的列?

在Oracle中,您可以使用SEQUENCE来实现相同的功能。我看看能不能找到一个好的链接,然后贴在这里。

更新:看起来你自己找到了。这里是链接: http://www.techonthenet.com/oracle/sequences.php

查询如何在oracle中创建自动增量。在下面的查询中,每当插入新行时,列值将自动递增

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);
  create trigger t1_trigger
  before insert on AUDITLOGS
  for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

我只需要改变表名(AUDITLOGS)与你的表名和新的。使用new.column_name标识