Oracle Les11_创建其它数据库对象

1、创建其它数据库对象创建其它数据库对象11 - 2ObjectivesAfter completing this lesson, you should be able to do the following:创建视图通过视图检索数据创建、维护和使用序列创建和维护索引创建私有和公有的同义词11 - 3Database Objects逻辑上代表一个或多个表中数据的子集视图View 数字值生成器序列Sequence 基本的存储单元，由行和列组成 表Table给出对象的替代名称同义词Synonym 提高某些查询的性能索引IndexDescriptionObject11 - 4什么是视图什么是视图?EMP

2、LOYEES 表表11 - 5视图的特点视图的特点限制数据访问限制数据访问简化复杂的简化复杂的查询查询提供提供数据的数据的独立性独立性提供相同提供相同数据的数据的不同视图不同视图11 - 6简单视图和复杂视图简单视图和复杂视图能否否一个简单视图简单视图是是否包含函数是是否包含数据组一个或多个表的数量不一定能否通过视图执行DML操作复杂视图复杂视图特性特性11 - 7创建视图创建视图在CREATE VIEW 语句中嵌入了一个子查询:在此语法中：OR REPLACE如果该视图已经存在则重新创建它FORCE不管基表是否存在都创建视图NOFORCE仅当基表存在时才创建视图（默认）view是视图的名称a

3、lias指定由视图的查询所选择的表达式的名称subquery是一个完整的SELECT语句（可以在查询中使用别名）WITH CHECK OPTION指定只能插入或更新那些视图可以访问的行WITH READ ONLY确保不能对视图执行DML操作constraint为两个WITH选项命名该子查询可以包含复杂的SELECT 语句.CREATE OR REPLACE FORCE|NOFORCE VIEW view (alias, alias.) AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY CONSTRAINT cons

4、traint;11 - 8创建视图创建视图创建视图EMPVU80 , 该视图要包含部门80中员工的详细信息:像描述表的结构信息一样，使用DESCRIBE描述视图的结构 :DESCRIBE empvu80CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80;11 - 9创建视图的准则创建视图的准则 子查询，可以包含复杂的查询，包括多表联接，组函数。 如果创建的时候，使用了WITH CHECK OPTION这种约束，我们可以给此约束自定义约束的名称，或

5、者我们不进行指定，系统将自动分配一个SYS_Cn这种格式的名字给此约束命名. 我们可以使用or replace来重新创建视图，以实现修改的目的11 - 10创建视图创建视图在子查询中使用列别名来创建视图:通过给定的别名从此视图中选择列.CREATE VIEW salvu50 AS SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY FROM employees WHERE department_id = 50;11 - 11SELECT *FROM salvu50;从视图中检索数据从视图中检索数据11 - 12

6、从视图中检索数据从视图中检索数据Oracle ServerUSER_VIEWSSALVU50SELECT employee_id ID_NUMBER, last_name NAME,salary*12 ANN_SALARYFROM employeesWHERE department_id = 50;EMPLOYEES使用视图进行使用视图进行查询查询SELECT *FROM salvu50;12311 - 13修改视图修改视图使用CREATE OR REPLACE VIEW子句修改EMPVU80视图，为每个列名添加一个别名:CREATE VIEW子句中的列别名与子查询中的列排列顺序相同.CREA

7、TE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id)AS SELECT employee_id, first_name | | last_name, salary, department_id FROM employees WHERE department_id = 80;11 - 14创建复杂视图创建复杂视图创建包含分组函数的复杂视图以显示两个表中的值:CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)AS SELECT d.depart

8、ment_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY d.department_name;11 - 15在视图上执行在视图上执行DML操作的规则操作的规则可以在简单的视图上执行DML操作.如果视图包含以下内容，则不能通过视图删除行: 分组函数 GROUP BY 子句 DISTINCT 关键字 伪列 ROWNUM 关键字11 - 16在视图上执行在视图上执行DML操作的规则操作

9、的规则如果视图中包含以下内容，则不能通过视图修改数据:分组函数GROUP BY 子句DISTINCT 关键字伪列 ROWNUM 关键字由表达式定义的列11 - 17在视图上执行在视图上执行DML操作的规则操作的规则如果视图中包含以下内容，则不能通过视图添加数据:分组函数GROUP BY 子句DISTINCT 关键字伪列 ROWNUM 关键字由表达式定义的列基表中NOT NULL的列未包含在视图中11 - 18使用使用 WITH CHECK OPTION 子句子句使用 WITH CHECK OPTION 子句可以确保在视图上执行的DML操作发生在视图的范围内: 尝试在视图中更改任一行的部门编号都

10、不会成功，因为它违反了 WITH CHECK OPTION 约束.With read only;CREATE OR REPLACE VIEW empvu20AS SELECT* FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck ;11 - 19使用使用 WITH CHECK OPTION 子句子句WITH CHECK OPTION子句指定通过视图执行的INSERT和UPDATE命令不能创建该视图无法选择的行，从而允许对插入或更新的数据强制执行数据验证检查。如果试图对视图未选中的行执行D

11、ML操作，就会报错。上例中，如果部门编号更改为10，该视图就无法看到该员工了，因此不会更新任何行。所以，使用WITH CHECK OPTION子句，该视图只能看到部门20的员工，并且不允许通过该视图更改这些员工的部门编号。UPDATE empvu20 SETdepartment_id=10 WHEREemployee_id=201;11 - 20拒绝拒绝DML操作操作通过在视图中定义 WITH READ ONLY 选项可以确保不能执行DML操作.任何对视图中的执行 DML 的尝试都会收到一个Oracle server 错误.11 - 21CREATE OR REPLACE VIEW empvu

12、10 (employee_number, employee_name, job_title)AS SELECTemployee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY ;拒绝拒绝DML操作操作11 - 22删除视图删除视图因为视图是基于数据库中的基表，所以删除视图不会导致丢失数据.DROP VIEW view;DROP VIEW empvu80;11 - 23序列序列逻辑上代表一个或多个表中数据的子集视图View 数字值生成器序列Sequence 基本的存储单元，由行和列组成 表T

13、able给出对象的替代名称同义词Synonym 提高某些查询的性能索引IndexDescriptionObject11 - 24什么是序列什么是序列序列具有以下特性:自动生成唯一编号是一个可共享的对象通常用于创建主键值替换应用程序代码如果将序列缓存到内存中，可以提高访问序列值的效率2435687109111 - 25CREATE SEQUENCE 语句语法语句语法定义一个可以自动生成序列号的序列:sequence是序列生成器的名称INCREMENT BY n指定序列号之间的间隔，n是一个整数，如果省略，则为1START WITH n指定生成的第一个序列号，如果省略，则为1MAXVALUE n指

14、定序列可以生成的最大值NOMAXVALUE指定10的27次幂做为序列的最大值MINVALUE n指定序列的最小值NOMINVALUE指定-（1026）作为递减序列的最小值CYCLE|NOCYCLE指定达到最大或最小值后，序列是否继续生成值，默认NOCACHE n|NOCACHE指定Oracle服务器预先分配并保留内存中的值的数量，默认20个CREATE SEQUENCE sequence INCREMENT BY n START WITH n MAXVALUE n | NOMAXVALUE MINVALUE n | NOMINVALUE CYCLE | NOCYCLE CACHE n | NO

15、CACHE;11 - 26创建序列创建序列创建一个名为 DEPT_DEPTID_SEQ 的序列，将它用作表DEPARTMENTS的主键.不使用CYCLE选项.CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;11 - 27确认确认序列序列在USER_SEQUENCES数据字典中验证序列值.LAST_NUMBER列显示下一个可用的序列号（如果指定了NOCACHE）SELECT sequence_name,min_value,max_value, incremen

16、t_by,last_numberFROM user_sequences;11 - 28NEXTVAL and CURRVAL 伪列伪列NEXTVAL 会返回下一个可用的序列值，每次被引用时，它都会返回一个唯一的值，即使对于不同的用户也是这样.CURRVAL 会获得当前的序列值.必须对该序列发出 NEXTVAL，然后 CURRVAL.才能包含值。11 - 29 使用使用NEXTVAL和和CURRVAL的规则的规则可以在以下情况中使用NEXTVAL和CURRVAL： 不属于子查询一部分的SELECT语句的SELECT列表 INSERT语句中的子查询SELECT列表 INSERT语句中的VALUES

17、子句 UPDATE语句中的SET子句不能在以下情况中使用NEXTVAL和CURRVAL： 视图的SELECT列表 带DISTINCT关键字的SELECT语句 带有GROUP BY、HAVING和ORDER BY子句的SELECT语句 SELECT、DELETE或UPDATE语句中的子查询 CREATE TABLE或ALTER TABLE语句中的DEFAULT表达式11 - 30使用序列使用序列在地点标识为2500中插入一个名为Support的新部门:查看DEPT_DEPTID_SEQ 序列的当前值:INSERT INTO departments(department_id, departmen

18、t_name, location_id)VALUES (dept_deptid_seq.NEXTVAL, Support, 2500);SELECTdept_deptid_seq.CURRVALFROMdual;11 - 31缓存序列的值缓存序列的值在内存中高速缓存序列值可以更快地访问缓存的序列值.当发生以下情况时，序列值会出间断: 发生回退 系统崩溃 在其它表中使用了序列如果创建序列时使用了NOCACHE选项，可以通过查询USER_SEQUENCES表来查看下一个可用的值。11 - 32修改序列修改序列更改增量值、最大值、最小值、循环选项或高速缓存选项:ALTER SEQUENCE dept

19、_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;11 - 33修改序列的准则修改序列的准则必须是序列的所有者或对该序列具有ALTER权限.修改只会影响以后生成的序列号.如果要从不同的序列处重新开始，则必须删除原有的序列然后重新创建它.(不能修改start with)系统会执行一些验证操作.通过DROP SEQUENCE语句可以从数据字典中删除序列:序列一旦删除，就不能再引用它。DROP SEQUENCE dept_deptid_seq;11 - 34索引索引逻辑上代表一个或多个表中数据的子集视图View 数字值生成器序列S

20、equence 基本的存储单元，由行和列组成 表Table给出对象的替代名称同义词Synonym 提高某些查询的性能索引IndexDescriptionObject11 - 35什么是索引什么是索引索引具有以下特点:是一个schema对象通过指针可以让ORACLE服务器快速定位行通过使用快速路径访问方法来快速查找数据，降低磁盘I/O开销独立于它索引的表由ORACLE服务器自动的维护和使用11 - 36如何创建索引如何创建索引自动创建: 如果在表定义中定义了 PRIMARY KEY 和 UNIQUE 约束，系统会自动创建一个唯一性索引.手动创建: 用户可以在列上创建非唯一的索引，以加速对行的访问

21、.11 - 37创建索引创建索引在一个或多个列上创建索引:提高对表EMPLOYEES 的LAST_NAME列的查询访问速度:CREATE INDEX emp_last_name_idxON employees(last_name);CREATE UNIQUEBITMAPINDEX indexON table (column, column.);11 - 38索引创建准则索引创建准则什么情况下不要创建索引什么情况下不要创建索引:在查询中不经常该列作为条件预计多数查询检索的行要超过表中总行数的2% 到 4%表的更新比较频繁列包含大量的空值在 WHERE 子句或联结条件中频繁使用的一个或多个列列包含

22、较大范围的值被索引的列将作为表达式的一部分进行引用 表的行数相当的多，但是大多数查询检索的行，不到该表总行数的2% 到 4%什么情况下要创建索引什么情况下要创建索引:11 - 39确认索引确认索引USER_INDEXES数据字典包含索引的名称及其唯一性:USER_IND_COLUMNS数据字典包含索引名、表名和列名:SELECT ic.index_name,ic.column_name, ic.column_position col_pos,ix.uniquenessFROM user_indexes ix,user_ind_columns icWHERE ic.index_name=ix.i

23、ndex_nameAND ic.table_name=EMPLOYEES;11 - 40删除索引删除索引使用 DROP INDEX 命令可以从数据字典中删除索引:从数据字典中删除 emp_last_name_idx 索引:要删除索引，必须是索引的所有者或者具有 DROP ANY INDEX 权限.DROP INDEX emp_last_name_idx;DROP INDEX index;11 - 41同义词同义词逻辑上代表一个或多个表中数据的子集视图View 数字值生成器序列Sequence 基本的存储单元，由行和列组成 表Table给出对象的替代名称同义词Synonym 提高某些查询的性能索

24、引IndexDescriptionObject11 - 42为一个对象创建同义词为一个对象创建同义词通过创建同义词（对象的另一个名称）可以简化对对象的访问，使用同义词具有以下优点:易于引用其它用户所拥有的表缩短冗长的对象名可以为表、视图、序列、过程或其它对象创建同义词。在语法中：PUBLIC创建一个所有用户都可以访问的同义词synonym 是要创建的同义词的名称object标识要为其创建同义词的对象准则：1.该对象不能包含在程序包中2.私用同义词必须与同一用户拥有的所有其它对象不同名CREATE PUBLIC SYNONYM synonymFOR object;11 - 43创建和删除同义词创

25、建和删除同义词为DEPT_SUM_VU 视图创建一个简短的名称:删除同义词:CREATE SYNONYM d_sumFOR dept_sum_vu;DROP SYNONYM d_sum;11 - 44QuizIndexes must be created manually and serve to speed up access to rows in a table.1.True2.False11 - 45SummaryIn this lesson, you should have learned how to:Create, use, and remove viewsAutomatically generate sequence numbers by using a sequence generatorCreate indexes to improve speed of query retrievalUse synonyms to provide alternative names for objec