函数

一、什么是函数
1、函数与命令的区别。select是命令，命令不返回结果，只完成某种操作，函数都返回结果。
2、函数一般有如下形式：函数名（参数1，参数2，……）
 参数是你想传递给函数的数据。如Power（2,3）。这个函数的作用是计算2的3次方。2和3就是参数。参数会被传给函数，函数根据传过来的参数进行计算，并返回计算结果。上例中结果是8。我们可以如下显示它的结果：

 > select power(2,3) from dual;

POWER(2,3)
----------
         8

在上面的例子中，select是一条命令，而POWER是一个函数。我们可以从中看到命令与函数的区别。函数比较简单，形式就是函数名（参数1，参数2，……）。而命令比较复杂，有各种各样的参数、选项。就像select，它后面可以有条件、ORDER BY等等。命令为我们完成各种操作。就像SELECT，它的功能是输入指定的数据。而函数，通常只是加工数据。我们传递给某个函数一些数据，它把这些数据计算、处理或加工后，把结果返回。这是命令和函数的区别。

 

二、函数的类型
1、单行函数与多行函数：
 了解单行函数与多行函数的区别
 ORACLE中函数总的类型，可以分为单行函数与多和函数。像刚才我们演示的Power（）就是一个单行函数。单行函数只对一行数据起作用，即使你将它应用到表中，它也是针对每行数据，分别进行计算。多行函数，我们找一个简单的例子：avg(列名)。它是求平均值函数，比如：

 > select avg(salary) from t1;

AVG(SALARY)
-----------
 20066.6667

单行函数只对一行数据起作用，即使你将它应用到表中，它也是针对每行数据，分别进行计算。而不是将所有行数据，放在一起进行计算。注意，这是单行函数与多行函数的区别。在函数的参数是表的某一列时，多行函数把列中所有行的数据看作一个整体，对所有行一起操作。比如将所有行的值累加起来，或者累加起来再除于总行数（求平均函数），等等。多行函数的返回结果，无论表有多少行，最终结果是一个。而单行函数的参数是表的某一列时，列中每一个数据，都被认为是一个个体，单行函数对这个个体进行计算，返回结果。再对表中下一行的数据进行计算，返回结果，等等。也就是说，表有几行，单行函数的返回结果就有几个。
本节课，主要以单行函数为主进行介绍。

2、以数据类型划分的函数类型
了解函数类型
如果以函数所处理的数据类型，或返回的结果的类型，我们还可以把函数分为字符型函数、数字型函数、日期型函数、转换型函数和通用型函数。
这5种类型的函数，是我们下面要讲的重点。
下面，在进行下一部分内容前，我们再来看看函数在使用时的一些特殊形式。

3、函数的使用形式
了解函数嵌套使用、自动转换数据类型时的一些问题，还有函数的使用位置。
（1）函数的嵌套，如下：

> select sqrt(power(2,8)) from dual;

SQRT(POWER(2,8))
----------------
              16

多行函数与单行函数也可互相嵌套，如下：

 >  select sum(power(salary,2)) from t1;

SUM(POWER(SALARY,2))
--------------------
          7133000000

> col salary for 999999999999999999.99
> select power(sum(salary),2) salary from t1;

                SALARY
----------------------
        90601000000.00

嵌套的层数，根据需要可以是多层。无论有几层，ORACLE都是从最里面一层开始计算，用里层函数的返回值，作为外层数的参数。

（2）数据类型的自动转换
如果在使用函数时，如果参数的类型有错误，如果可以的话，ORACLE会尽量将参数转为正确的类型，再开始计算。比如：

> select power('2','4') from dual;

POWER('2','4')
--------------
            16

> select power('a','b') from dual;
select power('a','b') from dual
             *
ERROR at line 1:
ORA-01722: invalid number

这个转换，将会发生错误，因为，’a’根据就转不成数字。
因此，数据类型的自动转换，是如果可以的话，ORACLE会尽量将参数转为正确的类型。ORACLE不保证所有转换都可以成功。

（3）函数的使用位置
上面我们所举的例子中，都是在SQL语句select后使用函数，其实在SQL语句中的各个地方，都可以使用函数。比如：

> select * from t1 where upper(name)='JOE';

        ID NAME                       SALARY
---------- ---------- ----------------------
         9 Joe                      22000.00

在条件中使用函数，将NAME列转为大写字母，再和‘JOE’比较。

> select * from t1 order by power(salary,2);

        ID NAME                       SALARY
---------- ---------- ----------------------
         2 gyj2                      8000.00
         3 gyj3                     10000.00
         5 gyj5                     12000.00
         7 gyj7                     12000.00

在排序中使用函数，按SALARY列平方的大小排序。在这里，ORACLE先计算出SALARY列的平方，再根据这个平方值排序。
无论函数被使用在哪儿，函数的执行，优先于命令。ORACLE先计算出函数的结果，再根据结果执行命令。

三、字符函数
1、大小写处理
讲述三个大小写处理函数，以及单引号的使用规则
ORACLE中有如下三个大小写函数：
LOWER(column|expression) ： 转换字符值为小写
UPPER(column|expression) ： 转换字符值为小写
INITCAP(column|expression) ：转换每个单词的首字母值为大写，所有其它值为小写
括号中的(column|expression)，意思是你即可以以列名为参数，也可以以表达式为参数（expression就是表达式的意思）
如下操作：
（1）LOWER()的操作：

> select lower('Joe') from dual;

LOW
---
joe

结果是无论大写还是小写，都被转换为小写。以列名为参数

（2）UPPER()的操作：

>  select upper(name) from t1;

UPPER(NAME
----------
GYJ2
GYJ3
GYJ4

这次我使用UPPER（），所有的列都被转换为大写。当以列名为参数时，表中每一行都被分别转换、输出。

>  select upper('name') from t1;

UPPE
----
NAME
NAME
NAME

如果将upper(name)中的NAME外加上引号了，那么NAME将不再表示一个列的名字，它不再是列名，而仅仅是N、A、M、E这四个字符

（3）INITCAP()的操作

> select INITCAP('joe') from dual;

INI
---
Joe

joe单词的首字母值为大写，所有其它值为小写

2、字符处理函数
了解常用的字符处理函数
（1）SUBSTR(column|expression,m [,n])：字符串截取函数。从第M个字符处开始，截取N个字符。如果N省略，则从M开始截取到结尾。

> select substr('浙江省杭州市',4,3) from dual;

SUBSTR('浙江省杭州市',4,3)
--------------------------
杭州市 

从第4个字符开始，截取3个字符。

（2）LENGTH(column|expression) ：返回字符串的长度。

> select name,length(name) from t1;

NAME       LENGTH(NAME)
---------- ------------
gyj2                  4
gyj3                  4
Joe                   3

这个命令返回的是字符数，lengthb将返回字节数。

> select lengthb('浙江省杭州市') from dual;

LENGTHB('浙江省杭州市')
-----------------------
                     12

一个汉字2个字节，6个汉字总共12个字节。

（3）INSTR(column|expression,‘string’, [,m], [n] ) ：字符串的查找。在column|expression （也就是列或表达式中），查找String。返回Strgin第一次出现的位置。

> select instr('浙江省杭州市','杭州') from dual;

INSTR('浙江省杭州市','杭州')
----------------------------
                           4 

杭州是在第4个字符处。
M是起始的查找位置，默认是从第一字符处开始。N是查找第几个符合要求的字符，默认是查找第一个符合要求的字符。

> select instr('浙江省杭州市的杭州市民','杭州',1,2) from dual;

INSTR('浙江省杭州市的杭州市民'
------------------------------
                             8

从第1个字符处，查找第2个杭州。

> select instr('浙江省杭州市的杭州市民','杭州',1,3) from dual;

INSTR('浙江省杭州市的杭州市民'
------------------------------
                             0

仍从第1个字符处，查找第3个杭州，结果为0。也就是找不到。

（5）LPAD(column|expression, n,'string')
（6）RPAD(column|expression, n,'string')
这两个分别是左、右填充函数。在列于表达式的左、右端填充若干个String，使字符串的总长度为N。

>  select lpad(name,10,'.') from t1;

LPAD(NAME,10,'.')
--------------------
......gyj1
......gyj8
.......Joe
.......Tom

在左端填充“.”号，填充后，每行字符的长度，都是10个。在Joe前填充7个”.”，在gyj1前，填充6个“.”。它们的总长度在填充后一致都是10。

> select rpad(name,10,'.') from t1;

RPAD(NAME,10,'.')
--------------------
gyj1......
gyj8......
Joe.......
Tom.......

它将字符填充在右端。

（7）TRIM(leading|trailing|both trim_character FROM trim_source) ：从trim_source的头（leading）、尾（trailing）或头尾（both），删除和trim_character一样的字符。

> select trim(leading 'g' from 'guoyJoe') from dual;

TRIM(L
------
uoyJoe

从‘guoyJoe’的头部，截掉字符‘g’。再看下面的例子：

> select trim(leading 'g' from 'gggggggguoyJoe')||'12' from dual;

TRIM(LEA
--------
uoyJoe12

向上面这样，如果‘g’有多个，头部的‘g’将都被截掉。
这个函数还有一种简单的使用方法，是去除两端的空格：

> select 'guo'||' y '||'Joe' from dual;

'GUO'||'Y
---------
guo y Joe 

我们可以看到，在y的前后，都有一些空格。

> select 'guo'||trim(' y ')||'Joe' from dual;

'GUO'||
-------
guoyJoe

这次TRIM的使用，除了要处理的字符串，没有任何参数。使用过trime后，空格都被截掉了。这种简单的形式，只能用来截除两端的空格。

（8）REPLACE(text, search_string, replacement_string)：将text中的search_string替换为replacement_string 。

> select replace('guoyJoe','Joe','jun') from dual;

REPLACE
-------
guoyjun

将guoyJoe中的所有Joe换为了jun。replacement_string定为NULL，或'’（紧挨着的两个引号，中间没有空格），这将达到在源册中删除所有的search_string 串的目的。

> select replace('guoyJoe','y',null) from dual;

REPLAC
------
guoJoe

所有的两处y都没有了。

>  select replace('guoyJoe','y','') from dual;

REPLAC
------
guoJoe 

这个命令的效果和上面是一样的。都是把y置换成空

> >  select replace(' guo y Joe ',' ','') from dual;

REPLACE
-------
guoyJoe

去除所有的空格

 

四、数字函数
我们主要介绍三个：
1、ROUND(column|expression, n) ：对列或表达式的值，以四舍五入的方式保留n 位小数位。如果n 被忽略，无小数位，相当于n为0时的情况。也就是n的默认值为0。

2、TRUNC(column|expression,n) ：截断列或表达式的值，保留n 位小数，在截断时不四舍五入。如果n 被忽略，那么n 默认为0。截断的意义就是不四舍五入，直接舍去。

3、MOD(m,n) ：求余函数。返回m 除以n 的余数

下面我们分别试验一下：

> select ROUND(45.926, 2) from dual;

ROUND(45.926,2)
---------------
          45.93

意义非常明确，45.926保留2位小数，四舍五入，结果为45.93。下面我们再试保留1位，0位，和－1位时的结果：
ROUND(45.926, 1) 结果为：45.9
ROUND(45.926, 0) 结果为：46
ROUND(45.926, -1) 结果为：50
当小数位数为－1时，开始从个位四舍五入。以次类推，小数位数为－2时，从十位四舍五入。

Trunc和Round基本上一样，只是Trunc是只保留N位小数，并不四舍五入：

> select TRUNC(45.926, 2) from dual;

TRUNC(45.926,2)
---------------
          45.92

MOD()是一个比较简单函数，求余，比如求1600除以300的余数，就是Mod(1600,300)。

> select MOD(1600, 300) from dual;

MOD(1600,300)
-------------
          100

 

五、日期函数
1、日期的存贮格式
每一个日期，在ORACLE内部都由如下七部分构成：世纪、年、月、日、小时、分钟、秒。使用如下命令，可以设置本地的日期显示格式：

> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

在ORACLE中，有一个SYSDATE函数，可以返回系统当前的日期：

> select sysdate from dual;

SYSDATE
-------------------
2013-02-21 12:15:46

在创建表时，日期型用DATE表示，我们为T1表，增加一个日期型列:HIRE_DATE

>  alter table t1 add(hire_date date);

Table altered.

> update t1 set hire_date=sysdate;

6 rows updated.

> select * from t1;

        ID NAME                       SALARY HIRE_DATE
---------- ---------- ---------------------- -------------------
         2 gyj2                      8000.00 2013-02-21 12:19:43
         3 gyj3                     10000.00 2013-02-21 12:19:43
         4 gyj4                     15000.00 2013-02-21 12:19:43
         5 gyj5                     12000.00 2013-02-21 12:19:43
         7 gyj7                     12000.00 2013-02-21 12:19:43
         6 gyj6                     12000.00 2013-02-21 12:19:43

> alter session set nls_date_format='yy-mm-dd';

Session altered.

> select * from t1;

        ID NAME                       SALARY HIRE_DAT
---------- ---------- ---------------------- --------
         2 gyj2                      8000.00 13-02-21
         3 gyj3                     10000.00 13-02-21
         4 gyj4                     15000.00 13-02-21
         5 gyj5                     12000.00 13-02-21
         7 gyj7                     12000.00 13-02-21
         6 gyj6                     12000.00 13-02-21

如果在输入日期时，没有指定这七部分中的某些可以省略的部分，通常这些部分将按0值处理。注意，有些部分可以省略，有些部分不能省略。下面我们试一下：

>  insert into t1 values(20,'guo',9000,'2013-02-18');

1 row created.

> commit;

Commit complete.

> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

> select * from t1 where id=20;

        ID NAME                       SALARY HIRE_DATE
---------- ---------- ---------------------- -------------------
        20 guo                       9000.00 2013-02-18 00:00:00

还要注意我输入日期的方式，'2013-02-18'，用单引号括起来。我们前面讲过，用单引号括起来的，都是字符串，怎么这里又变成日期了呢？用单引号括起来的都是字符串，这没错，ORACLE在这里进行了自动类型专换，将字符串按照我们定义的NLS_DATE_FORMAT参数的值，转换成日期。在转换的时候，发现你省略了什么部分，就为你补上去0。

>  insert into t1 values(21,'guo',9000,2013-02-18);
 insert into t1 values(21,'guo',9000,2013-02-18)
                                            *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

没加单引号，orace是不认的，以为是 2013减去2再减去18

我们再来插一条记录，时间2013年去掉纪世，13-02-18

> insert into t1 values(21,'guo',9000,'13-02-18');

1 row created.

> commit;

Commit complete.

> select * from t1 where id=20 or id=21;

        ID NAME                       SALARY HIRE_DATE
---------- ---------- ---------------------- -------------------
        20 guo                       9000.00 2013-02-18 00:00:00
        21 guo                       9000.00 0013-02-18 00:00:00

变成了0013年了！！！！！！！！！！！！！

如果我将日期显示格式中的年定为两位：

> alter session set nls_date_format='yy-mm-dd hh24:mi:ss';

Session altered.

再用13年为日期插入：

> insert into t1 values(22,'guo',8888,'13-02-18');

1 row created.

> commit;

Commit complete.

> select * from t1 where id=22;

        ID NAME                       SALARY HIRE_DATE
---------- ---------- ---------------------- -----------------
        22 guo                       8888.00 13-02-18 00:00:00

这个时候的结果，是日期正确的

建议你在输入日期时，要注意和你设定的日期显示格式像吻合。如果日期格式是yyyy-mm-dd，年是4位，你就不要输入两位的年份。

2、日期的计算：
（1）日期和日期，只能做减法运算，结果是两个日期间的天数

> select '2014-01-30'-'2013-02-21' from dual;
select '2014-01-30'-'2013-02-21' from dual
       *
ERROR at line 1:
ORA-01722: invalid number

我们上面说过了，'2014-01-30'是字符，在插入时，ORACLE会自动转换为日期。但在这里，ORACLE不想再为我们作转换操作了，我们要自己动手了。其实这里也不是Oracle不想为我们转换了，而是对于减号运算符来说，只将减号两端的字符转为数字：

> select '30'-'10' from dual;

 '30'-'10'
----------
        20

'2014-01-30'这样的字符，无法正常的转为数字，因此，报“无效数字”的错误。
在运算符中，Oracle一般都会将字符串尝试转换为数字，而不是日期。这时就需要我们手动进行转换。其实转换命令也非常的简单，TO_DATE（‘’）。TO_DATE，就是到日期的意思。准确的说，这是一个函数，不是命令。
这个函数有两种用法，一是省略日期格式：to_date('2014-01-30')，这样，Oracle将按照你所设定的NLS_DATE_FORMAT中的格式，进行转换。或者，你可以在此函数中，自定格式：to_date('2014-01-30'，’yyyy-mm-dd’)。
下面，用它修改我们上面的命令：

> select to_date('2014-01-30')-to_date('2013-02-21') from dual;

TO_DATE('2014-01-30')-TO_DATE('2013-02-21')
-------------------------------------------
                                        343 

以2013年21日为准算出了离2104年过年还有343天！

（2）日期和数字
（1）、日期加减整数：

> select to_date('2013-02-21 10:00:00')+10 from dual;

TO_DATE('2013-02-21
-------------------
2013-03-03 10:00:00

日期加减整数，结果还是一个日期。整数是针对日的，就像上例，加10后，由21号变为了3号。

（2）、日期加减小数
 整数是日，1就是1天。如果1/24，也就是.041666667，就是一个小时了。

> select to_date('2013-02-21 10:00:00')+.041666667 from dual;

TO_DATE('2013-02-21
-------------------
2013-02-21 11:00:00

本来时间是10点，现在变成了11点了。当然，使用1/24也可以：

> select to_date('2013-02-21 10:00:00')+1/24 from dual;

TO_DATE('2013-02-21
-------------------
2013-02-21 11:00:00

使用1/24更直接明了，1天的24分之一，哪不就是一小时吗。也可以加上半小时：

> select to_date('2013-02-21 10:00:00')+0.5/24 from dual;

TO_DATE('2013-02-21
-------------------
2013-02-21 10:30:00

1/24是一小时，0.5/24就是半小时。

3、日期函数
（1）MONTHS_BETWEEN(date1, date2)：计算date1 和date2 之间的月数，

> select months_between('2013-02-08','2013-03-08') from dual;

MONTHS_BETWEEN('2013-02-08','2013-03-08')
-----------------------------------------
                                       -1

结果为－1，也就是说，2013-02-08','2013-03-08'了整好一个月。

我将第二个日期换一下，换成3月1日：

> select months_between('2013-02-08','2013-03-01') from dual;

MONTHS_BETWEEN('2013-02-08','2013-03-01')
-----------------------------------------
                               -.77419355

2013-02-08比2013-03-01，少了0.77个月。
再额外说明一点，日期型函数，因为只是针对日期进行操作，因此像'2013-02-08'这样的字符串，会被转换为日期，而不是转换为数据。在日期型函数内，我们不必在使用TO_DATE。

（3）ADD_MONTHS(date, n)：添加n 个日历月到date。n 的值必须是整数，但可以是负的。
测试很简单，2013年2月21日后12个月是几号：

> select add_months('2013-02-21',12) from dual;

ADD_MONTHS('2013-02
-------------------
2014-02-21 00:00:00

如果N为－12，则是2013年2月21日前12个月是几号，就是2012年2月21日了。

（3）NEXT_DAY(date, N)：计算在date 之后的下一周指定星期几的日期。

> > select NEXT_DAY(sysdate,1) from dual;

NEXT_DAY(SYSDATE,1)
-------------------
2013-02-24 13:31:25

函数的结果是以当天为准，下个星期第一天（即星期天）是几号。

（4）LAST_DAY(date)：计算包含date 的月的最后一天的日期。

> select last_DAY(sysdate) from dual;

LAST_DAY(SYSDATE)
-------------------
2013-02-28 13:32:37

本月的最后一天是2月28号。

（5）ROUND(date [,‘fmt’])：从年、月、日开始对日期进行四舍五入，可选项FMT有三种取值，分别是YEAR：从年开始四舍五入，MONTH：从月，DAY从日。如果省略FMT选项，将从小时开始四舍五入。下面我们分别做个测试。
  注意ROUND也可以针对数字型数据，Oracle优化把字符串转换为数字，因此，'2013-02-21'这样的字符串，在ROUND中又要加TO_DATE了。下面我们试验这个函数。
①、以年为准，四舍五入月。
当FMT的值定为YEAR时，就是以年为为准四舍五入。舍入的对象，主要是月。也就是这时主要看月，月分如果是在上半年，就“四舍”，直接将月舍去。如果是在下半年，就按“五入”，年加1后月被舍去。
上半年下半年的分界，就是6月30日。6月30日之前，也就是上半年。7月1日后，也就是下半年。看一下日期四舍五入的结果：

> select round(to_date('2013-06-30'),'year') from dual;

ROUND(TO_DATE('2013
-------------------
2013-01-01 00:00:00

现在日期是2013-06-30，月被舍去了，结果是2013-01-01。现在换为2013-07-01试试：

> select round(to_date('2013-07-01'),'year') from dual;

ROUND(TO_DATE('2013
-------------------
2014-01-01 00:00:00

2013-07-01时，往前进位了，日期变为了2014-01-01。这就是从年开始四舍五入。
这和数字的四舍五入是差不多的，如果是数字，你在十位四舍五入，看它是大于等于5，还是小于5，根据这个决定是进位并舍去，还是不进位并舍去。

②、以月为准，四舍五入日：
 这将主要看日，看是在前半月、还是后半月。以15日为标准，15日前是前半月，从16后是后半月。下面我们试一下：

> select round(to_date('2013-02-15'),'month') from dual;

ROUND(TO_DATE('2013
-------------------
2013-02-01 00:00:00

2013-02-15，这算是前半月，日被舍去，结果2013-02-01。这次将日期定为后半月：

> select round(to_date('2013-02-16'),'month') from dual;

ROUND(TO_DATE('2013
-------------------
2013-03-01 00:00:00

月份加1，日被舍掉。变为了2013-03-01。

③、以日为准，对星期四舍五入：
 从日开始的四舍五入，主要和星期相关。看日是在一个星期的前半星期，还是后半星期。如果是在前半星期，结果将是星期日的0点0分0秒。因为在西方星期日是一个星期的第一天。如果日期在后半星期，最后结果就是下一星期日的0点0分0秒。而一个星期前半星期与后半星期的分界，是以星期三的12：00：00为准。大于等于这个时间的就是后半星期。否则就是前半星期。
 下面，我们看一下测试：

> select round(to_date('2013-2-20 11:59:59'),'day') from dual;

ROUND(TO_DATE('2013
-------------------
2013-02-17 00:00:00

2013-2-20 11:59:59，是星期三的12点前，属前半个星期。舍入结果是2013-02-17 00:00:00。是星期日的0点0分0秒。下面试一个后半星期的：

> select round(to_date('2013-2-20 12:00:00'),'day') from dual;

ROUND(TO_DATE('2013
-------------------
2013-02-24 00:00:00

结果是下一星期日的0点0分0秒。

④、从小时开始四舍五入：
 当ROUND（）括号中，只有一个日期，而省略了FMT时，就表示要从小时四舍五入，以12:00:00为准，大于等于此时间，属后半天，小于则属前半天。下面我们仍然用试验看效果：

> select round(to_date('2013-2-21 11:59:59')) from dual;

ROUND(TO_DATE('2013
-------------------
2013-02-21 00:00:00

11:59:59，属前半天，直接舍去，日期不变。

> select round(to_date('2013-2-21 12:00:00')) from dual;

ROUND(TO_DATE('2013
-------------------
2013-02-22 00:00:00

2013-2-21 12:00:00属后半天，时间归0，日期加1，变为了2013-02-22 00:00:00

（6）TRUNC(date [,‘fmt’])：从年、月、日开始对日期进行截断。它和四舍五入不同的时，它只舍不入。其实除了它只舍不入外，它和ROUND一样。
如果对'2013-2-21 12:00:00'截断：按小时结果为'2013-2-21 00:00:00'。
按日结果为2013-02-17 00:00:00。按月结果为2013-2-1 00:00:00。按年结果为2013-01-01 00:00:00

 
六、转换函数
1、数据类型的转换
（1）隐式转换
隐式转换就是Oracle自动完成的转换，我们前面已经提到过了。但我们最好不要使用隐式类型转换。因为隐式类型转换藏在下面，由ORACLE内部决定数据最终被转换成什么，这是你无法控制的。有时，你认为ORACLE应该把A类型数据转换为B类型数据，但是，有可能ORACLE转换成了C类型的数据。转换的最终结果，极大的依赖于外部环境。外部环境一旦发生变化，本来好好的应用，可能忽然之间就报错了。我认为，每一处隐式类型转换，其实都是一处安全隐患。
比如，在程序中有一条向表中插入日期的语句：

> Insert into t1 (hire_date) values ('2013-02-21');

1 row created.

> commit;

Commit complete

这条语句一直运行正常。有一天，某个DBA通过设置日期格式参数NLS_FORMAT_DATE，将日期格式改为了月－日－年格式。

> alter session set nls_date_format='mm-dd-yyyy';

Session altered.

本来运行良好的应用程序，忽然之间就报出了错误。

> Insert into t1 (hire_date) values ('2013-02-21');
Insert into t1 (hire_date) values ('2013-02-21')
                                   *
ERROR at line 1:
ORA-01843: not a valid month

这种类型的错误有时是很难解决的，因为可能这条语句藏在某个程序中的某处，或者出现错误的原因，可能是在很久之前埋下的祸根所造成的。我的建议是，向上面的插入日期，写成这样：Insert into t1 (hire_date) values (to_date('2013-02-21','yyyy-mm-dd')); 
将日期格式固定在语句内部，无论外界环境如何变化，不会对这条语句有任何影响。
有时候，数字、字符的转换，也会出现问题。比如我有一个编号列，各种编号，格式可能会比较复杂，有时不能使用简单的数字型。比如，我的编号列是字符型：

 > create table t5(id varchar2(10));

Table created.

> insert into t5 values('1001');

1 row created.

> insert into t5 values('1002');

1 row created.

> insert into t5 values('1003');

1 row created.

> insert into t5 values('01003');

1 row created.

> commit;

Commit complete.

下面我显示ID是'1003’的行：

> select * from t5 where id='1003';

ID
----------
1003

如果把1003外的引号去掉，分发生什么情况呢：

> select * from t5 where id=1003;

ID
----------
1003
01003

连01003都给显示出来了。有时候，这是不希望看到的结果。1003和01003可能分别代表着不同的信息。查找1003不应该把01003也显示出来。这就是“安全隐患”。

(2)、显式转换
 通过调用函数，将某种类型的数据转换为另一种类型。前面课程中，也用到过一个显示转换，就是TO_DATE。
 ORACLE主要提供了三个转换函数，TO_CHAR（）、TO_NUMBER（）、TO_DATE（），下面我们分别介绍一下。

 2、TO_CHAR
  TO_CAHR可以把一个数字或日期，转换为字符串。
 （1）数字转为字符，TO_CHAR（数字，‘格式’）。

 > select name,to_char(salary,'$9,999,999.99') from t1;

NAME       TO_CHAR(SALARY
---------- --------------
gyj2            $8,000.00
gyj3           $10,000.00
gyj4           $15,000.00
gyj5           $12,000.00

> select name,to_char(salary,'$9,999.99') from t1;

NAME       TO_CHAR(SA
---------- ----------
gyj2        $8,000.00
gyj3       ##########
gyj4       ##########
gyj5       ##########

这就是最终的输出结果，$的功能，就是在字符串前加上一个货币符号，就是$ 美元币符号。而一串9，代表了位数。一个9是一位，如果9的个数，少于数据的位数，将显示一串#号：

（2）日期转为字符：
 在转换时，所数字一样，ORACLE提供了大量的格式（也称模板），利用这些格式，可以在转换过程中对日期的输出格式进行任意的控制。此命令的形式为：TO_CHAR（日期，格式）
 简单做几个测试显示一下时间格式：

 > select to_char(sysdate,'yyyy-mon-dd HH24:mi:ss day') from dual;

TO_CHAR(SYSDATE,'YYYY-MON-DDHH24:MI:S
-------------------------------------
2013-feb-21 15:16:36 thursday

> select to_char(sysdate,'HH24:mi:ss mon-yyyy-dd HH24:mi:ss day') from dual;

TO_CHAR(SYSDATE,'HH24:MI:SSMON-YYYY-DDHH24:MI:
----------------------------------------------
15:17:51 feb-2013-21 15:17:51 thursday

> select to_char(sysdate,'yyyy-dd' ) from dual;

TO_CHAR
-------
2013-21

>  select to_char(sysdate,'"今天是本年的第" ww "周,本月的第" w "周"' ) from dual;

TO_CHAR(SYSDATE,'"今天是本年的第"W
----------------------------------
今天是本年的第 08 周,本月的第 3 周

除了8和3是用ww 和w格式，从日期中取得的外，其他信息用“”（双引号）括起来

3、TO_NUMBER  字符转数字。
使用形式：TO_NUMBER（字符）。这个函数非常简单。通常字符到数字的转换，可以由Oracle自动完成，因此，这个函数使用的并不多。

4、TO_DATE  字符转日期
使用形式：TO_DATE(字符，格式)。这个函数我们前面已经用到来了，它是把字符按照指定的格式转为日期。格式和TO_CHAR中的格式是一样的。如果省略格式，将按照NLS_DATE_FORMAT指定的格式转换。

> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

> select to_date('2014-01-30')-to_date('2013-02-21') from dual;

TO_DATE('2014-01-30')-TO_DATE('2013-02-21')
-------------------------------------------
                                        343

5、日期函数的RR格式与YY格式
RR与YY两种格式，主要用于在操作日期时，如果省略了世纪，就是年只有两位时，ORACLE如何添加世纪。
我实验两个简单的例子：

> select to_date('99-02-21','yy-mm-dd') from dual;

TO_DATE('99-02-21',
-------------------
2099-02-21 00:00:00
> select to_date('99-02-21','rr-mm-dd') from dual;

TO_DATE('99-02-21',
-------------------
1999-02-21 00:00:00

年份是99，YY日期添加的世纪是21，而RR的世纪则是20。我们把日期改成13年再试试：

> select to_date('13-02-21','rr-mm-dd') from dual;

TO_DATE('13-02-21',
-------------------
2013-02-21 00:00:00
>  select to_date('13-02-21','yy-mm-dd') from dual;

TO_DATE('13-02-21',
-------------------
2013-02-21 00:00:00

无论是RR还是YY，世纪都是21。
总结一下，首先说YY，它的世纪永远和当前世纪相同。比如： to_date('13-02-21','yy-mm-dd') 的结果，世纪就是21。如果我们计算机内的日期设为上个世纪：
sid=47 pid=14> select to_date('13-02-21','yy-mm-dd') from dual;
TO_DATE('08-05-07',
-------------------
1913-02-21 00:00:00
可以看，这里的世纪也变化了上个世纪。
RR比较灵活，它将世纪以50为分界，分为上半世纪和下半世纪。小于50年的，是上半世纪，也就是0-49年。大于等于50年属下半世纪，也就是50-99年。具体有这样四种情况：
（1）、如果当前日期是上半世纪，你想转换的日期也是上半世纪，那么，以当前世纪为日期的世纪：
如当前年份是2008：select to_date('08-05-07','rr-mm-dd') from dual;  结果：2008-05-07 00:00:00
（2）、如果当前日期是上半世纪，你想转换的日期去是下半世纪，那么，用当前世纪的上个世纪为日期的世纪
如当前年份是2008：select to_date('95-05-07','rr-mm-dd') from dual; 结果为1995-05-07 00:00:00 ，世纪为当前世纪的上个世纪。
（3）、如果当前日期是下半世纪，你想转换的日期是上半世纪，那么，是以当前世纪的下个世纪为日期的世纪
如当前年份是1999年：select to_date('08-05-07','rr-mm-dd') from dual; 结果为当前世纪的下个世纪，2008-05-07 00:00:00。
（4）、如果当前日期是下半世纪，你想转换的日期也是下半世纪，以当前世纪为日期的世纪：
当年年份是1999年：select to_date('98-05-07','rr-mm-dd') from dual; 结果是1998-05-07 00:00:00。以当前世纪为转换后日期的世纪。

 

七、通用函数
用函数可以针对各种数据类型时行操作，包括NULL值在内。其中有一类通用函数，是专门针对NULL值的。
1、针对NULL值的通用函数
（1）NVL（表达式1，表达式2）： 转换空值为一个实际值。如果表达式1值为空，NVL将返回表达式2的值作为结果。如果表达式1不为空，以表达式1的值作为结果。
 这个函数在有些时候非常有用，在很多应用程序中，都要把空当作0处理，这利用NVL是容易实现的：

> select name,salary,nvl(salary,0) from t1;

NAME           SALARY NVL(SALARY,0)
---------- ---------- -------------
gyj2             8000          8000
gyj3            10000         10000
gyj4            15000         15000
gyj8                     0

（2）NVL2 （表达式1，表达式2，表达式3）
此函数比NVL更复杂些。它首先判断表达式1的值，如果表达式1非空，函数返回表达式2的值作为结果。如果表达式1 为空， 返回表达式3的值为结果。表达式1 可以是任意数据类型、运算式子。
仍以上面的例子为准，将SAL列中为空的转换成0，不为空的不变：

> select name,salary,nvl2(salary,salary,0) from t1;

NAME           SALARY NVL2(SALARY,SALARY,0)
---------- ---------- ---------------------
gyj2             8000                  8000
gyj3            10000                 10000
gyj4            15000                 15000
gyj8                                      0

（3）NULLIF(表达式1，表达式2)
比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式。
用它可以实现和上面例子中相反的效果。有些应用程序，要求把为0的值显示为空：

> update t1 set salary=0 where id=5;

1 row updated.

> commit;

Commit complete.

> select id,name,nullif(salary,0) from t1;

        ID NAME       NULLIF(SALARY,0)
---------- ---------- ----------------
         2 gyj2                   8000
         3 gyj3                  10000
         4 gyj4                  15000
         5 gyj5
         7 gyj7                  12000

nullif(sal,0)，就是将SAL为0的转变为空。NVL和NVL2是将为空的转变成其他不是空的值。

（4）COALESCE（表达式1，表达式2，表达式3，…………，表达式n）
返回表达式列表中的第一个值为非空的表达式的值。
它可以实现和NVL一样的功能，而且它的功能比NVL强。如果用它来实现将NULL转为0的操作，方法如下：

> select id,name,COALESCE(salary,0) from t1;

   ID NAME       COALESCE(SALARY,0)
---------- ---------- ------------------
         2 gyj2                     8000
         3 gyj3                    10000
         4 gyj4                    15000
         5 gyj5                        0
         7 gyj7                    12000

2、条件类通用函数
(1)CASE分析
 它的使用形式为：
CASE 判断表达式 
WHEN 表达式1  THEN  结果表达式1
WHEN 表达式2  THEN  结果表达式2
WHEN 表达式3  THEN  结果表达式3
…………
ELSE 结果表达式N
END
 好我们来找个例子提取如下数据：一级代理商、姓名、地区、一级自身充值量（30元）、一级自身充值量（其他面额）、所管辖二级代理商充值量（30元）、所管辖二级代理商充值量（其他面额）。
 select a.agtphone 一级代理商,b.agtname 姓名,c.name 地区,ltone30 联一级自身充值量30,ltone 联一级自身充值量其它,lttwo30 联二进制级自身充值量30,lttwo 联二级自身充值量其它，
dxone30 电一级自身充值量30, dxone 电一级自身充值量其它 ,dxtwo30 电二级自身充值量30, dxtwo 电二级自身充值量其它 from (select a.agtbelong agtphone,
                                nvl(sum(case when  agtlevel=1 and opmoney=3000  and prepaychnl='02' then opmoney else 0 end),0)/100 ltone30,
                                nvl(sum(case when  agtlevel=1 and opmoney<>3000 and prepaychnl='02' then opmoney else 0 end),0)/100 ltone,
                                nvl(sum(case when agtlevel=2  and opmoney=3000  and prepaychnl='02' then opmoney  else 0 end),0)/100 lttwo30,
                                nvl(sum(case when agtlevel=2  and opmoney<>3000 and prepaychnl='02' then opmoney  else 0 end),0)/100 lttwo,
                                nvl(sum(case when  agtlevel=1 and opmoney=3000  and prepaychnl='03' then opmoney else 0 end),0)/100 dxone30,
                                nvl(sum(case when  agtlevel=1 and opmoney<>3000 and prepaychnl='03' then opmoney else 0 end),0)/100 dxone,
                                nvl(sum(case when agtlevel=2  and opmoney=3000  and prepaychnl='03' then opmoney  else 0 end),0)/100 dxtwo30,
                                nvl(sum(case when agtlevel=2  and opmoney<>3000 and prepaychnl='03' then opmoney  else 0 end),0)/100 dxtwo
             from (select agtphone, agtphone agtbelong, agtlevel
                    from pub_agt_info where agtlevel = 1
                    union
                    select agtphone, agtbelong, agtlevel
                     from pub_agt_info where agtlevel = 2) a,
                     log_agt_prepay b,
                     pub_agtacc_info c
              where a.agtphone = c.agtphone
                and c.contractno = b.contractno
                and state = 0
                and b.deptno = '0200000000'
                and to_char(optime, 'yyyymmdd') between '20110301' and '20110331'
                group by a.agtbelong) a,
                pub_agt_info b,
                base_dept_info c
                where a.agtphone=b.agtphone
                and b.agtdepart=c.deptno;
                                             
(2)DECODE 函数
 使用DECODE函数，可以实现和CASE同样的结果。它的使用形式是：
DECODE(判断表达式, 表达式1, 结果表达式1，[, 表达式2, 结果表达式2,...,][, 结果表达式N])
用判断表达式的值，依次和每个表达式比较，找到第一个相等的，其后的结果表达式就是函数的最终结果。如果没有一个表达式相等，括号中最后一个值结果表达式N就是最终的结果。

好我们来找个例子提取如下数据：机构   总网点   新增    充值笔数    充值量   累计充值量    存款笔数   存款额   累计存款笔数    累计存款额 冲正笔数 冲正量 帐户余额。
select decode(b.jg,
              0200066300,
              '清远市',
              0200066200,
              '阳江市',
              0200075400,
              '汕头市',
              0200076000,
              '中山市',
              0200075900,
              '湛江市',
              0200075000,
              '江门市',
              0200066800,
              '茂名市',
              0200066000,
              '汕尾市',
              0200075300,
              '梅州市',
              0200076200,
              '河源市',
              0200075700,
              '佛山市',
              0200076800,
              '潮州市',
              0200076300,
              '清远市',
              0200075100,
              '韶关市',
              0200020000,
              '广州市',
              0200075800,
              '肇庆市',
              0200075500,
              '深圳市',
              0200076900,
              '东莞市',
              0200076600,
              '云浮市',
              0200075200,
              '惠州市',
              0200075600,
              '珠海市',
              'GZ') 机构,
       b.zwd 总网点,
       b.xz 新增,
       a.czbs 充值笔数,
       a.czl 充值量,
       f.chzhbs 冲正笔数,
       f.chzhl 冲正量,
       c.ljczl 累计充值量,
       d.ckbs 存款笔数,
       d.cke 存款额,
       e.ljckbs 累计存款笔数,
       e.ljcke 累计存款额,
       h.zhye 账户余额,
       '电信'
  from (select c.agtdepart jg, --机构/充值笔数/充值量
               count(*) czbs,
               sum(opmoney) / 100 czl
          from log_agt_prepay a, pub_agtacc_info b, pub_agt_info c
         where a.contractno = b.contractno
           and b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and b.agtstate <= 1
           and a.prepaychnl = '03'
           and a.state in (0, 9)
           and c.provid = '0200'
           and a.optime >= to_date(20110220000000, 'yyyymmddhh24miss') --起始日期(自定义)
           and a.optime <= to_date(20110228235959, 'yyyymmddhh24miss') --截止日期(自定义)
         group by c.agtdepart) a,
       --机构/总网点/新增
       (select a.agtdepart jg, a.topnum zwd, addnum xz
          from (select agtdepart, count(*) topnum
                  from pub_agt_info a, pub_agtacc_info b
                 where a.agtphone = b.agtphone
                   and a.corpid = b.corpid
                   and agtstate <= 1
                   and a.provid = '0200'
                   and agtregtime < =
                       to_date(20110228235959, 'yyyymmddhh24miss') --总网点截止日期(自定义)
                 group by agtdepart) a,
               (select agtdepart, count(*) addnum
                  from pub_agt_info a, pub_agtacc_info b
                 where a.agtphone = b.agtphone
                   and a.corpid = b.corpid
                   and agtstate <= 1
                   and a.provid = '0200'
                   and agtregtime >=
                       to_date(20110220000000, 'yyyymmddhh24miss') --新增网点起始日期(自定义)
                   and agtregtime <=
                       to_date(20110228235959, 'yyyymmddhh24miss') --新增网点截止日期(自定义)
                 group by agtdepart) b
         where a.agtdepart = b.agtdepart(+)) b,
       --机构/累计充值量
       (select c.agtdepart jg, sum(opmoney) / 100 ljczl
          from log_agt_prepay a, pub_agtacc_info b, pub_agt_info c
         where a.contractno = b.contractno
           and b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and c.provid = '0200'
           and b.agtstate <= 1
           and a.prepaychnl = '03'
           and a.state in (0, 9)
           and a.optime >= to_date(20110101000000, 'yyyymmddhh24miss') --起始日期(自定义)
           and a.optime <= to_date(20110228235959, 'yyyymmddhh24miss') --截止日期(自定义)
         group by c.agtdepart) c,
       --机构/存款笔数/存款额
       (select agtdepart jg, sum(depositnum) ckbs, sum(topdepositmoney) cke
          from ((select contractno,
                        count(*) depositnum,
                        sum(opmoney) / 100 topdepositmoney
                   from log_agt_deposit
                  where state = '0'
                    and optime >= to_date(20110220000000, 'yyyymmddhh24miss') --起始日期(自定义)
                    and optime <= to_date(20110228235959, 'yyyymmddhh24miss') --截止日期(自定义)
                  group by contractno)) a,
               pub_agtacc_info b,
               pub_agt_info c
         where b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and a.contractno = b.contractno
           and c.provid = '0200'
           and b.agtstate <= 1
         group by c.agtdepart) d,
       ---机构/累计款存笔数/累计款存额
       (select agtdepart jg,
               sum(depositnum) ljckbs,
               sum(topdepositmoney) ljcke
          from ((select contractno,
                        count(*) depositnum,
                        sum(opmoney) / 100 topdepositmoney
                   from log_agt_deposit
                  where state = '0'
                    and optime >= to_date(20110101000000, 'yyyymmddhh24miss') --起始日期(自定义)
                    and optime <= to_date(20110228235959, 'yyyymmddhh24miss') --截止日期(自定义)
                  group by contractno)) a,
               pub_agtacc_info b,
               pub_agt_info c
         where b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and a.contractno = b.contractno
           and c.provid = '0200'
           and b.agtstate <= 1
         group by c.agtdepart) e,
       --冲正笔记和冲正量
       (select c.agtdepart jg, count(*) chzhbs, sum(opmoney) / 100 chzhl
          from log_agt_prepay a, pub_agtacc_info b, pub_agt_info c
         where a.contractno = b.contractno
           and b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and b.agtstate <= 1
           and a.prepaychnl = '03'
           and a.state in (1, 2)
           and c.provid = '0200'
           and a.optime >= to_date(20110220000000, 'yyyymmddhh24miss') --起始日期(自定义)
           and a.optime <= to_date(20110228235959, 'yyyymmddhh24miss') --截止日期(自定义)
         group by c.agtdepart) f,
       --帐户余额
       (select agtdepart jg, sum(credit) / 100 zhye
          from pub_account_info a, pub_agtacc_info b, pub_agt_info c
         where a.accountno = b.accountno
           and b.agtphone = c.agtphone
           and b.corpid = c.corpid
           and b.agtstate <= 1
           and c.provid = '0200'
         group by c.agtdepart) h
 where b.jg = a.jg(+)
   and b.jg = c.jg(+)
   and b.jg = d.jg(+)
   and b.jg = e.jg(+)
   and b.jg = f.jg(+)
   and b.jg = h.jg(+);

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
QQ: 252803295
Email:[e-mail]
尖峰官网：
尖峰淘宝：
WEIBO：
尖峰OCP认证考试群297227448  
尖峰OCM认证考试群99606943
尖峰MySQL研究院群314746420
尖峰JAVA研究院群 315405063  
尖峰Hadoop研究院群366294602
尖峰线上技术分享群252296815
尖峰SQL优化研究院群250057366