使用游标更新数据库
SQL>declare
2 emp_rec emp%rowtype;
3 cursor c1
4 is
5 select * from emp for update;
6begin
7 open c1;
8 loop
9 exit when c1%notfound;
10 fetch c1 into emp_rec;
11 if emp_rec.job='CLERK' then update emp set comm =1500 where current of c1;
12 end if;
13 end loop;
14 commit;
15 close c1;
16 end;
17 /;
一开始提示:第一行出现错误:
ORA-01410:无效的ROWID
ORA-06512:在line 10
后来发现把第9行和第10行调换位置之后就运行成功了。
这是为什么啊,希望有人解答,谢谢
SQL>declare
2 emp_rec emp%rowtype;
3 cursor c1
4 is
5 select * from emp for update;
6begin
7 open c1;
8 loop
9 exit when c1%notfound;
10 fetch c1 into emp_rec;
11 if emp_rec.job='CLERK' then update emp set comm =1500 where current of c1;
12 end if;
13 end loop;
14 commit;
15 close c1;
16 end;
17 /;
一开始提示:第一行出现错误:
ORA-01410:无效的ROWID
ORA-06512:在line 10
后来发现把第9行和第10行调换位置之后就运行成功了。
这是为什么啊,希望有人解答,谢谢
1.打开游标
2.获取数据
3.判断是否取得数据而LZ 打开游标还没有获取数据就执行判断exit when c1%notfound;
此时无法判断,所以报错不知道理解的对否,等待高人解释
4 is
5 select * from emp for update;
这里搞个for update 搞什么。
SELECT column_list FROM table_list FOR UPDATE
WHERE CURRENT OF cursor_name
我把for update去掉后再运行会提示错误,但是把where current of c1一块去掉再运行就不提示错误了
具体本质上为什么我也不太清楚,可能是格式完整性要求吧。
谢谢你的回答也,期待更好的回答
declare
emp_rec emp%rowtype;
cursor c1 is
select * from emp for update;
begin
open c1;
loop
fetch c1
into emp_rec;
exit when c1%notfound;
if emp_rec.job = 'CLERK' then
update emp set comm = 1500 where current of c1;
end if;
end loop;
close c1;
end;
declare
emp_rec emp%rowtype;
cursor c1 is
select * from emp for update;
begin
open c1;
loop
fetch c1
into emp_rec;
exit when c1%notfound;
if emp_rec.job = 'CLERK' then
update emp set comm = 1500 where current of c1;
end if;
end loop;
close c1;
end;
declare
emp_rec emp%rowtype;
cursor c1 is
select * from emp for update;
begin
open c1;
loop
--要先fetch,再exit when c1%notfound
fetch c1
into emp_rec;
exit when c1%notfound;
if emp_rec.job = 'CLERK' then
update emp set comm = 1500 where current of c1;
end if;
end loop;
close c1;
end;