如何使用对象类型的嵌套表创建 Oracle 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9454933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I create an Oracle table with nested tables of object types?
提问by cagcowboy
I have a highly nested OBJECT TYPE structure that allows me to hold data in a tree-like manner.
我有一个高度嵌套的 OBJECT TYPE 结构，它允许我以树状方式保存数据。
I'd now like to create a table to cache these objects, but I'm having difficulty getting the table to create. I suspect my problem is the multiple nesting levels, but not sure how to fix.
我现在想创建一个表来缓存这些对象，但是我在创建表时遇到了困难。我怀疑我的问题是多个嵌套级别，但不知道如何解决。
CREATE TYPE STEP_OBJ AS OBJECT (
ID VARCHAR2(20),
OTHER_FIELDS VARCHAR2(100));
/
CREATE TYPE STEP_TAB_OBJ AS TABLE OF STEP_OBJ;
/
CREATE TYPE TREE_OBJ AS OBJECT (
STEPS STEP_TAB_OBJ,
COMPLETE VARCHAR2(1));
/
CREATE TYPE TABLE_OF_TREE_OBJ AS TABLE OF TREE_OBJ;
/
CREATE TABLE TREE_TABLE_CACHE (
HANDLE VARCHAR2(20),
TABLE_OF_TREES TABLE_OF_TREE_OBJ)
NESTED TABLE TABLE_OF_TREES STORE AS TREE_TABLE_CACHE_OBJS;
/
CREATE TABLE TREE_TABLE_CACHE (
*
ERROR at line 1:
ORA-02320: failure in creating storage table for nested table column TABLE_OF_TREES
ORA-22913: must specify table name for nested table column or attribute
Oracle 11g.
甲骨文 11g。
回答by APC
You need to specify a storage clause for the inner collection. The syntax is simply to wrap the clause for the nested collection in brackets like this:
您需要为内部集合指定一个存储子句。语法只是将嵌套集合的子句包装在括号中，如下所示：
SQL> CREATE TABLE TREE_TABLE_CACHE (
2 HANDLE VARCHAR2(20),
3 TABLE_OF_TREES TABLE_OF_TREE_OBJ)
4 NESTED TABLE TABLE_OF_TREES STORE AS TREE_TABLE_CACHE_OBJS
5 ( NESTED TABLE STEPS STORE AS TREE_TABLE_CACHE_STEPS)
8 /
Table created.
SQL>
