Sequence and trigger in Oracle

Sequence and trigger in Oracle

Sequence:

Sequence(Sequence) is a serial number generator. It can automatically generate serial numbers for rows in the table and generate a set of equal-interval values (in the type of numbers ). Its main purpose is to generate the table's primary key value, which can be referenced in the insert statement, or you can check the current value through the query, or increase the sequence to the next value.

The create sequence system permission is required to create a sequence. The sequence creation syntax is as follows:

Create sequence name [increment by N] [start with N] [{maxvalue/minvalue n | nomaxvalue}] [{cycle | nocycle}] [{cache n | nocache}];

Increment by is used to define the sequence step. If omitted, the default value is 1. If a negative value is displayed, the sequence value is decreased according to this step.

Start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.

Maxvalue defines the maximum value that a sequence generator can generate. The "nomaxvalue" option is the default option, indicating that no maximum value is defined. In this case, the maximum value generated by the system for an incremental sequence is the 27 power of 10. For a descending sequence, the maximum value is-1.

Minvalue defines the minimum value that can be generated by the sequence generator. The "nomaxvalue" option is the default option, indicating that there is no minimum value defined. What is the minimum value that the system can produce for the descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.

Cycle and nocycle indicate whether to cycle when the value of the sequence generator reaches the limit value. Cycle indicates loop, and nocycle indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.

Cache (buffer) defines the size of the memory block that stores the sequence. The default value is 20. Nocache indicates no memory buffer for the sequence. Buffer the sequence memory to improve the sequence performance.

The syntax for deleting a sequence is:

Drop sequence name;

Where:

The person who deletes the sequence should be the creator of the sequence or the user who has the permission to drop any sequence system. Once deleted, the sequence cannot be referenced.

Some parts of the sequence can also be modified in use, but the satrt with option cannot be modified. Modifications to the sequence only affect the subsequent sequence numbers. The generated sequence numbers remain unchanged. The syntax for modifying the sequence is as follows:

Create and delete Sequences

Example 1: Create sequence:

Create sequence ABC increment by 1 start with 10 maxvalue 9999999 nocycle nocache;

Execution result

The sequence has been created.

Step 2: delete sequence:

Drop sequence ABC;

Execution result:

The sequence has been discarded.

Note: The sequence created above is named ABC, which is an incremental sequence. The increment value is 1 and the initial value is 10. This sequence is not cyclic and memory is not used. No minimum value is defined. The default minimum value is 1 and the maximum value is 9 999 999.

[Case 1]

Question:
-- Trigger:
-- Add employee information. The serial number is used as the automatic serial number (generated by serial number ),
-- If the wage is less than 0, it is changed to 0. If the wage is greater than 10000, it is changed to 10000.

Create Table emp2 (
E_id number,
E_no number,
E_name varchar2 (20 ),
E_sal number
)

Select * From emp2;

Create sequence seq_trg_id;

Insert into emp2 (e_id, e_no, e_name, e_sal) values (seq_trg_id.nextval, 7788, 'zhangzi ',
1000000000000)
Insert into emp2 (e_id, e_no, e_name, e_sal) values (seq_trg_id.nextval, 7788, 'zhangziyi',-10)

Create or replace trigger trg_add_emp_info
Before insert
On emp2
For each row
Declare
-- Local variables here
Begin
Select seq_trg_id.nextval into: New. e_id from dual;
If: New. e_sal <0 then
: New. e_sal: = 0;
Elsif: New. e_sal & gt; 10000 then
: New. e_sal: = 10000;
End if;
End;

Case 2]

Question:

-- Expand exercise:
-- Create a trigger for EMP and put the deleted records in the emp3 table (autoid, deptno, empno, ename, del_rq-date of deletion)
-- Test code

Create Table emp3 (
Autoid number primary key,
Deptno number,
Empno number,
Ename varchar2 (20 ),
Del_rq date
)

Create sequence seq_trg_del_autoid;

Insert into EMP
(Empno, ename, deptno)
Values
(114, 'abaclo', 10 );
Commit;
 
Select * from EMP;
 
Delete E-mapreduce where e-mapreduce = 114;
Select * From emp3;
 
Answer:

Create or replace trigger trg_del_emp_info
Before Delete
On EMP
For each row
Declare
-- Local variables here
Begin
Insert into emp3 (autoid, deptno, empno, ename, del_rq)
Values (seq_trg_del_autoid.nextval,: Old. deptno,: Old. empno,: Old. ename, sysdate );
End;