oracle 第13讲 使用视图索引序列和同义词Word格式.docx
《oracle 第13讲 使用视图索引序列和同义词Word格式.docx》由会员分享，可在线阅读，更多相关《oracle 第13讲 使用视图索引序列和同义词Word格式.docx（14页珍藏版）》请在冰豆网上搜索。
ENAMEVARCHAR2（10）Y
SALNUMBER（7,2）Y
使用数据字典user_views查看用户视图
selectview_namefromuser_views;
VIEW_NAME
------------------------------
VU_EMP
查看视图文本：
selecttextfromuser_viewswhereview_name=upper（'
vu_emp'
）;
对简单视图的增、删、改操作
insertintovu_emp（empno,ename,sal）values（1000,'
郭永洪'
1500）;
updatevu_empsetsal=3000whereempno=1000;
deletefromvu_empwhereempno=1000;
视图本身没有任何数据，视图上的增、删、改、查操作都是针对视图基表来完成的。
练习：
建立视图vu_emp01，可以查看雇员的编号、姓名、工资和奖金。
并使用测试数据作增、删、改操作（数据自拟）。
（2）建立复杂视图
建立视图vu_gdept，按部门统计平均工资、合计工资、最高工资和最低工资。
createviewvu_gdeptas
selectdeptno,
round（avg（sal）,2）avgsal,
round（sum（sal）,2）sumsal,
round（max（sal）,2）maxsal,
round（min（sal）,2）minsal
fromemp
groupbydeptno;
建立视图显示工资高于部门平均工资的雇员姓名、工资和部门号。
createviewvu_emp02as
selectename,sal,deptno
wheresal>
（selectavg（e.sal）
fromempe
wheree.deptno=emp.deptno）;
（3）建立连接视图
建立视图vu_emp_dept，包含emp表的empno,ename,sal列和dept的dname列。
createviewvu_emp_deptas
selecte.empno,e.ename,e.sal,d.dname
fromempe,deptd
wheree.deptno=d.deptno;
使用数据字典视图user_updatable_columns查看视图列具有的DML权限。
selecttable_name,
column_name,
updatable,
insertable,
deletable
fromuser_updatable_columns
wheretable_name=upper（'
vu_emp_dept'
TABLE_NAMECOLUMN_NAMEUPDATABLEINSERTABLEDELETABLE
-----------------------------------------------------------------------
VU_EMP_DEPTEMPNOYESYESYES
VU_EMP_DEPTENAMEYESYESYES
VU_EMP_DEPTSALYESYESYES
VU_EMP_DEPTDEPTNOYESYESYES
VU_EMP_DEPTDNAMENONONO
练习1：
建立视图显示雇员的编号、姓名及其主管的编号和姓名。
练习2：
建立视图显示雇员的编号、姓名、工作、部门名称和工资等级。
（4）建立只读视图
建立只读视图vu_emp_d20，包含列empno,ename,job,sal,deptno。
createviewvu_emp_d20as
selectempno,ename,job,sal,deptno
fromemp
wheredeptno=20
withreadonly;
insertintovu_emp_d20（empno,ename,job,sal）values（1003,'
李四'
'
CLERK'
ORA-01733:
此处不允许虚拟列
建立只读视图显示雇员的编号、姓名、工作、部门名称和工资等级。
（5）建立带有check约束的视图
建立视图vu_emp_d30，显示30部门的雇员编号、姓名、工作、工资和部门号，不允许添加其它部门信息。
createviewvu_emp_d30as
selectempno,ename,job,sal,deptnofromempwheredeptno=30
withcheckoptionconstraintck_vuempd30;
测试：
insertintovu_emp_d30（empno,ename,job,sal,deptno）values（1004,'
李四1'
1500,20）;
ORA-01402:
视图WITHCHECKOPTIDNwhere子句违规
建立可以添加工作为‘CLERK’的雇员的视图，包含列编号、姓名、工作，不允许添加其他工种的雇员信息。
（6）建立视图时建立列别名
createviewvu_emp_d40as
selectempno雇员编号,
ename雇员姓名,
job雇员工作,
sal雇员工资,
deptno部门编号
wheredeptno=40
withcheckoptionconstraintck_vuempd40;
1.2维护视图
（1）修改视图定义
修改视图vu_emp_dept，增加列deptno。
createorreplaceviewvu_emp_deptas
selecte.empno,e.ename,e.sal,e.deptno,d.dname
重新编译视图
alterviewvu_emp_deptcompile;
（2）删除视图
dropviewvu_emp_dept;
2.使用索引
何时使用索引？
（1）索引正确的表和列
索引应该建立在where子句经常使用的表列上。
如果在大表上频繁使用某列或某几列作为条件执行检索操作，并且检索行数低于总行数的15%，那么应考虑在该列上建立索引。
为提高多表连接的性能，应该在连接列上建立索引。
如果经常需要基于某例或某几列执行排序操作，那么通过在这些列上建立索引，可以加快数据排序的速度。
不要在小表上建立索引。
（2）限制表的索引个数
索引主要用于加快查询速度，但会降低DML操作速度。
索引越多，DML操作速度会越慢，尤其会极大影响insert和delete操作速度。
因此，在规划索引时，需权衡查询和DML的需要。
（3）删除不需要的索引
删除在小表上建立的索引。
如果表小使用索引不会加快查询速度。
删除查询语句不会引用的索引。
如果在某列或某几列上建立的索引，但这些列不会在where子句中引用，那么应该删除相应的索引。
使用数据字典视图user_indexes查询索引信息：
selecttable_name,index_name,uniqueness,statusfromuser_indexes;
TABLE_NAMEINDEX_NAMEUNIQUENESSSTATUS
-----------------------------------------------------------------------------
DEPTPK_DEPTUNIQUEVALID
EMPPK_EMPUNIQUEVALID
EMP03PK_EMPNO03UNIQUEVALID
DEPT02UK_DNAMEUNIQUEVALID
DEPT02UK_DEPTNOUNIQUEVALID
XSXKPK_XSXK_XSBH_KCBHUNIQUEVALID
EMP01UK_EMP01_ENAMEUNIQUEVALID
EMP01PK_EMP01_EMPIDUNIQUEVALID
selecttable_name,index_name,column_namefromuser_ind_columns;
TABLE_NAMEINDEX_NAMECOLUMN_NAME
---------------------------------------------------------
DEPTPK_DEPTDEPTNO
EMPPK_EMPEMPNO
EMP03PK_EMPNO03EMPNO
DEPT02UK_DNAMEDNAME
DEPT02UK_DEPTNODEPTNO
XSXKPK_XSXK_XSBH_KCBHKCBH
XSXKPK_XSXK_XSBH_KCBHXSBH
EMP01UK_EMP01_ENAMEENAME
EMP01PK_EMP01_EMPIDEMPNO
2.1建立索引（index）
创建emp04表
createtableemp04（empno,ename,job,sal,deptno）
as
selectempno,ename,job,sal,deptnofromemp;
（1）建立单列索引
基于单个列建立索引，如果经常在where子句中引用某个列，那么应该考虑在该列上建立单列索引。
createindexi_emp04_jobonemp04（job）;
使用索引：
selectempno,ename,job,comm,deptnofromemp04wherejob='
;
（2）建立复合索引
基于多列建立的索引，如果经常在where子句需要引用同一表的多个列定位数据，那么可以考虑在这些列上建立复合索引。
createindexi_emp04_deptno_jobonemp04（deptno,job）;
下面两条语句使用索引：
selectempno,ename,job,comm,deptnofromemp04wheredeptno=20andjob='
selectempno,ename,job,comm,deptnofromemp04wheredeptno=20;
下面两条语句不使用索引：
selectempno,ename,job,comm,deptnofromemp04wheredeptno=20orjob='
selectempno,ename,job,comm,deptnofromemp04wherejob='
（3）建立非唯一索引
索引列值可以重复的索引。
createindexi_emp04_enameonemp04（ename）;
（4）建立唯一索引
索引列值不能重复的索引。
createuniqueindexi_emp04_empnoonemp04（empno）;
2.2维护索引
（1）重建索引
在使用delete删除数据时，在索引上只是逻辑删除，其占用的空间不能供其他插入操作使用。
频繁执行update和delete操作，应定期重建索引，提高空间利用率。
alterindexi_emp04_jobrebuild;
（2）联机重建索引
当用户正在表上执行DML操作时，重建索引会失败。
指定online选项可以最小化DML影响。
alterindexi_emp04_jobrebuildonline;
（3）删除索引
dropindexi_emp04_job;
3.使用序列（sequence）
3.1建立序列
（1）建立序列
语法：
CreatesequenceSqe_Name
[incrementbyn]
[startwithn]
[{maxvaluen|nomaxvalue}]
[{minvaluen|nominvalue}]
[{cycle|nocycle}]
[{cachen|nocache}]
说明：
Sqe_Name：
序列对象名。
incrementbyn：
序列递增值（正整数）或递减值（负整数）。
startwithn：
起始序列值（该项缺省，则默认为1）。
maxvaluen：
可产生的最大序列值（大于或等于startwith指定的值，且大于minvalue），默认值为nomaxvalue。
Minvaluen：
可产生的最小序列值（小于或等于startwith指定的值，且小于maxvalue），默认值为nominvalue。
Cycle：
达到序列最大值或最小值后是否继续生成序列号，默认为nocycle。
Cachen：
在内存中可以预分配的序列号个数（默认20），nocache指不使用内存。
例1：
建立一个产生雇员编号的序列，雇员编号从8000开始，依次增长1，最大值9999，不允许序列循环，不使用高速缓存技术。
createsequenceseq_empno
incrementby1
startwith8000
maxvalue9999
nocache
nocycle;
例2：
建立一个产生部门编号的序列，雇员编号从50开始，每次增长10，最大值99，高速缓存10个。
createsequenceseq_deptno
incrementby10
startwith50
maxvalue99
cache10;
以上序列将产生50，60，70，80，90共5个依次递增的序列值。
查询序列：
selectsequence_name,increment_by,cache_size,max_value,last_numberfromseq;
（2）使用序列
伪列nextval返回下一个序列值，首次使用序列时，只能使用为例nextval。
位列currval返回当前序列值，currval使用前，必须使用nextval产生一个序列值。
在dept表中添加研发部门，使用序列seq_deptno产生研发部门的部门编号，部门地点在常州。
insertintodept（deptno,dname,loc）values（seq_deptno.nextval,'
研发部'
常州'
查询：
select*fromdept;
DEPTNODNAMELOC
---------------------------------
50研发部常州
10ACCOUNTINGNEWYORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
为研发部门添加一个雇员“马云”，职位“CEO”，月薪2万，入职日期“2011-5-24”，雇员编号使用序列seq_empno产生。
insertintoemp（
empno,
ename,
job,
sal,
hiredate,
deptno）
values（seq_empno.nextval,
'
马云'
CEO'
20000,
to_date（'
2011-5-24'
yyyy-mm-dd'
）,
seq_deptno.currval）;
select*fromemp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
8000马云CEO2011/5/2420000.0050
7369SMITHCLERK79021980/12/171760.00160.0020
7499ALLENSALESMAN76981981/2/201600.00300.0030
7521WARDSALESMAN76981981/2/221250.00500.0030
7566JONESMANAGER78391981/4/23272.50297.5020
7654MARTINSALESMAN76981981/9/281250.001400.0030
7698BLAKEMANAGER78391981/5/12850.0030
7782CLARKMANAGER78391981/6/91500.00300.0010
7788SCOTTANALYST75661987/4/193300.00300.0020
7839KINGPRESIDENT1981/11/175000.0010
7844TURNERSALESMAN76981981/9/81500.000.0030
7876ADAMSCLERK77881987/5/231210.00110.0020
7900JAMESCLERK76981981/12/3950.0030
7902FORDANALYST75661981/12/33300.00300.0020
7934MILLERCLERK77821982/1/231300.0010
创建一个序列seq_author为作者提供编号，范围1000至9999，递增步长为1。
并使用该序列产生器产生的编号在author表中添加两条作者信息。
3.2维护序列
（1）修改序列
当序列值不符合实际需求时，可以执行altersequence命令修改序列。
altersequenceSqe_Name
注意：
启动序列号不可以修改（startwithn）
序列seq_deptno的递增步长修改为1
altersequenceseq_deptno
incrementby1
selectseq_deptno.nextvalfromdual;
NEXTVAL
----------
91
（2）删除序列
dropsequenceseq_deptno;
4.使用同一词
4.1建立同义词
授予scott账户创建公共同义词的权限。
grantcreatepublicsynonymtoscott;
授予scott账户创建私有同义词的权限。
grantcreatesynonymtoscott;
（1）建立公共同义词
createpublicsynonympublic_empforscott.emp;
分别用scott账户和system账户访问同义词表：
select*frompublic_emp;
7654MARTINSALESMAN76981981/9/281250.0
升级会员 