PL/SQL --> 游标

游标的相关概念及特性

定义

映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求 对该行进行相应特定的操作。

游标的基本操作

游标包括两种游标:隐含游标和显式游标.隐含游标用于处理SELECT INTO和DML语句,而显式游标则专门用于处理SELECT语句返回的多行数据.

游标的基本操作有:

声明游标、打开游标、提取游标和关闭游标。

隐式游标：

直接使用读取，声明、打开、关闭都是系统自动进行的

DECLARE
    CURSOR c_emp IS SELECT * FROM EMP; --声明游标
    emp_record emp%ROWTYPE;
BEGIN
    OPEN c_emp; --打开游标
    LOOP
        FETCH c_emp INTO emp_record; --提取游标
        DBMS_OUTPUT.PUT_LINE('雇员名称:'||emp_record.ename);
    EXIT WHEN c_emp%NOTFOUND;
    END LOOP;
    CLOSE c_emp; --关闭游标
END;
/

游标属性

游标作为一个临时表,可以通过游标的属性来获取游标状态.游标有4个常用的属性:

1. %ISOPEN:用于判断游标是否已经打开


2. %FOUND:用于判断游标是否找到记录


3. %NOTFOUND:与%FOUND相反.


4. %ROWCOUNT:返回到当前为止已经提取到的实际行数.

参数化游标

参数游标是指带有参数的游标。当定义了参数游标后，使用不同的参数值多次打开游标则会生成不同的结果集。

定义参数游标：

DECLARE
    CURSOR emp_cursor(dno NUMBER) IS 
    SELECT ename FROM emp WHERE deptno=dno;
    v_ename emp.ename%TYPE;
BEGIN
    OPEN emp_cursor(10);
    LOOP
        FETCH emp_cursor INTO v_ename;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_ename);
    END LOOP;
    CLOSE emp_cursor;
END;
/

隐式游标

隐式游标就是指非PL/SQL程序中定义的,而且是在PL/SQL中使用UPDATE、DELETE或SELECT INTO语句时，Oracle系统自动分配的游标。隐式游标名称固定为SQL。隐式游标无须声明和打开，使用完后也不用关闭，所有这一切都由系统自动维护。

DECLARE
    v_emp emp%ROWTYPE;
BEGIN
    SELECT * INTO v_emp FROM emp WHERE empno=7788;
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('7788的雇员名称：'||v_emp.ename);
    END IF;
END;
/

使用游标更新记录

通过使用显式游标，不仅可以一行一行地处理SELECT语句的结果，而且也可以更新或删除当前游标行的数据。在使用游标更新或删除数据时有两点需要注意的地方：

1. 声明游标是必须带有FOR UPDATE子句，如：
   
   
   

   CURSOR cursor_name IS SELECT ... FOR UPDATE;

   
   


2. 在提取了游标数据之后，为了更新或删除当前游标行数据，必须在UPDATE或DELETE语句中引用WHERE CURRENT OF子句。如:
   
   
   

   UPDATE table_name SET column=... WHERE CURRENT OF cursor_name;
   DELETE table_name WHERE CURRENT OF cursor_name;

   
   

--例：使用游标修改所有记录的工资，根据JOB来作不同的修改。

DECLARE
v_job tb_emp.job%TYPE;
CURSOR emp_cur IS SELECT job FROM tb_emp FOR UPDATE;  --定义时，使用FOR UPDATE
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_job;
EXIT WHEN emp_cur%NOTFOUND;
CASE  
    WHEN v_job='CLERK' THEN
    UPDATE tb_emp SET sal=sal*1.1 WHERE CURRENT OF emp_cur;  --注意，需要使用WHERE CURRENT OF
    WHEN v_job='SALESMAN' THEN
    UPDATE tb_emp SET sal=sal*1.08 WHERE CURRENT OF emp_cur;
    ELSE
        UPDATE tb_emp SET sal=sal*1.05 WHERE CURRENT OF emp_cur;
END CASE;
END LOOP;
CLOSE emp_cur;
END;

--例：利用游标删除数据

DECLARE
 v_job tb_emp.job%type;
 v_sal tb_emp.sal%type;
cursor emp_cur is select job,sal from tb_emp for update;
BEGIN
open emp_cur;
fetch emp_cur into v_job,v_sal;
while emp_cur%found
loop
   if v_sal>3000 then
    delete from tb_emp where current of emp_cur;
   end if;
   fetch emp_cur into v_job,v_sal;
end loop;
close emp_cur;
END;

游标FOR循环

游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标的关闭都是ORACLE系统自动进行的。

DECLARE
    CURSOR emp_cursor IS SELECT * FROM emp;
    v_emp emp%ROWTYPE;
BEGIN
    FOR v_emp IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT||'个雇员'||v_emp.ename);
    END LOOP;
END;
/

更多参考：

PL/SQL-->基础知识篇

PL/SQL-->存储过程