一些注意的sql写法

  Dashboard > 流程空间 > home > SQLMAP编写规范以及一些常见的SQL问题     
Page Operations
View Edit Attachments (0) Info
Browse Space
Pages Labels Attachments Mail News Templates Advanced
Add Content
Add Page  Add News  Welcome 李文学 | History | Preferences | Log Out    
  流程空间
SQLMAP编写规范以及一些常见的SQL问题
Added by 李文学, last edited by 李文学 on 五月 18, 2007  (view change) Labels: (None) EDIT

Add Labels  
Enter labels to add to this page:
  Tip: Looking for a label? Just start typing.  



select语句中*号的问题
1。表连接时，绝不允许写select * ，否则按照编码错误Bug处理。

2。单表查询，一般情况下可以使用select * ，但以下几种情况禁用：
   a、表中包含lob字段（BLOB、CLOB、LONG、LONG RAW等）。
   b、表中包含长度较大的字段，如varchar2(1000)以上的字段，但该SQL实际上并不需要取出该字段的值。
   c、字段数量很多，但实际要用的字段很少，比如表有50个字段，而你实际只用5个，并且该sql目前没有被重用。
   d、DBA要求优化调整的。

严格要求使用正确类型的变量，杜绝oracle做隐式类型转换的情况
1。推荐在sqlmap的变量中指定变量的数据类型，如：

select * from iw_user where iw_user_id = #userid:VARCHAR#2。其中，对于时间类型的字段，必须使用TO_DATE进行赋值（当前时间可直接用sysdate表示），不允许下列这些错误用法：

   错误的写法（使用date类型的变量）：

select * from iw_account_log where trans_account = #transaccount:varchar#
   and trans_dt >= #dateBegin:date#
   and trans_dt < #dateEnd:date#   错误的写法（使用包含sysdate的表达式）：

select * from iw_account_log where trans_account = #transaccount:varchar#
   and trans_dt >= trunc(sysdate - 1)
   and trans_dt < sysdate + 1   错误的写法（将to_date函数和数字进行算术运算）：

select * from iw_account_log where trans_account = #transaccount:varchar#
   and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
   and trans_dt < to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss') + 1   正确的写法：

select * from iw_account_log where trans_account = #transaccount:varchar#
   and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
   and trans_dt < to_date(#dateEnd:varchar#, 'yyyy-mm-dd hh24:mi:ss')       /*或 trans_dt < sysdate */3。对于变量数据类型错误导致SQL严重性能问题的，按严重的编码错误Bug处理！

杜绝循环调用
   例如：在迭代的过程中，使用同一SQL反复查询DB，如：

while (listObj.hasnext()) {
  SELECT * FROM beyond_trade_process WHERE (trade_no = ?)
  ......
}   这样不仅效率不高，还造成交互过于频繁，严重情况会导致服务器LOAD增加
   解决方式：
   1。如果该查询是使用唯一键（如上例），参考后面的STR2VARLIST或STR2NUMLIST的用法
   2。联系DBA协助分析解决方式
   3。违反本条规范，属较严重Bug，如是前台应用应立即予以修复

绑定变量和替代变量
在Ibatis中：
绑定变量用 #变量名# 表示
替代变量用 $变量名$ 表示

注意几点：
1。通常，应使用绑定变量，尤其是具体取值变化范围较大的变量，如iw_user_id = #userid#。
2。取值范围很小（比如枚举字段），并且通常取值会比较固定，在DBA预先同意的情况下使用替代变量，或者干脆使用常量。
3。当一个绑定变量在实际使用中实际取值总是为某一固定常量时，应当直接使用常量而不是变量，关于这点，可在具体使用时咨询DBA
4。在order by子句中，通常使用替代变量而不是绑定变量。
5。IN子句，使用"iterate + 数组类型变量"的方式实现绑定变量，例如：

<isNotEmpty prepend="and" property="userIds">
     <iterate  property="userIds" open="t.creator in (" close=")" conjunction=",">
          #userIds[]#
     </iterate>
</isNotEmpty>将生成 t.creator in (:1, :2, :3, :4, :5 ...) 的语句

在字段上加函数的问题
1。通常，不允许在字段上添加函数或者表达式，如：

   错误的写法：

select * from iw_account_log where to_char ( trans_dt, 'yyyy-mm-dd') = '2007-04-04';
select qty from product where p_id + 12 = 168;   正确的写法：

select * from iw_account_log
   where trans_dt >= to_date ( '2007-04-04', 'yyyy-mm-dd') and trans_dt < to_date ( '2007-04-05', 'yyyy-mm-dd');
select qty from product where p_id = 168 - 12;2。如果是业务要求的除外，但需要在编写时咨询DBA

3。特别注意，当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数，如

   错误的写法（a.id是number类型，而b.operator_number是char类型）：

select count(*) from adm_user a, adm_action_log b where a.id = b.operator_number and a.username = '小钗';   正确的写法：

select count(*) from adm_user a, adm_action_log b where to_char(a.id) = b.operator_number and a.username = '小钗';
select count(*) from adm_user a, adm_action_log b where a.id = to_number(b.operator_number) and a.username = '小钗';   上面两种写法哪个正确？遇到这种情况时必须咨询DBA！

表连接
1。不使用ANSI连接，如inner join、left join、right join、full outer join，而使用（+）来表示外连接

   错误的写法：

select a.*, b.goods_title from iw_account_log a left join beyond_trade_base b on a.TRANS_OUT_ORDER_NO = b.trade_no
   where a.trans_code = '6003' and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)   正确的写法：

select a.*, b.goods_title from iw_account_log a, beyond_trade_base b
   where a.TRANS_OUT_ORDER_NO = b.trade_no(+) and a.trans_code = '6003'
   and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)SQLMAP的其它编写规范
1。对表的记录进行更新的时候，必须包含对gmt_modified字段的更新，并且不要使用dynamic标记，如：

   错误的写法：

update BD_CONTACTINFO
       <dynamic prepend="set">
           ......
           <isNotNull prepend="," property="gmtModified">
               GMT_MODIFIED = #gmtModified:TIMESTAMP#
           </isNotNull>
       </dynamic>
   where ID = #id#   正确的写法（当然，这里更推荐直接更新为sysdate）：

update BD_CONTACTINFO
       set GMT_MODIFIED = #gmtModified:TIMESTAMP#
       <dynamic>
           ......
       </dynamic>
   where ID = #id#2。不允许在where后添加1=1这样的无用条件，where可以写在prepend属性里，如：

   错误的写法：

select count(*) from BD_CONTRACT t where 1=1
       <dynamic>
           ......
       </dynamic>   正确的写法：

select count(*) from BD_CONTRACT t
       <dynamic prepend="where">
           ......
       </dynamic>3。对大表进行查询时，在SQLMAP中需要加上对空条件的判断语句，具体可在遇到时咨询DBA，如：

   性能上不保险的写法：

select count(*) from iw_user usr
   <dynamic prepend="where">
       <isNotEmpty prepend="AND" property="userId">
           usr.iw_user_id = #userId:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="email">
           usr.email = #email:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="certType">
           usr.cert_type = #certType:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="certNo">
           usr.cert_no = #certNo:varchar#
       </isNotEmpty>
   </dynamic>   性能上较保险的写法（防止那些能保证查询性能的关键条件都为空）：

select count(*) from iw_user usr
   <dynamic prepend="where">
       <isNotEmpty prepend="AND" property="userId">
           usr.iw_user_id = #userId:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="email">
           usr.email = #email:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="certType">
           usr.cert_type = #certType:varchar#
       </isNotEmpty>
       <isNotEmpty prepend="AND" property="certNo">
           usr.cert_no = #certNo:varchar#
       </isNotEmpty>
       <isEmpty property="userId">
            <isEmpty property="email">
                <isEmpty property="certNo">
                    query not allowed
                </isEmpty>
            </isEmpty>
       </isEmpty>
   </dynamic>   另外，对查询表单的查询控制建议使用web层进行控制而不是客户端脚本（JAVASCRIPT/VBSCRIPT）

聚合函数常见问题
1。不要使用count(1)代替count（*）
2。count(column_name)计算该列不为NULL的记录条数
3。count(distinct column_name)计算该列不为NULL的不重复值数量
4。count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(qty),0)来避免返回NULL

NULL的使用
1。理解NULL的含义，是"不确定"，而不是"空"
2。查询时，使用is null或者is not null
3。更新时，使用等于号，如：update tablename set column_name = null

STR2NUMLIST、STR2VARLIST函数的使用：
1。适用情况：使用唯一值（或者接近唯一值）批量取数据时，能够大大减少和数据库的交互次数

2。编写规范：a表必须放在from list的第一位，并且必须在select后加上下面的hint
  注意一：参数是由各个交易号拼成的字符串，以逗号间隔，不要有空格
  注意二：函数的参数是一个字符串，长度不能超过4000个字节
  注意三：函数生成的表只有一个字段，名字是column_value
  注意四：生成表的column_value字段是varchar2类型，如果希望是number类型，请使用str2numlist函数，并将vartabletype替换为numtabletype

   错误的写法（缺少hint）：

select a.column_value, b.goods_title
     from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
    where a.column_value = b.trade_no;   错误的写法（函数生成的表必须放在from list的第一位）：

select /*+ ordered use_nl(a,b) */ a.column_value, b.goods_title
     from beyond_trade_base b, TABLE(CAST(str2varlist(:1) as vartabletype)) a
    where a.column_value = b.trade_no;   正确的写法：

select /*+ ordered use_nl(a,b) */ a.column_value, b.goods_title
     from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
    where a.column_value = b.trade_no;   如果要求返回的结果记录条数和参数中交易号的个数一致，可使用外连接：

select /*+ ordered use_nl(a,b) */ a.column_value, b.goods_title
     from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
    where a.column_value = b.trade_no (+);   如果参数内的值不唯一，可能返回多行，而并不需要返回多行，可考虑使用聚合函数：

select /*+ ordered use_nl(a,b) */
       a.column_value, min(b.goods_title) goods_title
     from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
    where a.column_value = b.out_trade_no (+)
    group by a.column_value;   使用该函数与使用IN的区别：
    1。IN返回的结果是无序的，而该函数返回的结果是以参数中各值的顺序为顺序的
    2。IN返回的结果是不重复的，而该函数返回的结果可重复，取决于输入的参数

分页查询的使用
1。分页通常是先执行COUNT语句然后执行分页语句，当COUNT返回值为0的时候，应当避免执行后面的分页语句

2。有时，只须执行分页语句而无须执行COUNT语句，就不要执行COUNT语句，例如，用户下载excel格式的账户明细

3。有时，在分页前除了要统计COUNT还需要统计SUM，这些WHERE子句一致的统计应该在一条SQL中查出，而不是分多次统计

4。包含排序逻辑的分页查询写法，必须是三层select嵌套：

   错误的写法：

SELECT t1.*
     FROM (SELECT   t.*, ROWNUM rnum
               FROM beyond_trade_base t
              WHERE seller_account = :1
                AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
           ORDER BY gmt_create DESC) t1
    WHERE rnum >= :4 AND rnum < :5   正确的写法：

SELECT t2.*
     FROM (SELECT t1.*, ROWNUM rnum
             FROM (SELECT   t.*
                       FROM beyond_trade_base t
                      WHERE seller_account = :1
                        AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                        AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
                   ORDER BY gmt_create DESC) t1
            WHERE ROWNUM <= :4) t2
    WHERE rnum >= :55。不包含排序逻辑的分页查询写法，则是两层select嵌套，但对rownum的范围指定仍然必须在不同的查询层次指定：

   错误的写法：

SELECT t1.*
     FROM (SELECT t.*, ROWNUM rnum
             FROM beyond_trade_base t
            WHERE seller_account = :1
              AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
              AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')) t1
    WHERE rnum >= :4 AND rnum <= :5   正确的写法：

SELECT t1.*
     FROM (SELECT t.*, ROWNUM rnum
             FROM beyond_trade_base t
            WHERE seller_account = :1
              AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
              AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
              AND ROWNUM <= :4) t1
    WHERE rnum >= :56。注意下面两种写法的逻辑含义是不同的：

   按交易创建时间排序（倒序），然后再取前10条：

SELECT t2.*
     FROM (SELECT t1.*, ROWNUM rnum
             FROM (SELECT   t.*
                       FROM beyond_trade_base t
                      WHERE seller_account = :1
                        AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                        AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
                   ORDER BY gmt_create DESC) t1
            WHERE ROWNUM <= 10) t2
    WHERE rnum >= 1   随机取10条，然后在这10条中按照交易创建时间排序（倒序）：

SELECT t1.*
     FROM (SELECT   t.*, ROWNUM rnum
               FROM beyond_trade_base t
              WHERE seller_account = :1
                AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
                AND ROWNUM <= 10
           ORDER BY gmt_create DESC) t1
    WHERE rnum >= 17。先连接后分页与先分页后连接

   性能较差：

SELECT t2.*
     FROM (SELECT t1.*, ROWNUM rnum
             FROM (SELECT   a.*, b.receive_fee
                       FROM beyond_trade_base a, beyond_trade_process b
                      WHERE a.trade_no = b.trade_no(+)
                        AND a.seller_account = :1
                        AND a.gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                        AND a.gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
                   ORDER BY a.gmt_create DESC) t1
            WHERE ROWNUM <= :4) t2
    WHERE rnum >= :5   性能较好：

SELECT /*+ ordered use_nl(a,b) */
          a.*, b.receive_fee
     FROM (SELECT t2.*
             FROM (SELECT t1.*, ROWNUM rnum
                     FROM (SELECT   t.*
                               FROM beyond_trade_base t
                              WHERE seller_account = :1
                                AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd')
                                AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
                           ORDER BY gmt_create DESC) t1
                    WHERE ROWNUM <= :4) t2
            WHERE rnum >= :5) a,
          beyond_trade_process b
    WHERE a.trade_no = b.trade_no(+)   后面这种写法的适用情况：
   a、where子句中的查询条件都是针对beyond_trade_base表的（否则得到的结果将不相同）
   b、关联beyond_trade_process表时，用的是该表的主键或者唯一键字段（否则将改变结果集的条数）


Add Comment