注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

陈钟鸣的博客

独立之精神,自由之思想.

 
 
 

日志

 
 

oracle 精妙函数  

2009-01-06 11:00:58|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
oracle 精妙函数

1.日期函数
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

  1.1 ADD_MONTHS --增加月份
select add_months(sysdate,1),add_months(sysdate,-1),sysdate from dual;

  1.2  DBTIMEZONE  --取服务器时区
SELECT DBTIMEZONE FROM DUAL;

  1.3  extract --提取日期元素
select extract(year from sysdate) y,
extract(month from sysdate) m,
extract(day from sysdate) d,
sysdate
from dual;

  1.4  last_day --每月最后一天
SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

  1.5  months_between --间隔月数
select months_between(to_date('20090201','yyyymmdd'),to_date('20090101','yyyymmdd'))
from dual;

  1.6  next_day  --某日期下个星期几是哪天[乱了。说不清楚]
select next_day(sysdate,1),sysdate from dual;
select next_day(sysdate,'日曜日'),sysdate from dual;

  1.7  round --日期四舍五入
select round(to_date('2008-07-01','yyyy-mm-dd'),'year')
,round(to_date('2008-06-30','yyyy-mm-dd'),'year') 
from dual;

  1.8  to_char
select to_char(sysdate,'FMyyyymmdd'),to_char(sysdate,'yyyymmdd') from dual;
select to_char(sysdate,'EERR Mon dd','NLS_CALENDAR=''Japanese Imperial''') from dual;
select to_char(sysdate,'yyyy Month dd','NLS_DATE_LANGUAGE=''Japanese''') from dual;

  1.9  trunc
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;

2.分析函数
   2.1 last_value
select ename,hiredate,deptno,last_value(hiredate) over(partition by deptno order by hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from emp;

   2.2 lag,lead
select empno,ename,lag(empnom1) over(order by empno),lag(ename,1) over(order by empno) from emp;
select empno,ename,lead(empno,1) over(order by empno),lead(ename,1) over(order by empno) from emp;

   2.3 MEDIAN(10G)
select empno,ename,sal,MEDIAN(sal) over (partition by deptno) from emp;

   2.4 rank,dense_rank
select empno,ename,deptno,sal,rank() over (partition by deptno order by sal) from emp;
select empno,ename,deptno,sal,dense_rank() over (partition by deptno order by sal) from emp;
select empno,ename,deptno,sal,dense_rank() over (partition by deptno order by sal),rank() over (order by sal) from emp;

   2.5 ntile
-以下例子取工资排名前1/5的员工。
select empno,ename,sal,ntile(5) over (order by sal) from emp;

   2.6 keep取每个部门中入职时间最早的一个员号。
select empno
,ename
,hiredate
,min(empno) keep (dense_rank first order by hiredate) over (partition by deptno) empno_e
,decode(min(empno) keep (dense_rank first order by hiredate) over (partition by deptno),empno,'〇','') earliest_flg 
from emp;

3.字符串函数
  3.1 ascii,chr
select ascii('α') from dual;
set serverout on size 8000
begin
for i in 0..24 
loop
dbms_output.put_line(chr(33727+i));
end loop;
end;
/
    
    3.2 lpad,rpad
select lpad('10',7,'0') from dual;

    3.3 TRANSLATE
SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

    3.4 INSTR
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) "Reversed Instring"  FROM DUAL;
SELECT INSTR('CORPORATE FLOOR','OR', -3, 1) "Reversed Instring"  FROM DUAL;
SELECT INSTR('CORPORATE FLOOR','OR', 1) "Reversed Instring"  FROM DUAL;
SELECT INSTR('CORPORATE FLOOR','OR', 1,2) "Reversed Instring"  FROM DUAL;
SELECT INSTR('CORPORATE FLOOR','OR') "Reversed Instring"  FROM DUAL;

4.编码与解码函数
   4.1 decode
select ename,deptno,decode(deptno,10,'dept 10',20,'dept 20',30,'dept 30',deptno) from emp;
  
   4.2 dump
select dump('1234') from dual;

   4.3 vsize
select length('あいう陳'),vsize('あいう陳') from dual;

   4.4 case when then... end

select ename,sal,case when sal<1000 then sal+1000 
           when sal <2000 then sal+500
           else sal
       end
from emp;

select ename,sal,deptno, case deptno when 10 then sal+1111
                                    when 20 then sal+2222
                                    when 30 then sal+3333
                                    when 50 then sal+5555
                         end
from emp;

5.null函数

  5.1 COALESCE
select coalesce(null,20,10,38) from dual;

  5.2 LNNVL
select * from lnnvl_ex where a=b;
select * from lnnvl_ex where lnnvl(a=b);

   5.3 NVL,NVL2
select nvl(a,0),nvl2(a,b,0), a,b from lnnvl_ex;

   5.4 NULLIF
select NULLIF(1,1),NULLIF(1,0) from dual;

6.环境函数
   6.1 userenv
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;

   6.2 user,uid
SELECT USER, UID FROM DUAL;

7.聚合函数
  7.1 avg--分组平均
select deptno,avg(sal) from emp group by deptno;
  
  7.2 count
select deptno,count(*) from emp group by deptno;

    7.3 sum
select deptno,sum(sal) from emp group by deptno;
   
8.通用比较函数
  8.1 greatest
select greatest('HARRY','XWINDOW','HARRIOT','A') from dual;
  8.2 least
select least('HARRY','XWINDOW','HARRIOT','Y') from dual;

9.大对象函数
  9.1 empty_blob()
insert into XXX(id,lob) values(1,empty_blob());

  9.2 empty_clob()
insert into XXX(id,lob) values(1,empty_clob());

  评论这张
 
阅读(87)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017