;<br>
<br>
<br>
<br>
create procedure proc_analysis_incorrect_kq as<br>
type tbl_type is table of t_incorrect_log%rowtype index by pls_integer;<br>
tbl tbl_type; -- 用来存放考勤异常记录<br>
<br>
i pls_integer; -- 计数的<br>
<br>
v_final_date_prefix varchar2(20) := to_char(sysdate, 'yyyyMMdd');<br>
v_final_intime date := to_date(v_final_date_prefix || ' 09:00',<br>
'yyyyMMdd hh24:mi');<br>
v_final_outtime date := to_date(v_final_date_prefix || ' 18:00',<br>
'yyyyMMdd hh24:mi');<br>
begin<br>
<br>
-- 这里最好是分为两个for循环进行处理， 分别对 主SQL再添加过滤条件 status <> 0 表示迟到的 lost_range_mark <> 0 表示缺勤的<br>
-- 这里我偷懒就只写一个了<br>
for r in (with v_base as<br>
(select t.*,<br>
lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber,<br>
(case<br>
when t.intime > '09:00' and t.outtime < '18:00' then<br>
3<br>
when t.intime > '09:00' then<br>
1<br>
when t.outtime < '18:00' then<br>
2<br>
else<br>
0<br>
end) status<br>
from t_worktime t)<br>
select a.*,<br>
(case<br>
when next_daynumber - daynumber > 1 then<br>
1<br>
else<br>
0<br>
end) lost_range_mark<br>
from v_base a) loop<br>
<br>
-- 判断迟到<br>
if r.status <> 0 then<br>
i := tbl.count + 1; --要+1<br>
tbl(i).staffid := r.id;<br>
tbl(i).daynumber := r.daynumber;<br>
tbl(i).status := r.status;<br>
<br>
tbl(i).time1 := (to_date(v_final_date_prefix || ' ' || r.intime,<br>
'yyyyMMdd hh24:mi') - v_final_intime) * 24 * 60; --迟到时间(单位：分钟）<br>
tbl(i).time2 := (v_final_outtime -<br>
to_date(v_final_date_prefix || ' ' || r.outtime,<br>
'yyyyMMdd hh24:mi')) * 24 * 60; --早退时间(单位：分钟）<br>
<br>
if r.status = 2 then<br>
-- 只早退的不要记录迟到时间，否则会变成负数<br>
tbl(i).time1 := null;<br>
elsif r.status = 1 then<br>
-- 只迟到的不记录早退时间，否则会变成负数<br>
tbl(i).time2 := null;<br>
end if;<br>
<br>
tbl(i).lost := 0;<br>
end if;<br>
<br>
-- 判断缺勤<br>
if r.lost_range_mark = 1 then<br>
-- 找出缺勤的日期<br>
for j in r.daynumber + 1 .. r.next_daynumber - 1 loop<br>
dbms_output.put_line(r.daynumber || '~' || r.next_daynumber ||<br>
' ---> ' || j);<br>
i := tbl.count + 1; --要+1<br>
tbl(i).staffid := r.id;<br>
tbl(i).daynumber := j; -- 缺勤日<br>
tbl(i).status := null;<br>
tbl(i).time1 := null; --迟到时间(单位：分钟）<br>
tbl(i).time2 := null; --早退时间(单位：分钟）<br>
tbl(i).lost := 1;<br>
end loop;<br>
<br>
end if;<br>
<br>
-- 批量插入<br>
if i >= 50000 then<br>
forall x in 1 .. tbl.count<br>
insert into t_incorrect_log<br>
(staffid, daynumber, status, time1, time2, lost)<br>
values<br>
(tbl(x).staffid,<br>
tbl(x).daynumber,<br>
tbl(x).status,<br>
tbl(x).time1,<br>
tbl(x).time2,<br>
tbl(x).lost);<br>
<br>
commit;<br>
tbl.delete(); -- 一定要清空tbl<br>
end if;<br>
<br>
end loop;<br>
<br>
-- 最后一批数据可能没有达到50000条， 别漏了操作<br>
forall x in 1 .. tbl.count<br>
insert into t_incorrect_log<br>
(staffid, daynumber, status, time1, time2, lost)<br>
values<br>
(tbl(x).staffid,<br>
tbl(x).daynumber,<br>
tbl(x).status,<br>
tbl(x).time1,<br>
tbl(x).time2,<br>
tbl(x).lost);<br>
<br>
commit;<br>
<br>
end;<br>
/<br>
<br>
select * from t_incorrect_log;<br>
truncate table t_incorrect_log; -- 每次测试都先删掉记录表的数据<br>
<br>
<br>
-- 现在可在 t_incorrect_log 进行更多的分析了， 如看谁是迟到3巨头。。是否超出每个月限定的迟到次数、迟到时间等等。。。<br>
<br>
<br>
-- 校验是否正确：<br>
-- 运行完存储过程后， 查看 t_incorrect_log 表的数据量， 看和第一条SQL查询出来的数据量是否一致。<br>
-- 也可单独看某个员工的出勤，来和 t_incorrect_log 表的数据进行比对<br>
<br>
<br>
<br>
<br>
-- 第二题的答案：每个员工每个月工作总时长<br>
select b.dept, sum(a.day_worktime) / count(distinct a.id) staff_cnt / 30<br>
from (select x.id,<br>
(to_date(x.outtime, 'hh24:mi') - to_date(x.intime, 'hh24:mi')) * 24 day_worktime<br>
from t_worktime x) a<br>
inner join t_staffer b<br>
on (a.id = b.id)<br>
group by b.dept;<br>
<br>
</pre>
<br>
</div>
</div>
<br>
<script type=)
var cpro_id = "u6885494";
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有