Orcale 游标的实例

  DECLARE
        CURSOR dept_cur
        IS SELECT deptno FROM dept ORDER BY deptno ASC;
        CURSOR emp_cur (P_deptno dept.deptno%TYPE)
        IS SELECT sal FROM emp WHERE deptno=p_deptno ORDER BY sal DESC;
        o_sal emp_cur%ROWTYPE;
        o_sal_temp emp.sal%TYPE;
        o_num NUMBER(2,0):=1;
        o_deptno dept.deptno%TYPE;
  BEGIN
        OPEN dept_cur;
        LOOP
           FETCH dept_cur INTO o_deptno;
                  EXIT WHEN dept_cur%NOTFOUND;
                     dbms_output.put_line('编号' || o_deptno);
                            OPEN emp_cur(o_deptno);
                            FETCH emp_cur INTO o_sal.sal;
                            o_sal_temp:=o_sal.sal;
                            dbms_output.put_line(o_num ||' '|| o_sal.sal);
                            FETCH emp_cur INTO o_sal.sal;
                            WHILE emp_cur%FOUND
                            LOOP
                                 IF o_sal.sal=o_sal_temp THEN
                                     dbms_output.put_line(o_num ||' '|| o_sal.sal);
                                 ELSE
                                      o_num:=o_num+1;
                                      dbms_output.put_line(o_num ||' '|| o_sal.sal);
                                 END IF;
                                 FETCH emp_cur INTO o_sal.sal;
                           END LOOP;
                           CLOSE emp_cur;
                           o_num:=1;
         END LOOP;
               CLOSE dept_cur;
   END;