视图序列同义词管理

1、辽宁工程技术大学 软件工程系,第八章 视图、序列、同义词管理,辽宁工程技术大学 软件工程系,8.1.1 创建普通视图,具有CREATE VIEW 权限就可以在自己的帐户下创建视图； 具有CREATE ANY VIEW 权限就可以在自己的帐户下或其他帐户下创建视图； 具有DROP VIEW 或 DROP ANY VIEW 就可以删除视图；,辽宁工程技术大学 软件工程系,创建视图的语法,CREATE FORCE|NOFORCE VIEW view_name (alias , alias) AS SQLQuery WITH CHECK OPTION CONSTRAINT constraint_nam

2、e WITH READ ONLY CONSTRAINT constraint_name;,辽宁工程技术大学 软件工程系,创建视图的语法,CONSTRAINT 约束 WITH CHECK OPTION ：不允许 INSERT和UPDATE； WITH READ ONLY ：不允许 INSERT、UPDATE和DELETE。,辽宁工程技术大学 软件工程系,8.1.1 创建普通视图,Create view dept30 As select ename,deptno,job, sal*12 sal12 From emp where deptno=30;,辽宁工程技术大学 软件工程系,创建连接视图,CR

3、EATE VIEW emp_dept AS SELECT emp.empno, emp.ename, emp.deptno,emp.sal,dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc IN (DALLAS, NEW YORK, BOSTON);,辽宁工程技术大学 软件工程系,创建连接视图,对于连接视图来说，它的操作要遵循下面规则： 1、一般规则 连接视图的任何INSERT、UPDATE、DELETE操作在同时刻只能修改其下属表. 2、UPDATE规则 连接视图的带有WITH CH

4、ECK OPTION 子句，则不能进行UPDATE.,辽宁工程技术大学 软件工程系,创建连接视图,3、DELETE规则 连接视图存在一个保留关键字，则该连接视图可以进行删除；如果建立时带有WITH CHECK OPTION 子句，则连接视图不能进行DELETE。 4、INSERT规则：如果建立时带有WITH CHECK OPTION 子句，则连接视图不能进行INSERT。,辽宁工程技术大学 软件工程系,创建连接视图,Create view clerk (id_number, person, depart, position ) As select empno,ename,deptno,job

5、From emp where job=clerk With check option constraint wco; 用户不能往clerk视图中作insert(或update)非clerk的记录。,辽宁工程技术大学 软件工程系,关于with check option,默认情况下，由于行通过视图进行添加或更新，当其不再符合定义视图的查询的条件时，它们即从视图范围中消失。 例如，可创建一个查询，从而定义一个视图以在表中检索所有员工薪水低于 $30,000 的行。如果该员工的薪水涨到了 $32,000，则查询视图时该特定员工将不再出现，因其薪水不符合视图所设的标准。但是，,辽宁工程技术大学 软件工程

6、系,关于 WITH CHECK OPTION,WITH CHECK OPTION 子句强制所有数据修改语句均根据视图执行，以符合定义视图的 where 语句中所设的条件。 如果使用该子句，修改行时需考虑到不让它在修改完后从视图中消失。任何可能导致行消失的修改都会被取消，并显示错误信息。 这个选项可以确保数据库中正在修改的数据的完整性。,辽宁工程技术大学 软件工程系,关于 WITH CHECK OPTION,CREATEVIEWEMP_VIEW2 (EMPNO,EMPNAME,DEPTNO,JOBTITLE,HIREDATE) ASSELECTEMPNO,ENAME,DEPT,JOB,HIRED

7、ATEFROMEMP WHEREDEPT=10 WITHCHECKOPTION; 下列SQL语句可能会导致不能更新： SQLUPDATEEMP_VIEW2SETDEPT=20WHEREDEPT=10; 已更新0行。,辽宁工程技术大学 软件工程系,检查无效视图,select alter view | owner | . | object_name | compile ; from dba_objects where status = INVALID and object_type = VIEW ;,辽宁工程技术大学 软件工程系,导出视图的语法,set linesize 150 set pages

8、ize 1000 set arraysize 8 set feedback off set heading off set long 5000 col view_name for a20 col text for a80 select CREATE OR REPLACE VIEW |VIEW_NAME| AS ,TEXT FROM USER_VIEWS order BY VIEW_NAME;,辽宁工程技术大学 软件工程系,8.2 管理实体视图,Oracle8i以后的版本提供可以创建实体视图(MATERIALIZED VIEW)，它确实存放有物理数据。实体视图包含定义视图的查询时所选择的基表中的

9、行。对实体视图的查询就是直接从该视图中取出行。,辽宁工程技术大学 软件工程系,创建实体视图前提,要有授权创建实体视图的权限（CREATE MATERIALIZED VIEW 或CREATE SNAPSHOT）； 必须有访问各个主表的权限，即 有SELECT ANY TABLE 的系统权限。 如果在另外的用户模式下创建实体视图，则： 需要有CREATE ANY MATERIALIZED VIEW或CREATE ANY SNAPSHOT、SELECT ANY TABLE 权限； 必须有CREATE TABLE、SELECT ANY TABLE系统权限。,辽宁工程技术大学 软件工程系,创建实体视图,

10、CREATE MATERIALIZED VIEW SNAPSHOT schema. materializede_view|snapshot segment_attributes_clause|LOB_storage_clause|CACHE|NOCACHE | CLUSTER cluster( column1,) patitioning_clauses parallel_clause build_clause | ON PREBUILT TABLE WITH | WITHOUT REDUCED PRECISION USING INDEX physical_attributes_clause|

11、TABLESPACE tablespace refresh_clause FOR UPDATE DISABLE | ENABLE QUERY REWRITE AS subquery;,辽宁工程技术大学 软件工程系,创建实体视图,示例： CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT BUILD IMMEDIATE AS SELECT t.month, d_name, SUM(f.sales) AS sum_sales FROM time t, product p, fact f WHERE f.curDate = t.curDa

12、te AND f.item = p.item GROUP BY t.month, d_name;,辽宁工程技术大学 软件工程系,创建实体视图,例2.自动刷新的实体视图: 下面语句创建一个复杂的实体视图all_emps,它查询DALLAS和BALTIMORE中的职工表: CREATE MATERIALIZED VIEW all_emps PCTFREE 5 PCTUSED 60 TABLESPACE users STORAGE (INITIAL 50K NEXT 50K) USING INDEX STORAGE (INITIAL 25K NEXT 25K) REFRESH START

13、WITH ROUND(SYSDATE + 1) + 11/24 AS SELECT * FROM fran.empdallas UNION SELECT * FROM marco.empbalt;,辽宁工程技术大学 软件工程系,创建实体视图,不考虑刷新的问题 ，上述实体化视图可简化为： CREATE MATERIALIZED VIEW all_emps PCTFREE 5 PCTUSED 60 TABLESPACE users AS SELECT * FROM emp; 注意:首先,在创建实体视图的表上应该有实体化视图日志 CREATE MATERIALIZED VIEW log on sco

14、tt.emp;,辽宁工程技术大学 软件工程系,8.2.3 与实体视图有关的数据字典,1.DBA_MVIEW_AGGREGATES存放实体视图的基本信息 2.DBA_MVIEW_ANALYSIS 存放实体视图的附加信息 3.DBA_MVIEW_DETAIL_RELATIONS 存放实体视图的子查询等信息 4.DBA_MVIEW_DETAIL_JOINS 存放实体视图的列的连接关系信息 5.DBA_MVIEW_DETAIL_KEYS 存放实体视图的列或表达式的信息 6.DBA_MVIEWS 存放实体视图的基本信息,辽宁工程技术大学 软件工程系,8.3 管理序列,序列是一个发布唯一数字的ORACLE

15、 对象，在需要时，每次按1或一定增量增加。序列通常用于产生表中的唯一主键或唯一索引等。 序列（sequence）是Oracle提供用于产生唯一号的一个简单方法。用序列技术可以实现许多一般程序所不能完成的工作，比如产品加密的产品号等。,辽宁工程技术大学 软件工程系,8.3 管理序列,1. Oracle序列号和高速缓存(Cache) Oracle的参数文件init.ora文件中有一个参数cache 。它就是用于设置序列号的初始化参数。当init.ora文件设置 cache 大于0 ,并且在创建序列时设置了cache 值时： 在实例启动后，Oracle自动产品一组序列号放在缓存中以便加快访问速度；

16、当这组序列号被用完时，Oracle有自动产生另外一组放序列号放在缓存中； 当实例关闭或异常退出时，该组还未用完的序列号将丢失，从而产生跳号现象。 为了避免序列号丢失现象产生，可以设置初始化参数为0，并创建序列时指定,辽宁工程技术大学 软件工程系,建立序列,CREATE SEQUENCE emp_no INCREEMNT By 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;,辽宁工程技术大学 软件工程系,使用序列,例：使用sequence insert into orders(orderno,custno) values(order_seq.nextval,

17、1032)； update orders set orderno- orderno=order_seq.nextval where orderno=10112；,辽宁工程技术大学 软件工程系,使用序列,一般情况下是在nextval使用之后才能使用currval，可以用它来产生同样的号，比如有一定货号有多种商品和数量： insert into line items(orderno,partno,quantity) values(order_seq.currval,20231,3)； insert into line_items(orderno,partno,quantity) values(or

18、der_seq.currval,29374,1)；,辽宁工程技术大学 软件工程系,创建序列,在ORACLE8 中，如果在建立序列的语句中未加上NOCACHE，则有可能在关闭系统再启动后产生跳号现象。如果你的系统要求不许跳号，请在创建序列时在后面加 NOCACHE 。,辽宁工程技术大学 软件工程系,修改序列,例1将emp_no修改为步长为2，最大值为9999： ALTER SEQUENCE emp_no INCRMENT By 2 MAXVALUE 9999 CYCLE ;,辽宁工程技术大学 软件工程系,查询序列视图,与序列有关的视图有： ALL_SEQUENCES DBA_SEQUENCES USER_SEQUENCES,辽宁工程技术大学 软件工程系,8.4 管理同义词,Orac