update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0; update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0; commit还是有问题啊
update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0; update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0; commit;
UPDATE "YYZHGL"."KHXX" SET khjlfz = 0 - ABS(khjlfz), khjlje = 0 - ABS(khjlje) WHERE khjlfz>0 OR khjlje>0;
这两条记录不能同时运行,在oracle中在行上加了排它锁.应该这样写 update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0; commit update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0; commit;
if you run upper 2 updates for different users, it will cause exclusive lock. but I think in this case, you are facing the problem not lock but invalid character as the ORA number is 911. So check your scripts to remove the invalid chars and run it again----------------------------------- SQL> set time on 04:07:21 SQL> set timing on 04:07:25 SQL> create table test_update ( khjlfz number );Table created.Elapsed: 00:00:00.13 04:08:17 SQL> insert into test_update values ( 100 );1 row created.Elapsed: 00:00:00.00 04:08:35 SQL> insert into test_update values ( 200 );1 row created.Elapsed: 00:00:00.00 04:08:48 SQL> insert into test_update values ( 0 );1 row created.Elapsed: 00:00:00.00 04:08:57 SQL> commit;Commit complete.Elapsed: 00:00:00.02 04:09:04 SQL> update test_update set khjlfz = 0 - khjlfz where khjlfz > 0;2 rows updated.Elapsed: 00:00:00.01 04:09:44 SQL> update test_update set khjlfz = 0 - khjlfz where khjlfz > 0;0 rows updated.Elapsed: 00:00:00.01 04:09:51 SQL> select * from test_update; KHJLFZ ---------- -100 -200 0Elapsed: 00:00:00.00------------------------------------------sql statements are fine
多谢各位的回答,我执行的地方不对。在sqlplus中执行时就对了。 update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0; update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0; commit;
update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0;
commit还是有问题啊
update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0;
commit;
SET khjlfz = 0 - ABS(khjlfz),
khjlje = 0 - ABS(khjlje)
WHERE khjlfz>0
OR khjlje>0;
update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0;
commit
update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0;
commit;
SQL> set time on
04:07:21 SQL> set timing on
04:07:25 SQL> create table test_update ( khjlfz number );Table created.Elapsed: 00:00:00.13
04:08:17 SQL> insert into test_update values ( 100 );1 row created.Elapsed: 00:00:00.00
04:08:35 SQL> insert into test_update values ( 200 );1 row created.Elapsed: 00:00:00.00
04:08:48 SQL> insert into test_update values ( 0 );1 row created.Elapsed: 00:00:00.00
04:08:57 SQL> commit;Commit complete.Elapsed: 00:00:00.02
04:09:04 SQL> update test_update set khjlfz = 0 - khjlfz where khjlfz > 0;2 rows updated.Elapsed: 00:00:00.01
04:09:44 SQL> update test_update set khjlfz = 0 - khjlfz where khjlfz > 0;0 rows updated.Elapsed: 00:00:00.01
04:09:51 SQL> select * from test_update; KHJLFZ
----------
-100
-200
0Elapsed: 00:00:00.00------------------------------------------sql statements are fine
update "YYZHGL"."KHXX" set khjlfz=(0-khjlfz) where khjlfz>0;
update "YYZHGL"."KHXX" set khjlje=(0-khjlje) where khjlje>0;
commit;