Oracle PL/SQL - 如何使用 oracle 集合将大量数据从一个表复制到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17284974/
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
Oracle PL/SQL - How do i copy a large amount of data from one table to another using oracle collections
提问by Richard Lin
This is part of an assignment to test the processing speed of each oracle collection (associative array, variable array, and nested tables), but i'm unsure how to approach this - i'm fairly new to PL/SQL and oracle in general.
这是测试每个 oracle 集合（关联数组、变量数组和嵌套表）的处理速度的作业的一部分，但我不确定如何解决这个问题 - 我对 PL/SQL 和 oracle 总体上还是很陌生.
So i have two tables created - one contains three columns, with over 3 million records, the other is empty. How do i copy the data from the filled table to the empty one using the collection method?
所以我创建了两个表 - 一个包含三列，有超过 300 万条记录，另一个是空的。如何使用收集方法将填充表中的数据复制到空表中？
Thanks in advance - please excuse me if i did not provide enough information - i'm fairly new to this...
提前致谢 - 如果我没有提供足够的信息，请原谅 - 我对这个还很陌生......
Richard
理查德
采纳答案by the_slk
/* package header */
CREATE OR REPLACE PACKAGE perfrormance_test AS
PROCEDURE nested_table;
PROCEDURE associative_array;
END perfrormance_test;
/* package body */
CREATE OR REPLACE PACKAGE BODY perfrormance_test AS
PROCEDURE nested_table
AS
/* select all records from source table */
CURSOR big_table_cur IS
SELECT col1
, col2
, col3
FROM big_table;
/* create nested table type and variable that will hold BIG_TABLE's records */
TYPE big_table_ntt IS TABLE OF big_table_cur%ROWTYPE;
l_big_table big_table_ntt;
BEGIN
/* open pointer to SELECT statement */
OPEN big_table_cur;
/* collect data in the collection */
FETCH big_table_cur BULK COLLECT INTO l_big_table;
/* close the pointer */
CLOSE big_table_cur;
/* print size of the collection */
DBMS_OUTPUT.PUT_LINE('Nested table holds: ' || TO_CHAR(l_big_table.COUNT) || ' records.');
/* write data down to target table */
FORALL indx IN l_big_table.FIRST..l_big_table.LAST
INSERT INTO big_table_target(col1, col2, col3)
VALUES (l_big_table(indx).col1, l_big_table(indx).col2, l_big_table(indx).col3);
/*
** or you can use it this way:
**
** VALUES (l_big_table(indx));
*/
/* print number of rows inserted */
DBMS_OUTPUT.PUT_LINE('Number of rows inserted ' || SQL%ROWCOUNT || ' rows');
/* save changes */
COMMIT;
/* or if you want undo changes */
/* ROLLBACK; */
END nested_table;
PROCEDURE associative_array
AS
/* create record (row) type */
TYPE strings_rec IS RECORD
(
one VARCHAR2(4000)
, two VARCHAR2(4000)
, three VARCHAR2(4000)
);
/* create collection of records: type and variable */
TYPE strings_aat IS TABLE OF strings_rec INDEX BY VARCHAR2(4000);
l_strings strings_aat;
BEGIN
/* populate collection with 3 000 000 rows */
/* looping can take some time */
FOR indx IN 1..3000000 LOOP
l_strings('indx_' || TO_CHAR(indx)).one := 'column one indx ' || TO_CHAR(indx);
l_strings('indx_' || TO_CHAR(indx)).two := 'column two indx ' || TO_CHAR(indx);
l_strings('indx_' || TO_CHAR(indx)).three := 'column three indx ' || TO_CHAR(indx);
END LOOP;
/* print size of the collection */
DBMS_OUTPUT.PUT_LINE('Assoc table holds: ' || TO_CHAR(l_strings.COUNT) || ' records.');
/*
** CREATE TABLE aat_target
** (
** t_id VARCHAR(4000)
** , one VARCHAR(4000)
** , two VARCHAR(4000)
** , three VARCHAR(4000)
** );
*/
/* insert rows */
FORALL indx IN l_strings.FIRST..l_strings.LAST
INSERT INTO aat_target(t_id, one, two, three)
VALUES (l_strings(indx), l_strings(indx).one, l_strings(indx).two, l_strings(indx).three);
/* print number of rows inserted */
DBMS_OUTPUT.PUT_LINE('Number of rows inserted ' || SQL%ROWCOUNT || ' rows');
COMMIT;
END associative_array;
END perfrormance_test;
Run SQL*PLUS from command line:
从命令行运行 SQL*PLUS：
sqlplus user/pass@db_name
Type:
类型：
SQL> SET SERVEROUTPUT ON
SQL> SET TIMING ON
SQL> BEGIN
2 perfrormance_test.nested_table;
3 END;
-- exec time will be displayed here
SQL> BEGIN
2 perfrormance_test.associative_array;
3 END;
-- exec time will be displayed here
It is not tested so you will have to correct small typos.
它没有经过测试，因此您必须更正小的拼写错误。
I do no use VARRAY.
我不使用VARRAY。
