游标、游标及三种循环方式笔记

SET ServerOutput ON;
Declare
    Cursor myCur is select empno,ename,sal from emp;
    vna varchar2(10);
    vno number(4);
    vsal number(7,2);
 Begin
    open myCur;
    fetch myCur into vno,vna,vsal;
    dbms_output.put_line(vno||'    '||vna||'    '||vsal);
    close myCur;
 End;

利用简单循环统计并输出各个部门的平均工资。
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal   FROM emp GROUP BY deptno;
  v_dept c_dept_stat%ROWTYPE;
BEGIN
OPEN c_dept_stat;
LOOP
    FETCH c_dept_stat INTO v_dept;

    EXIT WHEN c_dept_stat%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
  END LOOP;

  CLOSE c_dept_stat;
END;

DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
  v_dept c_dept_stat%ROWTYPE;
BEGIN
  OPEN c_dept_stat;
  FETCH c_dept_stat INTO v_dept;
  WHILE c_dept_stat%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
    FETCH c_dept_stat INTO v_dept;
  END LOOP;
  CLOSE c_dept_stat;
END;

利用FOR循环统计并输出各个部门的平均工资。
DECLARE
  CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno;
BEGIN
  FOR v_dept IN c_dept_stat LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_dept.avgsal);
  END LOOP;
END;

【游标及三种循环方式】

 DECLARE
   CURSOR c_emp IS SELECT * FROM emp  ;
   v_emp c_emp%ROWTYPE;
 BEGIN
   OPEN c_emp;

   LOOP
      FETCH c_emp INTO v_emp;
      EXIT WHEN c_emp%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
 END LOOP;
 CLOSE c_emp;
 END;

 DECLARE
   CURSOR c_emp IS SELECT * FROM emp  ;
   v_emp c_emp%ROWTYPE;
 BEGIN
   OPEN c_emp;
   FETCH c_emp INTO v_emp;

   while  c_emp%FOUND loop
       DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
       FETCH c_emp INTO v_emp;
   END LOOP;

 CLOSE c_emp;
 END;

 

 DECLARE
   CURSOR c_emp IS SELECT * FROM emp  ;
   v_emp c_emp%ROWTYPE;
 BEGIN
    for v_emp in c_emp loop
       DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);
    end loop;
 END;