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;
*/