游标的使用

一、简介

1、游标的概念

游标(Cursor) 就是一个变动的光标，它本质上是一个指针，指向从数据库查询出来的结果集任何一条记录，初始的时候指向第一条记录。

2、游标的分类

Oracle中游标分为两类：普通游标、REF游标，普通游标又可以分为两种类型：显式游标、隐式游标.

二、显式游标

显式游标是指在使用前必须有着明确的游标声明和定义，显式游标的定义会关联查询语句，返回一条或多条记录，显示游标的使用由开发人员控制。

1、使用步骤

1)、声明游标

CURSOR cursor_name                  --声明游标，cursor_name是游标名称
    is select_statement;            --游标关联的select语句，注意：不能是select ... into 语句

2)、打开游标
游标中想要读取数据都是建立在游标已打开的前提下

OPEN cursor_name；

3)、读取数据
读取数据是使用 FETCH语句，它可以把游标指向的行记录数据提取出来赋值给声明的变量，注意：FETCH语句只能取出当前行的记录，一般情况下， FETCH语句 都是搭配 循环语句 一起使用，直到某个条件不符合退出循环.

FETCH cursor_name INTO v_name;

4)、关闭游标

CLOSE cursor_name;

2、示例

示例1：
declare 
  CURSOR cur_product                                --1、声明游标
        is select * from product;              
  v_row_product      product%rowtype;               --声明product表的行变量     
 
begin
  OPEN cur_product;                                 --2、打开游标
       LOOP
          FETCH cur_product INTO v_row_product;     --3、读取数据放入行变量
       EXIT  WHEN cur_product%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE('商品名：'    ||  v_row_product.name  ||  '价格：' ||   v_row_product.price);
       END LOOP;
  CLOSE cur_product;                                --4、关闭游标  
end;


示例2：
declare 
  CURSOR cur_product
        is select name, price from product;
  v_name     product.name%type;
  v_price    product.price%type;
 
begin
  OPEN cur_product;
       LOOP
          FETCH cur_product INTO v_name, v_price;
       EXIT  WHEN cur_product%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE('商品名：'    ||  v_name  ||  '价格：' ||   v_price);
       END LOOP;
  CLOSE cur_product;
end;

3、显示游标的属性

显示游标的属性用于返回其执行信息，包括：
1）ISOPEN：获取游标是否打开，已打开返回true，没有打开返回false

IF cur_product%ISOPEN  THEN
    ...                            --游标已打开执行的语句
ELSE 
  OPEN cur_product;                --游标未打开则打开游标
END IF;

2）FOUND：检查是否从结果集中提取到了数据，提取到返回true，否则返回false

LOOP
    FETCH cur_product INTO v_name, v_price;
    IF cur_product%FOUND;
        DBMS_OUTPUT.PUT_LINE('商品名：'    ||  v_name  ||  '价格：' ||   v_price);
    ELSE
        EXIT;
    END IF;
END LOOP;

3）NOTFOUND：和FOUND相反，提取到数据返回false，未提取到数据返回true
4）ROWCOUNT：返回当前已经提取了多少行数据

4、使用 FETCH ... BULK COLLECT INTO 语句提取全部数据

declare 
  CURSOR cur_product 
         is select * from product;
 
  TYPE product_tab_type IS TABLE OF product%rowtype;
  product_tab  product_tab_type;

begin
  OPEN cur_product;
      FETCH cur_product BULK COLLECT INTO product_tab;       --将数据全部提取出来放入product_tab
      FOR i in 1..product_tab.count LOOP                     --循环遍历product_tab
           DBMS_OUTPUT.PUT_LINE('商品名：'    ||  product_tab(i).name  ||  '价格：' ||   product_tab(i).price);
      END LOOP;
  CLOSE cur_product;
  
end;

5、使用 FETCH ... BULK COLLECT INTO LIMIT语句提取部分数据

declare 
  CURSOR cur_product 
         is select * from product;
 
  TYPE product_tab_type IS TABLE OF product%rowtype;
  product_tab  product_tab_type;

begin
  OPEN cur_product;
       LOOP                                          --循环1：每次提取3行数据放入product_tab
           FETCH cur_product BULK COLLECT INTO product_tab LIMIT 3;
           FOR i in 1..product_tab.count LOOP        --循环2：遍历输出product_tab中的3行数据
               DBMS_OUTPUT.PUT_LINE('商品名：'    ||  product_tab(i).name  ||  '价格：' ||   product_tab(i).price);
           END LOOP;
               DBMS_OUTPUT.PUT_LINE('-------');
       EXIT WHEN cur_product%NOTFOUND;
       END LOOP;
  CLOSE cur_product;
  
end;

6、游标FOR循环

游标的使用大部分是为了迭代结果集，在PL/SQL中有一种更方便的循环游标的方式实现

declare 
  CURSOR cur_product 
         is select * from product;
 
begin
  FOR cur_info in cur_product      --将游标返回的数据放入cur_info ，该变量是%rowtype类型，并且无需声明
  LOOP
      DBMS_OUTPUT.PUT_LINE('商品名：'    ||  cur_info.name  ||  '价格：' ||  cur_info.price);
  END LOOP;
end;

这种方式简化了对游标的处理，使用这种情况时，Oracle会隐式地打开游标、提取数据、关闭游标.

7、带参数的游标

使用显示游标时可以指定参数，参数可以传递给游标在查询语句中使用.

参数游标的定义：
    cursor cursor_name              
        (param_name datatype, ...)
        is select_statement;        

示例：

declare 
  v_cid  product.cid%type := '1';  

  CURSOR cur_product
         (param_id varchar2)           --指定参数
         is select * from product where cid = param_id;    --使用参数
  
  v_row_product    product%rowtype; 

begin
  OPEN cur_product(v_cid);            --打开游标时传入参数
  LOOP
      FETCH cur_product INTO v_row_product;
      EXIT WHEN cur_product%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_row_product.name);
  END LOOP;
  CLOSE cur_product;
end;

三、隐式游标

1、隐式游标的特点

隐式游标是PL/SQL自动管理的，有以下特点：
1）隐式游标有默认名称：SQL
2）每当运行SELECT语句或者DML语句时，PL/SQL会打开一个隐式游标
3）隐式游标属性值始终是最新执行的SQL语句的

declare 
  v_name  product.name%type;  

begin
  select p.name into v_name from product p where p.pid = '1';
  IF SQL%FOUND THEN
     DBMS_OUTPUT.PUT_LINE('pid为666的商品名称为：' || v_name);
  END IF;
end;

2、隐式游标的属性

隐式游标属性名称和显式游标一样，不过其含义有区别
1）ISOPEN：由Oracle控制，永远返回false
2）FOUND：反应DML语句是否影响了数据，有影响时返回true，否则返回false；也可以反应SELECT INTO语句是否返回了数据，返回了数据则该属性值为true
3）NOTFOUND：和FOUND相反，DML语句没有影响数据或SELECT INTO语句没有返回数据时值为true，其它false
4）ROWCOUNT：反应DML语句影响数据的数量

四、REF游标

REF CURSOR是一个游标变量，当使用显式游标时，必须在定义部分指定其对应的select语句，而使用REF CURSOR时，可以在打开游标时指定其对应的select语句.

1、使用步骤

1)、定义REF CURSOR类型、游标变量

TYPE ref_type IS REF CURSOR [RETURN return_type];      --定义REF CURSOR类型
cur_name    type_type;                                 --声明游标变量

2)、打开游标

OPEN cur_name FOR select_statement;

3)、提取数据：和显式游标使用方法一样
4)、关闭游标：和显式游标使用方法一样

2、示例

declare 
  v_name  product.name%type;  

  TYPE ref_type IS REF CURSOR;
  cur_product ref_type;
  
begin
  OPEN cur_product FOR select p.name from product p;
  LOOP
       FETCH cur_product INTO v_name;
       EXIT WHEN cur_product%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  CLOSE cur_product;
end;

3、指定RETURN子句

如果在定义REF CURSOR类型时指定了RETURN 子句，那么在select_statement中返回的结果必须与RETURN 子句定义的记录类型匹配.

declare 
  v_row_product  product%rowtype;  

  TYPE ref_type IS REF CURSOR RETURN product%rowtype;
  cur_product ref_type;
  
begin
  OPEN cur_product FOR select * from product p;      --必须是product%rowtype类型
  LOOP
       FETCH cur_product INTO v_row_product;
       EXIT WHEN cur_product%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE(v_row_product.name);
  END LOOP;
  CLOSE cur_product;
end;