Oracle游标（Cursor）------------------数据的缓存区

一、游标的概念

光从字面上理解可以将游标（Cursor）形象地看成一个变动的光标。它实际上是一个指针，它在一段Oracle存放数据查询结果集或数据操作结果集的内存中，这个指针可以指向结果集中的任何一条记录。说到这很多小伙伴就会联想到编程语言里的数组，确实像极了，So很多知识是互通的。

 

二、游标的分类

Oracle中游标分为静态游标和REF游标，本文不对REF游标做介绍，感兴趣的小伙伴可以自己动手查阅。静态游标就像一个数据快照，打开游标后的结果集是对数据库数据的一个备份，数据不会随着对表执行DML操作后而改变。从这个特性来说，结果集是静态的。

静态游标包含下面两种类型：

1、显示游标：是指在使用之前必须有这明确声明和定义的游标。它需要用户自己编写代码实现，一切由用户控制。

2、隐式游标：和显示游标截然不同，它由PL/SQL自动管理，也被称之为SQL游标。该游标由Oracle自动管理，用户无法控制，但能得到它的属性。

 

三、显示游标的属性

利用游标属性可以得到游标执行过程的相关信息。显式游标有以下4个属性：
■ %ISOPEN：用于判断游标是否打开，如果已经打开则返回TRUE，如果游标未打开则返回FALSE。
■ %FOUND：此属性可用于检测行数据是否有效。如果有效返回TRUE，否则返回FALSE。
■ %NOTFOUND：与%FOUND属性恰好相反，如果没有提取出数据则返回TRUE，否则返回FALSE。
■ %ROWCOUNT：累计到当前为止使用FETCH语句提取的数据行数。

 

四、显示游标的使用

 

1.显示游标---通过显式游标用户可以操作返回的数据，使得一些在编程语言中复杂的功能变得更容易实现。

1.1游标语法：

CURSOR cursor_name
[(parameter_name datatype,...)]
IS select_statement;

CURSOR cursor_name：声明游标，cursor_name是游标的名称。

[]：括号内内容代表可选

parameter_name：参数名称。

datatype：参数类型。

select_statement：与游标关联的SELECT语句，但该语句不能是SELECT...INTO...语句。

1.2游标的使用步骤

（1）声明游标---主要用于给游标命名并且为游标关联一个查询。

    具体语句如下：

DECLARE CURSOR cursor_name
IS select_statement;

（2）打开游标---游标中任何对数据的操作都是建立在游标被打开的前提下。打开游标即初始化了游标指针，游标一旦打开，其结果集都是静态的。也就是说此时的结果集不会反映出数据库中对数据库进行的增删改操作。

具体语句如下：

OPEN cursor_name;

（3）读取数据---它可以把游标指向位置的记录放入到PL/SQL声明的变量当中。但它只能取出指针当前行的记录，一般结合循环语句使用。

具体语句如下：

FETCH cursor_name INTO record_name;

（4）关闭游标---关闭游标，此时将释放资源。

具体语句如下：

CLOSE cursor_name

下面结合产品信息表，举出几个示例。关于示例，建议初学的小伙伴还是自己建表加数据，动手实践加深印象（示例可在PL/SQL Developer中的SQL窗口直接执行，并在Output标签查看输出）------纸上得来终觉浅，绝知此事要躬行

PRODUCTINFO表结构：

【示例一】带参数游标+LOOP+%NOTFOUND

01  DECLARE 
02    cur_productid productinfo.Productid%TYPE := '0240';
03    cur_productprice productinfo.Productprice%TYPE := 1200;
04    cur_prodrcd productinfo%ROWTYPE;
05    
06    CURSOR pdct_parameter_cur (id VARCHAR,price NUMBER)
07    IS SELECT * FROM PRODUCTINFO 
08    WHERE productid like id||'%' 
09    AND productprice > price;
10  
11  BEGIN
12      OPEN pdct_parameter_cur(cur_productid,cur_productprice);
13           LOOP
14                FETCH pdct_parameter_cur INTO cur_prodrcd;
15                EXIT WHEN pdct_parameter_cur%NOTFOUND;
16                DBMS_OUTPUT.PUT_LINE('产品ID：' || cur_prodrcd.productid ||'  产品名称：'|| 
17                cur_prodrcd.productname ||'  产品价格：'||
18                cur_prodrcd.productprice);
19           END LOOP;
20      CLOSE pdct_parameter_cur;
21  END;

【代码解析】
■ 第2～3行声明变量并赋值，这两个参数是要传递给游标的变量。
■ 第4行声明表productinfo的行对象，用于存放FETCH语句提取的数据。
■ 第6行声明了游标，包括两个参数，注意，参数需要指明类型。
■ 第7～9行是游标关联的查询语句，可以看到第8行和第9行的查询条件都使用了游标中的变量。
■ 第12行表示打开游标，并把第2行和第3行的变量传入游标中。

 

【示例二】%ISOPEN+IF...THEN

01    DECLARE 
02    CURSOR pdct_isopen_cur 
03    IS SELECT * FROM PRODUCTINFO ;
04   
05    cur_prodrcd productinfo%ROWTYPE;
06   
07    BEGIN
08      IF pdct_isopen_cur%ISOPEN THEN
09           FETCH pdct_isopen_cur INTO cur_prodrcd;
10           DBMS_OUTPUT.PUT_LINE('产品ID：' || cur_prodrcd.productid 
11           ||'  产品名称：'|| cur_prodrcd.productname 
12           ||'  产品价格：'|| cur_prodrcd.productprice);
13  
14      ELSE 
15           DBMS_OUTPUT.PUT_LINE('游标pdct_isopen_cur没有打开');
16      END IF;
17    END;

【代码解析】
■ 第8行、第14行表示利用游标属性%ISOPEN判断游标是否打开，如果游标已经打开，则执行THEN后面的语句，否则执行ELSE后面的语句。
■ 第16行是IF语句的结束标志。

 

【示例三】FOR...LOOP+%ROWCOUNT+%NOTFOUND

01    DECLARE 
02    CURSOR pdct_rowcount_cur 
03    IS SELECT *  FROM PRODUCTINFO ;
04   
05    TYPE PDCT_TAB IS TABLE OF PRODUCTINFO%ROWTYPE;
06    pdct_count_rd PDCT_TAB;
07   
08    BEGIN
09      OPEN pdct_rowcount_cur;
10      LOOP
11        
12          FETCH pdct_rowcount_cur BULK COLLECT INTO pdct_count_rd LIMIT 2 ;
13          FOR i in pdct_count_rd.first..pdct_count_rd.last LOOP
14              DBMS_OUTPUT.PUT_LINE('产品ID：' || pdct_count_rd(i).productid ||'  产品名称：'|| 
15              pdct_count_rd(i).productname ||'  产品价格：'||
16              pdct_count_rd(i).productprice);
17          END LOOP;
18          IF mod(pdct_rowcount_cur%ROWCOUNT,2) = 0 THEN
19              DBMS_OUTPUT.PUT_LINE('读取到了第' 
20          || pdct_rowcount_cur%ROWCOUNT
21            || '条记录!');
22          ELSE 
23              DBMS_OUTPUT.PUT_LINE('读取到单条记录为第' 
24          || pdct_rowcount_cur%ROWCOUNT 
25          || '条记录!');
26          END IF;
27          EXIT WHEN pdct_rowcount_cur%NOTFOUND;
28          
29       END LOOP;
30     CLOSE pdct_rowcount_cur;
31    END; 

【代码解析】
■ 第5～6行表示定义与表PRODUCTINFO 行对象一致的集合类型pdct_rd，该变量用于存放批量提取的数据。
■ 第13行表示对批量读取的数据从第一条到最后一条进行遍历。
■ 第18行表示用条件语句进行判断，如果提取出来的数据行数能被2整除就执行THEN后面的输出脚本，如果提取的数据行数不能被2整除则进入第22行ELSE后面的输出脚本。其中，第18行的mod()是取余函数，表示pdct_rowcount_cur%ROWCOUNT对2取余。
■ 第26行是IF语句的结束标志。
■ 第27行利用%NOTFOUND属性判断游标是否已走到尾端。至此如果仍没有发现可用的数据则利用EXIT退出。

 

学习了显示游标，接下来咱们继续学习隐式游标

 

五、隐式游标的属性

■ %ISOPEN属性：该属性永远返回FALSE，它由Oracle自己控制。
■ %FOUND属性：此属性可以反映出DML操作是否影响到了数据，当DML操作对数据有影响时该属性为TRUE，否则为FALSE。它也可以反映出SELECT INTO语句是否返回了数据，当有数据返回时该属性为TURE。
■ %NOTFOUND属性：与%FOUND属性相反，当DML操作没有影响数据以及SELECT INTO没有返回数据时该属性为TRUE，其他情况为FALSE。
■ ％ROWCOUNT属性：该属性可反映出DML操作对数据影响的数量。

 

六、隐式游标的使用

当运行SELECT或DML语句时，PL/SQL会打开一个隐式的游标。隐式游标不受用户的控制，这一点和显式游标有明显的不同。下面列出了隐式游标和显式游标的不同之处：
■ 隐式游标由PL/SQL自动管理。
■ 隐式游标的默认名称是SQL。
■ SELECT或DML操作会产生隐式游标。
■ 隐式游标的属性值始终是最新执行的SQL语句的值。

下面让咱们通过几个示例来更清晰了解隐式游标吧，还是那句话，示例还是得自己多动手

【示例四】SQL%FOUND

01  DECLARE
02          cur_productname productinfo.Productname%TYPE;
03          cur_productprice productinfo.Productprice%TYPE;
04   BEGIN
05          SELECT productname, productprice INTO cur_productname,cur_productprice 
06          FROM PRODUCTINFO ;
07  
08          EXCEPTION 
09          WHEN TOO_MANY_ROWS THEN  
10            IF SQL%FOUND THEN          
11               DBMS_OUTPUT.PUT_LINE('%FOUND为TRUE'); 
12               DELETE FROM  PRODUCTINFO WHERE productid = '00000000';
13               IF SQL%FOUND THEN  
14                  DBMS_OUTPUT.PUT_LINE('删除数据！'); 
15               END IF; 
16            END IF ;  
17      
18   END;

 

【代码解析】
■ 第1～4行小伙伴理解起来应该 不会有问题。
■ 第5～6行利用SELECT INTO语句向变量中保存数据，但此语句会返回多条数据，也就是说会引发异常。
■ 第9行是SELECT INTO语句可能引发的异常，当返回多条数据时会出现TOO_MANY_ROWS异常，脚本会进入THEN后面的代码流程。
■ 第10行在发生TOO_MANY_ROWS异常时检测%FOUND是否为TRUE，如果为TRUE则执行该行THEN后面的脚本。
■ 第12行表示当%FOUND为TRUE时，则执行该删除脚本。
■ 第13行继续判断该删除脚本产生的游标的%FOUND属性，如果为TRUE，则会执行第14行脚本。

注意    在SELECT  INTO语句中%FOUND不会因语句是否发生了异常而改变，只要有返回值该属性就为TRUE。但有异常发生时，执行流程会马上发生改变。也就是说，在异常代码外检查该属性有可能得不到有效执行。第13行的%FOUND是第12行删除语句游标的属性，这一点小伙伴需要注意。
 

【示例五】SQL%ROWCOUNT

01  DECLARE
02          cur_productname productinfo.Productname%TYPE;
03          cur_productprice productinfo.Productprice%TYPE;
04          cur_count varchar(8);
05   BEGIN
06          SELECT productname, productprice INTO cur_productname,cur_productprice 
07          FROM PRODUCTINFO ;
08  
09          EXCEPTION 
10          WHEN NO_DATA_FOUND THEN
11            DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
12            DBMS_OUTPUT.PUT_LINE('没有数据！');
13                     
14          WHEN TOO_MANY_ROWS THEN  
15            cur_count:=SQL%ROWCOUNT;
16            DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT值是：' || cur_count);  
17       UPDATE PRODUCTINFO SET PRODUCTINFO.productname = '' ;      
18       DBMS_OUTPUT.PUT_LINE('修改脚本影响的记录数：' || SQL%ROWCOUNT);
19           ROLLBACK;
20       DBMS_OUTPUT.PUT_LINE('脚本回滚后：' || SQL%ROWCOUNT);
19   END;

【代码解析】
■ 运行此脚本时会出现TOO_MANY_ROWS异常，然后进入异常块内部。
■ 第4行声明了一个类型为varchar类型的变量，变量用于存放%ROWCOUNT的值。
■ 第15行表示发生异常时把%ROWCOUNT的值保存到变量cur_count中，通过第16行的输出可以测试此处的保存是否成功。
■ 第17行修改表PRODUCTINFO中的数据。
■ 第18行输出修改语句影响的记录数。
■ 第19行表示事务回滚。
■ 第20行输出事务回滚后%ROWCOUNT属性的值。

 

七、游标案例----让我们以一个大案例结束游标的学习

本案例涉及两张表，分别是PRODUCTINFO（产品信息表）和CATEGORYINFO（产品类型信息表）。

 

需求：利用游标转换这两张表的数据，要求把商品价格高于1000的、产地标为“中国”和“杭州”的家电和电子产品放入到表productinfo_tmp（该表的字段同productinfo一致，字段类型可根据实际需要自行定义）中，并且要求商品类型编号换成商品类型，商品价格高于2000的下调5%。

需求分析：
■ 要建表productinfo_tmp。
■ 利用SQL语句把符合要求的数据查询出来。
■ 把符合要求的数据放进新表。
■ 把新表的数据中商品类型编号换成商品类型。
■ 商品价格下调。

1）建表productinfo_tmp
首先创建表productinfo_tmp。由于要求该表的字段和productinfo表字段一致，而且经过观察CATEGORY字段的长度可以暂时容纳表CATEGORYINFO中GATEGORYNAME字段内容，所以这里不对新表的CATEGORY字段长度做修改。利用如下语句完成表的创建：

01  CREATE  TABLE productinfo_tmp AS SELECT * FROM productinfo 
02  WHERE 1=0

【代码解析】
■ 该脚本表示创建结构和表productinfo一样的新表productinfo_tmp。
■ 第2行表示复制表的时候不包括数据。如果WHERE后的条件为TRUE，则复制表的时候会把原表的数据一同复制。

2）以下是按照案例要求完成的脚本。脚本比较多，小伙伴可以仔细阅读代码分析部分。

01    DECLARE 
02    
03    cur_categoryid categoryinfo.categoryid%TYPE;
04    cur_categoryname categoryinfo.categoryname%TYPE;
05    cur_prodrcd productinfo%ROWTYPE;
06    tmpnum number(8,0);
07    
08    CURSOR cur_prdt_catg IS     
09    SELECT * FROM productinfo WHERE productprice >1000 AND origin in('中国','杭州')
10    AND category IN
11    (SELECT categoryid 
12     FROM categoryinfo 
13     WHERE categoryname 
14     IN('路由器','电视','洗衣机','MP3')
15     );
16     
17    CURSOR cur_catg IS     
18    SELECT CATEGORYID,CATEGORYNAME FROM categoryinfo 
19    WHERE categoryname 
20    IN('路由器','电视','洗衣机','MP3');
21   
22    BEGIN
23      
24      ----把符合要求的数据放进表productinfo_tmp
25      OPEN cur_prdt_catg;
26      LOOP
27         FETCH cur_prdt_catg INTO cur_prodrcd; 
28         IF cur_prdt_catg%FOUND THEN     
29            INSERT INTO productinfo_tmp 
30               (productid,productname,productprice,
31                 quantity,category,desperation,origin) 
32             VALUES 
33               (cur_prodrcd.productid,cur_prodrcd.PRODUCTNAME,
34                 cur_prodrcd.PRODUCTPRICE,cur_prodrcd.QUANTITY,
35                 cur_prodrcd.CATEGORY,cur_prodrcd.DESPERATION,
36                 cur_prodrcd.ORIGIN);
37            
38         ELSE 
39            DBMS_OUTPUT.PUT_LINE('已取出所有数据！共' || cur_prdt_catg%ROWCOUNT ||'条记录');
40            EXIT;
41         END IF;
42      END LOOP;    
43      COMMIT;
44      
45      ----转换产品类型
46      OPEN cur_catg;
47      tmpnum := 0;
48      LOOP
49        FETCH cur_catg INTO cur_categoryid,cur_categoryname;      
50        IF  cur_catg%FOUND THEN  
51            UPDATE productinfo_tmp SET productinfo_tmp.category = cur_categoryname 
52            WHERE category = cur_categoryid;
53            IF SQL%FOUND THEN
54               tmpnum := tmpnum+SQL%ROWCOUNT;
55            END IF;
56        ELSE
57            DBMS_OUTPUT.PUT_LINE('产品类型转换完毕！共转换' || tmpnum ||'条记录');
58            EXIT;
59        END IF; 
60      END LOOP;
61  
62      ----产品价格下调
63    UPDATE productinfo_tmp 
64    SET productinfo_tmp.productprice = productinfo_tmp.productprice*0.95
65    WHERE productinfo_tmp.productprice > 2000;
66    DBMS_OUTPUT.PUT_LINE('价格下调完毕！共下调' || SQL%ROWCOUNT ||'条商品');
67    COMMIT;
68    END;

代码解析】
■ 第3～4行表示声明变量，其类型同表字段类型一致。
■ 第5行声明一个行对象类型的变量。
■ 第6行声明一个number类型的变量。
■ 第8～15行表示创建游标得到商品符合价格高于1000的、产地标为“中国”和“杭州”的家电和电子产品的数据。
■ 第11～15行表示查询出categoryname字段符合IN括号里面条件的数据，此查询的结果将作为第9～10行SELECT语句的条件。
■ 第17～20行表示创建游标得到表categoryinfo中家电和电子产品的产品编码和产品类型。
■ 第25～27行表示打开游标cur_prdt_catg并进入循环体部分提取行数据。
■ 第28行表示如果cur_prdt_catg游标的%FOUND属性为TRUE，则进入插入数据语句。
■ 第29～36行表示把符合要求的数据插入到新表productinfo_tmp中。
■ 第38～39行表示当没有符合要求的数据时进入该流程，它利用游标的%ROWCOUNT属性进行统计输入到新表的记录数。EXIT表示退出该游标。
■ 第43行表示提交事务。
■ 第46～60行表示打开游标cur_catg，完成把productinfo_tmp表中的产品类型编码改成产品类型名称的功能。
■ 第47行为变量tmpnum赋初始值0。
■ 第48～50行脚本表示进入循环体部分提取数据，并利用%FOUND属性判断数据是否提取完毕。
■ 第51～52行对表productinfo_tmp中的数据进行修改，把产品类型编码修改为产品类型名称。
■第53～55行是比较有趣的地方。这里利用了隐式游标的%FOUND属性判断修改了多少条数据，并利用tmpnum变量和隐式游标的%ROWCOUNT属性最终得到转换数据的数量。
■ 第57行表示输出最终转换的数据数目。
■ 第63～65行完成产品价格下调的功能。
■ 第66行表示利用隐式游标的%ROWCOUNT属性得到价格下调成功的产品的数量。
■ 第67行表示事务提交。

【执行效果】
在PL/SQL Developer的SQL窗口执行以上代码，执行结果见SQL窗口中的Output标签页面，打印输出结果如下：

已取出所有数据！共4条记录
产品类型转换完毕！共转换4条记录
价格下调完毕！共下调3条商品

最后表productinfo_tmp中的内容如下图所示，小伙伴可以自行同productinfo表做对比。

到此，游标学习告一段落，欢迎纠正，欢迎补充。

本博客主要引自：《零基础学Oracle》 — 赵雪 胡可 王建强