oracle--游标

游标简介

游标类似于指针，开发者可以首先获取一个记录集合，并将其封装于游标变量中。游标变量利用自身的属性，来实现记录的访问。例如，初始化的游标变量总是只想结果集合中的第一条记录。当游标下移时，便指向“当前记录”的下一条记录，如此循环。

针对每条记录，游标也提供了访问记录中各列的方式，从而将访问的粒度细化到数据表的原子单位。游标的使用总是在pl/sql编程环境中。

游标的主要类型分为静态游标和动态游标。而静态游标是常用的游标。静态游标又分为显式游标和隐式游标。但是，无论哪种游标，只是在开发过程中的使用方式不同，其实现原理都是完全相同的。

显式游标

显式游标是指开发人员显式声明和定义的游标。显示游标的好处在于开发人员可以控制游标的整个生命周期。

声明显式游标

declare cursor 游标名称 is 查询语句

delcare cursor用于声明游标，查询语句指定游标中所封装的记录集合，游标名称与查询语句之间使用is关键字进行连接

声明列变量

declare cursor cu_employee is select employee_id,employee_name,employee_age from employees;

employee_id employees.employee_id%type;
employee_name employees.employee_name%type;
employee_age employees.employee_age%type;

employees.employee_id%type代表数据类型为表employees的列employee_id的数据类型，这种方式灵活声明，再表的字段类型更改时，这里就不需要更改

声明行变量

declare cursor cu_employee is select * from employees;

employee employees%rowtype;

employee为变量名；employees%rowtype指定该变量的数据类型为表employee的行类型

使用显式游标

使用游标应追寻打开游标、通过游标访问数据、关闭游标的步骤。只有打开游标时，游标定义中的sql查询语句才被执行，后续的访问游标动作才可以操作记录集合。

open 游标名称 -- 打开游标

fetch 游标名称 into 变量 -- 通过游标捕获数据

使用普通变量获取游标信息

declare cursor cu_employee is select employee_id,employee_name,employee_age from employees;
employee_id number;
employee_name varchar2(20);
employee_age number;
begin
    open cu_employee;
    fetch cu_employee into employee_id,employee_name,employee_age;
    while cu_employee%found loop
        dbms_output.put_line(employee_id || ':' || employee_name || ':' employee_age);
        fetch cu_employee into employee_id,employee_name,employee_age;
    end loop;
end;

结果：

1：王潇：35
2：钟晓平：30

open cu_employee用于打开游标cu_employee，在打开的同时，查询语句执行，并将结果集合封装于游标变量中，fetch cu_employee into employee_id,employee_name,employee_age;捕获当前记录并存储到变量中，变量的顺序必须和select的顺序一致。while cu_employee%found loop用于循环执行某个动作，条件是游标变量捕获成功，以end loop结束；再循环内部，打印各列的值，并再次用fetch语句捕获新记录

使用行类型变量获取游标信息

也可以使用行类型的变量来代替多个变量

declare cursor cu_employee is select * from employees;
employee employees%rowtype;
begin
    open cu_employee;
    fatch cu_employee into employee;
    while cu_employee%found loop
        dbms_output.put_line(employee.employee_id || ':' || employee.employee_name || ':' || employee.employee_age);
        fetch cu_employee into employee;
    end loop;
    close cu_employee;
end;

结果：

1：王潇：35
2：钟晓平：30
3：刘军：25

使用行类型变量处理游标和使用普通变量处理游标的不同：

1、使用行类型变量处理游标，游标的定义语句应使用select * from 表名，而普通变量处理游标则必须指明要搜寻的列
2、在利用fetch获取记录时，只需使用一个行类型变量，而无需多个普通变量按顺序进行排列
3、在引用结果集中数据时，利用‘行变量.列明’方式进行

为游标传递参数

显式游标可以带参数，参数用来指定集合的搜寻条件

declare cursor cu_employee(minAge in number,maxAge in number) is select * from emoloyees where employee_age>=minAge and employee_age<=maxAge;
employee employees%rowtype;
begin
    open cu_employee(21,30);
    fetch cu_employee into employee;
    while cu_employee%found loop 
        dbms_output.put_line(employee.employee_id || ':' || employee.employee_name || ':' || employee.employee_age);
    fetch cu_employee into employee;
    end loop;
    close cu_employee;
end;

结果：

2：钟晓平：30
3：刘军：25
4：王龙：24

总结显式游标

1、fetch命令可以捕获当前记录，并将指针移至下一条记录。游标位置下移才能捕获新纪录。如果使用循环并未使用fetch命令，则游标总处于首条记录，容易造成死循环。
2、游标使用完后，应使用close命令关闭，以防占用数据库资源。

游标属性

游标属性标识游标当前状态

1.found属性：执行fetch命令，Oracle将更新游标的found属性。如果fetch成功捕获新记录，found属性为真（true），否则为假（false）
2、not found 属性：该属性是found属性的对立面，当fetch不能捕获记录返回true，否则返回false
3.rowcount属性：在每次执行fetch命令时更新，反回当前已捕获的记录数
4.isopen属性：该属性被open和close命令更新，与用于判断游标是否处于打开状态，打开返回true，否则返回false。

隐式游标

隐士游标不允许用户声明和控制，仅限于使用。隐式游标包含2中：使用Oracle预定义的名为SQL的隐式游标和使用cursor for loop来进行循环的隐式游标。

SQL隐式游标的使用

SQL隐式游标是Oracle内置的游标。SQL游标与当前会话有关。当前会话中的更新（Update）、删除操作都会影响SQL隐式游标的属性。

查看SQL隐式游标的默认值

begin 
    if sql%isopen then 
        dbms_output.put_line('sql游标已打开')；
    else
        dbms_output.put_line('sql游标未打开')；
    end if;
        dbms_output.put_line('游标捕获记录数' || sql%rowcount)；
end


sql游标未打开
游标捕获记录数

查询语句对SQL隐式游标的影响

declare employee_age number;
begin
    select employee_age into employee_age from employees where employee_id=1;
    if sql%isopen then 
        dbms_output.put_line('sql游标已打开')；
    else
        dbms_output.put_line('sql游标未打开')；
    end if;
    dbms_output.put_line('游标捕获记录数' || sql%rowcount)；
end;


sql游标未打开
游标捕获记录数1

select语句获得查询信息并将值传入变量employee_age，此时SQL游标未打开

更新语句对SQL隐式游标的影响

begin
    Update employee set employee_age=employee_age+1；
    if sql%isopen then 
        dbms_output.put_line('sql游标未打开')；
    else
        dbms_output.put_line('sql游标未打开')；   
    end if;
    dbms_output.put_line('游标捕获记录数' || sql%rowcount)；
end;


sql游标未打开
游标捕获记录数1

SQL游标的属性信息保留为最后一条语句的更新信息

SQL隐式游标的使用

SQL隐式游标的isopen属性都为‘未打开’，印证了隐式游标不能为用户手动操作。SQL游标最常用的属性为found和rowcount。

存在更新，不存在新增

begin
    update employee set employee_name='王五',employee_age=25 where employee_id=10;
    if sql%rowcount then 
        insert into employee(employee_id,employee_name,employee_age) values (10,'王五',25);
    end if;
end;

SQL游标的另一个属性 not found 也能实现

cursor for 游标的使用

利用该游标，用户可以像使用普通循环语句一样循环处理select语句

语法：
    for 游标变量 in (查询语句) loop
        DML操作
    end loop


begin
    for employee in (select * from employees where employee_id<=6) loop dbms_output.put_line(employee.employee_id || ':' || employee.employee_name || ':' || employee.employee_age);
    end loop;
end;



1:王小平：37
2：钟晓平：32
3：刘军：27
4：王龙：26
5：中文：26
6：张三：25

总结显式游标和隐式游标

显式游标：
            1.实现对记录集的循环操作
            2.需要用户遵循声明、打开、捕获记录、操作记录、关闭等步骤
            3.使用灵活
隐式游标：
            1、实现对记录集的循环操作
            2、无须声明等操作，但不能人工干预。适用简单的游标操作

动态游标

无论显式还是隐式游标，获取记录集的查询定义在使用游标前必须定义。这些查询定义在整个生命周期内不能修改，因此成为静态游标。

动态游标指在声明游标时，不指定其查询定义，而是在游标打开时进行定义

动态游标分为2类：强类型动态游标和弱类型动态游标

强类型动态游标

强类型动态游标指：在游标使用前，虽未指定游标的查询定义，但游标类型已经确定。也就是游标所代表的记录类型必须确定。不能既用来存储客户信息，又存储订单信息

声明动态游标使用ref cursor关键字

type 游标类型 ref cursor
    return 记录类型

type 游标类型 ref cursor 定义一个新的动态游标类型。return 记录类型表示是一个强类型动态游标。

begin
declare
type employee_type is ref cursor
    return employees%rowtype;
e_count number;
employee employees%rowtype;
cu_employee employee_type;
    begin
        select count(*) into e_count from employees where employee_position='项目经理'；
        if e_count=0 then 
            open cu_employee for select * from employees;
        else
            open cu_employee for select * from employees where employee_position='项目经理'；
        end if;
        fetch cu_employee into employee;
            while cu_employee%found loop
            dbms_output.put_line(employee.employee_name || ':' || employee.employee_position);
        fetch cu_employee into employee;
        end loop;
    end;
end;


王晓：开发经理
钟晓平：高级工程师
刘军：高级工程师

弱类型动态游标

无须声明记录类型

type 游标类型 is ref cursor

---

在employees表中无项目经理信息，则在managers表中查询

begin
declare 
type custom_type is ref cursor ;
c_count number;
employee employees%rowtype;
manager managers%rowtype;
cu_custom custom_type;
    begin
        select count(*) into c_count from employees where employee_position='项目经理'；
        if c_count=0 then 
            open cu_custom for select * from managers where manager_position='项目经理'；
            fetch cu_custom into manager;
            while cu_custom%found loop dbms_output.put_line(manager.manager_name || ':' || manager.manager_position);
            fetch cu_custom into manager;
            end loop;   
        else
            open cu_custom for select * from employees where employee_position='项目经理'；
            fetch cu_custom into employee；
            while cu_custom%found loop dbms_output.put_line(employee.employee_name || ':' || employee.employee_position);
            fetch cu_custom into employee;
            end loop;
        end if;
    end;
end;