[大数据]PLSQL——游标（显式游标、隐式游标、REF游标）

1.游标简介

游标逐行处理查询结果，以编程的方式访问数据
它实际上是一个指针，它在一段Oracle存放数据查询结果集或数据操作结果集的内存中(数据的缓存区)，游标就是指向这个缓冲区的一个指针

2.游标的类型

1，隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql。 (固定存在的)
2，显式游标：显式游标用于处理返回多行的查询。
3，REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

3.游标的属性

%found:返回一个布尔类型的值，如果游标指向的数据不为空，那么返回true，否则返回false
%notfound:和%found相反，指向的数据是空，返回true
%rowcount:它可以表示游标指向的缓冲区（结果集）的数据条数
%isopen:返回一个布尔类型的值，判断当前游标是否打开，如果是打开的返回true，否则返回false

注：boolean布尔类型：只有三个值，true,false,null

4.显式游标

显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
显式游标，就是自己定义的游标
显式游标的使用：
（1）游标变量的声明
cursor 游标名 is select语句; （指向集而并非第一条数据）

cursor cur_emp is select * from emp;

(2)打开游标
open 游标名称;
open cur_emp;
(3)fetch into 当前游标指向下一条数据并把数据保存在一个变量中
fetch 游标变量 into 变量
fetch cur_emp into v_emp;
(4)关闭游标
close 游标名称
close cur_emp;

如：

--使用游标打印所有员工信息
declare
   --声名一个游标变量，指向所有员工的信息select * from emp;
   cursor c_emp is select * from emp;
   --声名一个emp%rowtype类型的变量用来保存游标中的一条记录
   v_emp emp%rowtype;
begin
   --打开游标
   open c_emp;
   --遍历游标
   loop
     --fetch into游标指向下一条数据，并保存数据到变量v_emp中
     fetch c_emp into v_emp;
     --判断游标是否还有数据，如果没有数据，就退出循环
     exit when c_emp%notfound;
     --把这条员工信息打印出来
     dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
   end loop;
   --关闭游标
   close c_emp;
end;

--使用游标打印所有员工的信息
declare
   --声名一个游标变量指向所有员工的集合
   cursor c_emp is select * from emp;
   --声名一个变量用来保存游标的一条记录
   v_emp emp%rowtype;
begin
   --打开游标
   open c_emp;
   --执行一次fetch into 让游标指向第一条数据
   fetch c_emp into v_emp;
   --while循环，条件是循环条件
   while c_emp%found loop
      --打印员工信息
      dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
      --fetch into将游标指向下一条数据
      fetch c_emp into v_emp;
   end loop;
   dbms_output.put_line(c_emp%rowcount||'');
   --关闭游标
   close c_emp;
end;

for循环会自动打开游标、关闭游标，并且自动会让游标指向一条记录
--使用游标打印所有员工的信息
declare
   --声名一个游标变量指向所有员工的集合
   cursor c_emp is select * from emp;
begin
   for v_emp in c_emp loop
       --打印员工信息
       dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno||','||c_emp%rowcount);
   end loop;
end;	

带参数的显式游标
声明显式游标时可以带参数以提高灵活性
语法：
cursor 游标名(参数名 数据类型,参数名 数据类型,…) is select 语句;

使用 ：open 游标名(参数,参数…);
declare

--声名一个游标，输入一个部门编号，打印该部门的员工信息
  cursor c_emp(v_dno emp.deptno%type) is select * from emp where deptno=v_dno;
  --声名一个rowtype类型变量保存游标中的一条记录
  v_emp emp%rowtype;
begin
  --打开游标
  open c_emp(10);
  loop
    --fetch into
    fetch c_emp into v_emp;
    --判断游标当前数据是否为空，如果为空退出循环
    exit when c_emp%notfound;
    --打印员工信息
    dbms_output.put_line(v_emp.ename||','||v_emp.job||','||v_emp.sal||','||v_emp.deptno);
  
  end loop; 
  --关闭游标
  close c_emp;
end; 

declare
    cursor c(dno number) is select * from emp where deptno=dno;
begin
    for x in c(&dno) loop
        dbms_output.put_line(x.ename);
        dbms_output.put_line(c%rowcount);
    end loop;
    --for 自动关闭 for循环结束之后 end loop;
end;

使用显式游标更新行
允许使用游标删除或更新活动集中的行
声明游标时必须使用 SELECT … FOR UPDATE语句

declare
  v_emp emp%rowtype;
  cursor mycursor is select * from emp where empno = 7369 for update;
begin
  open mycursor;
  fetch mycursor into v_emp;
  while mycursor%found loop
    --where current of游标名; 对游标读取的所在行进行更新和删除
    update emp set ename = 'tom' where current（现在） of mycursor;
    fetch mycursor into v_emp;
  end loop;
  close mycursor;
end;

5.隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN - 游标是否打开，始终为FALSE
如：

declare
  v_emp emp%rowtype;
begin
   update emp set comm=100 where deptno=&deptno;
   dbms_output.put_line('修改的数据条数：'||sql%rowcount);
   if sql%found then
       dbms_output.put_line('aaaaaaaaaaaaaaa');
   end if;
   delete from emp where deptno=&dno;
   dbms_output.put_line('删除了'||sql%rowcount||'条数据'); 
end;

6.REF游标

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
创建游标变量需要两个步骤：
1.声明 REF 游标类型
语法：type 类型名称 is ref cursor;

2.声明 REF 游标类型的变量
语法 变量名称 游标变量类型;

使用： open 游标变量 for select 语句;

如：

--查询所有的员工信息
declare
  --声明 REF 游标类型
  type c_type① is ref cursor;
  --声名REF游标类型变量
  cur② c_type①;
  --声名一个变量保存emp表的信息
  v_emp③ emp%rowtype;（用来接收）
begin
  open cur② for (指向作用)select * from emp;
（可同时打开多个游标for不同的值）
  loop
     fetch cur② into v_emp③;将游标赋予到③身上	
     exit when cur②%notfound;
     dbms_output.put_line(v_emp③.ename);
  end loop;
  close cur②;
end;