Oracle函数之单行函数

SQL函数概述： 

输入                      函数执行作用                  输出

参数1

参数2                         函数                         结果值

...

参数n

单行函数

（一）单行函数语法

 语法:

 函数名[(参数1，参数2,…)] 

其中的参数可以是以下之一： 

变量 

列名

 表达式

（二）单行函数特征：

单行函数对单行操作

每行返回一个结果

有可能返回值与原参数数据类型不一致

单行函数可以写在SELECT、WHERE、ORDER BY子句中

有些函数没有参数，有些函数包括一个或多个参数

函数可以嵌套

（三）单行函数分类

1字符函数

定义：主要指参数类型是字符型，不同函数返回值可能是字符型或数值型。 

分类：大小写转换、字符处理

大小写转换：

（1）LOWER(列名|表达式)：将大写或大小写混合的字符转换成小写

实例：LOWER('ORACLE Test')--------->>oracle test

（2）UPPER(列名|表达式) ：将小写或大小写混合的字符转换成大写

实例：UPPER('ORACLE Test')--------LOWER('ORACLE Test')--------->>oracle test->>ORACLE TEST

（3）INITCAP(列名|表达式):将每个单词的第一个字母转换成大写，其余的字母都转换成小写

实例：INITCAP('ORACLE Test')--------->>OracleTest

完整代码实例：

SQL>SELECT * FROM EMP WHERE ENAME=UPPER('smith');

字符处理函数

（1）COUNCAT(列1, 列2)  或者  COUNCAT(表达式1,表达式2)  

注意：连接两个值，等同于 ||

实例：COUNT('Good','morning')--------->>Goodmoring

（2）SUBSTR(列，n1[,n2]) 或者 SUBSTR(表达式，n1[,n2]) 

注意：返回第一个参数中，从第n1位开始，长度为n2的子串。 如果n2省略,取第n1位开始的所有字符。 如果n1是负值，表示从第一个参数的后面第abs(n1)位开始向右取长度为n2的子串。

实例：SUBSTR('Good',1,3)--------->>Goo

（3）LENGTH(列)  或者  LENGTH(表达式) 

注意：取字符的长度

实例：LENGTH('Good')--------->>4

（4）INSTR(s1,s2,[,n1],[n2])  

注意：返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1

实例：INSTR('Good','d')--------->>4

（5）LPAD(s1,n1,s2) 

注意：返回s1被s2从左面填充到n1长度后的字符串

实例：LPAD(Good,10,'#')--------->>######Good

（6）RPAD(s1,n1,s2)  

注意：返回s1被s2从右面填充到n1长度后的字符串。

实例：RPAD(Good,10,'#')--------->>Good######

（7）TRIM(leading | trailing | both trim_character From trim_source) 

注意：TRIM:去除字符串头部或尾部（头尾）的字符 格式

实例：TRIM('G'  FROM 'Good')--------->>ood

（8）REPLACE(s1,s2,s3)  

注意：把s1中的s2用s3替换。

实例：REPLACE('Good',o,a)--------->>Gaad

2、数值函数

（1）ROUND(列, n) 或者 ROUND(表达式, n)

注意：ROUND将列或表达式所表示的数值四舍五入到小数点后的第n位

实例：ROUND(3.1415，3)--------->>3.142

（2）TRUNC(列, n) 或者 ROUND(表达式, n)

注意：TRUNC将列或表达式所表示的数值截取到小数点后的第n位

实例：TRUNC（3.1415,3）--------->>3.141

（3）MOD(m,n)

注意：MOD:取m除以n后得到的余数

实例：MOD（1100,200）--------->>100

3、日期函数

日期类型数学运算 

• 日期类型可以加减数字，功能是在该日期上加减对应的天数。如：’10-AUG-06’+15结果是’25-AUG-06’ 
• 日期类型之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天 
• 如果需要对一个日期进行加减相应小时操作，可以使用n/24来实现 
• 默认的日期形式是： DD-MON-RR

（1）SYSDATE:返回系统日期

（2）MONTH_BETWEEN:返回两个日期类型数据之间间隔的自然月数

实例：查询所有员工上班以来的月数

SELECT  * MONTHS_BETWEEN(SYSDATE,hiredate) months

FROM   emp

ORDER BY months; 

（3）ADD_MONTH:返回指定日期加上相应的月数后的日期

实例：查询82年后入职的员工转正日期，按照3个月试用期考虑

SELECT  ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date 

FROM      emp 

WHERE  hiredate>'01-1月-82'; 

（4）NEXT_DAY：返回某一日期的下一个指定日期（星期几） 

实例：返回在09-8月-16之后的下一个周一是什么日期。

SELECT  NEXT_DAY('09-8月-16','星期一') NEXT_DAY 

FROM     DUAL; 

（5）LAST_DAY：返回指定日期当月最后一天的日期

实例：返回16年8月9日所在月份的最后一天。 

SELECT LAST_DAY('09-8月-16') "LAST DAY" 

FROM    DUAL; 

（6）ROUND(date[,‘fmt’])将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。

实例：查询81年入职的员工姓名，入职日期按月四舍五入的日期。 

SELECT  empno, hiredate, ROUND(hiredate, 'MONTH') 

FROM      emp 

WHERE  SUBSTR(hiredate,-2,2)=‘81'; 

（7）TRUNC(date[,‘fmt’])将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。 

实例：查询81年入职的员工姓名，入职日期按月截断的日期。

SELECT    empno, hiredate, TRUNC(hiredate, 'MONTH') 

FROM        emp 

WHERE    SUBSTR(hiredate,-2,2)=‘81'; 

（8）EXTRACT：返回日期类型数据中的年份、月份或者日。

EXTRACT ([YEAR] [MONTH][DAY]  FROM       [日期类型表达式]) 

实例：部门编号是20的部门中所有员工入职月份。

SELECT  ename, hiredate,  EXTRACT (MONTH  FROM  HIREDATE) MONTH

 FROM   emp 

WHERE  deptno= 20; 

4、转换函数

（1）TO_CHAR 

用于日期型：TO_CHAR(date, 'fmt')

日期格式模型的元素：

YYYY:完整的年份数字表示

YEAR:年份的英文表示

MM:用两位数字来表示月份

MONTH:月份的全名

DAY:星期几

DY:用3个英文字符缩写来表示星期几

实例：SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hired  FROM emp;

用于数值型：TO_CHAR(number, 'fmt')

数值格式模型的元素：

9：一位数字

0：显示前导零

$:显示美元符号

L：显示本地货币符号

.  :显示小数点

,  :显示千位符

实例：SQL> SELECT    TO_CHAR(sal,'$99,999') SALARY   FROM    emp     WHERE    ename = 'SCOTT';

------->> 

                 SALARY 

                 $3,000

（2）TO_NUMBER:将一个字符串转换成数值型数据

         TO_NUMBER(char[, 'fmt']) 

注意：要转换的char类型数据必须是由数字组成的字符串，格式码中相应的格式必须要和char中的格式匹配。

（3）TO_DATE：将一个字符串转换成日期型数据

         TO_DATE(char[, 'fmt']) 

注意：要转换的char类型数据必须是可以转换成日期的字符，格式码的格式必须要和char中的格式匹配。

5、通用函数

与空值（NULL） 相关的一些函数，完成对空值（NULL）的一些操作。主要包括以下函数：NVL,NVL2，NULLIF,COALESCE;

条件处理函数：CASE表达式、DECODE

（1）NVL函数

语法:NVL (expr1,expr2)         如果expr1不是null,返回expr1,否则返回expr2  

实例：SELECT enamel，sal,comm,(sal*12)+NVL(comm,0) from emp;

（2）NVL2函数

语法:NVL2(expr1,expr2,expr3)    如果expr1不是null,返回expr2,否则返回expr3 

（3）NULLIF函数

语法: NULLIF(expr1,expr2)       比较两个表达式,如果相等，返回null,否则，返回第一个表达式 

（4）COALESCE函数

语法：COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数，参数个数不受限制。 

实例：SELECT  ename, COALESCE(comm, 0)  comm , deptno FROM   emp;

（5）CASE函数

语法：CASE expr       

                   WHEN comparison_expr1  THEN return_expr1    

                   [WHEN comparison_expr2 THEN return_expr2                                                                     

                   WHEN comparison_exprn  THEN return_exprn      

                   ELSE else_expr] 

           END

实例：SELECT ename, deptno,       

          (CASE deptno                    

                           WHEN 10  THEN '销售部'                         

                           WHEN 20  THEN '技术部'                        

                            WHEN 30  THEN '管理部'                           

                            ELSE '无此部门'    

                            END)                                

            deptname    FROM emp;

（6）DECODE函数

语法：DECODE(字段|表达式, 条件1,结果1[,条件2,结果2…，][,缺省值])

实例：SELECT ename, deptno,   

           DECODE(deptno,    

            10,'销售部',     

            20,'技术部',     

            30 , '管理部',    

            '无此部门')   

          deptname FROM emp;

6、函数的嵌套

单行函数可以嵌套于任何层。 

嵌套的函数是从最里层向最外层的顺序计算的。

实例：SQL> SELECT    ename,   

          NVL(TO_CHAR(mgr),'No Manager')   

          FROM    emp   

          WHERE    mgr IS NULL;