cursor 应用:------
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;
动态cursor 实例应用:---
DECLARE
TYPE emp_cur IS REF CURSOR
RETURN emp%ROWTYPE;
empObj emp_cur;
empRecord emp%ROWTYPE;
BEGIN
OPEN empObj FOR
SELECT * FROM emp;
loop
FETCH empObj
INTO empRecord;
exit when empObj%notfound;
dbms_output.put_line(empRecord.ename);
end loop;
CLOSE empObj;
END;
---------动态表名
--用动态sql写
declare
table_name varchar(10);
sql_statement varchar2(1024) beginselect tname into table_name from T1;
sql_statement := 'select source from ' || table_name;
execute immediate sql_statement;
end;