在网上看到这种办法,大家帮我检查一下。另外,还有什么其他办法做这个事?
表数据有24W行~~
/* Formatted on 2009/07/16 16:08 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE cp_data_2
AS
TYPE t_cur IS REF CURSOR;
c_table t_cur;
TYPE t_employee IS TABLE OF wo_prd_cost_aud@db89%ROWTYPE;
v_employees t_employee;
ROWS NUMBER := 50;
v_sql VARCHAR2 (300);
v_table VARCHAR (50);
BEGIN
v_table := 'wo_prd_cost_aud';
OPEN c_table FOR
SELECT *
FROM wo_prd_cost_aud@db89; --sour
v_sql :=
'insert /*+ APPEND*/ into '
|| v_table
|| ' (ORDSEQ,ITMNO,VERNO,COSTNO,ORDNO,FACTORY,CHK,PRD,PURCHASE_UNIT,PURCHASE_BASE_CONV,PURCHASE_WEIGHT,WEIGHT_AVG_PRC,
CUST_UNIT,CUST_BASE_CONV,USG,WASTE,BASECONV,PRDSZKG,CRCY,BOM,BOL,UPDDTTM,UPDUSER,AVG_CUSTOMS_PRC,CUSTOMS_BOM)
values (:1, :2,:3,:4,:5,:6, :7, :8,:9,:10, :11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25)';
LOOP
FETCH c_table
BULK COLLECT INTO v_employees LIMIT ROWS; --分批
DBMS_OUTPUT.put_line (v_employees.COUNT);
FOR i IN 1 .. v_employees.COUNT
LOOP
EXECUTE IMMEDIATE v_sql
USING v_employees (i).ordseq,
v_employees (i).itmno,
v_employees (i).verno,
v_employees (i).costno,
v_employees (i).ordno,
v_employees (i).factory,
v_employees (i).chk,
v_employees (i).prd,
v_employees (i).purchase_unit,
v_employees (i).purchase_base_conv,
v_employees (i).purchase_weight,
v_employees (i).weight_avg_prc,
v_employees (i).cust_unit,
v_employees (i).cust_base_conv,
v_employees (i).usg,
v_employees (i).waste,
v_employees (i).baseconv,
v_employees (i).prdszkg,
v_employees (i).crcy,
v_employees (i).bom,
v_employees (i).bol,
v_employees (i).upddttm,
v_employees (i).upduser,
v_employees (i).avg_customs_prc,
v_employees (i).customs_bom;
END LOOP;
COMMIT;
EXIT WHEN c_table%NOTFOUND;
END LOOP;
CLOSE c_table;
END;
表数据有24W行~~
/* Formatted on 2009/07/16 16:08 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE cp_data_2
AS
TYPE t_cur IS REF CURSOR;
c_table t_cur;
TYPE t_employee IS TABLE OF wo_prd_cost_aud@db89%ROWTYPE;
v_employees t_employee;
ROWS NUMBER := 50;
v_sql VARCHAR2 (300);
v_table VARCHAR (50);
BEGIN
v_table := 'wo_prd_cost_aud';
OPEN c_table FOR
SELECT *
FROM wo_prd_cost_aud@db89; --sour
v_sql :=
'insert /*+ APPEND*/ into '
|| v_table
|| ' (ORDSEQ,ITMNO,VERNO,COSTNO,ORDNO,FACTORY,CHK,PRD,PURCHASE_UNIT,PURCHASE_BASE_CONV,PURCHASE_WEIGHT,WEIGHT_AVG_PRC,
CUST_UNIT,CUST_BASE_CONV,USG,WASTE,BASECONV,PRDSZKG,CRCY,BOM,BOL,UPDDTTM,UPDUSER,AVG_CUSTOMS_PRC,CUSTOMS_BOM)
values (:1, :2,:3,:4,:5,:6, :7, :8,:9,:10, :11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25)';
LOOP
FETCH c_table
BULK COLLECT INTO v_employees LIMIT ROWS; --分批
DBMS_OUTPUT.put_line (v_employees.COUNT);
FOR i IN 1 .. v_employees.COUNT
LOOP
EXECUTE IMMEDIATE v_sql
USING v_employees (i).ordseq,
v_employees (i).itmno,
v_employees (i).verno,
v_employees (i).costno,
v_employees (i).ordno,
v_employees (i).factory,
v_employees (i).chk,
v_employees (i).prd,
v_employees (i).purchase_unit,
v_employees (i).purchase_base_conv,
v_employees (i).purchase_weight,
v_employees (i).weight_avg_prc,
v_employees (i).cust_unit,
v_employees (i).cust_base_conv,
v_employees (i).usg,
v_employees (i).waste,
v_employees (i).baseconv,
v_employees (i).prdszkg,
v_employees (i).crcy,
v_employees (i).bom,
v_employees (i).bol,
v_employees (i).upddttm,
v_employees (i).upduser,
v_employees (i).avg_customs_prc,
v_employees (i).customs_bom;
END LOOP;
COMMIT;
EXIT WHEN c_table%NOTFOUND;
END LOOP;
CLOSE c_table;
END;
wo_prd_cost_aud是一个已建好的空表
wo_prd_cost_aud@db89是通过DBLink连接到另一个数据库里的一个表,里面有20多万数据
执行只要几秒就行了 - -我昏
最后我用这个办法建好一个表,再把索引、主键等其他东西加上去……
嘿嘿