在 Oracle 11g 及之前参考如下文章
https://www.jianshu.com/p/e8e29df265a5
在 Oracle 12c 及之后
1. 语法
GENERATED (ALWAYS | (BY DEFAULT [ON NULL])) AS IDENTITY[sequence_options,...]
| 身份列声明 | 描述 |
|---|---|
| GENERATED ALWAYS AS IDENTITY | 序列生成器始终提供一个IDENTITY值。您不能为列指定值。 |
| GENERATED BY DEFAULT AS IDENTITY | 每当您不提供列值时，序列生成器都会提供IDENTITY值 |
| GENERATED BY DEFAULT ON NULL AS IDENTITY | 如果您指定NULL columnn值，则序列生成器将提供下一个IDENTITY值 |
2. 建表时声明
CREATE TABLE IF NOT EXISTS tname1 (id INTEGER GENERATED ALWAYS AS IDENTITY);
3. 在现有表中插入
ALTER TABLE SNSOFT.XD_INTEREST_ADJUSTMENTADD "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY
4. 修改现有id列
ALTER TABLE SNSOFT.XD_INTEREST_ADJUSTMENTMODIFY "ID" NUMBER(10,0) GENERATED BY DEFAULT ON NULL AS IDENTITY
5. 删除自增列
ALTER TABLE SNSOFT.XD_INTEREST_ADJUSTMENT DROP COLUMN ID;
6. 别忘了还原主键
alter table SNSOFT.XD_INTEREST_ADJUSTMENT add constraint XD_INTEREST_ADJUSTMENT_PK primary key ("ID");
