oracle嵌套表选择 [英] oracle nested table select

问题描述

如何编写一个查询，列出所有驾驶员的名字和姓氏，以及他们所驾驶汽车的车辆识别号?这是我到目前为止的内容:

How can I write a query that lists the first and last names of all drivers, along with the vehicle identification number of the car they drive? This is what I have so far:

Create type DRIVER_TY as object (first_name VARCHAR2(20), last_name VARCHAR2(20), date_of_birth VARCHAR2(20));
Create type OWNER_TY as object (first_name VARCHAR2(20), last_name VARCHAR2(20), date_purchased VARCHAR2(25));
Create type OWNERS_NT is table of OWNER_TY;
Create table AUTOMOBILE (vehicle_identification_number NUMBER(25) PRIMARY KEY,
driver DRIVERS_VA,owner OWNERS_NT) NESTED TABLE owner STORE AS owner_infor;

推荐答案

您发布的代码与您的问题不太匹配，因此我创建了此变量表:

Your posted code doesn't quite match with your question, so I created this variant table:

Create table AUTOMOBILE (
   vehicle_identification_number NUMBER(25) PRIMARY KEY,
    drivers DRIVERS_VA,
    owners OWNERS_NT) 
    NESTED TABLE owners STORE AS owner_infor,
    NESTED TABLE drivers STORE AS driver_infor;

我用一些测试数据填充了它:

I populated it with some test data:

insert into automobile
values ( 23
        , DRIVERS_VA(DRIVER_TY('FOX', 'IN SOCKS', date '1947-08-11')
                      , DRIVER_TY('MR', 'KNOX', date '1941-02-01')
                      )
       , OWNERS_NT(OWNER_TY('THE', 'LORAX', date '1969-01-01')
                      )
   );
insert into automobile 
values ( 42
            , DRIVERS_VA(DRIVER_TY('SAM', 'I AM', date '1988-08-11')
                          , DRIVER_TY('DAISY-HEAD', 'MAYZIE', date '1983-02-01')
                          )
           , OWNERS_NT(OWNER_TY('THE', 'LORAX', date '1969-01-01')
                          )
       );

这是您想要的查询:

SQL> select a.vehicle_identification_number as vin
  2         , drv.first_name
  3         , drv.last_name
  4  from automobile a
  5       , table(a.drivers) drv
  6  /

       VIN FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
        23 FOX                  IN SOCKS
        23 MR                   KNOX
        42 SAM                  I AM
        42 DAISY-HEAD           MAYZIE

SQL> 

TABLE()函数将嵌套表转换为可查询的对象；它太酷了.请注意，别名是可选的，但还是建议(一如既往).

The TABLE() function converts a nested table into a queryable object; it's pretty cool. Note that aliases are optional but advisable (as always).

这篇关于oracle嵌套表选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！