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

SYS_GUID返回一个GUID——一个全局唯一的ID。SYS_GUID是RAW(16)。它不会生成递增的数值。

如果你想要创建一个递增的数字键，你需要创建一个序列。

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

然后，您可以在INSERT语句中使用该序列

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

或者您可以定义一个触发器，该触发器使用序列自动填充主键值

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;

如果您使用的是Oracle 11.1或更高版本，可以稍微简化触发器

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

如果您真的想使用SYS_GUID

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

在甲骨文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;
/

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;

也许可以试试这个简单的脚本:

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

结果是:

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;

查询如何在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);