PL/SQL 中的嵌套类（对象类型）

Question

在 Java 中，可以定义递归（嵌套）类，例如：

private class Node

{

  Item item;

  Node next;

}

是否可以使用 PL/SQL 对象类型创建类似的结构？

Answer 1

好吧，您可以使用可替换类型来规避明显的自我引用问题，如下所示。CREATE TYPE item_tAS&nbsp;&nbsp; &nbsp;OBJECT (&nbsp; &nbsp; &nbsp; attr_a VARCHAR2 (30));/CREATE TYPE base_node_tAS&nbsp; &nbsp;OBJECT (&nbsp; &nbsp; &nbsp; item item_t)&nbsp; &nbsp; &nbsp; NOT FINAL&nbsp;&nbsp; &nbsp; &nbsp; NOT INSTANTIABLE;/CREATE TYPE node_t&nbsp; &nbsp;UNDER base_node_t (&nbsp; &nbsp; &nbsp; next_node base_node_t);/DECLARE&nbsp; &nbsp;nodes node_t :=&nbsp;&nbsp; &nbsp; &nbsp; node_t (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;item_t ( 'grandparent'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;node_t (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; item_t ( 'parent'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; node_t (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;item_t ( 'child'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL)));BEGIN&nbsp; &nbsp;DBMS_OUTPUT.put_line (&nbsp; &nbsp; &nbsp; XMLTYPE (nodes).getclobval (0, 2));END;/<NODE_T>&nbsp; <ITEM>&nbsp; &nbsp; <ATTR_A>grandparent</ATTR_A>&nbsp; </ITEM>&nbsp; <NEXT_NODE>&nbsp; &nbsp; <ITEM>&nbsp; &nbsp; &nbsp; <ATTR_A>parent</ATTR_A>&nbsp; &nbsp; </ITEM>&nbsp; &nbsp; <NEXT_NODE>&nbsp; &nbsp; &nbsp; <ITEM>&nbsp; &nbsp; &nbsp; &nbsp; <ATTR_A>child</ATTR_A>&nbsp; &nbsp; &nbsp; </ITEM>&nbsp; &nbsp; &nbsp; <NEXT_NODE/>&nbsp; &nbsp; </NEXT_NODE>&nbsp; </NEXT_NODE></NODE_T>但是，我不相信此类类型可以声明为列数据类型，因此不能将它们直接保存在关系表中（尽管诸如在 anydata 内部或转换为 xml 之类的间接方法会起作用）。此外，我还没有在实际场景中真正使用过它，我不知道是否有递归限制或这通常有多健壮。

Answer 2

没有这个选项，但是根据关于嵌套对象类型的Oracle 文档，您应该使用Collection来保存相同的类型（没有递归）集合类型是用于建模多值属性的对象数据类型。嵌套表是集合类型例如：describe&nbsp;dm_nested_categoricals DM_NESTED_CATEGORICALS&nbsp;TABLE&nbsp;OF&nbsp;SYS.DM_NESTED_CATEGORICAL

Answer 3

可以定义递归（嵌套）类是的，可以做到，但只能实现inheritance. 在创建对象时，您必须确保它不是Final。见下面的演示：--Created an Object which is not `Final`&nbsp; &nbsp; CREATE OR REPLACE TYPE prnt_obj AS OBJECT (&nbsp; &nbsp; &nbsp; &nbsp; id&nbsp; &nbsp; &nbsp;NUMBER,&nbsp; &nbsp; &nbsp; &nbsp; name&nbsp; &nbsp;VARCHAR2(10)&nbsp; &nbsp; )&nbsp; &nbsp; NOT INSTANTIABLE NOT FINAL; --<-- This make sure that the object can be nested.&nbsp; &nbsp; /&nbsp; &nbsp; --Created a type where the above table columns can be nested.&nbsp; &nbsp; CREATE OR REPLACE&nbsp; TYPE chld_obj UNDER prnt_obj (&nbsp; &nbsp; &nbsp; &nbsp; chld_id&nbsp; &nbsp; &nbsp;NUMBER,&nbsp; &nbsp; &nbsp; &nbsp; chld_name&nbsp; &nbsp;VARCHAR2(10)&nbsp; &nbsp; );&nbsp; &nbsp; /&nbsp; &nbsp; --Created a table of Object.&nbsp; &nbsp; CREATE TABLE TAB of chld_obj;&nbsp; &nbsp; /&nbsp; &nbsp; --Inserted record to the table&nbsp; &nbsp; INSERT INTO tab VALUES (&nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; 'XXX',&nbsp; &nbsp; &nbsp; &nbsp; 2,&nbsp; &nbsp; &nbsp; &nbsp; 'YYY'&nbsp; &nbsp; );&nbsp; &nbsp; --selected records&nbsp; &nbsp; SELECT * FROM tab;&nbsp; &nbsp; /