Oracle Notes (12) collections, sequences

Oracle Notes (12) collections, sequences

First, the collection

In the operation of mathematics there is the concept of intersection, difference, and, complement, and in the data query also exists this concept, there are several 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 in two queries;
• minus : returns the different parts of a two query;

To verify the above actions, create a table with only 20-door employee information:

CREATE TABLE Emp20 as SELECT * from emp WHERE deptno=;

Example: Verifying the Union

SELECT * from EMP

UNION

SELECT * from EMP20;

Example: Verifying UNION ALL

SELECT * from EMP

UNION All

SELECT * from EMP20;

Example: verifying intersect

SELECT * from EMP

INTERSECT

SELECT * from EMP20;

Example: verifying minus

SELECT * from EMP

Minus

SELECT * from EMP20;

There was an unfinished topic at the time of grouping: All the people who received the bonus found the average wage, and all the people who did not receive the bonus were given the average wage, and the implementation code was as follows:

SELECT Comm,avg (SAL)

From EMP

GROUP by Comm;

This problem can only rely on the query connection operation, prepare two queries:

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

SELECT ' Uncomm ', AVG (SAL) from EMP WHERE Comm is NULL

UNION

SELECT ' COMM ', AVG (SAL) from EMP WHERE COMM are not NULL;

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

Second, sequence

In many data tables there is an operation called the autogrow column, but in Oracle, this automatic growth column is not automatic, but requires manual control by the user, which is mainly for the convenience of development, the syntax for creating sequences is as follows:

CREATE SEQUENCE SEQUENCE

[INCREMENT by N] [START with N]

[{MAXVALUE n| Nomaxvalue}]

[{MINVALUE n| Nominvalue}]

[{cycle| Nocycle}]

[{CACHE n| NOCACHE}];

Example: Creating a sequence

CREATE SEQUENCE Myseq;

Once a sequence has been created, the sequence can be accessed in the following two ways:

• The sequence name. Nextval: Let the sequence grow to the next content;
• Sequence name. Currval: Gets the contents of the current sequence;

Example: verifying the operation of a sequence

SELECT Myseq.currval from dual;

However, directly executing the above program will issue the following error message: "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;

SELECT Myseq.nextval from dual;

SELECT Myseq.currval from dual;

Sequences are generally used as primary keys, for example, a table is defined below:

DROP TABLE Mytab PURGE;

CREATE TABLE Mytab (

ID number PRIMARY KEY,

Name VARCHAR2 () not NULL

);

Now add the data to the Mytab table:

INSERT into Mytab (id,name) VALUES (myseq.nextval, ' name ');

Be sure to keep in mind that this process is done manually by the user and cannot be completed automatically.

By default, the sequence starts at 0 and increases by 1 each time, and can now be modified;

Example: creating a sequence that starts at 10 and grows by 2 each time

DROP SEQUENCE Myseq;

2 ten;

Example: want to define a sequence that can be looped between 1, 3, 5, 7, 9;

DROP SEQUENCE Myseq;

2 1 Ten 1 CYCLE NOCACHE;

about the sequence of CACHE Explanation:

In Oracle database, because the sequence is often used, so oracle for the range of performance, the sequence of operations to do the following processing.

Prepared a space, in this space, for the user to prepare a number of well-formed sequences, each operation is to take out the contents of the sequence from this space, but there is a problem, if the database instance is now closed, then the contents of the saved in this space may disappear, but although disappeared , but the database has grown well, so there will be a jump, and if you want to cancel out of this problem, the best way is to set the sequence to not cache, using the NoCache declaration.

Oracle Notes (12) collections, sequences