[20180904]工作中一个错误.txt
–//昨天看我提交一份修改建议,发现自己写的sql语句存在错误.
–//链接:http://blog.itpub.net/267265/viewspace-2213259/
–//里面提到:
5f2atm993xz6w
update pd_pmxs set pdbz =:”sys_b_0″ , status =:”sys_b_1″ where rdid =:1
修改为
update pd_pmxs set pdbz =:”sys_b_0″ , status =:”sys_b_1″ where rdid =:1 and pdbz <> :”sys_b_0″ and status <>:”sys_b_1″
–//这样修改是有问题,通过一个例子说明:
1.环境:
scott@test01p> @ ver1
port_string version banner con_id
—————————— ————– ——————————————————————————– ———-
ibmpc/win_nt64-9.1.0 12.1.0.1.0 oracle database 12c enterprise edition release 12.1.0.1.0 – 64bit production 0
scott@test01p> create table t (id number,a number,b number);
table created.
insert into t values (1,0,0);
insert into t values (1,0,1);
insert into t values (1,1,0);
insert into t values (1,1,1);
insert into t values (1,2,2);
insert into t values (1,2,1);
commit;
2.测试:
scott@test01p> select * from t where id=1 and a<>1 and b<>1;
id a b
———- ———- ———-
1 0 0
1 2 2
–//可以发现这样写仅仅显示两条.如果按照前面的语句执行dml,就存在错误了.
update pd_pmxs set pdbz =1 , status =1 where rdid =:1
–//修改为
update pd_pmxs set pdbz =1 , status =1 where rdid =:1 and pdbz <>1 and status <>1
–//这样仅仅(pdbz, status) =(0,0),(2,2) 才会修改.其它情况不会修改,实际上这是一个集合问题.
–//执行如下就对了.
scott@test01p> select * from t where id=1 and (a<>1 or b<>1);
id a b
———- ———- ———-
1 0 0
1 0 1
1 1 0
1 2 2
1 2 1
–//也就是我上面的语句要修改如下:
update pd_pmxs set pdbz =:”sys_b_0″ , status =:”sys_b_1″ where rdid =:1 and (pdbz <> :”sys_b_0″ or status <>:”sys_b_1″ );
–//实际上面的写法很容易混乱,写成集合的形式就容易理解也不会错误.
scott@test01p> select * from t where id=1 and (a,b) not in (1,1);
select * from t where id=1 and (a,b) not in (1,1)
*
error at line 1:
ora-00920: invalid relational operator
–//集合还要加一个括号.
scott@test01p> select * from t where id=1 and (a,b) not in ((1,1));
id a b
———- ———- ———-
1 0 0
1 0 1
1 1 0
1 2 2
1 2 1
–//这样上面的修改如下:
5f2atm993xz6w
update pd_pmxs set pdbz =:”sys_b_0″ , status =:”sys_b_1″ where rdid =:1
修改为
update pd_pmxs set pdbz =:”sys_b_0″ , status =:”sys_b_1″ where rdid =:1 and (pdbz,status) not in(( :”sys_b_0″ , :”sys_b_1″ );
–//这样也好理解也不会错误,给自己一个工作中提一个醒.