Create and use oracle sequence, and create oracle Sequence

Create and use oracle sequence, and create oracle Sequence
Create and use an oracle sequence (16:14:09)---------------------------------------------------------------------------------------------------Reprinted token -------------------------------------------------------------------------------------------------------------------

In Oracle, a sequence can be used to automatically generate an integer sequence. It is mainly used to automatically provide ordered and unique values for primary key columns of the Data Type in the table, in this way, you do not need to manually specify the primary key value when adding data to the table. In addition, when manually specifying the primary key value, because the primary key value cannot be repeated, it requires the operator to determine whether the newly added value already exists when specifying the primary key value, this is obviously not desirable.

(1) create a sequence

Like a view, a sequence does not occupy the actual storage space, but stores its definition information in the data dictionary. The create sequence statement is required to CREATE a SEQUENCE. Its syntax is as follows:

Create sequence [schema]. Sequence_name -- sequence name

[Start with start_number] -- START number

[Increment by increment_number] -- number added each time

[MINVALUE minvalue | NOMINVALUE] -- Minimum value

[MAXVALUE maxvalue | NOMAXVALUE] -- maximum value

[CACHE cache_number | NOCACHE]

[CYCLE | NOCYCLE] -- indicates whether to CYCLE

[ORDER | NOORDER];

Note: The cache parameter specifies the number of pre-allocated sequences in the memory. The default value is 20. To speed up access.

The order parameter specifies whether to generate the serial number in the Request order. Generally, when the sequence is used to generate the primary key value, the effect is not significant.

(2) Two pseudo columns in the sequence

Currval: used to obtain the current value of the sequence. It can only be used once after nextval is used. - Nextval: used to obtain the next value of the sequence. The first time the returned value is the initial value, this pseudo column is used when the value is assigned to the primary key of the table. (3) sequence example

• First, create the student table:

Create table student ( Sid number (4) primary key, Sname varchar2 (8) not null );

• Create a student_seq Sequence

Create sequence student_seq

Start with 1

Increment by 1

Nocache nocycle order;

• Create a trigger (using the student_seq sequence)

 

 

Create trigger tr_student

Before insert on student

For each row

Begin

Select student_seq into: new. sid from dual;

End;

• Insert data:

SQL> insert into student (sname) values ('zhang ')

One row has been created.

SQL> insert into student (sname) values ('lil ');

One row has been created.

SQL> insert into student (sname) values ('wang ')

One row has been created.

• Query data:
• SQL> select * from student;
• SID SNAME
  ------------------
  1 zhang
  2 li
  3 wang