果冻想
认真玩技术的地方

Oracle学习笔记——批处理利器游标

一行这么处理,两行呢?

我们经常写这样的代码:

-- Created on 2015-7-15 by JellyThink 
declare 
    strAreaCode VARCHAR2(10);
    strUserName VARCHAR2(30);
    strTelNum VARCHAR2(12);
begin
    -- 我们这里确信只返回一行数据
    select a.area_code, a.user_name, a.tel_num into strAreaCode, strUserName, strTelNum 
        from xg.sys_spec_tel a where a.tel_num='15034974832';
    dbms_output.put_line(strAreaCode);
    dbms_output.put_line(strUserName);
    dbms_output.put_line(strTelNum);
exception
    when NO_DATA_FOUND then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    when TOO_MANY_ROWS then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
end;

在使用select ... into ...语句时,我们笃信肯定会返回一行数据,而且只有一行;但是,如果返回的数据不止一行,这样都会抛出异常。而我们期望的不是抛出异常,那么如果返回的数据不止一行的时候,如何将每一行的值赋值给变量进行处理呢?这就要用到游标了,这篇文章就对Oracle中的游标进行详细的总结。

隐式游标

在Oracle中,游标分为以下两种:

  • 隐式游标
  • 显式游标

我们先说说隐士游标。
隐式游标又叫SQL游标,是在执行DML操作和查询操作返回单条记录时,由PL/SQL自动、隐藏定义。隐式游标由PL/SQL自动定义、自动打开、自动关闭、不需要用户的参与;隐式游标的游标名叫SQL。

隐式游标有以下四个属性,这些游标属性用于返回DML和查询操作的信息。

属性名称 属性含义 结果类型
%FOUND 用于判断DML语句是否改变了行,或者判断SELECT INTO是否返回了一行数据 布尔类型
%NOTFOUND 和%FOUND的含义相反 布尔类型
%ISOPEN 判断游标是否打开,对于隐式游标来说,当SQL语句执行完成以后,游标被自动关闭,因此SQL%ISOPEN的值永远是FALSE 布尔类型
%ROWCOUNT 用于判断DML语句影响了多少行,或者SELECT INTO返回了多少行 整数

看一个小小的例子:

declare
begin
    delete from xg.sys_spec_tel where tel_num='15034784641';
    if sql%found then
        dbms_output.put_line(sql%rowcount);
        commit; -- 删除成功了,就提交
    else
        NULL; -- 执行空语句
    end if;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
end;

显式游标

显式游标用CURSOR...IS命令定义,它可以对查询语句返回的多条记录进行处理。显示游标需要用户显示进行定义、显示打开、从游标取数据和显示关闭。

  1. 定义游标
    -- 其中,cursor_name是游标的名字,SELECT_statement是查询语句
    CURSOR cursor_name IS SELECT_statement;
    

    比如:

    declare
        cursor cSpecTel is select * from xg.sys_spec_tel;
    begin
        -- Do Something
    end;
    
  2. 打开游标
    定义了游标,在真正的使用游标之前,我们需要打开游标。具体语法如下:

    OPEN cursor_name;
    
  3. 从游标取数据
    我们使用游标,主要就是为了批量处理数据,从游标中提取数据的语法如下:

    FETCH cursor_name INTO variable1[, variable2, ... ];
    

    使用FETCH命令从游标中提取数据,每提取一次,游标都指向结果集的下一行。其中variable1是变量,从游标中取得的数据就存放在该变量中。

    -- Created on 2015-7-17 by JellyThink 
    declare 
        cursor cSpecTel is select tel_num from xg.sys_spec_tel;
        phone varchar2(13);
    begin
        dbms_output.enable(1000000); -- 1~1000000
        open cSpecTel;
    
        loop
            fetch cSpecTel into phone;
            exit when cSpecTel%NOTFOUND or cSpecTel%NOTFOUND is null;
            dbms_output.put_line('Special Phone Number:' || phone);
        end loop;   
    
        close cSpecTel;
    exception
        when others then
            dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    
            -- 如果发生异常以后,检查游标是否被关闭
            if cSpecTel%isopen then
                close cSpecTel;
            end if;
    end;
    
  4. 关闭游标
    在使用完游标以后,必须要关闭游标,释放游标占用的系统资源,如果数据库中存在很多的未关闭的游标,还可能导致数据库死机。关闭游标的语法如下:

    CLOSE cursor_name;
    

在上面的代码中,我使用了%NOTFOUND%ISOPEN等显式游标的属性,对于显式游标来说,它也有四大属性,分别如下:

属性名称 属性含义 结果类型
%FOUND 当最后一次读(FETCH)记录成功,则返回TRUE 布尔类型
%NOTFOUND 和%FOUND的含义相反 布尔类型
%ISOPEN 判断游标是否打开,当游标已打开时返回TRUE 布尔类型
%ROWCOUNT 返回已从游标中读取的记录数(到目前为止) 整数

游标FOR循环

我们可以清楚的知道,使用显式游标的标准步骤如下:

  1. 打开游标
  2. 开始循环游标
  3. 从游标取值,并处理数据
  4. 退出循环
  5. 关闭游标

这样的过程非常繁琐,因此Oracle中引入了FOR循环。FOR循环把上面的过程融合在一起,忽略了显式的打开游标、关闭游标、从游标中取数据等过程。使用这种方式,系统隐藏定义了一个%ROWTYPE类型的记录。游标FOR循环的语法如下所示:

FOR record_name IN (cursor_name) | (query_difinition)
LOOP
    statements
END LOOP;
元素名称 说明
record_name 记录的名称,%ROWTYPE类型的记录
cursor_name 游标的名称
query_difinition 如果不指定游标的话,可以指定一个查询

现在将上面的示例代码改写成使用FOR的形式。

-- Created on 2015-7-17 by JellyThink 
declare 
    cursor cSpecTel is select * from xg.sys_spec_tel;
begin
    dbms_output.enable(1000000);
    for specTel in cSpecTel
    loop
        dbms_output.put_line('Phone Number:' || specTel.Tel_Num);
    end loop;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
end;

这样写起来,代码是不是简洁多了。

向游标中传递参数

可以看到,上面的查询语句select * from xg.sys_spec_tel都是写死的。如果我们想动态的根据条件改变查询,那怎么办?这个时候,我们可以利用游标参数动态改变查询语句。

-- 定义一个根据区号获得特殊号码的存储过程
create or replace procedure p_get_spec_tel(area varchar2)
as
    phone varchar2(13);
    areaCode varchar2(5);
    cursor cSpecTel(area1 varchar2) is -- area1就是定义的游标参数
        select a.tel_num, a.area_code
        from XG.SYS_SPEC_TEL a
        where a.area_code=area1;
begin
    dbms_output.enable(1000000);

    -- 打开游标时,将参数传递进去
    -- 使用FOR隐藏了打开游标的过程,不知道怎么将参数传递进去
    open cSpecTel(area);

    loop
        fetch cSpecTel into phone, areaCode;
        exit when cSpecTel%notfound or cSpecTel%notfound is null;
        dbms_output.put_line('Phone Number:' || phone || '  Area Code:' || areaCode);
    end loop;
    close cSpecTel;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
end;

这样的话,我们在运行存储过程的过程中,可以传递一个参数进去,定义游标的时候,可以指定参数,这样的话,就可以成功进行动态的指定查询条件。

游标变量

在下面的代码中:

cursor cSpecTel is
    select a.tel_num, a.area_code
    from XG.SYS_SPEC_TEL a;

当我们这样定义了游标以后,这个名为cSpecTel的游标就只对应查询语句select a.tel_num, a.area_code from XG.SYS_SPEC_TEL a;;也就是说一旦我们定义了这样的一个游标,这个游标就相当于一个常量了。如果我们想定义一个游标变量,想给这个游标变量赋什么值就赋什么值,这该多好。

游标变量就是一个指针,这个指针可以指向不同的查询工作区;而显式游标总是指向相同的查询工作区。简单的说,游标变量是一个变量,只是这个变量可以动态指向不同的游标,所以游标变量又称动态游标;而显式游标又称静态游标。游标变量可以作为函数或者存储过程的参数。

定义游标变量分为两步(自定义类型都是这样,C++也是如此):

  1. 定义CURSOR类型的指针
  2. 声明游标变量

在创建游标变量之前,我们必须定义REF CURSOR类型。定义REF CURSOR类型的语法如下:

TYPE ref_type_name IS REF CURSOR [RETURN return_type]

其中ref_type_name是新类型的名字,是声明游标变量的类型,这里实际上定义的是一种新的数据类型。return_type是可选的,用于指定游标变量返回值的类型,它必须是一个记录类型(RECORD)或者行类型(ROWTYPE),如果有返回类型,则称之为“强REF CURSOR”;如果没有返回类型,则称之为“弱REF CURSOR”。我们使用“弱REF CURSOR”,则允许把游标变量与任何查询进行关联;如果使用“强REF CURSOR”,则只允许把游标变量与特定查询进行关联。

定义一个强REF CURSOR:

-- 有返回类型
TYPE strongType IS REF CURSOR RETURN specialTel%ROWTYPE;

定义一个弱REF CURSOR:

-- 没有返回类型
TYPE weakType IS REF CURSOR;

游标类型定义完成以后,我们就可以声明一个游标变量了。

telnum_cur weekType;

现在截取我们生产库上的一个例子看看游标变量的具体使用方法。

create or replace procedure cp.insert_accountrent_rate
IS
    type t_cur IS ref cursor; -- 定义一个弱REF CURSOR类型
    rate_cur t_cur; -- 声明一个游标变量
    sql_stmt varchar2(10240);

begin
    sql_stmt :='SELECT curve_id,base_val,rate_val,formula_id,share_num FROM cp.PM_CURVE_SEGMENTS';

    -- 使用open...for...打开这个游标
    open rate_cur for sql_stmt;
    loop
        fetch rate_cur into mrate_id,mbase_val,mrate_val,mformula_id,mshare_num;
        exit when rate_cur%notfound;
    end loop;
    close rate_cur;
end;

总结

游标在实际工作中是经常使用的,而且对于批处理来说,使用起来也非常方便,掌握了游标,将会极大的提高你的工作效率。

这篇文章稍长,代码稍多,希望你能耐心的看完。如果连这么点文字都看不下去,这么点耐心都没有,那你也太浮躁了。最后,希望这篇文章能够对大家有所帮助。

下班、回家、陪老婆去。。。

果冻想,认真玩技术的地方。

2015年7月17日 于呼和浩特。

未经允许不得转载:果冻想 » Oracle学习笔记——批处理利器游标
网站维护离不开您的支持,您可以赞助本站,谢谢支持
×

感谢您的支持,我们会一直保持!

扫码支持
请土豪扫码随意打赏

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

赞助本站
关注微信公众号
关注微信公众号和果冻一起分享你的疑惑与心得。
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

玩技术,我们是认真的

联系我们关于果冻