Oracle sort commonly used in the treatment of the value NULL

  Oracle sort commonly used in the treatment of the value NULL 

 ç¼ºçœå¤„理  1,  dealing with default 
 ä½¿ç”¨nvl函数  Oracle in the Order by default when considered null is maximum, therefore, if the ASC ascending ranked in the final, DESC descending ranked in the top two,  use nvl function 
  nvl function can be input parameters for the space-time conversion to a particular value, such as 
  nvl (employee_name, 'John') that when employee_name for the space-time is to return to 'John', if not empty then return to employee_name 
  Through this function can be customized null sort of position.   ä½¿ç”¨decode函数  3,  the use of decode function 
  decode function more powerful than nvl function, it can also be the same input parameters for the space-time conversion to a particular value, such as 
  decode (employee_name, null, 'John', employee_name) said that when employee_name for the space-time is to return to 'John', if not empty then return to employee_name 
  Through this function can be customized null sort of position.   ä½¿ç”¨case语法  4,  the use of case grammar 
  Case syntax is after Oracle 9i support, is a more flexible syntax, the same sort can also be applied 
  Such as: 
  select
  from employee 
  order by (case employee_name 
  when null then 
  'John' 
  else 
employee_name
  end) 
  When employee_name for that space-time is to return to 'John', if not to return to the air employee_name 
  Grammar through the same case can be customized null sort of position. 
 ä½¿ç”¨nulls first或者nulls last语法  5,  or the use of nulls first nulls last syntax 
  Nulls first and nulls last is Oracle Order by the support of grammar 
  If specified in the Order by the expression Nulls first said null value of the records will be in the front row (whether or asc desc) 
  If specified in the Order by the expression Nulls last null value, said the record will be ranked in the final (whether or asc desc) 
  The use of grammar is as follows: 
  — Nulls always on the front 
  select * from zl_cbqc order by cb_ld nulls first 
  — Nulls always on the last 
  select * from zl_cbqc order by cb_ld desc nulls last 
   Ye Zheng Sheng (MKing) 2007-08-04  

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button