[Oracle数据库学习]三、单行函数（1）

D1

单行函数

function_name[(arg1, arg2,...)]

 

特点：

1）操作数据对象，可以转换数据类型；

2）接受函数返回一个结果；

3）只对一行进行变换，每行返回一个结果；

4）参数可以是一个值或者一列；

5）可以嵌套。

 

字符函数

类型	函数
大小写控制函数	LOWER UPPER INITCAP
字符控制函数	CONCAT SUBSTR LENGTH INSTR LPAD | RPAD TRIM REPLACE

 

大小写控制函数

SELECT LOWER('SQL Course'), UPPER('SQL Course'), INITCAP('SQL Course')  FROM dual;

LOWER('SQLCOURSE')	UPPER('SQLCOURSE')	INITCAP('SQLCOURSE')
sql course	SQL COURSE	Sql Course

 

字符串匹配大小写

SELECT employee_name, department_id, job_id
FROM employees
WHERE employee_name = 'alice';

此时没有查询结果，如果采用大小写转换：

SELECT employee_name, department_id, job_id
FROM employees
WHERE LOWER(employee_name) = 'alice';

EMPLOYEE_NAME	DEPARTMENT_ID	JOB_ID
Alice	1	PM

 

字符控制函数

SELECT CONCAT('Hello', 'World'),
SUBSTR('HelloWorld',1,5),
LENGTH('HelloWorld'),
INSTR('HelloWorld', 'W'),
TRIM('H' FROM 'HelloWorld')
FROM dual;

CONCAT('HELLO','WORLD')	SUBSTR('HELLOWORLD',1,5)	LENGTH('HELLOWORLD')	INSTR('HELLOWORLD','W')	TRIM('H'FROM'HELLOWORLD')
HelloWorld	Hello	10	6	elloWorld

注：

CONCAT：连接字符串；

SUBSTR：截取子串；

LENGTH：长度；

INSTR：字符在字符串中的位置；

TRIM：去除首字符。

 

SELECT salary, LPAD(salary,10,'*'), RPAD(salary, 10, '*')
FROM employees;

SALARY	LPAD(SALARY,10,'*')	RPAD(SALARY,10,'*')
10000	*****10000	10000*****
5000	******5000	5000******
6000	******6000	6000******
4000	******4000	4000******
8000	******8000	8000******

注：用指定字符补位对齐，LPAD从左边补位，RPAD从右边补位。

SELECT employee_name, REPLACE(employee_name, 'a', 'A')
FROM employees;

EMPLOYEE_NAME	REPLACE(EMPLOYEE_NAME,'A','A')
Alice	Alice
Bob	Bob
Cindy	Cindy
Donald	DonAld
Elsa	ElsA

注：REPLACE替换字符。

SELECT employee_name, 
LENGTH(employee_name), 
CONCAT(department_id, job_id) "department_job", 
INSTR(employee_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR(JOB_ID,2,1) = 'M';

如果employee_name定义为char(100)，则：

EMPLOYEE_NAME	LENGTH(EMPLOYEE_NAME)	department_job	Contains 'a'?
Alice	100	1PM	0
Donald	100	2AM	4

如果employee_name定义为varchar(100)，则：

EMPLOYEE_NAME	LENGTH(EMPLOYEE_NAME)	department_job	Contains 'a'?
Alice	5	1PM	0
Donald	6	2AM	4

 

数字函数

函数名	含义
ROUND	四舍五入
TRUNC	截断
MOD	求余

ROUND函数

SELECT ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-1)
FROM dual;

ROUND(45.923,2)	ROUND(45.923,0)	ROUND(45.923,-1)
45.92	46	50

 

TRUNC函数

SELECT TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-2)
FROM dual;

TRUNC(45.923,2)	TRUNC(45.923)	TRUNC(45.923,-2)
45.92	45	0

 

MOD函数

SELECT MOD(45.923,10), MOD(45.923,1), MOD(45.923,0.02)
FROM dual;

MOD(45.923,10)	MOD(45.923,1)	MOD(45.923,0.02)
5.923	0.923	0.003

示例

SELECT employee_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'DEV';

EMPLOYEE_NAME	SALARY	MOD(SALARY,5000)
Bob	5000	0
Elsa	8000	3000

 

日期函数

日期

Oracle内部使用数字存储日期: 世纪-年-月-日-小时-分钟-秒；
默认的日期格式是DD-MON-RR；
可以只指定年的后两位在20世纪存放21世纪的日期，同样可以在21世纪存放20世纪的日期。

 

SELECT employee_name, hire_date
FROM employees
WHERE job_id = 'DEV';

EMPLOYEE_NAME	HIRE_DATE
Bob	2017-03-10T00:00:00Z
Elsa	2007-11-13T00:00:00Z

注：编辑器原因非默认格式，下同。

 

日期函数SYSDATE

返回：当前日期和时间

 

日期的数学运算

1）在日期上加上或减去一个数字结果仍为日期。
2）两个日期相减返回日期之间相差的天数。
3）可以用数字除24来向日期中加上或减去小时。

 

SELECT employee_name, hire_date, (SYSDATE-hire_date)/364 AS YEARS
FROM employees
WHERE department_id = 1;

EMPLOYEE_NAME	HIRE_DATE	YEARS
Alice	2007-11-15T00:00:00Z	12.693238769332519
Bob	2017-03-10T00:00:00Z	3.3443376704314205

 

日期函数

函数	描述
MONTHS_BETWEEN	两个日期间相差的月数
ADD_MONTHS	向指定日期中加上若干个月
NEXT_DAY	指定日期的下一个日
LAST_DAY	本月的最后一天
ROUND	日期四舍五入
TRUNC	日期阶段

 

SELECT MONTHS_BETWEEN ('01-SEP-95','11-JAN-94'),
ADD_MONTHS ('11-JAN-94',6),
NEXT_DAY ('01-SEP-95','FRIDAY'),
LAST_DAY ('01-FEB-95')
FROM dual;

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')	ADD_MONTHS('11-JAN-94',6)	NEXT_DAY('01-SEP-95','FRIDAY')	LAST_DAY('01-FEB-95')
19.677419354838708	1994-07-11T00:00:00Z	1995-09-08T00:00:00Z	1995-02-28T00:00:00Z

 

SELECT ROUND(SYSDATE,'MONTH') ,
ROUND(SYSDATE ,'YEAR') ,
TRUNC(SYSDATE ,'MONTH') ,
TRUNC(SYSDATE ,'YEAR')
FROM dual;

ROUND(SYSDATE,'MONTH')	ROUND(SYSDATE,'YEAR')	TRUNC(SYSDATE,'MONTH')	TRUNC(SYSDATE,'YEAR')
2020-07-01T00:00:00Z	2021-01-01T00:00:00Z	2020-07-01T00:00:00Z	2020-01-01T00:00:00Z

 

总结：

1）本节介绍以下单行函数：字符函数、数字函数、日期函数；

2）字符函数：大小写控制函数（LOWER、UPPER、INITCAP）、字符控制函数（CONTRACT、SUBSTR、LENGTH、INSTR、LPAD | RPAD、TRIM、REPLACE）；

3）数字函数：ROUND、TRUNC、MOD；

4）日期函数：SYSDATE、MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND、TRUNC。