oracle pl/sql如何创建列表列表

您可以将继承与Oracle类型一起使用,以拥有多个嵌套集合:

Oracle安装程序 :

CREATE TYPE collect_abstract_type IS OBJECT(
  isCollection NUMBER(1,0),
  MEMBER FUNCTION toString RETURN CLOB
) NOT FINAL NOT INSTANTIABLE
/

CREATE TYPE collect_element_type UNDER collect_abstract_type (
  value VARCHAR2(20),
  OVERRIDING MEMBER FUNCTION toString
    RETURN CLOB,
  CONSTRUCTOR FUNCTION collect_element_type(value VARCHAR2)
    RETURN SELF AS RESULT
)
/

CREATE TYPE BODY collect_element_type AS
  OVERRIDING MEMBER FUNCTION toString
    RETURN CLOB
  IS
  BEGIN
    RETURN '"' || SELF.value || '"';
  END;

  CONSTRUCTOR FUNCTION collect_element_type(value VARCHAR2)
    RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.isCollection := 0;
    SELF.value := value;
    RETURN;
  END;
END;
/

CREATE OR REPLACE TYPE collect_abstract_type_table IS TABLE OF collect_abstract_type
/

CREATE TYPE collect_list_type UNDER collect_abstract_type (
  items collect_abstract_type_table,
  OVERRIDING MEMBER FUNCTION toString
    RETURN CLOB,
  CONSTRUCTOR FUNCTION collect_list_type
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION collect_list_type(items collect_abstract_type_table)
    RETURN SELF AS RESULT
)
/

CREATE TYPE BODY collect_list_type AS
  OVERRIDING MEMBER FUNCTION toString
    RETURN CLOB
  IS
    p_string CLOB;
    p_first  BOOLEAN := TRUE;
  BEGIN
    p_string := '[';
    FOR i IN 1 .. SELF.items.count LOOP
      IF p_first THEN
        p_first := FALSE;
      ELSE
        p_string := p_string || ',';
      END IF;
      IF SELF.items(i) IS NULL THEN
        p_string := p_string || 'NULL';
      ELSE
        p_string := p_string || SELF.items(i).toString;
      END IF;      
    END LOOP;
    p_string := p_string || ']';
    RETURN p_string;
  END;

  CONSTRUCTOR FUNCTION collect_list_type
    RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.isCollection := 1;
    SELF.items := collect_abstract_type_table();
    RETURN;
  END;

  CONSTRUCTOR FUNCTION collect_list_type(items collect_abstract_type_table)
    RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.isCollection := 1;
    IF items IS NULL THEN
      SELF.items := collect_abstract_type_table();
    ELSE
      SELF.items := items;
    END IF;
    RETURN;
  END;
END;
/

然后你可以做一些类似的事情:

SELECT collect_list_type(
         collect_abstract_type_table(
           collect_element_type( 'Element1' ),
           collect_list_type(
             collect_abstract_type_table(
               collect_element_type( 'Element2' ),
               collect_list_type()
             )
           ),
           NULL,
           collect_element_type( 'Element4' )
         )
       ).toString() AS list
FROM   DUAL;

哪些输出:

LIST
--------------------------------------------
["Element1",["Element2",[]],NULL,"Element4"]