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

dbhelper發表於2015-02-24
今天快下班的時候,有個開發的同事問我一個問題,說他在客戶端執行一條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 約束








來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8494287/viewspace-1440455/,如需轉載,請註明出處,否則將追究法律責任。

相關文章