第4章 對象-關(guān)系SQL

4.1引言O(shè)RSQL模型：對象-關(guān)系SQL模型支持用戶對象定義；對象：一組可以存儲在一個表中的某類型的數(shù)值；ORACLE：對象類型INFORMIX：行類型DB2UDB：用戶定義類型UDT：SQL-99標準表：包含用戶定義類型的多個行；列：包含一個用戶定義類型的值。14.1引言COLLECTIONTYPE：匯集類型對象-關(guān)系模型允許一個行包括一個行值匯集；單個列允許包含多個值或結(jié)構(gòu)化數(shù)據(jù)值；違反了第一范式的規(guī)則。eidenamepositiondependentsdep_namedep_agee001SmithJohnAgentMichaelJ.9SusanR.7e002Andrews,DavidSuperintendentDavidM.Jr.10e003Jones,FranklinAgentAndrewK.11MarkW.9LouisaM.4employees24.1引言方法和UDF在純面向?qū)ο蟮沫h(huán)境中，所有數(shù)據(jù)都是私有的，一個對象類的方法的匯集刻畫了該類的行為；例如某銀行賬戶對象：get_balance(),make_deposit()在ORDBMS中，所有對象被看作是公共的而非私有的。UDF：用戶定義函數(shù)34.2對象和表ORACLE中的對象類型（C中的結(jié)構(gòu)類型）：一個對象類型有多個類型屬性。createtype

name_t

asobject

(lnamevarchar(30),

fnamevarchar(30),

michar(1));createtableteachers(tidint,tnamename_t,roomint);insertintoteachersvalues(1234,name_t(‘Einstein’,‘Albert’,‘E’),120);對象構(gòu)造器：通過屬性值構(gòu)造對象44.2對象和表對象類型的訪問：必須用表的別名限定select語句中的所有列；對頂層屬性可不加限定；頂層以下的屬性嚴格限定；用“點號”的形式來訪問表中的對象列。selectt.tid,t.tname.fname,t.tname.lnamefromteacherstwheret.room=123;selecttid,tname.fname,tname.lnamefromteacherswhereroom=123;Χ54.2對象和表一個對象類型可用另一個對象類型的屬性來定義。例4.2.3構(gòu)造一個分別包含人的社會保障號、姓名和年齡的對象類型person_t。createtypeperson_tasobject

(ssnoint,

pnamename_t,ageint);刪除對象類型name_t之前，必須先刪除對象類型person_t。64.2對象和表對象表：一個表中的行包含對象類型每一行由該類型的一個對象組成例4.2.4構(gòu)造一個包含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對象構(gòu)造器94.2對象和表Insert和Update語句：使用對象構(gòu)造器為新行指定值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對象和表創(chuàng)建和刪除對象類型和對象表的ORACLE語句：CREATETYPEtypenameASOBJECT(attrnamedatatype{,attrnamedatatype···});CREATETABLEtablenameOFtypename([attrnameNOTNULL]{,attrnameNOTNULL···}[,PRIMARYKEY(attrname{,attrname···})]);DROPTYPEtypename;DROPTABLEtablename;124.2對象和表REF對象引用的定義ORACLE為每個行對象提供一個唯一標識，即對象標識符。REF：一個表中的列，系統(tǒng)內(nèi)部的數(shù)據(jù)類型，允許指向?qū)ο蟊碇械男袑ο蟆＠?.2.9對顧客、代理商和訂單使用對象表生成CAP數(shù)據(jù)庫，在每個表中保留原來的列，并依次增加從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找出所有至少被兩個客戶訂購的產(chǎn)品的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()函數(shù)：獲得出現(xiàn)在其他連接的SQL語句中的REF對象值；例4.2.12查詢所有沒通過代理商a05訂購商品的顧客amefromcustomerscwherenotexists(select*fromordersxwherec.cid=x.cidandx.aid=‘a(chǎn)05’);amefromcustomerscwherenotexists(select*fromordersxwherex.ordcust=ref(c)andx.aid=‘a(chǎn)05’);164.2對象和表例4.2.13找到所有通過NewYork的代理商發(fā)訂單的顧客的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子句：保證所有的非空引用在創(chuàng)建時都指向正確的表。例4.2.15創(chuàng)建一個帶有通過對另一警官對象的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函數(shù)：檢索一個由給定的REF引用的整個對象例，檢索所有警官及其搭檔的信息。selectvalue(p),deref(p.partner)frompolice_officersp;204.2對象和表REF依賴：回路一個表集合可以具有一個用REF表示的復(fù)雜關(guān)系集214.2對象和表REF依賴的創(chuàng)建：ORACLE支持不完全類型定義。部分創(chuàng)建第一個類型：createtypeemployee_t獲得了一個指向employee_t的REF;完整地創(chuàng)建第二個類型：包含了一個指向employee_t的REF;完善第一個類型：包含了一個指向的department的REF;224.2對象和表REF依賴的刪除：按創(chuàng)建時的相反順序刪除：表、類型對在多個表間的REF環(huán)中涉及的所有類型使用：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）允許在一行中放入多個值。每個匯集類型包含所有相同類型的項，即元素類型；一個元素類型可以是內(nèi)部類型或?qū)ο箢愋停粩?shù)據(jù)庫系統(tǒng)中成熟的數(shù)據(jù)類型，經(jīng)過適當?shù)霓D(zhuǎn)換，可以在查詢中解釋為表。兩種匯集類型：表類型、數(shù)組類型264.3匯集類型嵌套表和表類型例4.3.1創(chuàng)建一個名為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匯集類型構(gòu)造帶有多個嵌套表列的表：為每個這樣的列聲明一個NESTEDTABLE子句。父表：包含嵌套表的表；子表：被嵌套的表；物理上，獨立于父表；邏輯上，包含于它自身的數(shù)據(jù)庫表中；子表的數(shù)據(jù)僅能通過父表訪問。例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轉(zhuǎn)換成表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匯集類型嵌套游標：像一個在執(zhí)行查詢時一個行集合的循環(huán)控制變量。Theideaofthehierarchicaloutputissimple:outputtheparentinformationonce,andthenprintatableofalltherelevantchildrowsforthatparent,thengoontothenextparent,andsoon.頂層的select提供一個在from表上像游標一樣的循環(huán)，以產(chǎn)生待檢索的行；當在外層select循環(huán)中遇到時，嵌套的游標語法cursor()允許我們增加一個掃描每一個行中嵌套表的第二層循環(huán)例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)子查詢可能返回單個嵌套表的列值或正常的關(guān)系行集合。在使用關(guān)系行的情況下，CURSOR()將移過子查詢的所有行。CURSOR()僅能用于頂層選擇列表(selectlist)中。如果存在一個嵌套表或在外層掃描中遇到行集合，則一個CURSOR()可被用于另一個中。CURSOR()的結(jié)果帶有不同數(shù)量和類型的數(shù)據(jù)值，即不是關(guān)系表。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計算每個雇員的家屬數(shù)。嵌套游標：selecteid,cursor(selectcount(*)fromtable(e.dependents))fromemployeese;使用結(jié)果的標量子查詢：selecteid,(selectcount(*)fromtable(e.dependents))fromemployeese;利用表乘積消除嵌套：selecteid,count(*)fromemployeese,table(e.dependents))groupbyeid;selecteid,count(*)fromemployeese,table(e.dependents));404.3匯集類型VARRAY數(shù)組類型ORACLE的另一種匯集類型；以VARRAY聲明，代表“變長數(shù)組”；每個數(shù)組需要聲明：名字元素類型一個VARRAY對象可以包含的最大元素個數(shù)；VARRAY類型的元素有特定的次序。414.3匯集類型例4.3.11建立一個簡單的電話號碼本：每個人可能有多個對外聯(lián)系的電話號。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()表同樣可以應(yīng)用于VARRAY值。例4.3.13在PH