在oracle中自动递增到已创建的表 [英] Autoincrement in oracle to already created table
问题描述
如何将自动增量添加到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 及更高版本上，创建一个序列以通过触发来增加列强>.参见 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列.请参见 IDENTITY列自动递增Oracle 12c中的功能
On 12c , use Identity column. See IDENTITY column autoincrement functionality in Oracle 12c
例如，
表和 IDENTITY COLUMN
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 column.您可以找到它的名称为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
这篇关于在oracle中自动递增到已创建的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
