Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve self-adding Oracle12c, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

I. Mysql self-increasing column

As we all know, mysql has a self-adding function.

There can be only one self-incrementing column per table in Mysql, and the self-incrementing column must be a primary or unique key.

Mysql > create table test2 (id int not null auto_increment)

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Mysql > create table test2 (id int not null auto_increment primary key)

Query OK, 0 rows affected (0.01 sec)

Mysql insert null value, you can see that it is allowed, default will be inserted with self-incrementing column value.

Mysql > insert into test2 values ()

Query OK, 1 row affected (0.01sec)

Mysql > select * from test2

+-- +

| | id |

+-- +

| | 1 |

+-- +

1 row in set (0.00 sec)

Mysql also allows values to be inserted discontiguously

Mysql > insert into test2 values (3)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test2

+-- +

| | id |

+-- +

| | 1 |

| | 3 |

+-- +

2 rows in set (0.00 sec)

Add a column to the table, explicitly insert the new column, and the self-increment column implicitly increments from the current maximum.

Mysql > insert into test2 (b) values (4)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test2

+-+ +

| | id | b | |

+-+ +

| | 1 | NULL |

| | 3 | NULL |

| | 4 | 4 |

+-+ +

3 rows in set (0.00 sec)

II. Oracle self-increasing column

In the version of Oracle before 12c, the self-increment function can only be realized by sequence + trigger. Version 12c finally ushered in this new feature.

Self-increment syntax

Build a table

SQL > CREATE TABLE test1 (

2 id int GENERATED ALWAYS AS IDENTITY

3)

Table created.

SQL > desc test1

Name Null? Type

-

ID NOT NULL NUMBER (38)

Not null constraints are automatically added to the self-incrementing column.

A sequence is automatically generated.

SQL > select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,CACHE_SIZE from dba_sequences where SEQUENCE_OWNER='MING'

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE

ISEQ$$_45410 1 1.0000E+28 1 20

SQL > set line 150

SQL > select TABLE_NAME,COLUMN_NAME,DATA_DEFAULT from dba_tab_columns where TABLE_NAME='TEST1' and COLUMN_NAME='ID'

TABLE_NAME COLUMN_NAME DATA_DEFAULT

TEST1 ID "MING". "ISEQ$$_45410" .nextval

Pay attention to the following two actions

SQL > select ISEQ$$_45410.nextval from dual

NEXTVAL

-

one

SQL > select ISEQ$$_45410.nextval from dual

NEXTVAL

-

two

Insert a value

SQL > insert into test1 values (1)

Insert into test1 values (1)

*

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

You cannot insert values into self-incrementing columns.

SQL > alter table test1 add b int

Table altered.

SQL > insert into test1 (b) values (1)

1 row created.

SQL > select * from test1

ID B

--

3 1

You can see that the ID column inserts a value, but not 1, but 3. 0. Because the manual select has taken values from the sequence before.

After the above experiment, it is not difficult to guess that the following sentence will report an error.

SQL > insert into test1 (id,b) values (4pm 1)

Insert into test1 (id,b) values

*

ERROR at line 1:

ORA-32795: cannot insert into a generated always identity column

Update and delete

SQL > update test1 set id=5

Update test1 set id=5

*

ERROR at line 1:

ORA-32796: cannot update a generated always identity column

SQL > delete from test1

1 row deleted.

SQL > commit

Commit complete.

Update can't, but delete can.

Default can be used to refer to

SQL > insert into test1 values (default,1)

1 row created.

SQL > select * from test1

ID B

--

5 1

SQL > commit

Commit complete.

The generated sequence is also available to other users.

SQL > insert into tx (a) values (ISEQ$$_45410.nextval)

1 row created.

SQL > commit

Commit complete.

This sequence cannot be deleted.

SQL > drop sequence ming.ISEQ$$_45410

Drop sequence ming.ISEQ$$_45410

*

ERROR at line 1:

ORA-32794: cannot drop a system-generated sequence

SQL > drop table test1

Table dropped.

SQL > select ISEQ$$_45410.nextval from dual

NEXTVAL

-

six

Not since the Purge Recycle Bin.

SQL > purge recyclebin

Recyclebin purged.

So you can add purge when you delete the table, and adding cascade constraint will not delete the sequence immediately.

SQL > CREATE TABLE test2 (

2 id int GENERATED BY DEFAULT ON NULL AS IDENTITY

3)

Table created.

SQL > insert into test2 values ('')

1 row created.

SQL > select * from test2

ID

-

one

Insert can specify a value, but the specified value is not recognized by the sequence. In other words, if you manually insert a value of 2, the next value of the sequence is 2, then when the insertion is empty, 2 is inserted instead of 3.

SQL > insert into test2 values (4)

1 row created.

SQL > insert into test2 values (3)

1 row created.

SQL > commit

Commit complete.

SQL > select ISEQ$$_45418.nextval from dual

NEXTVAL

-

three

SQL > select * from test2

ID

-

one

two

four

three

SQL > insert into test2 values ('')

1 row created.

SQL > select * from test2

ID

-

one

two

four

three

four

The following Update action is interesting. There are two ID=4 values on the current table, which will be assigned different values with a sequence when updated with default.

SQL > update test2 set id=default where id=4

2 rows updated.

SQL > select * from test2

ID

-

one

two

five

three

six

Now that we know that self-incrementing columns are created from sequences, the following two ways are also possible

Create sequence ming$seq01 minvalue 1 maxvalue 9999999 start with 1 increment by 2 cache 20 nocycle

Create table test3 (id int default ming$seq01.nextval)

Create table test3 (id number)

Alter table test3 modify id number default seq_1.nextval

The difference does not become a number when the null is inserted, and the column does not have not null constraints.

SQL > insert into test3 values (null)

1 row created.

SQL > select * from test3

ID

-

L Oracle self-incrementing columns are done using sequences. Non-null constraints are automatically added to the self-increment column

When the l table is deleted, the sequence will not be deleted immediately. This is because the deleted table will enter the Recycle Bin. To associate deletion, you need to add purge.

L GENERATED ALWAYS AS IDENTITY can be delete, cannot display insert, cannot update

L GENERATED BY DEFAULT ON NULL AS IDENTITY will automatically insert null values into the values in the sequence, which can be added, deleted or modified, which is more flexible than GENERATED ALWAYS AS IDENTITY, but the uniqueness of the column cannot be guaranteed.

Inspired by the self-adding column, you can create your own sequence and specify it as the default value for the table column.

The properties of the self-built sequence of the system cannot be changed, and can be modified manually when creating the self-increment column, otherwise a smaller default value of cachewill cause performance problems. Like this:

CREATE TABLE test4 (id NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 10 cache 100))

Other users need sequence permission if they want to insert data into a list with self-increment.

The above is all the content of the article "how to add Oracle12c". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.