利用procedure和cursor实现多表更新

Oracle Procedure中使用游标cursor更新多表(2008-11-17 16:02:38)转载▼标签： 杂谈
Oracle中写Procedure一直是Oracle开发中比较复杂的地方，但是掌握procedure 和cursor将使得你在Oracle开发中如鱼的水，真正体会Oracle的强大。下面通过我自己写的一个Procedure简单就procedure 和cursor的使用给一个简单的讲解和示例。

一、描述问题
我要解决的问题是使用Oracle作为数据库开发一套人力资源管理系统，在这个系统中每天员工的考勤数据是通过考勤软件每天采集考勤机的数据并存成固定格式的文本文档，有相关人员通过系统上传到数据库的attendance表中。同时数据库中的employee表记录员工信息包括工卡号 card_number。work_calen_emp表记录员工每天的工作日历明细也就是每天应该的上下班时间。考勤记录采集上传后需要更新 Work_calen_emp表中的上下班字段的数据。

二、解决思路
这是Oracle中典型的多表查询更新问题，这个问题如何解决可以看我的Blog中另一篇转载的文章《Oracle update 多表》，其中的一个方法就是使用Cursor这个方法非常灵活，适合复杂的多表查询更新。在这个Cursor的基础上必须加上更新的日期也就是要更新哪天的考勤记录。因此我写了如下的一个procedure

三、Procedure代码

create or replace procedure proc_update_wce
--1
( v_adate8 nvarchar2 ) as
--2

cursor c(c_adate8 attendance.adate8%type) 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;
--3
v c%ROWTYPE;
begin
--4
open c(v_adate8);
loop
--5
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_out = 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_out = 2;
--6
end loop;
close c;
end;

四、详细讲解这个procedure
首先Oracle 创建procedure的语法是
create or replace procedure p_name
( 变量 in/out/in out 变量类型 )
as

begin

exception

end
详细的语法可以找资料看看。
1 处就是声明了一个procedure变量用于传递日期值控制更新范围。
2 处申明游标注意这个游标是带参数的因为日期值同样要传递到游标中控制游标打开数据集的范围。注意这里
and a.adate8 = c_adate8

已经将游标参数作为查询条件来限制数据集大小。
3 处 声明一个游标行类型用于存放游标中每行的数据，这里也可以一个一个变量的申明但是这样太复杂。注意语法

v c%TYPE;

%TYPE 是游标属性之一，游标属性集合可以看看相关的参考资料。常用的还有 %notfund 。

4 处显式打开游标注意这里要传递参数

open c(v_adate8);

也就是把过程参数传递给游标。

5 处开始循环并将游标结果集中一行fetch到游标变量 V 中。在这里要注意的是

exit when c%notfound;

这一句写在循环块的头部，以确保不将空数据或者重复数据更新到目标表中。

6 处结束循环并关闭游标 。

五、结束
通过这个简单的存储过程可以看到Orale开发中的灵活，掌握存储过程和游标的使用也并不难，并且能够熟练的使用可以大幅度地提高开发的效率。