使用游标

 

显式游标专门用于处理SELECT语句返回的多行数据。

使用显式游标的步骤：

1，定义游标

CURSOR cursor_name IS select_statment;

2，打开游标

OPEN cursor_name;

3,提取数据

FETCH cursor_name INTO variable1,variable2...

FETCH cursor_name BUIK COLLECT INTO collect1,collect2...

4,CLOSE cursor_name; 

 

/*
  在显式游标中使用FETCH ... INTO
*/

declare
  CURSOR emp_cursor is SELECT ename,sal FROM emp WHERE deptno=10;
  v_ename emp.ename% TYPE;
  v_sal emp.sal% TYPE;
  BEGIN
    OPEN emp_cursor;
    LOOP
      FETCH emp_cursor into v_ename,v_sal;
      EXIT WHEN emp_cursor%NOTFOUND;
      dbms_output.put_line(v_ename||': '||v_sal);
    END LOOP;
    CLOSE emp_cursor;
  END;