今天有个复杂的SQL，我对oralce就是一个小白。。。
需求为：需要将execl的数据导入到一张temp表，然后从temp表中查出所有的数据分别导入到两张表中，但是这两张表分别为主副表，主表必须先插，才能插副表。
我的SQL在38万条数据的基础上，需要16个小时。。。。呵呵哒。。真特么慢。。
但是同事的只用了1分钟多点。。。
算是学习了，记录一下
于是乎我的SQL如下：
--先删除索引
------------
declare
v_cnt number(8):= 0;
v_seq int := 0;
begin
while v_cnt < 324271 loop
--v_seq := SELECT SEQ_T_SH_CREADIT_PLM7020.nextval;
select SEQ_T_SH_CREADIT_PLM7020.nextval into v_seq from dual;
INSERT INTO T_SH_CREADIT_PLM7020 (
ID,
ID_NUM,
CUST_NAME,
CELL_PHONE,
APP_NUM,
APP_DATE,
APP_FLAG,
FRD_PRODUCT_NUM,
APP_PRODUCT_NUM,
TRANSFER_ORG_NUM,
APP_REASOM,
FRD_DATA_NUM,
ID_TYPE,
REPAY_DATE,
QUERY_STATE,
CREATE_TIME,
UPDATE_TIME
) SELECT
v_seq,
SS.ID_NUM,
SS.CUST_NAME,
SS.CELL_PHONE,
SS.APP_NUM,
SS.APP_DATE,
'1',
'PLM7020',
'A01',
'TRA000',
'R3',
'1',
'0',
'1',
'1',--已查询
SYSDATE,
SYSDATE
FROM
(
SELECT ROWNUM
rn,
t.ID_NUM,
t.CUST_NAME,
t.CELL_PHONE,
t.APP_NUM,
t.APP_DATE
FROM
T_TMP_CREDIT_PLM7020 t
WHERE
t.TERM_NUM = '1'
) SS
WHERE
rn > v_cnt
AND rn <= v_cnt+1;
--插入result表 SEQ_T_SH_PLM7020_RESULT.nextval, v_seq,
INSERT INTO T_SH_CREADIT_PLM7020_RESULT (
"ID",
"USER_PLM7020_ID",
"APP_DATE",
"APP_NO",
"APP_STATUS",
"APP_VALID",
"ERR_MSG",
"APP_WARN",
"DATA_STATUS",
"DATA_VALID",
"DATA_ERR_MSG",
"PD1",
"PD8",
"PD22",
"PD26",
"PD29",
"PD30",
"PD36",
"PD43",
"PD45",
"PD57",
"PD59",
"PD64",
"PD66",
"PD84",
"PD86",
"PD106",
"PD111",
"PD117",
"PD122",
"PD123",
"PD139",
"PD144",
"PD161",
"PD166",
"PD167",
"PD172",
"PD177",
"PD178",
"PD379",
"PD381",
"PD383",
"PD384",
"PD385",
"PD386",
"PD387",
"PD399",
"PD400",
"PD401",
"PD402",
"PD411",
"ORI_JSON",
"CREATE_TIME",
"UPDATE_TIME"
) SELECT
SEQ_T_SH_PLM7020_RESULT.nextval,
v_seq,
SS.APP_DATE,
SS.APP_NUM,
'',
'',
'',
'',
'',
'',
'',
SS.PD1,
SS.PD8,
SS.PD22,
SS.PD26,
SS.PD29,
SS.PD30,
SS.PD36,
SS.PD43,
SS.PD45,
SS.PD57,
SS.PD59,
SS.PD64,
SS.PD66,
SS.PD84,
SS.PD86,
SS.PD106,
SS.PD111,
SS.PD117,
SS.PD122,
SS.PD123,
SS.PD139,
SS.PD144,
SS.PD161,
SS.PD166,
SS.PD167,
SS.PD172,
SS.PD177,
SS.PD178,
SS.PD379,
SS.PD381,
SS.PD383,
SS.PD384,
SS.PD385,
SS.PD386,
SS.PD387,
SS.PD399,
SS.PD400,
SS.PD401,
SS.PD402,
SS.PD411,
'null',
SYSDATE,
SYSDATE
FROM
(
SELECT ROWNUM
rn,
t.APP_DATE,
t.APP_NUM,
t.PD1,
t.PD8,
t.PD22,
t.PD26,
t.PD29,
t.PD30,
t.PD36,
t.PD43,
t.PD45,
t.PD57,
t.PD59,
t.PD64,
t.PD66,
t.PD84,
t.PD86,
t.PD106,
t.PD111,
t.PD117,
t.PD122,
t.PD123,
t.PD139,
t.PD144,
t.PD161,
t.PD166,
t.PD167,
t.PD172,
t.PD177,
t.PD178,
t.PD379,
t.PD381,
t.PD383,
t.PD384,
t.PD385,
t.PD386,
t.PD387,
t.PD399,
t.PD400,
t.PD401,
t.PD402,
t.PD411
FROM
T_TMP_CREDIT_PLM7020 t
WHERE
t.TERM_NUM = '1'
) SS
WHERE
rn > v_cnt
AND rn <= v_cnt + 1;
--判断是否提交 1000条一次commit
if MOD(v_cnt, 1000)=0 THEN
if (v_cnt/1000)>=1 THEN
commit;
END if;
END IF;
--循环增加
v_cnt := v_cnt+1;
end loop;
commit;
end;
同事写的代码使用了自定义类型和游标，代码如下：
declare
v_cnt number(8):= 0;
v_plm7020_pk NUMBER;
-- 每次检索1000条
RECORDS_PER_LOOP CONSTANT NUMBER(10) := 1000;
-- 声明新的类型
TYPE PLM7020_LIST_TYPE IS TABLE OF t_tmp_credit_PLM7020%ROWTYPE INDEX BY BINARY_INTEGER;
v_PLM7020_LIST PLM7020_LIST_TYPE;
v_PLM7020_LIST_ROW T_TMP_CREDIT_PLM7020%rowtype;
-- 查询批次号是20181102001，且未处理的数据
cursor cur_PLM7020_LIST is
select * from t_tmp_credit_PLM7020 t1
where t1.status = 0
and t1.batch_num = '20181102001';
begin
-- 打开游标
OPEN cur_PLM7020_LIST;
LOOP
fetch cur_PLM7020_LIST BULK COLLECT
INTO v_PLM7020_LIST limit RECORDS_PER_LOOP;
-- 如果集合有数据
IF v_PLM7020_LIST.COUNT > 0 THEN
-- 循环插入到算话结果表
FOR i IN v_PLM7020_LIST.FIRST .. v_PLM7020_LIST.LAST LOOP
v_PLM7020_LIST_ROW := v_PLM7020_LIST(i);
-- 获取SEQ_T_SH_CREADIT_PLM7020主键
select SEQ_T_SH_CREADIT_PLM7020.nextval
into v_plm7020_pk
from dual;
INSERT INTO T_SH_CREADIT_PLM7020 (
ID,
ID_NUM,
CUST_NAME,
CELL_PHONE,
APP_NUM,
APP_DATE,
APP_FLAG,
FRD_PRODUCT_NUM,
APP_PRODUCT_NUM,
TRANSFER_ORG_NUM,
APP_REASOM,
FRD_DATA_NUM,
ID_TYPE,
REPAY_DATE,
QUERY_STATE,
CREATE_TIME,
UPDATE_TIME
) VALUES(
v_plm7020_pk,
v_PLM7020_LIST_ROW.ID_NUM,
v_PLM7020_LIST_ROW.CUST_NAME,
v_PLM7020_LIST_ROW.CELL_PHONE,
v_PLM7020_LIST_ROW.APP_NUM,
v_PLM7020_LIST_ROW.APP_DATE,
'1',
'PLM7020',
'A01',
'TRA000',
'R3',
'1',
'0',
'1', --几号端
'1',--已查询
SYSDATE,
SYSDATE
);
--插入result表 SEQ_T_SH_PLM7020_RESULT.nextval, v_seq,
INSERT INTO T_SH_CREADIT_PLM7020_RESULT (
ID,
USER_PLM7020_ID,
APP_DATE,
APP_NO,
APP_STATUS,
APP_VALID,
ERR_MSG,
APP_WARN,
DATA_STATUS,
DATA_VALID,
DATA_ERR_MSG,
PD1,
PD8,
PD22,
PD26,
PD29,
PD30,
PD36,
PD43,
PD45,
PD57,
PD59,
PD64,
PD66,
PD84,
PD86,
PD106,
PD111,
PD117,
PD122,
PD123,
PD139,
PD144,
PD161,
PD166,
PD167,
PD172,
PD177,
PD178,
PD379,
PD381,
PD383,
PD384,
PD385,
PD386,
PD387,
PD399,
PD400,
PD401,
PD402,
PD411,
ORI_JSON,
CREATE_TIME,
UPDATE_TIME
) values (
SEQ_T_SH_PLM7020_RESULT.nextval,
v_plm7020_pk,
v_PLM7020_LIST_ROW.APP_DATE,
v_PLM7020_LIST_ROW.APP_NUM,
'',
'',
'',
'',
'',
'',
'',
v_PLM7020_LIST_ROW.PD1,
v_PLM7020_LIST_ROW.PD8,
v_PLM7020_LIST_ROW.PD22,
v_PLM7020_LIST_ROW.PD26,
v_PLM7020_LIST_ROW.PD29,
v_PLM7020_LIST_ROW.PD30,
v_PLM7020_LIST_ROW.PD36,
v_PLM7020_LIST_ROW.PD43,
v_PLM7020_LIST_ROW.PD45,
v_PLM7020_LIST_ROW.PD57,
v_PLM7020_LIST_ROW.PD59,
v_PLM7020_LIST_ROW.PD64,
v_PLM7020_LIST_ROW.PD66,
v_PLM7020_LIST_ROW.PD84,
v_PLM7020_LIST_ROW.PD86,
v_PLM7020_LIST_ROW.PD106,
v_PLM7020_LIST_ROW.PD111,
v_PLM7020_LIST_ROW.PD117,
v_PLM7020_LIST_ROW.PD122,
v_PLM7020_LIST_ROW.PD123,
v_PLM7020_LIST_ROW.PD139,
v_PLM7020_LIST_ROW.PD144,
v_PLM7020_LIST_ROW.PD161,
v_PLM7020_LIST_ROW.PD166,
v_PLM7020_LIST_ROW.PD167,
v_PLM7020_LIST_ROW.PD172,
v_PLM7020_LIST_ROW.PD177,
v_PLM7020_LIST_ROW.PD178,
v_PLM7020_LIST_ROW.PD379,
v_PLM7020_LIST_ROW.PD381,
v_PLM7020_LIST_ROW.PD383,
v_PLM7020_LIST_ROW.PD384,
v_PLM7020_LIST_ROW.PD385,
v_PLM7020_LIST_ROW.PD386,
v_PLM7020_LIST_ROW.PD387,
v_PLM7020_LIST_ROW.PD399,
v_PLM7020_LIST_ROW.PD400,
v_PLM7020_LIST_ROW.PD401,
v_PLM7020_LIST_ROW.PD402,
v_PLM7020_LIST_ROW.PD411,
'offline',
SYSDATE,
SYSDATE
);
v_cnt :=v_cnt + 1;
END LOOP;
COMMIT;
END IF;
-- 清空列表 v_PLM7020_LIST
v_PLM7020_LIST.delete;
-- 如果游标检索不到数据，则退出循环
EXIT WHEN cur_PLM7020_LIST%NOTFOUND;
END LOOP;
-- 统计插入了多少行
dbms_output.put_line('插入数据行数:' || v_cnt);
-- 关闭游标
CLOSE cur_PLM7020_LIST;
EXCEPTION
WHEN OTHERS THEN
rollback;
dbms_output.put_line('执行出错');
end;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
