[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" );

--//這樣也好理解也不會錯誤,給自己一個工作中提一個醒.