oracle常用函数汇总

以下是对oracle中的常用函数进行了汇总介绍需要的朋友可以过来参考下

一运算符
算术运算符+ * / 可以在select 语句中使用
连接运算符|| select deptno|| dname from dept;
比较运算符> >= = != < <= like between is null in
逻辑运算符not and or
集合运算符 intersect union union all minus
要求对应集合的列数和数据类型相同
查询中不能包含long 列
列的标签是第一个集合的标签
使用order by时必须使用位置序号不能使用列名

例集合运算符的使用

复制代码 代码如下:
intersect union union all minus
select * from emp intersect select * from emp where deptno= ;
select * from emp minus select * from emp where deptno=;
select * from emp where deptno= union select * from emp where deptno in (); 不包括重复行
select * from emp where deptno= union all select * from emp where deptno in (); 包括重复行

二ORACLE日期时间函数大全
TO_DATE格式(以时间: ::为例)

Year:
yy two digits 两位年 显示值:
yyy three digits 三位年 显示值:
yyyy four digits 四位年 显示值:

Month:
mm number 两位月 显示值:
mon abbreviated 字符集表示 显示值:月若是英文版显示nov
month spelled out 字符集表示 显示值:月若是英文版显示november

Day:
dd number 当月第几天 显示值:
ddd number 当年第几天 显示值:
dy abbreviated 当周第几天简写 显示值:星期五若是英文版显示fri
day spelled out 当周第几天全写 显示值:星期五若是英文版显示friday
ddspth spelled out ordinal twelfth

Hour:
hh two digits 小时进制 显示值:
hh two digits 小时进制 显示值:

Minute:
mi two digits 进制 显示值:

Second:
ss two digits 进制 显示值:

其它
Q digit 季度 显示值:
WW digit 当年第几周 显示值:
W digit 当月第几周 显示值:

小时格式下时间范围为 :: ::
小时格式下时间范围为 :: ::

日期和字符转换函数用法（to_dateto_char）

select to_char(sysdateyyyymmdd hh:mi:ss) as nowTime from dual; //日期转化为字符串
select to_char(sysdateyyyy) as nowYear from dual; //获取时间的年
select to_char(sysdatemm) as nowMonth from dual; //获取时间的月
select to_char(sysdatedd) as nowDay from dual; //获取时间的日
select to_char(sysdatehh) as nowHour from dual; //获取时间的时
select to_char(sysdatemi) as nowMinute from dual; //获取时间的分
select to_char(sysdatess) as nowSecond from dual; //获取时间的秒

select to_date( ::yyyymmdd hh:mi:ss) from dual//

select to_char( to_date(J)Jsp) from dual

显示Two Hundred TwentyTwo

求某天是星期几
select to_char(to_date(yyyymmdd)day) from dual;
星期一
select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
也可以这样
TO_DATE ( YYYYmmdd NLS_DATE_LANGUAGE = American)

两个日期间的天数
select floor(sysdate to_date(yyyymmdd)) from dual;

时间为null的用法
select id active_date from table
UNION
select TO_DATE(null) from dual;
注意要用TO_DATE(null)

月份差
a_date between to_date(yyyymmdd) and to_date(yyyymmdd)
那么月号中午点之后和月号的点之前是不包含在这个范围之内的
所以当时间需要精确的时候觉得to_char还是必要的

日期格式沖突问题
输入的格式要看你安装的ORACLE字符集的类型 比如: USASCII date格式的类型就是: Jan
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date(yyyymmdd)dayNLS_DATE_LANGUAGE = American) from dual;
注意我这只是举了NLS_DATE_LANGUAGE当然还有很多
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS

复制代码 代码如下:
select count(*)
from ( select rownum rnum
from all_objects
where rownum <= to_date(yyyymmdd) to_date(
yyyymmdd)+
)
where to_char( to_date(yyyymmdd)+rnum D )
not in ( )

查找至间除星期一和七的天数
在前后分别调用DBMS_UTILITYGET_TIME 让后将结果相减(得到的是/秒 而不是毫秒)

查找月份

复制代码 代码如下:
select months_between(to_date(MMDDYYYY)to_date(MMDDYYYY)) "MONTHS" FROM DUAL;

select months_between(to_date(MMDDYYYY)to_date(MMDDYYYY)) "MONTHS" FROM DUAL;

Next_day的用法

复制代码 代码如下:
Next_day(date day)

MondaySunday for format code DAY
MonSun for format code DY
for format code D

select to_char(sysdatehh:mi:ss) TIME from all_objects
注意第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题

复制代码 代码如下:
create or replace function sys_date return date is
begin
return sysdate;
end;

select to_char(sys_datehh:mi:ss) from all_objects;

获得小时数
extract()找出日期或间隔值的字段值

复制代码 代码如下:
SELECT EXTRACT(HOUR FROM TIMESTAMP ::) from offer
SQL> select sysdate to_char(sysdatehh) from dual;

SYSDATE TO_CHAR(SYSDATEHH)

::

SQL> select sysdate to_char(sysdatehh) from dual;

SYSDATE TO_CHAR(SYSDATEHH)

::

年月日的处理

复制代码 代码如下:
select older_date
newer_date
years
months
abs(
trunc(
newer_date
add_months( older_dateyears*+months )
)
) days

from ( select
trunc(months_between( newer_date older_date )/) YEARS
mod(trunc(months_between( newer_date older_date )) ) MONTHS
newer_date
older_date
from (
select hiredate older_date add_months(hiredaterownum)+rownum newer_date
from emp
)
)

处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) + ) yyyymmdd)last_day(sysdate) from dual
找出今年的天数
select add_months(trunc(sysdateyear) ) trunc(sysdateyear) from dual
闰年的处理方法
to_char( last_day( to_date( | | :yearmmyyyy) ) dd )
如果是就不是闰年
yyyy与rrrr的区别

复制代码 代码如下:
YYYY TO_C

yyyy
rrrr
yyyy
rrrr

不同时区的处理
select to_char( NEW_TIME( sysdate GMTEST) dd/mm/yyyy hh:mi:ss) sysdate
from dual;

秒钟一个间隔

复制代码 代码如下:
Select TO_DATE(FLOOR(TO_CHAR(sysdateSSSSS)/) * SSSSS) TO_CHAR(sysdateSSSSS)
from dual
::
SSSSS表示位秒数

一年的第几天
select TO_CHAR(SYSDATEDDD)sysdate from dual

::

计算小时分秒毫秒

复制代码 代码如下:
select
Days
A
TRUNC(A*) Hours
TRUNC(A** *TRUNC(A*)) Minutes
TRUNC(A*** *TRUNC(A**)) Seconds
TRUNC(A**** *TRUNC(A***)) mSeconds
from
(
select
trunc(sysdate) Days
sysdate trunc(sysdate) A
from dual
)
select * from tabname
order by decode(modeFIFO)*to_char(rqyyyymmddhhmiss);

//
floor((datedate) /) 作为年
floor((datedate ) /) 作为月
d(mod(datedate ) )作为日

next_day函数 返回下个星期的日期day为或星期日星期六表示星期日
next_day(sysdate)是从当前开始下一个星期五后面的数字是从星期日开始算起

日 一 二 三 四 五 六



select (sysdateto_date( ::yyyymmdd hh:mi:ss))*** from ddual
日期 返回的是天 然后 转换为ss

round[捨入到最接近的日期](day:捨入到最接近的星期日)
select sysdate S
round(sysdate) S
round(sysdateyear) YEAR
round(sysdatemonth) MONTH
round(sysdateday) DAY from dual

trunc[截断到最接近的日期单位为天] 返回的是日期类型
select sysdate S
trunc(sysdate) S //返回当前日期无时分秒
trunc(sysdateyear) YEAR //返回当前年的月日无时分秒
trunc(sysdatemonth) MONTH //返回当前月的日无时分秒
trunc(sysdateday) DAY //返回当前星期的星期天无时分秒
from dual

返回日期列表中最晚日期
select greatest(月月月) from dual

计算时间差
注:oracle时间差是以天数为单位所以换算成年月日

  select floor(to_number(sysdateto_date(  ::yyyymmdd hh:mi:ss))/) as spanYears from dual  //时间差年
select ceil(moths_between(sysdateto_date(  ::yyyymmdd hh:mi:ss))) as spanMonths from dual  //时间差月
select floor(to_number(sysdateto_date(  ::yyyymmdd hh:mi:ss))) as spanDays from dual  //时间差天
select floor(to_number(sysdateto_date(  ::yyyymmdd hh:mi:ss))*) as spanHours from dual  //时间差时
select floor(to_number(sysdateto_date(  ::yyyymmdd hh:mi:ss))**) as spanMinutes from dual  //时间差分
select floor(to_number(sysdateto_date(  ::yyyymmdd hh:mi:ss))***) as spanSeconds from dual  //时间差秒

更新时间
注:oracle时间加减是以天数为单位设改变量为n所以换算成年月日
  select to_char(sysdateyyyymmdd  hh:mi:ss)to_char(sysdate+n*yyyymmdd hh:mi:ss) as newTime  from dual //改变时间年
select to_char(sysdateyyyymmdd  hh:mi:ss)add_months(sysdaten) as newTime from  dual //改变时间月
select  to_char(sysdateyyyymmdd hh:mi:ss)to_char(sysdate+nyyyymmdd  hh:mi:ss) as newTime from dual //改变时间日
select  to_char(sysdateyyyymmdd  hh:mi:ss)to_char(sysdate+n/yyyymmdd hh:mi:ss) as newTime  from dual //改变时间时
select to_char(sysdateyyyymmdd  hh:mi:ss)to_char(sysdate+n//yyyymmdd hh:mi:ss) as newTime  from dual //改变时间分
select to_char(sysdateyyyymmdd  hh:mi:ss)to_char(sysdate+n///yyyymmdd hh:mi:ss) as  newTime from dual //改变时间秒

查找月的第一天最后一天
SELECT Trunc(Trunc(SYSDATE MONTH) MONTH) First_Day_Last_Month
Trunc(SYSDATE MONTH) / Last_Day_Last_Month
Trunc(SYSDATE MONTH) First_Day_Cur_Month
LAST_DAY(Trunc(SYSDATE MONTH)) + / Last_Day_Cur_Month
FROM dual;

三 字符函数(可用于字面字符或数据库列)
字符串截取
select substr(abcdef) from dual

查找子串位置
select instr(abcfdgfdhdfd) from dual

字符串连接
select HELLO||hello world from dual;

)去掉字符串中的空格
select ltrim( abc) s
rtrim(zhang ) s
trim( zhang ) s from dual

)去掉前导和后缀
select trim(leading from ) s
trim(trailing from ) s
trim( from ) s from dual;

返回字符串首字母的Ascii值
select ascii(a) from dual

返回ascii值对应的字母
select chr() from dual

计算字符串长度
select length(abcdef) from dual

initcap（首字母变大写） lower(变小写)upper(变大写)
select lower(ABC) s
upper(def) s
initcap(efg) s
from dual;

Replace
select replace(abcbxy) from dual;

translate
select translate(abcbxx) from dual; x是位

lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad(func=) s rpad(func) s from dual;
select lpad(dname=) from dept;

decode[实现if then 逻辑] 注:第一个是表达式最后一个是不满足任何一个条件的值
select deptnodecode(deptno其他) from dept;
例:
select seedaccount_namedecode(seed) from t_userInfo//如果seed为则取;为取;其它取
  select seedaccount_namedecode(sign(seed)big seedlittle  seedequal seed) from  t_userInfo//如果seed>则显示大;为则显示小;其它则显示相等

case[实现switch case 逻辑]

复制代码 代码如下:
SELECT CASE XFIELD
WHEN XFIELD < THEN XFIELD 小于
WHEN XFIELD < THEN XFIELD 小于
WHEN XFIELD < THEN XFIELD 小于
ELSE UNBEKNOWN
END
FROM DUAL

注:CASE语句在处理类似问题就显得非常灵活当只是需要匹配少量数值时用Decode更为简洁

四数字函数
取整函数（ceil 向上取整floor 向下取整）
select ceil() Nfloor() N from dual;

取幂(power) 和 求平方根(sqrt)
select power() Nsqrt() N from dual;

求余
select mod() from dual;

返回固定小数位数 (round:四捨五入trunc:直接截断)
select round() Ntrunc() N from dual;

返回值的符号(正数返回为负数为)
select sign()sign() from dual;

五转换函数
to_char()[将日期和数字类型转换成字符类型]
) select to_char(sysdate) s
to_char(sysdateyyyymmdd) s
to_char(sysdateyyyy) s
to_char(sysdateyyyymmdd hh:mi:ss) s
to_char(sysdate hh:mi:ss) s
to_char(sysdateDAY) s
from dual;
) select salto_char(sal＄) nto_char(sal＄) n from emp

to_date()[将字符类型转换为日期类型]
insert into emp(empnohiredate) values(to_date(yyyymmdd));

to_number() 转换为数字类型
select to_number(to_char(sysdatehh)) from dual; //以数字显示的小时数

六其他函数
user:
返回登录的用户名称
select user from dual;

vsize:
返回表达式所需的字节数
select vsize(HELLO) from dual;

nvl(exex):
ex值为空则返回ex否则返回该值本身ex（常用）
例如果雇员没有佣金将显示否则显示佣金
select commnvl(comm) from emp;

nullif(exex):
值相等返空否则返回第一个值
例如果工资和佣金相等则显示空否则显示工资
select nullif(salcomm)salcomm from emp;

coalesce:
返回列表中第一个非空表达式
select commsalcoalesce(commsalsal*) from emp;

nvl(exexex) :
如果ex不为空显示ex否则显示ex
如查看有佣金的雇员姓名以及他们的佣金
select nvl(commename) as HaveCommNamecomm from emp;


七分组函数
max min avg count sum
整个结果集是一个组
) 求部门 的最高工资最低工资平均工资总人数有工作的人数工种数量及工资总和

复制代码 代码如下:
select max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp where deptno=;

带group by 和 having 的分组
)按部门分组求最高工资最低工资总人数有工作的人数工种数量及工资总和

复制代码 代码如下:
select deptno max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp group by deptno;

)部门的最高工资最低工资总人数有工作的人数工种数量及工资总和

复制代码 代码如下:
select deptno max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp group by deptno having deptno=;

stddev 返回一组值的标准偏差
select deptnostddev(sal) from emp group by deptno;
variance 返回一组值的方差差
select deptnovariance(sal) from emp group by deptno;

带有rollup和cube操作符的Group By
rollup 按分组的第一个列进行统计和最后的小计
cube 按分组的所有列的进行统计和最后的小计
select deptnojob sum(sal) from emp group by deptnojob;
select deptnojob sum(sal) from emp group by rollup(deptnojob);
cube 产生组内所有列的统计和最后的小计
select deptnojob sum(sal) from emp group by cube(deptnojob);

八临时表
只在会话期间或在事务处理期间存在的表
临时表在插入数据时动态分配空间

复制代码 代码如下:
create global temporary table temp_dept
(dno number
dname varchar())
on commit delete rows;
insert into temp_dept values(ABC);
commit;
select * from temp_dept; 无数据显示数据自动清除
on commit preserve rows:在会话期间表一直可以存在（保留数据）
on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据）

除法

复制代码 代码如下:
select MOD() from dual 取余
select trunc( /) from dual 取整 trunc () =
select ceil(/) from dual 取整 ceil() =