【OracleDB】 05 单行函数 Single-Row Functions

单行函数　　Single-Row Functions

特点：

-　1、操作数据对象

-　2、接受参数返回一个结果

-　3、只对一行进行变换

-　4、每行返回一个结果

-　5、可以转换数据类型

-　6、可以嵌套

-　7、参数可以是一列或一个值

语法公式：

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

主要分类：

-　1、通用型

-　2、字符处理

-　3、数值运算

-　4、类型转换

-　5、日期计算

字符函数　Character Function

-　1、大小写控制

　　全小写　LOWER

　　全大写　　UPPER

　　单词首字符大写　INITCAP

-　2、其他控制

　　拼接　CONCAT

　　截取　SUBSTR

　　求个数　LENGTH

　　求下标　INSTR

　　左填充 & 右填充　LPAD & RPAD

　　去除　TRIM

　　替换　REPLACE

大小写控制：

LOWER('SQL Course')　　sql course 

UPPER('SQL Course')　　SQL COURSE

INITCAP('SQL Course')　　Sql Course

查询员工 Higgins的信息:

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) = 'higgins';

其他控制函数：

拼接参数列表中的字符串

建议字符都使用单引号修饰，以便和字段与别名区分

CONCAT('Hello', 'World')    HelloWorld

截取字符串

-　第一参数：字符串

-　第二参数：截取的起始位置

-　第三参数：截取的结束位置

注意起始从1开始，包括截至的参数

SUBSTR('HelloWorld',1,5)    Hello

获取字符个数

返回数值

LENGTH('HelloWorld')    10

查找字符第一次出现的下标位置

-　第一参数：字符串

-　第二参数：要查找的字符

INSTR('HelloWorld', 'W')　　6

从左边填充字符个数

-　第一参数：字符串

-　第二参数：总字符的个数

-　第三参数：填充使用的字符

LPAD(salary,10,'*')　　*****24000

反之亦可

RPAD(salary, 10, '*')    24000*****

从字符串中移除

【不知道。。。】

TRIM('H' FROM 'HelloWorld')    elloWorld

替换字符

-　第一参数：字符串

-　第二参数：要替换的原始字符

-　第三参数：替换使用的新字符

REPLACE(‘abcd’,’b’,’m’)    amcd

综合使用案例：

SELECT employee_id, CONCAT(first_name, last_name) NAME, 
       job_id, LENGTH (last_name), 
       INSTR(last_name, 'a') "Contains 'a'?"
FROM   employees
WHERE  SUBSTR(job_id, 4) = 'REP';

数字函数　　Math Function

-　四舍五入　ROUND

-　小数截断位　TRUNC

-　求余　MOD

ROUND函数

第一参数：

　　要四舍五入的数值

第二参数：

　　保留的位置

　　整数表示小数位数，0表示到个位数，-1表示到十位数，以此类推

从伪表中查询

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

【45.92　46　50】

关于【伪表】的一个概念知识，这在MySQL中是从未存在的

https://www.cnblogs.com/curedfisher/p/11534009.html

TRUNC函数

第一参数：

　　要四舍五入的数值

第二参数：

　　截取的位数

表示截断，第三个数值取值为-2，表示从十位数开始截取，

百位数没有数值，截取的结果为0

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

MOD函数

第一参数：

　　被取模的数【被求余数】

第二参数：

　　取模数【求余数】

求余取模

SELECT last_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';

日期　DATE

Oracle 中的日期型数据实际含有两个值:

日期和时间

SELECT last_name, hire_date
FROM   employees
WHERE  last_name like 'G%';

函数SYSDATE 返回：

SELECT sysdate FROM dual;

日期的运算处理

-　1、在日期上加上或减去一个数字结果仍为日期。

-　2、两个日期相减返回日期之间相差的天数。

　　日期不允许做加法运算，无意义

-　3、可以用数字除24来向日期中加上或减去天数。

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE  department_id = 90;

日期函数　Date Function

求两个日期的月份差

MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')　　19.6774194

在参数日期的基础上推移月份

ADD_MONTHS ('11-JAN-94',6)    11-JUL-94

指定日期的下一个星期几对应的日期

NEXT_DAY ('01-SEP-95','FRIDAY')    '08-SEP-95'

本月的最后一天

LAST_DAY('01-FEB-95')    '28-FEB-95'

日期也可以被四舍五入和截断

这是假定的系统时间

Assume SYSDATE = '25-JUL-95':

按月份ROUND

ROUND(SYSDATE,'MONTH')         01-AUG-95

按年份ROUND

ROUND(SYSDATE ,'YEAR')         01-JAN-96

按月份截断

TRUNC(SYSDATE ,'MONTH')        01-JUL-95

按年份截断

TRUNC(SYSDATE ,'YEAR')         01-JAN-95

转换函数　TransForm Function

是对数据类型的转换

分为【显式转换】&【隐式转换】

隐式转换又可称为自动转换

Oracle会自动完成下面的类型转换：

-　VARCHAR2　OR　CHAR　->　NUMBER

-　VARCHAR2　OR　CHAR　->　DATE

-　NUMBER　->　VARCHAR2

-　DATE　->　VARCHAR2

显示转换需要声明转换，明显的表示数据类型的变化

CHARACTER　&　DATE　之间的转换：

【简称 C & D】

C　->　D　TO_DATE

D　->　C　TO_CHAR

CHARACTER　&　NUMBER　之间的转换：

【简称 C & N】

C　->　N　TO_NUMBER

N　->　C　TO_CHAR

TO_CHAR函数对日期的转换

语法公式

TO_CHAR(date, 'format_model')

格式：

　　1、必须包含在单引号中而且大小写敏感。

　　2、可以包含任意的有效的日期格式。

　　3、日期之间用逗号隔开。

SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;

日期格式的元素：

年份元素

　　-　表示数字类型的年　YYYY　2020

　　-　表示英文单词的年　YEAR　Two Thousand And Twenty

月份元素

　　-　表示数字类型的月　MM　05

　　-　表示单词类型的月　MONTH　March

　　-　表示单词缩写的月　MON　Mar

日份元素

　　-　表示数字类型的日【按月份】　DD　14

　　-　表示单词类型的日【按周期】　DAY　Thursday

　　-　表示单词缩写的日【按周期】　DY　Thur

时间元素　　HH24 : MI : SS AM

　　-　表示24小时制的时　HH24　21

　　-　表示60分钟的分　MI　33

　　-　表示60秒的秒　SS　45

在日期格式中添加字符处理

DD  "of"  MONTH  ->  14 of  March

演示案例1：

SELECT last_name,
       TO_CHAR(hire_date, 'DD Month YYYY')
       AS HIREDATE
FROM   employees;

演示案例2：

select employee_id,last_name,hire_date
from employees
where to_char(hire_date,’yyyy-mm-dd’) = ‘1987-09-17’

TO_DATE 函数对字符的转换

语法公式：

TO_DATE(char[, 'format_model'])

演示案例：

TO_DATE(‘2012年10月29日 08:10:21’,’yyyy“年”mm”月”dd“日”hh:mi:ss’)
From dual

练习：

返回hire_date 为 ****/**/**的员工信息，使用显示日期表达

TO_CHAR函数对数字的转换

语法公式：

TO_CHAR(number, 'format_model')

数字格式？

-　9　数字

-　0　零

-　$　美元符

-　L　本地货币符号

-　.　小数点

-　，千分位符

按此格式查询名字叫Ernst员工的工资

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

TO_NUMBER 函数对字符的转换

语法公式：

TO_NUMBER(char[, 'format_model'])

按指定的参数格式显示这个字符

TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’)
from dual

通用函数　General Function

适用于任何数据类型，同时也适用于空值：

语法公式：

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

COALESCE (expr1, expr2, ..., exprn)

NVL 函数

将空值转换成一个已知的值：

-　可以使用的数据类型有日期、字符、数字。

-　函数的一般形式：

NVL(commission_pct,0)

NVL(hire_date,'01-JAN-97')

NVL(job_id,'No Job Yet')

练习

求公司员工的年薪（含commission_pct) 

输出last_name,department_id，当department_id为null时，显示‘没有部门’。

SELECT 
　　last_name, 
　　salary, 
　　NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM 
　　employees;

NVL2 函数

语法公式：

expr1不为NULL，返回expr2；为NULL，返回expr3
NVL2 (expr1, expr2, expr3)

案例

SELECT 
　　last_name,  
　　salary, 
　　commission_pct,
    NVL2(commission_pct,'SAL+COMM', 'SAL') income
FROM   
　　employees 
WHERE 
　　department_id IN (50, 80);

练习：

查询员工的奖金率，若为空，返回0.01, 若不为空，返回实际奖金率+0.015

NULLIF 函数

语法公式：

NULLIF (expr1, expr2) :  相等返回NULL，不等返回expr1

案例：

SELECT 
　　first_name, 
　　LENGTH(first_name) "expr1", 
　　last_name,  
　　LENGTH(last_name)  "expr2",
　　NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   
    employees;

COALESCE 函数

-　COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

-　如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

案例

SELECT   
    last_name,
    COALESCE(commission_pct, salary, 10) comm
FROM     
    employees
ORDER BY 
    commission_pct;

条件表达式　Condition Expression

在 SQL 语句中使用IF-THEN-ELSE 逻辑

使用两种方法:

-　CASE 表达式

-　DECODE 函数

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

练习：

查询部门号为 10, 20, 30 的员工信息, 

若部门号为 10,则打印其工资的 1.1 倍, 

若部门号为 20 号部门,则打印其工资的 1.2 倍, 

若部门号为 30 号部门,打印其工资的 1.3 倍数

DECODE 函数

DECODE(
    col|expression, 
    search1, 
    result1 ,
    [, search2, result2,...,]
    [, default]
)

案例：

SELECT 
    last_name, 
    job_id, salary,
    DECODE(
        job_id, 
        'IT_PROG', 1.10*salary,
        'ST_CLERK', 1.15*salary,
        'SA_REP', 1.20*salary,
        salary
        )    REVISED_SALARY
FROM   
    employees;

函数嵌套　Function Nesting

-　单行函数可以嵌套。

-　嵌套函数的执行顺序是由内到外。

语法公式：

F3(F2(F1(col,arg1),arg2),arg3)

案例：

SELECT 
    last_name,
    NVL(
        TO_CHAR(manager_id), 'No Manager'
    )
FROM   
    employees
WHERE  
    manager_id IS NULL;

posted @ 2020-05-14 22:28 emdzz 阅读(459) 评论(0) 收藏举报

刷新页面返回顶部

Silent1376

Emancipate the mind,Seek truth from the facts.

【OracleDB】 05 单行函数 Single-Row Functions

单行函数　　Single-Row Functions

字符函数　Character Function

大小写控制：

其他控制函数：

综合使用案例：

数字函数　　Math Function

日期　DATE

日期的运算处理

日期函数　Date Function

转换函数　TransForm Function

TO_CHAR函数对日期的转换

日期格式的元素：

在日期格式中添加字符处理

TO_DATE 函数对字符的转换

TO_CHAR函数对数字的转换

TO_NUMBER 函数对字符的转换

通用函数　General Function

NVL 函数

NVL2 函数

NULLIF 函数

COALESCE 函数

条件表达式　Condition Expression

CASE 表达式

DECODE 函数

函数嵌套　Function Nesting

公告

Silent1376

Emancipate the mind,Seek truth from the facts.

【OracleDB】 05 单行函数 Single-Row Functions

单行函数 Single-Row Functions

字符函数 Character Function

大小写控制：

其他控制函数：

综合使用案例：

数字函数 Math Function

日期 DATE

日期的运算处理

日期函数 Date Function

转换函数 TransForm Function

TO_CHAR函数对日期的转换

日期格式的元素：

在日期格式中添加字符处理

TO_DATE 函数对字符的转换

TO_CHAR函数对数字的转换

TO_NUMBER 函数对字符的转换

通用函数 General Function

NVL 函数

NVL2 函数

NULLIF 函数

COALESCE 函数

条件表达式 Condition Expression

CASE 表达式

DECODE 函数

函数嵌套 Function Nesting

公告

单行函数　　Single-Row Functions

字符函数　Character Function

数字函数　　Math Function

日期　DATE

日期函数　Date Function

转换函数　TransForm Function

通用函数　General Function

条件表达式　Condition Expression

函数嵌套　Function Nesting