使用for循环索引插入表中

Question

尝试使用for循环将数据插入表中，对于表客户，我将数据插入到3个分区中。

这是代码

begin
FOR var_1 in 1 .. 3
LOOP
INSERT into tmp_table
(
SELECT cust_no FROM customer PARTITION (customer_PR$var_1)
WHERE city='ba' AND first_name='john'
AND salary=1000);
commit;
END LOOP;
END;
/

我得到的结果

Error report -
ORA-02149: Specified partition does not exist
ORA-06512: at line 4
02149. 00000 -  "Specified partition does not exist"
*Cause:    Partition not found for the object.
*Action:   Retry with correct partition name.

但是当我正常运行时(对于分区1(，

SELECT cust_no FROM customer PARTITION (customer_PR1);

我得到了预期的产出。

Answer 1

我认为您需要动态SQL来实现您的目标-

Declare
sql_text varchar2(500);
begin
FOR var_1 in 1 .. 3
LOOP
sql_text := 'INSERT into tmp_table
(SELECT cust_no FROM customer PARTITION (customer_PR' || var_1 || ')
WHERE city=''ba'' AND first_name=''john''
AND salary=1000)';
EXECUTE IMMEDIATE sql_text;
COMMIT;
END LOOP;
END;
/

Answer 2

您不需要3个不同的查询，只需正确使用PARTITION()子句-指定所需的所有分区：

INSERT into tmp_table
SELECT cust_no 
FROM customer PARTITION (customer_PR1,customer_PR3,customer_PR3)
WHERE city='ba' AND first_name='john'
AND salary=1000;

如果你真的需要动态sql，你可以连接你的查询：

begin
FOR var_1 in 1 .. 3
LOOP
execute immediate q'[
INSERT into tmp_table
SELECT cust_no FROM customer PARTITION (customer_PR]'||var_1||q'[
WHERE city='ba' AND first_name='john'
AND salary=1000)
]';
commit;
END LOOP;
END;
/

或者，根据您的分区规则，您可以使用partition for子句：

begin
FOR partitioning_key in {your_partitioning keys}
LOOP
INSERT into tmp_table
(
SELECT cust_no FROM customer PARTITION FOR (partitioning_key)
WHERE city='ba' AND first_name='john'
AND salary=1000);
commit;
END LOOP;
END;
/

https://www.oracle.com/technical-resources/articles/database/sql-11g-partitioning.html