SQL> select * from a; A1
----------
8
8
2SQL> declare
2 v_sql varchar2(100);
3 v_tablename varchar2(10) :='a';
4 begin
5 update a set a1=2;
6 end;
7 /PL/SQL procedure successfully completed.SQL> select * from a; A1
----------
2
2
2
----------
8
8
2SQL> declare
2 v_sql varchar2(100);
3 v_tablename varchar2(10) :='a';
4 begin
5 update a set a1=2;
6 end;
7 /PL/SQL procedure successfully completed.SQL> select * from a; A1
----------
2
2
2
如果你要更新只要update a set a1=2;就行了,何必写那么长?
还是你想表达其它什么意思?可以详细点吗?
ORA-01002: 读取违反顺序
Action Parse and execute a SQL statement before attempting to fetch the data. 一定是你的语法有问题.
create or replace procedure proc_wlgd is
i number:=0;
c_khbm varchar2(10);
c_kmbm varchar2(20);
c_jfje numeric(16,2);
c_id1 NUMERIC(10);
c_id2 numeric(10);
cursor wlgd is
select trim(khbm),trim(kmbm),nvl(jfje,0)-nvl(dfje,0),id from yswlgdtemp where (gdbz=0 or gdbz is null) for update;
begin open wlgd;
loop
fetch wlgd into c_khbm,c_kmbm,c_jfje,c_id1;
select count(*) into i from yswlgdtemp where
trim(khbm)=trim(c_khbm) and trim(kmbm)=trim(c_kmbm) and (nvl(dfje,0)-nvl(jfje,0))=c_jfje;
if i>0 then
select min(id) into c_id2 from yswlgdtemp where trim(khbm)=trim(c_khbm) and trim(kmbm)=trim(c_kmbm) and (nvl(dfje,0)-nvl(jfje,0))=c_jfje;
update yswlgdtemp set gdbz=3 where current of wlgd;
update yswlgdtemp set gdbz=3 where id=c_id2;
end if; exit when wlgd%notfound;
end loop;
close wlgd;
end proc_wlgd;
SQL> select * from test1; ID ZYZ1 P1 DOCTIME
---------- ---------- ---------- ----------
1 sdfds 6
1 sdfds 6 01-5月 -02
1 sdfds 6 20-8月 -02
62 sdfds 6
63 sdfds 6
1 sdfds 6
83 sdfds 6
91 sdfds 6
92 sdfds 6
93 sdfds 6 22-8月 -02
94 sdfds 6 02-8月 -02 ID ZYZ1 P1 DOCTIME
---------- ---------- ---------- ----------
2 01-9月 -02
1 01-9月 -02
3 01-9月 -02
4 temp 5 01-1月 -02
5 字符集
16 sdfdsfsdf
17 sdfdsfsd43
43f
18 rows selected.SQL> create or replace procedure proc_wlgd is
2 i number:=0;
3 c_khbm varchar2(10);
4 c_kmbm varchar2(20);
5 c_jfje numeric(16,2);
6 c_id1 NUMERIC(10);
7 c_id2 numeric(10);
8 cursor wlgd is
9 select id from test1 for update;
10 begin
11
12 open wlgd;
13 loop
14 fetch wlgd into c_id2;
15 if c_id2=1 then
16 update test1 set p1=333 where current of wlgd;
17 update test1 set zyz1='333' where id=c_id2;
18 end if;
19
20 exit when wlgd%notfound;
21 end loop;
22 close wlgd;
23
24 end proc_wlgd;
25 /Procedure created.SQL> exec proc_wlgdPL/SQL procedure successfully completed.SQL> select * from test1; ID ZYZ1 P1 DOCTIME
---------- ---------- ---------- ----------
1 333 333
1 333 333 01-5月 -02
1 333 333 20-8月 -02
62 sdfds 6
63 sdfds 6
1 333 333
83 sdfds 6
91 sdfds 6
92 sdfds 6
93 sdfds 6 22-8月 -02
94 sdfds 6 02-8月 -02 ID ZYZ1 P1 DOCTIME
---------- ---------- ---------- ----------
2 01-9月 -02
1 333 333 01-9月 -02
3 01-9月 -02
4 temp 5 01-1月 -02
5 字符集
16 sdfdsfsdf
17 sdfdsfsd43
43f
18 rows selected.
才会写进数据库里去,这样的话
select trim(khbm),trim(kmbm),nvl(jfje,0)-nvl(dfje,0),id from yswlgdtemp where (gdbz=0 or gdbz is null) for update;会把我认为已经更新的记录再选进来,因为他们的gdbz还不为3我执行proc_wlgd得到的结果是:
KHBM KMBM JFJE DFJE ID GDBZ
A1 1002 2.00 63 3
A1 1002 2.00 64 3
A1 1002 2.00 65 3
我实际想要的结果是ID为63,65的记录其gdbz为3,而ID为64的记录不应该修改
请问在哪commit;?
select trim(khbm),trim(kmbm),nvl(jfje,0)-nvl(dfje,0),id from yswlgdtemp where (gdbz=0 or gdbz is null) for update of gdbz nowait; 后得到的结果仍是一样。
for update,
update temp set gdbz=3 where current of wlgd.我从来不这么用.