在Oracle 11.2中工作
我已经创建了一种用于与员工相关联的电话号码的类型。...我正在尝试学习语法，并且不想因为数据库设计的好坏而受到批评...
这是我的* .sql文件
CREATE TYPE AddressType AS OBJECT(streetNumber NUMBER(5), StreetName VARCHAR2(30), city VARCHAR2(20));
/
CREATE TYPE empName AS OBJECT(firstname VARCHAR2(10), middle VARCHAR2(10), lastname VARCHAR2(10));
/
CREATE TYPE PhoneNumbers AS OBJECT(phNumb NUMBER(10), numType VARCHAR2(10));
/
CREATE TYPE ContactNumbers AS VARRAY(5) OF PhoneNumbers;
/
CREATE TABLE Workers(eid NUMBER(5), name empName, loc AddressType, contactNums ContactNumbers);
INSERT INTO Workers VALUES( 1,
empName('Ramos', null, 'Phil'),
AddressType(123, 'A Street', 'San Diego'),
ContactNumbers(
PhoneNumbers(1234567890, 'cell'),
PhoneNumbers(2345678901, 'home')
)
);
--Display all members of workers
SELECT * FROM Workers; -- i get something here I'm ok with
--Display all the names
--SELECT (w.name.firstname||' '||w.name.middle||' '||w.name.lastname) as "Name" FROM Workers w; --this works
--Display their location
--SELECT (w.loc.streetNumber||' '||w.loc.StreetName||', '||w.loc.city) as "Location" FROM Workers w; -- this works
--Display their phone numbers
SELECT (w.contactNums(1).phNumb||' <'||w.ContactNumbers(1).numType||'>') as "Phone Numbers" FROM Workers w; -- this line I can't figure out
问：如何写最后一个，SELECT以便获得电话号码列表？
我希望我的输出看起来像：
phone Numbers
-------------
1234567890 <cell>
2345678901 <home>
TABLE在以下情况下，您将使用use运算符VARRAY：
SELECT e.phNumb||' <'||e.numType||'>' as "Phone Numbers"
FROM Workers w, TABLE(w.contactNums) e;
Phone Numbers
-----------------------------------------------------
1234567890 <cell>
2345678901 <home>
您可以在《数据库Oject-Relational开发人员指南》中找到有关它的更多信息。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句