[20180904]工作中一个错误.txt

[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" ); --//这样也好理解也不会错误,给自己一个工作中提一个醒.