PL/SQL --> 游标

--==================

-- 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语句操作游标中的当前行时,需要在updatedelete语句中引用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:='SALESMAN';

        NO.     Name       Sal

        1    ALLEN    1600

        2    WARD    1250

        3    MARTIN    1250

        4    TURNER    1500

       

    --例:直接在游标for循环中使用子查询来逐个显示记录

        DECLARE

            v_job emp.job%TYPE;

        BEGIN

            v_job:='&inputjob';

            DBMS_OUTPUT.PUT_LINE('Name     Sal');

            FOR emp_record IN (SELECT ename,sal FROM emp WHERE job=v_job) LOOP

                DBMS_OUTPUT.PUT_LINE(emp_record.ename||'    '

                    ||emp_record.sal);

            END LOOP;

        END;

        /

 

五、参数游标

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

    定义参数游标:

   

        CURSOR cursor_name (para_name1 datatype [,para_name2 datatype ...])

        IS select_statement;

 

    注:datatype 只指定数据类型即可,不能指定参数的长度、精度、刻度

 

    打开参数游标:

        OPEN cursor_name [(vlaues)]

 

        参数个数、类型必须与定义时的形参相匹配。

        对于定义的参数游标,一定要在游标子查询的where子句中指定定义的参数,否则将使得参数游标失去意义

 

    例:用部门编号deptno作形参,显示每个人的姓名和工资

 

    scott@ORCL> get /u01/bk/scripts/emp_cur5.sql

      1  DECLARE

      2      v_deptno emp.deptno%type;

      3      cursor emp_cur(v_deptno emp.deptno%type) is    --定义游标时指定了参数v_deptno及类型

      4     select ename,sal from emp where deptno=v_deptno;   --必须在where子句中指定定义的参数

      5      emp_record emp_cur%rowtype;

      6  BEGIN

      7      v_deptno:=&inputno;

      8      open emp_cur(v_deptno);

      9      loop

     10          fetch emp_cur into emp_record;

     11          exit when emp_cur%notfound;

     12          dbms_output.put_line

     13               ('Employe Name is :'||emp_record.ename||' ,Sal:'||emp_record.sal);

     14      end loop;

     15      close emp_cur;

     16* END;

     17  /

    Enter value for inputno: 10

    old   7:     v_deptno:=&inputno;

    new   7:     v_deptno:=10;

    Employe Name is :CLARK ,Sal:2450

    Employe Name is :KING ,Sal:5000

    Employe Name is :MILLER ,Sal:1300

 

六、游标变量

    简言之,其一是一个游标,其次则是一个变量,因此称之为游标变量,可以用来存储不同的游标

    对于游标变量的使用,在打开游标变量时指定其对应的select语句

    1.游标变量的使用步骤

        a.定义REF CURSOR 类型和游标变量

            TYPE ref_type_name IS REF CURSOR [RETURN return_type];   --必须先定义REF CURSOR类型

            cursor_variable ref_type_name;                           --接下来再定义游标变量

           

            ref_type_name:   指定自定义的类型名

            RETURN:          指定REF CURSOR返回结果的数据类型

            cursor_variable: 定义游标变量的名字

            注:若指定RETURN子句,其数据类型必须是记录类型,此外,不能在包内定义游标变量

       

        b.打开游标

            在打开游标时必须指定其对应的select语句,一旦打开游标变量则对应的select结果集将存放到游标变量中

            OPEN cursor_variable FOR select_statement;

           

        c.提取数据

            提取数据与普通的显示游标提取数据的方法类似

            FETCH cursor_variable INTO variable1,...variable2 ;   --提取单行数据,需要配合循环语句来使用

            FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows];    --提取多行数据,collect为集合变量

           

        d.关闭游标变量

            CLOSE cursor_vairable;

           

    2.游标变量使用的例子

        --.根据部门名称显示该部门的所有雇员(定义REF CURSOR时不指定RETURN子句)

            scott@ORCL> get /u01/bk/scripts/emp_cur12.sql

              1  DECLARE

              2      type emp_cur_type is ref cursor;    --定义游标类型为ref cursor

              3      emp_cur emp_cur_type;               --定义游标变量为emp_cur

              4      emp_record emp%rowtype;             --定义游标变量记录类型为emp_record

              5      v_deptno emp.deptno%type;

              6  BEGIN

              7      v_deptno:=&inputno;

              8      open emp_cur for select * from emp where deptno=v_deptno;

              9      dbms_output.put_line('No    Name');

             10      loop

             11          fetch emp_cur into emp_record;

             12          exit when emp_cur%notfound;

             13          dbms_output.put_line(emp_cur%rowcount||'    '||emp_record.ename);

             14      end loop;

             15      close emp_cur;

             16* END;

             17  /

            Enter value for inputno: 10

            old   7:     v_deptno:=&inputno;

            new   7:     v_deptno:=10;

            No    Name

            1    CLARK

            2    KING

            3    MILLER

 

        --:根据部门名称显示该部门的所有雇员名字及薪水(定义REF CURSOR时指定RETURN子句)

            scott@ORCL> get /u01/bk/scripts/emp_cur13.sql

              1  DECLARE

              2      type emp_record_type is record(name varchar2(10),salary number(6,2));  --定义PL/SQL记录变量类型

              3      type emp_cur_type is ref cursor return emp_record_type;  --定义游标类型为ref cursor,且具有返回类型

              4      emp_cur emp_cur_type;                                    --定义游标变量为emp_cur

              5      emp_record emp_record_type;                              --定义类型为emp_record_type记录变量emp_record

              6      v_deptno emp.deptno%type;

              7  BEGIN

              8      v_deptno:=&inputno;

              9      open emp_cur for select ename,sal from emp where deptno=v_deptno;

             10      dbms_output.put_line('Name    Salary');

             11      loop

             12          fetch emp_cur into emp_record;

             13          exit when emp_cur%notfound;

             14          dbms_output.put_line(emp_record.name||'    '||emp_record.salary);

             15      end loop;

             16      close emp_cur;

             17* END;

             18  /

            Enter value for inputno: 10

            old   8:     v_deptno:=&inputno;

            new   8:     v_deptno:=10;

            Name    Salary

            CLARK    2450

            KING    5000

            MILLER    1300

 

            --如果REF CURSOR指定RETURN子句的数据列于select子句的数据列不一致将收到如下的错误提示信息

            scott@ORCL> start /u01/bk/scripts/emp_cur13.sql

            Enter value for inputno: 10

            old   8:     v_deptno:=&inputno;

            new   8:     v_deptno:=10;

                open emp_cur for select ename,sal,job from emp where deptno=v_deptno;  --多出了一列

                                 *

            ERROR at line 9:

            ORA-06550: line 9, column 22:

            PLS-00382: expression is of wrong type

            ORA-06550: line 9, column 5:

            PL/SQL: SQL Statement ignored          

       

        --例:游标变量的多次使用

            scott@ORCL> get /u01/bk/scripts/emp_cur14.sql

              1  DECLARE

              2      type cur_type is ref cursor;

              3      scott_cur  cur_type;

              4      v_emp  emp%rowtype;

              5      v_dept dept%rowtype;

              6  BEGIN

              7      open scott_cur for select * from emp where deptno=10;       --使用for select首次打开游标

              8      dbms_output.put_line('No, Name');

              9      loop

             10          fetch scott_cur into v_emp;

             11          exit when scott_cur%notfound;

             12          dbms_output.put_line(scott_cur%rowcount||','||v_emp.ename);

             13      end loop;

             14      open scott_cur for select * from dept where deptno=10;--使用for select 再此打开游标,此次加载了不同数据

             15      dbms_output.put_line('Deptno, Name');

             16      loop

             17          fetch scott_cur into v_dept;

             18          exit when scott_cur%notfound;

             19          dbms_output.put_line(v_dept.deptno||','||v_dept.dname);

             20      end loop;

             21* END;

             22  /

 

                No, Name

                1,CLARK

                2,KING

                3,MILLER

                Deptno, Name

                10,ACCOUNTING      

           

七、更多参考           

有关SQL请参考

        SQL 基础--> 子查询

        SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

 

    有关PL/SQL请参考

        PL/SQL --> 语言基础

PL/SQL --> 流程控制

PL/SQL --> 存储过程

PL/SQL --> 函数

PL/SQL --> 游标

PL/SQL -->隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

 

 

  • 6
    点赞
  • 11
    收藏
  • 打赏
    打赏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 3

打赏作者

Leshami

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值