How to Set auto-increment primary keys in oracle

How to Set auto-increment primary keys in oracle

First, you must have a table!

Create table example (
ID Number (4) not null primary key,
Name varchar (25 ),
Phone varchar (10 ),
Address varchar (50 ));
If you have any questions about the preceding table creation statements, we recommend that you do not continue! For some time, you might as well go and see Jin Yong read Qiong Yao!
Then, you need a custom sequence
Create sequence emp_sequence
Increment by 1 -- add several
Start with 1 -- count from 1
NOMAXVALUE -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
NOCACHE -- do not create a buffer
The above code completes the establishment of a sequence, named emp_sequence. The range is from 1 to infinitely large (the degree of infinity is determined by your machine ), nocycle decides not to loop. If you set the maximum value, you can use cycle to make seq reach the maximum and then loop. for nocache, by the way, if you provide the cache value, the system will automatically read the seq of your cache value.
In this way, the operation speed will be accelerated during repeated operations. However, if an unexpected situation occurs, such as when the machine or oracle is dead, the seq value retrieved next time will be inconsistent with the previous one. (If the connection is not coherent, we recommend using cache because time is money! Run the question !)
You only have tables and sequences. You also need a trigger to execute them! The Code is as follows:
Create trigger "TRIGGER name" BEFORE
Insert on example for each row when (new. id is null)
Begin
Select emp_sequence.nextval into: new. id from dual;
End;
Close the work! Next, try to insert data!
Insert into example (Name, phone, address) Values (''cao '', ''000000'', ''heibei '');

========================================================== ==================================
A Brief Introduction to oracle sequence (auto-increment field )--

In oracle, sequence is the so-called serial number, which is automatically increased every time it is obtained. It is generally used in places where the sequence numbers need to be sorted.
1. CreateSequence
You must first have the CREATESEQUENCE or CREATEANYSEQUENCE permissions,
CREATESEQUENCEemp_sequence
INCREMENTBY1 -- add a few
STARTWITH1 -- count from 1
NOMAXVALUE -- do not set the maximum value
NOCYCLE -- always accumulate without repeating
CACHE10;

Once emp_sequence is defined, you can use CURRVAL, NEXTVAL
CURRVAL = returns the current sequence Value
NEXTVAL = increase the sequence value, and then return the sequence Value
For example:
Emp_sequence.CURRVAL
Emp_sequence.NEXTVAL

Where sequence can be used:
-SELECT statements that do not contain subqueries, snapshot, and VIEW
-The INSERT statement is in the subquery.
-In the value of the NSERT statement
-UPDATE in SET

See the following example:
INSERTINTOempVALUES
(Em1_q. nextval, ''lewis '', ''cler'', 7902, SYSDATE, 1200, NULL, 20 );

SELECTempseq. currvalFROMDUAL;

Note that:
-The first value returned by NEXTVAL is the initial value. The subsequent NEXTVAL will automatically add your defined INCREMENTBY value and then return the added value. CURRVAL always returns the value of the current SEQUENCE, but CURRVAL can be used only after the first NEXTVAL initialization; otherwise, an error will occur. NEXTVAL increases the SEQUENCE value once. Therefore, if you use multiple NEXTVAL values in the same statement, their values are different. Understand?

-If the CACHE value is specified, ORACLE can place some sequence in the memory in advance, so that the access speed is faster. After the cache is obtained, oracle automatically retrieves another group to the cache. The cache may be skipped. For example, if the database suddenly fails to be shut down (shutdownabort), the sequence in the cache will be lost. Therefore, nocache can be used in createsequence to prevent this situation.

2. AlterSequence
You are either the owner of the sequence, or you have the ALTERANYSEQUENCE permission to modify the sequence. You can alter all sequence parameters except start. To change the start value, you must dropsequence and re-create.
Altersequence example
ALTERSEQUENCEemp_sequence
INCREMENTBY10
MAXVALUE10000
CYCLE -- start from scratch after 10000
NOCACHE;

Initialization parameters that affect Sequence:
SEQUENCE_CACHE_ENTRIES = sets the number of sequence that can be simultaneously cached.

It can be very simple DropSequence
Drow.quenceorder_seq;

Okay, that's it.

-------------------------------------------------------------
Auto-growth and triggers:

In Oracle, how does one implement the function of auto-adding IDs?
Edit: ChinaASP

We often use an ID automatically assigned by the system as our primary key when designing a database, but this is not true in ORACLE.

Feature, we can automatically add the ID by taking the following features
1. First create sequence
Createsequenceseqmaxincrementby1
2. Usage
Selectseqmax. nextvalIDfromdual
You get an ID.
If you put this statement in the trigger, you can automatically add the same ID as mssql!

-------------------------------------------------------------------------

### Create a table ###

CREATETABLE "SPORTS". "LINEUP" ("ID" NUMBERNOTNULL, "TYPE"
NUMBER (3) NOTNULL, "BODY" VARCHAR2 (100) NOTNULL, "HITS" NUMBER (
10) DEFAULT0NOTNULL, PRIMARYKEY ("ID "))
TABLESPACE "TS_SPORTS"

### Sequence creation ###

CREATESEQUENCE "SPORTS". "SPORTS_LINEUP_ID_SEQ" INCREMENTBY1
STARTWITH1MAXVALUE1.0E28MINVALUE1NOCYCLE
CACHE50NOORDER

### Create an automatic update trigger ###

CREATEORREPLACETRIGGER "SPORTS". "SPORTS_LINEUP_ID_TRIGGER"
BEFOREINSERT
ON "SPORTS". "LINEUP"
FOREACHROW
DECLARE
Next_idNUMBER;
BEGIN
-- Getthenextidnumberfromthesequence
SELECTsports_lineup_id_seq.NEXTVAL
INTOnext_id
FROMdual;

-- Usethesequencenumberastheprimarykey
-- Fortherecordbeinginserted.