Выбрать запрос из вложенной таблицы ссылок

У меня есть две объектно-реляционные таблицы: person_table и account_table, построенные из объектов person и account. Строка учетной записи имеет вложенную таблицу для всех людей, использующих эту учетную запись, ее тип — «список_клиентов».

create type person as object(
  id integer,
  name varchar2,
  phone varchar2
);


create type customer_list as table of ref person;

create type account as object(
  accid integer,
  owned_by customer_list,
  balance Integer
);

create table account_table of account;

create table person_table of person;

Я хочу выбрать все учетные записи, принадлежащие определенному человеку, с учетом идентификатора человека. Как просмотреть все вложенные таблицы счетов, какой запрос? Я пробовал безуспешные запросы.

Например

select a.*
from account_table a
where table(a.owned_by) = (select ref(p) from person_table p where p.id=id_given);

Спасибо


sql oracle user-defined-types nested-table object-relational-model
person Guilhem Fry    schedule 18.03.2017    source источник
comment
Я отредактировал ваш вопрос, чтобы стандартизировать имена объектов.   -  person APC    schedule 18.03.2017

Ответы (1)


arrow_upward
1
arrow_downward

Вот мои тестовые данные:

SQL> select * from person_table;

        ID NAME                           PHONE
---------- ------------------------------ ------------
        11 MR KNOX                        07000700811
        22 SAM-I-AM                       07000700822

SQL> select * from account_table;

     ACCID
----------
OWNED_BY
--------------------------------------------------------------------------------
   BALANCE
----------
       123
CUSTOMER_LIST(00002202084B026AE209637509E050007F010047FD4B026AE209627509E050007F010047FD
            , 00002202084B026AE209647509E050007F010047FD4B026AE209627509E050007F010047FD)
      9900

       345
CUSTOMER_LIST(00002202084B026AE209637509E050007F010047FD4B026AE209627509E050007F010047FD)
        30


SQL>

Sam-I-Am и г-н Нокс являются совладельцами первой учетной записи, а г-н Нокс является единственным владельцем второй учетной записи. Чтобы найти учетные записи, принадлежащие мистеру Ноксу, мы можем запустить этот запрос, который ищет REF от person_table...

SQL> select acct.accid
  2        , acct.balance
  3        , deref(value(ob)).name as owned_by
  4        , deref(value(ob)).phone as ntact_noco
  5  from account_table acct
  6       cross join table(acct.owned_by) ob
  7  where ob.column_value = ( select ref(p) pref
  8                            from person_table p
  9                            where p.name = 'MR KNOX')
 10  /  

     ACCID    BALANCE OWNED_BY                       NTACT_NOCO
---------- ---------- ------------------------------ ------------
       123       9900 MR KNOX                        07000700811
       345         30 MR KNOX                        07000700811

SQL>

В качестве альтернативы мы можем просто найти человека с синтаксисом DEREF:

SQL> select acct.accid
  2         , acct.balance
  3         , deref(value(ob)).name as owned_by
  4         , deref(value(ob)).phone as contact_no
  5  from account_table acct
  6       cross join table(acct.owned_by) ob
  7  where  deref(value(ob)).id = 11 
  8  /

     ACCID    BALANCE OWNED_BY                       CONTACT_NO
---------- ---------- ------------------------------ ------------
       123       9900 MR KNOX                        07000700811
       345         30 MR KNOX                        07000700811

SQL>
person APC    schedule 18.03.2017