我的学习之路——Oracle数据库之单行函数

清疚 2022-08-27 03:29 217阅读 0赞

1单行函数的一些特点

单行函数还有以下的一些特征：  
–单行函数对单行操作  
–每行返回一个结果  
–有可能返回值与原参数数据类型不一致（转换函数）  
–单行函数可以写在SELECT、WHERE、ORDER BY子句中  
–有些函数没有参数，有些函数包括一个或多个参数  
–函数可以嵌套

2单行函数的分类

字符函数

数字函数

日期函数

转换函数

通用函数

3 字符函数

大小写操作函数

LOWER(列名|表达式)其它同理

LOWER,UPPER,INITCAP(将首字母大写，其它字母小写)

字符处理函数

CONCAT:连接两个值，等同于||

CONCAT('Good','String‘) 结果 GoodString

SUBSTR:返回第一个参数中从n1字符开始长度为n2的子串，如果n1是负值，表从后向前数的abs(n1)位，如果n2省略,取n1之后的所有字符 格式：SUBSTR(column | expression,n1\[,n2\])

SUBSTR('String',1,3) 结果Str

LENGTH ：取字符串的长度 LENGTH('String') 结果 6

INSTR:返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1 格式:INSTR(s1,s2,\[,n1\],\[n2\])

INSTR('String','r') 结果 3

LPAD:返回s1被s2从左面填充到n1长度。 格式：LPAD(s1,n1,s2)

LPAD(sal,10,'\*') 结果 \*\*\*\*\*\*5000  
RPAD:返回s1被s2从右面填充到n1长度。 格式：RPAD(s1,n1,s2)

RPAD(sal,10,'\*') 结果5000\*\*\*\*\*\*  
TRIM:去除字符串头部或尾部（头尾）的字符 格式:TRIM(leading | trailing | both trim\_character From trim\_source)

TRIM('S' FROM 'SSMITH') 结果MITH  
REPLACE:把s1中的s2用s3替换。 格式:REPLACE(s1,s2,s3)

REPLACE（'abc','b','d') 结果adc

查找公司员工编号，用户名（first\_name与last\_name连接成一个字符串），职位编号及last\_name的长度，要求职位从第四位起匹配'ACCOUNT'，同时last\_name中至少包含一个’e’字母。  
SELECT employee\_id, CONCAT(first\_name, last\_name)  
NAME, job\_id, LENGTH (last\_name) length  
FROM employees  
WHERE SUBSTR(job\_id, 4) = 'ACCOUNT'  
AND INSTR(last\_name, 'e')>0;

4数字函数

ROUND(列名|表达式, n)：将列或表达式所表示的数值四舍五入到小数点后的第n位。  
TRUNC(列名|表达式,n)：将列或表达式所表示的数值截取到小数点后的第n位。  
MOD(m,n)：取m除以n后得到的余数。

5日期函数

常用的日期运算如下：  
–日期类型列或表达式可以加减数字，功能是在该日期上加减对应的天数。如：’10-AUG-06’+15结果是’25-AUG-06’。  
–日期类型列或表达式之间可以进行减操作，功能是计算两个日期之间间隔了多少天。如：’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天。  
–如果需要加减相应小时或分钟，可以使用n/24来实现

SYSDATE：返回系统日期  
•MONTHS\_BETWEEN：返回两个日期间隔的月数  
•ADD\_MONTHS：在指定日期基础上加上相应的月数  
•NEXT\_DAY：返回某一日期的下一个指定日期  
•LAST\_DAY：返回指定日期当月最后一天的日期  
•ROUND(date\[,'fmt'\])将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD’，将date四舍五入为最近的天。  
格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS  
•TRUNC(date\[,'fmt'\])将date按照fmt指定的格式进行截取，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。  
•EXTRACT：返回从日期类型中取出指定年、月、日

MONTHS\_BETWEEN 函数演示——公司员工服务的月数。

SELECT last\_name, salary, MONTHS\_BETWEEN(SYSDATE,hire\_date) months  
FROM employees  
ORDER BY months;  
ADD\_MONTHS 函数演示——99年公司员工转正日期。

SELECT last\_name, salary, hire\_date, ADD\_MONTHS(hire\_date,3) new\_date

FROM employees

WHERE hire\_date>'01-1月-1999';

NEXT\_DAY 函数演示——下周一的日期。

SELECT NEXT\_DAY('02-2月-06','星期一') NEXT\_DAY FROM DUAL;

LAST\_DAY 函数演示——06年2月2日所在月份最后一天。  
SELECT LAST\_DAY('02-2月-2006') "LAST DAY" FROM DUAL;

ROUND函数演示——98年入职员工入职日期按月四舍五入。  
SELECT employee\_id, hire\_date, ROUND(hire\_date, 'MONTH') FROM employees WHERE SUBSTR(hire\_date,-2,2)='98';

TRUNC 函数演示——98年入职员工入职日期按月截断。  
SELECT employee\_id, hire\_date, TRUNC(hire\_date, 'MONTH') FROM employees WHERE SUBSTR(hire\_date,-2,2)='98';

EXTRACT 函数语法  
部门编号是90的部门中所有员工入职月份。  
EXTRACT (\[YEAR\] \[MONTH\]\[DAY\] FROM \[日期类型表达式\])  
SELECT last\_name, hire\_date, EXTRACT (MONTH FROM HIRE\_DATE) MONTH FROM employees WHERE department\_id = 90;

6转换函数

数据类型转换分为隐式转化和显示转换

对数据进行隐式转换

对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型  
–对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型  
–当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型  
–当比较字符型和日期型的数据时，oracle会把字符型转换为日期型  
–用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型  
–如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等

数据类型的显示转换，通常是在字符类型，日期类型，数字类型直接进行显示转换

主要有3个显性函数

\-TO\_CHAR

\-TO\_NUMBER

\-TO\_DATE

TO\_CHAR(date|number \[,‘fmt’\])把日期类型/数字类型的表达式或列转换为字符类型。  
–‘fmt’指的是需要显示的格式：  
–需要写在单引号中，并且是大小写敏感  
–可包含任何有效的日期格式

常用日期格式  
–YYYY：4位数字表示年份；  
–YY：2位数字表示年份，但是无世纪转换(与RR区别在后面章节介绍)；  
–RR：2位数字表示年份，有世纪转换（与YY区别在后面章节介绍）；  
–YEAR：年份的英文拼写；  
–MM：两位数字表示月份；  
–MONTH：月份英文拼写；

–DY：星期的英文前三位字母；  
–DAY：星期的英文拼写；  
–D：数字表示一星期的第几天，星期天是一周的第一天。  
–DD：数字表示一个月中的第几天；  
–DDD：数字表示一年中的第几天。

–AM 或PM：上下午表示；  
–HH 或HH12或HH24：数字表示小时。HH12代表12小时计时，HH24代表24小时计时；  
–MI：数字表示分钟；  
–SS：数字表示秒；

一些特殊格式  
–TH：显示数字表示的英文序数词，如：DDTH显示天数的序数词。  
–SP：显示数字表示的拼写。  
–SPTH：显示数字表示的序数词的拼写。  
–“字符串”:如在格式中显示字符串，需要两端加双引号。  
TO\_CHAR(SYSDATE,’DDSPTH’)  
TO\_CHAR(SYSDATE,’DD “of” MONTH ‘)

select to\_char（sysdate,'YYYY-MM-DD'）  
from dual;

TO\_NUMBER(char\[,’fmt’\]) 把字符类型列或表达式转换为数字类型。  
–使用格式和TO\_CHAR中转换成字符类型中的格式相同

TO\_DATE(char\[,‘fmt’\]) 把字符类型列或表达式转换为日期类型。  
–格式和TO\_CHAR中转换成字符类型中的格式相同。

具体格式如下：  
–9：一位数字；  
–0：一位数字或前导零；  
–$：显示为美元符号；  
–L：显示按照区域设置的本地货币符号；  
–.：小数点;  
–,：千位分割符；

TO\_CHAR 函数进行数字到字符型格式转换  
注意：进行数字类型到字符型转换，格式中的宽度一定要超过实际列宽度，否则会显示为\#\#\#。  
SELECT last\_name, TO\_CHAR(salary, '$99,999.00') salary FROM employees WHERE last\_name = 'King';  
SELECT last\_name, TO\_CHAR(salary, '$9,999.00') salary FROM employees WHERE last\_name = 'King';

7RR功能

![Center][]

8通用函数

与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操作。主要包括以下函数：  
–NVL  
–NVL2  
–NULLIF  
–COALESCE  
条件处理函数：  
–CASE表达式  
–DECODE

NVL (表达式1, 表达式2)函数功能是空值转换，把空值转换为其他值，解决空值问题。表达式1是需要转换的列或表达式，表达式2是如果第一个参数为空时，需要转换的值。  
–NVL(comm,0)  
–NVL(hire\_date,'01-JAN-06')  
–NVL(job\_id,'No Job Yet')  
注意：数据格式可以是日期、字符、数字，但数据类型必须匹配。

NVL2(表达式1, 表达式2, 表达式3)函数是对第一个参数进行检查。如果第一个参数不为空，则输出第二个参数；如果第一个参数为空，则输出第三个参数。表达式1可以为任何数据类型

NULLIF (表达式1, 表达式2)函数主要是完成两个参数的比较。当两个参数不相等时，返回值是第一个参数值；当两个参数相等时，返回值是空值。

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

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 last\_name, commission\_pct,

(CASE commission\_pct

WHEN 0.1 THEN '低'

WHEN 0.2 THEN '中'

WHEN 0.3 THEN '高'

ELSE '无'

END) Commission

FROM employees

WHERE commission\_pct IS NOT NULL

ORDER BY last\_name;

DECODE(字段|表达式, 条件1,结果1\[,条件2,结果2…，\]\[,缺省值\])  
SELECT last\_name, commission\_pct, decode(commission\_pct, 0.1,'低', 0.2,'中', 0.3 , '高', '无') Commission FROM employees WHERE commission\_pct IS NOT NULL ORDER BY last\_name;

9嵌套函数

单行函数可以嵌套于任何层。  
嵌套的函数是从最里层向最外层的顺序计算的。

SELECT employee\_id,manager\_id , NVL2(TO\_CHAR(manager\_id),to\_char(manager\_id),'No Manager') FROM employees

[Center]: /images/20220824/54403bae6ca04f5c81c99ea0f1f2af3c.png