PL/SQL中游标的使用

一、游标的概念：
        游标是PL/SQL中的一种对象，开发者借助游标可以对查询语句返回的多条记录进行逐条处理，另外还可以通过游标获取语句的执行状态或执行结果！

二、游标的分类：
     (1)显示游标：需开发者自行定义方能使用。当select语句返回的记录数量不定时，就需要使用显示游标！
     (2)隐式游标：无需开发者自行定义，由Oracle自动创建。此游标专门用于获取update、insert、delete(DML操作)语句的执行状态，也成为SQL游标；

三、显示游标的用法：

◆游标使用的基本格式:
   (1)定义游标的语法格式：
    cursor cursor_name is select_statement;
    cursor_name 表示游标名；Oracle建议游标名带_cursor后缀;
        select_statement 表示返回多条记录的查询语句。
   (2)打开游标：
     open cursor_name;
   (3)从游标中提取记录：
      fetch cursor_name into variableList;         //一条一条记录的提取
      variableList表示变量列表，变量的个数和数据类型必须和声明游标时的子查询一致;也可以使用记录型变量来保存每一条记录中的值。
   (4)关闭游标：
    close cursor_name;

◆获取游标的状态：
  (1)cursor_name%notfound 返回数据提取状态，如为提取数据则返回true;
  (2)cursor_name%found 如果通过游标提取到了记录则返回true;
  (3)cursor_name%isopen 游标是否处于打开状态；
  (4)cursor_name%rowcount 到当前为止通过游标所提取的记录行数。
  
【示例】使用游标插叙并输出prodcuts表中的产品类别为1的所有产品信息
declare
       --定义游标
       cursor v_prd_cursor is select * from products where product_type_id = 1;
       
       --定义行类型的变量，存储产品表中的每一行记录
       v_prd_record products%rowtype;
begin
     --打开游标
     open v_prd_cursor;
     
     --通过游标，提取数据
     loop
         --游标游标，遍历其缓冲区中的记录
         fetch v_prd_cursor into v_prd_record;
         if v_prd_cursor%notfound then
            exit;
         end if;
 dbms_output.put_line(v_prd_record.product_id||','||v_prd_record.product_type_id||','||v_prd_record.description||','||v_prd_record.price);
      --dbms_output.put_line('rowcount:'||v_prd_cursor%rowcount);
     end loop;
     dbms_output.put_line('rowcount:'||v_prd_cursor%rowcount);
     --关闭游标
     close v_prd_cursor;
end;

◆定义游标时，子查询where子句中可以使用PL/SQL变量，通过在程序中修改该变量的值，以获取不同的查询结果

【示例】使用同一个游标分别检索产品类别为1的产品，类别为2的产品
declare
       --定义产品的编号
       v_prd_type_id int;
        --定义行类型的变量，存储产品表中的每一行记录
        v_prd_record products%rowtype;
       --定义游标
       cursor v_prd_cursor is select * from products where product_type_id = v_prd_type_id; 
begin
     --给其产品编号赋值
     --v_prd_type_id := 1;
     v_prd_type_id := 2;
     --打开游标
     open v_prd_cursor;
     --通过游标，提取数据
     loop
         fetch v_prd_cursor into v_prd_record;
         if v_prd_cursor%notfound then
            exit;
         end if;
         dbms_output.put_line(v_prd_record.product_id||','||v_prd_record.product_type_id||
         ','||v_prd_record.description||','||v_prd_record.price);
     end loop;
     dbms_output.put_line('rowcount:'||v_prd_cursor%rowcount);
     --关闭游标
     close v_prd_cursor;
end;
注意：试图打开一个已打开的游标或关闭一个已关闭的游标，都会出现错误！

四、游标for循环：
◆因为经常通过游标循环提取记录，所有Oracle支持使用如下简单循环形式：
  格式：for v_prd_record in v_product_cursor loop
              --处理数据
        end loop;
 注意： 在这种循环结构中，Oracle会自动的打开游标、提取数据、最后关闭游标！
  
  v_product_record 是可以以如下方式定义:
  v_prd_record v_product_cursor%rowtype;(解决了游标中的属性列和记录类型变量中的属性列数量和类型不一致的情况)
  /*rowtype使用游标，获取游标返回的行类型*/
  
【示例】使用 for in 循环结构输出产品类别为 2 的产品信息
--游标的打开、关闭Oracle自动关闭
declare
       --定义游标
       cursor v_prd_cursor is select p.product_id,p.product_type_id,p.price from products p where p.product_type_id = 2;
       --定义记录类型的变量
       v_prd_record v_prd_cursor%rowtype;
begin
       for v_prd_record in v_prd_cursor loop
         dbms_output.put_line(v_prd_record.product_id||','||v_prd_record.product_type_id||
         ','||v_prd_record.price);
       end loop;
end;

五、隐式游标的使用:
◆在执行一个DML语句时，Oracle会自动定义一个隐式游标，该游标名为SQL。
 事务提交Oracle和MySQL中的区别： Oracle执行DML语句,事务是手动提交的，而mySQL中的DML语句，事务的自动提交！
◆该隐式游标的属性：
    (1)SQL%rowcount 返回DML语句影响的记录条数;
    (2)SQL%found 返回有记录被影响;
    (3)SQL%notfound 返回没有记录被影响.
    
使用循环控制更新更多条记录，最后输出更新的记录总数
【示例】如果产品类别为 1，且价格低于15块，则提价10%；如果产品价格为 2，且价格低于20块，则提价5%
declare
       --定义游标
       cursor v_prd_cursor is select product_id,product_type_id,price from products;
       
       --定义游标记录型变量
       v_prd_record v_prd_cursor%rowtype;
       
       --统计更新的记录数
       v_count int := 0;
begin
       for v_prd_record in v_prd_cursor loop
           if v_prd_record.product_type_id = 1 and v_prd_record.price < 15 then
              update products set price = price*1.1 where product_id = v_prd_record.product_id;
              v_count := v_count+SQL%rowcount;      --使用隐式游标，SQL%rowcount获取update语句影响的记录条数(此时为1)
           elsif v_prd_record.product_type_id = 2 and v_prd_record.price < 20 then
              update products set price = price*1.05 where product_id = v_prd_record.product_id;
              v_count := v_count+SQL%rowcount;      --使用隐式游标，SQL%rowcount获取update先前语句影响的记录条数(此时为1)  
           end if;   
       end loop;
       commit;  --事务控制语句,提交事务；rollback事务回滚，savepoint设置事务的保存点！
       dbms_output.put_line('v_count='||v_count);
end;


【示例】 将product_changes表中的数据合并到 products表中,最后输出插入和更新的记录数
解释：将A表中的数据插入B表中，记录一条一条操作，若B表中存在和A表中的主键一致，则只需更新操作，否则采用插入操作。
declare
       --定义游标
       cursor v_prd_cursor is select * from product_changes;
       --定义一个游标行类型的变量
       v_prd_record v_prd_cursor%rowtype;
       --定义变量，保存检索的结果
       v_count int := 0;
       --定义更新，插入的变量
       v_insert int := 0;
       v_update int := 0;
begin
       --遍历游标
       for v_prd_record in v_prd_cursor loop
          select count(*)
          into v_count
          from products
          where product_id = v_prd_record.product_id; 
          if v_count = 0 then 
             insert into products values(v_prd_record.product_id,v_prd_record.product_type_id,v_prd_record.name,v_prd_record.description,v_prd_record.price);
             v_insert := v_insert + SQL%rowcount;  --隐式游标来判断上条sql语句影响记录条数
          else
              update products set product_type_id = v_prd_record.product_type_id,
                                  name = v_prd_record.name,
                                  description = v_prd_record.description,
                                  price = v_prd_record.price
              where product_id = v_prd_record.product_id;
              v_update := v_update + SQL%rowcount;  --隐式游标来判断上条sql语句影响记录条数
          end if;
       end loop;
       commit;  --处理完毕，事务提交;
       dbms_output.put_line('v_insert='||v_insert||',v_update'||v_update);
end;


六、借助显示游标执行DML操作：
◆如果要借助显示游标更新或删除记录，在定义游标时须带有for update 子句：
   cursorcursor_name is select_query for update[nowait];
   for update 的功能：打开游标时会对查询到的记录行上加锁，防止别的用户在这些行上执行DML操作。
   nowait 选项是指如果加锁失败，直接抛出错误。


◆更新或删除游标所指向的当前行，使用where current of cursor_name子句。
--利用显示游标更新记录
--【示例】更新产品表中的数据，如果产品价格低于15元，则提价30%；如果价格低于40元，则提价10%；其余不变。
declare
      --定义游标
      cursor  v_prd_cursor is select price from products for update;  --给其查询的结果进行加锁，不允许其他的DML操作来更新这些记录
      
      --定义变量
      v_prd_record v_prd_cursor%rowtype;
begin
     for v_prd_record in v_prd_cursor loop
         if v_prd_record.price < 15 then   --where current of cursor_name可减少使用主键列
            update products set price = price*1.3 where current of v_prd_cursor;
         elsif v_prd_record.price < 40 then
            update products set price = price*1.1 where current of v_prd_cursor;
         end if;
     end loop;
     commit;  --事务的提交
end;

注意：任何的DML操作在更新完Oracle中的记录时，若没有提交事务，就会默认在此记录上加锁;别的用户在此记录上若再次更新操作，就只能等待啦！
--Oracle中的隔离级别默认为 read commited只读取另一个用户提交的数据，只能等到先前用户事务提交时，释放锁才可以提交事务！