如何插入REF表格？

Question

CREATE TYPE artist_table_type AS TABLE OF REF artist_type; 
/              

INSERT INTO track_table VALUES (        
    1,              
    'test title',           
    123,             
    to_date('12-09-1989', 'dd-mm-yyyy'),     
    artist_table_type(          
      -- What goes here??? 
    ),              
    artist_table_type()); 

我想插入到这个表中的对象的引用的嵌套表。我可以这样做吗？我将不得不放弃这张桌子吗？

Answer 1

您可以使用COLLECT和CAST函数在SQL中创建嵌套表。例如，如果您想根据某些条件从某个其他表中选择艺术家对象，我相信这应该起作用：

INSERT INTO track_table 
    SELECT 
    1, 
    'test title', 
    123, 
    to_date('12-09-1989', 'dd-mm-yyyy'), 
    CAST(COLLECT(REF(artists)) AS artist_table_type) 
    artist_table_type() 
    FROM 
    artists 
    WHERE <whatever the condition is for selecting the appropriate artists> 
    ; 

Answer 2

INSERT INTO TABLE(this table is syntax dont think its name of the table..) 

(SELECT t.nested_tbl_colm 
(nested_tbl_colm is nested table) 

FROM table_name t 
(table_name is the normal table whose one or more column is nested table here its nested_tbl_colm) 

WHERE t.tbl_colm= any input or conditions) 

VALUES 
(value to be inserted); 

COMMIT; 

其余的列将被正常插入并且上面的代码被用于插入到嵌套表中。 如果你不明白，请告诉我。