oracle单行函数详解

oracle单行函数详解

一  介绍

1．函数分为单行函数和多行函数

2．单行函数分为：字符、数值、日期、转换、通用的函数

3．单行函数:

l  操作数据对象

l  接受参数返回一个结果

l  只对一行进行变换

l  每行返回一个结果

l  可以转换数据类型

l  可以嵌套

l  参数可以是一列或一个值

二  详解：

1.  字符函数分为

（1）  大小写控制函数

小写转换 : SQL> select lower('LAOWANG')from dual;

SQL> select * from emp wherelower(ename)='smith';

大写转换: SQL> select upper('laowng')from dual;

只转换第一个字符为大写：

SQL> select initcap('laoWANG')from dual;

（2）  字符控制函数

连接字符串：SQL> selectconcat('hi','laowang') from dual;

截取字符串：SQL> selectsubstr('laowang',0,1) from dual; 结果 l

SQL>select substr('laowang',0,2) from dual; 结果 la

SQL>select substr('laowang',1,2) from dual;结果la

可以看出截取字符串的时候是从索引为1开始的

    字符串的长度：SQL> select length('laowang') from dual;  结果为6

    获取指定字符的索引：SQL> select instr('laowang','a') fromdual;

                      结果为2

    从左边截取10个字符：SQL> select lpad('laowang',10)from dual;

                         结果 laowang

          没有的用*代替：SQL> select lpad('laoang',20,'*')from dual;

                         结果：**************laowang

    从右边截取：SQL> select rpad('laoang',20,'*') fromdual;

    去空格：SQL> select trim('laowang   ') from dual;

               注：取出的是’’中字符前后的字符，去不掉两个字符之间的的空格

    查询某字符中包含某字符：

           SQL>select * from emp where instr(ename,'R')!=0;或

           SQL>select * from emp where instr(ename,'R') <>0;

           SQL>select * from emp where instr(ename,'R')=4;

R在索引为4的ename

2．数值函数

截取.后两位四舍五入：SQL> select round(234324.354,2)from dual; 

结果为234324.35

         截取.后两位，不截取：SQL> select mod(1700,200) fromdual;

                             结果为：100

                            SQL> selectmod(1701.5,200) from dual;

                            结果为：101.5

3．日期函数

日期的差值：

SQL> select to_date('08-8月-08')-to_date('13-3月-12') from dual;  结果为：-1313天数

SQL> select (to_date('08-8月-08')-to_date('13-3月-12'))/365 from dual;   结果：-3.5972603

两个日期相差的月数：

SQL> selectmonths_between(sysdate,hiredate) from emp;    

           向指定日期中加入若干月数：

              SQL>select add_months(sysdate,3) from dual;

           指定日期的下一个日期：

SQL> select next_day(sysdate,'星期一') from dual;

本月的最后一天：SQL> select last_day(sysdate)from dual;

           日期四舍五入：SQL> selectround(sysdate,'yyyy') from dual;

                         SQL> select round

(to_date('1998-10-08','yyyy-mm-dd'),'yyyy')from dual;

           日期截断：SQL> selecttrunc(sysdate,'YEAR') from dual;

4．转换函数

数据类型转换分为隐形的和显性的，我们对显型转换来介绍

转换函数对日期的转换：

首先显示出当前日期：SQL> select  to_char(sysdate) from dual;

SQL> select  to_char(sysdate,'YYYY-MM-DD') from dual;

结果为：2012-03-13

           SQL> select  to_char(sysdate,'YYYY-MM-DD-day') from dual;

                  结果为：2012-03-13-星期二 

           SQL> select  to_char(sysdate,'YYYY/MM/DD-day') from dual;

                  结果为：2012/03/13-星期二 

SQL> select  to_char(sysdate,'YYYY"年"MM"月"DD"日"-day') from dual;        结果为：2012年03月13日-星期二

 

SQL> select  to_char(sysdate,'year"年"mon-DD-day') from dual;  结果为：twenty twelve年3月 -13-星期二    

 

SQL> select  to_char(sysdate,'yyyy-mm-dd HH24:MI:SS AM')

from dual;  结果为：2012-03-13 10:30:39 上午

 

SQL> select  to_char(sysdate,'DD "of" MONTH')from dual;

              结果：13 of 3月

 

转换函数对数字的转换:

           SQL> selectto_char(sal,'$99,999.00') from emp;

                     结果为： $800.00   美元符

           SQL> selectto_char(sal,'L99,999.00') from emp;

                     结果为：￥800.00   本地的

 

           字符串转换为数字：SQL> select to_number('999.00')from dual;

                     结果为：999

6．通用函数

通用函数函数适用于任何数据类型，同时也适用于空值：

（1）将空值转换为一个已知的值：

           可以使用的数据类型有日期、字符、数字。

           为空的替换为0  SQL>select nvl(comm,0) from emp;

           为空的替换为指定日期：

SQL> select nvl(hiredate,’01-2月-01’) from emp;

           （2）．相当于是一个三目运算符

SQL> select nvl2(comm,1,0) fromemp;

解释：当comm为空的用0替换，不为空的用1替换

SQL> select nvl2(comm,comm,0)from emp;

         解释：当comm为空的用0替换，不为空你替换为原有的comm

（3）．nullif(expr1, expr2) :  相等返回NULL，不等返回expr1

     SQL>selectlength(ename) "expr1",

length(job) "expr2",

nullif(length(ename),length(job))

from emp; 

结果为：5         5                                                          

        5         8           5    

          （4）．使用 coalesce 函数      

coalesce与 nvl 相比的优点在于 coalesce可以同时处理交替的多个值。

如果第一个表达式为空,则返回下一个表达式，对其他的参数进行coalesce。

SQL> selectcoalesce(commission_pct,salary,10)  fromemployees;

解释：在执行此函数的时候如果commission_pct没有值，显示的是salary，一旦commission_pct有值了，显示的commission_pct的值。

 

 

2. 条件表达式

在 SQL 语句中使用if-then-else逻辑

例1．SQL> selectempno,ename,hiredate,

 2  case

 3  job when 'CLERK' then 1.2*sal

 4  when 'MANAGER' then 2*sal

 5  else sal

 6  end

 7  "涨工资了"

 8  from emp;

结果为：

EMPNO ENAME      HIREDATE         涨工资了                                     

----- ---------- ------------------------                                     

 7369SMITH      17-12月-80            960                                     

 7499ALLEN      20-2月 -81           1600

 

 

例2：

SQL> select empno,ename,hiredate,

 2  decode

 3  (job,'CLERK',1.2*sal,

 4  'MANAGER',2*sal,

 5  sal) "工资又涨了"

 6  from emp;

 

结果为：

EMPNO ENAME      HIREDATE       工资又涨了                                      

----- ---------- ------------------------                                     

 7369SMITH      17-12月-80            960                                     

 7499ALLEN      20-2月 -81           1600                                     

 7521WARD       22-2月 -81           1250