oracle中批处理游标,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命令定义，它可以对查询语句返回的多条记录进行处理。显示游标需要用户显示进行定义、显示打开、从游标取数据和显示关闭。定义游标-- 其中，cursor_name是游标的名字，SELECT_statement是查询语句

CURSOR cursor_name IS SELECT_statement;

比如：declare

cursor cSpecTel is select * from xg.sys_spec_tel;

begin

-- Do Something

end;

打开游标

定义了游标，在真正的使用游标之前，我们需要打开游标。具体语法如下：OPEN cursor_name;

从游标取数据

我们使用游标，主要就是为了批量处理数据，从游标中提取数据的语法如下：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;

关闭游标

在使用完游标以后，必须要关闭游标，释放游标占用的系统资源，如果数据库中存在很多的未关闭的游标，还可能导致数据库死机。关闭游标的语法如下：CLOSE cursor_name;

在上面的代码中，我使用了%NOTFOUND、%ISOPEN等显式游标的属性，对于显式游标来说，它也有四大属性，分别如下：属性名称属性含义结果类型%FOUND当最后一次读(FETCH)记录成功，则返回TRUE布尔类型

%NOTFOUND和%FOUND的含义相反布尔类型

%ISOPEN判断游标是否打开，当游标已打开时返回TRUE布尔类型

%ROWCOUNT返回已从游标中读取的记录数(到目前为止)整数

游标FOR循环

我们可以清楚的知道，使用显式游标的标准步骤如下：打开游标

开始循环游标

从游标取值，并处理数据

退出循环

关闭游标

这样的过程非常繁琐，因此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++也是如此)：定义CURSOR类型的指针

声明游标变量

在创建游标变量之前，我们必须定义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;

总结

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

本文来自投稿，不代表访得立场，如若转载，请注明出处：http://www.found5.com//view/543.html