Oracle笔记（八）：游标

目录

一、概述

二、显式游标

提取当前指针指向的行记录

使用显式游标的LOOP语句

游标循环提取记录

使用Bulk Collect 和 For语句批量提取

CURSOR FOR LOOP遍历游标 

三、隐式游标

隐式游标的实例

游标异常处理机制

---

一、概述

        游标是Oracle的一种数据访问机制，它允许用户访问单独的数据行，用户可对每一行单独处理，从而降低系统开销和潜在的阻隔情况。用户也可使用这些数据生成SQL语句。

        游标分为静态游标与REF游标两类。静态游标又包含两种类型：显式游标 与 隐式游标。

1. 显式游标：使用前必须有明确游标声明和定义，这样的定义会关联数据查询语句，通常返回一行或多行。打开游标后，用户可以利用游标位置对结果集进行检索，返回单一的行记录，用户可以操作该记录。关闭游标后，就不能对结果集进行任何操作。显式游标需要用户自己Coding完成，一切由用户控制。
2. 隐式游标：被数据库自动管理，此游标用户无法控制，但可以得到它的属性信息。

 

二、显式游标

声明的语法：

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

 

以水果表fruits为例 

表结构为

• CURSOR：声明游标
• parameter_name：参数名称
• datatype：参数类型
• select_statement：游标关联的Select语句

DECLARE CURSOR cursor_fruits
     IS SELECT f_name, f_price 
   FROM fruits;

打开游标

使用之前，必须打开游标

OPEN cursor_name;

读取游标中数据

FETCH cursor_name INTO record_name

关闭游标

CLOSE cursor_name;

提取当前指针指向的行记录

 /**
   声明一个游标提取当前指针指向的行记录

   以下代码在Command界面执行，不要忘了 "/" 结束脚本
 */

DECLARE
CURSOR fru_cur                              --声明区域
    IS SELECT f_id, f_name FROM fruits;
 cur_fruits fru_cur%ROWTYPE;                --定义游标变量
BEGIN
  OPEN fru_cur;                             --打开游标
    FETCH  fru_cur INTO cur_fruits;         --提取当前行记录
    dbms_output.put_line(cur_fruits.f_id||'.'||cur_fruits.f_name);
CLOSE fru_cur;
END;
/

打印结果为： 

a1.apple
PL/SQL procedure successfully completed

 

• ‘||’ 表示连接符
• 使用%type可以取得表中的字段类型
• 使用%rowtype可以申明基于某个表的行类型

 

使用显式游标的LOOP语句

DECLARE
CURSOR cur_frt_loop
    IS SELECT f_id, f_name, f_price FROM fruits
 WHERE f_price>10;
 
 cur_id fruits.f_id%TYPE;            --表示变量类型同表fruits对应字段类型一致
 cur_name fruits.f_name%TYPE;
 cur_price fruits.f_price%TYPE; 
BEGIN 
  OPEN cur_frt_loop;
    LOOP 
      FETCH cur_frt_loop INTO cur_id, cur_name, cur_price;
      EXIT WHEN cur_frt_loop%NOTFOUND;
      dbms_output.put_line(cur_id||','||cur_name||','||cur_price);
    END LOOP;
  CLOSE cur_frt_loop;
END;
/

打印结果为：

b1,blackberry,10.2
bs1,orange,11.2
t1,banana,10.3
m1,mango,15.6
m3,xxtt,11.6
PL/SQL procedure successfully completed

 

游标循环提取记录

DECLARE
CURSOR cur_frt_loop
    IS SELECT f_id, f_name, f_price FROM fruits
 WHERE f_price>10;
 
 cur_id fruits.f_id%TYPE;            --表示变量类型同表fruits对应字段类型一致
 cur_name fruits.f_name%TYPE;
 cur_price fruits.f_price%TYPE; 

BEGIN 
  OPEN cur_frt_loop;
    LOOP 
      FETCH cur_frt_loop INTO cur_id, cur_name, cur_price;
      EXIT WHEN cur_frt_loop%NOTFOUND;                        --循环退出的条件
      dbms_output.put_line(cur_id||','||cur_name||','||cur_price);
    END LOOP;
  CLOSE cur_frt_loop;
END;

结果为：

b1,blackberry,10.2
bs1,orange,11.2
t1,banana,10.3
m1,mango,15.6
m3,xxtt,11.6
PL/SQL procedure successfully completed

 

使用Bulk Collect 和 For语句批量提取

SET SERVEROUTPUT ON;                        --打开输出
DECLARE                                     --定义各项
CURSOR cur_fru_collect
    IS SELECT * FROM fruits
 WHERE f_price>10;
  TYPE frt_tab IS TABLE OF fruits%ROWTYPE;      --集合类型frt_rd, 与表fruits行对象一致
frt_rd frt_tab;                                 --该变量用于存放批量提取的数据
  
BEGIN 
  OPEN cur_fru_collect;
    LOOP 
      FETCH cur_fru_collect BULK COLLECT INTO frt_rd LIMIT 2;    --每次提取2条数据
      FOR i in 1..frt_rd.count 
        LOOP
        dbms_output.put_line(frt_rd(i).f_id||','||frt_rd(i).f_name
                             ||','||frt_rd(i).f_price);
        END LOOP;
      EXIT WHEN cur_fru_collect%NOTFOUND;
    END LOOP;
  CLOSE cur_fru_collect;
END;

b1,blackberry,10.2
bs1,orange,11.2
t1,banana,10.3
m1,mango,15.6
m3,xxtt,11.6
PL/SQL procedure successfully completed

 

CURSOR FOR LOOP遍历游标 

DECLARE 
CURSOR frt1 
    IS SELECT * FROM fruits
 WHERE f_price<10;

BEGIN
  FOR curfrt1 IN frt1
      LOOP
        dbms_output.put_line(curfrt1.f_id||'.'|| curfrt1.f_name
                          ||'.'|| curfrt1.f_price);
      END LOOP;
END;

a1.apple.5.2
bs2.melON.8.2
t2.grape.5.3
o2.cocONut.9.2
c0.cherry.3.2
a2.apricot.2.2
l2.lemON.6.4
b2.berry.7.6
m2.xbabay.2.6
t4.xbababa.3.6
b5.xxxx.3.6

PL/SQL procedure successfully completed

 

三、隐式游标

隐式游标的实例

DECLARE 
cur_id fruits.f_id%TYPE;
cur_name fruits.f_name%TYPE;
cur_price fruits.f_price%TYPE;

BEGIN
  SELECT f_id, f_name, f_price INTO cur_id, cur_name, cur_price
    FROM fruits
   WHERE f_price=5.3;
   IF SQL%FOUND
     THEN 
       dbms_output.put_line(cur_id||','||cur_name||','||cur_price);
   END IF;
END;
/

游标异常处理机制

为了效果先删除表fruits

SQL> delete fruits;

16 rows deleted

DECLARE 
cur_id fruits.f_id%TYPE;
cur_name fruits.f_name%TYPE;
cur_price fruits.f_price%TYPE;
BEGIN
   SELECT f_id, f_name, f_price INTO cur_id, cur_name, cur_price
    FROM fruits;
    
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        dbms_output.put_line('NoDataFounded!!!!!!');
END;

结果为：

SQL> NoDataFounded!!!!!!

【注】使用完游标之后，一定要将其关闭。作用是释放游标和数据库的连接，释放内存。