想到这个,是因为之前在工作中经常遇到批量插入的情况,按照平时的工作习惯,就是在PL/SQL Developer中for update 目标表,然后把内容粘贴进去。也能达到插入的目的,但是这种做法,总觉得过于依赖环境,而且也缺少灵活性。然后某一天看到游标这个概念后,觉得可以拿来用到工作场景中。
--首先给目标表pub_oods_zb创建一张错误日志表 pub_goos_zb_error_log,语句中的dbms_errlog是包,create_error_log() 是包里的存储过程,是oracle内置的。
begin
dbms_errlog.create_error_log('pub_goods_zb','pub_goos_zb_error_log');
end;
--这里是存储过程,不能把上面的语句放到存储过程中,会报错,不知道为什么。
create or replace procedure inse_pub_goods
is
v_goodsid pub_goods.goodsid%type;
v_goodsname pub_goods.goodsname%type;
v_num number(20);
--自定义一个异常,-12899是oracle错误代码,表示插入的数据超出字段长度
data_long exception ;
pragma exception_init(data_long,-12899) ;
--定义游标,游标是指向查询语句在内存中结果集的某一个地址,首先指向头部。
cursor cur_test
is
select goodsid,goodsname from pub_goods ;
begin
--打开游标
open cur_test ;
--遍历游标,游标指针自动从头部往下移动。
loop
--将游标中的内容存入变量
fetch cur_test into v_goodsid , v_goodsname ;
exit when cur_test%notfound ;
--将变量里的数据插入表中,或者对变量的数据经过处理再插入表中,对数据的超过过程都在这个环节,同时将错误记录插入错误记录表中。
--这个错误记录表不能记录所有的错误,唯一性约束错误就不能记录。详细的可以在网上找。
--用这个错误记录表的好处在与整个事务可以跑完,而且目标表中会插入所有满足插入条件的数据,异常数据及异常信息会全部被记录到日志表中。
--这样就可以根据错误日志记录进行增量插入,不用再完全重新插入,但是这个功效只适用于日志表能记录的错误类型。
--不能记录的错误类型还是会中断事务的运行,并回滚整个事务。
insert into pub_goods_zb (goodsid,goodsname) values(v_goodsid, v_goodsname)
log errors into pub_goos_zb_error_log('zb') reject limit unlimited ;
commit ;
end loop ;
--异常处理,这里因为有了错误日志表,执行过程不会跑剩下的语句,所以才在前面循环中加一个commit。
--如果没有之前的commit,那在调用这个存储过程后还有一个提交动作。如果提交,则目标表中会保存满足插入条件的数据。
exception
when data_long then
dbms_output.put_line('插入内容超过字段长度');
rollback;
when others then
dbms_output.put_line('插入的数据有错误');
rollback;
select count(*) into v_num from pub_goods_zb ;
--没有错误日志表,则这里就必须要有一个commit。
commit ;
dbms_output.put_line('成功插入:'||v_num||'条数据!');
--关闭游标,释放资源
close cur_test ;
end inse_pub_goods;
在写这个列子的时候,又心血来潮的加入了储存过程,异常,和错误日志的概念在里面,所以感觉有点混乱。中途在网上查资料,发现游标的用法远不止于此,我这里就粘贴一个连接了,留作记录。点击打开链接,看完这片博客之后,有一种感觉,就是调用者和被调用者之间变量的数据类型必须相同。特别需要注意一个东西:SYS_REFCURSOR ,这是一个关键字。字面意思看着像是游标类型,看网上说的是专用于存储过程返回游标结果集用的。也就是说,如果A过程或者函数中有一个游标,B过程或者函数想调用A过程中所产生的游标,那A过程就必须定义返回数据类型为SYS_REFCURSOR,同时B过程必须声明一个SYS_REFCURSOR的变量。用于接受A过程传递过来的数据。然后这个SYS_REFCURSOR和open 游标返回变量 for 查询子句 ; 应该是配套出现,本来我以为open for 是游标的另一种简写方式。但是细细想来,是不能单独这样使用游标的。验证也不通过。
-----------------------------------------------更简便的游标使用方式
declare
v_num number := 291000 ;
begin
for hz in ( select billno , billcode from xjzl_tmp ) loop
update wms_itf_dbzp_kpd t set t.billno = v_num where t.billcode = hz.billcode ;
v_num := v_num + 1 ;
dbms_output.put_line(v_num);
end loop ;
end ;
--------------------------------------------
for 自定义游标名称 in 查询内容 loop
执行语句 ;
end loop ;
---------------------------------------------
--这种方式,系统自动打开游标,关闭游标,自动判断工作空间是否还有下一个游标
--省时身心,居家旅行必备之良方,你值得拥有!