Two Oracle creation fields from the growth implementation _oracle

In other databases such as MySQL, where the table ID automatically grows with the insertion of the record, and Oracle does not, there are two ways we can solve the function of the field from growth.
Because both of these methods need to be created by creating a sequence, here we first give a way to create a sequence.

CREATE SEQUENCE sequence name
[INCREMENT by n]
[START with n]
[{maxvalue/minvalue n| Nomaxvalue}]
[{cycle| Nocycle}]
[{CACHE n| NoCache}];

Analytical:

1) INCREMENT by is used to define the step size of the sequence, if omitted, the default is 1, and if a negative value is present, the values representing the Oracle sequence are decremented by this step.

2 START with defines the initial value of the sequence (that is, the first value produced), and the default is 1.

3) MAXVALUE defines the maximum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a maximum definition, in which case the maximum value that the system can produce for an ascending Oracle sequence is 10 27; For a descending sequence, the maximum value is-1.

4) MinValue defines the minimum value that a sequence generator can produce. Option Nomaxvalue is the default option, which represents the absence of a minimum value definition, in which case the minimum value that the system can produce for a descending sequence is 10 26; For an ascending sequence, the minimum value is 1.

5 cycle and Nocycle indicate whether the value of the sequence generator loops after it reaches its limit. Cycle represents loops, and nocycle represents not loops. Loops to the minimum when the increment sequence reaches its maximum value, and loops to the maximum value for the descending sequence when it reaches the minimum value. If you do not loop, and the limit is reached, the new value continues to produce an error.

6 cache (buffer) defines the size of the memory block that holds the sequence, and defaults to 20. NoCache indicates no memory buffering of the sequence. Memory buffering of the sequence can improve the performance of the sequence.

Solution One, sequence + trigger

The specific implementation methods are as follows:

The first step is to create a sequence

--Create sequence
create sequence seq_t_recv
minvalue 1
maxvalue 9999999 start with
1
increment by 1
Cache 50;

Step two, create a table

--Create TABLE
CREATE TABLE recv_msg
(ID number     ,
 messageid  VARCHAR2),
 Contents  VARCHAR2,
 app_flg VARCHAR2 (MB), PhoneNumber VARCHAR2
 (Watts
 ), UpdateTime DATE default Sysdate
);

Step three, set up triggers

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 fourth, test and confirm
Insert a piece of data into the table to see if the ID field is automatically growing.

Workaround two, sequence + display call sequence

First, create sequence

Create sequence Seq_on_test 
increment by 1- 
start with 1 
nomaxvalue 
nocycle 

Second, the establishment of the table

--Build Table 
drop table test; 
CREATE TABLE Test ( 
ID integer 
, Stu_name nvarchar2 (4) 
, stu_age number 

Inserting data (displays the next value of the call sequence insert)

--insert data into 
test values (seq_on_test.nextval, ' Mary '); 
INSERT into test values (seq_on_test.nextval, ' Tom ', 16); 
 

Four, view

SELECT * from test; 

--Results/ 
* 
1 Mary 
2 Tom 16 

With: View how the current and next values of the sequence are viewed

--seq's two methods 
select Seq_on_test.currval from dual; 
Select Seq_on_test.nextval from dual; 

--Results/ 
* 
2 
3 
* * 
 

Summarize

Adding directly through triggers is easier than a display call, and we don't need which field to go through which sequence also gets the next value, and the add that executes through the trigger.

The above is the entire content of this article, I hope that we have mastered the two Oracle create fields from the growth of the implementation of the way to help.