表格模板-SMSE10管理表对象 精品.ppt

上传人:lao****ou 文档编号:13490 上传时间:2022-09-21 格式:PPT 页数:78 大小:2.09MB
下载 相关 举报
表格模板-SMSE10管理表对象 精品.ppt_第1页
第1页 / 共78页
表格模板-SMSE10管理表对象 精品.ppt_第2页
第2页 / 共78页
表格模板-SMSE10管理表对象 精品.ppt_第3页
第3页 / 共78页
表格模板-SMSE10管理表对象 精品.ppt_第4页
第4页 / 共78页
表格模板-SMSE10管理表对象 精品.ppt_第5页
第5页 / 共78页
亲,该文档总共78页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述

《表格模板-SMSE10管理表对象 精品.ppt》由会员分享,可在线阅读,更多相关《表格模板-SMSE10管理表对象 精品.ppt(78页珍藏版)》请在第一文库网上搜索。

1、Managing Tables管理表管理表ObjectivesAfter completing this lesson, you should be able todo the following:Identify the various methods of storing dataOutline Oracle data typesDistinguish between an extended versus a restricted ROWIDOutline the structure of a rowCreate regular and temporary tablesManage sto

2、rage structures within a tableManage index-organized table,clusters and partitioned tableReorganize, truncate, drop a tableAdd, Drop or modify a column within a tableObtain table information Distribution of Rows Within a TableOracle Data TypesCHAR(N), NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)RAW(N

3、)DATETIMESTAMPBLOB, CLOB,NCLOB, BFILELONG, LONG RAWROWID, UROWIDVARRAYTABLEREFData typeBuilt-inUser-definedScalarRelationshipCollectionRepresenting Numeric DataUse the NUMBERNUMBER datatype to store real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to

4、 be portable among different Oracle platforms, and offer up to 38 decimal digits of precision. Oracle 9i DateTime SupportData TypeDescriptionDateFixed at 7 bytes for each row in the table. Default format is a string (such as DD-MON-YY) TIMESTAMPVaries from 7 to 11 bytes.A value representing a date a

5、nd time, including fractional seconds. INTERVAL YEAR TO MONTHFixed at 5 bytes.Stored as an interval of years and monthsINTERVAL DAY TO SECONDFixed at 11 bytes.Stored as an interval of days to hours minutes and secondsTIMESTAMP WITH TIME ZONEFixed at 13 bytes. A value representing a date and time, pl

6、us an associated time zone setting. TIMESTAMP WITH LOCAL TIME ZONE Varies from 7 to 11 bytes.TO_DATE (November 13, 1992, MONTH DD, YYYY) TO_DATE(13-NOV-92 10:56 A.M.,DD-MON-YY HH:MI A.M. )CURRENT_DATE CURRENT_TIMESTAMPINTERVAL YEAR TO MONTH Data TypeINTERVAL YEAR TO MONTH stores a period of time usi

7、ng the YEAR and MONTH datetime fields.INTERVAL YEAR (year_precision) TO MONTHINTERVAL 123-2 YEAR(3) TO MONTHIndicates an interval of 123 years, 2 months.INTERVAL 123 YEAR(3)Indicates an interval of 123 years 0 months.INTERVAL 300 MONTH(3)Indicates an interval of 300 months.INTERVAL 123 YEARReturns a

8、n error, because the default precision is 2, and 123 has 3 digits. CREATE TABLE time_example2 (loan_duration INTERVAL YEAR (3) TO MONTH); INSERT INTO time_example2 (loan_duration) VALUES (INTERVAL 120 MONTH(3); SELECT TO_CHAR( sysdate+loan_duration, dd-mon-yyyy) FROM time_example2; -todays date is 0

9、3-May-2003-查询结果:查询结果:03-May-2013Data Types for Storing Large ObjectsOracle provides six data types for storing LOBsCLOB and LONG for large fixed-width character dataNCLOB for large fixed-width national character set dataBLOB and LONG RAW for storing unstructured dataBFILE for storing unstructured da

10、ta in operating system filesThere are two distinct parts of LOB :LOB value and locator. The LOB column stores a locator to the LOBs valueLOB locatorLOB columnOf a tableLOB ValueData Types for Storing Large ObjectsLONG, LONG RAWSingle column per tableUp to 2 gigabytesSELECT returns dataData stored in

11、-lineNo object type supportSequential access to chunksLOBMultiple columns per tableUp to 4 gigabytesSELECT returns locator Data stored in-line or out-of-lineSupports object typesRandom access to chunksOracle9i Application Developers Guide - Large Objects (LOBs)PDF:1011页页 Data Types for Storing Large

12、 ObjectsExternal LOBs(BFILE):Store a locator to the physical file.Definition of BFILE objectsAssociation of BFILE object to corresponding external filesSecurity for BFILEsExternal LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying ope

13、rating system. You cannot locate a single BFILEBFILE on more than one device.Data Types for Storing Large ObjectsInternal LOBs: the LOB value is stored in the databaseManaging internal LOBsPL/SQL package DBMS_LOBOracle Call Interface(OCI)Oracle Objects for object linking and embeddingODBC/JDBCSQLROW

14、ID Data TypeUnique identifier for each row in the database.Does not stored explicitly as a column value.Although the ROWID does not directly give the physical address of a row, it can be used to locate the row.ROWID provides the fastest means of accessing a row in a table.ROWIDs are stored in indexe

15、s to specify rows with a given set of key valuesOOOOOOBBBBBBFFFRRRData object numberRelative file numberRow numberBlock numberROWID Data TypeData object (segment) identifier Datafile identifier Block identifier Row identifier SELECT DATA_OBJECT_ID FROM ALL_OBJECTS WHERE OBJECT_NAME = Sys_DepotOperat

16、eDetail; select extent_id, file_id,block_id,blocks from dba_extents where segment_name=Sys_DepotOperateDetail;ROWID Data TypeBBBBBBBBFFFFRRRRBlock numberRow numberFile number.SELECT department_id, rowid FROM hr.departments;DEPARTMENT_ID ROWID10 AAABQMAAFAAAAA6AAA20 AAABQMAAFAAAAA6AAB30 AAABQMAAFAAAAA6AAC ExampleRestricted ROWID(INDEX)Can identify rows within a segmentNeeds less spaceCollection Data TypesA collection is an object that contains other objects, where each contained object is of the

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 技术资料 > 统计图表

copyright@ 2008-2022 001doc.com网站版权所有   

经营许可证编号:宁ICP备2022001085号

本站为文档C2C交易模式,即用户上传的文档直接被用户下载,本站只是中间服务平台,本站所有文档下载所得的收益归上传人(含作者)所有,必要时第一文库网拥有上传用户文档的转载和下载权。第一文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。若文档所含内容侵犯了您的版权或隐私,请立即通知第一文库网,我们立即给予删除!



客服