Oracle Create field self-growth-two ways to summarize your implementation

Other databases such as MySQL have the ability to automatically grow the table ID as the record is inserted, and Oracle does not have this capability, and we have the following two ways to solve the self-growing function of the field.

Because both of these methods need to be implemented by creating a sequence, here 's how the sequence is created first.

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

parsing :

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

2) START with defines the initial value of the sequence (that is, the first value produced), which defaults to 1.

3) MAXVALUE defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum value defined, at which point the system can produce a maximum of 10 27 times for incrementing an Oracle sequence, and for a descending sequence, the maximum value is-1.

4) MinValue defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means there is no minimum definition, and for a descending sequence, the minimum value that the system can produce is 10 of the 26 square, and for the increment sequence, the minimum value is 1.

5) cycle and nocycle indicate whether the sequence generator will loop if its value reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

6) the cache (buffer) defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a sequence can improve the performance of the sequence.

Solution One, sequence + trigger

The specific implementation is as follows:

The first step is to create the sequence

-- Create Sequence Create  19999999 with 1 by 1   ; 

The second step is to create the table

--Create TableCreate Tablerecv_msg (ID Number, MessageIDVARCHAR2( +), ContentsVARCHAR2( -), App_flgVARCHAR2( -), PhoneNumberVARCHAR2( -), UpdateTime DATEdefaultsysdate);

Step three, create a trigger

CREATE OR REPLACE TRIGGER "Recv_trig"   INSERT  on recv_msg    as  as  for Each ROW DECLARE BEGIN  SELECT  into  from DUAL; END Recv_trig;

Fourth step, test and confirm
Insert a piece of data into the table to see if the ID field is automatically growing.

Solution Two, sequence + display call sequence

First, create sequence

Createby1 with 1

Ii. Establishment of the table

--droptablecreatetable integer  , Stu_name nvarchar2 (4 number

Inserting data (showing the next value of the call sequence is inserted)

--insertintovalues(seq_on_test.nextval,'Mary'  , insert into values (Seq_on_test.nextval, ' Tom ',

Four, view

Select *  from --/**/

Included: View the current value of the sequence and the way the next value is viewed

   --Select from select from-- /* */

Summarize

The way to add directly through a trigger is more convenient than a display call, and we don't need which field to go through which sequence to get the next value, and the addition of the execution through the trigger.

Oracle Create field self-growth-two ways to summarize your implementation