学步园

--==================
-- PL/SQL --> 游标
--==================
 
一、游标的相关概念及特性
1.定义
        映射在结果集中某一行数据的具体位置，类似于C语言中的指针。即通过游标方式定位到结果集中某个特定的行，然后根据业务需求
        对该行进行相应特定的操作。

2.游标的分类
        显示游标:即用户自定义游标，专门用于处理select语句返回的多行数据
        隐式游标:系统自动定义的游标，记录集只有单行数据，用于处理select into 和DML语句

3.游标使用的一般过程：
        显示游标：声明, 打开, 读取, 关闭
        隐式游标：直接使用读取，声明、打开、关闭都是系统自动进行的

4.显示游标的过程描述
        a.声明游标
CURSOR cursor_name IS select_statement
            如：CURSOR emp_cur IS SELECT empno,ename,job,sal FROM scott.emp;

        b.打开游标
OPEN cursor_name     --打开游标则执行对应的select语句，将对应的结果集存放到游标当中
            如：OPEN emp_cur

        c.读取数据
FETCH cursor_name INTO var_name1,...var_name2 ;   --提取单行数据，需要配合循环语句来使用
            FETCH cursor_name BULK COLLECT INTO collect1,collect2,...[LIMIT rows];    --提取多行数据，collect为集合变量
           
        d.关闭游标
CLOSE cursor_name

5.显示游标的个属性
        cursor_name%ISOPEN      游标是否打开   
        cursor_name%FOUND       最近的FETCH是否提取到数据
        cursor_name%NOTFOUND       最近的FETCH是否没有提取到数据
        cursor_name%ROWCOUNT       返回到目前为止，已经从游标缓冲区中提取到数据的行数

二、显示游标应用示例
--例：浏览数据，输入职位，查看每个人工资(使用fetch cursor_name into来提取单行记录)
 
        scott@ORCL> get /u01/bk/scripts/emp_cur1.sql
1  DECLARE
2      v_name emp.ename%TYPE;       --定义用于存放游标提取的数据的变量
          3      v_job emp.job%TYPE;
4      v_sal emp.sal%TYPE;
5      CURSOR emp_cur IS select ename,sal FROM emp WHERE job=v_job;
6  BEGIN
7      v_job:='&inputjob';
8      OPEN emp_cur;
9      DBMS_OUTPUT.PUT_LINE('Name     Sal');
10      LOOP
11         FETCH emp_cur INTO v_name,v_sal;
12         EXIT WHEN emp_cur%NOTFOUND;
13         DBMS_OUTPUT.PUT_LINE(v_name||'    '||v_sal);
14      END LOOP;
15      CLOSE emp_cur;
16* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur1.sql
        Enter value for inputjob: CLERK
        old   7:     v_job:='&inputjob';
        new   7:     v_job:='CLERK';
        Name     Sal
        SMITH    800
        ADAMS    1100
        JAMES    950
        MILLER    1300

        PL/SQL procedure successfully completed.

--例：定义一个游标，输入部门号时，则显示该部门所有成员的名字(使用fetch cursor_name bulk collect into提取所有数据)
        scott@ORCL> get /u01/bk/scripts/emp_cur2.sql
1  DECLARE
2      v_deptno emp.deptno%type;
3      type ename_table_type is table of varchar2(10);    --定义PL/SQL表类型
          4      ename_table ename_table_type;                      --定义PL/SQL表变量存放游标数据
          5      cursor emp_cur is
6      select ename from emp where deptno=v_deptno;
7  BEGIN
8      v_deptno:=&inputno;
9      open emp_cur;
10      fetch emp_cur bulk collect into ename_table;       --使用bulk collect into提取所有数据
         11      for i in 1..ename_table.count
12          loop
13              dbms_output.put_line(ename_table(i));
14          end loop;
15      close emp_cur;
16* END;      

        scott@ORCL> start /u01/bk/scripts/emp_cur2.sql
        Enter value for inputno: 10
        old   8:     v_deptno:=&inputno;
        new   8:     v_deptno:=10;
        CLARK
        KING
        MILLER

--例：游标属性使用示例(使用%isopen和%rowcount属性)
        scott@ORCL> get /u01/bk/scripts/emp_cur3.sql
1  DECLARE
2      v_deptno emp.deptno%type;
3      type ename_table_type is table of varchar2(10);
4      ename_table ename_table_type;
5      cursor emp_cur is
6     select ename from emp where deptno=v_deptno;
7  BEGIN
8      v_deptno:=&inputno;
9      if not emp_cur%isopen then       --判断游标是否打开，如未打开，则打开游标
         10          open emp_cur;
11      end if;
12      fetch emp_cur bulk collect into ename_table;
13      dbms_output.put_line
14          ('All record counts from cursor is : '||emp_cur%rowcount);  --使用cursor_name%rowcount 统计游标的记录数
         15      close emp_cur;
16* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur3.sql
        Enter value for inputno: 20
        old   8:     v_deptno:=&inputno;
        new   8:     v_deptno:=20;
All record counts from cursor is : 5

--例:基于游标定义记录变量(该方式大大简化了所需要定义的变量个数)
        scott@ORCL> get /u01/bk/scripts/emp_cur4.sql
1  DECLARE
2      cursor emp_cur is select ename,sal from emp;
3      emp_record emp_cur%rowtype;          --定义游标类型记录变量
          4  BEGIN
5      open emp_cur;
6      loop
7          fetch emp_cur into emp_record;
8          exit when emp_cur%notfound;
9          dbms_output.put_line
10             ('Employee Name : '||emp_record.ename ||' ,Sal: '||emp_record.sal);
11      end loop;
12      close emp_cur;
13* END;

        scott@ORCL> start /u01/bk/scripts/emp_cur4.sql
        Employee Name : SMITH ,Sal: 800
        Employee Name : ALLEN ,Sal: 1600
        Employee Name : WARD ,Sal: 1250
                 .......

三、使用游标更新记录       
    通过游标既可以逐行检索结果集中的记录，又可以更新或删除当前游标行的数据
    如果要通过游标更新和删除数据，在定义游标时必须要带有FOR UPDATE子句
        格式： 
CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]

FOR UPDATE ：子句用于在游标结果集数据上加行共享锁，以防止其它用户在相应行上执行DML操作
OF ：子句用于游标子查询到多张表时来确定哪些表要加锁，如未指定，则select语句所引用的全部表将被加锁
        NOWAIT ：子句指定不等待锁
        使用DML语句操作游标中的当前行时，需要在update或delete语句中引用where current of子句
UPDATE tbname set col1=.. WHERE CURRENT OF cursor_name;
DELETE tbname  WHERE CURRENT OF cursor_name;
--例：使用游标修改所有记录的工资，根据JOB来作不同的修改。
 
            scott@ORCL> create table tb_emp as select * from emp;
            scott@ORCL> get /u01/bk/scripts/emp_cur6.sql
1  DECLARE
2      v_job tb_emp.job%TYPE;
3      CURSOR emp_cur IS SELECT job FROM tb_emp FOR UPDATE;  --定义时，使用FOR UPDATE
              4  BEGIN
5      OPEN emp_cur;
6      LOOP
7          FETCH emp_cur INTO v_job;
8          EXIT WHEN emp_cur%NOTFOUND;
9          CASE  
10              WHEN v_job='CLERK' THEN
11                  UPDATE tb_emp SET sal=sal*1.1 WHERE CURRENT OF emp_cur;  --注意，需要使用WHERE CURRENT OF
             12              WHEN v_job='SALESMAN' THEN
13                  UPDATE tb_emp SET sal=sal*1.08 WHERE CURRENT OF emp_cur;
14              ELSE
15                  UPDATE tb_emp SET sal=sal*1.05 WHERE CURRENT OF emp_cur;
16          END CASE;
17      END LOOP;
18      CLOSE emp_cur;
19* END;
--例：利用游标删除数据
            scott@ORCL> get /u01/bk/scripts/emp_cur7.sql
1  DECLARE
2      v_job tb_emp.job%type;
3      v_sal tb_emp.sal%type;
4      cursor emp_cur is select job,sal from tb_emp for update;
5  BEGIN
6      open emp_cur;
7      fetch emp_cur into v_job,v_sal;
8      while emp_cur%found
9          loop
10              if v_sal>3000 then
11                  delete from tb_emp where current of emp_cur;
12              end if;
13              fetch emp_cur into v_job,v_sal;
14          end loop;
15      close emp_cur;
16* END;
17  /
--例：使用OF子句对特定的表加共享锁
            scott@ORCL> get /u01/bk/scripts/emp_cur8.sql
1  DECLARE
2      cursor emp_cur is
3          select ename,sal,dname,e.deptno
4          from tb_emp e join dept d
5              on e.deptno=d.deptno for update of e.deptno;
6      emp_record emp_cur%rowtype;
7  BEGIN
8      open emp_cur;
9      loop
10          fetch emp_cur into emp_record;
11          exit when emp_cur%notfound;
12          if emp_record.deptno=20 then
13              update tb_emp set sal=sal+100 where current of emp_cur;
14          end if;
15          dbms_output.put_line('Ename: '||emp_record.ename||
16                               ',Sal: '||emp_record.sal||
17                               ',Deptname:'||emp_record.dname);
18      end loop;
19      close emp_cur;
20* END;
21  /
            Ename: SMITH,Sal: 880,Deptname:RESEARCH
            Ename: ALLEN,Sal: 1728,Deptname:SALES
                       ........
--例:NOWAIT子句的使用  
            scott@ORCL> get /u01/bk/scripts/emp_cur9.sql
1  DECLARE
2      v_ename tb_emp.ename%type;
3      v_oldsal tb_emp.sal%type;
4      cursor emp_cur is
5          select ename,sal from tb_emp for update nowait;  --使用nowait子句指定不等待锁，会给出错误提示
              6  BEGIN
7      open emp_cur;
8      loop
9          fetch emp_cur into v_ename,v_oldsal;
10          exit when emp_cur%notfound;
11          if v_oldsal<2000 then
12              update tb_emp set sal=sal+200 where current of emp_cur;
13          end if;
14      end loop;
15      close emp_cur;
16* END;
            scott@ORCL> start /u01/bk/scripts/emp_cur9.sql
DECLARE
*
            ERROR at line 1:
            ORA-00054: resource busy and acquire with NOWAIT specified
            ORA-06512: at line 5
            ORA-06512: at line 7
四、游标FOR循环
    游标FOR循环是为了简化游标使用过程而设计的。使用游标FOR循环检索游标时，游标的打开、数据提取、数据是否检索到的判断与游标
    的关闭都是ORACLE系统自动进行的。
    游标FOR循环两种语句格式：
        格式一：
            先在定义部分定义游标，然后在游标FOR循环中引用该游标
FOR record_name IN cursor_name LOOP
                statement1;
                statement2;
END LOOP;
        格式二：
            在FOR循环中直接引用子查询，隐式定义游标
FOR record_name IN subquery LOOP
                statement;
END LOOP;
--例：定义游标并使用for循环逐个显示记录
   
DECLARE
            v_job emp.job%TYPE;
CURSOR emp_cur IS SELECT ename,sal FROM emp WHERE job=v_job;
BEGIN
            v_job:='&inputjob';
            DBMS_OUTPUT.PUT_LINE('NO.     Name       Sal');
FOR emp_record IN emp_cur LOOP
                DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT||'    '||
                emp_record.ename||'    '||emp_record.sal);
END LOOP;
END;
/
        scott@ORCL> start /u01/bk/scripts/emp_cur10.sql
        Enter value for inputjob: SALESMAN
        old   5:                        v_job:='&inputjob';
        new   5:                        v_job:='











														
							

返回