如何将值插入嵌套表而不丢失该表中的数据?

2021-12-30 00:00:00 oracle11g oracle

如何将新数据插入到现有行的嵌套表中?例如，我已经定义了

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').

如何在不删除条目或丢失嵌套表中存储的数据的情况下，为 Category 中的该条目插入新数据到嵌套表中?

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 对单个命令的限制.这是因为该类别中有许多企业.我想创建类别，然后将业务一个一个(或者可能是小分组)插入到嵌套表业务"中.

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] 运算符:

SQL 小提琴

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

结果:

|       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

结果:

|         NAME | CARDINALITY(BUSINESSES) |
|--------------|-------------------------|
| lots of data |                   10000 |
|   Restaurant |                       2 |

相关文章