Oracle note (12) set and Sequence

1. Set

There is a concept of intersection, difference, union, and population in mathematical operations, and this concept also exists in data queries, which has the following connection symbols:

• Union:Connect two queries, the same part is not displayed;
• Union all:Connect two queries. The same part is displayed;
• Intersect:Returns the same part of the two queries;
• Minus:Returns different parts of two queries;

To verify the preceding operations, create a table that only contains information about employees in 20 departments:

 
Create TableEmp20As Select * FromEMPWhereDeptno=20;
 

Example:Verify Union

Select * FromEMPUnionSelect * FromEmp20;
 

Example:Verify Union all

 
Select * FromEMPUnion AllSelect * FromEmp20;
 

Example:Verify intersect

Select * FromEMPIntersectSelect * FromEmp20;
 

Example:Verify minus

 
Select * FromEMP minusSelect * FromEmp20;
 

I have left an unfinished question when I explained the grouping: all the persons who receive the bonus obtain the average salary, and all those who do not receive the bonus obtain the average salary.CodeAs follows:

SelectComm,AVG(Sal)FromEMPGroup ByComm;
 

In this case, you can only rely on the query connection operation to prepare two queries:

• The first query queries the average salary of all employees who receive bonuses;
• The second query is responsible for querying the average salary of all employees who do not receive the bonus;

Select'Uncomm ',AVG(Sal)FromEMPWhereCommIs NullUnionSelect'Comm ',AVG(Sal)FromEMPWhereCommIs Not Null;
 

For this connection query, you only need to understand its concept.

Ii. Sequence

In many data tables, there is an operation called auto-increment column. However, in Oracle, such auto-increment columns are not automatically controlled, but require manual control, the sequence creation syntax is as follows:

CreateSequence[Increment by N] [Start with N][{Maxvalue n | nomaxvalue}][{Minvalue n | nominvalue}][{Cycle | nocycle}][{Cache n | nocache}];
 

Example:Create Sequence

CreateSequence myseq;
 

After a sequence is created, you can use either of the following methods to access the sequence:

• Sequence name. nextval: increases the sequence to the next content;
• Sequence name. currval: obtains the content of the current sequence;

Example:Verify sequence operations

 
SelectMyseq. currvalFromDual;
 

But directly execute the aboveProgramThe following error message is prompted: "ORA-08002: sequence myseq. currval has not been defined in this session"

In Oracle, if you want to operate currval, you must first use nextval;

 
SelectMyseq. nextvalFromDual;SelectMyseq. currvalFromDual;
 

Sequences are generally used as primary keys. For example, the following table is defined:

Drop TableMytab purge;Create TableMytab (IDNumber Primary Key, NameVarchar2(20)Not Null);
 

Add data to the mytab table:

Insert IntoMytab (ID, name)Values(Myseq. nextval,'Name');
 

Remember that this process is performed manually by the user and cannot be completed automatically.

By default, the sequence starts from 0 and increases by 1 each time. Now, you can modify the sequence;

Example:Create a sequence, starting from 10, increasing by 2 each time

 
DropSequence myseq;CreateSequence myseq IncrementBy 2StartWith 10;
 

Example:You want to define a sequence that can appear cyclically between 1, 3, 5, 7, and 9;

DropSequence myseq;CreateSequence myseq IncrementBy 2StartWith 1Maxvalue10Minvalue1Cycle nocache;
 

Explanation of the cache in the sequence:

In Oracle databases, because sequences are frequently used, Oracle performs the following operations to achieve performance.

A piece of space has been prepared, and several generated sequences have been prepared for the user. Each operation extracts the sequence content from the space, but there is a problem, if the database instance is closed now, the content stored in this space may disappear, but although it disappears, the database has grown, so there will be a jump, if you want to cancel this problem, the best way is to set the sequence to not cache and use nocache declaration.