Oracle存储过程实例
程序员文章站
2022-06-03 23:51:53
...
Oracle存储过程
存储过程
例子
计算信用积分的存储过程：
--记录存储过程log的存储过程（方法函数） sp_exception_log
create or replace procedure sp_exception_log(v_date in date,
v_proc_name in varchar2,
v_state in varchar2,
v_msg in varchar2,
v_num number) is
---------------------------------------------------------------------
begin
insert into check_exception_day
values
(v_date, v_proc_name, v_state, v_msg, v_num);
commit;
exception
when others then
null;
end;
CREATE OR REPLACE PROCEDURE SP_GR_GHF_PFLC(C_DATE VARCHAR2) IS
-- V_FLAG NUMBER;
L_DATE VARCHAR2(8);
V_ERR_FLAG INT := 0;
V_ERROR VARCHAR2(200);
V_NOW_YEAR varchar2(8);
v_count number(12);
BEGIN
L_DATE := TO_CHAR(TO_DATE(C_DATE, 'YYYYMMDD') - 1, 'YYYYMMDD');
V_NOW_YEAR := TO_CHAR(TO_DATE(C_DATE, 'YYYYMMDD'), 'YYYY');
-- 开始执行
BEGIN
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
0);
END;
-- 个人基本信息表按照身份证去重，创建临时表
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table yw_p_grjbxx_qc';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE yw_p_grjbxx_qc parallel 8 as
SELECT *
FROM yw_p_grjbxx a
where id = (select max(id) from yw_p_grjbxx b where a.sfzh = b.sfzh)';
EXECUTE IMMEDIATE 'create index IND_yw_p_grjbxx_qc on yw_p_grjbxx_qc (SFZH) parallel 8';
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
1);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
1);
END;
-- 插入个人基本信息
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop TABLE GR_XYZBJFBZ_'||L_DATE;
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index SFZH_1129B';
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index XM_1129A';
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index XM_1129C';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE GR_XYZBJFBZ_'||C_DATE||' as SELECT * FROM GR_XYZBJFBZ';
EXECUTE IMMEDIATE 'truncate table GR_XYZBJFBZ';
EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ (id,xm,sfzh,status,create_time,NIANL)
SELECT id,
xm,
sfzh,
status,
create_time,
case
when '||V_NOW_YEAR||' - case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end < 25 then ''25岁以下''
when '||V_NOW_YEAR||' - case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end >= 25
and '||V_NOW_YEAR||' -case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end <= 40 then ''25-40岁''
when '||V_NOW_YEAR||' - case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end > 40
and '||V_NOW_YEAR||' -case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end <= 60 then ''40-60岁''
when '||V_NOW_YEAR||' - case when csrq is not null then
to_char(csrq, ''yyyy'')
else
substr(sfzh, 7, 4)
end > 60 then ''60岁以上'' else null end NIANL
FROM yw_p_grjbxx_qc';
commit;
EXECUTE IMMEDIATE 'create index SFZH_1129B on GR_XYZBJFBZ (SFZH) parallel 8';
EXECUTE IMMEDIATE 'create index XM_1129A on GR_XYZBJFBZ (XM) parallel 8';
EXECUTE IMMEDIATE 'create index XM_1129C on GR_XYZBJFBZ (ID) parallel 8';
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
2);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
2);
END;
-- 职业资格
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ghf_zyzg';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE ghf_zyzg as
SELECT SFZH, sum(cnt) cnt
FROM (SELECT SFZ SFZH, count(*) cnt
FROM (SELECT distinct XM, SFZ, ZCZSMC, ZYZGDJ, ZCZSBH
FROM [email protected]_ywk)
GROUP BY SFZ
union all
SELECT SFZH, count(*) cnt
FROM (SELECT distinct TGRQ, XM, SFZH, CYZGMC, CYZGDJ, FZRQ, FZDW
FROM YW_P_GRCYZG)
GROUP BY SFZH)
GROUP BY SFZH';
EXECUTE IMMEDIATE 'CREATE index i_ghf_zyzg_0 on ghf_zyzg(SFZH)';
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.zizzk = (SELECT b.cnt FROM ghf_zyzg b WHERE a.sfzh = b.sfzh)';
commit;
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.zizzk = 0 WHERE a.zizzk is null';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
3);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
3);
END;
-- 献血信息
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ghf_xxxx';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE ghf_xxxx as
SELECT sum(ZSXXL) ZSXXL,sfzh FROM YW_P_GRXIANXIE GROUP BY sfzh';
EXECUTE IMMEDIATE 'CREATE index i_ghf_xxxx_0 on ghf_xxxx(SFZH)';
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.xianxjl = (SELECT b.ZSXXL FROM ghf_xxxx b WHERE a.sfzh = b.sfzh)';
commit;
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.xianxjl = 0 WHERE a.xianxjl is null';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
4);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
4);
END;
-- 志愿者服务
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ghf_zyzfw';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE ghf_zyzfw as
SELECT sum(fwsc) fwsc,sfzh FROM YW_P_GRZYZFW GROUP BY sfzh';
EXECUTE IMMEDIATE 'CREATE index i_ghf_zyzfw_0 on ghf_zyzfw(SFZH)';
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.zhiyzfw = (SELECT b.fwsc FROM ghf_zyzfw b WHERE a.sfzh = b.sfzh)';
commit;
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.zhiyzfw = 0 WHERE a.zhiyzfw is null';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
5);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
5);
END;
-- 荣誉表彰
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ghf_rybz';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE ghf_rybz as
SELECT count(*) cnt,sfzh FROM YW_P_GRRYXX GROUP BY sfzh';
EXECUTE IMMEDIATE 'create index I_GHF_RYBZ_0 on GHF_RYBZ (SFZH)';
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.rongybz = (SELECT b.cnt FROM ghf_rybz b WHERE a.sfzh = b.sfzh)';
commit;
EXECUTE IMMEDIATE 'update /*+ parallel(a,8)*/ GR_XYZBJFBZ a
set a.rongybz = 0 WHERE a.rongybz is null';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
6);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
6);
END;
-- 个人评分
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop TABLE GR_GERENPINGFENG_'||L_DATE;
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index INDEX_GRPF_SCORE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index IND_GRPF_ID';
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index IND_GRPF_SFZH';
EXCEPTION WHEN OTHERS THEN NULL;
END;
BEGIN
EXECUTE IMMEDIATE 'drop index IND_GRPF_XM';
EXCEPTION WHEN OTHERS THEN NULL;
END;
EXECUTE IMMEDIATE 'CREATE TABLE GR_GERENPINGFENG_'||C_DATE||' as SELECT * FROM GR_GERENPINGFENG';
EXECUTE IMMEDIATE 'create index INDEX_GRPF_SCORE_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (SCORE)';
EXECUTE IMMEDIATE 'create index IND_GRPF_ID_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (ID)';
EXECUTE IMMEDIATE 'create index IND_GRPF_SFZH_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (SFZH)';
EXECUTE IMMEDIATE 'create index IND_GRPF_XM_'||C_DATE||' on GR_GERENPINGFENG_'||C_DATE||' (XM)';
EXECUTE IMMEDIATE 'truncate table GR_GERENPINGFENG';
EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG
SELECT id,
XM,
SFZH,
STATUS,
create_time,
(case
when zizzk >= 2 then
5
else
0
end) + (case
when xianxjl is not null and xianxjl > 0 and xianxjl <= 1000 then
6
when xianxjl is not null and xianxjl > 1000 and xianxjl <= 3000 then
11
when xianxjl is not null and xianxjl > 3000 and xianxjl <= 5000 then
17
when xianxjl is not null and xianxjl > 5000 then
22
else
0
end) + (case
when zhiyzfw is not null and zhiyzfw > 0 and zhiyzfw <= 80 then
6
when zhiyzfw is not null and zhiyzfw > 80 and zhiyzfw <= 240 then
11
when zhiyzfw is not null and zhiyzfw > 240 and zhiyzfw <= 480 then
17
when zhiyzfw is not null and zhiyzfw > 480 then
23
else
0
end) + (case
when rongybz = 1 then
11
when rongybz > 1 then
22
else
0
end) SCORE
FROM GR_XYZBJFBZ';
commit;
EXECUTE IMMEDIATE 'create index INDEX_GRPF_SCORE on GR_GERENPINGFENG (SCORE)';
EXECUTE IMMEDIATE 'create index IND_GRPF_ID on GR_GERENPINGFENG (ID)';
EXECUTE IMMEDIATE 'create index IND_GRPF_SFZH on GR_GERENPINGFENG (SFZH)';
EXECUTE IMMEDIATE 'create index IND_GRPF_XM on GR_GERENPINGFENG (XM)';
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
7);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
7);
END;
-- 个人基本信息增量数据
BEGIN
EXECUTE IMMEDIATE 'truncate table GR_XYZBJFBZ_ZL';
-- 新增人员
EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ_ZL
SELECT * FROM GR_XYZBJFBZ WHERE SFZH not in (SELECT SFZH FROM GR_XYZBJFBZ_'||C_DATE||')';
commit;
-- 有数据更新的人员
EXECUTE IMMEDIATE 'insert into GR_XYZBJFBZ_ZL
SELECT a.*
FROM GR_XYZBJFBZ a
join GR_XYZBJFBZ_'||C_DATE||' b on a.SFZH = b.SFZH
WHERE nvl(a.ZIZZK, 0) <> nvl(b.ZIZZK, 0)
or nvl(a.RONGYBZ, 0) <> nvl(b.RONGYBZ, 0)
or nvl(a.XIANXJL, 0) <> nvl(b.XIANXJL, 0)
or nvl(a.ZHIYZFW, 0) <> nvl(b.ZHIYZFW, 0)
or a.NIANL <> b.NIANL';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
8);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
8);
END;
-- 个人评分增量数据
BEGIN
EXECUTE IMMEDIATE 'truncate table GR_GERENPINGFENG_ZL';
-- 新增人员
EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG_ZL
SELECT * FROM GR_GERENPINGFENG WHERE SFZH not in (SELECT SFZH FROM GR_GERENPINGFENG_'||C_DATE||')';
commit;
-- 有数据更新的人员
EXECUTE IMMEDIATE 'insert into GR_GERENPINGFENG_ZL
SELECT a.*
FROM GR_GERENPINGFENG a
join GR_GERENPINGFENG_'||C_DATE||' b on a.SFZH = b.SFZH
WHERE to_number(a.SCORE) <> to_number(b.SCORE)';
commit;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
9);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
9);
END;
-- 新增XXX同步流程
-- 2019.4.8
-- xc
BEGIN
-------------------------------------------------------------- XXX基本信息更新 --------------------------------------------------------------
-- 判断是否有增量数据，没有直接跳出循环，有则判断是否有新增数据
SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL;
IF v_count = 0 THEN
null;
else
-- 判断是否有新增数据，如果有新增数据则插入新增数据
SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL WHERE SFZH not in (SELECT SFZH FROM [email protected]_ghf);
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'insert into [email protected]_ghf
SELECT * FROM GR_XYZBJFBZ_ZL WHERE SFZH not in (SELECT SFZH FROM [email protected]_ghf)';
commit;
-- 判断是否除了新增数据外还有更新数据，如果有则更新数据
SELECT count(*) into v_count FROM GR_XYZBJFBZ_ZL WHERE SFZH in (SELECT SFZH FROM [email protected]_ghf);
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'update [email protected]_ghf a
set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.GONGZWDX, a.ZHIC, a.ZHIY, a.JUZWDX, a.ZHUFQL, a.HUJ, a.NIANL, a.WENHCD, a.HUNYZK, a.JIANKZT, a.ZIZZK, a.YUQFKJL, a.BULXJL, a.RONGYBZ, a.TOUSLSJL, a.WEIJNGGSYFJL, a.XIANXJL, a.ZHIYZFW) = (SELECT a.ID,
a.XM,
a.STATUS,
a.CREATE_TIME,
a.GONGZWDX,
a.ZHIC,
a.ZHIY,
a.JUZWDX,
a.ZHUFQL,
a.HUJ,
a.NIANL,
a.WENHCD,
a.HUNYZK,
a.JIANKZT,
a.ZIZZK,
a.YUQFKJL,
a.BULXJL,
a.RONGYBZ,
a.TOUSLSJL,
a.WEIJNGGSYFJL,
a.XIANXJL,
a.ZHIYZFW
FROM GR_XYZBJFBZ_ZL b
WHERE a.SFZH =
b.SFZH)
WHERE a.sfzh in (SELECT SFZH FROM GR_XYZBJFBZ_ZL)';
END IF;
else
--如果没有新增数据则说明都是更新数据，执行更新流程
EXECUTE IMMEDIATE 'update [email protected]_ghf a
set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.GONGZWDX, a.ZHIC, a.ZHIY, a.JUZWDX, a.ZHUFQL, a.HUJ, a.NIANL, a.WENHCD, a.HUNYZK, a.JIANKZT, a.ZIZZK, a.YUQFKJL, a.BULXJL, a.RONGYBZ, a.TOUSLSJL, a.WEIJNGGSYFJL, a.XIANXJL, a.ZHIYZFW) = (SELECT a.ID,
a.XM,
a.STATUS,
a.CREATE_TIME,
a.GONGZWDX,
a.ZHIC,
a.ZHIY,
a.JUZWDX,
a.ZHUFQL,
a.HUJ,
a.NIANL,
a.WENHCD,
a.HUNYZK,
a.JIANKZT,
a.ZIZZK,
a.YUQFKJL,
a.BULXJL,
a.RONGYBZ,
a.TOUSLSJL,
a.WEIJNGGSYFJL,
a.XIANXJL,
a.ZHIYZFW
FROM GR_XYZBJFBZ_ZL b
WHERE a.SFZH =
b.SFZH)
WHERE a.sfzh in (SELECT SFZH FROM GR_XYZBJFBZ_ZL)';
END IF;
END IF;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
10);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
10);
END;
BEGIN
-------------------------------------------------------------- XXX分值更新 --------------------------------------------------------------
-- 判断是否有增量数据，没有直接跳出循环，有则判断是否有新增数据
SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL;
IF v_count = 0 THEN
null;
else
-- 判断是否有新增数据，如果有新增数据则插入新增数据
SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL WHERE SFZH not in (SELECT SFZH FROM [email protected]_ghf);
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'insert into [email protected]_ghf
SELECT * FROM GR_GERENPINGFENG_ZL WHERE SFZH not in (SELECT SFZH FROM [email protected]_ghf)';
commit;
-- 判断是否除了新增数据外还有更新数据，如果有则更新数据
SELECT count(*) into v_count FROM GR_GERENPINGFENG_ZL WHERE SFZH in (SELECT SFZH FROM [email protected]_ghf);
IF v_count > 0 THEN
EXECUTE IMMEDIATE 'update [email protected]_ghf a
set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.SCORE) = (SELECT a.ID,
a.XM,
a.STATUS,
a.CREATE_TIME,
a.SCORE
FROM GR_GERENPINGFENG_ZL b
WHERE a.SFZH =
b.SFZH)
WHERE a.sfzh in (SELECT SFZH FROM GR_GERENPINGFENG_ZL)';
END IF;
else
--如果没有新增数据则说明都是更新数据，执行更新流程
EXECUTE IMMEDIATE 'update [email protected]_ghf a
set (a.ID, a.XM, a.STATUS, a.CREATE_TIME, a.SCORE) = (SELECT a.ID,
a.XM,
a.STATUS,
a.CREATE_TIME,
a.SCORE
FROM GR_GERENPINGFENG_ZL b
WHERE a.SFZH =
b.SFZH)
WHERE a.sfzh in (SELECT SFZH FROM GR_GERENPINGFENG_ZL)';
END IF;
END IF;
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'SUCC',
NULL,
11);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERR_FLAG := V_ERR_FLAG + 1;
V_ERROR := SUBSTR(SQLERRM, 1, 200);
SP_EXCEPTION_LOG(SYSDATE,
'SP_GR_GHF_PFLC',
'ERROR',
V_ERROR,
11);
END;
-- 执行结束
END SP_GR_GHF_PFLC;
计算业务展示数据量的存储过程：
create or replace procedure p_dp_process is
--处理数据清洗完成后各个类型的数据量，然后记录到dp_process_result表中，供统计分析查询使用
--有效量： etl_type=1 and type=1 业务库表中的rwbh字段为关联条件，计算count
--未关联量：etl_type=1 and type=4 业务库表中的rwbh字段关联条件，计算jgdj_id is null的count
--疑问量:etl_type=0 and type=2 DP_SUMMARY_INVALID_DATA表中的logic_table_id，task_code未关联条件，计算count
--更新量： etl_type=0 and type=3 dp_data_size的总量-dp_data_size疑问数据量-业务库有效量
--为了提高执行效率可以增加一些索引：
--1、业务库所有YW_开头的表，对字段 RWBH 加索引
--2、业务库dp_data_size表，对字段 logic_table_id和task_code加联合索引，不过此表数据量应该比较小，不加也可以，影响不大
--PS:加了索引插入更新速度会相应变慢，可酌情处理
-- 游标
CURSOR handle_list IS
select UPPER(a.table_code) table_code,
UPPER(a.yw_table_name) yw_table_name,
B.logic_table_id,
B.dept_id,
s.code
from dp_ysyw_relation a
join view_version_table_dept b
on UPPER(a.table_code) = UPPER(b.code)
join sys_department s on b.dept_id=s.sys_department_id
where b.id<>'82a07a65647eb4d7016492e3773c0b93'
group by table_code,yw_table_name,B.logic_table_id,B.dept_id,s.code;
YOUXIAO_COUNT number; --有效量
JGDJ_ID_COUNT number; --含有jgdj_id的
v_sql varchar2(32767); --用于存放SQL的变量
BMBM_COUNT number; --含有jgdj_id的
begin
--删除旧数据
delete from dp_process_result_test;
commit;
for line in handle_list loop
YOUXIAO_COUNT := 0;
--查询数据量（全量的）
v_sql := 'select count(*) from ' || line.yw_table_name ||
' yw where rwbh is not null ';
execute immediate v_sql
into YOUXIAO_COUNT;
BMBM_COUNT := 0;
--查询数据量（全量的）
v_sql := 'select COUNT(1) from user_tab_cols where table_name=''' || line.yw_table_name ||'''and
column_name=''BMBM''';
execute immediate v_sql
INTO BMBM_COUNT;
IF YOUXIAO_COUNT > 0 AND BMBM_COUNT>0 THEN
/*---------------计算有效量并记录----------------*/
v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
table_code,etl_type,task_code,dept_id )
select sys_guid(),1,ruku as youxiao,CREATE_TIME,''' || line.table_code || ''',1,
rwbh,''' || line.dept_id || '''
from (
select count(1) as ruku,--入库量
rwbh,--批次编号
min(yw.CREATE_TIME) as CREATE_TIME--时间
from ' || line.yw_table_name ||' yw
JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
where rwbh is not null
and bmbm=''' || line.code || '''
AND LOG.logic_table_id =''' || line.logic_table_id || '''
group by rwbh) where ruku > 0';
execute immediate v_sql;
COMMIT;
/*---------------计算更新量并记录----------------*/
v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
table_code,etl_type,task_code,dept_id )
select sys_guid(),3,
case when gengxinliang>0 then gengxinliang else 0 end gengxinliang,
CREATE_TIME,''' || line.table_code || ''',1,
rwbh,''' || line.dept_id || '''
from (
select MAX(ds.all_size)-count(1)-MAX(ds.fail_size) as gengxinliang,--更新量
rwbh,--批次编号
min(yw.CREATE_TIME) as CREATE_TIME--时间
from ' || line.yw_table_name ||' yw
JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
LEFT JOIN ( SELECT TASK_CODE,sum(nvl(ds.all_size, 0)) all_size,sum(nvl(ds.fail_size, 0)) fail_size FROM dp_data_size ds group by TASK_CODE)DS
on yw.rwbh=ds.task_code
where rwbh is not null and bmbm=''' || line.code || '''
AND LOG.logic_table_id =''' || line.logic_table_id || '''
group by rwbh) where gengxinliang > 0';
execute immediate v_sql;
COMMIT;
JGDJ_ID_COUNT := 0;
--查询数据量（全量的）
v_sql := 'select COUNT(1) from user_tab_cols where table_name=''' || line.yw_table_name ||'''and
column_name=''JGDJ_ID''';
execute immediate v_sql
into JGDJ_ID_COUNT;
IF JGDJ_ID_COUNT > 0 THEN
/*---------------计算未关联量并记录----------------*/
--YW_L_JGSLBGDJ机构基本信息表不需要统计未关联量,自然人表不统计未关联量
IF line.yw_table_name != 'YW_L_JGSLBGDJ' and
SUBSTR(line.yw_table_name, 0, 4) = 'YW_L' THEN
v_sql := ' insert into dp_process_result_test(id,type,process_size,process_time,
table_code,etl_type,task_code,dept_id )
select sys_guid(),4,weiguanlian,CREATE_TIME,''' || line.table_code || ''',1,
rwbh,'''|| line.dept_id ||'''
from (
select count(1) as weiguanlian,--未关联量
rwbh,--批次编号
min(yw.CREATE_TIME)as CREATE_TIME--时间
from ' || line.yw_table_name ||' yw
JOIN SYS_DEPARTMENT S ON S.CODE=YW.BMBM
JOIN dp_data_report_log LOG ON LOG.DEPT_ID=S.SYS_DEPARTMENT_ID AND LOG.TASK_CODE=YW.RWBH
where rwbh is not null and jgdj_id is null
and bmbm=''' || line.code || '''
AND LOG.logic_table_id =''' || line.logic_table_id || '''
group by rwbh) where weiguanlian > 0';
execute immediate v_sql;
COMMIT;
END IF;
END IF;
end if ;
END LOOP;
-- 提交循环后不能整除的剩余事务
insert into dp_process_result_test
(id,
type,
process_size,
process_time,
table_code,
etl_type,
task_code,
dept_id)
values
(sys_guid(),
1,
281098232,
to_date('01-01-2018 17:20:34', 'dd-mm-yyyy hh24:mi:ss'),
'ZRRSBJNXX_P',
'1',
'201801010101',
'4028810a4e6bb410014e6c458e8f0096');
commit;
end p_dp_process;
上一篇： Java核心类库2——lang
下一篇： Java面向对象编程5——枚举