Oracle中常用SQL语句及函数收集

可以参考http://www.techonthenet.com/oracle/functions/index.php关于详细的函数介绍，下面简单陈述一些所看到的并用例子验证的。

• AS更改结果集列的名称或为派生列分配名称，可以省略，也可以保留

  select objectid as oid from xuser_mv;
  select objectid oid from xuser_mv;
  select count(*) total from (select objectid from xuser_mv) tableid;
  select count(*) total from (select objectid from xuser_mv) as tableid; //wrong

  
  
• to_char（http://www.techonthenet.com/oracle/functions/to_char.php）：将其他类型转化为想要的char型，如日期转化，小数转化等

  select to_char(createddate,'DD/MM/YYYY') dat from xuser_mv;
  select contenttype,to_char(avg(count),'099.99') from xobjectinfo_mv group by contenttype order by 2 desc;

  
  
• decode函数(http://www.techonthenet.com/oracle/functions/decode.php):相当于条件判断的if...else if...else...语句，就是当expression如果取值为searchi，则函数返回结果为resulti，否则就是default值。对于这个函数，我们可以输入不同的expression值然后选择不同的结果。比如获取最近30天或者获取之前某一个月的所有数据，可以传递expression值来控制。
  

  DECODE( expression , search1 , result1 [, searchi , resulti]... [, default] )
  select * from xuser_mv where 
  decode('MONTH','30',createddate,sysdate+30) between sysdate-30 and sysdate or                --if MONTH = 30 then it is true
  decode('MONTH','30',to_char(sysdate+31,'MM/YYYY'),to_char(createddate,'MM/YYYY')) = 'MONTH'; --if MONTH != 30 then it is true and check the month

• coalesce函数（http://www.techonthenet.com/oracle/functions/coalesce.php）：返回第一个非null的值

  select coalesce(null*3,0,0.1) dat from xuser_mv;  --value is 0

  
  
• round函数（http://www.techonthenet.com/oracle/functions/round_nbr.php）,

  select contenttype, round(avg(count),2) from xobjectinfo_mv group by contenttype order by 2 desc;
  select round(to_date('22/10/2013','DD/MM/YYYY'),'Q') from xuser_mv;

  
  
• trunc(http://www.techonthenet.com/oracle/functions/trunc_nbr.php)，保留小数点后多少位，功能类似于round。只不过round会四舍五入。

  select contenttype, trunc(avg(count),2) from xobjectinfo_mv group by contenttype order by 2 desc;
  select trunc(to_date('22/11/2013','DD/MM/YYYY'),'Q') from xuser_mv;

  
  
• case语句（http://technet.microsoft.com/en-us/library/ms181765.aspx）：个人觉得case语句和decode函数有类似功能

  select 
  case createddate 
  when sysdate-6 then sysdate  --when_expression
  else createddate+6 
  end newdate
  from xuser_mv;
  select 
  case 
  when createddate > sysdate-7 then sysdate  --bool_expression
  else createddate 
  end newdate
  from xuser_mv;

  
  
• 正则表达式函数如REGEXP_INSTR以及REGEXP_SUBSTR(http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm):参数第一个为输入的原串，第二个为用来匹配的正则表达式，之后的参数可有可无，第三个是开始搜索的位置默认1，第四个是匹配的次数默认为第一次，第五个是匹配的配置参数（i不管大小写，c大小写，n的话.匹配所有，m则区分行）。对于函数REGEXP_REPLACE则插入第三个参数为替换的string。

  select REGEXP_SUBSTR(lastconnecteddate,'[^ ]+') from xuser_mv;
  select REGEXP_SUBSTR(lastconnecteddate,'[^ ]+',1,2) from xuser_mv;
  select REGEXP_INSTR(lastconnecteddate,'[^ ]+',1,3) from xuser_mv;
  select REGEXP_REPLACE(lastconnecteddate,'[^ ]+','aaaa',1,2) from xuser_mv;