第4章 对象-关系SQL

4.1引言ORSQL模型：对象-关系SQL模型支持用户对象定义；对象：一组可以存储在一个表中的某类型的数值；ORACLE：对象类型INFORMIX：行类型DB2UDB：用户定义类型UDT：SQL-99标准表：包含用户定义类型的多个行；列：包含一个用户定义类型的值。14.1引言COLLECTIONTYPE：汇集类型对象-关系模型允许一个行包括一个行值汇集；单个列允许包含多个值或结构化数据值；违反了第一范式的规则。eidenamepositiondependentsdep_namedep_agee001SmithJohnAgentMichaelJ.9SusanR.7e002Andrews,DavidSuperintendentDavidM.Jr.10e003Jones,FranklinAgentAndrewK.11MarkW.9LouisaM.4employees24.1引言方法和UDF在纯面向对象的环境中，所有数据都是私有的，一个对象类的方法的汇集刻画了该类的行为；例如某银行账户对象：get_balance(),make_deposit()在ORDBMS中，所有对象被看作是公共的而非私有的。UDF：用户定义函数34.2对象和表ORACLE中的对象类型（C中的结构类型）：一个对象类型有多个类型属性。createtype

name_t

asobject

(lnamevarchar(30),

fnamevarchar(30),

michar(1));createtableteachers(tidint,tnamename_t,roomint);insertintoteachersvalues(1234,name_t(‘Einstein’,‘Albert’,‘E’),120);对象构造器：通过属性值构造对象44.2对象和表对象类型的访问：必须用表的别名限定select语句中的所有列；对顶层属性可不加限定；顶层以下的属性严格限定；用“点号”的形式来访问表中的对象列。selectt.tid,t.tname.fname,t.tname.lnamefromteacherstwheret.room=123;selecttid,tname.fname,tname.lnamefromteacherswhereroom=123;Χ54.2对象和表一个对象类型可用另一个对象类型的属性来定义。例4.2.3构造一个分别包含人的社会保障号、姓名和年龄的对象类型person_t。createtypeperson_tasobject

(ssnoint,

pnamename_t,ageint);删除对象类型name_t之前，必须先删除对象类型person_t。64.2对象和表对象表：一个表中的行包含对象类型每一行由该类型的一个对象组成例4.2.4构造一个包含person_t对象（行）的名为people的对象表，社会保障号为主键。createtablepeopleof

person_t(primarykey(ssno));30FJoseSanchez02389445559XPatrickDelaney24588213423EJacquelineMarch123550123pname.mipname.fnamepname.lnameagepnamessno命名列(顶层属性)pname中的属性ROW1ROW2ROW374.2对象和表无名顶层列：可用value(p)的形式引用整个people的全行对象。selectvalue(p)frompeoplepwhereage>25;30FJoseSanchez02389445559XPatrickDelaney24588213423EJacquelineMarch123550123pname.mipname.fnamepname.lnameagepnamessno30)name_t(‘Sanchez’,‘Jose’,‘F’),(023894455,person_t59)name_t(‘Delaney’,‘Patrick’,‘X’),(245882134,person_tage)pname(lname,fname,mi),(ssno,value(p)30)name_t(‘Sanchez’,‘Jose’,‘F’)02389445559name_t(‘Delaney’,‘Patrick’,‘X’)245882134agepname(lname,fname,mi)ssnoselect*frompeoplepwherep.age>25;显示列对象显示行对象84.2对象和表例4.2.5显示JoseF.Sanchez的所有信息。selectvalue(p)frompeoplepwherep.pname=name_t(‘Sanchez’,‘Jose’,‘F’)例4.2.6显示姓氏以‘Pat’开头、年龄大于50的所有人的姓名和年龄。selectp.pname,p.agefrompeoplepwherep.pname.fnamelike‘Pat%’andp.age>50;30FJoseSanchez02389445559XPatrickDelaney24588213423EJacquelineMarch123550123pname.mipname.fnamepname.lnameagepnamessno对象构造器94.2对象和表Insert和Update语句：使用对象构造器为新行指定值insertintopeoplevalues(123441998,name_t(‘Einstein’,‘Albert’,‘E’),100);insertintoscientistsselectp.pnamefrompeoplep;例4.2.7建立一个包含行对象类型为name_t的scientists表，并将‘AlbertEEinstein’插入表中。createtablescientistsofname_t;insertintoscientistsvalues(‘Einstein’,‘Albert’,‘E’);insertintoscientistsname_t(‘Einstein’,‘Albert’,‘E’);×例4.2.8将ssno为321341223,pname和age为空值的行插到people中。insertintopeoplevalues(321341223,null,null);104.2对象和表Insert：不支持表的别名，顶层属性在列表中被不加限定地引用；Update：支持表的别名，需要使用属性的完全限定的表达式。updatepeoplepsetp.pname=name_t(‘Gould’,‘Ben’,null)wheressno=321341223updatepeoplepsetp.pname.mi=‘C’wheressno=321341223updatepeoplepsetp=person_t(332341223,name_t(‘Gould’,‘Glen’,‘A’),55)wheressno=321341223ssnopnameagepname.lnamepname.fnamepname.mi123550123MarchJacquelineE23245882134DelaneyPatrickX59023894455SanchezJoseF30321341223ssnopnameagepname.lnamepname.fnamepname.mi123550123MarchJacquelineE23245882134DelaneyPatrickX59023894455SanchezJoseF30321341223BenGould114.2对象和表创建和删除对象类型和对象表的ORACLE语句：CREATETYPEtypenameASOBJECT(attrnamedatatype{,attrnamedatatype···});CREATETABLEtablenameOFtypename([attrnameNOTNULL]{,attrnameNOTNULL···}[,PRIMARYKEY(attrname{,attrname···})]);DROPTYPEtypename;DROPTABLEtablename;124.2对象和表REF对象引用的定义ORACLE为每个行对象提供一个唯一标识，即对象标识符。REF：一个表中的列，系统内部的数据类型，允许指向对象表中的行对象。例4.2.9对顾客、代理商和订单使用对象表生成CAP数据库，在每个表中保留原来的列，并依次增加从Orders对象表中的每个订单到该订单涉及到的Customer、Agent和Product行对象的REF。P18513ordnomonthcidaidpidqtydollarsordcustordagentordprod1011Janc001a01p011000450.001013Janc002a03p031000880.001014Janc003a03p0512001104.001021Febc004a06p011000460.001016Janc006a01p011000500.00cidcnamecitydiscntc001TipTopDuluth10.00c002BasicsDallas12.00c003AlliedDallas8.00c004ACMEDuluth8.00c006ACMEKyoto0.00aidanamecitypercenta01SmithNewYork6a02JonesNewark6a03BrownTokoy7a04GrayNewYork6a05OtasiDuluth5a06SmithDallas5pidpnamecityquantitypricep01combDallas1114000.50p02brushNewark2030000.50p03razorDuluth1506001.00p04penDuluth1253001.00p05pencilDallas2214001.00refrefref144.2对象和表例4.2.10检索所有的顾客-代理商名对。ame,o.ordagent.anamefromorderso;ame,agents.anamefromcustomers,orders,agentswherecustomers.cid=orders.cidandorders.aid=agents.aid例4.2.11找出所有至少被两个客户订购的产品的pid。selectdistinctx1.pidfromordersx1,ordersx2wherex1.pid=x2.pidandx1.ordcust<x2.ordcust;select

p.pid

from

productsp

where

2<=(selectcount(distinctcid)fromorderswherepid=p.pid);154.2对象和表REF()函数：获得出现在其他连接的SQL语句中的REF对象值；例4.2.12查询所有没通过代理商a05订购商品的顾客amefromcustomerscwherenotexists(select*fromordersxwherec.cid=x.cidandx.aid=‘a05’);amefromcustomerscwherenotexists(select*fromordersxwherex.ordcust=ref(c)andx.aid=‘a05’);164.2对象和表例4.2.13找到所有通过NewYork的代理商发订单的顾客的cid值。selectc.cidfromcustomerscwherenotexists(select*fromagentsawherea.city=‘NewYork’andnotexists(select*fromordersxwherex.ordcust=ref(c)andx.ordagent=ref(a)));selectc.cidfromcustomerscwherenotexists(select*fromagentsawherea.city=‘NewYork’andnotexists(select*fromordersxwherex.cid=c.cidandx.aid=a.aid));174.2对象和表当表中被引用的行被删除时，连接到这些行的REF将变成挂起的REF。挂起的REF是非空而无用的；ISDANGLING可查询这种异常；例4.2.14找出orders中挂起的REF的顾客的cid值。selecto.cidfromordersowhereo.ordcustisdangling;selecto.cidfromordersowhereo.ordcust<>(selectref(c)fromcustomerscwherec.cid=o.cid);184.2对象和表一个对象类型不能嵌套地包含一个与其类型相同的成员；可包含一个对具有同样类型的其他对象的引用。SCOPE子句：保证所有的非空引用在创建时都指向正确的表。例4.2.15创建一个带有通过对另一警官对象的REF表示的搭档属性的警官类型。createtypepolice_officer_tasobject(pol_personperson_t,badge_numberinteger,

partner

ref

police_officer_t);createtablepolice_officersofpolice_officer_t(primarykey(badge_number),

scopefor(partner)ispolice_officers);194.2对象和表例4.2.16检索其搭档年龄超过60岁的所有警官的姓。selectpol_person.lnamefrompolice_officerspwherep.partner.pol_person.age>60;DEREF函数：检索一个由给定的REF引用的整个对象例，检索所有警官及其搭档的信息。selectvalue(p),deref(p.partner)frompolice_officersp;204.2对象和表REF依赖：回路一个表集合可以具有一个用REF表示的复杂关系集214.2对象和表REF依赖的创建：ORACLE支持不完全类型定义。部分创建第一个类型：createtypeemployee_t获得了一个指向employee_t的REF;完整地创建第二个类型：包含了一个指向employee_t的REF;完善第一个类型：包含了一个指向的department的REF;224.2对象和表REF依赖的删除：按创建时的相反顺序删除：表、类型对在多个表间的REF环中涉及的所有类型使用：DROPTYPEtypenameFORCE;234.2对象和表带REF表的装载：REF列单独装载例4.2.17用带有对customers、agents和products的正确引用替换orders中的任意当前的REF值。updateordersosetordcust=(selectref(c)fromcustomerscwherec.cid=o.cid),ordagent=(selectref(a)fromagentsawherea.aid=o.aid),ordprod=(selectref(p)fromproductspwherep.pid=o.pid);244.2对象和表例4.2.18从people表中选择一个ssno是033224445的人作为新警官插入到表police_officers中，徽章号为1000，其搭档的徽章号为990。insertintopolice_officersselectvalue(p),1000,ref(p0)frompeoplep,officersp0wherep.ssno=033224445andp0.badge_number=990;updatepolice_officerssetp.partner=(selectref(p0)frompolice_officersp0wherep0.badge_number=1000)wherebadge_number=990;254.3汇集类型汇集类型（collectionTypes）允许在一行中放入多个值。每个汇集类型包含所有相同类型的项，即元素类型；一个元素类型可以是内部类型或对象类型；数据库系统中成熟的数据类型，经过适当的转换，可以在查询中解释为表。两种汇集类型：表类型、数组类型264.3汇集类型嵌套表和表类型例4.3.1创建一个名为dependents_t的表类型，包含person_t对象表。createtypename_tasobject

(lnamevarchar(30),fnamevarchar(30),michar(1));createtypeperson_tasobject

(ssnoint,pnamename_t,ageint);createtypedependents_tastableofperson_t

;createtableemployees(eidint,

epersonperson_t,

dependentsdependents_t,primarykey(eid))nestedtabledependentsstoreasdependents_tab;274.3汇集类型顶层表：employees嵌套表：dependents_tabeidepersondependents101person_t(123897766,name_t(‘Smith’,‘John’,‘P’),45)person_t(322456776,name_t(‘Smith’,‘Michael’,‘J’),8)person_t(123822332,name_t(‘Smith’,‘Susan’,‘R’),12)102person_t(432112233,name_t(‘Andrews’,‘David’,‘S’),32)person_t(565534555,name_t(‘Shaw’,‘David’,‘M’),3)所有的dependents都在单个的dependents_tab表中284.3汇集类型构造带有多个嵌套表列的表：为每个这样的列声明一个NESTEDTABLE子句。父表：包含嵌套表的表；子表：被嵌套的表；物理上，独立于父表；逻辑上，包含于它自身的数据库表中；子表的数据仅能通过父表访问。例4.3.2检索雇员101的所有家属的嵌套表selectdependentsfromemployeeswhereeid=101;dependents(ssno,pname(fname,minitial,lname),age)dependents_t(person_t(322456776,name_t(‘Smith’,‘Michael’,‘J’),8),person_t(123822332,name_t(‘Smith’,‘Susan’,‘R’),12))294.3汇集类型例4.3.3显示所有雇员的家属。selectdependentsfromemployees;例4.3.4显示有6个以上家属的雇员。selecteidfromemployeesewhere6<(selectcount(*)fromtable(e.dependents));例4.3.5列出带有社会保障号为3451112222的家属的雇员的eid。selecteidfromemployeesewhere3451112222in(selectd.ssnofromtable(e.dependents)d);selecteidfromemployeesewherename_t(‘Lukas’,‘David’,‘E’)

in

(selectd.pnamefromtable(e.dependents)d);将dependents转换成表304.3汇集类型table()的语法：FROMTABLE(collection_exp)不能用于最顶层select语句的from子句的表selectssnofromtable(employees.dependents);×Wecangiveeadefinitionbyembeddingthissubqueryinanouterqueryonemployess:selecteidfromemployeesewhereexists(select*fromtable(e.dependents));例4.3.6检索雇员101有多少位家属。selectcount(*)from(selecte.dependentsfromemployeesewheree.eid=101);0or1selectcount(e.dependents)fromemployeesewheree.eid=101);0or1selecttable(e.dependents)fromemployeesewheree.eid=101;×selectcount(*)fromtable(selecte.dependentsfromemployeesewheree.eid=101);√314.3汇集类型例4.3.7显示雇员101所有家属的社会保障号。selectd.ssnofromtable(selecte.dependentsfromemployeesawheree.eid=101)d;Whathappensifweremovethe"wheree.eid=101"thatspecifiedasinglenestedtable?TABLE()refusestoworkwiththemultiplenestedtablestryingtoflowintoit.TABLE()requiresasinglenestedtableasanargument.324.3汇集类型利用表的乘积消除嵌套：显示所有雇员家属的社会保障号ssno。selecte.eid,d.ssnofromemployeese,table(e.dependents)d;Thistableproductgeneratesaresultrowforeachdependent.selecte.eid,d.ssnofromemployeese,table(e.dependents)(+)d;tofindallthepossibilitiesingroupsofdependentsofexactlythesameage,whereweareonlyinterestedingroupsof3ormore.select

d.age,count(*)

fromemployeese,table(e.dependents)d

groupbyd.age

havingcount(*)>=3

orderbyd.age;eidepersondependents101person_t(123897766,name_t(‘Smith’,‘John’,‘P’),45)person_t(322456776,name_t(‘Smith’,‘Michael’,‘J’),8)person_t(123822332,name_t(‘Smith’,‘Susan’,‘R’),12)102person_t(432112233,name_t(‘Andrews’,‘David’,‘S’),32)person_t(565534555,name_t(‘Shaw’,‘David’,‘M’),3)334.3汇集类型Butexactlywhoarethesedependents?Wehavetheiragesfromthisquery,andthatcanbeusedtofindouttheiridentities:selectd1.ssno,d1.agefromemployeese1,table(e1.dependents)d1 whered1.agein(selectd.agefromemployeese,table(e.dependents)d groupbyd.agehavingcount(*)>=3)orderbyd1.age;eidepersondependents101person_t(123897766,name_t(‘Smith’,‘John’,‘P’),45)person_t(322456776,name_t(‘Smith’,‘Michael’,‘J’),8)person_t(123822332,name_t(‘Smith’,‘Susan’,‘R’),12)102person_t(432112233,name_t(‘Andrews’,‘David’,‘S’),32)person_t(565534555,name_t(‘Shaw’,‘David’,‘M’),3)344.3汇集类型嵌套游标：像一个在执行查询时一个行集合的循环控制变量。Theideaofthehierarchicaloutputissimple:outputtheparentinformationonce,andthenprintatableofalltherelevantchildrowsforthatparent,thengoontothenextparent,andsoon.顶层的select提供一个在from表上像游标一样的循环，以产生待检索的行；当在外层select循环中遇到时，嵌套的游标语法cursor()允许我们增加一个扫描每一个行中嵌套表的第二层循环例4.3.8显示雇员表中的所有行的eid值和该雇员的年龄小于16岁的任意家属的社会保障号。354.3汇集类型利用表乘积消除嵌套selecte.eid,d.ssnoasdep_ssno

fromemployeese,table(e.dependents)dwhered.age<16;eidepersondependents101person_t(123897766,name_t(‘Smith’,‘John’,‘P’),45)person_t(322456776,name_t(‘Smith’,‘Michael’,‘J’),8)person_t(123822332,name_t(‘Smith’,‘Susan’,‘R’),12)102person_t(432112233,name_t(‘Andrews’,‘David’,‘S’),32)person_t(565534555,name_t(‘Shaw’,‘David’,‘M’),3)eiddep_ssno101322456776101123822332102565534555364.3汇集类型利用嵌套游标selecte.eid,cursor(selectd.ssnoasdep_ssnofromtable(e.dependents)dwhered.age<16)dep_tab

fromemployeese;EIDDEP_TAB101CURSORSTATEMENT:2CURSORSTATEMENT:2102CURSORSTATEMENT:2CURSORSTATEMENT:2DEP_SSNO322456776123822332DEP_SSNO565534555374.3汇集类型CURSOR()表达式的一般形式：cursor_expression::=cursor(Subquery)子查询可能返回单个嵌套表的列值或正常的关系行集合。在使用关系行的情况下，CURSOR()将移过子查询的所有行。CURSOR()仅能用于顶层选择列表(selectlist)中。如果存在一个嵌套表或在外层扫描中遇到行集合，则一个CURSOR()可被用于另一个中。CURSOR()的结果带有不同数量和类型的数据值，即不是关系表。384.3汇集类型例4.3.9按最老的家属的名字列出雇员名和eid。selecteid,cursor(selectd1.pname.fnamefromtables(employees.dependents)d1whered1.age=(selectmax(d2.age)fromtable(employees.dependents)d2))

fromemployees;selecteid,d.pname.fnamefromemployeese,table(e.dependents)d1whered1.age=(selectmax(d2.age)fromtable(e.dependents)d2));394.3汇集类型例4.3.10计算每个雇员的家属数。嵌套游标：selecteid,cursor(selectcount(*)fromtable(e.dependents))fromemployeese;使用结果的标量子查询：selecteid,(selectcount(*)fromtable(e.dependents))fromemployeese;利用表乘积消除嵌套：selecteid,count(*)fromemployeese,table(e.dependents))groupbyeid;selecteid,count(*)fromemployeese,table(e.dependents));404.3汇集类型VARRAY数组类型ORACLE的另一种汇集类型；以VARRAY声明，代表“变长数组”；每个数组需要声明：名字元素类型一个VARRAY对象可以包含的最大元素个数；VARRAY类型的元素有特定的次序。414.3汇集类型例4.3.11建立一个简单的电话号码本：每个人可能有多个对外联系的电话号。createtypeextensions_tasvarray(4)ofint;createtablephonebook(phpersonperson_t,extensionsextensions_t,primarykey(phperson.ssno);例4.3.12检索社会保障号为123897766的人的名字和VARRAYextensions。selectpb.phperson.fname,pb.extensionsfromphonebookpbwherepb.phperson.ssno=123897766;phpersonextensionsperson_t(123897766,name_t(‘Smith’,’John’,’P’),45)extensions_t(345,989)person_t(432112233,name_t(‘Alan’,’John’,’Q’),32)extensions_t(123)424.3汇集类型SQL语句无法访问一个以VARRAY定义的对象的下标元素；TABLE()表同样可以应用于VARRAY值。例4.3.13在PH