oracle中的游标的原理和使用详解

from:https://www.2cto.com/database/201501/371435.html

游标 游标的简介：

逐行处理查询结果，以编程的方式访问数据

游标的类型：

1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql。

2，显式游标：显式游标用于处理返回多行的查询。

3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

隐式游标：

---

q在PL/SQL中使用DML语句时自动创建隐式游标 q隐式游标自动声明、打开和关闭，其名为 SQL q通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 q隐式游标的属性有： q%FOUND – SQL 语句影响了一行或多行时为 TRUE q%NOTFOUND – SQL 语句没有影响任何行时为TRUE q%ROWCOUNT – SQL 语句影响的行数 q%ISOPEN - 游标是否打开，始终为FALSE

? 1 2 3 4 5 6 7 8 begin    update student s set s.sage = s.sage + 10 ;    if sql %FOUND   then      dbms_output.put_line( '这次更新了' || sql% rowcount );      else        dbms_output.put_line ( '一行也没有更新' );        end if ;        end;

在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SQL> declare    2  sname1 student.sname%TYPE;    3  begin    4    select sname into sname1 from student;    5    if sql%found then    6      dbms_output.put_line(sql%rowcount);    7    else    8      dbms_output.put_line( '没有找到数据' );    9      end if ;   10      exception   11        when too_many_rows then   12          dbms_output.put_line( '查找的行记录多于1行' );   13         when no_data_found then   14            dbms_output.put_line( '未找到匹配的行' );   15       end;   16  / 查找的行记录多于 1 行 PL/SQL procedure successfully completed   SQL>

显式游标：

---

sqlserver与oracle的不同之处在于： 最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步： 声明游标、打开游标、使用游标读取记录、关闭游标。
显式游标的使用：

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 ------------------------------------无参数游标------------------------------- declare    sname varchar2( 20 ); --声明变量    cursor student_cursor is select sname from student ; --声明游标    begin      open student_cursor;--打开游标        fetch student_cursor into sname ;--让游标指针往下移动      while student_cursor%found --判断游标指针是否指向某行记录        loop--遍历          dbms_output.put_line ( '学生姓名' ||sname );          fetch student_cursor into sname;        end loop;         close student_cursor;        end;        ------------------------------------有参数游标------------------------------- declare sname student.sname%type; sno student.sno%type; cursor student_cursor (input_sno number) is select s.sname, s.sno from student s where s.sno > input_sno; --声明带参数的游标 begin    sno := &请输入学号 ;--要求从客户端输入参数值， "&" 相当于占位符；    open student_cursor( sno); --打开游标，并且传递参数    fetch student_cursor into sname, sno; --移动游标    while student_cursor% found      loop        dbms_output.put_line ( '学号为：' ||sno || '姓名为：' ||sname );        fetch student_cursor into sname,sno;        end loop;         close student_cursor;        end; ------------------------------------循环游标-------------------------------    -- Created on 18 - 1 月- 15 by 永文 declare stu1 student%rowtype ;--这里也不需要定义变量来接收fetch到的值 cursor student_cursor is select * from student ; begin   open student_cursor; --这里不需要开启游标    for stu1 in student_cursor      loop        dbms_output.put_line ( '学生学号：' ||stu1.sno || '学生姓名：' ||stu1.sname );        fetch student_cursor into stu1;--也不需要fetch了        end loop;      close student_cursor;  --这里也不需要关闭游标        end;        ------------------------------------使用游标更新行-------------------------------      declare    stu1 student%rowtype ;    cursor student_cursor is select * from student s where s.sno in ( 2 , 3 ) for update;--创建更新游标    begin     open student_cursor;     fetch student_cursor into stu1;--移动游标     while student_cursor%found --遍历游标，判断是否指向某个值       loop         update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据          fetch student_cursor into stu1;--移动游标         end loop;         close student_cursor;         end;      declare    stu1 student%rowtype ;    cursor student_cursor is select * from student s where s.sno in ( 2 , 3 ) for update;--创建更新游标    begin     open student_cursor;    -- fetch student_cursor into stu1;--移动游标    -- while student_cursor%found--遍历游标，判断是否指向某个值       loop           fetch student_cursor into stu1 ;--移动游标           exit when student_cursor %notfound ;         update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据         end loop;         close student_cursor;         end; ------------------------------------使用fetch ... bulk collect into-------------------------------   declare    cursor   my_cursor is select ename from emp where deptno= 10 ; --声明游标    type   ename_table_type is table of varchar2 ( 10 );--定义一种表类型，表中的属性列为varchar2类型    ename_table  ename_table_type;--通过上面定义的类型来定义变量 begin    open   my_cursor; --打开游标    fetch my_cursor bulk collect into   ename_table; --移动游标    for   i in 1 ..ename_table.count  loop       dbms_output.put_line(ename_table(i));    end   loop ;    close my_cursor; end;     -----------------------------------显示游标题目--------------------------------------   SQL > select * from student ;          XH XM ---------- ----------           1 A           2 B           3 C           4 D   SQL > select * from address ;          XH ZZ ---------- ----------           2 郑州           1 开封           3 洛阳           4 新乡           完成的任务 :给表student添加一列zz ,是varchar2 ( 10 )类型； 再从address中，将zz字段的数值取出来，对应的插入到 student新增的zz列中。 即：得到的结果：student表中，是：            XH XM         ZZ           -- ---------- ------            1 A          开封            2 B          郑州            3 C          洛阳            4 D          新乡     declare stu1 student %rowtype ; add1 address %rowtype ; cursor student_cursor is select * from student for update;--声明更新游标 cursor address_cursor is select * from address ;--声明游标 begin    open student_cursor ;--打开游标    fetch student_cursor into stu1;--移动游标    while student_cursor% found--判断游标是否指向某条记录      loop        open address_cursor ;--打开另外一个游标        fetch address_cursor into add1 ;--移动游标        while address_cursor %found--判断游标是否指向某条记录          loop            if add1.xh = stu1.xh then--判断两个游标所指向的记录中xh的值是否相等              update student s set s.zz = add1.zz where current of student_cursor;--假如相等就更新游标所指向的记录值              end if ;              fetch address_cursor into add1 ;--移动游标              end loop;              close address_cursor ;--关闭游标              fetch student_cursor into stu1 ;--移动游标              end loop;              close student_cursor ;--关闭游标              end;

REF游标也叫动态游标：

---

qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤： q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明 REF 游标类型的语法为：

TYPE <ref_cursor_name> IS REF CURSOR

[RETURN <return_type>];

? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 -----------------------------------ref游标--------------------------------- declare type ref_cursor  is ref cursor; --声明一个ref游标类型 tab_cursor ref_cursor ;--声明一个ref游标 sname student.xm %type ; sno student.xh %type ; tab_name varchar2 ( 20 ); begin    tab_name := '&tab_name' ; --接收客户输入的表明    if tab_name = 'student' then      open tab_cursor for select xh ,xm  from student ; --打开ref游标      fetch tab_cursor into sno ,sname ;--移动游标      while tab_cursor %found        loop          dbms_output.put_line ( '学号：' ||sno || '姓名：' ||sname );          fetch tab_cursor into sno ,sname ;          end loop;          close tab_cursor ;          else            dbms_output.put_line ( '没有找到你想要找的表数据信息' );            end if ;          end;      -----------------------------------ref游标题目--------------------------------- SQL > select * from student ;          XH KC ---------- ----------           1 语文           1 数学           1 英语           1 历史           2 语文           2 数学           2 英语           3 语文           3 英语 9 rows selected   SQL >       完成的任务 : 生成student2表 (xh number, kc  varchar2 ( 50 ))； 对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。 即，student2中的结果如下：                        XH KC                   --- -------------------------------------------                         1 语文数学英语历史                         2 语文数学英语                         3 语文英语   create table student2 (xh number, kc varchar2 ( 50 ));   declare kcs varchar2 ( 50 ); kc varchar2 ( 50 ); type ref_cursor is ref cursor; --声明一个ref游标类型 stu_cursor ref_cursor ;--定义一个ref游标类型的变量 type tab_type is table of number; --声明一个table类型 tab_xh tab_type ;--定义一个表类型的变量 cursor cursor_xh is select distinct( xh) from student; --声明一个游标 begin    open cursor_xh; --打开游标    fetch cursor_xh bulk collect into tab_xh; --提取数据到表中    for i in 1 .. tab_xh.count      loop        kcs := '' ;        open stu_cursor for select kc from student s where s.xh = tab_xh(i ); --打开ref游标        fetch stu_cursor into kc ; --移动游标        while stu_cursor %found          loop            kcs := kc ||kcs ; --连接字符串使用||而不是+            fetch stu_cursor into kc ; --移动游标            end loop;            insert into student2 (xh , kc ) values( i, kcs);            close stu_cursor ;        end loop;        close cursor_xh ;        end;