[2020528]寫sql語句不要忘記給欄位加上表別名.txt

[2020528]寫sql語句不要忘記給欄位加上表別名.txt

--//許多開發寫sql語句經常對於一些欄位前面不加表別名,有時候主要問題在於開發壓力太大,不注意這些細節.
--//昨天看連結我覺得應該讓開發養成習慣,給欄位前加上表或者表別名.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t1 as select rownum id ,'test'||rownum name from dual connect by level<=5;
create table t2 as select rownum idx ,'test'||rownum name from dual ;

--//分析略。

2.測試:
SCOTT@test01p> select * from t1 where id in (select id from t2);
        ID NAME
---------- --------------------
         1 test1
         2 test2
         3 test3
         4 test4
         5 test5

SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3260755764 511sg7v15qbtn            1  c25b2f34

SCOTT@test01p> @ expand_sql_text.sql  511sg7v15qbtn
SELECT "A1"."ID" "ID","A1"."NAME" "NAME" FROM "SCOTT"."T1" "A1" WHERE "A1"."ID"=ANY (SELECT "A1"."ID" "ID" FROM "SCOTT"."T2" "A2")
PL/SQL procedure successfully completed.
--//表T2並沒有id欄位。子查詢變成了(SELECT "A1"."ID" "ID" FROM "SCOTT"."T2" "A2")。

SCOTT@test01p> select * from t1 where  name  in (select name from t2);
        ID NAME
---------- --------------------
         1 test1

SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
2780930533 a9vgd1kkw38g5            0  a5c1a1e5
        
SCOTT@test01p> @ expand_sql_text.sql  a9vgd1kkw38g5
SELECT "A1"."ID" "ID","A1"."NAME" "NAME" FROM "SCOTT"."T1" "A1" WHERE "A1"."NAME"=ANY (SELECT "A2"."NAME" "NAME" FROM "SCOTT"."T2" "A2")
PL/SQL procedure successfully completed.

--//如果改成刪除就產生5條記錄。
SCOTT@test01p> delete from  t1 where id in (select id from t2);
5 rows deleted.

SCOTT@test01p> rollback ;
Rollback complete.

--//所以應該讓開發養成良好編寫sql語句的習慣,給欄位加上表名或者表別名.單表問題不大,多表情況下要特別注意.

3.繼續測試：
--//如果寫成如下：
SCOTT@test01p> select * from t1 where t1.id in (select t2.id from t2);
select * from t1 where t1.id in (select t2.id from t2)
                                        *
ERROR at line 1:
ORA-00904: "T2"."ID": invalid identifier

--//很容易定位錯誤。許多開發都沒有養成好的習慣特別在複雜sql語句多表連線的情況下。