Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to create a self-growing primary key in Oracle. The content of the article is of high quality, so I hope you can get something after reading this article.

To create a self-growing Oracle:

Create a self-growing sequence

-- create self-growing sequence create sequence seq_on_chance_contractincrement by 1-- add 1start with 1 each time-- count nomaxvalue from 1-- do not set maximum nocycle-- accumulate nocache all the time

Create a datasheet

INTEGER and NUMBER can be used for primary keys, but NUMBER is not recommended if the system uses Hibernate as the ORM framework

Create table TBL_CHANCE_CONTRACT (ID INTEGER PRIMARY KEY, CHANCE_SUCCESS_ID VARCHAR2 (50) not null, CONTENT CLOB, CREATE_USER_ID VARCHAR2 (50), CREATA_USER_NAME VARCHAR2 (80), CREATE_DATE TIMESTAMP (6)

Create a primary key trigger so that you do not need to manage the ID primary key when adding new records

-- create primary key trigger CREATE OR REPLACE TRIGGER tg_on_id_chance_contract BEFORE INSERT ON TBL_CHANCE_CONTRACT FOR EACH ROW WHEN (new.ID is null) BEGIN SELECT seq_on_chance_contract.nextval into:new.ID from dual;END

A few tips:

View all the sequences and tables

-- find all SEQUENCE, TABLEselect * from user_objects ubs;-- find all SEQUENCEselect * from user_objects ubs where ubs.OBJECT_TYPE='SEQUENCE'

Check how much the current sequence has reached

Select seq_on_chance_contract.nextval from dual;select seq_on_chance_contract.currval from dual

Reset sequence

Generally, to reset a sequence, you need to delete the sequence and then rebuild it, but this is troublesome. There are 2 ways to do this without deleting the sequence:

1. Using the step parameter, first find out the nextval of sequence, remember to change the increment to a negative value (vice versa), and then change it back.

Suppose the sequence name seq_on_chance_contract needs to be modified

-- reset sequence select seq_on_chance_contract.nextval from dual;-- suppose you get the result nalter sequence seq_on_chance_contract increment by-2;-- notice that it is-(nMel 1) select seq_on_chance_contract.nextval from dual;-- check again, walk around, reset to 1 alter sequence seq_on_chance_contract increment by 1;-- restore.

2. Using stored procedure to realize (v_seqname)

Create or replace procedure seq_reset (v_seqname varchar2) as n number (10); tsql varchar2 (100); begin execute immediate 'select' | | v_seqname | |'. Nextval from dual' into n; n _ increment by'; tsql:='alter sequence'| | v_seqname | | 'increment by' | | n; execute immediate tsql; execute immediate 'select' | | v_seqname |'. Nextval from dual' into n; tsql:='alter sequence'| | v_seqname | | 'increment by 1; execute immediate tsql; end seq_reset |

Then call the stored procedure:

Exec seq_reset ('viciseqame.')

A primary key (primary key) is one or more fields in a table whose values are used to uniquely identify a record in the table. Oracle primary keys are commonly divided into UUID and self-growing int, the following briefly describes their respective advantages and disadvantages.

Advantages of UUID

1. Easy to generate, whether through sys_guid () or java's uuid, you can easily create UUID.

2. Suitable for insert and update operations in batch data.

3. Cross-server data merging is very convenient.

Advantages of INT self-growth

1. Small footprint

2. Good performance, UUID is not on the same level as int.

3. Easy to remember

Their respective strengths are each other's weaknesses.

Scope of application:

UUID is generally used as the sole primary key in a distributed environment, but for other projects, it is strongly recommended to use int as the primary key.

PS: recently, one of my distributed projects also experienced UUID duplication during peak weekend visits, so I didn't write down the advantage of UUID as the world's only ID, and we can use Zookeeper as the primary key generator to create the only primary key of int type, which is no problem at all.

So to sum up, I still suggest that all primary keys should use the int type, and the advantage of the int type is particularly obvious in tens of millions of data levels.

This is how Oracle creates a self-growing primary key. Is there anything to gain after reading it? If you want to know more about it, you are welcome to follow the industry information. Thank you for reading.

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.