第8章 视图、同义词、序列

专注、专业、创新、共赢第8章视图、同义词、序列2020/1/311专注、专业、创新、共赢上节回顾Oracle10g数据类型表操作：创建、修改和删除完整性约束实现2020/1/312专注、专业、创新、共赢本讲目标了解视图的概念和作用掌握视图的创建、修改和删除掌握同义词的概念及使用掌握序列的创建与使用2020/1/313专注、专业、创新、共赢本章内容本章内容:(1）视图(2)同义词(3)序列本章重点:(1)视图、同义词和序列的操作本章难点:(1)同义词概念2020/1/314专注、专业、创新、共赢8.1、数据库的视图概念视图是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为基表——BaseTable）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基表。视图可以由以下任意一项组成：(1)一个基表的任意子集(2)两个或者两个以上基表的合集(3)两个或者两个以上基表的交集(4)对一个或者多个基表运算的结果集合2020/1/315视图一经定义以后，就可以象表一样被查询、修改、删除和更新。使用视图有下列优点：(1)为用户集中数据，简化用户的数据查询和处理。(2)屏蔽数据库的复杂性。(3)简化用户权限的管理。(4)便于数据共享。(5)可以重新组织数据以便输出到其它应用程序中。专注、专业、创新、共赢8.2、创建视图1.用OEM创建视图2020/1/3162.使用createview语句创建视图语法格式：create[orreplace]view视图名[视图列名]asselect语句[withcheckoption][withreadonly]【例1】创建v1视图，包括”SALES”部门各员工的部门名称、员工编号、员工姓名、员工工资。createviewv1(部门,编号,姓名,工资)asselectdname,empno,ename,sal+nvl(comm,0)fromemp,deptwhereemp.deptno=dept.deptnoanddname='SALES';【例2】创建各部门员工的平均工资视图v2，包括部门编号和平均工资。createviewv2(部门编号,平均工资)asselectdeptno,avg(sal+nvl(comm,0))fromempgroupbydeptno专注、专业、创新、共赢8.3.1、查询视图1.select语句查询视图【例】查找“SALES”部门的员工编号、员工姓名和员工工资。2020/1/317【例】查找平均工资在2000以上的部门编号和平均工资。select*fromv2where平均工资2000;专注、专业、创新、共赢8.3.2、更新视图1.可更新视图要通过视图更新基表数据，必须保证视图是可更新视图。一个可更新视图满足以下条件：(1)没有使用连接函数、集合运算函数和组函数；(2)创建视图的SELECT语句中没有聚合函数且没有GROUPBY等关键字；(3)创建视图的SELECT语句中不包含从基表列通过计算所得的列；(4)创建视图没有包含只读属性。2020/1/318通过更新视图（包括插入、修改和删除）数据可以修改基表数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图，才能进行更新。【例】创建视图v3，该视图仅包括部门编号为10的员工信息。createviewv3asselect*fromempwheredeptno=10;2.插入数据：使用insert语句通过视图向基本表插入数据。【例】向v3视图中插入一条记录：insertintov3values(8888,'CLARK','MANAGER',7839,'09-6月-81',2450,200,10);insertintov3values(9999,'AAAA','MANAGER',7839,'09-6月-81',9000,500,20);3.修改数据：使用updateE语句可以通过视图修改基本表的数据。【例】将v3视图中所有员工的佣金增加100。updatev3setcomm=nvl(comm,0)+100;updatev3setcomm=666whereempno=8888;updatev3setcomm=333whereempno=9999;该语句实际上是将v3视图所依赖的基本表emp中所有部门编号为10的记录的佣金字段值在原来基础上增加100。若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。4.删除数据：使用delete语句可以通过视图删除基本表的数据。但要注意，对于依赖于多个基本表的视图，不能使用delete语句。例如，不能通过对v3视图执行delete语句而删除与之相关的基本表emp及v3视图的数据。【例】删除v3中编号为888的员工记录。deletefromv3whereempno=8888；deletefromv3whereempno=9999；专注、专业、创新、共赢8.3.2、修改视图的定义1.通过OEM修改视图2020/1/3192.使用SQL命令修改视图Oracle提供了alterview语句，但它不是用于修改视图定义，只是用于重新编译或验证现有视图。【例】修改视图v3的定义，包括员工编号、员工姓名、部门编号、薪金。createorreplaceforceviewv3asselectempno,ename,deptno,salfromemp；专注、专业、创新、共赢8.3.3、删除视图如果不再需要视图了，可以通过OEM和SQL语句两种方式，把视图的定义从数据库中删除。删除一个视图，就是删除其定义和赋予的全部权限。2020/1/3110删除视图的SQL语句是dropview，格式为：dropview[schema.]view_name其中schema是所要删除视图的用户方案；view_name是视图名。例如：dropviewv3;将删除视图v3。专注、专业、创新、共赢8.4、同义词2020/1/31111、创建同义词2、使用同义词3、删除同义词专注、专业、创新、共赢8.4.1创建、使用同义词1.利用OEM创建同义词2020/1/31122.利用CREATESYNONYM命令创建同义词语法格式：CREATE[PUBLIC]SYNONYM[schema.]synonym_nameFOR[schema.]object专注、专业、创新、共赢8.4.2查看、删除同义词1.利用OEM删除同义词2.利用DROPSYNONYM命令删除同义词语法格式：DROP[PUBLIC]SYNONYM[schema.]synonym_name说明：PUBLIC表明删除一个公用同义词。Schema指定将要删除的同义词的用户方案。synonym_name为将要删除的同义词名称。2020/1/3113查看同义词的名称select*fromuser_synonyms;专注、专业、创新、共赢8.5、序列序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。2020/1/3114专注、专业、创新、共赢8.5.1、创建序列利用SQL命令创建序列语法格式为：createsequence[schema.]sequence_name[incrementbyinteger][startwithinteger][maxvalueinteger|nomaxvalue][minvalueinteger|nominvalue][cycle|nocycle][cacheinteger|nocache][order|noorder]2020/1/3115INCREMENTBY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。STARTWITH定义序列的初始值(即产生的第一个值)，默认为1。MAXVALUE定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。CYCLE和NOCYCLE表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。order保证序列产生的顺序和请求的顺序是一致的，在并行模式下如果A、B同时对序列请求那么先产生的序列号必然返回给先请求的用户。例如当前序列号为10，A先请求B后请求那么11一定返回给A，12给B，在noorder的情况下，有可能11给B，10给Acache跳号建立序列时cache选项可以把N个序列存在cache中，然后用户到cache取序列，当oracle不正常关闭时，cache中的内容就会丢失，而再重新生成序列等待用户来取，这样就可能丢失一部分序列，出现跳号.Rollback缺口在执行语名时提取出了序列,当执行rollback语名取消事务操作时,被提取了来的序列就丢了,所以有rollback缺口专注、专业、创新、共赢8.5.2、序列的属性序列有两个属性1.nextval属性该属性返回序列生成的下一个值.2.currval属性该属性返回序列的当前值.注意：在第一次使用时必须首先使用序列的nextval属性,然后才能正确访问currval.2020/1/3116专注、专业、创新、共赢8.5.3、使用序列createsequencesqincrementby2startwith1maxvalue100minvalue1cyclecache20;2020/1/3117createtabletm(idnumber(4),namechar(8));createtabletn(idnumber(4),namechar(8));insertintotmvalues(sq.nextval,'aa');insertintotnvalues(sq.nextval,'aa');insertintotmvalues(sq.nextval,'aa');insertintotnvalues(sq.nextval,'aa');insertintotnvalues(sq.nextval,'aa');insertintotmvalues(sq.nextval,'aa');insertintotmvalues(sq.nextval,'aa');insertintotnvalues(sq.nextval,'aa');insertintotnvalues(sq.nextval,'aa');select*fromtm;select*fromtn;select*fromuser_sequences;--用户序列selectsq.currvalfromdual;--当前值selectsq.Nextvalfromdual;--下个值专注、专业、创新、共赢8.5.4、修改序列利用SQL命令修改序列语法格式：altersequence[schema.]sequence_name[incrementbyinteger][maxvalueinteger|nomaxvalue][minvalueinteger|nominvalue][cycle|nocycle][cacheinteger