oracle对象解读系列之五自定义类型对集合类型的支持

ORACLE对象解读系列之自定义类型对集合类型的支持文档作者创建日期2013/07/13更新日期文档编号当前版本10ORACLEDATABASEVERSON11G拷贝号_文档控制变更记录11日期作者版本变更说明审阅姓名职位分发拷贝编号姓名位置/岗位1234备注本系列讲述了关于ORACLESQL对象的基本信息，解释了什么是对象类型和子程序，描述了如何创建和如何使用一个来自于共享的根和通过继承对象类型层次结构。本节介绍了ORACLE自定义对象类型对集合数据的支持，包括NESTEDTABLE和VARRAY。介绍了如何创建和使用这两种数据类型。本节要求读者会使用CREATETYPEXXXASOBJECT创建SQL对象类型。阅读本系里之前建议先阅读ORACLE对象解读系列之对象的创建和简单使用创建SQL对象类型和集合类型参考ORACLEDATABASEPL/SQLLANGUAGEREFERENCESQL集合数据类型ORACLE支持数组和嵌套表这两种数据集合类型，其中数组是一组有序的元素组成的集合嵌套表是可以有任意数量的元素如果你是想暂时保存一组数量固定的元素，或者按照顺序来循环一组元素，或者在进行函数调用的时候需要返回一组处理的元素，那么可以选择使用数组；如果你想更高效的去执行一个集合的查询，或者执行大量数据的DML操作那么可以选择使用嵌套表。创建集合类型创建集合类型下面通过一个例子来说明如何创建一个集合类型DROPTYPEPERSON_TYPFORCECREATEORREPLACETYPEPERSON_TYPASOBJECTIDNONUMBER,NAMEVARCHAR230,PHONEVARCHAR220,MAPMEMBERFUNCTIONGET_IDNORETURNNUMBER,MEMBERPROCEDUREDISPLAY_DETAILSSELFINOUTNOCOPYPERSON_TYPCREATEORREPLACETYPEBODYPERSON_TYPASMAPMEMBERFUNCTIONGET_IDNORETURNNUMBERISBEGINRETURNIDNOENDMEMBERPROCEDUREDISPLAY_DETAILSSELFINOUTNOCOPYPERSON_TYPISBEGINDBMS_OUTPUTPUT_LINETO_CHARIDNO|NAME|PHONEENDENDCREATETYPEPEOPLE_TYPASTABLEOFPERSON_TYPNESTEDTABLETYPE实例化一个集合类型可以像实例化一个对象那样的去对一个集合类型进行实例化。DECLAREPEOPLE_TBPEOPLE_TYPBEGINPEOPLE_TBPEOPLE_TYPPERSON_TYP1,JOHNSMITH,16505550135END集合的构造方法下面的例子是先创建一个包含了嵌套表类型列的表，然后展示了如何使用它的构造方法。CREATETABLEPEOPLE_TABGROUP_NONUMBER,PEOPLE_COLUMNPEOPLE_TYPANINSTANCEOFNESTEDTABLENESTEDTABLEPEOPLE_COLUMNSTOREASPEOPLE_COLUMN_NTSTORAGETABLEFORNTINSERTINTOPEOPLE_TABVALUES100,PEOPLE_TYPPERSON_TYP1,JOHNSMITH,16505550135,PERSON_TYP2,DIANESMITH,NULL疑问点当你查看数据的时候，如果使用SELECT查看PEOPLE_COLUMN列，PLSQLDEVLOPER会强制关闭，通过COMMAND_WINDOW则不会可以使用下面方法来访问DECLAREPEOPLE_TBPEOPLE_TYPBEGINSELECTPEOPLE_COLUMNINTOPEOPLE_TBFROMPEOPLE_TABWHEREGROUP_NO100FORIINPEOPLE_TBFIRSTPEOPLE_TBLASTLOOPDBMS_OUTPUTPUT_LINEPEOPLE_TBINAMEENDLOOPENDSELECTEFROMDEPARTMENT_PERSONSD,TABLEDDEPT_EMPSE下面的一个例子展示了如何给集合类型设置默认值CREATETABLEDEPARTMENT_PERSONSDEPT_NONUMBERPRIMARYKEY,DEPT_NAMECHAR20,DEPT_MGRPERSON_TYPDEFAULTPERSON_TYP10,JOHNDOE,NULL,DEPT_EMPSPEOPLE_TYPDEFAULTPEOPLE_TYPINSTANCEOFNESTEDTABLETYPENESTEDTABLEDEPT_EMPSSTOREASDEPT_EMPS_TABINSERTINTODEPARTMENT_PERSONSVALUES101,PHYSICALSCIENCES,PERSON_TYP65,VRINDAMILLS,16505550125,PEOPLE_TYPPERSON_TYP1,JOHNSMITH,16505550135,PERSON_TYP2,DIANESMITH,NULLINSERTINTODEPARTMENT_PERSONSVALUES104,LIFESCIENCES,PERSON_TYP70,JAMESHALLPEOPLE_TYPANEMPTYPEOPLE_TYPTABLE数组动态数组是一组有序的数据元素。在一个数组中的所有元素都是相同类型的或者是所定义数组类型的子类。数组的每一个元素都有一个下标，标识着这个元素在数组中的位置。在定一个数组的时候，尽管你可以改变这个数组的长度，但是你也必须指定数组的长度。CREATETYPEEMAIL_LIST_ARRASVARRAY10OFVARCHAR280上面的语句创建了一个类型为VARCHAR2的长度为10的数组。下面的例子创建了一个类型为自定义类型的数组，并将这个数组当成一个列放到表中DROPTYPEPHONE_TYPFORCECREATETYPEPHONE_TYPASOBJECTCOUNTRY_CODEVARCHAR22,AREA_CODEVARCHAR23,PH_NUMBERVARCHAR27CREATETYPEPHONE_VARRAY_TYPASVARRAY5OFPHONE_TYPCREATETABLEDEPT_PHONE_LISTDEPT_NONUMBER5,PHONE_LISTPHONE_VARRAY_TYPINSERTINTODEPT_PHONE_LISTVALUES100,PHONE_VARRAY_TYPPHONE_TYP01,650,5550123,PHONE_TYP01,650,5550148,PHONE_TYP01,650,5550192嵌套表嵌套表是一组没有顺序的数据元素的集合，他们的类型都是相同的。如果一个嵌套表只有一列，该列的类型如果是一个对象类型，那么这个嵌套表可以看成是多列表，对象列的每一个属性就是一列。嵌套表的元素实际上是存在一个单独的表中的，系统会产生一个长度为16BYTE的NESTED_TABLE_ID，通过这个ID来使嵌套表中的对象列和母表关联起来。如下面的图如果嵌套表存在主键，那么你可以是嵌套表变成一个索引组织表（IOT）；如果NESTED_TABLE_ID对于一个给定的父行的主键列是一个前缀，那么ORACLE会自动的将嵌套表中的数据进行进行汇总分组。所以，当一个父行被访问的时候，他所有的在嵌套表中的子行都可以得到快速的访问。只有父行访问时，效率是保持，因为子行和父行是不混淆的。下面的图示展示了一个嵌套表在索引组织中是如何WORKING的另外的，如果对在索引组织中的嵌套表使用COMPRESS子句，那么，ORACLE会自动提取相同的因子出来，也就是说，对于嵌套表的索引键（NESTED_TABLE_ID）不会重复出现在子行中，这就大大的减少了存储空间。看下面的图示NESTEDTABLESTOREAS子句定义了存储嵌套表的名称，该名称是用于创建嵌套表的一个索引。看下面的例子CREATETABLESTUDENTSGRADUATIONDATE,MATH_MAJORSPEOPLE_TYP,NESTEDTABLESEMPTYCHEM_MAJORSPEOPLE_TYP,PHYSICS_MAJORSPEOPLE_TYPNESTEDTABLEMATH_MAJORSSTOREASMATH_MAJORS_NTSTORAGETABLESNESTEDTABLECHEM_MAJORSSTOREASCHEM_MAJORS_NTNESTEDTABLEPHYSICS_MAJORSSTOREASPHYSICS_MAJORS_NT创建索引CREATEINDEXMATH_IDNO_IDXONMATH_MAJORS_NTIDNOCREATEINDEXCHEM_IDNO_IDXONCHEM_MAJORS_NTIDNOCREATEINDEXPHYSICS_IDNO_IDXONPHYSICS_MAJORS_NTIDNODML操作数据INSERTINTOSTUDENTSGRADUATIONVALUES01JUN03UPDATESTUDENTSSETMATH_MAJORSPEOPLE_TYPPERSON_TYP12,BOBJONES,6505550130,PERSON_TYP31,SARAHCHEN,4155550120,PERSON_TYP45,CHRISWOODS,4155550124,CHEM_MAJORSPEOPLE_TYPPERSON_TYP51,JOELANE,6505550140,PERSON_TYP31,SARAHCHEN,4155550120,PERSON_TYP52,KIMPATEL,6505550135,PHYSICS_MAJORSPEOPLE_TYPPERSON_TYP12,BOBJONES,6505550130,PERSON_TYP45,CHRISWOODS,4155550124WHEREGRADUATION01JUN03查询数据SELECTMIDNOMATH_ID,CIDNOCHEM_ID,PIDNOPHYSICS_IDFROMSTUDENTSS,TABLESMATH_MAJORSM,TABLESCHEM_MAJORSC,TABLESPHYSICS_MAJORSP另外的，我们还可以给嵌套表制定一个存储空间CREATETABLEPEOPLE_TABPEOPLE_COLUMNPEOPLE_TYPNESTEDTABLEPEOPLE_COLUMNSTOREASPEOPLE_COLUMN_NTTABLESPACESYSTEM如果TABLESPACE子句没有定义，那么系统会将嵌套表放到母表所在的空间。我们还可以通过修改的方法修改嵌套表的表空间ALTERTABLEPEOPLE_TABMOVETABLESPACESYSTEMMOVINGTABLEALTERTABLEPEOPLE_COLUMN_NTMOVETABLESPACEEXAMPLEMOVINGSTORAGETABLE多层集合数据类型多层集合类型指的是集合类型中的元素是自己的集合类型或者是其他的集合类型。嵌套表存储表的多层结构下面的例子展示了一个存储表的多层结构创建自定义对象类型LOCATIONCREATETYPELOCATION_TYPASOBJECTLOCATION_IDNUMBER4,STREET_ADDRESSVARCHAR240,POSTAL_CODEVARCHAR212,CITYVARCHAR230,STATE_PROVINCEVARCHAR225创建嵌套表类型CREATETYPENT_LOCATION_TYPASTABLEOFLOCATION_TYPNESTEDTABLETYPE创建带有自定义对象类型列的对象，也就是内部嵌套表类型CREATETYPECOUNTRY_TYPASOBJECTCOUNTRY_IDCHAR2,COUNTRY_NAMEVARCHAR240,LOCATIONSNT_LOCATION_TYPINNERNESTEDTABLE多层次结构的集合类型CREATETYPENT_COUNTRY_TYPASTABLEOFCOUNTRY_TYPMULTILEVELCOLLECTIONTYPE多层次结构的存储表CREATETABLEREGION_TABREGION_IDNUMBER,REGION_NAMEVARCHAR225,COUNTRIESNT_COUNTRY_TYPOUTERNESTEDTABLENESTEDTABLECOUNTRIESSTOREASNT_COUNTRIES_TABNESTEDTABLELOCATIONSSTOREASNT_LOCATIONS_TAB下面的一个例子说明了如何在NESTEDTABLE子句中指定用于存储表的物理属性DROPTABLEREGION_TABFORCECREATETABLEREGION_TABREGION_IDNUMBER,REGION_NAMEVARCHAR225,COUNTRIESNT_COUNTRY_TYPNESTEDTABLECOUNTRIESSTOREASNT_COUNTRIES_TABPRIMARYKEYNESTED_TABLE_ID,COUNTRY_IDORGANIZATIONINDEXCOMPRESSNESTEDTABLELOCATIONSSTOREASNT_LOCATIONS_TAB在上面的例子中，嵌套表COUNTRIES通过ORGANIZATIONINDEX子句和将嵌套表的NESTED_TABLE_ID放在索引的第一列构成了一个索引组织表IOT。指定NESTED_TABLE_ID为索引表的主键第一列，并且使该表成为IOT，则ORACLE会将嵌套表中属于同一父行的子行归集分组，这样是访问数据的效率更快。多层结构的动态数组存储多层次的数组的存储有两种方式，选取存储方式的判断方法是根据它是数组中包含数组，还是数组中包含嵌套表。在一个可变数组的变长数组中，整个变长数组存储在该行内部的，除非它是大于约4000个字节或显式指定LOB存储。在一个变长数组的嵌套表中，只有LOG存在行中，整个变长数组存储在LOB。在一个数组中没有相关的存储表关联嵌套表元素。看下面的例子，显示的指定了数组存储在LOB中CREATETYPEEMAIL_LIST_TYPASVARRAY10OFVARCHAR280CREATETYPEEMAIL_LIST_TYPASTABLEOFEMAIL_LIST_ARRCREATETABLEDEPT_EMAIL_LISTDEPT_NONUMBER,EMAIL_ADDRSEMAIL_LIST_TYPNESTEDTABLEEMAIL_ADDRSSTOREASEMAIL_ADDRS_NTVARRAYCOLUMN_VALUESTOREASLOBDEPT_EMAILS_LOBCREATETYPEEMAIL_LIST_TYP2ASOBJECTSECTION_NONUMBER,EMAILSEMAIL_LIST_ARRCREATETYPEEMAIL_VARRAY_TYPASVARRAY5OFEMAIL_LIST_TYP2CREATETABLEDEPT_EMAIL_LIST2DEPT_NONUMBER,EMAIL_ADDRSEMAIL_VARRAY_TYPVARRAYEMAIL_ADDRSSTOREASLOBDEPT_E