cursor重要实例应用

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;