常见ORACLE 函数

select replace('hello,world!','o','aa') as replace from dual;

select ltrim('   hello,world!') ltrim from dual ;
select rtrim('   hello,world!     ') rtrim from dual ;
select ltrim('    hello,world!   ') ltrim from dual ;
select trim('    hello,world!   ') trim from dual ;
select null('a',null)  from dual;
select length('hello,world!') as length  from dual;
select substr('hello,world!',-3) from dual;
/*select charindex('e','hello,world!') from dual;*/
select instr("varchar",'string')  from dual;

select  instr('ss.sss', '.', 1, 1) as a from dual;
select  instr('ss.ss.s', '.', -4, 1) as  from dual;

select lower('HELLO,WORLD!')  from dual;
select UPPER('hello,world!')  from dual;
select rand() from dual;

select ascii('j') j,ascii('o') o,ascii('a') a,ascii(' n') n from dual; 

/*select dateadd(day,2,'2004-10-15') from dual; */
select sysdate from dual;
   select to_char(sysdate,'dd-mm-yyyy day') from dual;
select concat('010-','88888888')||'转23'  高乾竞电话 from dual;
select initcap('smith') upp from dual;
select lpad(rpad('gao',4,'*'),17,'*')from dual;
select TRIM('s' from 'string') from dual;
select power(2,10),power(3,3) from dual;
select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
select last_day(sysdate) from dual;
select greatest('啊','安','天','操','爷') from dual;
select greatest(1,2,4,30,6) from dual;
select user from dual;
select username,user_id from dba_users where user_id=uid;
select userenv('sessionid') from dual;
select userenv('instance') from dual;

select distinct table_name from user_tab_columns where column_name='id';

select * from sp_resource;
select sr.resource_category_id,sum(sr.id) from sp_resource sr group by sr.resource_category_id;
select sr.sp_id,sum(sr.id) from sp_resource sr group by sr.sp_id;
select sr.sp_id,sum(sr.id) from sp_resource sr group by sr.sp_id;
selectr count(sr.sp_id),sum(sr.id) from sp_resource sr group by sr.sp_id;

select rownum num,a.*
  from (select count(sr.sp_id) count,sr.sp_id, sum(sr.id)
          from sp_resource sr
         group by sr.sp_id) a;

select to_char(sysdate ,'yyyy-mm-dd HH:mm:ss') from dual

select nvl('','456') from dual;
select nvl('123','456') from dual;