可以参考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;