视图创建

2019独角兽企业重金招聘Python工程师标准>>>

1.3 术语和缩略语#

此处请填入文档中的专业术语及解释。

序号	术语/缩略语	全称和说明
1	视图（VIEW）	视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。

2、视图设计规范#

2.1 本地视图的设计规范
2.1.1 通过dblink访问远程表或视图的时候，必须使用视图. 并且要求指定需要字段的最大集合。

当需要通过dblink访问远程表或视图的时候，必须使用视图。如果该类型的视图只提供数据查询功能，则必须加上read only 限制。
一个远程表只能创建一个对应视图,该视图给所有程序共享使用,这样做可以保证当远程表访问路径修改时,只会修改一个视图,而不会去修改程序中代码。可有效减少代码修改量,并可避免因为遗漏修改出现的错误。
而基于这个原因,在指定视图字段时必须选择所有可能用到字段的最大集合,以满足各个程序使用。

2.1.2 创建视图时禁止使用“select * from 表名”语句，要求将要用的字段在视图语句中列明。

因为在对视图进行解析的时候，会把“*”展开为表所对应的所有字段，这将影响到该视图的执行效率。因此必须在select后面显式的写出所有需要的字段名称。

2.1.3 视图创建语句中禁止使用FOR UPDATE子句.

FOR UPDATE子句会将他所涉及到的表锁定，影响其他用户对数据的操作。

2.1.4 视图中禁止使用ORDER BY子句

视图是在各个程序中共享使用,因此不能在视图中指定order by, 以避免不必要的性能消耗。建议在使用视图时再进行排序。

2.1.5 在使用WITH CHECK OPTION选项进行数据约束时，必须使用指定的约束名称

WITH CHECK OPTION选项是指,所有对该视图的修改,都必须满足视图定义中的Where子句中的所有条件,否则将返回错误。使用WITH CHECK OPTION选项进行数据约束时，必须在关键字constraint后面指定一个约束名。否则系统将自动指定一个名字(SYS_cn)，不便于管理。
例如:

create view querydata_bj_vw as select plan_id,start_date,end_date,contribution,region 
   from pts_contribution where region=’bj’ with check option constraint CK_pts_contribution_1;

如果所修改数据不满足where region=’bj’,则会返回错误,如下:

SQL> insert into querydata_bj_vw (plan_id,region) values (1,'sz'); ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> update querydata_bj_vw set region = 'sz' where plan_id=1; ORA-01402: view WITH CHECK OPTION where-clause violation

2.1.6 创建视图时，除了是通过DB_LINK访问的远程视图外，禁止嵌套其他视图。
嵌套视图会增加视图的复杂性，从而影响查询效率。
DB LINK视图规范请参见《数据库用户架构规范》。

2.1.7 在视图中不允许将远程视图和本地表进行关联

将远程视图和本地表关联会占用大量的网络资源，查询效率低。如下示例：

create or replace view emp_info_vw as select s.empmo,r.location from  staff_list s,location_rv  r
where s.location=r.location -- staff_list为本地表，location_rv为远程视图

需要将远程视图的数据先导入本地数据库的同构表，再将本地表与该同构表进行关联。如，上例可以先在本地建立与location_rv同构的表location_l，再将staff_list与location_l进行关联。
如果有这样的需求,需要通过数据同步的方式,先同步数据到本地的表,然后在与本地表进行关联

2.2 敏感数据屏蔽视图的设计规范

2.2.1 如果仅需要将表中部分数据开发给其它用户,可以使用敏感数据屏蔽视图。 屏蔽视图上的敏感字段需要做数据屏蔽，且屏蔽视图的字段名称需要与原字段一致。

2.3 远程视图的设计规范

2.3.1建议1：在使用远程视图时应避免大数据量操作

访问远程视图时,数据需要通过网络传输。因此,应避免大数据量的远程数据访问,可考虑使用其它方式实现,比如使用goldengate进行数据同步。
例如,对远程视图使用外连接,会引起对该视图的全表扫描,并经网络传输到本地,对数据库和网络都有性能影响。开发人员可优化远程视图的查询，或改写其逻辑，避免此情况发生。

select b.old_empno,b.emp_name,a.deptno,a.dept_name from  lbs_dept_info_rv a,staff_info b,lbs_staff_info_rv c  
where a.deptno (+) = c.deptno AND
c.empno (+) = b.old_empno;       -- lbs_dept_info_rv/lbs_staff_info_rv为远程视图 -- staff_info为本地表

2.3.2 对远程视图的操作必须及时调用commit/rollback

无论创建远程视图是否使用read only限制，在使用远程视图的的时候（包括select操作），必须在使用完毕或者事务结束后调用commit/rollback,并关闭远程连接，否则不能释放连接远程数据库的session资源。

create or replace procedure get_rec_info (v_policy_no number) is   v_cnt   number ;   v_sql   varchar2(200); begin   ....   select count(id)     into v_cnt 
    from rec_info_lds_rv where policy_no = syns_status='Y';   commit;   v_sql := ‘alter session close database link ’||v_dblink_name;        --该dblink名称从远程视图定义中获取   execute immediate v_sql;   .... end ;

附录#

附录A: 脚本模板 #

--普通视图 CREATE OR REPLACE VIEW pbs_dept_info_00 AS select C_DPT_CDE,C_DPT_CNM,C_DPT_CADDR from pbs_dept_info; --远程视图 CREATE OR REPLACE VIEW pbs_dept_info as select C_DPT_CDE,C_DPT_CNM,C_DPT_CADDR from pbs_dept_info@pbs
  --注意：远程视图在查询的时候会产生锁，所以对于远程视图的操作， --无论是update,delete,insert还是select，都要尽快commit或者rollback. drop view GBS_TRUSTEE_COMMISSION_DETAIL; --其他删除视图语句

附录 B: 《通过视图对数据更新的注意事项.doc》#

基于一个表的视图

在适当的情况下，可以通过视图对数据进行DML操作（如INSERT，UPDATE，DELETE）。以下为不能进行DML操作的情况：

DML类型	视图包含以下子句
Insert	聚合函数
	GROUP BY子句
	DISTINCT关键字
	伪列ROWNUM关键字
	用表达式定义的列
	数据表中存在的NOT NULL的列，但却不在视图中
Update	聚合函数
	GROUP BY 子句
	DISTINCT 关键字
	伪列ROWNUM 关键字
	用表达式定义的列
Delete	聚合函数
	GROUP BY子句
	DISTINCT关键字
	伪列ROWNUM关键字

例如：

CREATE OR REPLACE VIEW emp_vw
AS 
SELECT distinct emp_gender as gender     --性别 FROM emp;

则以下语句执行将出错：

INSERT INTO emp_vw(gender) VALUES (‘MALE’); UPDATE emp_vw SET gender ='FEMALE' ; DELETE FROM emp_vw where gender=’ MALE’;

基于多表连接的视图

在INSERT或UPDATE语句中不能显式或隐式的引用到任何非主键表中的字段。
例如：有两个表 EMP和DEPT。DEPT_NO是DEPT表的主键，EMP_NO是EMP表的主键。
基于以上两表建立连接视图：

CREATE OR REPLACE VIEW emp_dept_vw （emp_no，emp_name，dept_no，dept_name）  --视图列的别名 AS 
SELECT emp.emp_no，emp.emp_name，emp.dept_no，dept.dept_name  
FROM emp，dept  
WHERE emp.dept_no=dept.dept_no；

这个视图中，EMP_NO仍然充当主键角色(emp.emp_no)，而DEPT表中的DEPT_NO则只是一个关联字段（emp.dept_no=dept.dept_no），因此在这里，EMP表为主键表，DEPT表为非主键表，所以不能对视图中DEPT表里的任何字段做INSERT和UPDATE操作。
以下操作是非法的。

INSERT INTO emp_dept_vw(emp_no，emp_name，dept_no，dept_name) values (1,'张三',2,'行政部');   --不能为非主键表的字段（dept_name）添加数据 UPDATE emp_dept_vw set dept_name='财务部' where emp_no=1; --不能更新非主键表的字段（dept_name）的值

但如果仅操作主键表中的数据，则是允许的。如：

INSERT INTO emp_dept_vw (emp_id,emp_name,dep_no) values (1,'张三',2);   --emp_id,emp_name,dep_no 为主键字段 UPDATE emp_dept_vw set emp_name='张三' where emp_no=1;    --emp_name,emp_no 为主键字段 UPDATE emp_dept_vw set dept_id=1 where emp_no=1;         --dept_id,emp_no 为主键字段

如果需要向视图中的非主键表插入或更新数据，建议使用instead of触发器。如这里若要执行以下语句插入一条数据：

INSERT INTO emp_dept_vw(emp_no，emp_name，dept_no，dept_name) values (1,'张三',2,'行政部'); 

则应在视图emp_dept_vw上添加instead of触发器：

CREATE OR REPLACE TRIGGER tr_emp_dept_vw_I1
        INSTEAD OF INSERT ON emp_dept_vw
FOR EACH ROW BEGIN INSERT INTO EMP(emp_no，emp_name，dept_no) VALUES(:new. emp_no，:new. emp_name，:new. dept_no);     --将数据添加到EMP表 INSERT INTO DEPT(dept_no, dept_name) VALUES(:new. dept_no，:new. dept_name);   --将数据添加到DEPT表 END;

转载于:https://my.oschina.net/u/729507/blog/88676