第章Oracle资料学习_001.ppt
- 文档编号:18628122
- 上传时间:2023-08-21
- 格式:PPT
- 页数:32
- 大小:354.50KB
第章Oracle资料学习_001.ppt
《第章Oracle资料学习_001.ppt》由会员分享,可在线阅读,更多相关《第章Oracle资料学习_001.ppt(32页珍藏版)》请在冰点文库上搜索。
,2,第11章其他表类型,本章要点:
了解临时表的特点。
熟练掌握不同类别的临时表的创建和使用。
熟练掌握外部表的创建。
掌握在创建外部表时可以指定的一些子句。
熟练掌握不同类型的分区表的创建。
掌握增加分区的不同方法。
掌握合并与删除分区的方法。
2,3,11.1临时表,在Oracle数据库中,临时表是“静态”的,它与普通的数据表一样,只需要一次创建。
其结构从创建到删除的整个期间都是有效的。
3,4,11.1.1临时表的特点,临时表主要有如下特点:
临时表只有在用户向表中添加数据时,Oracle才会为其分配存储空间。
为临时表分配的空间来自临时表空间,这避免了与永久对象的数据争用存储空间。
在临时表中存储数据是以事务或会话为基础的。
当用户当前的事务结束或会话终止时,临时表占用的存储空间将被释放,存储的数据也随着丢失。
与堆表一样,用户可以在临时表上建立索引、视图和触发器等。
4,5,11.1.2临时表的类别,由于临时表中存储的数据只在当前事务处理或者会话进行期间有效,因此临时表主要分为两种事务级别临时表和会话级别临时表,说明如下。
(1)事务级别临时表事务级别临时表是指临时表中的数据只在事务生命周期中存在。
当一个事务结束(提交或者回滚事务),Oracle自动清除临时表中的数据。
创建事务级别临时表时,需要使用ONCOMMITDELETEROWS子句。
(2)会话级别临时表会话级别临时表是指临时表中的数据只在会话生命周期之中存在,当用户退出,会话结束的时候,Oracle自动清除临时表中数据。
创建会话级别临时表时,需要使用ONCOMMITPRESERVEROWS子句。
5,6,11.1.3临时表的创建与使用,1创建事务级别临时表创建临时表时,如果指定ONCOMMITDELETEROWS子句,则表示创建的临时表是事物级别临时表。
语法如下:
CREATEGLOBALTEMPORARYTABLEtable_name(column_namedata_type,column_namedata_type,.)ONCOMMITDELETEROWS;2使用事务级别临时表临时表的使用方法与堆表的使用方法相同,都可以执行SELECT语句、INSERT语句等。
接下来使用前面创建的临时表temp_student执行相应的操作。
6,7,11.1.3临时表的创建与使用,3创建会话级别临时表在创建临时表时,如果指定ONCOMMITPRESERVEROWS子句,则表示创建的临时表是会话级别临时表。
语法如下:
CREATEGLOBALTEMPORARYTABLEtable_name(column_namedata_type,column_namedata_type,.)ONCOMMITPRESERVEROWS;4使用会话级别临时表会话级别的临时表中的数据,当用户退出会话结束时,Oracle自动清除该临时表中的数据。
接下来使用前面创建的临时表temp_book执行相应的操作。
7,8,11.1.3临时表的创建与使用,5删除临时表删除临时表的操作和删除堆表的操作是一样的,都是使用DROPTABLE语句。
删除该表后,该表的相关内容也从user_tables视图中删除。
【例11.5】删除前面所创建的临时表temp_student,如下:
SQLDROPTABLEtemp_student;表已删除。
8,9,11.2外部表,外部表是引用在数据库以外的文件系统中存储的数据的只读表,也就是说,外部表所要读取的数据存储在Oracle数据库外部的文件中,并且只能读取这些数据,不能进行数据写入。
9,10,11.2.1使用外部表读取外部文件,创建外部表,需要在CREATETABLE语句中,添加ORGANIZATIONEXTERNAL子句,在该子句中可以指定下面所示的一些选项:
TYPE:
用来指定访问外部表数据文件时所使用的访问驱动程序,该程序可以将数据从它们最初的格式转换为可以向服务器提供的格式。
Oracle提供的默认访问驱动程序是ORACLE_LOADER。
DEFAULTDIRECTORY:
用来指定所使用的目录对象,该目录对象指向外部数据文件所在目录。
LOCATION:
用来指定源数据文件。
ACCESSPARAMETERS:
用来设置访问驱动程序进行数据格式转换时的参数。
FIELDSTERMINATEDBY:
用来指定字段之间的分隔符。
10,11,11.2.1使用外部表读取外部文件,【例11.6】下面将实现外部表读取外部文件的操作。
(1)使用外部表对外部文件执行读取操作之前,首先创建外部文件。
在操作系统目录E:
external中,创建一个记录学生的图书证信息的文本文件card.txt,文件的内容如图11-1所示。
(2)创建一个目录对象,该目录对象将作为操作系统文件系统上目录的别名,用来指向数据文件位置的目录。
(3)接下来创建外部表。
创建外部表需要使用CREATETABLEORGANIZATIONEXTERNAL语句。
(4)使用SELECT语句,查询外部表e_card中的数据,如下:
参见教材P249,11,12,11.2.2使用REJECTLIMIT子句,处理外部表错误的子句包括REJECTLIMIT、BADFILE(或NOBADFILE)和LOGFILE(或NOLOGFILE)。
本节将介绍REJECTLIMIT子句的用法。
BADFILE(或NOBADFILE)和LOGFILE(或NOLOGFILE)的用法将在后面的两个小节中进行介绍。
在创建外部表时使用REJECTLIMIT子句,可以指定在数据类型转换期间允许出现的错误个数。
12,13,11.2.2使用REJECTLIMIT子句,【例11.7】下面将介绍使用REJECTLIMIT子句,处理外部表中出现的数据类型转换错误。
(1)首先模拟使用外部表时出现的一个错误。
在创建外部表e_card时,将cardnumber列的数据类型设置为NUMBER,如下:
参见教材P250
(2)在card.txt文件中,保存的文件内容不变,对应cardnumber列的字段仍然是非数字字符串类型。
(3)使用SELECT语句查询e_card表时,将会出现类型转换错误,如下:
参见教材P250,13,14,11.2.2使用REJECTLIMIT子句,(4)接下来,将使用REJECTLIMIT子句,处理上述错误。
重新创建表e_card,添加REJECTLIMITUNLIMITED子句,表示错误个数不受限制。
具体如下:
参见教材P250(5)使用SELECT语句查询e_card表,如下:
SQLSELECT*FROMe_card;未选定行(6)如果对外部文件中的内容进行修改,使得数据类型符合转换的要求。
例如将card.txt文件中第一行数据修改为如下内容:
参见教材P251,14,15,11.2.3使用BADFILE子句,如果指定NOBADFILE子句,则Oracle将会忽略所有数据类型转换错误。
【例11.8】重新创建外部表e_card,在创建时指定BADFILE子句,如下:
SQLCREATETABLEe_card(2cardidNUMBER(10),3cardnumberNUMBER(9),4studentidNUMBER(10),5registertiemDATE6)ORGANIZATIONEXTERNAL(7TYPEoracle_loader8DEFAULTDIRECTORYexternal_card9ACCESSPARAMETERS(10RECORDSDELIMITEDBYNEWLINE11BADFILEcard_bad.txt12FIELDSTERMINATEDBY,)13LOCATION(card.txt)14)REJECTLIMITUNLIMITED;表已创建。
15,16,11.2.3使用BADFILE子句,现在使用SELECT语句查询e_card表,如下:
SQLSELECT*FROMe_card;CARDIDCARDNUMBESTUDENTIDREGISTERTIEM-12008001117-9月-09从查询结果可以看出,符合转换要求的数据可以正常显示。
而不符合转换要求的数据行将被复制到card_bad.txt文件中,该文件的内容如图11-2所示。
16,17,11.2.4使用LOGFILE子句,LOGFILE子句用来指定一个记录错误信息的日志文件。
如果使用NOLOGFILE子句,则不会将错误信息写入任何日志文件中。
【例11.9】重新创建外部表e_card,在创建时指定LOGFILE子句,如下:
参见教材P252上面在创建e_card表时,使用LOGFILE子句指定记录错误信息的日志文件为card_log.txt,该文件将在执行上述语句时被自动创建,并与card.txt文件同目录。
使用SELECT语句查询e_card表,则card_log.txt文件中生成的内容如图11-3所示。
17,18,11.3分区表,在大型的数据库应用中,需要处理的数据量可以达到几十到几百GB,甚至达到TB级。
分区表是将一个非常大的表分割成较小的片段(分区)。
Oracle数据库对表或索引分区的方法有5种:
范围分区、散列分区、列表分区、组合范围散列分区和组合范围列表分区。
在创建分区表时,应该根据不同类型分区表的特点,选择合适的分区类型。
18,19,11.3.1使用RANGE关键字创建范围分区表,范围分区就是根据数据表中的某个值的范围进行分区,根据某个值的大小或次序,决定将每条数据分别存储在哪个分区上。
在创建范围分区表之前,首先需要检查当前数据库已经存在的表空间,以便于将不同的分区指定到不同的表空间中。
19,20,11.3.1使用RANGE关键字创建范围分区表,【例11.10】创建一个范围分区表,用来保存图书信息,并且根据图书的出版时间将添加的记录数据保存到不同的分区中。
(1)创建范围分区表part_book,如下:
参见教材P254
(2)使用INSERT语句,向part_book表中添加多条记录信息。
语句如下:
SQLINSERTINTOpart_bookVALUES(1,Oracle11g,23.5,25-11月-07);SQLINSERTINTOpart_bookVALUES(2,Struts2,22.8,25-11月-08);SQLINSERTINTOpart_bookVALUES(3,SSH,27.8,25-11月-09);(3)使用SELECT语句检索part_book表中的数据,并且使用PARTITION关键字指定所要查看的分区。
下面检索part1和part3分区中的数据:
SQLSELECT*FROMpart_bookPARTITION(part1);BIDBOOKNAMEBOOKPRICEBOOKTIME-1Oracle11g23.525-11月-07SQLSELECT*FROMpart_bookPARTITION(part3);BIDBOOKNAMEBOOKPRICEBOOKTIME-3SSH27.825-11月-09,20,21,11.3.2使用HASH关键字创建散列分区表,散列分区是通过HASH算法均匀分布数据的一种分区类型。
通过在I/O设备上进行散列分区,可以使得分区的大小一致。
创建散列分区需要使用PARTITIONBYHASH子句。
【例11.11】创建散列分区表part_book2,使用PARTITIONBYHASH子句,根据bid列进行散列分区。
具体如下:
SQLCREATETABLEpart_book(2bidNUMBER(4),3booknameVARCHAR2(30),4bookpriceNUMBER(4,2),5booktimeDATE6)PARTITIONBYHASH(bid)(7partitionpart1tablespacemytemp1,8partitionpart2tablespacemytemp29);表已创建。
21,22,11.3.3使用LIST关键字创建列表分区表,列表分区表是基于特定值的列表对表进行分区。
列表分区适用于分区列的值为非数字或日期数据类型,并且分区列的取值范围较少时使用。
创建列表分区需要使用PARTITIONBYLIST子句。
22,23,11.3.3使用LIST关键字创建列表分区表,【例11.12】使用LIST关键字,指定创建的表为列表分区表,并且根据表中的一列将数据存放到不同的分区中。
(1)创建列表分区表part_book3,根据bookpress列进行列表分区,如下:
参见教材P256
(2)使用INSERT语句向表part_book3中添加记录,语句如下:
参见教材P256(3)使用SELECT语句检索表part_book3中的记录。
查询part1分区中的记录如下:
参见教材P256,23,24,11.3.4创建组合范围散列分区表,组合范围散列分区,就是将范围分区和散列分区进行组合使用。
这种形式首先使用范围值进行分区,然后使用散列值进行分区。
【例11.13】创建一个组合范围散列分区表part_book4,按照booktime列进行范围分区,按照bid列进行散列分区,如下:
参见教材P256上述示例中,使用PARTITIONBYRANGE子句,根据booktime列创建了3个分区(即part1、part2和part3);使用SUBPARTITIONBYHASH子句,将每一个分区又基于bid列的HASH值将数据均匀分到两个不同的分区中。
24,25,11.3.5创建组合范围列表分区表,组合范围列表分区,就是将范围分区和列表分区结合使用。
这种形式首先使用范围值进行分区,然后使用列表值进行分区。
【例11.14】创建一个组合范围列表分区表part_book5,根据booktime列进行范围分区,根据bookpress列进行列表分区。
具体如下:
参见教材P257上述示例创建表part_book5中,使用PARTITIONBYRANGE子句,根据booktime列进行范围分区,具体来说,分成了3个区(即part1、part2和part3);使用SUBPARTITIONBYLIST子句,根据bookpress列进行列表分区,将前面的3个区分别分成了两个区(即part1_1、part1_2、part2_1、part2_2、part3_1和part3_2分区)。
25,26,11.3.6增加分区,增加表分区适应于所有的分区表形式,其语法如下:
ALTERTABLEtable_nameADDPARTITION.在增加分区时,需要注意以下两点:
在最后一个分区之后增加分区,分区值必须大于当前分区中的最大值。
如果当前存在MAXVALUE或DEFAULT值的分区,那么在增加分区时会出现错误。
这种情况只能采用分隔分区的方法,具体来说是指定SPLITPARTITION子句。
26,27,11.3.6增加分区,1为范围分区表增加分区
(1)在最后一个分区之后增加分区。
(2)在分区中间或开始处增加分区。
2为散列分区表增加分区为散列分区表增加分区,只需要使用ALTERTABELtable_nameADDPARTITION语句即可。
3为列表分区表增加分区为列表分区表新增加一个分区,和创建列表分区表时一样需要为分区使用VALUES子句指定取值列表。
27,28,11.3.7合并与删除分区,1合并分区合并分区,需要使用ALTERTABLEMERGEPARTITION语句。
2删除分区删除分区,需要使用ALTERTABLEDROPPARTITION语句。
28,29,11.3.7合并与删除分区,【例11.20】前面创建了范围分区表range_book,将这个表中的part5和part6分区进行合并,如下:
SQLALTERTABLErange_book2MERGEPARTITIONSpart5,part6INTOPARTITIONpart1;表已更改。
执行上述语句,将range_book表中的part5和part6分区合并成一个part1分区。
【例11.21】删除范围分区表range_book中的part1分区,如下:
SQLALTERTABLErange_bookDROPPARTITIONpart1;表已更改。
29,30,11.4实验指导从Excel电子表格生成图书信息表,实验指导11-1:
从Excel电子表格生成图书信息表在CREATETABLE语句中添加ORGANIZATIONEXTERNAL子句,创建一个外部表,并且读取Excel文件中的内容。
(1)在本地操作系统目录E:
external下,创建两个Excel文件book1.csv和book2.csv。
其中book1.csv文件如图11-4所示。
30,31,11.4实验指导从Excel电子表格生成图书信息表,
(2)使用system用户连接数据库,创建一个目录对象test_data,指向目录E:
external。
如下:
SQLCREATEDIRECTORYtest_dataASE:
external;目录已创建。
(3)使用bookstore用户连接数据库,然后创建外部表test_book,如下:
参见教材P261(4)创建外部表test_book后,可以使用SELECT语句检索该表。
语句如下:
参见教材P261(5)在外部EXCEL文件book1.csv中添加一行数据,然后使用第4步中的语句查询外部表。
可以发现显示的数据将包含新添加的数据行。
(6)在外部表中,不能进行添加、修改和删除数据操作,为了解决这个问题,可以将外部表中显示的数据导入数据库。
将test_book表导入mybook表,如下:
参见教材P262,31,32,11.5习题,参见教材P262,32,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 资料 学习 _001
![提示](https://static.bingdoc.com/images/bang_tan.gif)