create or replace procedure pro_init_dzz_dy_exception
as
log_id number(16); --新增日志id
errdesc varchar(3000); --新增日志内容
v_userid varchar(40);
v_zjhm varchar(30);
v_csrq date;
v_rdsj date;
v_zzsj date;
v_idcardmult number(1);
v_idcardvalidity number(1);
v_subzjhmstr varchar(50);
v_csrqstr varchar(50);
dbname varchar(100);
insertSqlStrStart varchar(5000);
insertSqlStrMiddle varchar(5000);
insertSqlStrEnd varchar(5000);
insertSqlStr varchar(5000);
querysql varchar(5000);
deletesql varchar(5000);
v_all_dy sys_refcursor;
--v_dy_info zj_zjs.T_DY_INFO%rowtype;
cursor allSche is select schemaname from s_qkdzzinfo;
begin
SELECT SEQ_AUTO_CALC_LOG.NEXTVAL INTO LOG_ID FROM DUAL;
--插入日志表
INSERT INTO T_AUTO_CALC_LOG
(UUID, DJSJ, CCGCNAME, CWDM)
VALUES
(LOG_ID, SYSDATE, 'pro_init_dzz_dy_exception', '0');
COMMIT;
deletesql := 'delete from t_dzz_dy_exception_info';
execute immediate deletesql;
commit;
insertSqlStrStart := 'insert into t_dzz_dy_exception_info
(uuid,dzz_dy_id,datatype,errorlevel,errortype,operatetime)
select sys_guid(),';
insertSqlStrEnd := 'sysdate from dual';
for v_sche in allSche loop
dbname := v_sche.schemaname;
querysql := 'select userid,zjhm,csrq,rdsj,zzsj,idcardmult,idcardvalidity from '||dbname||'.t_dy_info where delflag =1 and dylb in (1,2) and dyzt = 1';
open v_all_dy for querysql;
loop
fetch v_all_dy into v_userid,v_zjhm,v_csrq,v_rdsj,v_zzsj,v_idcardmult,v_idcardvalidity;
--v_userid := v_dy_info.userid;
v_zjhm := REPLACE (v_zjhm,' ','');
--v_csrq := v_dy_info.csrq;
--v_rdsj := v_dy_info.rdsj;
--v_zzsj := v_dy_info.zzsj;
--v_idcardmult := v_dy_info.idcardmult;
--v_idcardvalidity := v_dy_info.idcardvalidity;
if v_zjhm is not null and v_csrq is not null then
v_subzjhmstr := substrb(v_zjhm, 7, 4)||'-'||substrb(v_zjhm, 11, 2)||'-'||substrb(v_zjhm, 13, 2);
v_csrqstr := to_char(v_csrq,'yyyy-mm-dd');
if v_subzjhmstr != v_csrqstr then
insertSqlStrMiddle := ',2,1,1,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
if v_rdsj is not null and v_zzsj is not null then
if (to_date('1921-07-01','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1923-06-09','yyyy-mm-dd')) or
(to_date('1928-06-18','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1945-04-22','yyyy-mm-dd')) or
(to_date('1969-04-01','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1977-08-11','yyyy-mm-dd')) then
if v_zzsj - v_rdsj <> 0 then
insertSqlStrMiddle := ',2,1,2,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
elsif to_date('1923-06-10','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1927-04-26','yyyy-mm-dd') then
if months_between(v_zzsj,v_rdsj) not in (3,6) then
insertSqlStrMiddle := ',2,1,2,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
elsif to_date('1927-04-27','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1928-06-17','yyyy-mm-dd') then
if months_between(v_zzsj,v_rdsj) not in (0,3) then
insertSqlStrMiddle := ',2,1,2,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
elsif to_date('1945-04-23','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1956-09-14','yyyy-mm-dd') then
if months_between(v_zzsj,v_rdsj) not in (6,12,24) then
insertSqlStrMiddle := ',2,1,2,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
else
if v_zzsj - v_rdsj not in (12) then
insertSqlStrMiddle := ',2,1,2,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
end if;
if v_idcardvalidity is not null then
if v_idcardvalidity = 1 then
insertSqlStrMiddle := ',2,1,3,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
if v_idcardmult is not null then
if v_idcardmult = 1 then
insertSqlStrMiddle := ',2,1,4,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
if v_rdsj is not null and v_csrq is not null then
if months_between(v_rdsj,v_csrq) < 228 then
insertSqlStrMiddle := ',2,1,5,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
if v_rdsj < v_csrq then
insertSqlStrMiddle := ',2,2,6,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
if v_rdsj is not null then
if v_rdsj < to_date('1921-07-01','yyyy-mm-dd') then
insertSqlStrMiddle := ',2,2,7,';
insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
execute immediate insertSqlStr;
commit;
end if;
end if;
exit when v_all_dy%notfound;
end loop;
close v_all_dy;
end loop;
--日志表更新结束时间
update t_auto_calc_log py set py.jsdjsj = sysdate where uuid = log_id;
commit;
EXCEPTION
WHEN OTHERS THEN
BEGIN
ROLLBACK;
ERRDESC := SQLERRM; --如果存储过程执行失败,则修改记录日志并将异常内容存入表中
DBMS_OUTPUT.PUT_LINE(ERRDESC);
UPDATE T_AUTO_CALC_LOG PY
SET PY.JSDJSJ = SYSDATE, PY.CWDM = '1', PY.CWXX = ERRDESC
WHERE UUID = LOG_ID;
COMMIT;
END;
end pro_init_dzz_dy_exception;
