在PL/SQL块中定义引用其自身集合的记录类型 [英] Define a record type in PL/SQL block that references a collection of itself
问题描述
如何在PL/SQL匿名块中定义一个记录类型,该记录类型包含一个本身就是一个集合的属性?看下面的例子:
How to define a record type in PL/SQL anonymous block that contains a property that is a collection of itself? Look at the following example:
DECLARE
type t_item is record (
name varchar2(64),
children t_items -- referencing t_items type
);
type t_items is table of t_item; -- referencing t_item type
BEGIN
-- script code
END
PL/SQL没有类型提升,因此Oracle引擎引发异常:
PL/SQL has no type hoisting so Oracle engine raises an exception:
PLS-00498:在声明类型之前非法使用该类型
PLS-00498: illegal use of a type before its declaration
如何定义在其属性children
中包含table of t_item
的记录t_item
?
How to define a record t_item
that contains a table of t_item
in its property children
?
推荐答案
您可以通过继承使用在SQL Scope中定义的对象:
You can use objects defined in the SQL Scope using inheritance:
Oracle 11g R2架构设置:
CREATE TYPE abstract_item IS OBJECT (
name VARCHAR2(64)
) NOT FINAL NOT INSTANTIABLE
/
CREATE TYPE t_items IS TABLE OF abstract_item
/
CREATE TYPE t_item UNDER abstract_item (
children t_items
) INSTANTIABLE
/
查询1 :
SELECT t_item(
'1',
t_items(
t_item( '1.1', t_items() ),
t_item(
'1.2',
t_items(
t_item( '1.2.1', null )
)
),
t_item( '1.3', null )
)
)
FROM DUAL
结果 :( SQLFiddle不会不能很好地显示它-但它运行时没有错误)
Results: (SQLFiddle doesn't display it nicely - but it runs without errors)
| T_ITEM('1',T_ITEMS(T_ITEM('1.1',T_ITEMS()),T_ITEM('1.2',T_ITEMS(T_ITEM('1.2.1',NULL))),T_ITEM('1.3',NULL))) |
|-------------------------------------------------------------------------------------------------------------|
| oracle.sql.STRUCT@2a094aab |
您可以在PL/SQL中使用类似的声明:
You could use a similar declaration in PL/SQL:
DECLARE
items t_item;
BEGIN
items = t_item( 'Item Name', t_items( /* ... */ ) );
END;
/
这篇关于在PL/SQL块中定义引用其自身集合的记录类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!