Oracle view, sequence and synonym, Set Operations

Oracle view, sequence and synonym, set operation 1. View (important) function: A View encapsulates a complex query statement. 1. syntax for creating a view:

Create view name AS subquery example: create view empv20 as select * FROM emp WHERE deptno = 20 for a VIEW that contains 20 departments. For Unauthenticated solutions: log on to sys as SYSDBA and grant create any view to username (scott)

 

2. query view:

SELECT * FROM empv20;

 

3. OR REPLACE

CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20;

 

4. updating a view should not contain real data in the view. In the subroutine, the created view actually has a creation condition, which is deptno = 20. if you change the Department Number of 7566 in the view to 30. example: Modify the Department ID of 7369

UPDATE empv20 SET deptno=30 WHERE empno=7566;

 

After re-querying the view, we can see that there are no more than 7566 employees in the view. What about the emp table? 7566 jones manager 7839-2975-81 30 found that the Department number in the emp table has changed, that is, the actual data content has been updated. Generally, when creating a view. SQL provides two important parameters: WITH CHECK OPTION cannot update the [Creation condition] of the view. For example, this parameter is used in view creation.

 CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH CHECK OPTION;

 

This cannot be updated! [ORA-01402: view with check option where clause violation] creation conditions cannot be updated at this time, but other fields can be updated normally. · With read only indicates that the created view is READ-ONLY and no data can be modified. Because the view itself is only used for query, it is best not to allow any data changes

 CREATE OR REPLACE VIEW empv20 AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY;

 

-- Create read-only view II. Sequence [Key] An auto-increment Column exists in many database systems. To achieve auto-increment in Orcale, you can only rely on the sequence, all auto-increment operations must be completed manually. Sequence creation format:

Create sequence sequence [MAXVALUE n] [NOMAXVALUE] [MINVALUE n] [NOMINVALUE] [increment by n] [start with m] [CYCLE (can be cyclically)] [CACHE (cached value)]

 

Example: 1. Create sequence:

CREATE SEQUENCE myseq;

 

2. after the sequence is created, all automatic growth should be handled by the user. Therefore, two operations are provided in the sequence: · nextVal: Obtain the next value of the sequence · currVal: get the current content of the sequence 3. the default growth rate is 1.

[INCREMENT BY n][START WITH m]

 

Use these two parameters to modify the amplitude and start value. 4. Create a sequence and set it to 1, 3, 5, and 7 cycles.

MAXVALUE 10 CYCLE CACHE 1;

 

3. Synonyms (understanding)

 SELECT SYSDATE from dual;

 

This dual table is actually in sys, but scott can directly access the dual table. Normally, you need to use "username. Table Name" to access other user tables ". This is a synonym that allows other users to access other user tables with a name. 1. Create a synonym:

CREATE SYNONYM

Synonym name FOR username. Table noun 2. Delete synonym:

DROP SYNONYM

Synonym name 4. Set operation provides three types of set in Orcale: UNION, INTERSECT, MINUS) · UNION combines multiple query results into one query result with no duplicate content · union all is the same as above, but there are repeated values. INTERSECT returns the same part of multiple query results. MINUS returns the difference set of two query results. Copy an emp table and copy the content of 20 of them.

CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20;

 

 

1. UNIONSELECT * FROM emp union select * FROM emp20; or 13 rows are returned without multiple rows. 2. union allselect * FROM emp union all select * FROM emp20; this is equivalent to EMP Data + emp20 table data 3. INTERSECT returns SELECT * FROM emp intersect select * FROM emp20; because only 20 departments are repeated, therefore, only information of 20 departments is returned. 4. MINUS difference SELECT * FROM emp minus select * FROM emp20. Information of 20 departments except emp is returned.