如何在嵌套表中插入值而不丢失该表中的数据? [英] How to insert a value into a nested table without losing data in that table?
问题描述
如何将新数据插入现有行的嵌套表中?例如,我定义了
How can I insert new data into an existing row's nested table? For example, I have defined
CREATE OR REPLACE TYPE businessTableForCategories AS TABLE OF VARCHAR(128);
/
CREATE TABLE Category (
name VARCHAR(128) PRIMARY KEY,
businesses businessTableForCategories
) NESTED TABLE businesses STORE AS categoryBusinessTable;
在类别中说一个带有name = 'Restaurant'
和businesses = businessTableForCategories('xzqpehc234ajdpa8')
的条目.
Say in Category there is an entry with name = 'Restaurant'
and businesses = businessTableForCategories('xzqpehc234ajdpa8')
.
如何在不删除条目或丢失存储在嵌套表中的数据的情况下,将新数据插入该类别中该条目的嵌套表中?
How can I insert new data into that nested table for that entry in Category without removing the entry, or losing the data stored in the nested table?
我问,因为我要插入的条目之一要求插入语句的长度为25137个字符,这超出了Oracle对单个命令的限制.这是因为该类别中有许多企业.我想创建类别,然后将业务一个接一个(或可能是小的分组)插入嵌套表"businesses"中.
I ask because one of the entries I am trying to insert requires an insert statement that is 25137 characters long, which is way past Oracle's limit for a single command. This is because there are many businesses in the category. I would like to create the category, and then insert the businesses one by one (or maybe small groupings) into the nested table "businesses".
推荐答案
使用MULTISET UNION [ALL|DISTINCT]
运算符:
Oracle 11g R2架构设置:
CREATE OR REPLACE TYPE businessTableForCategories AS TABLE OF VARCHAR(128);
/
CREATE TABLE Category (
name VARCHAR(128) PRIMARY KEY,
businesses businessTableForCategories
) NESTED TABLE businesses STORE AS categoryBusinessTable
/
INSERT INTO Category VALUES (
'Restaurant',
businessTableForCategories('xzqpehc234ajdpa8')
)
/
UPDATE Category
SET businesses = businesses
MULTISET UNION ALL
businessTableForCategories('other_value')
WHERE name = 'Restaurant'
/
查询1 :
SELECT *
FROM category
结果 :
Results:
| NAME | BUSINESSES |
|------------|------------------------------|
| Restaurant | xzqpehc234ajdpa8,other_value |
查询2 :
或使用绑定变量将集合包括在查询中:
Or use a bind variable to include the collection in the query:
DECLARE
businesses businessTableForCategories := businessTableForCategories();
BEGIN
businesses.EXTEND( 10000 );
FOR i IN 1 .. 10000 LOOP
businesses(i) := DBMS_RANDOM.STRING( 'x', 128 );
END LOOP;
INSERT INTO Category VALUES ( 'lots of data', businesses );
END;
查询3 :
SELECT name, CARDINALITY( businesses )
FROM Category
结果 :
Results:
| NAME | CARDINALITY(BUSINESSES) |
|--------------|-------------------------|
| lots of data | 10000 |
| Restaurant | 2 |
这篇关于如何在嵌套表中插入值而不丢失该表中的数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!