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