今天快下班的時候，有個開發的同事問我一個問題，說他在客戶端執行一條sql語句，裡面包含子查詢，如果單獨執行子查詢，會報"invalid identifier"的錯誤，但是整個sql語句一致性就沒有錯誤，而且資料的結果還是正確的，碰到這種問題，想必都是信心滿滿，越是奇怪越想探個究竟。
為了能夠簡單說明這個問題，我使用如下下面的語句來模擬一下。
select *from test1_customer where customer_id in (select customer_id from test2_customer where cycle_code>100);
執行這個語句沒有錯誤。
81 rows selected.

但是執行子查詢中的語句卻報出了ORA-00904的錯誤。
select customer_id from test2_customer where cycle_code>100
*
ERROR at line 1:
ORA-00904: "CYCLE_CODE": invalid identifier

檢視錶test2_customer的欄位，確實沒有發現cycle_code這個欄位，但是查詢竟然還是能夠執行。
原因只有一個，那個欄位就是從別的表中引用的。只有test1_customer

建表的語句如下：
create table test1_customer as select object_id customer_id,object_name customer_name, object_id cycle_code from user_objects;
create table test2_customer as select object_id customer_id,object_name customer_name, object_id bill_cycle from user_objects;

在子查詢中執行select customer_id from test2_customer where cycle_code>100，欄位cycle_code因為在test2_customer中不存在，於是會自動去引用test1_customer的欄位值，剛好匹配到了，就輸出了結果。
select *from test1_customer where customer_id in (select customer_id from test2_customer where cycle_code>100);

這個問題如果在複雜的場景中還是很難排查的，可能就因為一點點的小問題會導致資料的問題。
所以從這個問題可以反思我們在寫sql語句的時候還是需要一些基本的規範，這樣就不會導致一些模糊的定義，不明不白的問題。
當引用了多個表的時候最好還是給表起個簡單的別名，這樣在分析sql語句的時候也比較直觀和方便。
上面的查詢可以簡單的修改為：
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.bill_cycle>100);
如果有問題的話，也能夠很快定位倒底是哪裡出了問題。
SQL> select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100);
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100)
*
ERROR at line 1:
ORA-00904: "T2"."CYCLE_CODE": invalid identifier

引申一下，在建立表，索引，序列的時候也都可以透過規範的命名規則，這樣自己也很方便檢視。
比如
ACCOUNT_PK就代表是一個主鍵索引，
ACCOUNT_1UQ就是一個唯一性索引
ACCO_COMPANY_CODE_NN 就代表欄位COMPANY_CODE是一個not null 約束

透過ORA錯誤反思sql語句規範

相關文章