通过ORA错误反思sql语句规范(r4笔记第41天)

今天快下班的时候，有个开发的同事问我一个问题，说他在客户端执行一条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 约束