oracle中一些基本函数的使用

---数字计算函数

1.round函数(四舍五入）

描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果

参数:

number : 欲处理之数值

decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 )

select round(123.456, 0) from dual； 返回123
select round(123.456, 1) from dual; 返回123.5
select round(-123.456, 2) from dual; 返回-123.46

2.ceil和floor函数

ceil和floor函数在一些业务数据的时候，有时还是很有用的。

ceil(n) 取大于等于数值n的最小整数；

floor(n)取小于等于数值n的最大整数

select ceil(1.5) a from dual; 返回2
select ceil(-1.5) a from dual; 返回-1
select floor(1.5) a from dual; 返回1
select floor(-1.5) a from dual; 返回-2

 3.trunc函数

　　1）trunc函数处理数字

            格式：trunc（number[,decimals]）

            解析：number 待做截取处理的数值

　　　　　　decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。

　　　　　　trunc就是处理数字的显示位数，如果decimals为负数，就处理整数部分，处理完为0，-1就是个位为零，-2就到了十位，如果超过了 整数部分长度，则整个数字0；

　2）处理日期

       格式：trunc（date,[fmt]）　　　

       解析： trunc函数返回以指定元元素格式截去一部分的日期值，date为必要参数，是输入的一个日期值，

　　　　fmt参数可忽略，是日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去

使用例子如下：

trunc(sysdate,'yyyy') --返回当年第一天.
trunc(sysdate,'mm') --返回当月第一天.
trunc(sysdate,'d') --返回当前星期的第一天.
select trunc(sysdate,'YYYY') from dual;
select trunc(sysdate,'MM') from dual;
select trunc(sysdate,'D') from dual;

 4.add_months()函数

格式：add_months(x,y)或者add_months(times,months)，第二个参数单位是月份。

解析：这个函数用于计算在时间x之上加上Y个月后的时间值，要是Y的值为负数的话就是在
           这个时间点之前的时间值（这个时间-Y个月）。

用法实例：

 A：--从emp表查询列出来公司就职时间超过24年的员工名单
    select ename, hiredate from emp
   where hiredate <= add_months(sysdate, -288);
--负数代表系统时间（sysdate）之前的24年的时间-288 = -24*12
 B：--查询出在员工'SCOTT'入职一年后入职的员工的信息
    select ename, a.hiredate, sal from emp a, 
        (select hiredate from emp where ename = 'SCOTT') b
      where a.hiredate > = add_months(b.hiredate, 12);
 解析：上面的查询思路如下
      a:在from语句的后面将员工'SCOTT'的入职时间查出放在一个表里，表的别名为b
      b:将上面的b表再次作为查询表
      c：然后利用add_months（x,y）函数在b表的hiredate之后的12个月作为查询条件
 C：--查询半年前的时间
      select add_months(sysdate,-6) from dual;
        解析:dual是oracle提供的最小功能表，为了凑齐sql语法，它只有一行一列。

--字符串处理函数

1、instr()函数的格式  （俗称：字符串查找函数）

格式一：instr( string1, string2 )    /   instr(源字符串, 目标字符串)

格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：在string1中查找string2 ，从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2在string1中起始位置。

  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

实例：

--默认第一次出现“l”的位置
select instr('helloworld','l') from dual; --返回结果：3   
--即：在“lo”中，“l”开始出现的位置 
select instr('helloworld','lo') from dual; --返回结果：4  
--即“w”开始出现的位置
select instr('helloworld','wo') from dual; --返回结果：6  

   
--在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',2,2) from dual;  --返回结果：4 
--在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',3,2) from dual;  --返回结果：4    
--在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual;  --返回结果：9  
--在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual;  --返回结果：9
--在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置 
select instr('helloworld','l',-2,2) from dual;  --返回结果：4 
--在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置   
select instr('helloworld','l',2,3) from dual;  --返回结果：9  
--在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual; --返回结果：3   
  

注：模糊查询like 和 instr() 函数以下用法有相同查询效果，如下所示：

MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0;  

这两条语句的效果是一样的

2、substr(string,a,b)/substr(string,a)

string 为字符串，string 表示需要截取的字符串。

a、b 均为整型数字，a 表示开始截取的位置，b 表示截取几位，b 为空时冲开始位置截取全部字符串。

3、 replace(string1,tring2,sring3)

sring 为字符串；

string1 表示即将要修改和查找的字段。string2 表示要查找的字段，即被替换的字段；string3 表示要替换的新字段。

4 、decode(string, tring1, value1, value2)

string 表示原字符串或者表达式，string1 表示需要满足的条件，可以是值，也可以是表达式。

如果满足条件，则返回 value1 ，如果不满足条件，则返回 value2 。

decode(string, tring1, value1, string2, value2……)

string 表示原字符串，如果字符串为 string 等于 string1，则返回 value1 ，如果字符串为 string 等于 string2，则返回 value2。

 

还有to_char(),to_date(),to_number(),sum()等较为简单的函数就不介绍了，以后遇到相应的函数都会补充上来。