ORACLE游标笔记

发下ORACLE游标使用的一些笔记
--简单循环，取一条数据
/*
DECLARE
CURSOR C IS SELECT * FROM EMP;
V_EMP C%ROWTYPE;
BEGIN
      OPEN C;
      FETCH C INTO V_EMP;
      DBMS_OUTPUT.put_line(V_EMP.ENAME);
      CLOSE C;
END;
*/


--WHEN循环
/**
DECLARE
CURSOR C IS SELECT * FROM EMP;
V_EMP C%ROWTYPE;
BEGIN
      OPEN C;
      LOOP
        FETCH C INTO V_EMP;
        EXIT WHEN (C%NOTFOUND);
        DBMS_OUTPUT.put_line(V_EMP.ENAME);
      END LOOP;
      CLOSE C;
END;
*/

--WHILE循环
/*
DECLARE
CURSOR C IS SELECT * FROM EMP;
V_EMP C%ROWTYPE;
BEGIN
      OPEN C;
      FETCH C INTO V_EMP;
           WHILE (C%FOUND) LOOP
                 DBMS_OUTPUT.put_line(V_EMP.ENAME);
                 FETCH C INTO V_EMP;
           END LOOP;
      CLOSE C;
END;
*/

--FOR循环，最简单最实用的循环
/*
DECLARE
CURSOR C IS SELECT * FROM EMP;
V_EMP C%ROWTYPE;
BEGIN
      FOR V_EMP IN C LOOP
          DBMS_OUTPUT.put_line(V_EMP.ENAME);
      END LOOP;    
END;
*/

--带参数据的游标
/*
DECLARE
CURSOR C(V_DEPTNO EMP.DEPTNO%TYPE,V_JOB EMP.JOB%TYPE)
IS SELECT * FROM EMP WHERE DEPTNO =V_DEPTNO AND JOB=V_JOB;
V_EMP C%ROWTYPE;
BEGIN
     FOR V_EMP IN C('2','JOB2') LOOP
         DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
     END LOOP;
END;
*/


--ALTER TABLE EMP ADD SAL VARCHAR2(40)

--可更新的游标
/*
DECLARE
CURSOR C
IS SELECT * FROM EMP FOR UPDATE;
V_EMP C%ROWTYPE;
BEGIN
     FOR V_EMP IN C LOOP
         IF(V_EMP.SAL<2000) THEN
             UPDATE EMP SET SAL = SAL*2 WHERE CURRENT OF C;
         ELSIF(V_EMP.SAL =2000) THEN
             DELETE FROM EMP WHERE CURRENT OF C;
         END IF;
     END LOOP;
     COMMIT;
END;
*/