/*这是一个用于从多表中查询更新另一个表的Procedure，但是在编译的时候总是报错
Warning: Procedure created with compilation errors，实在是搞不懂了请各位高人给看看*/
create or replace procedure proc_update_wce
( v_adate8 nvarchar2 ) as
declare
c_adate8 attendance.adate8%TYPE;
cursor c is
select e.emp_id,a.card_num,a.adate8,a.atime4,a.door,m.in_out
from employee e,attendance a,atten_machine m
where e.card_number = a.card_num
and a.door = m.ma_id
and a.adate8 = c_adate8
order by e.emp_id,adate8,atime4;
v c%ROWTYPE;
begin
open c(v_adate8);
loop
fetch c into v;
exit when c%notfound;
update work_calen_emp w
set in_time = v.atime4,
door_in = v.door
where w.emp_id = v.emp_id
and w.bmouth || w.bday = v_adate8
and v.in_ouit = 1;
update work_calen_emp w
set out_time = v.atime4,
door_out = v.door
where w.emp_id = v.emp_id
and w.bmouth || w.bday = v_adate8
and v.in_ouit = 2;
end loop;
close c;
end;
|
