存储过程，大批量数据，导出导入，效率求教
现在项目经理让我优化公司存储过程，要求把A系统的部分表数据查出来，然后按照B系统的表结构插入到B系统里。存储过程写好了，但是速度很慢
现在的情况：
1、数据量在400W左右，需要大批量查询插入
2、现在执行一次要7个小时左右才能结束
3、该存储过程维护了B系统4张表的数据，都来自A系统即@LD
下边是存储过程代码，求高人指教。
CREATE OR REPLACE PROCEDURE P_CREATE_CUSTOMER AS
V_CUSTOMERID NUMBER(18);
V_CUSTOMERNO VARCHAR2(7);
ld_fr_date DATE := SYSDATE;
ld_end_date DATE;
ld_etr_date date := TO_DATE('1900-12-12', 'yyyy-mm-dd');
CURSOR CUR_CUST_ALL IS
SELECT S_CUSTOMER.NEXTVAL, TO_CHAR(CUST_ID)
FROM TB_AC001@LD;--where cust_id < 1010000
BEGIN
--请空图片表
--DELETE FROM CUR_PICTURE;
OPEN CUR_CUST_ALL;
LOOP
FETCH CUR_CUST_ALL
INTO V_CUSTOMERID, V_CUSTOMERNO;
--DBMS_OUTPUT.PUT_LINE(V_PIC_ID);
EXIT WHEN CUR_CUST_ALL %NOTFOUND;
--新增客户主表信息 TB_AC001@LD -->>CUSTOMER
INSERT INTO CUSTOMER
(CUSTOMERID, CUSTOMERNO, IS_MAIL, IS_DM, HAPPY_CALL, CUS_TYPE)
SELECT V_CUSTOMERID,
V_CUSTOMERNO,
DECODE(MAIL_SEND_YN, 'Y', '1', 'N', '0', '0'),
DECODE(DM_SEND_YN, 'Y', '1', 'N', '0', '0'),
DECODE(HAPPY_CALL_YN, 'Y', '1', 'N', '0', '0'),
1
FROM TB_AC001@LD
WHERE CUST_ID = V_CUSTOMERNO;
--在表CUS_PERSONAL表中插入数据
INSERT INTO CUS_PERSONAL
(CUSTOMERID,
BUSI_CODE,
CUS_CAT_ID,
NAME_CN,
GENDER,
REGION_ID,
CRED_TYP_ID,
CRED_NUM,
BIRTHDAY,
CUS_TRA_ID,
JOB_TYP_ID,
CUS_GRA_ID,
HAS_MARRIED,
SALARY_ID,
CUS_FROM_ID,
HAS_CHECKED,
STA_ID,
CRE_USE_ID,
CRE_DAT,
UPD_USE_ID,
UPD_DAT,
EXT1,
EXT2,
EXT3,
IS_DELETE,
CUS_AGE,
BIRTHDAY_TYPE,
EXT4,
EXT5,
EXT6,
EXT7,
EXT8,
EXT9,
EXT10,
EXT11,
EXT12,
EXT13,
EXT14,
EXT15)
SELECT V_CUSTOMERID,
'',
CUST_LVL_CD,
NVL(CUST_NM, ' '),
DECODE(A.SEX_CD, 'F', 2, 'M', '1', 3),
NULL,
'1',
CITI_NO,
NULL,
BIZJOB_CD,
JOB,
DECODE(CUST_LVL_CD,
'10',
0,
'100',
19,
'20',
1,
'30',
2,
'40',
3,
'50',
4,
'70',
20,
'80',
12,
'90',
11,
0),
DECODE(MRG_YN, 'Y', 1, 'N', 0, 0),
DECODE(MONPAY_CD,
'110',
1,
'120',
2,
'130',
3,
'140',
4,
'150',
5,
NULL),
DECODE('CUST_SOURCE_CD',
'01',
7,
'02',
7,
'03',
9,
'04',
3,
'05',
8,
'06',
6,
'08',
2,
7),
1,
1,
1,
A.ETR_DATE,
1,
A.MDF_DATE,
POSS_PPC_AMT,
POSS_ACCM_AMT,
POSS_CRDT_AMT,
0,
0,
DECODE(A.BIRTH_CD, 'L', 2, 'S', 1, 2),
'', --购物频次
(SELECT TO_CHAR(TOT_ORD_AMT)
FROM TB_AJ001@LD
WHERE CUST_ID = A.CUST_ID
AND ROWNUM = 1), --上次购物金额
(SELECT TO_CHAR(SUM(LJ_AMT))
FROM TB_AG011@LD
WHERE CUST_ID = A.CUST_ID
AND SYSDATE BETWEEN FR_DATE AND END_DATE), --礼金
DECODE(FILE_RTN_CNT,
NULL,
0,
0,
0,
DECODE(FILE_ORD_CNT,
NULL,
0,
0,
0,
ROUND(FILE_RTN_CNT / NVL(FILE_ORD_CNT, 1), 2))), --退换货比率
'0', --拒收比率
'', --年收入
'', --家庭结构
FILE_ORD_CNT, --累计订购次数
FILE_ORD_AMT, --累计订购金额
FILE_CAN_CNT, --取消数量
FILE_CAN_AMT, --取消金额
MAIN_TM_ID --最终客服人员
FROM TB_AC001@LD A
LEFT JOIN TB_AC003@LD B
ON A.CUST_ID = B.CUST_ID
WHERE A.CUST_ID = V_CUSTOMERNO;
--固定电话
INSERT INTO CUS_CONTACT
(CONTACT_ID,
CUSTOMERID,
CONTACT_TYPE_ID,
MAIN_CONTACT_NUM,
STATUS_ID)
SELECT SEQ_CUS_CONTACT_ID.NEXTVAL,
V_CUSTOMERID， 1， TELD || TELH || TELN,
1
FROM TB_AC001@LD A
WHERE TELH > ' '
AND CUST_ID = V_CUSTOMERNO;
--移动电话
INSERT INTO CUS_CONTACT
(CONTACT_ID,
CUSTOMERID,
CONTACT_TYPE_ID,
MAIN_CONTACT_NUM,
STATUS_ID)
SELECT SEQ_CUS_CONTACT_ID.NEXTVAL,
V_CUSTOMERID,10, HP_TELD || HP_TELH || HP_TELN,
1
FROM TB_AC001@LD A
WHERE HP_TELH > ' '
AND CUST_ID = V_CUSTOMERNO;
--送货地址
INSERT INTO CUS_DELIVERY
(DELIVERY_ID,
CUSTOMERID,
--DELIVERY_ADDRESS,
DELIVERY_NAME,
DELIVERY_PHONE,
CRE_DAT,
UPD_DAT,
REGIONID_SHENG,
REGIONID_SHI,
REGIONID_QU,
DELIVERY_POST,
DLV_ADDR_SEQ,
REGIONID_JIEDAO,
DELIVERY_TEL)
SELECT SEQ_CUS_DELIVERY_ID.NEXTVAL,
V_CUSTOMERID,
--ADDR_1 || ADDR_2,
RCVER_NM,
--'******',
HP_TELD || HP_TELH || HP_TELN,
NVL(ETR_DATE, ld_etr_date),
MDF_DATE,
(SELECT REGIONID
FROM REGION
WHERE AREANO = LRGN_CD
AND REGIONTYPE = 1),
(SELECT REGIONID
FROM REGION
WHERE AREANO = MRGN_CD
AND REGIONTYPE = 2),
(SELECT REGIONID
FROM REGION
WHERE AREANO = SRGN_CD
AND REGIONTYPE = 3),
ZIP_NO,
DLV_ADDR_SEQ,
(SELECT REGIONID
FROM REGION
WHERE AREANO = ZIP_NO_SEQ
AND REGIONTYPE = 4),
TELD || TELH || TELN || NVL2(TELI, '转' || TELI, '')
FROM TB_AC002@LD A
WHERE CUST_ID = V_CUSTOMERNO;
COMMIT;
END LOOP;
CLOSE CUR_CUST_ALL;
SELECT SYSDATE INTO ld_end_date FROM dual;
INSERT INTO yt VALUES (ld_fr_date, ld_end_date);
COMMIT;
END;
从SQL上看，
(SELECT TO_CHAR(TOT_ORD_AMT) FROM TB_AJ001@LD WHERE CUST_ID = A.CUST_ID AND ROWNUM = 1), --上次购物金额 (SELECT TO_CHAR(SUM(LJ_AMT)) FROM TB_AG011@LD WHERE CUST_ID = A.CUST_ID AND SYSDATE BETWEEN FR_DATE AND END_DATE), --礼金
这种查询子查询作为字段写法不太好，尝试用连接或者其他方式来代替
