---

layout: post title: "数据库之视图" subtitle: " " 1.基于实际表创建一个虚拟表，这个虚拟表包含了实际表的部分字段(有的字段不让看)，或者部分记录(有的记录不让看) 2.视图的数据，完全来自实际表。对视图的操作，就是对实际表的数据的操作。"" date: 2018-11-11 07:00:00 author: "青乡" header-img: "img/post-bg-2015.jpg" catalog: true tags: - database - 数据库

作用？解决什么问题？

官方文档
Overview of Views
A view is a tailored presentation of the data contained in one or more tables or other views. A view takes the output of a query and treats it as a table. Therefore, a view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.

For example, the employees table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access.

---

总结
官方文档，有个单词解释的特别好，就是对实际表的数据进行剪裁tailored。
1.垂直
按字段
2.水平
按记录

怎么解决？

是什么？

为什么叫虚拟表virtual table？

因为视图的数据，完全来自实际表。
对视图的操作，就是对实际表的数据的操作。

应用场景

工作使用

CREATE OR REPLACE VIEW V_JZPT_CASESORCETYPE
(dicid, typecode, diccode, dicname, parentid, treelevel, orderno)
AS
SELECT dicid,
            typecode,
            diccode,
            trim(dicname),
            (CASE
                WHEN SUBSTR (diccode, 3, 4) = '0000'
                THEN
                   '0'
                WHEN SUBSTR (diccode, 5, 2) = '00'
                THEN
                   SUBSTR (diccode, 1, 2) || '0000'
                ELSE
                   SUBSTR (diccode, 1, 4) || '00'
             END)
               AS parentid,
            (CASE
                WHEN SUBSTR (diccode, 3, 4) = '0000' THEN '一级'
                WHEN SUBSTR (diccode, 5, 2) = '00' THEN '二级'
                ELSE '三级'
             END)
               AS treelevel,
            orderno
       FROM t_jzpt_dicdetail //基于实际表，创建视图
      WHERE enabled = '1' AND typecode = 'D00029'
   ORDER BY diccode;
comment on table V_JZPT_CASESORCETYPE is '警综平台－案件类别';
comment on column V_JZPT_CASESORCETYPE.DICID is '案件类别ID';
comment on column V_JZPT_CASESORCETYPE.TYPECODE is '字典类型代码';
comment on column V_JZPT_CASESORCETYPE.DICCODE is '案件类别代码';
comment on column V_JZPT_CASESORCETYPE.DICNAME is '案件类别名称';
comment on column V_JZPT_CASESORCETYPE.PARENTID is '父级案件类别ID';
comment on column V_JZPT_CASESORCETYPE.TREELEVEL is '案件类型级别';
comment on column V_JZPT_CASESORCETYPE.ORDERNO is '排序号';

怎么使用

1.创建 //基于实际表创建一个虚拟表，这个虚拟表包含了实际表的部分字段(有的字段不让看)，或者部分记录(有的记录不让看)

2.查询 //视图主要用于查询 和查询实际表完全一样。

3.其他操作 //查询之外的操作比较少使用

参考

docs.oracle.com/cd/B28359_0…