PL/SQL Cursor常见用法模板

1.Loop

DECLARE
   emp_rec emp%rowtype;

   CURSOR emp_cur IS
   SELECT *
   FROM emp;

BEGIN
   OPEN emp_cur;
   
   LOOP  
     FETCH emp_cur INTO emp_rec;
     EXIT WHEN emp_cur%NOTFOUND;--这个必须有,否则buffer overflow
     dbms_output.put_line (emp_rec.ename || '  ' ||emp_rec.sal );    
   END LOOP;

   CLOSE emp_cur;
END; 

注：emp_rec必须和emp_cur保持结构一致

2.For Loop

DECLARE
 
 CURSOR emp_cur IS
 SELECT * FROM emp;

BEGIN

 FOR emp_rec in emp_cur LOOP  

   dbms_output.put_line(emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal);  

 END LOOP;

END;

对于FOR LOOP，无需声明一个record或者一个变量来存储Cursor的值，并且无需显示的Open,Fetch,Close游标

3.For Loop

DECLARE 

BEGIN 

 FOR emp_rec in (SELECT * FROM emp) LOOP  

 dbms_output.put_line( emp_rec.empno || ' ' ||