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 ASBEGIN  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 ASBEGINdeclare        --定义一个游标，内容为查询出所有测试表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 ASBEGIN  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 ASBEGIN--创建游标，使其可以接受用户输入的值  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 ASBEGIN  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 ASBEGIN  --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 ASBEGIN  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 ASBEGIN  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 ASBEGIN  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 ASBEGIN  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;