Oracle学习笔记day09——游标

一、概述

游标（Cursor）是Oracle系统在内存中开辟的一块工作区，在该工作区中存放查询语句返回的结果集。
结果集可以包含零条数据记录、一条数据记录，也可以是多条数据记录。在定义游标所在的工作区中，存在一个指针，在初始状态下，游标指针指向查询结果集的第一条数据记录的位置。当执行FETCH语句提取数据记录后，游标指针将向下移动一个数据记录的位置。

二、游标的作用

从结果集的当前位置检索一行数据记录；
支持对结果集的当前数据记录进行数据更新；
使用游标注意事项：
①.在创建游标时，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该避免使用游标；
②.如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作；

三、游标分类

①.Oracle中的游标分为显示游标,隐式游标,REF游标。
②.当查询返回的结果集超过一条数据记录时，就需要一个显式游标，此时用户不能使用SELECT INTO语句。显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开、提取和关闭。
③.PL/SQL管理隐式游标，当执行DML操作时隐式游标打开，结束时隐式游标自动关闭。
④.REF 游标用于处理运行时才能确定的动态 SQL 查询的结果。

四、游标使用过程和属性

①.建立游标的过程

1．声明游标
    声明游标，就是使一个游标与一条查询语句建立联系。	
    DECLARE
    CURSOR <游标名>[（参数1 数据类型[，…n]）] IS 查询语句
    [FOR UPDATE [OF [用户方案.]<表名>.<列名> [，…n]]];
2．打开游标
    就是执行游标定义时所对应的查询语句，并把查询返回的结果集存储在游标对应的工作区中。 
    OPEN <游标名>[（参数1 [，…n]）];
3．提取游标数据
    就是从定义游标的工作区中检索一条数据记录作为当前数据记录。
    FETCH <游标名> INTO 变量1 [，… n]
4．关闭游标
    CLOSE <游标名>;

②.游标的重要属性

%FOUND – SQL 语句影响了一行或多行时为 TRUE
%NOTFOUND – SQL 语句没有影响任何行时为TRUE
%ROWCOUNT – SQL 语句影响的行数
%ISOPEN  - 游标是否打开，始终为FALSE

五、游标详解

①.隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标，隐式游标自动声明、打开和关闭，其名为 SQL。通过检查隐式游标的属性可以获得最近执行的DML 语句的信息。

--%found
--查询emp表中有没有编号为7788的记录
declare
emp_row emp%rowtype;
begin
  select * into emp_row from emp where empno=7788;
  if(sql%found) then   --注意:sql是隐身游标默认游标名
      dbms_output.put_line('找到数据');
  else
      dbms_output.put_line('没有找到数据');
  end if;
end;
--%notfound
declare
emp_row emp%rowtype;
begin
  select * into emp_row from emp where empno=7788;
  if(sql%notfound) then
      dbms_output.put_line('没有找到数据');
  else
      dbms_output.put_line('找到数据');
  end if;
end;
--%rowcount
--%isopen
--将部门编号为30的员工都加薪100
begin
  update emp set sal=sal+100 where deptno=30;
  dbms_output.put_line('受影响的行数:'||sql%rowcount);
  if(sql%isopen) then
       dbms_output.put_line('游标已经打开');
  else
       dbms_output.put_line('游标没有打开');--隐式游标都是关闭的
  end if;
end;

②.显示游标

虽然在DML操作中，PL/SQL块中会创建隐式游标，但是如果在进行查询操作并赋值给标量类型的变量时，如果返回行超过1行，此时就需要应用显示游标。
显示游标需要先定义游标，再经历打开，提取，关闭整个过程。

--显示游标
--迭代游标 loop
--查询出部门编号为30的员工信息
Declare
--声明游标时与查询语句关联
cursor emp_cursor is select * from emp where deptno=30;
emp_row emp%rowtype;
begin
  open emp_cursor;--开启游标
  loop
      --fetch一次，游标下移一次
      fetch emp_cursor into emp_row;
      exit when emp_cursor%notfound;
      dbms_output.put_line('部门编号:'||emp_row.deptno||',姓名:'||emp_row.ename||',工资:'||emp_row.sal);
  end loop;
  close emp_cursor;--关闭游标
end;

--迭代游标 For
/*
For循环游标隐式打开游标，自动滚动获取一条记录，并自动创建临时记录类型变量存储记录。处理完后自动关闭游标。
*/
declare
cursor emp_cursor is select * from emp where deptno=30;
begin
  for emp_row in emp_cursor
    loop
      dbms_output.put_line('部门编号:'||emp_row.deptno||',姓名:'||emp_row.ename||',工资:'||emp_row.sal);
    end loop;
end;

③.ref游标

REF 游标是一个动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
REF游标和游标变量用于处理运行时动态执行的 SQL 查询。
创建游标变量需要两个步骤：

a.声明 REF 游标类型；
b.声明 REF 游标类型的变量；

用于声明 REF 游标类型的语法为：

TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];

REF 游标类型分为强类型和弱类型，由是否设置了return 类型决定。

a.强类型：设置了return,要求REF游标变量必须与REF游标类型一致。
b.弱类型：没有设置return,REF游标变量可以为任意类型。

静态游标和REF 游标的区别是什么?

1.静态游标是静态定义，REF 游标是动态关联；
2.使用REF 游标需REF 游标变量。
3.REF 游标能做为参数进行传递，而静态游标是不可能的。

弱类型REF变量案例:

--根据部门编号不同，执行不同的查询【输入部门编号查部门员工信息，如果没有编号，查询全部员工信息】
Declare
--定义REF游标类型
type my_ref_cursor is ref cursor;
--定义REF游标类型变量
emp_cursor my_ref_cursor;
v_emp emp%rowtype;
v_name emp.ename%type;
v_deptno emp.deptno%type :=&deptno;
begin
  if v_deptno is not null then
    --emp_cursor游标指向只查询ename的结果集
    open emp_cursor for select ename from emp where deptno=v_deptno;
    loop
         fetch emp_cursor into v_name;
         exit when emp_cursor%notfound;
         dbms_output.put_line('姓名:'||v_name);
    end loop;
  else
    --emp_cursor游标指向查询所有信息的结果集
    open emp_cursor for select * from emp;
    loop
         fetch emp_cursor into v_emp;
         exit when emp_cursor%notfound;
         dbms_output.put_line('部门编号:'||v_emp.deptno||'，姓名:'||v_emp.ename||'，待遇:'||v_emp.sal);
    end loop;
  end if;
  close emp_cursor;
end;

强类型REF变量案例：

Declare
--定义REF游标类型
type my_ref_cursor is ref cursor return emp%rowtype;
--定义REF游标类型变量
emp_cursor my_ref_cursor;
v_emp emp%rowtype;
v_deptno emp.deptno%type :=&deptno;
begin
  if v_deptno is not null then
    open emp_cursor for select * from emp where deptno=v_deptno;
  else
    open emp_cursor for select * from emp;
  end if;
  loop
         fetch emp_cursor into v_emp;
         exit when emp_cursor%notfound;
         dbms_output.put_line('部门编号:'||v_emp.deptno||'，姓名:'||v_emp.ename||'，待遇:'||v_emp.sal);
  end loop;
  close emp_cursor;
end;

游标变量的功能强大，可以简化数据处理。

1)游标变量的优点有：

①.可从不同的 SELECT 语句中提取结果集【定义时不指向select语句】

②.可以作为过程的参数进行传递

③.可以引用游标的所有属性

④.可以进行赋值运算

2)使用游标变量的限制：

①.不能在程序包中声明游标变量

②.FOR UPDATE子句不能与游标变量一起使用

③.不能使用比较运算符