Oracle数据库游标的类型及使用实例全解

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。Oracle数据库的Cursor类型包含三种: 静态游标：分为显式（explicit）游标和隐式（implicit）游标；REF游标：是一种引用类型，类似于指针。下面我们一一介绍它们的使用。

创新互联建站始终坚持【策划先行，效果至上】的经营理念，通过多达10多年累计超上千家客户的网站建设总结了一套系统有效的全网营销推广解决方案，现已广泛运用于各行各业的客户，其中包括：楼梯护栏等企业，备受客户赞誉。

1.隐式游标

1)Select …INTO…语句，DML语句，使用隐式Cursor。此外，还有一种使用FOR LOOP的Implicit Cursor用法。

2)可以通过隐式Cusor的属性来了解操作的状态和结果。Cursor的属性包含：

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数。

SQL%FOUND  布尔型值为TRUE代表插入、删除、更新或单行查询操作成功。

SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反。

SQL%ISOPEN 布尔型DML执行过程中为真，结束后为假。

3) 隐式Cursor由系统自动打开和关闭.

例如：

 
 
 
    
  
  
  
  1. set serveroutput on    
    2.   
  
  
  2.  
    3.   
  
  
  3. declare    
    4.   
  
  
  4.  
    5.   
  
  
  5. begin      
    6.   
  
  
  6.  
    7.   
  
  
  7. update employees set employee_name='Mike' where employee_id=1001;    
    8.   
  
  
  8.  
    9.   
  
  
  9. if SQL%FOUND then      
    10.   
  
  
  10.  
    11.   
  
  
  11. dbms_output.put_line('Name is updated');    
    12.   
  
  
  12.  
    13.   
  
  
  13. else    
    14.   
  
  
  14.  
    15.   
  
  
  15. dbms_output.put_line('Name is not updated');    
    16.   
  
  
  16.  
    17.   
  
  
  17. end if;    
    18.   
  
  
  18.  
    19.   
  
  
  19. end;    
    20.   
  
  
  20.  
    21.   
  
  
  21. /    
    22.   
  
  
  22.  
    23.   
  
  
  23. set serveroutput on    
    24.   
  
  
  24.  
    25.   
  
  
  25. declare    
    26.   
  
  
  26.  
    27.   
  
  
  27. begin      
    28.   
  
  
  28.  
    29.   
  
  
  29. for tableInfo in (select * from user_tables) loop    
    30.   
  
  
  30.  
    31.   
  
  
  31. dbms_output.put_line(tableInfo.table_name);    
    32.   
  
  
  32.  
    33.   
  
  
  33. end loop;    
    34.   
  
  
  34.  
    35.   
  
  
  35. exception    
    36.   
  
  
  36.  
    37.   
  
  
  37. when others then    
    38.   
  
  
  38.  
    39.   
  
  
  39. dbms_output.put_line(sqlerrm);    
    40.   
  
  
  40.  
    41.   
  
  
  41. end;    
    42.   
  
  
  42.  
    43.   
  
  
  43. /  
    44.

2.显式游标

1) 显式Cursor的属性包含：

游标的属性   返回值类型   意义

%ROWCOUNT   整型  获得FETCH语句返回的数据行数

%FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND   布尔型 与%FOUND属性返回值相反

%ISOPEN 布尔型 游标已经打开时值为真，否则为假

2) 对于显式游标的运用分为四个步骤：

a 定义游标---Cursor  [Cursor Name]  IS;

b 打开游标---Open  [Cursor Name];

c  操作数据---Fetch  [Cursor name]

d  关闭游标---Close [Cursor Name]

以下是几种常见显式Cursor用法。

 
 
 
    
  
  
  
  1. set serveroutput on    
    2.   
  
  
  2.  
    3.   
  
  
  3. declare    
    4.   
  
  
  4.  
    5.   
  
  
  5. cursor cur is select * from user_tables;    
    6.   
  
  
  6.  
    7.   
  
  
  7. tableInfo user_tables%rowtype;    
    8.   
  
  
  8.  
    9.   
  
  
  9. begin    
    10.   
  
  
  10.  
    11.   
  
  
  11. open cur;        
    12.   
  
  
  12.  
    13.   
  
  
  13. loop    
    14.   
  
  
  14.  
    15.   
  
  
  15. fetch cur into tableInfo;    
    16.   
  
  
  16.  
    17.   
  
  
  17. exit when cur%notfound;    
    18.   
  
  
  18.  
    19.   
  
  
  19. dbms_output.put_line(tableInfo.table_name);    
    20.   
  
  
  20.  
    21.   
  
  
  21. end loop;
    exception    
    22.   
  
  
  22.  
    23.   
  
  
  23. when others then    
    24.   
  
  
  24.  
    25.   
  
  
  25. dbms_output.put_line(sqlerrm);
      close cur;    
    26.   
  
  
  26.  
    27.   
  
  
  27. end;    
    28.   
  
  
  28.  
    29.   
  
  
  29. /
        
    30.   
  
  
  30.  
    31.   
  
  
  31. set serveroutput on    
    32.   
  
  
  32.  
    33.   
  
  
  33. declare    
    34.   
  
  
  34.  
    35.   
  
  
  35. cursor cur is select * from user_tables;    
    36.   
  
  
  36.  
    37.   
  
  
  37. begin      
    38.   
  
  
  38.  
    39.   
  
  
  39. for tableInfo in cur loop    
    40.   
  
  
  40.  
    41.   
  
  
  41. dbms_output.put_line(tableInfo.table_name);    
    42.   
  
  
  42.  
    43.   
  
  
  43. end loop;    
    44.   
  
  
  44.  
    45.   
  
  
  45. exception    
    46.   
  
  
  46.  
    47.   
  
  
  47. when others then    
    48.   
  
  
  48.  
    49.   
  
  
  49. dbms_output.put_line(sqlerrm);    
    50.   
  
  
  50.  
    51.   
  
  
  51. end;    
    52.   
  
  
  52.  
    53.   
  
  
  53. /  
    54.

还可以使用带参数open的cursor。

 
 
 
    
  
  
  
  1. set serveroutput on    
    2.   
  
  
  2.  
    3.   
  
  
  3. declare    
    4.   
  
  
  4.  
    5.   
  
  
  5. cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;    
    6.   
  
  
  6.  
    7.   
  
  
  7. tableInfo user_constraints%rowtype;    
    8.   
  
  
  8.  
    9.   
  
  
  9. begin    
    10.   
  
  
  10.  
    11.   
  
  
  11. open cur('EMPLOYEES');        
    12.   
  
  
  12.  
    13.   
  
  
  13. loop    
    14.   
  
  
  14.  
    15.   
  
  
  15. fetch cur into tableInfo;    
    16.   
  
  
  16.  
    17.   
  
  
  17. exit when cur%notfound;    
    18.   
  
  
  18.  
    19.   
  
  
  19. dbms_output.put_line(tableInfo.constraint_name);    
    20.   
  
  
  20.  
    21.   
  
  
  21. end loop;
    exception    
    22.   
  
  
  22.  
    23.   
  
  
  23. when others then    
    24.   
  
  
  24.  
    25.   
  
  
  25. dbms_output.put_line(sqlerrm);
      close cur;    
    26.   
  
  
  26.  
    27.   
  
  
  27. end;    
    28.   
  
  
  28.  
    29.   
  
  
  29. /
        
    30.   
  
  
  30.  
    31.   
  
  
  31. set serveroutput on    
    32.   
  
  
  32.  
    33.   
  
  
  33. declare    
    34.   
  
  
  34.  
    35.   
  
  
  35. cursor cur(tblName varchar2) is select * from user_constraints where table_name=tblName;    
    36.   
  
  
  36.  
    37.   
  
  
  37. begin    
    38.   
  
  
  38.  
    39.   
  
  
  39. for tableInfo in cur('EMPLOYEES') loop    
    40.   
  
  
  40.  
    41.   
  
  
  41. dbms_output.put_line(tableInfo.constraint_name);    
    42.   
  
  
  42.  
    43.   
  
  
  43. end loop;    
    44.   
  
  
  44.  
    45.   
  
  
  45. exception    
    46.   
  
  
  46.  
    47.   
  
  
  47. when others then    
    48.   
  
  
  48.  
    49.   
  
  
  49. dbms_output.put_line(sqlerrm);    
    50.   
  
  
  50.  
    51.   
  
  
  51. end    
    52.   
  
  
  52.  
    53.   
  
  
  53. /  
    54.

可以使用WHERE CURRENT OF子句执行UPDATE或DELETE操作。

 
 
 
    
  
  
  
  1. set serveroutput on    
    2.   
  
  
  2.  
    3.   
  
  
  3. declare    
    4.   
  
  
  4.  
    5.   
  
  
  5. cursor cur is select * from employees for update;    
    6.   
  
  
  6.  
    7.   
  
  
  7. begin      
    8.   
  
  
  8.  
    9.   
  
  
  9. for tableInfo in cur loop    
    10.   
  
  
  10.  
    11.   
  
  
  11. update employees set salarysalary=salary*1.1 where current of cur;    
    12.   
  
  
  12.  
    13.   
  
  
  13. end loop;    
    14.   
  
  
  14.  
    15.   
  
  
  15. commit;    
    16.   
  
  
  16.  
    17.   
  
  
  17. exception    
    18.   
  
  
  18.  
    19.   
  
  
  19. when others then    
    20.   
  
  
  20.  
    21.   
  
  
  21. dbms_output.put_line(sqlerrm);    
    22.   
  
  
  22.  
    23.   
  
  
  23. end;    
    24.   
  
  
  24.  
    25.   
  
  
  25. /  
    26.

3.REF CURSOR（Cursor Variables）

REF Cursor在运行的时候才能确定游标使用的查询。利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。

也可以利用REF CURSOR实现BULK SQL，提高SQL性能。

REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。

Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。

Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配。

Ref cursor的使用:

1) Type [Cursor type name] is ref cursor

2) Open cursor for...

3) Fetch  [Cursor name]

4) Close Cursor

例如：

Step1:

 
 
 
    
  
  
  
  1. create or replace package TEST as    
    2.   
  
  
  2.  
    3.   
  
  
  3. type employees_refcursor_type is ref cursor return employees%rowtype;    
    4.   
  
  
  4.  
    5.   
  
  
  5. procedure employees_loop(employees_cur IN employees_refcursor_type);    
    6.   
  
  
  6.  
    7.   
  
  
  7. end TEST;    
    8.   
  
  
  8.  
    9.   
  
  
  9. /   
    10.

Step2:

 
 
 
    
  
  
  
  1. create or replace package body TEST as    
    2.   
  
  
  2.  
    3.   
  
  
  3. procedure employees_loop(employees_cur IN employees_refcursor_type) is    
    4.   
  
  
  4.  
    5.   
  
  
  5. emp employees%rowtype;    
    6.   
  
  
  6.  
    7.   
  
  
  7. begin    
    8.   
  
  
  8.  
    9.   
  
  
  9. loop    
    10.   
  
  
  10.  
    11.   
  
  
  11. fetch employees_cur into emp;    
    12.   
  
  
  12.  
    13.   
  
  
  13. exit when employees_cur%NOTFOUND;    
    14.   
  
  
  14.  
    15.   
  
  
  15. dbms_output.put_line(emp.employee_id);    
    16.   
  
  
  16.  
    17.   
  
  
  17. end loop;    
    18.   
  
  
  18.  
    19.   
  
  
  19. end employees_loop;    
    20.   
  
  
  20.  
    21.   
  
  
  21. end TEST;    
    22.   
  
  
  22.  
    23.   
  
  
  23. / 
    24.

Step3:

 
 
 
    
  
  
  
  1. set serveroutput on    
    2.   
  
  
  2.  
    3.   
  
  
  3. declare    
    4.   
  
  
  4.  
    5.   
  
  
  5. empRefCur TEST.employees_refcursor_type;    
    6.   
  
  
  6.  
    7.   
  
  
  7. begin    
    8.   
  
  
  8.  
    9.   
  
  
  9. for i in 10..20 loop    
    10.   
  
  
  10.  
    11.   
  
  
  11. dbms_output.put_line('Department ID=' || i);    
    12.   
  
  
  12.  
    13.   
  
  
  13. open empRefCur for select * from employees where department_id=i;    
    14.   
  
  
  14.  
    15.   
  
  
  15. TEST.employees_loop(empRefCur);    
    16.   
  
  
  16.  
    17.   
  
  
  17. end loop;    
    18.   
  
  
  18.  
    19.   
  
  
  19. exception    
    20.   
  
  
  20.  
    21.   
  
  
  21. when others then    
    22.   
  
  
  22.  
    23.   
  
  
  23. dbms_output.put_line(sqlerrm);    
    24.   
  
  
  24.  
    25.   
  
  
  25.  
    26.   
  
  
  26. close empRefCur;    
    27.   
  
  
  27.  
    28.   
  
  
  28. end;    
    29.   
  
  
  29.  
    30.   
  
  
  30. / 
    31.

4.BULK SQL

使用FORALL和BULK COLLECT子句。利用BULK SQL可以减少PLSQL Engine和SQL Engine之间的通信开销，提高性能。

1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct. 加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.  加速SELECT，用BULK COLLECT INTO 来替代INTO。

 
 
 
    
  
  
  
  1. SQL> create table employees_tmp as select first_name, last_name, salary from employees where 0=1;  
    2.   
  
  
  2.  
    3.   
  
  
  3. set serveroutput on    
    4.   
  
  
  4.  
    5.   
  
  
  5. declare    
    6.   
  
  
  6.  
    7.   
  
  
  7. cursor employees_cur(depId employees.department_id%type) is select first_name, last_name, salary from employees where department_id=depId;    
    8.   
  
  
  8.  
    9.   
  
  
  9. type employee_table_type is table of employees_cur%rowtype index by pls_integer;    
    10.   
  
  
  10.  
    11.   
  
  
  11. employee_table employee_table_type;    
    12.   
  
  
  12.  
    13.   
  
  
  13. begin    
    14.   
  
  
  14.  
    15.   
  
  
  15. open employees_cur(100);    
    16.   
  
  
  16.  
    17.   
  
  
  17. fetch employees_cur bulk collect into employee_table;    
    18.   
  
  
  18.  
    19.   
  
  
  19. close employees_cur;    
    20.   
  
  
  20.  
    21.   
  
  
  21. for i in 1..employee_table.count loop    
    22.   
  
  
  22.  
    23.   
  
  
  23. dbms_output.put_line(employee_table(i).first_name || ' ' || employee_table(i).last_name || ',' || employee_table(i).salary);    
    24.   
  
  
  24.  
    25.   
  
  
  25. end loop;    
    26.   
  
  
  26.  
    27.   
  
  
  27. forall i in employee_table.first..employee_table.last    
    28.   
  
  
  28.  
    29.   
  
  
  29. insert into employees_tmp values(employee_table(i).first_name, employee_table(i).last_name, employee_table(i).salary);    
    30.   
  
  
  30.  
    31.   
  
  
  31. commit;    
    32.   
  
  
  32.  
    33.   
  
  
  33. end;    
    34.   
  
  
  34.  
    35.   
  
  
  35. /   
    36.

5.  动态性能表V$OPEN_CURSOR

本视图列出session打开的所有cursors。

关于Oracle数据库游标的类型和使用的知识就介绍到这里了，如果您想了解更多的Oracle数据库的知识，可以到这里看一下：http://database./oracle/，相信一定能够带给您收获的！

文章标题：Oracle数据库游标的类型及使用实例全解
URL分享：http://www.shufengxianlan.com/qtweb/news23/279123.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

定制网站知识

同城分类信息

成都网络营销　　 　bgp机房托管　　 　ios开发　　 　联通服务器托管　　 　成都服务器托管　　 　和正源建设　　 　眉山服务器租用　　 　成都营销型网站建设　　 　成都广告制作　　 　邛崃网站建设　　 　传美奇电器　　 　资阳网站建设　　 　德阳电信机房　　 　Android开发　　 　成都标志设计　　 　网站收录　　 　商标注册　　 　软文推广　　 　成都印刷厂　　 　温江电信机房