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;
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;
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;
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;
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.