oracle 将数据插入嵌套表

Insert data into nested table

提问by user2381256

May I please have some help to Insert values into a table. The table is called PurchaseOrder_objtab. Here is the type for the table:

我可以请一些帮助将值插入表中。该表称为 PurchaseOrder_objtab。这是表的类型：

CREATE TYPE PurchaseOrder_objtyp AUTHID CURRENT_USER AS OBJECT
(
  PONo NUMBER,
  CUST_ref REF Customer_objtyp,
  OrderDate DATE,
  ShipDate DATE,
  LineItemList_ntab LineItemList_ntabtyp,
  ShipToAddr_obj Address_objtyp
)
/

The LineItemList_ntab is a nested table.

LineItemList_ntab 是一个嵌套表。

Here is the create code for LineItemList_ntabtyp:

下面是 LineItemList_ntabtyp 的创建代码：

CREATE TYPE LineItem_objtyp AS OBJECT (
  LineItemNo NUMBER,
  Stock_ref REF StockItem_objtyp,
  Quantity NUMBER,
  Discount NUMBER
)
/

CREATE TYPE LineItemList_ntabtyp AS TABLE of LineItem_objtyp
/

Here is some insert code that does work:

这是一些有效的插入代码：

INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(),
NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

In the above code, the LineItemList_ntab is an empty LineItemList_ntabtyp. I am wanting to add values to this nested table rather than it being empty in the INSERT INTO code.

在上面的代码中，LineItemList_ntab 是一个空的 LineItemList_ntabtyp。我想向这个嵌套表添加值，而不是它在 INSERT INTO 代码中为空。

Here is some of the code I have tried to insert values with:

这是我尝试插入值的一些代码：

INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
LineItemList_ntabtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004,
NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

INSERT INTO PurchaseOrder_objtab
SELECT 1008, REF(C),
SYSDATE, '12-MAY-1999',
INSERT INTO LineItemList_ntab SELECT 1, REF(StckItem),10,1 FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004, 
NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

The first insert statement above produces the following error:

上面的第一个插入语句产生以下错误：

SQL Error: ORA-00933: SQL command not properly ended

SQL 错误：ORA-00933：SQL 命令未正确结束

The second insert statement above produces the following error:

上面的第二个插入语句产生以下错误：

SQL Error: ORA-00936: missing expression

SQL 错误：ORA-00936：缺少表达式

May I please have some help to successfully insert values into the LineItemList_ntab nested table?

我可以帮忙将值成功插入 LineItemList_ntab 嵌套表中吗？

UPDATE

更新

I have this code to insert the data:

我有这个代码来插入数据：

INSERT INTO PurchaseOrder_objtab
SELECT 
1008, 
REF(C),
SYSDATE, 
'12-MAY-1999',
(SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab StckItem WHERE StckItem.StockNo = 1004),
NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

I am now getting the following error:

我现在收到以下错误：

SQL Error: ORA-22979: cannot INSERT object view REF or user-defined REF 22979. 00000 - "cannot INSERT object view REF or user-defined REF" *Cause: Attempt to insert an object view REF or user-defined REF in a REF column created to store system generated REF values" *Action: Make sure the REF to be inserted is not from an object view or from a user-defined REF column

SQL 错误：ORA-22979：无法插入对象视图 REF 或用户定义的 REF 22979。00000 -“无法插入对象视图 REF 或用户定义的 REF” *原因：尝试在一个对象视图中插入对象视图 REF 或用户定义的 REF创建 REF 列以存储系统生成的 REF 值” *操作：确保要插入的 REF 不是来自对象视图或来自用户定义的 REF 列

I am pretty sure that this error is because I may have created the table incorrectly, here is my table create code for the PurchaseOrder_objtab :

我很确定这个错误是因为我可能错误地创建了表，这是我的 PurchaseOrder_objtab 表创建代码：

CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp(
PRIMARY KEY (PONo),
FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
OBJECT ID PRIMARY KEY

  NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab(
(PRIMARY KEY(NESTED_TABLE_ID, LineItemNo))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR
/

Here is the code to create the nested table:

这是创建嵌套表的代码：

CREATE TYPE LineItemList_ntabtyp AS TABLE of LineItem_objtyp
/

UPDATE2

更新2

This code successfully adds information to the table:

此代码成功地将信息添加到表中：

INSERT INTO PurchaseOrder_objtab
SELECT 1009, REF(C),
SYSDATE, '10-MAY-1999',
LineItemList_ntabtyp(),
NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

So the error has something to do with the LineItemList_ntabtyp.

所以该错误与 LineItemList_ntabtyp 有关。

Here is the table creation code:

这是表创建代码：

CREATE TABLE PurchaseOrder_objtab OF PurchaseOrder_objtyp(
PRIMARY KEY (PONo),
FOREIGN KEY (Cust_ref) REFERENCES Customer_objtab)
OBJECT ID PRIMARY KEY

NESTED TABLE LineItemList_ntab STORE AS PoLine_ntab((PRIMARY KEY(NESTED_TABLE_ID,LineItemNo)) ORGANIZATION INDEX COMPRESS) RETURN AS LOCATOR/

I am sure the error has something to do with the OBJECT IDENTIFIER IS PRIMARY KEY for the above table.

我确信该错误与上表的 OBJECT IDENTIFIER IS PRIMARY KEY 有关。

UPDATE3

更新3

Thank you for the code. How about inserting more than one value into the nested table.

谢谢你的代码。如何在嵌套表中插入多个值。

Your line of code for one item is:

您的一项代码行是：

(SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
NULL)

How about if I would like to add two items, both StockNo 1004 and 1005? Is it something like this:

如果我想添加两个项目，StockNo 1004 和 1005 怎么样？是不是像这样：

(SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004) AND (SELECT LineItemList_ntabtyp(LineItem_objtyp(2, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1005),
NULL)

UPDATE4

更新4

If, I add three items to the nested table, does the "FROM DUAL" statement stay as "FROM DUAL", or does it change to something reflecting three items? Also, does the "UNION ALL" statement appear as follows:

如果我向嵌套表中添加三个项目，“FROM DUAL”语句是否保持为“FROM DUAL”，还是更改为反映三个项目的内容？此外，“UNION ALL”语句是否如下所示：

(
SELECT LineItemList_ntabtyp(
    SELECT LineItem_objtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1004         
    UNION ALL 
    SELECT LineItem_objtyp(2, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1005 
    UNION ALL 
    SELECT LineItem_objtyp(3, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1006
)
FROM DUAL
)

回答by ZZa

You should use a type constructor in a select subquery to fill the nested table in your occasion:

您应该在选择子查询中使用类型构造函数来填充您的场合的嵌套表：

INSERT INTO PurchaseOrder_objtab
SELECT 
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE:

更新：

I'm not sure about the fact you're doing the task correctly (I mean it's useless to store object types as tables to my mind, I'd use managable ordinary tables instead of such archutecture.

我不确定您是否正确地完成了任务（我的意思是将对象类型作为表存储在我的脑海中是没有用的，我会使用可管理的普通表而不是这种架构。

INSERT INTO PurchaseOrder_objtab
SELECT PurchaseOrder_objtyp(
    1008, 
    REF(C),
    SYSDATE, 
    '12-MAY-1999',
    (SELECT LineItemList_ntabtyp(LineItem_objtyp(1, REF(StckItem), 10, 1)) FROM Stock_objtab WHERE StockNo = 1004),
    NULL)
FROM Customer_objtab C 
WHERE C.CustNo = 1;

UPDATE 2:

更新 2：

If you're eager to insert more than one value into the nested table, use enumeration (if the values are written by hand) or subquery:

如果您渴望在嵌套表中插入多个值，请使用枚举（如果值是手工编写的）或子查询：

(
    SELECT LineItemList_ntabtyp(
        SELECT LineItem_objtyp(1, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1004         
        UNION ALL 
        SELECT LineItem_objtyp(2, REF(StckItem), 10, 1) FROM Stock_objtab WHERE StockNo = 1005
    ) 
    FROM DUAL
)