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