Oracle学习笔记day06——Oracle函数

Oracle函数

一、字符串函数

字符串函数是oracle中比较常用的，下面我们就介绍些常用的字符串函数:

1. concat：字符串连接函数，也可以使用’||’

   --将职位和雇员名称显示在一列中
   Select concat ('Hello',' world') from dual;--->Hello world
   select ename || '(' || job || ')' from emp;

   
   
   
2. length：返回字符串的长度

   --查询雇员名字长度为5个字符的信息
   select * from emp where length(ename)=5;

   
   
   
3. lower：将字符串转换成小写

   --以小写方式显示雇员名
   select lower(ename) from emp;

   
   
   
4. upper：将字符串转换成大写

   --以大写方式显示雇员名
   select upper (ename) from emp;

   
   
   
5. substr：截取字符串

   --只显示雇员名的前3个字母
   select substr(ename,0,3) from emp;

   
   
   
6. replace：替换字符串

   --将雇员的金额显示为*号
   select ename,replace(sal,sal,’*’) from emp;

   
   
   
7. instr：查找字符串

   --查找雇员名含有’LA’字符的信息
   select * from emp where instr(ename,’LA’)>0
   select instr('abcabc','c',-1) from dual;-->6【负数从后开始】

   
   

二、日期函数

1. sysdate：返回当前session所在时区的默认时间

   --获取当前系统时间
   select  sysdate  from dual;

   
   
   
2. add_months：返回指定日期月份+n之后的值，n可以为任何整数

   --查询当前系统月份+2的时间
   select  add_months(sysdate,2)  from dual;
   --查询当前系统月份-2的时间
   select  add_months(sysdate,-2)  from dual;

   
   
   
3. last_day：返回指定时间所在月的最后一天

   --获取当前系统月份的最后一天
   select  last_day(sysdate)  from dual;

   
   
   
4. months_between：返回月份差，结果可正可负，当然也有可能为0

   --获取入职日期距离当前时间多少天
   select  months_between(sysdate, hiredate) from emp;

   
   
   
5. trunc：为指定元素而截去的日期值

   --获取当前系统年第一天，其他默认
   select  trunc(sysdate,'yy')  from dual;
   --查询81年2月份入职的雇员
   select * from emp 
   where trunc(hiredate,'mm')=trunc(to_date('1981-02','yyyy-mm'),'mm');

   
   
   

三、转换函数

1. to_char：将任意类型转换成字符串

   --日期转换
   select  to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')  from dual;
   --数字转换
   select  to_char(-100.789999999999,'L99G999D999')  from dual;

2. to_date：将字符串转换成日期对象

   --字符转换成日期
   select to_date('2011-11-11 11:11:11', 'yyyy-mm-dd hh24:mi:ss') from dual;

3. to_number：将字符转换成数字对象

   --字符转换成数字对象
   select to_number('209.976')*5 from dual 
   select to_number('209.976', '9G999D999')*5 from dual;

   
   
4. 数字格式控制符
   

   符号	描述
   9	代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示)
   0	强制显示该位，如果当前位有数字，显示数字，否则显示0
   $	增加美元符号显示
   L	增加本地货币符号显示
   .	小数点符号显示
   ,	千分位符号显示

   
   
   
   

四、数学函数

1. abs：返回数字的绝对值

   select  abs(-1999) from dual;

2. ceil：返回大于或等于n的最小的整数值

   select  ceil(2.48) from dual;

3. floor：返回小于等于n的最大整数值

   select  floor(2.48) from dual;

4. round：四舍五入

   select  round(2.48) from dual;
   select  round(2.485,2) from dual;

5. bin_to_num：二进制转换成十进制

   select  bin_to_num(1,0,0,1,0) from dual;

   
   

五、其他函数

1. NVL(内容,set)【类似SQLServer中使用的null()函数,代表如果内容为空，为制定的值set】

   select ename,nvl(comm,0) from emp;

2. NVL2(内容,set1,set2)【如果内容不为空，设置值为set1,为空为set2】

   select ename,nvl2(comm,comm+200,200) from emp;

3. NULLIF(a,b)【如果a,b的值相等，返回null，如果不相等，返回a】

   select nullif(10,10) from dual;-->空，神马都没有
   select nullif(10,11) from dual;-->返回10

4. row_number()【为有序组中的每一行（划分组的行或查询行）返回一个唯一的排序值】

   --查询用户名,工资待遇，并根据待遇排出名次
   select ename,sal,row_number() over(order by sal desc) 名次 from emp;
   --查询用户名，待遇，并为每个部门根据工资待遇给出名次
   select ename,sal,row_number() over(partition by deptno order by sal desc) 名次 from emp;

5. rank()【排名中如果出现相同的，名次相同，后面的名次跳过相应次数】

   select ename,sal,rank() over(order by sal desc) 名次 from emp;

6. dense_rank()【排名中如果出现相同的，名次相同，后面的名次不跳过相应次数】

   select ename,sal,dense_rank() over(order by sal desc) 名次 from emp;