PROCEDURE EXTRACT_AC01(PRM_CODE OUT VARCHAR2, PRM_MSG OUT VARCHAR2) IS
CURSOR CUR_AC01_SY IS
SELECT * FROM AC01_SY;
ROW_CNT NUMBER(12) := 0;
REC_AC01 AC01%ROWTYPE;
V_AAC002 VARCHAR2(2000) := NULL;
BEGIN
PRM_CODE := '0';
PRM_MSG := 'Completed With No Error';
EXECUTE IMMEDIATE 'Truncate Table Ac01';
EXECUTE IMMEDIATE 'Truncate table LEE_DT_LOG';
EXECUTE IMMEDIATE 'Truncate Table AC01_REPEAT';
EXECUTE IMMEDIATE 'Truncate Table AC01_REPEAT_AAC001';
--执行第几次
SELECT OPERATE_TIMES.NEXTVAL INTO OPER_TIMES FROM DUAL;
--Extract data of Unemployment Insurance
FOR REC IN CUR_AC01_SY LOOP
BEGIN
--判断人员信息是否重复,如果重复,选择参保时间最近的记录
REC_AC01 := NULL;
ROW_CNT := ROW_CNT + 1;
--唯一索引
SELECT AHSIMIS_TEST.SEQ_A_ROWID.NEXTVAL
INTO REC_AC01.OAE001
FROM DUAL;
--个人编号
REC_AC01.AAC001 := '21' || TRIM(REC.AAB324) ||
LPAD(TRIM(REC.OAE001), 6, '0');
--单位编号
REC_AC01.AAB001 := UPPER(TRIM(REC.AAB001));
--身份证号
REC_AC01.AAC002 := F_ID(UPPER(TRIM(REC.AAC002)));
--姓名
REC_AC01.AAC003 := REPLACE(REPLACE(REPLACE(TRIM(REC.AAC003),
' ',
''),
'.',
''),
' ',
'');
REC_AC01.AAC003 := REPLACE(REC_AC01.AAC003, '*', '');
IF INSTR(REC_AC01.AAC003, '(', 1) <> 0 THEN
REC_AC01.AAC003 := SUBSTR(REC_AC01.AAC003,
1,
INSTR(REC_AC01.AAC003, '(', 1) - 1);
END IF;
IF INSTR(REC_AC01.AAC003, '(', 1) <> 0 THEN
REC_AC01.AAC003 := SUBSTR(REC_AC01.AAC003,
1,
INSTR(REC_AC01.AAC003, '(', 1) - 1);
END IF;
/* --个人拼音码
Select ahsimis.Gethzpy.Gethzfullpy(Rec_Ac01.Aac003)
Into Rec_Ac01.Aac022
From Dual;*/
--拼音码 首字母
SELECT GETHZPY.GETHZPYCAP(REC_AC01.AAC003)
INTO REC_AC01.AAC022
FROM DUAL;
--拼音码 全拼 利用BAC100作为临时字段,用户人员比较
SELECT GETHZPY.GETHZFULLPY(REC_AC01.AAC003)
INTO REC_AC01.BAC100
FROM DUAL;
--性别
SELECT DECODE(TRIM(REC.AAC004), '男', '1', '女', '2', '9')
INTO REC_AC01.AAC004
FROM DUAL;
--民族
REC_AC01.AAC005 := '1';
--出生日期
BEGIN
REC_AC01.AAC006 := TO_DATE(REC.AAC006, 'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF REC_AC01.AAC002 != '*' THEN
REC_AC01.AAC006 := F_GETBIRTHDAY(F_ID(REC.AAC002));
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01出生日期信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END;
--参加工作日期
BEGIN
REC_AC01.AAC007 := TO_DATE(REC.AAC007, 'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
BEGIN
REC_AC01.AAC007 := TO_DATE(SUBSTR(F_GETAAE041(REC.AAE041),
1,
4) || '-' ||
SUBSTR(F_GETAAE041(REC.AAE041),
5,
2) || '-01',
'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01工作日期信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END;
--出生.工作日期为空校验
BEGIN
IF REC_AC01.AAC006 IS NULL THEN
IF REC.AAC002 != '*' THEN
REC_AC01.AAC006 := F_GETBIRTHDAY(F_ID(REC.AAC002));
ELSE
REC_AC01.AAC006 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
END IF;
IF REC_AC01.AAC007 IS NULL THEN
IF REC.AAE041 IS NOT NULL THEN
REC_AC01.AAC007 := TO_DATE(SUBSTR(F_GETAAE041(REC.AAE041),
1,
4) || '-' ||
SUBSTR(F_GETAAE041(REC.AAE041),
5,
2) || '-01',
'YYYY-MM-DD');
ELSE
REC_AC01.AAC007 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
---日期确实为空或不正确的默认为1900年1月1日
END IF;
END IF;
IF REC_AC01.AAC006 > TO_DATE('2000-01-01', 'YYYY-MM-DD') OR
REC_AC01.AAC006 < TO_DATE('1900-01-01', 'YYYY-MM-DD') THEN
REC_AC01.AAC006 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
IF REC_AC01.AAC007 >
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') OR
REC_AC01.AAC007 < REC_AC01.AAC006 THEN
REC_AC01.AAC007 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01出生工作日期信息错误', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
--身份证号*还原
V_AAC002 := NULL;
IF REC_AC01.AAC002 = '*' THEN
V_AAC002 := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(REC.AAC002),' ','),'.',''),' ',''),'′',''),'?',''),'"',''),'‘',''),'’','');
IF SUBSTR(V_AAC002, LENGTH(V_AAC002), 1) NOT IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') THEN
REC_AC01.AAC002 := SUBSTR((SUBSTR(UPPER(TRIM(V_AAC002)),
1,
LENGTH(V_AAC002) - 1) || 'X'),
1,
18);
ELSE
REC_AC01.AAC002 := SUBSTR(UPPER(TRIM(V_AAC002)), 1, 18);
END IF;
END IF; --所属区号
REC_AC01.AAB301 := REC.AAB324;
REC_AC01.AIC001 := NULL; --养老保险视同缴费月数
--失业保险视同缴费月数
BEGIN
IF SUBSTR(REC.CNT, LENGTH(TRIM(REC.CNT)), 1) = '月' THEN
REC_AC01.AJC001 := REPLACE(TRIM(REC.CNT), '个月', '');
ELSE
REC_AC01.AJC001 := NVL(TRIM(REC.CNT), 0);
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01缴费月数信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
INSERT INTO AC01 VALUES REC_AC01;
IF MOD(ROW_CNT, 100) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END LOOP;
COMMIT;
--删除同一人在同一个单位重复参保记录
INSERT INTO AC01_REPEAT
SELECT AAB001, AAC002, AAC003, COUNT(1)
FROM AC01
GROUP BY AAB001, AAC002, AAC003
HAVING COUNT(1) > 1;
INSERT INTO AC01_REPEAT_AAC001
SELECT AAC001
FROM AC01 A
WHERE A.AAC001 !=
(SELECT MAX(B.AAC001)
FROM AC01 B
WHERE A.AAC002 = B.AAC002
AND A.AAC003 = B.AAC003
AND A.AAB001 = B.AAB001
AND B.AAC002 IN (SELECT C.AAC002 FROM AC01_REPEAT C));
DELETE FROM AC01
WHERE AAC001 IN (SELECT AAC001 FROM AC01_REPEAT_AAC001);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成AC01信息_COMMIT', --opertate type
NULL, --enterprise code
NULL, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END EXTRACT_AC01;
里面引用的函数都没有问题,涉及到的表都有索引plsql
CURSOR CUR_AC01_SY IS
SELECT * FROM AC01_SY;
ROW_CNT NUMBER(12) := 0;
REC_AC01 AC01%ROWTYPE;
V_AAC002 VARCHAR2(2000) := NULL;
BEGIN
PRM_CODE := '0';
PRM_MSG := 'Completed With No Error';
EXECUTE IMMEDIATE 'Truncate Table Ac01';
EXECUTE IMMEDIATE 'Truncate table LEE_DT_LOG';
EXECUTE IMMEDIATE 'Truncate Table AC01_REPEAT';
EXECUTE IMMEDIATE 'Truncate Table AC01_REPEAT_AAC001';
--执行第几次
SELECT OPERATE_TIMES.NEXTVAL INTO OPER_TIMES FROM DUAL;
--Extract data of Unemployment Insurance
FOR REC IN CUR_AC01_SY LOOP
BEGIN
--判断人员信息是否重复,如果重复,选择参保时间最近的记录
REC_AC01 := NULL;
ROW_CNT := ROW_CNT + 1;
--唯一索引
SELECT AHSIMIS_TEST.SEQ_A_ROWID.NEXTVAL
INTO REC_AC01.OAE001
FROM DUAL;
--个人编号
REC_AC01.AAC001 := '21' || TRIM(REC.AAB324) ||
LPAD(TRIM(REC.OAE001), 6, '0');
--单位编号
REC_AC01.AAB001 := UPPER(TRIM(REC.AAB001));
--身份证号
REC_AC01.AAC002 := F_ID(UPPER(TRIM(REC.AAC002)));
--姓名
REC_AC01.AAC003 := REPLACE(REPLACE(REPLACE(TRIM(REC.AAC003),
' ',
''),
'.',
''),
' ',
'');
REC_AC01.AAC003 := REPLACE(REC_AC01.AAC003, '*', '');
IF INSTR(REC_AC01.AAC003, '(', 1) <> 0 THEN
REC_AC01.AAC003 := SUBSTR(REC_AC01.AAC003,
1,
INSTR(REC_AC01.AAC003, '(', 1) - 1);
END IF;
IF INSTR(REC_AC01.AAC003, '(', 1) <> 0 THEN
REC_AC01.AAC003 := SUBSTR(REC_AC01.AAC003,
1,
INSTR(REC_AC01.AAC003, '(', 1) - 1);
END IF;
/* --个人拼音码
Select ahsimis.Gethzpy.Gethzfullpy(Rec_Ac01.Aac003)
Into Rec_Ac01.Aac022
From Dual;*/
--拼音码 首字母
SELECT GETHZPY.GETHZPYCAP(REC_AC01.AAC003)
INTO REC_AC01.AAC022
FROM DUAL;
--拼音码 全拼 利用BAC100作为临时字段,用户人员比较
SELECT GETHZPY.GETHZFULLPY(REC_AC01.AAC003)
INTO REC_AC01.BAC100
FROM DUAL;
--性别
SELECT DECODE(TRIM(REC.AAC004), '男', '1', '女', '2', '9')
INTO REC_AC01.AAC004
FROM DUAL;
--民族
REC_AC01.AAC005 := '1';
--出生日期
BEGIN
REC_AC01.AAC006 := TO_DATE(REC.AAC006, 'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF REC_AC01.AAC002 != '*' THEN
REC_AC01.AAC006 := F_GETBIRTHDAY(F_ID(REC.AAC002));
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01出生日期信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END;
--参加工作日期
BEGIN
REC_AC01.AAC007 := TO_DATE(REC.AAC007, 'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
BEGIN
REC_AC01.AAC007 := TO_DATE(SUBSTR(F_GETAAE041(REC.AAE041),
1,
4) || '-' ||
SUBSTR(F_GETAAE041(REC.AAE041),
5,
2) || '-01',
'YYYY-MM-DD');
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01工作日期信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END;
--出生.工作日期为空校验
BEGIN
IF REC_AC01.AAC006 IS NULL THEN
IF REC.AAC002 != '*' THEN
REC_AC01.AAC006 := F_GETBIRTHDAY(F_ID(REC.AAC002));
ELSE
REC_AC01.AAC006 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
END IF;
IF REC_AC01.AAC007 IS NULL THEN
IF REC.AAE041 IS NOT NULL THEN
REC_AC01.AAC007 := TO_DATE(SUBSTR(F_GETAAE041(REC.AAE041),
1,
4) || '-' ||
SUBSTR(F_GETAAE041(REC.AAE041),
5,
2) || '-01',
'YYYY-MM-DD');
ELSE
REC_AC01.AAC007 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
---日期确实为空或不正确的默认为1900年1月1日
END IF;
END IF;
IF REC_AC01.AAC006 > TO_DATE('2000-01-01', 'YYYY-MM-DD') OR
REC_AC01.AAC006 < TO_DATE('1900-01-01', 'YYYY-MM-DD') THEN
REC_AC01.AAC006 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
IF REC_AC01.AAC007 >
TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') OR
REC_AC01.AAC007 < REC_AC01.AAC006 THEN
REC_AC01.AAC007 := TO_DATE('1900-01-01', 'YYYY-MM-DD');
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01出生工作日期信息错误', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
--身份证号*还原
V_AAC002 := NULL;
IF REC_AC01.AAC002 = '*' THEN
V_AAC002 := REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(REC.AAC002),' ','),'.',''),' ',''),'′',''),'?',''),'"',''),'‘',''),'’','');
IF SUBSTR(V_AAC002, LENGTH(V_AAC002), 1) NOT IN
('1', '2', '3', '4', '5', '6', '7', '8', '9', '0') THEN
REC_AC01.AAC002 := SUBSTR((SUBSTR(UPPER(TRIM(V_AAC002)),
1,
LENGTH(V_AAC002) - 1) || 'X'),
1,
18);
ELSE
REC_AC01.AAC002 := SUBSTR(UPPER(TRIM(V_AAC002)), 1, 18);
END IF;
END IF; --所属区号
REC_AC01.AAB301 := REC.AAB324;
REC_AC01.AIC001 := NULL; --养老保险视同缴费月数
--失业保险视同缴费月数
BEGIN
IF SUBSTR(REC.CNT, LENGTH(TRIM(REC.CNT)), 1) = '月' THEN
REC_AC01.AJC001 := REPLACE(TRIM(REC.CNT), '个月', '');
ELSE
REC_AC01.AJC001 := NVL(TRIM(REC.CNT), 0);
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01缴费月数信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
INSERT INTO AC01 VALUES REC_AC01;
IF MOD(ROW_CNT, 100) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成失业AC01信息', --opertate type
NULL, --enterprise code
REC_AC01.AAC001, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END;
END LOOP;
COMMIT;
--删除同一人在同一个单位重复参保记录
INSERT INTO AC01_REPEAT
SELECT AAB001, AAC002, AAC003, COUNT(1)
FROM AC01
GROUP BY AAB001, AAC002, AAC003
HAVING COUNT(1) > 1;
INSERT INTO AC01_REPEAT_AAC001
SELECT AAC001
FROM AC01 A
WHERE A.AAC001 !=
(SELECT MAX(B.AAC001)
FROM AC01 B
WHERE A.AAC002 = B.AAC002
AND A.AAC003 = B.AAC003
AND A.AAB001 = B.AAB001
AND B.AAC002 IN (SELECT C.AAC002 FROM AC01_REPEAT C));
DELETE FROM AC01
WHERE AAC001 IN (SELECT AAC001 FROM AC01_REPEAT_AAC001);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
PRM_CODE := SQLCODE;
PRM_MSG := SQLERRM;
PRC_DT_LOGS('Extract_Ac01', -- procedure name
'生成AC01信息_COMMIT', --opertate type
NULL, --enterprise code
NULL, --employee code
'ERROR', --error level
PRM_MSG, --error information
OPER_TIMES);
END EXTRACT_AC01;
里面引用的函数都没有问题,涉及到的表都有索引plsql
自己加断点吧