oracle中的自动增量到已经创建的表
如何给oracle中的现有列添加自动增量?列已经创建，它是表的主键.只想将表更改为自动增量.以下是栏目详情
How to add auto-increment to the existing column in oracle? Column has already been created and it is the primary key of the table. Just want to alter table to be autoincrement. Below is the column details
Column Name DATA_TYPE NULLABLE
SEQ_ID VARCHAR2(9 BYTE) No
自增数应该从150111111开始到150111112、150111113等值
Autoincrement number should be starting from 150111111 to the values such as 150111112, 150111113 etc
谢谢
推荐答案
在 11g 和之前，创建一个 sequence 以通过 trigger.请参阅 Pre 12c 版本中的自动递增主键(身份功能)
On 11g and prior, create a sequence to increment the column via trigger. See Auto-increment primary key in Pre 12c releases (Identity functionality)
例如
表格
SQL> CREATE TABLE t (
2 ID NUMBER(10) NOT NULL,
3 text VARCHAR2(50) NOT NULL);
Table created.
PRIMARY KEY 由序列填充
SQL> ALTER TABLE t ADD (
2 CONSTRAINT id_pk PRIMARY KEY (ID));
Table altered.
SEQUENCE支持主键
SQL> CREATE SEQUENCE t_seq
2 START WITH 150111111
3 INCREMENT BY 1;
Sequence created.
TRIGGER 如果您不想在 INSERT 中包含序列，您可以通过 TRIGGER 自动执行.
TRIGGER If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.
SQL> CREATE OR REPLACE TRIGGER t_trg
2 BEFORE INSERT ON t
3 FOR EACH ROW
4 WHEN (new.id IS NULL)
5 BEGIN
6 SELECT t_seq.NEXTVAL
7 INTO :new.id
8 FROM dual;
9 END;
10 /
Trigger created.
插入
SQL> INSERT INTO t(text) VALUES('auto-increment test 1');
1 row created.
SQL> INSERT INTO t(text) VALUES('auto-increment test 2');
1 row created.
让我们看看我们是否让 ID 列自动增加了所需的值-
Let's see if we have the ID column auto-incremented with the desired values-
SQL> SELECT * FROM t;
ID TEXT
---------- --------------------------------------------------
150111111 auto-increment test 1
150111112 auto-increment test 2
SQL>
因此，ID 列现在从值 150111111 开始，并在后续插入时递增 1.
So, the ID column now starts with value 150111111 and increments by 1 with subsequent inserts.
在 12c 上，使用 身份列.请参阅 IDENTITY 列自动增量功能在 Oracle 12c 中
On 12c , use Identity column. See IDENTITY column autoincrement functionality in Oracle 12c
例如
表格，带有身份列
SQL> CREATE TABLE t
2 (
3 ID NUMBER GENERATED ALWAYS AS IDENTITY
4 START WITH 150111111 INCREMENT BY 1,
5 text VARCHAR2(50)
6 );
Table created.
插入
SQL> INSERT INTO t
2 ( text
3 ) VALUES
4 ( 'This table has an identity column'
5 );
1 row created.
让我们看看我们是否让 ID 列自动增加了所需的值-
Let's see if we have the ID column auto-incremented with the desired values-
SQL> COLUMN text format A40
SQL> SELECT * FROM t;
ID TEXT
---------- ----------------------------------------
150111111 This table has an identity column
因此，ID 列现在从值 150111111 开始，并在后续插入时递增 1.
So, the ID column now starts with value 150111111 and increments by 1 with subsequent inserts.
Oracle 创建一个 sequence 来填充 identity 列.你可以找到它命名为 ISEQ$$
Oracle creates a sequence to populate the identity column. You can find it named as ISEQ$$
SQL> SELECT sequence_name,
2 min_value,
3 max_value,
4 increment_by
5 FROM user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY
------------------------------ ---------- ---------- ------------
ISEQ$$_94087 1 1.0000E+28 1
SQL>
有关标识列的更多信息，请使用 ALL_TAB_IDENTITY_COLS 视图.
More information about the identity columns, use the ALL_TAB_IDENTITY_COLS view.
SQL> SELECT table_name,
2 column_name,
3 generation_type,
4 identity_options
5 FROM all_tab_identity_cols
6 WHERE owner = 'LALIT'
7 ORDER BY 1,
8 2;
TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS
-------------------- ----------- ---------- ----------------------------------------------
T ID ALWAYS START WITH: 150111111, INCREMENT BY: 1,
MAX_VALUE:9999999999999999999999999999,
MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20,
ORDER_FLAG: N
相关文章