oracle 游标使用

游标简介：

     游标是PL&SQL中的一种控制结构。可以分为显式游标和隐式游标。pl&sql会为每一条select语句创建隐式游标。但是当我们需要处理多条数据时，我们就需要创建显式游标。

游标的属性：

1、%FOUND --判断游标中是否还有数据，若有，返回true，否则，返回false。
2、%NOTFOUND --与%FOUND 相反
3、%ISOPEN --判断游标是否为打开状态
4、%ROWCOUNT --记录已从游标中取出的记录数

为了以后方便首先我们创建一个测试用表及数据

create table T_ZOO
(
  ID   VARCHAR2(20),
  NAME VARCHAR2(20),
  AGE  VARCHAR2(3)
  );
insert into T_ZOO (ID, NAME, AGE)
values ('1', 'horse', '1');
insert into T_ZOO (ID, NAME, AGE)
values ('2', 'fox', '2');
insert into T_ZOO (ID, NAME, AGE)
values ('3', 'dog', '2');
insert into T_ZOO (ID, NAME, AGE)
values ('4', 'wolf', '4');
insert into T_ZOO (ID, NAME, AGE)
values ('5', 'tiger', '3');
insert into T_ZOO (ID, NAME, AGE)
values ('6', 'owl', '3');
insert into T_ZOO (ID, NAME, AGE)
values ('7', 'python', '4');

一、显式游标

显式游标创建方法；CURSOR cursor_name IS select_statement

1、For 循环使用游标，拿到参数的表中的每条数据然后进行操作

--创建一个存储过程
CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  declare
    --定义一个游标，内容为查询出所有测试表T_ZOO表中的数据
    cursor t_all is select  id,name,age from T_ZOO;
    --定义一个游标变量t_row t_all%ROWTYPE ，该类型为游标t_all中的一行数据类型
    t_row t_all%rowtype;
  begin
    --使用for循环来使用这个游标
    for t_row in t_all loop
      dbms_output.put_line('动物的id：' ||t_row.id || '，动物的名称：' || t_row.name || '，动物的年龄：' || t_row.age);
    end loop;
end;
end;

2、判断游标中是否有值

执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。%FOUND属性和%NOTFOUND返回的值完全相反

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
declare
        --定义一个游标，内容为查询出所有测试表T_ZOO表中的数据
        cursor t_all is select  id,name,age from T_ZOO;
        --定义一个游标变量t_row t_all%ROWTYPE ，该类型为游标t_all中的一行数据类型
       t_row t_all%rowtype;
begin
       --打开游标
       open t_all;
         loop
           --提取一行数据到t_row
           fetch t_all into t_row;
           --判读是否提取到值，没取到值就退出，取到值t_all%notfound 是false，取不到值t_all%notfound 是true
           exit when t_all%notfound;
             dbms_output.put_line('数据在表中的行数'||t_all%ROWCOUNT||'，动物的id：' ||t_row.id || '，动物的名称：' || t_row.name || '，动物的年龄：' ||t_row.age);
         end loop;
       --关闭游标
      close t_all;
end;
end;

3.使用游标和while循环来显示所有游标中的值（用%found属性）

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  declare
    --游标声明
    cursor t_all is
    --select语句
      select name from t_zoo;
    --指定行指针
    t_row t_all%rowtype;
  begin
    --打开游标
    open t_all;
    --给第一行赋值数据
    fetch t_all into t_row;
    --测试是否有数据，并执行循环
    while t_all%found loop
      dbms_output.put_line('动物名称：' || t_row.name);
      --给下一行赋值数据
      fetch t_all  into t_row;
    end loop;
    close t_all;
  end;
end;

4,接收用户输入的动物年龄，用for循环和游标，打印出此年龄的所有动物的所有信息(使用循环游标)

--定义的语法 CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
--创建游标，使其可以接受用户输入的值
  declare
    CURSOR t_all(p_deptNo number) is
      select * from t_zoo where t_zoo.age = p_deptNo;
    t_row t_zoo%rowtype;
  begin
  --调游标时输入差数进行调度
    for t_row in t_all(2) loop
      dbms_output.put_line('动物的id：' || t_row.id || '动物的名称：' || t_row.name ||
                           '动物的年龄：' || t_row.age);
    end loop;
  end;
end;

5.用游标更新动物年龄

CREATE OR REPLACE PROCEDURE P_TEST AS 
BEGIN
 declare
        cursor t_all is
        select * from  t_zoo for update OF age;
        t_row t_all%rowtype;
        t_age  t_zoo.age%TYPE;
begin
    FOR t_row IN t_all LOOP
      IF t_row.age<=1 THEN
        t_age:=t_row.age+1;
       elsif t_row.age>1 THEN
        t_age:=t_row.age-1;
      END IF;
    --CURRENT OF 用于update 和 delete，你要保证在你进行操作的时候，别人不能对这个数据进行操作。
      UPDATE t_zoo SET age=t_age WHERE CURRENT OF t_all;
     END LOOP;
END;
end;

6.用游标查询出动物年龄最大的两个（可以定义一个变量作为计数器控制游标只提取两条数据，也可以在申明游标时把年龄最大的两个动物查询出来放到游标中）

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  declare
    cursor t_all is
      select * from t_zoo order by age asc;
    --计数器
    top_two number := 2;
    t_row   t_all%rowtype;
  begin
    --打开游标
    open t_all;
    FETCH t_all
      INTO t_row;
    while top_two > 0 loop
      dbms_output.put_line('动物名称：' || t_row.name || ' 年龄：' || t_row.age);
      --计速器减一
      top_two := top_two - 1;
      FETCH t_all
        INTO t_row;
    end loop;
    --关闭循环
    close t_all;
  end;
end;

二、隐式游标

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为： SQL%
注：INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。 

1.观察更新数据是否已经成功

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  --1：任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen 观察update语句的执行情况。
  begin
    update t_zoo set age = 1 WHERE name = 'python';
    --判断游标是否为关闭状态，不管执行结果是什么，游标都为关闭状态
    if sql%isopen then
      dbms_output.put_line('游标为打开状态');
    else
      dbms_output.put_line('游标为关闭状态');
    end if;
    --判断游标是否执行
    if sql%found then
      dbms_output.put_line('游标指向了有效行');
    else
      dbms_output.put_line('对不起没有执行');
    end if;
    --判断语句执行是否成功
    if sql%notfound then
      dbms_output.put_line('对不起没有执行');
    else
      dbms_output.put_line('执行成功');
    end if;
    --打印出一共执行了多少条记录
    dbms_output.put_line(sql%rowcount);
    --抛出异常信息
  exception
  --no_data_found判断是否有数据，如果没有抛出异常
    when no_data_found then
      dbms_output.put_line('对不起，没有数据');
   --too_many_rows判断是否存在多条数据，如果有抛出异常
    when too_many_rows then
      dbms_output.put_line('对比起，行数过多');
  end;
end;

2.查询中的隐式游标

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  declare
    zooname t_zoo.name%TYPE;
  begin
    --判断游标是否打开
    if sql%isopen then
      dbms_output.put_line('第一个判断：游标打开状态');
    else
      dbms_output.put_line('第一个判断：游标关闭状态');
    end if;
    --判断是否有查询结果，由于没有查询所以zooname为null
    if sql%notfound then
      dbms_output.put_line('第一个判断：没有值');
    else
      dbms_output.put_line(zooname);
    end if;
    --得到游标得到的行数，由于没有sql所有查询出来的值为null
    dbms_output.put_line(sql%rowcount);
    --把查询出来的结果赋值给zooname，age=1时报查询值过多的错误age=4时为正常age=5时为没有查询到结果的错误
    select name into zooname from t_zoo where age = 1;
    --查看得到的行数
    dbms_output.put_line(sql%rowcount);
    --判断游标是否打开，不管sql是否执行和有无游标都没有打开
    if sql%isopen then
      dbms_output.put_line('游标打开状态');
    else
      dbms_output.put_line('游标关闭状态');
    end if;
    --打印出得到的值
    if sql%notfound then
      dbms_output.put_line('没有值');
    else
      dbms_output.put_line(zooname);
    end if;
  exception
    when no_data_found then
      dbms_output.put_line('因为sql没有查询出值，所以报错');
    when too_many_rows then
      dbms_output.put_line('因为sql查询出多个值，所以报错');
  end;
end;

三、游标变量 

首先 , 游标变量和游标是两个不同的概念 . 与游标相似 , 游标变量是指向多行查游标询的结果集的当前行 . 游标是静态的 , 游标变量是动态的 ,. 同时游标变量并不参与与特定的查询绑定 , 所以可以为任何兼容的查询打开游标变量 , 从而提高灵活性 . 而且 , 还可以将新的值赋予游标变量 , 将它作为参数传递给本地和存储过程 . 游标变量针对每个 PL/SQL 用户都是可用的 , 可以在客户端完全使用游标变量 .ORACLE 服务器同样包含 PL/SQL 引擎 , 可以将游标变量在应用和服务器之间进行传递 . 与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域。

1、定义 REF CURSOR 类型

语法格式为：
TYPE ref_type_name IS REF CURSOR  [ RETURN return_type];

其中：ref_type_name为新定义的游标变量类型名称；
return_type 为游标变量的返回值类型，它必须为记录变量。
在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，
而弱类型定义则不说明返回值类型。
声明一个游标变量的两个步骤：
步骤一：定义一个REF CURSOU数据类型，如：
TYPE ref_cursor_type IS REF CURSOR;
步骤二：声明一个该数据类型的游标变量，如：
cv_ref REF_CURSOR_TYPE
创建两个强类型定义游标变量和一个弱类型游标变量：

DECLARE
     TYPE deptrecord IS RECORD(
         Deptno departments.department_id%TYPE,
         Dname departments.department_name%TYPE,
         Loc departments.location_id%TYPE
     );
     TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;
     TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
     TYPE curtype IS REF CURSOR;
     Dept_c1 deptcurtype;--强类型游标变量
     Dept_c2 deptcurtyp1;--强类型游标变量
     Cv curtype;--弱类型游标变量

与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。 

1． 打开游标变量
打开游标变量时使用的是OPEN…FOR 语句。格式为：
OPEN {cursor_variable_name | :host_cursor_variable_name} FOR select_statement;
其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。
OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。
2． 提取游标变量数据
使用FETCH语句提取游标变量结果集合中的数据。格式为：
FETCH {cursor_variable_name | :host_cursor_variable_name} INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称。
3． 关闭游标变量
CLOSE语句关闭游标变量，格式为：CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  DECLARE
    TYPE t_parameter IS RECORD(
      t_id   t_zoo.id%TYPE,
      t_name t_zoo.name%TYPE);
    --申明一个强类型游标 start----
    TYPE q_type_avow IS REF CURSOR RETURN t_parameter;
    q_type q_type_avow;
    q_job  t_parameter;
    --申明一个强类型游标 end----
    --申明一个弱类型游标 start----
    Type r_type_avow IS REF CURSOR;
    r_type r_type_avow;
    r_job  t_parameter;
    --申明一个弱类型游标 end----
  BEGIN
    ---使用强类型 start
    --打开游标变量
    OPEN q_type FOR
      SELECT t_zoo.id, t_zoo.name FROM t_zoo;
    FETCH q_type
      INTO q_job;
    WHILE q_type%FOUND LOOP
      DBMS_OUTPUT.PUT_LINE(q_job.t_id || '名称: ' || q_job.t_name);
      FETCH q_type
        INTO q_job;
    END LOOP;
    CLOSE q_type;
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    --使用强类型 end
    ---使用弱类型 start
    OPEN r_type FOR
    
      SELECT t_zoo.id, t_zoo.name FROM t_zoo;
    FETCH r_type
      INTO r_job;
    WHILE r_type%FOUND LOOP
      DBMS_OUTPUT.PUT_LINE(r_job.t_id || '名称: ' || r_job.t_name);
      FETCH r_type
        INTO r_job;
    END LOOP;
    CLOSE r_type;
    ---使用弱类型 end
  END;
end;

1.使用游标变量（没有RETURN子句）

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  DECLARE
    --定义一个游标数据类型
    TYPE r_type_avow IS REF CURSOR;
    --声明一个游标变量
    r_type r_type_avow;
    --声明两个记录变量
    r_zoo_row t_zoo%ROWTYPE;
    r_zoo_row1 t_zoo%ROWTYPE;
  BEGIN
    OPEN r_type FOR
      SELECT * FROM t_zoo;
    LOOP
      FETCH r_type
        INTO r_zoo_row;
      EXIT WHEN r_type%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(r_zoo_row.name || 'id是：' ||
                           r_zoo_row.id|| '年龄是：' || r_zoo_row.age);
    END LOOP;
    --将同一个游标变量对应到另一个SELECT语句
    OPEN r_type FOR
       SELECT * FROM t_zoo;
    LOOP
      FETCH r_type
        INTO r_zoo_row1;
      EXIT WHEN r_type%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(r_zoo_row1.name || '年龄：' ||
                           r_zoo_row1.age);
    END LOOP;
    CLOSE r_type;
  END;
end;

2使用游标变量（有RETURN子句）

CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
  DECLARE
 --定义一个与employees表中的这几个列相同的记录数据类型
    TYPE t_parameter IS RECORD(
         t_name   t_zoo.name%TYPE,
         t_age   t_zoo.age%TYPE,
         t_id     t_zoo.id%TYPE);
 --声明一个该记录数据类型的记录变量
    q_job t_parameter;
 --定义一个游标数据类型
    TYPE q_type_avow IS REF CURSOR
         RETURN t_parameter;
 --声明一个游标变量
    q_type q_type_avow;
 BEGIN
    OPEN q_type FOR SELECT name,age,id FROM t_zoo;
    LOOP
       FETCH q_type INTO q_job;
       EXIT WHEN q_type%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('动物名称：'||q_job.t_name
                 ||'  动物id：'||q_job.t_id
                 ||'  动物年龄：'||q_job.t_age);
    END LOOP;
      CLOSE q_type;
 END;
end;