oracle单行超2499,Oracle 开发——单行函数

单行函数

本次文章主要学习

-理解函数的作用

-掌握单行函数的五大分类函数

-其中，会一直用到到的虚表

DUAL：是一张虚表，不能保存任何数据，只有一个字段，一行记录。当我们不希望从任何表中读取数据，同时又想利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用DUAL表

SQL函数概述

SQL函数分类主要分为两种

-单行函数

-多行函数

单行函数语法

语法:

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

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

变量

列名

表达式

单行函数特征

单行函数特征

单行函数对单行操作

每行返回一个结果

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

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

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

函数可以嵌套

单行函数分类

分为5大类

字符函数

数值函数

日期函数

转换函数

通用函数

字符函数

字符函数

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

大小写函数

LOWER(列名|表达式)

将大写或大小写混合的字符转换成小写

select lower('ABCDE')

from dual;

UPPER(列名|表达式)

将小写或大小写混合的字符转换成大写

select upper('abcde')

from dual;

INITCAP(列名|表达式)

将每个单词的第一个字母转换成大写，其余的字母都转换成小写

select initcap('oycp oyyyyy')

from dual;

字符处理函数

CONCAT(column1|expression1,column2|expression2)

连接两个值 ,等同于||

select concat(ename,sal)

from emp;

SUBSTR (column|expression,n1[,n2])

返回第一个参数中，从第n1位开始，长度为n2的子串。

如果n2省略,取第n1位开始的所有字符。

如果n1是负值，表示从第一个参数的后面第abs(n1)位开始向右取长度为n2的子串。

select substr('abcdefg',1,3)

from dual;

LENGTH(column | expression)

取字符长度

select length('abcdefg')

from dual;

INSTR(s1,s2,[,n1],[n2])

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

select instr('abcdefg','c')---3

from dual;

LPAD(s1,n1,s2)

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

select lpad('abc',11,'*')

from dual;

RPAD(s1,n1,s2)

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

select rpad('abc',11,'*')

from dual;

TRIM

去除字符串头部或尾部(头尾)的字符格式:TRIM(leading | trailing | both trim_character From trim_source)

select trim('c' from 'abcdefccccccccc')

from dual;


REPLACE(s1,s2,s3)

把s1中的s2用s3替换。

select replace('abcdef','c','p')----abpdef

from dual;

数值函数

ROUND

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

格式: ROUND(column| expression, n)

ROUND(45.926, 2) -> 45.93

select round(12.4846,2)

from dual;

TRUNC

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

格式：TRUNC(column| expression, n)

TRUNC(45.926, 2) -> 45.92

select trunc(12.4846,2)

from dual;

MOD

取m除以n后得到的余数

格式：MOD(m,n)

MOD(1600, 300) ->100

select mod(1600,300)

from dual;

常用日期函数

SYSDATE

返回系统日期

select sysdate

from dual;

MONTHS_BETWEEN

返回两个日期类型数据之间间隔的自然月数

SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months

FROM emp

ORDER BY months;

ADD_MONTHS

返回指定日期加上相应的月数后的日期

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

FROM emp

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

NEXT_DAY

返回某一日期的下一个指定日期

SELECT NEXT_DAY('02-2月-06','星期一') NEXT_DAY

FROM DUAL;

LAST_DAY

返回指定日期当月最后一天的日期

SELECT LAST_DAY('02-2月-06') "LAST DAY"

FROMDUAL;

ROUND(date[,‘fmt’])

将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。

格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS

SELECT empno, hiredate,

ROUND(hiredate, 'MONTH')

FROM emp

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

TRUNC(date[,‘fmt’])

将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。

SELECTempno, hiredate, TRUNC(hiredate, 'MONTH')

FROMemp

WHERESUBSTR(hiredate,-2,2)='81';

EXTRACT

返回日期类型数据中的年份、月份或者日

SELECT ename, hiredate,

EXTRACT (MONTH FROM HIREDATE) MONTH

FROM emp

WHERE deptno= 10;

转换函数

分为两种

隐式数据类型转换

显式数据类型转换

隐式数据类型转换规则

不同的数据类型之间关联，如果不使用显式转换函数，则它会根据以下规则对数据进行隐式转换：

对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型

对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型

当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型

当比较字符型和日期型的数据时，oracle会把字符型转换为日期型

用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型

如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等

显式数据类型转换

通常是在字符类型、日期类型、数值类型之间进行显式转换。主要有3个显式转换函数：

TO_CHAR

TO_NUMBER

TO_DATE

TO_CHAR 用于日期型

日期格式模型：

必须用单引号引起来并且是大小写敏感的

可以包含任何有效的日期元素

使用逗号将日期型数据与日期型格式模型分隔开

SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hired

FROM emp;

日期格式模型的元素

TO_CHAR 用于数值型

通过在TO_CHAR中使用以下形式可以把数值型数据转化成变长的字符串：

SELECTTO_CHAR(sal,'$99,999') SALARY

FROMemp

WHEREename = 'SCOTT';

TO_NUMBER 函数

使用TO_NUMBER将一个字符串转换成数值型数据

TO_NUMBER(char[, 'fmt'])

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

TO_DATE 函数

select to_date('2020/12/30','YYYY/MM/DD')

from dual

通用函数

与空值(NULL)相关的一些函数，完成对空值(NULL)的一些操作。主要包括以下函数：

NVL

语法:NVL (expr1,expr2)

如果expr1不是null,返回expr1,否则返回expr2

NVL2

语法:NVL2(expr1,expr2,expr3)

如果expr1不是null,返回expr2,否则返回expr3

NULLIF

语法: NULLIF(expr1,expr2)

比较两个表达式,如果相等，返回null,否则，返回第一个表达式

COALESCE

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

SELECT ename, COALESCE(comm, 0) comm , deptno

FROM emp;

条件处理函数：

CASE表达式

SELECT ename, deptno,

(CASE deptno

WHEN 10 THEN '销售部'

WHEN 20 THEN '技术部'

WHEN 30 THEN '管理部'

ELSE '无' END)

deptname

FROM emp;

DECODE

SELECT ename, deptno,

decode(deptno,

10,'销售部',

20,'技术部',

30 , '管理部',

'无') deptname

FROM emp;

收工，谢谢

标签：字符,函数,单行,SELECT,dual,Oracle,n1,select

来源： https://blog.csdn.net/qq_45187600/article/details/111996391