Orcale 游标的实例

最新推荐文章于 2012-01-14 23:48:16 发布

给力地板油

最新推荐文章于 2012-01-14 23:48:16 发布

阅读量1.2k

点赞数

分类专栏：技术类

本文链接：https://blog.csdn.net/wslyy99/article/details/1634116

版权

技术类专栏收录该内容

107 篇文章 0 订阅

订阅专栏

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;