Implement Auto-increment of oracle database fields (two methods): oracle Fields

Implement Auto-increment of oracle database fields (two methods): oracle Fields

Programmers know that mysql and other databases have the function of Automatically increasing table IDs with the insertion of records, but oracle does not, the following two methods are used to address the field growth function. The specific content is described below.

Because both methods need to be implemented by creating sequences, the sequence creation method is given first.
Copy codeThe Code is as follows:
Create sequence name
[Increment by n]
[Start with n]
[{MAXVALUE/MINVALUE n | NOMAXVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Resolution:

1) increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the value of the Oracle sequence decreases according to this step.

2) start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

3) MAXVALUE defines the maximum value that can be generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental Oracle sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

4) MINVALUE defines the minimum value generated by the sequence generator. The "NOMAXVALUE" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

5) CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

6) CACHE (buffer) defines the size of the memory block for storing the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

Solution 1: sequence + trigger

The specific implementation method is as follows:

Step 1: Create a sequence

Copy codeThe Code is as follows:
-- Create sequence
Create sequence SEQ_T_RECV
Minvalue 1
Max value 9999999
Start with 1
Increment by 1
Cache 50;

Step 2: Create a table

Copy codeThe Code is as follows:
-- Create table
Create table RECV_MSG
(
Id NUMBER,
Messageid VARCHAR2 (32 ),
Contents VARCHAR2 (2000 ),
App_flg VARCHAR2 (100 ),
Phonenumber VARCHAR2 (2000 ),
Updatetime DATE default sysdate
);

Step 3: Create a trigger

Copy codeThe Code is as follows:
Create or replace trigger "recv_trig"
Before insert on recv_msg
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
BEGIN
SELECT SEQ_T_RECV.NEXTVAL INTO: NEW. id from dual;
END recv_trig;

Step 4: Test and confirm
Insert a data entry to the table to check whether the id field is automatically increased.

Solution 2: sequence + display Call Sequence

1. Create sequence

Copy codeThe Code is as follows:
Create sequence seq_on_test
Increment by 1
Start with 1
Nomaxvalue
Nocycle
Nocache;

Ii. Create a table

Copy codeThe Code is as follows:
-- Create a table
Drop table test;
Create table test (
ID integer
, Stu_name nvarchar2 (4)
, Stu_age number
);

3. insert data (the next value of the call sequence is displayed)

Copy codeThe Code is as follows:
-- Insert data
Insert into test values (seq_on_test.nextval, 'Mary ', 15 );
Insert into test values (seq_on_test.nextval, 'Tom ', 16 );

Iv. View

Copy codeThe Code is as follows:
Select * from test;

-- Result
/*
1 Mary 15
2 Tom 16
*/

Note: view the current and next values of a sequence

Copy codeThe Code is as follows:
-- Two methods of seq
Select seq_on_test.currval from dual;
Select seq_on_test.nextval from dual;

-- Result
/*
2
3
*/

The preceding section describes the auto-increment of oracle database fields in two ways. We hope this will be helpful to you.