示例4 显式游标的技巧

/*============================================================

                       示例. 显式游标

  ============================================================*/

DECLARE

    name emp.ename%type; 

    sal  emp.sal%type; --定义2个变量来存放ename和sal的内容

    CURSOR emp_cursor IS

SELECT ename,sal FROM emp;

BEGIN

    OPEN emp_cursor;

    LOOP

FETCH emp_cursor INTO name,sal;

        EXIT WHEN emp_cursor%NOTFOUND;       

dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||name|| sal);

    END LOOP;

    CLOSE emp_cursor;

END;

/*============================================================

                       示例. 基于游标定义记录变量

  ============================================================*/

DECLARE

CURSOR emp_cursor IS

select ename,sal FROM emp WHERE deptno=10;

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line(emp_record.ename||':'||emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

/*============================================================

                       示例. 带参数显式游标

  ============================================================*/

DECLARE

CURSOR emp_cursor(no NUMBER) IS

select ename,sal FROM emp WHERE deptno=no;

emp_record emp_cursor%ROWTYPE;

v_sal emp.sal %TYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor(10);

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line(emp_record.ename||':'||emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

/*============================================================

                       示例. 使用显式游标更新或删除

  ============================================================*/

--单表

DECLARE

CURSOR emp_cursor IS

select ename,sal,deptno FROM emp FOR UPDATE;

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 AND emp_record.sal>2500 THEN

DELETE FROM emp WHERE CURRENT OF emp_cursor;

END IF;

END LOOP;

CLOSE emp_cursor;

END;

--多表

DECLARE

CURSOR emp_cursor IS

select ename,sal,emp.deptno,dname 

FROM emp,dept WHERE emp.deptno=dept.deptno 

FOR UPDATE of emp.sal;    --注意：多表时FOR UPDATE OF 后面明确的某个列的作用是，这个列是属于哪个表的则后面通过游标更新或修改的是哪个表。例如，该例中明确的是emp表的sal列，说明下列语句中通过游标更新或修改的表为emp表，如果下列语句中更新的是dept表，则报错。

           

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 THEN

UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;

END IF;

dbms_output.put_line(emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

DECLARE

CURSOR emp_cursor IS

select ename,sal,emp.deptno,dname 

FROM emp,dept WHERE emp.deptno=dept.deptno 

FOR UPDATE of dept.dname;    --该例中明确的是dept表的dname列，说明下列语句中通过游标更新或修改的表为dept表，如果下列语句中更新的是emp表，则报错。

           

emp_record emp_cursor%ROWTYPE;

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

if emp_record.deptno=30 THEN

UPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;

END IF;

dbms_output.put_line(emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

/*============================================================

                       示例. 循环游标

  ============================================================*/

--显示雇员表中所有雇员的姓名和薪水

DECLARE

CURSOR emp_cursor IS

select ename,sal FROM emp;

BEGIN

FOR emp_record IN emp_cursor LOOP

dbms_output.put_line('第'||emp_cursor%ROWCOUNT||'个雇员：'||emp_record.ename|| emp_record.sal);

END LOOP;

END;