搜索
ManagingTables管理表10-2ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Identifythevariousmethodsofstoringdata•OutlineOracledatatypes•DistinguishbetweenanextendedversusarestrictedROWID•Outlinethestructureofarow•Createregularandtemporarytables•Managestoragestructureswithinatable•Manageindex-organizedtable,clustersandpartitionedtable•Reorganize,truncate,dropatable•Add,Dropormodifyacolumnwithinatable•Obtaintableinformation10-3DistributionofRowsWithinaTableClusterIndex-organizedtableTableRandomOrderingofRowsGroupedOrderedPartitionedtable(Heap)(Sorted)(Clustered)10-4OracleDataTypesCHAR(N),NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)RAW(N)DATETIMESTAMPBLOB,CLOB,NCLOB,BFILELONG,LONGRAWROWID,UROWIDVARRAYTABLEREFDatatypeBuilt-inUser-definedScalarRelationshipCollection10-5RepresentingNumericData•UsetheNUMBERdatatypetostorerealnumbersinafixed-pointorfloating-pointformat.NumbersusingthisdatatypeareguaranteedtobeportableamongdifferentOracleplatforms,andofferupto38decimaldigitsofprecision.10-6Oracle9iDateTimeSupportDataTypeDescriptionDateFixedat7bytesforeachrowinthetable.Defaultformatisastring(suchasDD-MON-YY)TIMESTAMPVariesfrom7to11bytes.Avaluerepresentingadateandtime,includingfractionalseconds.INTERVALYEARTOMONTHFixedat5bytes.StoredasanintervalofyearsandmonthsINTERVALDAYTOSECONDFixedat11bytes.StoredasanintervalofdaystohoursminutesandsecondsTIMESTAMPWITHTIMEZONEFixedat13bytes.Avaluerepresentingadateandtime,plusanassociatedtimezonesetting.TIMESTAMPWITHLOCALTIMEZONEVariesfrom7to11bytes.TO_DATE('November13,1992','MONTHDD,YYYY')TO_DATE('13-NOV-9210:56A.M.','DD-MON-YYHH:MIA.M.')CURRENT_DATECURRENT_TIMESTAMP10-7INTERVALYEARTOMONTHDataType•INTERVALYEARTOMONTHstoresaperiodoftimeusingtheYEARandMONTHdatetimefields.INTERVALYEAR[(year_precision)]TOMONTHINTERVAL'123-2'YEAR(3)TOMONTHIndicatesanintervalof123years,2months.INTERVAL'123'YEAR(3)Indicatesanintervalof123years0months.INTERVAL'300'MONTH(3)Indicatesanintervalof300months.INTERVAL'123'YEARReturnsanerror,becausethedefaultprecisionis2,and'123'has3digits.CREATETABLEtime_example2(loan_durationINTERVALYEAR(3)TOMONTH);INSERTINTOtime_example2(loan_duration)VALUES(INTERVAL'120'MONTH(3));SELECTTO_CHAR(sysdate+loan_duration,'dd-mon-yyyy')FROMtime_example2;--today’sdateis03-May-2003----查询结果：03-May-201310-8DataTypesforStoringLargeObjects•OracleprovidessixdatatypesforstoringLOBs–CLOBandLONGforlargefixed-widthcharacterdata–NCLOBforlargefixed-widthnationalcharactersetdata–BLOBandLONGRAWforstoringunstructureddata–BFILEforstoringunstructureddatainoperatingsystemfiles•TherearetwodistinctpartsofLOB:LOBvalueandlocator.TheLOBcolumnstoresalocatortotheLOB’svalueLOBlocatorLOBcolumnOfatableLOBValue10-9DataTypesforStoringLargeObjectsLONG,LONGRAWSinglecolumnpertableUpto2gigabytesSELECTreturnsdataDatastoredin-lineNoobjecttypesupportSequentialaccesstochunksLOBMultiplecolumnspertableUpto4gigabytesSELECTreturnslocatorDatastoredin-lineorout-of-lineSupportsobjecttypesRandomaccesstochunksOracle9iApplicationDeveloper'sGuide-LargeObjects(LOBs)PDF:1011页10-10DataTypesforStoringLargeObjects•ExternalLOBs(BFILE):Storealocatortothephysicalfile.–DefinitionofBFILEobjects–AssociationofBFILEobjecttocorrespondingexternalfiles–SecurityforBFILEs•ExternalLOBsdonotparticipateintransactions.•Anysupportforintegrityanddurabilitymustbeprovidedbytheunderlyingoperatingsystem.•YoucannotlocateasingleBFILEonmorethanonedevice.10-11DataTypesforStoringLargeObjects•InternalLOBs:theLOBvalueisstoredinthedatabase•ManaginginternalLOBs–PL/SQLpackageDBMS_LOB–OracleCallInterface(OCI)–OracleObjectsforobjectlinkingandembedding–ODBC/JDBC–SQL10-12ROWIDDataType•Uniqueidentifierforeachrowinthedatabase.•Doesnotstoredexplicitlyasacolumnvalue.•AlthoughtheROWIDdoesnotdirectlygivethephysicaladdressofarow,itcanbeusedtolocatetherow.•ROWIDprovidesthefastestmeansofaccessingarowinatable.•ROWIDsarestoredinindexestospecifyrowswithagivensetofkeyvaluesOOOOOOBBBBBBFFFRRRDataobjectnumberRelativefilenumberRownumberBlocknumberROWIDFormat10-13ROWIDDataType•Dataobject(segment)identifier•Datafileidentifier•Blockidentifier•RowidentifierSELECTDATA_OBJECT_IDFROMALL_OBJECTSWHEREOBJECT_NAME='Sys_DepotOperateDetail';selectextent_id,file_id,block_id,blocksfromdba_extentswheresegment_name='Sys_DepotOperateDetail';10-14ROWIDDataTypeBBBBBBBBFFFFRRRRBlocknumberRownumberFilenumber..SELECTdepartment_id,rowidFROMhr.departments;DEPARTMENT_IDROWID10AAABQMAAFAAAAA6AAA20AAABQMAAFAAAAA6AAB30AAABQMAAFAAAAA6AAC……•Example•RestrictedROWID(INDEX)–Canidentifyrowswithinasegment–Needslessspace10-15CollectionDataTypes•Acollectionisanobjectthatcontainsotherobjects,whereeachcontainedobjectisofthesametype.Collectiontypesareparameterizeddatatypes.–VARRAYsareorderedsetsofelementscontainingacountandalimit.–NestedtablesaretableswithacolumnorvariableoftheTABLEdatatype.•UserdefineddatatypesVARRAYNestedtable10-16ANSIDatatypeConversionstoOracleDatatypes•Usefunctionstoautomaticallyconvertdatatotheexpecteddatatype•TO_NUMBER(),TO_DATE,TO_CHAR,…10-17StructureofaRowDatabaseblockRowheaderColumnlengthCo