Oracle用户定义的对象 - 自引用类型的集合 [英] Oracle User Defined Objects - collection of self referencing type

问题描述

可能重复：

我有一个场景，其中我必须创建一个用户定义的类型A，它的类型为A.
我试着做以下但是没有帮助：

I have a scenario in which I have to create a user defined type A which has a collection of type A. I tried doing the following but did not help:

create or replace type sku_t;

create or replace type skulink_t as table of sku_t;

create or replace type sku_t as object(skuId varchar(12), display_name varchar(100), bundlnks ref skulink_t ); 

这会使对象sku_t和skulink_t处于不完整状态，编译器会提示完成它们。我不知道如何去这个。任何帮助将非常感激。

This leaves objects sku_t and skulink_t in an incomplete state and the compiler complains to complete them. I am not sure how to go about this. Any help will be much appreciated.

推荐答案

您需要使用REFs的嵌套表，而不是嵌套表的REF。

You need to use a nested table of REFs, instead of a REF of nested tables.

create or replace type sku_t;
create or replace type skulink_t as table of ref sku_t;
create or replace type sku_t as object(skuId varchar(12), display_name varchar(100), bundlnks skulink_t ); 

如何使用此示例：

create table sku_table of sku_t nested table bundlnks store as outer_nt;

insert into sku_table values(sku_t('sku1', 'sku1', null));
insert into sku_table values(sku_t('sku2', 'sku2', null));
insert into sku_table
values(sku_t('sku3', 'sku3',
    skulink_t
    (
        (select ref(s) from sku_table s where s.skuId = 'sku1'),
        (select ref(s) from sku_table s where s.skuId = 'sku2')
    )));

commit;

select deref(b.column_value).skuid skuid
from sku_table, table(bundlnks) b where skuid = 'sku3';

skuid
-----
sku1
sku2

但是一个常规的层次表可能会工作得更好99.99％的时间。

But a regular hierarchical table would probably work much better 99.99% of the time.

这篇关于Oracle用户定义的对象 - 自引用类型的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！