北大青鸟oracle学习资料2

第三章

数据库对象本章任务使用Oracle创建各种数据库对象理解和使用序列理解和创建视图理解和创建索引本章目标数据库对象简介Oracle数据库对象又称模式对象数据库对象是逻辑结构的集合，最基本的数据库对象是表其他数据库对象包括数据库对象序列视图索引序列序列是用于生成唯一、连续序号的对象序列可以是升序的，也可以是降序的使用CREATESEQUENCE语句创建序列SQL>CREATESEQUENCEtoys_seq STARTWITH10 INCREMENTBY10 MAXVALUE2000 MINVALUE10 NOCYCLE CACHE10;指定内存中预先分配的序号数访问序列通过序列的伪列来访问序列的值NEXTVAL返回序列的下一个值CURRVAL返回序列的当前值SQL>INSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,‘TWENTY’,25);SQL>INSERTINTOtoys(toyid,toyname,toyprice)VALUES(toys_seq.NEXTVAL,’MAGICPENCIL’,75);指定序列的下一个值SQL>SELECTtoys_seq.CURRVALFROMdual;检索序列的当前值更改和删除序列

SQL>ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;使用ALTERSEQUENCE语句修改序列，不能更改序列的STARTWITH参数使用DROPSEQUENCE语句删除序列SQL>DROPSEQUENCEtoys_seq;视图视图以经过定制的方式显示来自一个或多个表的数据视图可以视为“虚拟表”或“存储的查询”创建视图所依据的表称为“基表”视图的优点有：提供了另外一种级别的表安全性隐藏的数据的复杂性简化的用户的SQL命令隔离基表结构的改变通过重命名列，从另一个角度提供数据创建视图3-1

studnostudnamestudmarkssubnostudcaste1Rob452Open2James334SC3Jesica405OpenStud_detailsStud_viewstudnostudnamesubno1Rob22James43Jesica5CREATEVIEWstud_viewASSELECTstudno,studname,subnoFROMStud_details;创建视图3-2创建视图的语法：

CREATE[ORREPLACE][FORCE]VIEWview_name[(alias[,alias]...)]ASselect_statement[WITHCHECKOPTION][WITHREADONLY];创建视图3-3使用WITHCHECKOPTION选项创建视图图CREATEORREPLACEVIEWpause_viewASSELECT*FROMorder_masterWHEREostatus='p'WITHCHECKOPTIONCONSTRAINTchk_pv;使用ORDERBY子句创建视图图CREATEORREPLACEVIEWord_venASSELECT*FROMvendor_masterORDERBYvenname;创建带有错误误的视图CREATEFORCEVIEWvenASSELECT*FROMvenmaster;联接视图2-1StudnoStudnameSubmrksSubno1Rob4522James3343Jesica404SubnoSubname2English4Maths5ScienceStud_detailsSub_detailsCREATEVIEWStud_sub_viewASSELECTStudno,Studname,Submrks,SubnameFROMStud_details,Sub_DetailsWHEREStud_details.Subno=Sub_details.Subno;Stud_sub_view联接视图StudnoStudnameSubmrksSubname1Rob45English2James33Maths3Jesica40Maths联接视图2-2创建外联接视视图CREATEVIEWven_ord_outj_viewASSELECTvm.vencode,venname,orderno,odate,ostatusFROMvendor_mastervm,order_masteromWHEREvm.vencode=om.vencode(+);SELECTvm.vencode,venname,orderno,odate,ostatusFROMvendor_mastervmLEFTOUTERJOINorder_masteromONvm.vencode=om.vencode;视图上的DML语句在视图上也可可以使用修改改数据的DML语句，如INSERT、UPDATE和DELETE视图上的DML语句有如下限限制：只能修改一个个底层的基表表如果修改违反反了基表的约约束条件，则则无法更新视视图如果视图包含含连接操作符符、DISTINCT关键字、集合合操作符、聚聚合函数或GROUPBY子句，则将无无法更新视图图如果视图包含含伪列或表达达式，则将无无法更新视图图索引3-1索引是与表相相关的一个可可选结构用以提高SQL语句句执行的性能能减少磁盘I/O使用CREATEINDEX语语句创建索索引在逻辑上和物物理上都独立立于表的数据据Oracle自动维护护索引索引3-2索引有各种类类型，除了标标准索引外，，还有一些特特殊类型的索索引：索引的类型基于函数的索索引反向键索引位图索引唯一索引组合索引索引3-3SQL>CREATEINDEXitem_indexONitemfile(itemcode)[TABLESPACEtablespace_name];创建标准索引引重建索引SQL>ALTERINDEXitem_indexREBUILD;删除索引SQL>DROPINDEXitem_index;唯一索引唯一索引确保保在定义索引引的列中没有有重复值Oracle自动在表表的主键列上上创建唯一索索引使用CREATEUNIQUEINDEX语句创建唯唯一索引SQL>CREATEUNIQUEINDEXitem_indexONitemfile(itemcode);组合索引组合索引是在在表的多个列列上创建的索索引索引中列的顺顺序是任意的的如果SQL语句的WHERE子句中引引用了组合索索引的所有列列或大多数列列，则可以提提高检索速度度SQL>CREATEINDEXcomp_indexONitemfile(p_category,itemrate);反向键索引反向键索引反反转索引列键键值的每个字字节通常建立在值值是连续增长长的列上，使使数据均匀地地分布在整个个索引上创建索引时使使用REVERSE关键键字SQL>CREATEINDEXrev_indexONitemfile(itemcode)REVERSE;SQL>ALTERINDEXrev_indexREBUIDNOREVERSE;位图索引位图索引适合合创建在低基基数列上位图索引不直直接存储ROWID，而而是存储字节节位到ROWID的映射射减少响应时间间节省空间占用用SQL>CREATEBITMAPINDEXbit_indexONorder_master(orderno);基于函数的索索引基于一个个或多个个列上的的函数或或表达式式创建的的索引表达式中中不能出出现聚合合函数不能在LOB类类型的列列上创建建创建时必必须具有有QUERYREWRITE权权限SQL>CREATEINDEXlowercase_idxONtoys(LOWER(toyname));SQL>SELECTtoyidFROMtoysWHERELOWER(toyname)='doll';获取索引引的信息息与索引有有关的数数据字典典视图有有：只能修改改一个底底层的基基表USER_INDEXES－用户户创建的的索引的的信息USER_IND_PARTITIONS－用户户创建的的分区索索引的信信息USER_IND_COLUMNS－与索索引相关关的表列列的信息息SQL>SELECTINDEX_NAME,TABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSORDERBYINDEX_NA