不知道我的存储过程写的好不好？

最新推荐文章于 2025-07-23 15:29:16 发布

最新推荐文章于 2025-07-23 15:29:16 发布 · 97 阅读

文章标签：

#C #C++ #C# #REST #F#

数据库专栏收录该内容

8 篇文章

订阅专栏

该存储过程用于根据输入的开始和结束日期以及工作日数量范围，计算并返回相应的颜色标记，黄色表示工作日在指定范围内，红色表示超过范围。同时，定义了一个功能函数用于工单通告预警，依据业务单位和当前日期计算预警级别、数量等信息。

不知道这样写存储过程，是不是不够优化？

create or replace function F_GET_WORK_DAYS_COLOR
(
in_begin_date DATE,
in_end_date DATE,
in_first_days number,
in_second_days number
)
return varchar2 is
/*=========================================================
功能：去掉休息日，去工作日，
说明：传入日期和工作日天数，返回到期时间
创建：
修改：
时间：2006-12-12
=========================================================*/
v_color varchar(20);
v_days number(6);
--v_exception Exception;
begin
select count(1)
into v_days
from ZX_CALENDAR_AND_REST z
where z.calendar_day >= trunc(in_begin_date)
and z.calendar_day <= trunc(in_end_date)
and z.rest_flag = 'N';
if v_days > in_first_days and v_days <= in_second_days then
v_color := 'yellow';
elsif v_days > in_second_days then
v_color := 'red';
else
v_color := 'white';
end if;
return v_color;
exception
when others then
rollback;
-- p_pub_error_log(v_result, sqlcode, sqlerrm, 'sf_f_note_issue_confirm');
return sysdate;
end;

create or replace function F_ZX_WORK_SHEET_VERIFY_NOTICES(
begin_day date,
end_day date,
v_operator varchar2,
stat_bureau varchar2)
return integer is
------------------------------------------------------------------------------------------
-- 功能：工单通告预警
-- 参数：当前日期
-- 创建： 2007-1-24
-- 修改：
------------------------------------------------------------------------------------------
v_day date;
v_end_day date;
v_busi varchar2(8);
v_result integer;
begin
v_day := sysdate ;
v_busi := stat_bureau;
v_end_day := end_day;
--初使化v_busi
if v_busi = '%' then
v_busi := 'TT';
end if;
--清除已经发布的内容
v_result := -10;
delete from ZX_WORK_SHEET_VERIFY_NOTICES h
where h.start_day = begin_day and
h.end_day = v_end_day and
(h.STAT_BUREAU = v_busi or h.bureau_code = v_busi); --新加的列，统计单位
dbms_output.put_line('liuhui');
--插入数据
v_result := -20;
if v_busi = 'TT' then
insert into ZX_WORK_SHEET_VERIFY_NOTICES
(
NOTICE_LEVEL ,
NOTICE_DAY ,
SHEET_NUM ,
SHEET_RATE ,
RATE_NUM ,
REMARK ,
BUREAU_CODE ,
OPERATOR ,
DATA_TIME ,
START_DAY ,
END_DAY ,
STAT_BUREAU )
SELECT DECODE(W.COLOR,'red',1,'yellow',2,3),
v_day,
W.NUM,
0,
0,
DECODE(W.COLOR,'red','非常严重','yellow','严重',''),
W.BUREAU_CODE,
v_operator ,
v_day,
begin_day ,
v_end_day ,
v_busi
FROM
(select count(1) NUM,
z.bureau_code BUREAU_CODE,
max(F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13)) COLOR
from ZX_WORK_SHEET_VERIFY z
where F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13) <> 'white'
and z.confirm_sign = 'Y' and z.app_date between begin_day and v_end_day and (z.error_type <> '0' or z.error_type is not null)
group by z.bureau_code,F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13)
ORDER BY COLOR,NUM ) W;
ELSE
insert into ZX_WORK_SHEET_VERIFY_NOTICES
(
NOTICE_LEVEL ,
NOTICE_DAY ,
SHEET_NUM ,
SHEET_RATE ,
RATE_NUM ,
REMARK ,
BUREAU_CODE ,
OPERATOR ,
DATA_TIME ,
START_DAY ,
END_DAY ,
STAT_BUREAU )
SELECT DECODE(W.COLOR,'red',1,'yellow',2,3),
v_day,
W.NUM,
0,
0,
DECODE(W.COLOR,'red','非常严重','yellow','严重',''),
W.BUREAU_CODE,
v_operator ,
v_day,
begin_day ,
v_end_day ,
v_busi
FROM
(select count(1) NUM,
z.bureau_code BUREAU_CODE,
max(F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13)) COLOR
from ZX_WORK_SHEET_VERIFY z
where F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13) <> 'white'
and z.confirm_sign = 'Y' and z.app_date between begin_day and v_end_day and (z.error_type <> '0' or z.error_type is not null)
group by z.bureau_code,F_GET_WORK_DAYS_COLOR(z.confirm_date,decode(z.back_verify_date,'',sysdate,z.back_verify_date),3,13)
ORDER BY COLOR,NUM ) W
WHERE W.BUREAU_CODE = v_busi;
end if;
------合计
v_result := -30;
IF v_busi = 'TT' then
insert into ZX_WORK_SHEET_VERIFY_NOTICES
(
NOTICE_LEVEL ,
NOTICE_DAY ,
SHEET_NUM ,
SHEET_RATE ,
RATE_NUM ,
REMARK ,
BUREAU_CODE ,
OPERATOR ,
DATA_TIME ,
START_DAY ,
END_DAY ,
STAT_BUREAU )
SELECT DECODE(t.notice_level,'',3,t.notice_level),
v_day,
sum(nvl(t.sheet_num,0)),
'',
max(0),
max(t.remark),
'TT' ,
v_operator ,
v_day,
begin_day ,
v_end_day ,
v_busi
from ZX_WORK_SHEET_VERIFY_NOTICES t
where t.start_day = begin_day
and t.end_day = v_end_day
and t.bureau_code <> v_busi
and t.stat_bureau = v_busi
group by rollup(t.notice_level);
end if;
----工单超期率不包括天津的
v_result := -40;
if v_busi = 'TT' then
update ZX_WORK_SHEET_VERIFY_NOTICES B
set (B.SHEET_RATE) = (select round(decode((select count(*) from ZX_WORK_SHEET_VERIFY z
where z.app_date between begin_day and v_end_day
and z.bureau_code = a.bureau_code
and (z.confirm_date <> date'1900-1-1' or z.confirm_date is not null)),0,0,
(select sum(c.SHEET_NUM) from ZX_WORK_SHEET_VERIFY_NOTICES c
where c.bureau_code = a.bureau_code
and a.notice_level = c.notice_level
and b.start_day = c.start_day
and c.end_day = b.end_day and c.bureau_code <> v_busi and
c.stat_bureau = v_busi )
/
(select count(*) from ZX_WORK_SHEET_VERIFY z
where z.app_date between begin_day and v_end_day
and z.bureau_code = a.bureau_code
and (z.confirm_date <> date'1900-1-1' or z.confirm_date is not null))),4)*100
from ZX_WORK_SHEET_VERIFY_NOTICES a
WHERE A.BUREAU_CODE = B.BUREAU_CODE
AND A.NOTICE_LEVEL = B.NOTICE_LEVEL
and b.start_day = a.start_day
and a.end_day = b.end_day
and a.bureau_code <> v_busi AND A.STAT_BUREAU = B.STAT_BUREAU);
else
update ZX_WORK_SHEET_VERIFY_NOTICES B
set (B.SHEET_RATE) = (select round(decode((select count(*) from ZX_WORK_SHEET_VERIFY z
where z.app_date between begin_day and v_end_day
and z.bureau_code = a.bureau_code
and (z.confirm_date <> date'1900-1-1' or z.confirm_date is not null)),0,0,
(select sum(nvl(c.SHEET_NUM,0)) from ZX_WORK_SHEET_VERIFY_NOTICES c
where c.bureau_code = a.bureau_code
and a.notice_level = c.notice_level
and b.start_day = c.start_day
and c.end_day = b.end_day
)
/
(select count(*) from ZX_WORK_SHEET_VERIFY z
where z.app_date between begin_day and v_end_day
and z.bureau_code = a.bureau_code
and (z.confirm_date <> date'1900-1-1' or z.confirm_date is not null))),4)*100
from ZX_WORK_SHEET_VERIFY_NOTICES a
WHERE A.BUREAU_CODE = B.BUREAU_CODE
AND A.NOTICE_LEVEL = B.NOTICE_LEVEL
and b.start_day = a.start_day
and a.end_day = b.end_day
and a.bureau_code = v_busi AND A.STAT_BUREAU = B.STAT_BUREAU);
end if;
-----排名
v_result := -50;
if v_busi = 'TT' then
update ZX_WORK_SHEET_VERIFY_NOTICES B
set (B.RATE_NUM) = (select r.row_num
FROM (
SELECT row_number () over (PARTITION by z.NOTICE_LEVEL order by z.SHEET_RATE asc) row_num,
Z.BUREAU_CODE , Z.NOTICE_LEVEL
FROM ZX_WORK_SHEET_VERIFY_NOTICES Z where
begin_day = z.start_day and v_end_day = z.end_day and z.notice_level <> 3 AND Z.BUREAU_CODE <> v_busi
AND Z.STAT_BUREAU = v_busi
) r
WHERE r.BUREAU_CODE = B.BUREAU_CODE AND r.NOTICE_LEVEL = B.NOTICE_LEVEL );
else update ZX_WORK_SHEET_VERIFY_NOTICES B
set (B.RATE_NUM) = '';
end if;
commit;
return 0;
exception
when others then
begin
rollback ;
P_PUB_ERROR_LOG(v_result,sqlcode,sqlerrm,'F_ZX_WORK_SHEET_VERIFY_NOTICES');
return v_result;
end;
END;