Oracle函数
1、字符函数
select 'asdas'||'dasd' from dual;
select concat('asfsad','sdafsd') from dual;
select initcap('sadf') from dual;
select initcap('对方告诉对方') from dual;
select instr('sdfgsd','f') from dual;
select length('dfvgdfs ') from dual;
select length(trim(' df vg dfs ')) from dual;
select replace('sadfaasd','a','c') from dual;
select substr('dfesrfer','3','5') from dual;
select lower('SEDFdsfASDFgsd') from dual;
select upper('SGDSFGdsfgDSFG') from dual;
2、数值函数
select abs(-90) from dual;
select ceil('90.1') from dual;
select ceil('90.9') from dual;
select floor('96.98') from dual;
select mod('12.5','3') from dual;
select power('2','10') from dual;
select round('32.4') from dual;
select round('32.6') from dual;
select sqrt('16') from dual;
select trunc('1.23456','2') from dual;
3、日期函数
select add_months(sysdate,'2') from dual;
select last_day(sysdate) from dual;
select months_between(sysdate,to_date('20181123','yyyymmss')) from dual;
select next_day(sysdate,'星期一') from dual;
select round(sysdate,'yyyy') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'day') from dual;
4、转换函数
select to_char(sysdate) from dual;
select to_date('2017-01-01','yyyy-mm-dd') from dual;
select to_number('20170101') from dual;
5、其他函数
select * from emp;
select nvl(e.commission_pct,0)as commission_pct,e.* from emp e;
select PRICE,MINNUM,NVL(MAXNUM,9999999) from T_PRICETABLE where OWNERTYPEID=1;
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限') from T_PRICETABLE where OWNERTYPEID=1;
select name,decode( ownertypeid,1,'居民',2,'行政事业单位',3,'商业') as 类型 from T_OWNERS;
6、分析函数
select rank() over(order by usenum desc),usenum from t_account;
select dense_rank() over(order by usenum desc),usenum from t_account;
select row_number() over(order by usenum desc ),usenum from T_ACCOUNT