sql oracle cursor用法,PL/SQL Cursor常见用法模板

1.LoopDECLARE

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 || \' \' ||emp_rec.ename || \' \' ||emp_rec.sal);

END LOOP;

END;

4.FETCH cursor BULK COLLECT INTO a collection

DECLARE

TYPE NameList IS TABLE OF emp.ename%TYPE;

TYPE SalList IS TABLE OF emp.sal%TYPE;

names NameList;

sals SalList;

CURSOR c1 IS SELECT ename,sal FROM emp WHERE job = \'CLERK\';

l_index NUMBER;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO names,sals;

l_index := names.FIRST;

LOOP

EXIT WHEN l_index IS NULL;

dbms_output.put_line(names(l_index)||\',\'||sals(l_index));

EXIT WHEN l_index = names.LAST;

l_index := names.NEXT(l_index);

END LOOP;

CLOSE c1;

END;The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once.