一、三种类型的游标
1. 隐式Cursor:
1).对于Select …INTO…语句,一次只能从数据库中获取到一条数据,对于这种类型的DML Sql语句,就是隐式Cursor。
例如:Select /Update / Insert/Delete操作。
2)作用:可以通过隐式Cusor的属性来了解操作的状态和结果,从而达到流程的控制。Cursor的属性包含:
SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数
SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真,结束后为假
3) 隐式Cursor是系统自动打开和关闭Cursor.
例如:
Set Serveroutput on;
begin
update t_contract_master set liability_state = 1 where policy_code = '123456789';
if SQL%Found then
dbms_output.put_line('the Policy is updated successfully.');
commit;
else
dbms_output.put_line('the policy is updated failed.');
end if;
end;
/
2. 显式Cursor:
(1) 对于从数据库中提取多行数据,就需要使用显式Cursor。显式Cursor的属性包含:
游标的属性 返回值类型 意 义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数
%FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假
%NOTFOUND 布尔型 与%FOUND属性返回值相反
%ISOPEN 布尔型 游标已经打开时值为真,否则为假
(2) 对于显式游标的运用分为四个步骤:
ü 定义游标---Cursor [Cursor Name] IS select...;
ü 打开游标---Open [Cursor Name];
ü 操作数据---Fetch [Cursor name] into ...
ü 关闭游标---Close [Cursor Name],这个Step绝对不可以遗漏。
例1:
Set serveroutput on;
declare
---define Cursor
Cursor cur_policy is
select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account
from t_contract_master cm
where cm.liability_state = 2
and cm.policy_type = 1
and cm.policy_cate in ('2','3','4')
and rownum < 5
order by cm.policy_code desc;
curPolicyInfo cur_policy%rowtype;---定义游标变量
Begin
open cur_policy; ---open cursor
Loop
--deal with extraction data from DB
Fetch cur_policy into curPolicyInfo;
Exit when cur_policy%notfound; --这句一定不能少,否则死循环了
Dbms_Output.put_line(curPolicyInfo.policy_code);
end loop;
Exception
when others then
close cur_policy;
Dbms_Output.put_line(Sqlerrm);
if cur_policy%isopen then
--close cursor
close cur_policy;
end if;
end;
/
3. Ref Cursor(动态游标):
1) 与隐式Cursor,显式Cursor的区别:
Ref Cursor是可以通过在运行期间传递参数来获取数据结果集。 //执行过程中才知道结果集是什么,且可以多次使用
而另外两种Cursor,是静态的,在编译期间就决定数据结果集。
2) Ref cursor的使用:
ü Type [Cursor type name] is ref cursor;
ü Define 动态的Sql语句
ü Open cursor for sqlstr
ü 操作数据---Fetch [Cursor name]
ü Close Cursor
下面是一个Sample:
Set serveroutput on;
Declare
---define cursor type name
type cur_type is ref cursor;
cur_policy cur_type;
sqlStr varchar2(500);
rec_policy t_contract_master%rowtype;
begin
---define 动态Sql
sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm
where cm.liability_state = 2
and cm.policy_type = 1
and cm.policy_cate in (2,3,4)
and rownum < 5
order by cm.policy_code desc ';
---Open Cursor
open cur_policy for sqlStr; //此时才确定结果集
loop
fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;
exit when cur_policy%notfound;
--do something...
Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);
end loop;
close cur_policy;
end;
/
二、遍历游标的三种方法
create or replace package testPackeg is
procedure T_sp_testCursor;
end testPackeg;
create or replace package body testPackeg IS
procedure T_sp_testCursor IS
--普通游标
CURSOR cur IS SELECT id FROM a;
id NUMBER;
BEGIN
OPEN cur; --打开游标
------------------------------------------------------------------------------
IF cur%FOUND THEN --cur的found属性
dbms_output.put_line('found');
ELSIF cur%NOTFOUND THEN ----cur的notfound属性
dbms_output.put_line('not found');
ELSE
dbms_output.put_line('null'); --未执行fetch之前,cur的found属性/notfound属性均为null,执行之后,有一个为true
END IF;
close cur;
------------------------------------------------------------------------------
--循环获取游标值,方法一:loop循环
OPEN cur; --打开游标
LOOP
FETCH cur INTO id;
EXIT WHEN cur%NOTFOUND; --!!!!一定要加上这句,否则就死循环了!!!!!
--do sometiing else...
dbms_output.put_line(id);
END LOOP;
CLOSE cur;
------------------------------------------------------------------------------
OPEN cur;
--循环获取游标值,方法二:while循环
FETCH cur INTO id;
WHILE cur%FOUND LOOP
dbms_output.put_line(id);
--do sometiing else...
FETCH cur INTO id;
END LOOP;
CLOSE cur;
-----------------------------------------------------------------------------
--循环获取游标值,方法三:for循环 [最好,最简单]
--OPEN cur;
FOR v_cur IN cur LOOP
--do sometiing else...
dbms_output.put_line(v_cur.id);
END LOOP;
--CLOSE cur; --关闭游标
-----------------------------------------------------------------------------
end T_sp_testCursor;
end testPackeg;
可见for循环是比较简单实用的方法。
首先,它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。
其它,自动定义了一个记录类型及声明该类型的变量,并自动fetch数据到这个变量中。
我们需要注意v_cur 这个变量无需要在循环外进行声明,无需要为其指定数据类型。
它应该是一个记录类型,具体的结构是由游标决定的。
这个变量的作用域仅仅是在循环体内。
把v_cur看作一个记录变量就可以了,如果要获得某一个值就像调用记录一样就可以了。
如v_cur.id
由此可见,for循环是用来循环游标的最好方法。高效,简洁,安全。
但遗憾的是,常常见到的却是第一种方法。所以从今之后得改变这个习惯了。