Oracle learning note

oracle SQL
select 'para1' || 'para2' as "para"                                                      must ""
select count(*),c.id,c.name                                                              count(*) not use with "group by" count total rows number,otherwise "count(*)" always equal 1
  from table t
 where c.name in ('test1','test2') group by c.name,c.id having count()                   show data group by "count(*)","id","name",selected fields must equal to group by fields except count(*)

(select a.p1 as ap,b.p2 as bp from tablea a join tableb b on a.id = b.id) tablec         combine a&b to c

select (sysdate - c.date) *24 *60 as d,round(123.456, 0) r                               return 123,返回小数点后面0位
 from test 
 where nvl（arg,00）<> 23;                                                                代表如果前面的arg的值为null那么返回的值为后面的00,oracle不等于标准写法<>

select distinc * from table t where 1<>1                                                  只取table结构，不取值
select * from table t where 1=1                                                           用于动态创建sql语句以至于不会报错
 

trunc(123.45),trunc(sysdate,'mm')                                                         函数截取时不进行四舍五入2017/1/1 18:00:00 会显示为2017/1/1
round(123.456, 2)                                                                         函数截取时进行四舍五入
to_char(sysdate,'mon')
substr(para,-1,2)                                                                         截取para从最后1个字母开始数的2个字母
to_date('2017-08-09','yyyy-MM-dd')
sign(p1-p2)                                                                               函数根据某个值是0、正数还是负数，分别返回0、1、-1
select a.* from A a where exists (select b.* from B b)                                    适合B表比A表数据大的情况，因为exists()会执行A.length次,它并不缓存exists()结果集

if v_name='vickey' then 
    dbms_output.put_line('success!');
else
  dbms_output.put_line('failed');
end if;
decode(filed,ifval1,thensetval1,ifval2,thensetval2,...,else)