首页 > 代码库 > oracle游标的使用（二）

oracle游标的使用（二）

2024-09-28 16:57:39   219人阅读

 

技术分享

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

游标的类型：

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

2、显式游标：显式游标用于处理返回多行的查询。
3、REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果
隐式游标：
在PL/SQL中使用DML语句时自动创建隐式游标，隐式游标自动声明、打开和关闭，其名为 SQL，通过检查隐式游标的属性可以获得最近执行的 DML 语句的信息，隐式游标的属性有： %FOUND – SQL 语句影响了一行或多行时为 TRUE，%NOTFOUND – SQL 语句没有影响任何行时为TRUE，%ROWCOUNT – SQL 语句影响的行数，%ISOPEN - 游标是否打开，始终为FALSE

 

[sql] view plain copy
 
  1. BEGIN  
  2.   UPDATE t_bjqk SET fBL = fBL - 2 WHERE fBJDM=‘1461‘;  
  3.   IF SQL%FOUND THEN  
  4.     dbms_output.put_line(‘这次更新了‘ || SQL%ROWCOUNT);  
  5.   ELSE  
  6.     dbms_output.put_line(‘一行也没有更新‘ );  
  7.   END IF;  
  8. END;  
  9. --SELECT * FROM t_bjqk;  
[sql] view plain copy
 
  1. 在select中有两个中比较常见的异常： 1. NO_DATA_FOUND 2. TOO_MANY_ROWS  
  2. SQL> declare  
  3.   2  sname1 student.sname%TYPE;  
  4.   3  begin  
  5.   4    select sname into sname1 from student;  
  6.   5    if sql%found then  
  7.   6      dbms_output.put_line(sql%rowcount);  
  8.   7    else  
  9.   8      dbms_output.put_line(‘没有找到数据‘);  
  10.   9      end if;  
  11.  10      exception  
  12.  11        when too_many_rows then  
  13.  12          dbms_output.put_line(‘查找的行记录多于1行‘);  
  14.  13         when no_data_found then  
  15.  14            dbms_output.put_line(‘未找到匹配的行‘);  
  16.  15       end;  
  17.  16  /  
  18. 查找的行记录多于1行  
  19. PL/SQL procedure successfully completed  
  20.    
  21. SQL>  

显式游标：

 

技术分享
sqlserver与oracle的不同之处在于： 最后sqlserver会deallocate 丢弃游标，而oracle只有前面四步： 声明游标、打开游标、使用游标读取记录、关闭游标。
显式游标的使用：
[sql] view plain copy
 
  1. declare  
  2.   sname varchar2( 20); --声明变量  
  3.   cursor student_cursor is select sname from student ; --声明游标  
  4. begin  
  5.   open student_cursor;                 --打开游标  
  6.   fetch student_cursor into sname;     --让游标指针往下移动  
  7.   while student_cursor%found           --判断游标指针是否指向某行记录  
  8.   loop --遍历  
  9.     dbms_output.put_line (‘学生姓名‘ ||sname );  
  10.     fetch student_cursor into sname;  
  11.   end loop;  
  12.   close student_cursor;  
  13. end;  
  14.         
  15. ------------------------------------有参数游标-------------------------------  
  16. declare  
  17.   sname student.sname%type;  
  18.   sno student.sno%type;  
  19.   cursor student_cursor (input_sno number) is   
  20.   select s.sname, s.sno from student s where s.sno > input_sno; --声明带参数的游标  
  21. begin  
  22.   sno := &请输入学号;                     --要求从客户端输入参数值，"&"相当于占位符；  
  23.   open student_cursor( sno);             --打开游标，并且传递参数  
  24.   fetch student_cursor into sname, sno;  --移动游标  
  25.   while student_cursor% found  
  26.   loop  
  27.     dbms_output.put_line (‘学号为：‘ ||sno ||‘姓名为：‘ ||sname );  
  28.     fetch student_cursor into sname,sno;  
  29.   end loop;  
  30.   close student_cursor;  
  31. end;  
  32.   
  33.   
  34. ------------------------------------循环游标-------------------------------      
  35. -- Created on 18-1月-15 by 永文  
  36. declare  
  37.   stu1 student%rowtype ;       --这里也不需要定义变量来接收fetch到的值  
  38.   cursor student_cursor is select * from student ;  
  39. begin  
  40.   open student_cursor;         --这里不需要开启游标  
  41.   for stu1 in student_cursor  
  42.   loop  
  43.     dbms_output.put_line (‘学生学号：‘ ||stu1.sno ||‘学生姓名：‘ ||stu1.sname );  
  44.     fetch student_cursor into stu1;   --也不需要fetch了  
  45.   end loop;  
  46.   close student_cursor;               --这里也不需要关闭游标  
  47. end;  
  48.   
  49.   
  50. ------------------------------------使用游标更新行-------------------------------     
  51. declare  
  52. stu1 student%rowtype ;  
  53. cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标  
  54. begin  
  55.   open student_cursor;  
  56.   fetch student_cursor into stu1;--移动游标  
  57.   while student_cursor%found --遍历游标，判断是否指向某个值  
  58.   loop  
  59.     update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据  
  60.     fetch student_cursor into stu1;--移动游标  
  61.   end loop;  
  62.   close student_cursor;  
  63. end;  
  64.    
  65.   
  66. declare  
  67.   stu1 student%rowtype ;  
  68.   cursor student_cursor is select * from student s where s.sno in (2 ,3 ) for update;--创建更新游标  
  69. begin  
  70.   open student_cursor;  
  71.   -- fetch student_cursor into stu1;--移动游标  
  72.   -- while student_cursor%found--遍历游标，判断是否指向某个值  
  73.   loop  
  74.     fetch student_cursor into stu1 ;--移动游标  
  75.     exit when student_cursor %notfound ;  
  76.     update student set sage = sage + 10 where current of student_cursor;--通过游标中的信息更新数据  
  77.   end loop;  
  78.   close student_cursor;  
  79. end;  
  80.   
  81.   
  82. ------------------------------------使用fetch ... bulk collect into-------------------------------     
  83. declare  
  84.   cursor   my_cursor is select ename from emp where deptno= 10; --声明游标  
  85.   type   ename_table_type is table of varchar2 (10 );--定义一种表类型，表中的属性列为varchar2类型  
  86.   ename_table  ename_table_type;--通过上面定义的类型来定义变量  
  87. begin  
  88.   open   my_cursor; --打开游标  
  89.   fetch my_cursor bulk collect into   ename_table; --移动游标  
  90.   for   i in 1 ..ename_table.count  loop  
  91.      dbms_output.put_line(ename_table(i));  
  92.   end   loop ;  
  93.   close my_cursor;  
  94. end;  
  95.    
  96.    
  97. -----------------------------------显示游标题目--------------------------------------  
  98.    
  99. SQL > select * from student ;  
  100.         XH XM  
  101. ---------- ----------  
  102.          1 A  
  103.          2 B  
  104.          3 C  
  105.          4 D  
  106.    
  107. SQL > select * from address ;  
  108.         XH ZZ  
  109. ---------- ----------  
  110.          2 郑州  
  111.          1 开封  
  112.          3 洛阳  
  113.          4 新乡  
  114.            
  115. 完成的任务 :给表student添加一列zz ,是varchar2 (10 )类型；  
  116. 再从address中，将zz字段的数值取出来，对应的插入到  
  117. student新增的zz列中。  
  118. 即：得到的结果：student表中，是：  
  119.           XH XM         ZZ  
  120.          -- ---------- ------  
  121.           1 A          开封  
  122.           2 B          郑州  
  123.           3 C          洛阳  
  124.           4 D          新乡  
  125.    
  126.    
  127. declare  
  128.   stu1 student %rowtype ;  
  129.   add1 address %rowtype ;  
  130.   cursor student_cursor is select * from student for update; --声明更新游标  
  131.   cursor address_cursor is select * from address ;           --声明游标  
  132. begin  
  133.   open student_cursor ;                --打开游标  
  134.   fetch student_cursor into stu1;      --移动游标  
  135.   while student_cursor% found--判断游标是否指向某条记录  
  136.   loop  
  137.     open address_cursor ;              --打开另外一个游标  
  138.     fetch address_cursor into add1 ;   --移动游标  
  139.     while address_cursor %found--判断游标是否指向某条记录  
  140.     loop  
  141.       if add1.xh = stu1.xh then--判断两个游标所指向的记录中xh的值是否相等  
  142.         update student s set s.zz = add1.zz where current of student_cursor; --假如相等就更新游标所指向的记录值  
  143.       end if;  
  144.       fetch address_cursor into add1 ; --移动游标  
  145.     end loop;  
  146.     close address_cursor ;             --关闭游标  
  147.     fetch student_cursor into stu1 ;   --移动游标  
  148.   end loop;  
  149.   close student_cursor ;               --关闭游标  
  150. end;  
REF游标也叫动态游标：
qREF 游标和游标变量用于处理运行时动态执行的 SQL 查询 q创建游标变量需要两个步骤： q声明 REF 游标类型 q声明 REF 游标类型的变量 q用于声明 REF 游标类型的语法为：
TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];
[sql] view plain copy
 
  1. -----------------------------------ref游标---------------------------------  
  2. declare  
  3.   type ref_cursor  is ref cursor; --声明一个ref游标类型  
  4.   tab_cursor ref_cursor ;--声明一个ref游标  
  5.   sname student.xm %type ;  
  6.   sno student.xh %type ;  
  7.   tab_name varchar2 (20 );  
  8. begin  
  9.   tab_name := ‘&tab_name‘; --接收客户输入的表明  
  10.   if tab_name = ‘student‘ then  
  11.     open tab_cursor for select xh ,xm  from student ; --打开ref游标  
  12.     fetch tab_cursor into sno ,sname ;--移动游标  
  13.     while tab_cursor %found  
  14.     loop  
  15.       dbms_output.put_line (‘学号：‘ ||sno ||‘姓名：‘ ||sname );  
  16.       fetch tab_cursor into sno ,sname ;  
  17.     end loop;  
  18.     close tab_cursor ;  
  19.   else  
  20.     dbms_output.put_line (‘没有找到你想要找的表数据信息‘ );  
  21.   end if;  
  22. end;  
  23.    
  24.     
  25. -----------------------------------ref游标题目---------------------------------  
  26. SQL > select * from student ;  
  27.         XH KC  
  28. ---------- ----------  
  29.          1 语文  
  30.          1 数学  
  31.          1 英语  
  32.          1 历史  
  33.          2 语文  
  34.          2 数学  
  35.          2 英语  
  36.          3 语文  
  37.          3 英语  
  38. 9 rows selected  
  39.    
  40. SQL >         
  41. 完成的任务 :  
  42. 生成student2表 (xh number, kc  varchar2 (50 ))；  
  43. 对应于每一个学生，求出他的总的选课记录，把每个学生的选课记录插入到student2表中。  
  44. 即，student2中的结果如下：  
  45.                       XH KC  
  46.                  --- -------------------------------------------  
  47.                        1 语文数学英语历史  
  48.                        2 语文数学英语  
  49.                        3 语文英语  
  50.    
  51. create table student2 (xh number, kc varchar2 (50 ));  
  52.    
  53. declare  
  54.   kcs varchar2 (50 );  
  55.   kc varchar2 (50 );  
  56.   type ref_cursor is ref cursor; --声明一个ref游标类型  
  57.   stu_cursor ref_cursor ;--定义一个ref游标类型的变量  
  58.   type tab_type is table of number; --声明一个table类型  
  59.   tab_xh tab_type ;--定义一个表类型的变量  
  60.   cursor cursor_xh is select distinct( xh) from student; --声明一个游标  
  61. begin  
  62.   open cursor_xh; --打开游标  
  63.   fetch cursor_xh bulk collect into tab_xh; --提取数据到表中  
  64.   for i in 1 .. tab_xh.count  
  65.   loop  
  66.     kcs :=‘‘ ;  
  67.     open stu_cursor for select kc from student s where s.xh = tab_xh(i ); --打开ref游标  
  68.     fetch stu_cursor into kc ; --移动游标  
  69.     while stu_cursor %found  
  70.     loop  
  71.       kcs := kc ||kcs ; --连接字符串使用||而不是+  
  72.       fetch stu_cursor into kc ; --移动游标  
  73.     end loop;  
  74.     insert into student2 (xh , kc ) values( i, kcs);  
  75.     close stu_cursor ;  
  76.   end loop;  
  77.   close cursor_xh ;  
  78. end;  

oracle游标的使用（二）


联系
我们