Oracle Database auto-increment Field

When SQL Server is used, an auto-increment field is added and set as the primary key to avoid repeated records. Now let go of the advantages and disadvantages of doing so. Let alone implementation problems.

When oracle is used, this situation occurs. When a module is added, only one module name is input, and there is a module ID in the table. This ID is set to auto-increment. However, the field auto-increment function is not available in Oracle. However, this function can be implemented by writing sequences and triggers by yourself.

Let's first talk about the auto-increment function: auto-increment is nothing more than auto-increment and assign values to auto-increment fields according to certain sequence during insertion. Then we will create a sequence in Oracle and create a trigger so that at the time of insertion, the sequence increases by 1 and the auto-increment field is assigned a value.

I use PL/SQL for visual creation.

Creation sequence:

Name: sequence name

Min value: Minimum count

Max value: maximum count

Star with: Start Time

Increment by: Step Size

Cache size: cache Sequence

Specify the cache. Oracle will prevent a set of specified size sequences in the memory in advance. After these sequences are used up, the next group will be generated.

The access speed is faster, but when the database is shut down, the next generation of the sequence may interrupt the sequence, rather than genetic continuity.

Number. It is best to specify a sequence when it is not particularly required;

If no cache is entered, the default settings are used. When the cache is set to 0 or nocache, a continuous sequence is generated.

Cycle: cyclic sequence. When the maximum value is reached, start from the minimum value again.

Order: ensure that the sequence generated and the request sequence are consistent. In parallel mode, if both A and B request the sequence at the same time

The generated serial number must be returned to the first requested user. This happens only on the Oracle Parallel Server (not currently available ),

In most cases, it is required in the lower part.

Create a trigger:

A statement-level trigger that does not contain the foreachrow clause in the createtrigger statement. Statement level

A trigger can only trigger an event once and cannot be affected by the column value of each row of the trigger. Generally, it is processed with a series trigger.

Information about the SQL statements that trigger the trigger, for example, who executes the SQL statements and when the SQL statements are executed.

A row-level trigger that contains the foreachrow clause in the createtrigger statement. Row-level triggers can be triggered

Each row affected by the sender is triggered, and the original column value and new column value processed by SQL statements can be accessed. The typical application of Row-level triggers is

When you need to know the column value of a row, execute a transaction rule.

　　　　

After confirmation, the following picture will appear

Insert a select seq_model.nextval into: New. mdlid from dual;

I don't know. Have you understood the above sentence? If not, let's take a look at the supplement below.

Supplement:

1) For the insert statement, the value to be inserted is included in new. column_name, where column_name is a column in the table.

2) for the update statement, the original value of the column is included in old_column_name, and the new value of the data column is new. column_name

.

3) for the delete statement, put the column value of the row to be deleted in old. column_name.