oracle INSERT 使用 LOOP 和 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13934243/
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
INSERT using LOOP with SELECT
提问by Jaanna
I am trying to insert the data from other table using INSERT/SELECT combo. I also need to insert increment with specific calculation. However, I can't figure out why it is not working.
我正在尝试使用 INSERT/SELECT 组合从其他表中插入数据。我还需要插入带有特定计算的增量。但是，我无法弄清楚为什么它不起作用。
I have the table (temp_business_area) like this:
我有这样的表（temp_business_area）：
----------
| bname |
----------
| London |
| Sydney |
| Kiev |
----------
I would like to have this in enum table:
我想在枚举表中有这个：
-----------------------------------------------------------------
| identifier | language_id | code | data | company_limit |
----------------------------------------------------------------|
| BUSINESS_UNIT | 0 | 100 | London | 126 |
| BUSINESS_UNIT | 0 | 200 | Sydney | 126 |
| BUSINESS_UNIT | 0 | 300 | Kiev | 126 |
-----------------------------------------------------------------
But what I get is this:
但我得到的是：
-----------------------------------------------------------------
| identifier | language_id | code | data | company_limit |
----------------------------------------------------------------|
| BUSINESS_UNIT | 0 | 100 | London | 126 |
| BUSINESS_UNIT | 0 | 100 | Sydney | 126 |
| BUSINESS_UNIT | 0 | 100 | Kiev | 126 |
| BUSINESS_UNIT | 0 | 200 | London | 126 |
| BUSINESS_UNIT | 0 | 200 | Sydney | 126 |
| BUSINESS_UNIT | 0 | 200 | Kiev | 126 |
| BUSINESS_UNIT | 0 | 300 | London | 126 |
| BUSINESS_UNIT | 0 | 300 | Sydney | 126 |
| BUSINESS_UNIT | 0 | 300 | Kiev | 126 |
-----------------------------------------------------------------
And here is my loop.
这是我的循环。
BEGIN
FOR x IN 1 .. 3 LOOP
INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT', 0, x*100, bname, 126 FROM temp_business_area;
END LOOP;
END;
I can't figure out where am I making mistake. Help?
我不知道我哪里出错了。帮助？
回答by a_horse_with_no_name
You are doing three inserts for eachrow in temp_business_area, that's why you wind up with 9 rows.
您正在为 中的每一行进行三个插入temp_business_area，这就是为什么您最终得到 9 行。
From your description of what you want to achieve you don't need the loop at all.
根据您对要实现的目标的描述，您根本不需要循环。
Just use a single insert:
只需使用一个插入：
INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT',
0,
row_number() over (order by null) * 100,
bname, 126
FROM temp_business_area;
The SELECT statement will return 3 rows, and each row will be inserted into the enumtable. The row_number()function will return an incrementing value for each row (1,2,3) which multplied by 100 will yield the code that you want.
SELECT 语句将返回 3 行，每行都将插入到enum表中。该row_number()函数将为每一行 (1,2,3) 返回一个递增值，乘以 100 将产生您想要的代码。
Edit
(after David's comments):
编辑
（在大卫的评论之后）：
The use of the windowing function does add a bit of an overhead to the statement. If the additional control over the numbering is not needed, using ROWNUMinstead will be a bit more efficient (although it won't matter for only three rows).
窗口函数的使用确实给语句增加了一些开销。如果不需要对编号进行额外的控制，使用ROWNUM它会更有效一些（尽管它只对三行没有影响）。
INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT',
0,
rownum * 100,
bname, 126
FROM temp_business_area;
回答by Andrey Khmelev
you may to use two variants else:
您可以使用其他两种变体：
declare
i integer := 1;
BEGIN
FOR x IN (select distinct bname from temp_business_area) LOOP
INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT', 0, i*100, x.bname, 126 FROM temp_business_area;
i := i + 1;
END LOOP;
END;
variant 2
变体2
BEGIN
FOR x IN 1..3 LOOP
INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT distinct 'BUSINESS_UNIT', 0, x*100, bname, 126 FROM temp_business_area WHERE rownum = x;
END LOOP;
END;
