【SQL 函数】

下面我为大家演示Oracle中有关SQL语法中的常用函数分类及用法，这些函数的使用并不难，但是熟练掌握其用法却是DBA的一个基本必备技能。

SQL之函数作特点及作用：
操作数据；
将输入的变量处理，返回一个结果；
可以有一个变量或者多个变量；
变量可以是列的值，也可是表达式；
可以嵌套，内层函数结果是外层函数的变量；

SQL中的函数可以归纳为：
单行函数：每行返回一个结果
分组函数： 多行返回一个结果
分支函数：又叫条件表达式

其中单行函数又可归纳为：
字符操作函数
数字操作函数
日期操作函数
日期转换函数
综合数据类型函数

字符操作函数常见的如下：
lower（column）、lower ('string')：将变量中字符串全部转换为小写形式； 如：SQL> select   lower（ename） from  EMP；
upper（column）、upper（'string'）：将变量中字符串全部转换为大写形式； 如：SQL>select  upper ('love')    from  dual;
initcap（column）、initcap （'string'）：将变量中字符串转换为只有首字母大写形式；  如： SQL>select  initcap ('GOOD')   from  dual;   SQL >select initcap (ename) from EMP;
例如：
SQL> select upper('liumang'),lower('liumang'),initcap('liumang') from dual;


UPPER(' LOWER(' INITCAP
------- ------- -------
LIUMANG liumang Liumang

concat（column ，column）、concat（column，’string‘）、concat （’string‘，'string'）： 将两个变量字符串连接在一起，作用等同于连字符“||” ； 如：
SQL> select concat （ename, sal ) from emp <=> SQL>select ename || sal from emp;
SQL> select concat ('ename' ,  'sal' ) from dual   <=> SQL> select 'ename' || 'sal' from dual;
SQL> select  ename || '- - - -' || sal  from emp <=> select concat (concat (ename,'- - - -')，sal ) from emp;

length('string') :按照字符个数求字符串长度
lengthb('string')：按照字节求字符串长度
lengthc('string')： unicode 的长度
例如：
SQL> select length('中国') from dual;
LENGTH('中国')
--------------
    2

SQL> select lengthb('中国') from dual;
LENGTHB('中国')
---------------
     6

SQL> select lengthc('中国') from dual;
LENGTHC('中国')
---------------
     2

substr('string' , N , M ) :从string的第N个字符开始（如果为负数，就为倒数第N个开始），往后输出M个字符，如果M为空，就是输出到最后一个字符。例如：
SQL> select substr('ABCDEFGH',-4,3) from dual;
SUB
---
EFG

SQL> select substr('ABCDEFGH',4,3) from dual;
SUB
---
DEF

SQL> select substr('ABCDEFGH',4) from dual;
SUBST
-----
DEFGH

SQL> select substr('ABCDEFGH',-4) from dual;
SUBS
----
EFGH

instr（'string' , 'A'） : 字符A在字符串中排第几位 ,例如：
SQL> select instr('abcd','c') from dual;

INSTR('ABCD','C')
-----------------
3
trim（ leading  'a' from 'aabcaaedfaa'） :截断连续前置的a
trim（trailing  'a' from 'aabcaadefaa'）:截断连续后置的a
trim（both  'a' from 'aabcaadefaa' ）:截断连续前置和后置的a
trim （'a' from 'aabcaadefaa'）   ：截断连续前置和后置的a  等价于 trim（both  'a' from 'aabcaadefaa'）
trim （'string'） : 去除字符前后的空格  等价于 trim （ ' ' from  ' string '）
trim （leading  ' '  from ' string '）:去除前置空格
trim （trailing  ' '  from ' string '） :去除后置空格
例如：
SQL> select trim(leading 'a' from 'aabcaadefaa') from dual;


TRIM(LEAD
---------
bcaadefaa


SQL> select trim(trailing 'a' from 'aabcaadefaa') from dual;


TRIM(TRAI
---------
aabcaadef


SQL> select trim(both 'a' from 'aabcaadefaa') from dual;


TRIM(BO
-------
bcaadef


SQL> select trim( 'a' from 'aabcaadefaa') from dual;


TRIM('A
-------
bcaadef

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

注释：以下实验中 '  abc    ' 表示 ：  abc前面有2个空格后面有4个空格

SQL> select length( '  abc    ') from dual;          
LENGTH('ABC')
-------------
   9


SQL> select length( trim ( leading ' ' from  '  abc    ') ) from dual;


LENGTH(TRIM(LEADING''FROM'ABC'))
--------------------------------
      7


SQL> select length( trim ( trailing ' ' from  '  abc    ') ) from dual;


LENGTH(TRIM(TRAILING''FROM'ABC'))
---------------------------------
5


SQL> select length( trim ( both ' ' from  '  abc    ') ) from dual;


LENGTH(TRIM(BOTH''FROM'ABC'))
-----------------------------
   3


SQL> select length( trim (  '  abc    ') ) from dual;


LENGTH(TRIM('ABC'))
-------------------
 3

lpad（'string' , N ,'-'）:左铺垫，在string的左边添加N个'-'，一般是为了输出好看
rpad（‘string’,N, '-'）:右铺垫,在string的右边添加N个‘-’，同样是为了排版好看
注意：经过本人亲自实验发现，每次输出的铺垫符号都为N-3个且若N<=3时候无铺垫符号输出，如果哪位童鞋知晓其中的原因还望留言告知，谢谢！
（本人实验环境为11g的  11.2.0.3.0  不知是否是版本原因导致的变动 ）
例如：

SQL> select lpad(name,2 ,'-')  from t4;


LPAD(NAM
--------
aa
bb
ab


SQL> select lpad(name,3 ,'-')  from t4;


LPAD(NAME,3,
------------
aaa
bbb
abc


SQL> select lpad(name,5 ,'-')  from t4;


LPAD(NAME,5,'-')
--------------------
--aaa
--bbb
--abc


SQL> select rpad(name,5 ,'-')  from t4;


RPAD(NAME,5,'-')
--------------------
aaa--
bbb--
abc--

replace （'string','str1' ,'str2'）: 在string中 用str2替换str2，可以是单个字符也可以是字符串。
例如：

SQL> select name from t5;


NAME
--------------------
abc123
abc456
abc789


SQL> select replace(name,'abc','wxy') from t5;


REPLACE(NAME,'ABC','WXY')
------------------------------------------------------------
wxy123
wxy456
wxy789

数字操作函数常见的如下：
round（number, N）: N可以为正数负数或者0，N为正数表示对number以四舍五入法保留N位小数，并由其后面一位小数的大小决定是否对其进行进1，
如果N为负数，则为对小数点左边第N为数字进行四舍五入。若N为0可以不写
例如：

SQL> select round(156.556,0),round(156.456,1),round(156.456,-1),round(156.456,-3) from dual;    


ROUND(156.556,0)     ROUND(156.456,1)        ROUND(156.456,-1)         ROUND(156.456,-3)
------------                     ----------------                   -----------------                     -----------------
    157                           156.5                                   160                               0

trunc(number,N) : N可以为正数和负数，N为正数表示保留到N为小数截断其后的小数，若为负数，表示对小数点左边第N为开始全部截断，N为0时可以不写。
例如：
SQL> select trunc(156.556,0),trunc(156.456,1),trunc(156.456,-1),trunc(156.456,-3) from dual;


TRUNC(156.556,0)       TRUNC(156.456,1)       TRUNC(156.456,-1)     TRUNC(156.456,-3)
---------------- -              ---------------                    -----------------                 -----------------
    156                        156.4                           150                                0

ceil（number）：取整并进向小数点左边第一位进1（也即个为进1）
例如：
SQL> select ceil(156.001),ceil(156.661) from dual;

CEIL(156.001)     CEIL(156.661)
-------------               -------------
 157                         157

abs（-number）：对-number 求绝对值
例如：
SQL> select abs(-156.001),abs(-156.661) from dual;


ABS(-156.001)          ABS(-156.661)
-------------                   -------------
156.001                  156.661

mod（number，N）：求number除以N的余数
例如：
SQL> select mod(5,2),mod(-10,4) from dual;

MOD(5,2)     MOD(-10,4)
----------          ----------
   1                    -2

操作日期的函数：

round（date1-date2）：求两个日期之间的整天数
如：
SQL> select (to_date('2014-06-30','yyyy/mm/dd')-sysdate) from dual;


(TO_DATE('2014-06-30','YYYY/MM/DD')-SYSDATE)
--------------------------------------------
 -733.27337


SQL> select round(to_date('2014-06-30','yyyy/mm/dd')-sysdate) from dual;


ROUND(TO_DATE('2014-06-30','YYYY/MM/DD')-SYSDATE)
-------------------------------------------------
    -733

months_between(date1,date2): 求date1和date2之间的月间隔
如：
SQL> select months_between(sysdate,to_date('2014-06-30','yyyy/mm/dd')) from dual;


MONTHS_BETWEEN(SYSDATE,TO_DATE('2014-06-30','YYYY/MM/DD'))
----------------------------------------------------------
24.1056821

add_months(date1,N): N个月后的今天是哪一天
如：
SQL> select sysdate from dual;


SYSDATE
-------------------
2016-07-02 06:42:30


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


ADD_MONTHS(SYSDATE,
-------------------
2016-12-02 06:42:36

next_day(date1,'friday'):  在date1日期的下一个周五是什么时间
在讲此函数前需要先给大家讲一下与数据库字符集有关的系统变量： NLS_LANG：指定客户端的语言、地域和字符集 ； 因为他能影响此函数的正确使用方式。
NLS_LANG 在Oracle 的~/.bash_profile 定义 如下：
[oracle@XiaoYong Desktop]$ grep NLS_LANG ~/.bash_profile 
#export NLS_LANG=american_america.AL32UTF8
export NLS_LANG='simplified chinese_china.AL32UTF8'

其组成主要包括三个部分：
language：语言，指定服务器消息在客户端是中文还是英文显示  对应于上例输出的  american  或 simplified chinese 
territory: 地域，影响服务器的日期和数字格式在客户端的显示
charset：字符集，告诉数据库客户端使用的字符集，当然未必是真实的客户端字符集。
默认情况下数据库就以系统配置文件制定的为准，当然也可以在数据库当中的会话级别分别设置他们
language 对应 nls_language  参数
territory 对应 nls_territory  参数
charset  设置跟以上两种略有不同，到时我会专门在字符集专题中详细介绍，此处就先不赘述。
如：
SQL> alter session set nls_language=american;   --会话级别设置系统提示语言为英文

Session altered.

SQL> desc tt1;
ERROR:
ORA-04043: object tt1 does not exist    --验证错误提示信息为英文

SQL> select next_day(sysdate,'friday') from dual;  因为语言设置为英文所以需要用英文的周五才能正确显示


NEXT_DAY(SYSDATE,'
------------------
08-JUL-16


SQL> select next_day(sysdate,'星期五') from dual;  因为语言为英文所以中文的friday 不能被识别
select next_day(sysdate,'星期五') from dual
                        *
ERROR at line 1:
ORA-01846: not a valid day of the week

SQL> alter session set nls_language='simplified chinese';  --修改系统提示语言为中文

Session altered.

SQL> desc tt1;
ERROR:
ORA-04043: 对象 tt1 不存在      --  验证错误提示确实变为了中文

SQL> select next_day(sysdate,'friday') from dual;   --因为客户端语言改为了中文，所以需要指定中文的’周五‘
select next_day(sysdate,'friday') from dual
                        *
ERROR at line 1:
ORA-01846: 周中的日无效




SQL> select next_day(sysdate,'星期五') from dual; --指定中文friday能正确输出


NEXT_DAY(SYSDA
--------------
08-7月 -16

last_day(date) :显示date当月的最后一天的时间
如：
SQL> select last_day(sysdate) from dual;


LAST_DAY(SYSDATE)
-------------------
2016-07-31 15:04:09


SQL> select last_day('2014-02-02') from dual;


LAST_DAY('2014-02-0
-------------------
2014-02-28 00:00:00

点评：据说日期类比较神奇，尽量使用日期类型来存储日期
查看系统日期：注意是系统日期而不是数据库日期，数据库中没有日期，显示的是操作系统的日期。
SQL> select sysdate from dual;

SYSDATE
-------------------
2016-07-02 02:07:52

日期显示格式由Oracle家目录下的环境变量NLS_DATE_FORMAT决定 
例如
[oracle@XiaoYong Desktop]$ cat  ~/.bash_profile 

PATH=$PATH:$HOME/bin
export PATH
export ORACLE_SID=wxy
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
#export ORACLE_HOSTNAME=node1.test.com
export ORACLE_UNQNAME=wxy
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.AL32UTF8
export  NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
export EDITOR=vi

环境变量中指定的 NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'的格式就是数据库日期默认显示格式
查看数据库日期默认显示格式可以以sys用户通过参数  NLS_DATE_FORMAT查看 如下：
 SQL> show parameter nls_date_format;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format     string yyyy-mm-dd hh24:mi:ss
SQL> 

或者以任何角色通NLS_SESSION_PARAMETERS 查看当前session的日期显示格式：如下
SQL> select * from nls_session_parameters where parameter=upper('nls_date_format');


PARAMETER       VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT       yyyy-mm-dd hh24:mi:ss

修改当前会话日期显示格式如下：
SQL> alter session set nls_date_format='yyyy/mm/dd';


Session altered.

SQL> select sysdate from dual;


SYSDATE
----------
2016/07/02
若没在~/.bash_profile 中指定NLS_DATE_FORMAT变量 则默认的日期显示格式是怎样的呢，看如下实验即可知道真相：
在操作系统取消此变量：
$unset NLS_DATE_FORMAT
登陆数据库查询结果如下：
SQL> select sysdate from dual;


SYSDATE
------------------
02-JUL-16


SQL> show parameter nls_date_format;


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format     string DD-MON-RR

即：默认NLS（国际语言支持）显示的是美国惯用的日期显示格式，毕竟Oracle是源自美国，所以自然可以理解默认显示。

实际上日期类型的数据在数据库内部都是以yyymmddhh24:mi:ss的形式存在，只不过输出时候可以指定不同方式输出。
可以修改NLS_DATE_FORMAT来指定显示方式或者用to_char函数指定显示模式，例如：
SQL> select sysdate from dual;


SYSDATE
----------
2016/07/02


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


Session altered.


SQL> select sysdate from dual;


SYSDATE
-------------------
2016-07-02 04:38:45
SQL>alter session set nls_date_format='yyyy-mm-dd hh:mi:ss am'; --以12小时格式显示时间

SQL> select sysdate from dual;


SYSDATE
----------------------
2018-01-02 06:32:49 pm



SQL> select to_char(sysdate,'yyyy*mm*dd hh12:mi:ss') from dual;


TO_CHAR(SYSDATE,'YY
-------------------
2016*07*02 04:40:02

下面说一下数据类型转换：隐式转换、显示转换
隐式转换：
一、数字到字符 

SQL> create table t1 (name varchar2(20));


Table created.


SQL> insert into t1 values(1);      --把number的 1  隐式转换为‘1’；


1 row created.

SQL> select * from t1 where name=1;     -- 把number的 1  隐式转换为‘1’；


NAME
--------------------
1

注意：上边一个查询中，当列中非全部为数字的字符串时候，将报错如下

SQL> insert into t1 values ('a');


1 row created.


SQL> select * from t1 where name=1;
ERROR:
ORA-01722: invalid number

no rows selected

二、字符串到数字
SQL> create table t2 (id number);


Table created.


SQL> insert into t2 values ('1')； --把字符串的'1'隐式转换为number的1

SQL> select * from t2 where id='1';     --把字符串的'1'隐式转换为number的1


ID
----------
1

三、字符串到日期
SQL> create table t3 (date_col date);


Table created.


SQL> insert into t3 values ('2014-02-06');


1 row created.


SQL> insert into t3 values ('2014-02-06 17:12:25');   ---字符串隐式转换为日期


1 row created.


SQL> select * from t3 where date_col='2014-02-06';  ---字符串隐式转换为日期


DATE_COL
-------------------
2014/02/06 00:00:00


SQL> select * from t3 where date_col='2014-02-06 17:12:25';   ---字符串隐式转换为日期


DATE_COL
-------------------
2014/02/06 17:12:25

显示转换：to_char、to_date、to_number

一、to_char : 日期转字符串、10进制数字转16进制字符串、数字转

1.日期转字符串
SQL> select to_char(sysdate,'yyyy"年" mm"月" dd"日" hh24"小时" mi"分" ss"秒"') from dual;


TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"HH2
----------------------------------------
2016年 07月 02日 06小时 25分 11秒



2.10进制转16进制

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31135825/viewspace-2121383/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31135825/viewspace-2121383/