Oracle SQL 学习笔记2 - SQL中的函数

Oracle数据库为用户的SQL语句提供了语义丰富、功能强大、应用灵活的函数。本文讨论这些函数的各种用法，包括SELECT语句中常用的字符串、数字和日期函数，以及转换函数的用法。

单行函数

单行函数用于操作数据项。它接收参数并返回一个值。“单行”是指在SQL语句返回的每一行上进行处理，每一行都有一个处理结果。对一行执行单行函数，可能要修改数据类型。单行函数可以进行嵌套。

function_name (column | expression, [arg1, arg2, ...])

字符函数

string函数可以用在条件和from前面。

大小写转换函数
LOWER
UPPER
INITCAP
字符串操作函数

函数	结果
CONCAT(‘Good’, 'String')	GoodString
SUBSTR('String', 1, 3)	Str
LENGTH('String')	6
INSTR('String', 'r')*	3
LPAD(sal, 10, '*')	******5000
TRIM('S' FROM 'SSMITH')	MITH

*如果是字节操作，要用函数instrb。

数字函数

ROUND：对指定的值进行四舍五入
ROUN(45.926, 2) ----------> 45.93
TRUNC：对指定的值进行取整
TRUNC(45.926, 2) ----------> 45.92
MOD : 返回除法计算后的余数
MOD(1600,300) -------> 100

使用日期

Oracle以一种内部的格式来保存日期：世纪，年，月，日，小时，分钟，秒。其缺省格式为DD-MON-YY，例如：23-JAN-02。
SYSDATE是一个返回日期和时间的函数。

用数学运算符对日期进行计算

SQL> SELECT (SYSDATE - birth_day)/7 WEEKS FROM EMPLOYEE; 

     WEEKS
----------
.046810516
.020902778
.044927249
.041947751
 .04150463
.024508929

6 rows selected.

日期函数

函数	描述
MONTHS_BETWEEN	两个日期之间的月数
ADD_MONTHS	为一个日期增加月份
NEXT_DAY	一个日期的下一个指定日子（例如：星期1）的日期
LAST_DAY	某个月份的最后一天
ROUND	对日期进行四舍五入计算
TRUNC	对日期进行取整计算

使用Date函数

MONTHS_BETWEEN('01-SEP-95', '11-JAN-94')
ADD_MONTHS('11-JAN-94', 6)
NEXT_DAY('01-SEP-95', 'FRIDAY')
LAST_DAY('01-SEP-95')

转换函数

转换函数用于数据类型转换，包括隐式数据类型转换和显式数据类型转换。

隐式数据类型转换

Oracle服务器可以自动进行下列类型的转换：

From	To
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

对于表达式的计算，Oracle服务器可以自动进行下列转换：

From	To
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE

显式数据类型转换

data_trans.png

TO_CHAR函数

TO_CHAR(date, 'fmt')

fmt的格式*：

必须用单引号括起来，并且是大小写敏感的
日期格式必须是可用的
前面加上fm字符以压缩掉开始和结束的空格
同日期值用逗号隔开

* 也可以在OS层面设置时间的格式。

日期元素的格式

格式	含义
YYYY	四个数字表示的年份
YEAR	年份的名字
MM	两个数字表示的月份
MONTH	月份的名字
DY	每星期某天的字母缩写
DAY	天的完整名字

日期中时间部分的格式
HH24:MI:SS AM ----> 15:45:32 PM
增加编码字串需要引号将字符串括起来
DD "of" MONTH ----> 12 of OCTOBER
使用TO_CHAR函数

SQL> SELECT ename, TO_CHAR(birth_day,'DD Month YYYY') BIRTH_DAY FROM employee;

ENAME      BIRTH_DAY
---------- --------------------------------------------
Kobe       03 February  2020
Oneal      03 February  2020
Ross       03 February  2020
O%     03 February  2020

6 rows selected.

带有数字的TO_CHAR用法
TO_CHAR(number, 'fmt')
在TO_CHAR函数中使用这些格式，以按照某种字符格式展示一个数字值。

格式	含义
9	代表一个数字值
0	强制显示一个0
$	在一个浮点值前面显示一个$符号
L	使用浮点类型的本地货币符号
.	显示一个小数点
,	千位的指示符

TO_NUMBER 和 TO_DATE函数
将一个字符串转化为数字使用 TO_NUMNER 函数：
TO_NUMBER(char[, 'fmt'])
将一个字符串转化为日期格式使用 TO_DATE 函数：
TO_DATE(char[, 'fmt'])

NVL函数

NLV(expr1, expr2)

如果expr1为null，则返回expr2，否则返回expr1。
该函数可以使用的数据类型是数字、日期和字符型。
数据类型必须能够匹配，例如：
NVL(comm, 0)
NVL(birth_day, '01-JAN-97')
NVL(job, 'No Job Yet')

Decode 函数

DECODE函数的作用类似于CASE 或者 IF-THEN-ELSE语句。

DECODE(col/expression, search1, result1
                    [, search 2, result2, ....]
                    [, default]
                )

实例

SQL> SELECT ename, sal, DECODE( job,
    'ANALYST', SAL * 1.1,
    'CLERK', SAL * 1.15,
    'MANAGER', SAL * 1.20,
                          SAL        
)  REVISED_SALARY 
FROM employee;

函数的嵌套

单行函数可以嵌套到任何一个层次。其计算顺序为：先计算深层嵌套，再计算浅层嵌套。

embed.JPG

分组函数

什么是分组函数
分组函数对一组数据行进行操作，并对每个组得出一个结果：

group.JPG
分组函数的类型
AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

SELECT   [column] group_function(column)
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

分组函数和NVL函数的集合

NVL函数使得分组函数可以处理空值。

SQL> SELECT AVG(NVL(comm, 0))
2    FROM employee;

使用GROUP BY子句

在SELECT语句中，没有使用分组函数的列必须在GROUP BY子句中。

SQL> SELECT deptno, AVG(sal)
2  FROM employee
3  GROUP  BY deptno

分组函数的误用

不能在WHERE子句中对列做出限定。只能用HAVING子句来限定分组。

SQL> select avg(sal) from employee where avg(sal) > 1000 group by ename;
select avg(sal) from employee where avg(sal) > 1000 group by ename
                                    *
ERROR at line 1:
ORA-00934: group function is not allowed here

用HAVING子句排除分组结果

通过HAVING子句，可以将行分组。在HAVING子句中，可以使用分组函数，将符合HAVING子句条件的组选择出来。

SELECT column, group_function
FROM  table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

分组函数的嵌套使用

显示最大的平均工资：

SQL> SELECT max(avg(sal))
2  FROM employee
3  GROUP BY deptno;

Oracle SQL 学习笔记2 - SQL中的函数