Oracle常用函数

同之前MySql部分的函数在此不再赘述，这里介绍Oracle中独用的一些函数

 

一、日期函数：

sysdate：返回当前时间

add_months(d，n)：在d日期上，加上指定n个月

last_day（d，n）：d日期当前月的最后一天

months_between（d1，d2）：计算d1与d2之间相差的月份

extract（所提部分  from  date）：提取日期中的指定部分

　　其中：提取部分可以是：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。 YEAR、MONTH、DAY可以与 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配；但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。

 

 

 

二、转换函数：

转换函数将值从一种数据类型转换为另外一种数据类型。

 

1、  TO_CHAR( d | n [,fmt] )：将日期或数字转换为指定格式的字符串。fmt为指定的格式

　　select to_char(sysdate,'yyyy') from dual;

　　select to_char(sysdate,'fmyyyy-mm-dd') from dual;

　　select to_char(sal,'L999,999,999') from emp;

　　select to_char(sysdate,’D’) from dual;//返回星期

　　举个栗子：SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS')"date" FROM dual;

  

2、TO_DATE(X,[,fmt])

把一个字符串以fmt格式转换成一个日期类型

举个栗子： to_date('20020812','yyyyMMdd')

 

3、TO_NUMBER(X,[,fmt])

把一个字符串以fmt格式转换为一个数字

　　select to_number('13')+to_number('14') from dual

三、其他单行函数：

1、NVL(X,VALUE)    类似于MySql中的  IFNULL();

如果X为空，返回value，否则返回X

 

2、NVL2(x,value1,value2)　　类似于MySql中的 IF()；

如果x非空，返回value1，否则返回value2

 

3、dbms_random.string('a',6)   生成一个随机的六位字符

 　 'u','U'    :    大写字母
      'l','L'    :    小写字母
      'a','A'    :    大、小写字母
      'x','X'    :    数字、大写字母
      'p','P'    :    可打印字符

4、dbms_random.value(a，b)　　生成一个指定范围a~b的小数

 

5、concat（‘a’，‘b’）====>'a' || 'b'　　拼接字符串

 

6、replace（xxx，'a'，'b'）　　将xxx中的‘a’字符全部用‘b’字符来替换

 

7、instr('Hello World','or') 　　查询‘or’子串在字符串‘Hello World’中的下表位置（下标从1开始）

 

8、trim('   Mr Smith   ')　　 过滤首尾空格 

 

9、decode( 条件 , 值1 , 返回值1 , 值2 , 返回值2 , ...值n , 返回值n ,  缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

例如：select empno, ename, sal, decode(deptno, 10, '财务部',20, '研发部',30, '销售部','未知部门') from emp;

select decode(5+4,9,'x','y')from dual；计算5+4，结果等于9，则返回x ,否则返回y

decode(字段或字段的运算，值1，值2，值3）

       这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

使用方法： 

比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

 

10、取整随机数：

--四位数

select trunc(dbms_random.value(1000,9999)) from dual; --按照指定的精度截取一个数

select round(dbms_random.value(1000,9999)) from dual; --按照指定的精度进行四舍五入

select ceil(dbms_random.value(1000,9999)) from dual; --ceil返回大于或等于，给出数字的最小整数

select floor(dbms_random.value(1000,9999)) from dual; --floor取整数位

select dbms_random.value from dual; --dbms_random.value小数（0-1）

select dbms_random.value(0,100) from dual; 小数（0-100）

select substr(cast(dbms_random.value as varchar(38)),3,20) from dual; --长度为20的随机字串

select dbms_random.normal from dual; --正态分布随机数

select dbms_random.string('x',3) from dual; --随机字符串

select dbms_random.string('A',20) from dual;--string函数指定长度为20的随机文本字符串

select to_date(2454084+trunc(dbms_random.value(0,365)),'J') from dual; --随机日期

select to_char(sysdate,'J') from dual; --指定日期基数

select sys_guid() from dual;--生成GUID

select to_char(to_date('01/01/03','mm/dd/yy'),'J') from dual;--2452641

select to_date(trunc(dbms_random.value(2452641,2452641+364)),'J') from dual;--2003年内的任意日期