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

4.1引言O(shè)RSQL模型：對(duì)象-關(guān)系SQL模型支持用戶對(duì)象定義；對(duì)象：一組可以存儲(chǔ)在一個(gè)表中的某類型的數(shù)值；ORACLE：對(duì)象類型INFORMIX：行類型DB2UDB：用戶定義類型UDT：SQL-99標(biāo)準(zhǔn)表：包含用戶定義類型的多個(gè)行；列：包含一個(gè)用戶定義類型的值。14.1引言COLLECTIONTYPE：匯集類型對(duì)象-關(guān)系模型允許一個(gè)行包括一個(gè)行值匯集；單個(gè)列允許包含多個(gè)值或結(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ù)都是私有的，一個(gè)對(duì)象類的方法的匯集刻畫(huà)了該類的行為；例如某銀行賬戶對(duì)象：get_balance(),make_deposit()在ORDBMS中，所有對(duì)象被看作是公共的而非私有的。UDF：用戶定義函數(shù)34.2對(duì)象和表ORACLE中的對(duì)象類型（C中的結(jié)構(gòu)類型）：一個(gè)對(duì)象類型有多個(gè)類型屬性。createtype

name_t

asobject

(lnamevarchar(30),

fnamevarchar(30),

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

(ssnoint,

pnamename_t,ageint);刪除對(duì)象類型name_t之前，必須先刪除對(duì)象類型person_t。64.2對(duì)象和表對(duì)象表：一個(gè)表中的行包含對(duì)象類型每一行由該類型的一個(gè)對(duì)象組成例4.2.4構(gòu)造一個(gè)包含person_t對(duì)象（行）的名為people的對(duì)象表，社會(huì)保障號(hào)為主鍵。createtablepeopleof

person_t(primarykey(ssno));30FJoseSanchez02389445559XPatrickDelaney24588213423EJacquelineMarch123550123pname.mipname.fnamepname.lnameagepnamessno命名列(頂層屬性)pname中的屬性ROW1ROW2ROW374.2對(duì)象和表無(wú)名頂層列：可用value(p)的形式引用整個(gè)people的全行對(duì)象。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;顯示列對(duì)象顯示行對(duì)象84.2對(duì)象和表例4.2.5顯示JoseF.Sanchez的所有信息。selectvalue(p)frompeoplepwherep.pname=name_t(‘Sanchez’,‘Jose’,‘F’)例4.2.6顯示姓氏以‘Pat’開(kāi)頭、年齡大于50的所有人的姓名和年齡。selectp.pname,p.agefrompeoplepwherep.pname.fnamelike‘Pat%’andp.age>50;30FJoseSanchez02389445559XPatrickDelaney24588213423EJacquelineMarch123550123pname.mipname.fnamepname.lnameagepnamessno對(duì)象構(gòu)造器94.2對(duì)象和表Insert和Update語(yǔ)句：使用對(duì)象構(gòu)造器為新行指定值insertintopeoplevalues(123441998,name_t(‘Einstein’,‘Albert’,‘E’),100);insertintoscientistsselectp.pnamefrompeoplep;例4.2.7建立一個(gè)包含行對(duì)象類型為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對(duì)象和表Insert：不支持表的別名，頂層屬性在列表中被不加限定地引用；Update：支持表的別名，需要使用屬性的完全限定的表達(dá)式。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對(duì)象和表創(chuàng)建和刪除對(duì)象類型和對(duì)象表的ORACLE語(yǔ)句：CREATETYPEtypenameASOBJECT(attrnamedatatype{,attrnamedatatype···});CREATETABLEtablenameOFtypename([attrnameNOTNULL]{,attrnameNOTNULL···}[,PRIMARYKEY(attrname{,attrname···})]);DROPTYPEtypename;DROPTABLEtablename;124.2對(duì)象和表REF對(duì)象引用的定義ORACLE為每個(gè)行對(duì)象提供一個(gè)唯一標(biāo)識(shí)，即對(duì)象標(biāo)識(shí)符。REF：一個(gè)表中的列，系統(tǒng)內(nèi)部的數(shù)據(jù)類型，允許指向?qū)ο蟊碇械男袑?duì)象。例4.2.9對(duì)顧客、代理商和訂單使用對(duì)象表生成CAP數(shù)據(jù)庫(kù)，在每個(gè)表中保留原來(lái)的列，并依次增加從Orders對(duì)象表中的每個(gè)訂單到該訂單涉及到的Customer、Agent和Product行對(duì)象的REF。P18513ordnomonthcidaidpidqtydollarsordcustordagentordprod1011Janc001a01p011000450.001013Janc002a03p031000880.001014Janc003a03p0512001104.001021Febc004a06p011000460.001016Janc006a01p011000500.00cidcnamecitydiscntc001TipTopDuluth10.00c002BasicsDallas12.00c003AlliedDallas8.00c004ACMEDuluth8.00c006ACMEKyoto0.00aidanamecitypercenta01SmithNewYork6a02JonesNewark6a03BrownTokoy7a04GrayNewYork6a05OtasiDuluth5a06SmithDallas5pidpnamecityquantitypricep01combDallas1114000.50p02brushNewark2030000.50p03razorDuluth1506001.00p04penDuluth1253001.00p05pencilDallas2214001.00refrefref144.2對(duì)象和表例4.2.10檢索所有的顧客-代理商名對(duì)。ame,o.ordagent.anamefromorderso;ame,agents.anamefromcustomers,orders,agentswherecustomers.cid=orders.cidandorders.aid=agents.aid例4.2.11找出所有至少被兩個(gè)客戶訂購(gòu)的產(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對(duì)象和表REF()函數(shù)：獲得出現(xiàn)在其他連接的SQL語(yǔ)句中的REF對(duì)象值；例4.2.12查詢所有沒(méi)通過(guò)代理商a05訂購(gòu)商品的顧客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對(duì)象和表例4.2.13找到所有通過(guò)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對(duì)象和表當(dāng)表中被引用的行被刪除時(shí)，連接到這些行的REF將變成掛起的REF。掛起的REF是非空而無(wú)用的；ISDANGLING可查詢這種異常；例4.2.14找出orders中掛起的REF的顧客的cid值。selecto.cidfromordersowhereo.ordcustisdangling;selecto.cidfromordersowhereo.ordcust<>(selectref(c)fromcustomerscwherec.cid=o.cid);184.2對(duì)象和表一個(gè)對(duì)象類型不能嵌套地包含一個(gè)與其類型相同的成員；可包含一個(gè)對(duì)具有同樣類型的其他對(duì)象的引用。SCOPE子句：保證所有的非空引用在創(chuàng)建時(shí)都指向正確的表。例4.2.15創(chuàng)建一個(gè)帶有通過(guò)對(duì)另一警官對(duì)象的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對(duì)象和表例4.2.16檢索其搭檔年齡超過(guò)60歲的所有警官的姓。selectpol_person.lnamefrompolice_officerspwherep.partner.pol_person.age>60;DEREF函數(shù)：檢索一個(gè)由給定的REF引用的整個(gè)對(duì)象例，檢索所有警官及其搭檔的信息。selectvalue(p),deref(p.partner)frompolice_officersp;204.2對(duì)象和表REF依賴：回路一個(gè)表集合可以具有一個(gè)用REF表示的復(fù)雜關(guān)系集214.2對(duì)象和表REF依賴的創(chuàng)建：ORACLE支持不完全類型定義。部分創(chuàng)建第一個(gè)類型：createtypeemployee_t獲得了一個(gè)指向employee_t的REF;完整地創(chuàng)建第二個(gè)類型：包含了一個(gè)指向employee_t的REF;完善第一個(gè)類型：包含了一個(gè)指向的department的REF;224.2對(duì)象和表REF依賴的刪除：按創(chuàng)建時(shí)的相反順序刪除：表、類型對(duì)在多個(gè)表間的REF環(huán)中涉及的所有類型使用：DROPTYPEtypenameFORCE;234.2對(duì)象和表帶REF表的裝載：REF列單獨(dú)裝載例4.2.17用帶有對(duì)customers、agents和products的正確引用替換orders中的任意當(dāng)前的REF值。updateordersosetordcust=(selectref(c)fromcustomerscwherec.cid=o.cid),ordagent=(selectref(a)fromagentsawherea.aid=o.aid),ordprod=(selectref(p)fromproductspwherep.pid=o.pid);244.2對(duì)象和表例4.2.18從people表中選擇一個(gè)ssno是033224445的人作為新警官插入到表police_officers中，徽章號(hào)為1000，其搭檔的徽章號(hào)為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）允許在一行中放入多個(gè)值。每個(gè)匯集類型包含所有相同類型的項(xiàng)，即元素類型；一個(gè)元素類型可以是內(nèi)部類型或?qū)ο箢愋?；?shù)據(jù)庫(kù)系統(tǒng)中成熟的數(shù)據(jù)類型，經(jīng)過(guò)適當(dāng)?shù)霓D(zhuǎn)換，可以在查詢中解釋為表。兩種匯集類型：表類型、數(shù)組類型264.3匯集類型嵌套表和表類型例4.3.1創(chuàng)建一個(gè)名為dependents_t的表類型，包含person_t對(duì)象表。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都在單個(gè)的dependents_tab表中284.3匯集類型構(gòu)造帶有多個(gè)嵌套表列的表：為每個(gè)這樣的列聲明一個(gè)NESTEDTABLE子句。父表：包含嵌套表的表；子表：被嵌套的表；物理上，獨(dú)立于父表；邏輯上，包含于它自身的數(shù)據(jù)庫(kù)表中；子表的數(shù)據(jù)僅能通過(guò)父表訪問(wèn)。例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個(gè)以上家屬的雇員。selecteidfromemployeesewhere6<(selectcount(*)fromtable(e.dependents));例4.3.5列出帶有社會(huì)保障號(hào)為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()的語(yǔ)法：FROMTABLE(collection_exp)不能用于最頂層select語(yǔ)句的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所有家屬的社會(huì)保障號(hào)。selectd.ssnofromtable(selecte.dependentsfromemployeesawheree.eid=101)d;Whathappensifweremovethe"wheree.eid=101"thatspecifiedasinglenestedtable?TABLE()refusestoworkwiththemultiplenestedtablestryingtoflowintoit.TABLE()requiresasinglenestedtableasanargument.324.3匯集類型利用表的乘積消除嵌套：顯示所有雇員家屬的社會(huì)保障號(hào)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匯集類型嵌套游標(biāo)：像一個(gè)在執(zhí)行查詢時(shí)一個(gè)行集合的循環(huán)控制變量。Theideaofthehierarchicaloutputissimple:outputtheparentinformationonce,andthenprintatableofalltherelevantchildrowsforthatparent,thengoontothenextparent,andsoon.頂層的select提供一個(gè)在from表上像游標(biāo)一樣的循環(huán)，以產(chǎn)生待檢索的行；當(dāng)在外層select循環(huán)中遇到時(shí)，嵌套的游標(biāo)語(yǔ)法cursor()允許我們?cè)黾右粋€(gè)掃描每一個(gè)行中嵌套表的第二層循環(huán)例4.3.8顯示雇員表中的所有行的eid值和該雇員的年齡小于16歲的任意家屬的社會(huì)保障號(hào)。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匯集類型利用嵌套游標(biāo)selecte.eid,cursor(selectd.ssnoasdep_ssnofromtable(e.dependents)dwhered.age<16)dep_tab

fromemployeese;EIDDEP_TAB101CURSORSTATEMENT:2CURSORSTATEMENT:2102CURSORSTATEMENT:2CURSORSTATEMENT:2DEP_SSNO322456776123822332DEP_SSNO565534555374.3匯集類型CURSOR()表達(dá)式的一般形式：cursor_expression::=cursor(Subquery)子查詢可能返回單個(gè)嵌套表的列值或正常的關(guān)系行集合。在使用關(guān)系行的情況下，CURSOR()將移過(guò)子查詢的所有行。CURSOR()僅能用于頂層選擇列表(selectlist)中。如果存在一個(gè)嵌套表或在外層掃描中遇到行集合，則一個(gè)CURSOR()可被用于另一個(gè)中。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計(jì)算每個(gè)雇員的家屬數(shù)。嵌套游標(biāo)：selecteid,cursor(selectcount(*)fromtable(e.dependents))fromemployeese;使用結(jié)果的標(biāo)量子查詢：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聲明，代表“變長(zhǎng)數(shù)組”；每個(gè)數(shù)組需要聲明：名字元素類型一個(gè)VARRAY對(duì)象可以包含的最大元素個(gè)數(shù)；VARRAY類型的元素有特定的次序。414.3匯集類型例4.3.11建立一個(gè)簡(jiǎn)單的電話號(hào)碼本：每個(gè)人可能有多個(gè)對(duì)外聯(lián)系的電話號(hào)。createtypeextensions_tasvarray(4)ofint;createtablephonebook(phpersonperson_t,extensionsextensions_t,primarykey(phperson.ssno);例4.3.12檢索社會(huì)保障號(hào)為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語(yǔ)句無(wú)法訪問(wèn)一個(gè)以VARRAY定義的對(duì)象的下標(biāo)元素；TABLE()表同樣可以應(yīng)用于VARRAY值。例4.3.13在PH