Oracle-day03 on

First, the View
(a) What is a view
A view is a database object, a virtual table that is exported from one or more data tables or views, and the data for the view is not really stored in the view, but is stored in the referenced data table, and the structure and data of the view are the result of querying the data table. Depending on the criteria given when the view is created, the view can be part of a data table or a union of multiple base tables, which stores the definition of the query statement to perform the retrieval to use when referencing the view.
Advantages of using Views:
1. Simplify data manipulation: Views Simplify how users work with data.
2. Focus on specific data: unnecessary data or sensitive data can not be present in the view.
3. The view provides a simple and effective security mechanism that allows you to customize the access rights of different users to the data.
4. Provide backward compatibility: Views enable users to create backward-compatible interfaces for tables when the schema of a table changes.
(ii) Create or modify view syntax

CREATE [OR REPLACE] [FORCE] VIEW view_nameAS subquery[WITH CHECK OPTION ][WITH READ ONLY]

Option Explanation:
OR REPLACE: ORACLE rebuilds the view automatically if an attempt to create it already exists;
Force: The view is automatically created by ORACLE regardless of the presence of the base table;
Subquery: A complete SELECT statement in which the alias can be defined;
With CHECK OPTION: The data rows that are inserted or modified must satisfy the constraints of the view definition;
With READ only: No DML operations can be performed on this view.
(iii) Delete view syntax

DROP VIEW view_name

(iv) Case

1. Creating and using a simple view
   What is a simple view? If the statement in the view is a single-table query, and there are no aggregate functions, we
   Called a simple view.
   Requirements: Create a view: Owner information for owner Type 1
   Statement:

Create or replace view view_owners1 as
SELECT * FROM T_owners where ownertypeid=1

利用该视图进行查询`select * from view_owners1 where addressid=1;`就像使用表一样去使用视图就可以了。对于简单视图，我们不仅可以用查询，还可以增删改记录。我们下面写一条更新的语句，试一下：`update view_owners1 set name=‘王刚‘ where id=2;`再次查询：`select * from view_owners1`查询结果如下： ![](http://i2.51cto.com/images/blog/201806/04/0e4d2b4cc58b990c6f1394997171b959.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)结果已经更改成功。我们再次查询表数据 ![](http://i2.51cto.com/images/blog/201806/04/6b2738461c0f6d123c2bc30440dd3f3c.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)发现表的数据也跟着更改了。由此我们得出结论：视图其实是一个虚拟的表，它的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化，更改了表的数据，视图也自然会变化。一个视图所存储的并不是数据，而是一条 SQL语句。2. 带检查约束的视图需求：根据地址表（T_ADDRESS）创建视图 VIEW_ADDRESS2 ,内容为区域 ID为 2 的记录。语句：

Create or replace view View_address2 as
SELECT * FROM t_address where areaid=2
With CHECK option

执行下列更新语句：

[/align]
Update View_address2 set areaid=1 where id=4

系统提示如下错误信息： ![](http://i2.51cto.com/images/blog/201806/04/8c31e9189ae052e65654d62e40df9459.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)3. 只读视图的创建与使用如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视图时指定 WITH READ ONLY选项，这样创建的视图就是一个只读视图。需求：将上边的视图修改为只读视图语句：

Create or replace view view_owners1 as
SELECT * FROM T_owners where ownertypeid=1
With Read only

修改后，再次执行 update 语句，会出现如下错误提示 ![](http://i2.51cto.com/images/blog/201806/04/fd9df668dc0c797df19656c6fba93109.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)4. 创建带错误的视图我们创建一个视图，如果视图的 SQL 语句所设计的表并不存在，如下

Create or replace view view_temp as
SELECT * FROM T_temp

T_TEMP 表并不存在，此时系统会给出错误提示 ![](http://i2.51cto.com/images/blog/201806/04/016f9401e4e7ee07a977d9e1e11b55dd.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)有的时候，我们创建视图时的表可能并不存在，但是以后可能会存在，我们如果此时需要创建这样的视图，需要添加 FORCE 选项，SQL 语句如下：create or replace FORCE view view_TEMP asselect * from T_TEMP此时视图创建成功。5. 复杂视图的创建与使用所谓复杂视图，就是视图的 SQL 语句中，有聚合函数或多表关联查询。我们看下面的例子：（1）多表关联查询的例子需求：创建视图，查询显示业主编号，业主名称，业主类型名称语句：

Create or replace view view_owners as
Select O.id owner number, o.name owner name, ot.name owner type
From T_owners o,t_ownertype ot
where o.ownertypeid=ot.id

  Use this view to query ' select * from View_owners ' Can this view be used to modify the data? Let's try the following statement: ' Update view_owners set owner name = ' Fan Xiaobing ' where owner number = 1; ' can be modified successfully. Let's try the following statement: ' Update view_owners set Owner type = ' Ordinary resident ' where owner number = 1; ' This time we will find that the system pop-up error prompt:! [] (http://i2.51cto.com/images/blog/201806/04/af92c44c167c173b198846a5e1d0633d.jpg?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) What does this mean? Is that the columns we need to change are not the columns of the key reservation table. What do you call a key reservation table? The key reservation table is a basic concept to understand the limitations of connection view modification. The table's primary key columns are all displayed in the view, and their values are unique and non-empty in the view. That is, the key value of the table is also the key value in a connection view, then it is called the table is the key reservation table. In our example, there are two tables in the view, the owner table (t_owners) and the Owner Type table (T_ownertype), where the T_owners table is the key reservation table, because the T_owners primary key is also the primary key for the view. The fields of the key reservation table can be updated, and non-key reservation tables cannot be updated. (2) Group Aggregation Statistical Query example requirements: Create a view, according to the date the amount of water charges, the effect is as follows! [] (http://i2.51cto.com/images/blog/201806/04/f41d427032a8aa433e1ee0bd20b8e270.jpg?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Statement:  

CREATE View View_accountsum as
Select Year,month,sum (Money) moneysum
From T_account
GROUP BY Year,month
ORDER BY Year,month

此例用到聚合函数，没有键保留表，所以无法执行 update 。 ![](http://i2.51cto.com/images/blog/201806/04/62eaff589cdb415edd2117d15c819424.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

Oracle-day03 on