Oracle (CREATE VIEW)

Source: Internet
Author: User

Concept:

View: The so-called view is to extract one or more tables of data to generate a map, management view can also achieve the effect of the original table, convenient data management and security operations.

A view is actually a query SQL statement that displays related data in one or more tables or other views. The view uses the results of a query as a table, so the view can be thought of as a virtual table that stores the results of the query. Views originate from tables, and all modifications to view data are eventually reflected in the base table of the views, which must be subject to the integrity constraints of the base table.

Storage of views

Unlike tables, views do not require allocation of storage space, nor does the view contain actual data. A view simply defines a query, and the data in the view is fetched from the base table, which is generated dynamically when the view is referenced. Because views are based on other objects in the database, a view requires only the space in the data dictionary to hold its definition, without additional storage space.

Benefits of the View:

1. Information hiding

For example , there are wages in the S_emp table, you can create views and hide payroll information. (You can work with permissions so that a user can view only the view and cannot view the table.) )

2. make complex queries simple.

3. Data Independence

4. different representations of the same data.

Categories of views:

1. Simple View

2. Complex views

Comparison:

Simple View Complex view

Number of tables involved 1 1 or more

The Include function does not contain a

Contains group data does not contain

use the view by using the DML can not be

Creation of the View:

CREATE [OR REPLACE] [force| Noforce] VIEW view_name

[(alias[, alias] ...)]

As

Select ....

[With CHECK OPTION [CONSTRAINT CONSTRAINT]]

[With READ only]

Note

1.or Replace: represents a modified view

2.force| Noforce: Build This view even if the base table does not exist | The base table does not exist and does not establish this view, the default value.

3.alias: The name of the column in the View ( equivalent to the alias of the result column for the subquery )

4. subqueries can contain complex query syntax, which is hidden from the user.

5. the sub-query cannot contain an order by clause.

6.WITH CHECK OPTION The data row that is inserted or modified must satisfy the constraints of the view definition, in other words, when the keyword indicates a DML operation on view Only the contents of the Where condition limit in the select statement can be manipulated

7.WITH Read Only: This view is read-only and cannot be performed on any DML operations on this view .

8. View structure: desc view_name;

For example:

Create or Replace view MyView

As

Select Id,last_name,start_date

From S_emp

where ID <= 4;

At this point you can use:

1. View all data information in the view

SELECT * from MyView;

2. perform the insert:

INSERT into MyView values (111, ' haha ', ' 03-5 month -16 '); Insert Success!

3. once again, the data you just inserted cannot be found because this data does not meet id<=4, but it is s_emp to view the original table .

If:

Create or Replace view MyView

(id,name,s_date)

As

Select Id,last_name,start_date

From S_emp

Where ID <= 4

with CHECK option;

at this point you can use:

1. View all data information in the view

SELECT * from MyView;

2. perform the insert:

INSERT into MyView values (121, ' haha ', ' 03-5 month -16 '); Insert failed! Because the view constraint is id<=4, now the ID value inserted is 121, so the failure!

Create or Replace view MyView

(id,name,s_date)

As

Select Id,last_name,start_date

From S_emp;

Or

Create or Replace view MyView

As

Select Id,last_name,start_date s_date

From S_emp;

the names of the columns in MyView are id,name,s_date.

To create a complex view:

Complex views may contain groupings, group functions, multi-table joins, and so on.

For example:

CREATE or replace VIEW MyView

(Name, Minsal, Maxsal, Avgsal)

As SELECT D.name, MIN (e.salary),

MAX (E.salary), AVG (e.salary)

From S_emp E, s_dept D

WHERE e.dept_id = d.id

GROUP by D.name;

Viewing View information

Data dictionary user_views can be used ;

To delete a View object:

ROP VIEW view_name;

Oracle (CREATE VIEW)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.