oracle function white night

/***********to_date() \  to_char() \ to_number() \ trunc() \ round()  *****/


select 3/4,to_char(3/4),to_char(3/4,'0.99') ,trunc(to_char(3/4,'0.99'),1) from dual ;


select round(0.85),round(0.85,1),round(0.85,3),round(234.987,-2) from dual;


select trunc(sysdate),trunc(sysdate,'mm'),trunc(sysdate+1/2,'HH24'),trunc(sysdate+1/2,'HH24') from dual;


select round(sysdate+1/2),round(sysdate,'mm'), round(sysdate,'HH24') FROM dual;




select to_date('20120301','yyyymmdd') from dual;
SELECT to_date('2012-3-1','YYYY-MM-DD') from dual;
SELECT to_date('2012-03-01','YYYY-MM-DD') from dual;


select to_number('a') from dual;
select to_number(null) from dual;


/********decode() nvl() nvl2() ***********/


select nvl(null,2),nvl('a','b') from dual;     -- small knowleDge:  varchar ''!=null  varchar2 ''==null ;


select nvl2(null,null,'a'),nvl2(null,'a','b'),nvl2('a','b','c'),nvl2(1,2,3) from dual ;


select decode('1','1','equals one','not equals one') from dual ;  --ps 括号内的参数 绝大多数 可以用表中的字段代替，别思维定势了。
select decode('1','1','equals one') from dual ;
select decode('2','1','equals one') from dual ;
select decode('2','1','equals one','2','equals two') from dual ;
select decode('3','1','equals one','2','equals two','3','equals three') from dual ;
select decode('4','1','equals one','2','equals two','3','equals three','not 1 2 3 ') from dual ;




 -- small knowledge  , 
select 1 from dual where null != 1 ;
select 1 from dual where null != null ;
select 1 from dual where null = null ;
select 1 from dual where null is null ;  




/******************substr()   instr() ***************/
select substr('123456789',0) from dual;
select substr('123456789',1) from dual;
select substr('123456789',2) from dual;
select substr('123456789',2,5) from dual;
select substr('123456789',-2,1) from dual;
select substr('123456789',-2,2) from dual;




select instr('12345aaa789','aaa',1,1) from dual;
select instr('12345aaa78aaa9','aaa',1,2) from dual;


/**********chr() ascii() ****************/


select chr(2) from dual;
select chr(38) from dual;
select ascii('&') from dual;


select t.*,t.rowid from  temp_pub_book t;
insert into temp_pub_book(isbn)
  select 'xxx&xxx' from dual; 
insert into temp_pub_book(isbn)
  select 'xxx'||chr(38)||'xxx' from dual; 
  
/**********lpad()  rpad() ************/
 
/*********trim()***********/ 


select trim(' aaa   ') from dual;
select trim('1' from '1 aaa   1') from dual;
select trim(leading '1' from '1 aaa   1') from dual;
select trim(trailing '1' from '1 aaa   1') from dual;
 


/**********sum() count()  avg() ********************/


/**************oracle 分析函数************/
select num ,country,rownum ,dense_rank() over(order by country asc )
                           , rank() over(order by country asc )   
                               , row_number() over(order by country asc )  
                            ,dense_rank() over(partition by num  order by country asc )
                           , rank() over( partition by num  order by country asc )   
                               , row_number() over( partition by num  order by country asc )       
 from (
      select case when rownum<5 then 1
              when rownum<10 then 2
              when rownum <15 then 3
        else 4 end  num,
        case when rownum<3 then 'China'
              when rownum<9 then 'Japan'
              when rownum <13 then 'USA'
        else 'England' end    country
         from dual connect by rownum <21
) t ;




/**********随机函数******/
select dbms_random.value, dbms_random.value(0,18)  from dual;


http://cache.baiducontent.com/c?m=9f65cb4a8c8507ed4fece763105392230e54f73260878e482a958448e435061e5a2eb9e87b7750598f90262052b21a1cbbaa6d36601e20b599cd9548d7a6922a328223347a1f865612a443e9941831847dd00ce6f259b4ebae6584afa2c4af2744b927120bf3e7ff2c1715ba7880122697a78e391f4863ca&p=882a9141c89d12a05aaed1384e05&newp=882a91418e801ffb44a5c7710f4392695c16ed64388f934b2bc8db&user=baidu&fm=sc&query=oracle%BA%AF%CA%FD%B4%F3%C8%AB&qid=&p1=1




/*********oracle self defined function ***********/






/*************************/
REGEXP_LIKE
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR




select 1 from dual where regexp_like('abc','b') ;
select 1 from dual where regexp_like('33333','\d') ;
select 1 from dual where regexp_like('dddd','\d') ;


select regexp_replace('aaa','a','b') from dual ;




select regexp_substr('aa345abbb','\d+') from dual ;


select REGEXP_INSTR('abeee444c','\d+') from dual;




/*********wm_concat()**************/
select  to_char(wm_concat( prj_type ) ) from (
  select distinct prj_type   from project 
)
select  *  from sys_resource s 
select s.id ,s.parent_id , sys_connect_by_path(s.zh_cn_name,'-') from sys_resource s 
where s.zh_cn_name is not null and s.resource_type='menu'
start with s.parent_id = 0
connect by s.parent_id = prior s.id;


每一次都在徘徊孤单中坚强
每一次就算很受伤也不闪泪光
我知道我一直有双隐形的翅膀
带我飞飞过绝望
不去想他们拥有美丽的太阳
我看见每天的夕阳也会有变化
我知道我一直有双隐形的翅膀
带我飞给我希望
我终於看到所有梦想都开花
追逐的年轻歌声多嘹亮
我终於翱翔用心凝望不害怕
哪里会有风就飞多远吧
隐形的翅膀让梦恒久比天长
留一个愿望让自己想像