Specific steps for Oracle Auto-incremental ID implementation

Today we are going to discuss with you the implementation of Oracle Auto-incremental ID. I am not sure about this problem. The information we saw on the relevant website two days ago is quite good, I hope it will help you in this regard.

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) );  
 
 

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','56498543','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. Create Sequence

You must first have the Create Sequence or Create AnySequence permissions,

 
 

  
  
Create Sequence emp_sequence 
 
 

Increment by 1 -- add several

STARTWITH 1 -- 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 VALUES of the INSERT statement

-UPDATE in SET

See the following example:

 
 

  
  
INSERT INTO emp VALUES  
  
  
(empseq.nextval,'LEWIS','CLERK',7902,SYSDATE,1200,NULL,20);  
  
  
SELECT empseq.currval FROM DUAL;  
 
 

Note that:

The first value returned by NEXTVAL is the initial value. The subsequent NEXTVAL will automatically increase the value of your defined INCREMENTBY 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, when the database suddenly fails to shut down and shutdownabort), the sequence in the cache will be lost. Therefore, nocache can be used when createsequence is used to prevent this situation.

2. Alter Sequence

You are either the owner of the sequence, or you have the alter anysequence permission to modify the sequence. you can alter all sequence parameters except start. if you want to change the start value, you must drop sequence and re-create.

Alter sequence example

 
 

  
  
ALTER SEQUENCE emp_sequence  
  
  
INCREMENT BY 10  
  
  
MAXVALUE 10000  
 
 

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's easy to Drop Sequence.

 
 

  
  
DRO SEQUENCE order_seq; 
 
 

Auto-growth and triggers:

In Oracle Auto-increment ID, how does one implement a function similar to auto-increment ID?

We often use an ID automatically assigned by the system as our primary key when designing a database, but this feature is not available in ORACLE, we can automatically add IDS by taking the following features

1. First create sequence

 
 

  
  
create sequence seq maxincrement by 1 
 
 

2. Usage

 
 

  
  
select seqmax.nextval ID from dual 
 
 

You get an ID.

If you put this statement in the trigger, you can use Oracle Auto-increment ID to automatically add the same ID function as mssql!

Create a table

 
 

  
  
CREATE TABLE "SPORTS"."LINEUP"("ID" NUMBER NOT NULL,  
  
  
"TYPE" NUMBER(3) NOT NULL,  
  
  
"BODY" VARCHAR2(100) NOT NULL,  
  
  
"HITS" NUMBER(10) DEFAULT 0 NOT NULL,  
  
  
PRIMARYKEY("ID"))  
  
  
TABLESPACE "TS_SPORTS"  
 
 

Sequence Creation

 
 

  
  
CREATE SEQUENCE "SPORTS"."SPORTS_LINEUP_ID_SEQ" INCREMENT BY 1  
  
  
START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE  
  
  
CACHE 50 NOORDER  
 
 

Create an automatic update trigger

 
 

  
  
CREATE OR REPLACE TRIGGER "SPORTS"."SPORTS_LINEUP_ID_TRIGGER"  
  
  
BEFORE INSERT ON "SPORTS"."LINEUP" FOR EACH ROW  
  
  
DECLARE  
  
  
next_id NUMBER;  
  
  
BEGIN  
  
  
--Get the next id number from the sequence  
  
  
SELECT sports_lineup_id_seq.NEXTVAL INTO next_id FROM dual;  
  
  
--Use the sequence number as the primarykey  
  
  
--for there cord being inserted.  
  
  
:new.id:=next_id;  
  
  
END;  
 
 

Create a trigger to protect PRIMARYKEY

 
 

  
  
CREATE OR REPLACE TRIGGER "SPORTS"."LINEUP_ID_UPDATE_TRIGGER"  
  
  
BEFORE UPDATE OF "ID" ON "SPORTS"."LINEUP" FOR EACHROW  
  
  
BEGIN  
  
  
RAISE_APPLICATION_ERROR(-20000,  
  
  
'sports_lineup_id_update_trigger:Update sof the ID field'  
  
  
｜｜'arenotallowed.');  
  
  
END;  
 
 

Create a delete trigger

 
 

  
  
create or replace trigger tr_bis_exc_req_del  
  
  
before delete  
  
  
on bis_exc_req  
  
  
referencing old as old new as new  
  
  
for each row  
  
  
begin  
  
  
if :old.check_status = '3' then  
  
  
raise_application_error (-20001,'*****!');  
  
  
return;  
  
  
end if;  
  
  
end;  
  
  
/  
 
 

Create an update trigger

 
 

  
  
create or replace trigger tr_bis_exc_req_upd  
  
  
before update  
  
  
on bis_exc_req  
  
  
referencing old as old new as new  
  
  
for each row  
  
  
begin  
  
  
if :old.check_status = '3' then  
  
  
raise_application_error (-20001,'*******!');  
  
  
return;  
  
  
end if;  
  
  
end;