Oracle内置函数

dual表

该表是Oracle中真实存在的一个表，任何用户都可以读取，多数情况下可以用在没有目标的SELECT查询语句中。它本身只包含了一个DUMMY字段。DUAL表对Oracle很重要，用户不要试图删除该表，一旦删除，Oracle将无法启动。

聚合函数

Oracle聚合函数同时可以对多行数据进行操作，并返回一个结果。比如经常用来计算一些分组数据的总和和平均值之类，常用函数有AVG()、SUM()、MIN()、MAX()等等。

MAX/MIN函数

MAX([distinct] expr)、MIN([distinct] expr)函数可以返回指定列或列组成的表达式expr中的最大值或最小值。该函数也通常和where条件、group by分组结合在一起使用。

SUM函数

SUM([distinct] expr)函数可以对指定列或列组成的表达式expr进行求和，假如不使用分组group by ,那就是按照整表作为一个分组。

AVG函数

AVG([distinct ] expr)：该函数可以求列或列组成的表达式expr的平均值，返回的是数值类型。其中 distinct是可选参数，表示是否去掉重复行。

COUNT函数

count(*|[distinct]expr)函数可以用来计算查询结果的条数或行数。函数中必须指定列名或者表达式expr，不然就要全选使用*号。

关于count(1)，count(*),和count(列名)的区别

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL；
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL；
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空；

数值型函数

绝对值、取余、判断数值正负函数

ABS(n)函数：用于返回绝对值

SELECT ABS('100'),ABS(-50) FROM DUAL;
--OUTPUT:100, 50

MOD(n2, n1)函数：返回n2除以n1的余数

参数为任意数值或可以隐式转成数值的类型。如果n1为0，那么该函数将返回n2。

SELECT MOD(5,2), MOD(8/2,5),MOD('11',5),MOD(-10,6),MOD(3,0) FROM DUAL;
--OUTPUT:1, 4, 1, -4, 3

SIGN(n)函数：返回参数n的符号

正数返回1，0返回0，负数返回-1。但如果n为BINARY_FLOAT或BINARY_DOUBLE类型时，n>=0或者n=NaN函数会返回1。

SELECT SIGN('9'),SIGN(-9),SIGN(0.00),SIGN(-2*'9') FROM DUAL;
--OUTPUT:1, -1, 0, -1

三角函数

COS(n)函数：用于返回参数n的余弦，n为弧度表示的角度。

SELECT COS(3.1415926),COS('3.1416926') FROM DUAL;

其它三角函数

ACOS(n)：返回n的反余弦值。

COSH(n)：返回n的双曲余弦值。

SIN(n)：返回n的正弦值。

SINH(n)：返回n的双曲正弦值。

ASIN(n)：返回n的反正弦值。

TAN(n)：返回n的正切值。

TANH(n)：返回n的双曲正切值。

ATAN(n)：返回n的反正切值。

返回以指定数值为准整数的函数

CEIL(n)函数：返回大于等于输入参数的最小整数

该输入参数要求是十进制数值类型，或可以隐式地转换成数值的类型，可以是非整数。

SELECT CEIL(10),CEIL('10.5'),CEIL(-10.2) FROM DUAL;
-- OUTPUT:10, 11, -10

FLOOR(n)函数：返回小于或等于参数的最大整数

该函数输入参数要求是十进制数值类型，或可以隐式地转换成数值的类型，可以是非整数。

SELECT FLOOR(10),FLOOR('10.5'),FLOOR(-10.2) FROM DUAL;
--OUTPUT:10, 10, -11

指数、对数函数

POWER(n2,n1)函数：利用该函数可以得到n2的n1次幂的结果

这两个参数为任意数值，但如果n2为负数，那么n1必须为整数。

SELECT POWER(5,2),POWER('5',2),POWER(5.5,2.5),POWER(-5,2), 5 * 5 FROM DUAL;

类似函数：EXP(n)函数，表示返回e的n次幂

SQRT(n)函数：该函数返回n的平方根

n为数字类型的时候不能为负数，将返回一个实数，当n为BINARY_FLOAT或BINARY_DOUBLE类型时，n<0将返回Nan。

SELECT SQRT(100),SQRT('64') FROM DUAL;
--OUTPUT:10, 8

LOG(n1,n2)函数：返回以n1为底n2的对数

n1是除1和0以外的任意正数，n2为正数。

SELECT LOG(10,100),LOG('10','1000'),LN(10) FROM DUAL;

类似函数：LN(n)函数，表示返回n的自然对数。

四舍五入截取函数

ROUND(n,integer)函数：将数值n四舍五入成第二个参数指定的形式的十进制数

参数integer要求是整数，如果不是整数，那么它将被自动截取为整数部分。当integer为正整数时，表示n被四舍五入为integer位小数。如果该参数为负数，则n被四舍五入至小数点向左integer位。

SELECT ROUND(3.1415926,3), ROUND(3.1416926,2.5),ROUND(13.1415926,-1) FROM DUAL;
--OUTPUT:3.142, 3.14, 10

TRUNC(n,integer)函数：它把数值n根据integer的值进行截取

SELECT TRUNC(3.1415926), TRUNC(3.1415926,4), TRUNC(3.1415926,2.5),TRUNC(13.1415926,-1) FROM DUAL;
--OUTPUT:3, 3.1415, 3.14, 10

字符串处理函数

字符集函数

CHR(n[USING NCHAR_CS])函数：根据相应的字符集，把给定的ASCII码转换为字符

USING NCHAR_CS指明字符集。

SELECT CHR(65) || CHR(67) ABC,CHR(54678) FROM DUAL;
--OUTPUT:AC, $

ASCII(str)函数：返回参数首字母的ASCII码值

该返回值总是以用户使用的字符集为基础的，如果用户的数据库字符集是7位的ASCII值，那就得到一ASCII码值。

SELECT ASCII('中'),ASCII('HELLO') FROM DUAL;
--OUTPUT:14989485, 72

获取字符串长度函数

{[LENGTH] | [LENGTHB] | [LENGTHC] | [LENGTH2] | [LENGTH4]} (str)函数：返回指定字符串的长度

SELECT LENGTH('HELLO WORLD') FROM DUAL;
--OUTPUT:11

字符串截取函数

{[SUBSTR] | [SUBSTRB] | [SUBSTRC] | [SUBSTR2] | [SUBSTR4]} (str,position[,substring_length])函数：该函数提供截取字符串的功能

各参数表示含义如下：

SUBSTR：以字符为单位。

SUBSTRB：以字节为单位。

SUBSTRC：以unicode字符为单位。

SUBSTR2：以UCS2代码点为单位。

SUBSTR4：以UCS4代码点为单位。

str：原始字符串。

position：要截取字符串的开始位置。初始为1，如果该值为负数，则表示从str的右边算起。

substring_length：截取的长度。

SELECT SUBSTR('HELLO WORLD',3,4),SUBSTR('HELLO WORLD',-3,4),SUBSTRB('ABCDEFG',5,4.2) FROM DUAL;
--OUTPUT:LLO, RLD, EFG

字符串连接函数

CONCAT(str1,str2)函数：该函数连接两个参数并返回，效果和连接符“||”相似

SELECT CONCAT('HELLO',' WORLD') FROM DUAL;
--OUTPUT:HELLO WORLD

字符串搜索函数

{[INSTR] | [INSTRB] | [INSTRC] | [INSTR2] | [INSTR4]} (string, substring[,position[,occurrence]])函数：该函数可以让我们在指定的字符串中搜索是否存在另一个字符串

各项参数表示含义如下：

INSTR：以字符为单位。

INSTRB：以字节为单位。

INSTRC：以unicode字符为单位。

INSTR2：以UCS2代码点为单位。

INSTR4：以UCS4代码点为单位。

string：待搜索的字符串。

substring：要搜索的字符串。

position：搜索的开始位置，默认为1，表示字符串左边第一个位置；如果为负数，则表示字符串的右边位置为起始位置。

occurrence：substring第几次出现，默认是1。

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2),INSTRB('CORPORATE FLOOR','OR',5,2) FROM DUAL;
--OUTPUT:2, 14

字母大小写转换函数

UPPER(str)函数：该函数将指定的参数全部转换成大写字母

SELECT UPPER('hello world') FROM DUAL;
--OUTPUT:HELLO WORLD

LOWER(str)函数：该函数将指定的参数全部转换成小写字母

SELECT LOWER('HELLO WORLD') FROM DUAL;
--OUTPUT:hello world

INITCAP(str)函数：该函数参数的所有单词首字母转换成大写字母

SELECT INITCAP('hello world') FROM DUAL;
--OUTPUT:Hello World

带排序参数的字母大小写转换函数

NLS_INITCAP(str[,nlsparam])函数：将指定参数的第一个字母转换成大写

nlsparam参数为可选参数，其设置可以到NLS_DATABASE_PARAMETERS表中查询。

SELECT NLS_INITCAP('hello world'),NLS_INITCAP('this is my cat','NLS_SORT=SCHINESE_STROKE_M') FROM DUAL;
--OUTPUT:Hello World, This Is My Cat

NLS_UPPER(str[,nlsparam])函数：将指定参数变成大写

nlsparam参数同NLS_INITCAP函数设置

SELECT NLS_UPPER('this is my cat','NLS_SORT=SCHINESE_PINYIN_M') FROM DUAL;
--OUTPUT:THIS IS MY CAT

NLS_LOWER(str[,nlsparam])函数：将指定参数转换成小写

nlsparam参数同NLS_INITCAP函数设置

SELECT NLS_LOWER('HELLO CHINA','NLS_SORT=XGerman') FROM DUAL;
--OUTPUT:hello china

替换字符串函数

REPLACE(str,search_string[,replacement_string])函数：替换字符串的函数

具体代表的含义如下：

str：表示搜索的目标字符串。

search_string：在目标字符串中要搜索的字符串。

replacement_string：该参数可选，用它可替代被搜索到的字符串，如果该参数不用，则表示从str参数中删除search_string字符串。

SELECT REPLACE('this is a dog','dog','cat') FROM DUAL;
--OUTPUT:this is a cat

字符串填充函数

RPAD(expr1,n[,expr2])函数：该函数功能是在字符串expr1的右边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填充

SELECT RPAD('--',6,'*') FROM DUAL;
--OUTPUT:--****

LPAD(expr1,n[,expr2])函数：该函数功能是在字符串expr1的左边用字符串expr2填充，直到整个字符串长度为n时为止。如果expr2不存在，则以空格填充

SELECT LPAD('--',6,'*') FROM DUAL;
--OUTPUT:****--

删除字符串首尾指定字符的函数

TRIM( [LEADING | TRAILING | BOTH] [trim_character FROM] trim_source)函数：该函数将删除指定的前缀或尾随的字符，默认删除空格

SELECT TRIM('   HELLO WORLD') FROM DUAL;
--OUTPUT:HELLO WORLD

RTRIM(str[,set])函数：与RPAD函数相反，该函数会提供将str右边出现在set中的字符删除掉。如果set没有，则默认删除空格

SELECT LTRIM('-----HELLO WORLD*****','-') FROM DUAL;
--OUTPUT:HELLO WORLD*****

LTRIM(str[,set])函数：与RTRIM函数相似，该函数会提供将str左边出现在set中的字符删除掉。如果set没有，则默认删除空格

SELECT RTRIM('-----HELLO WORLD*****','*') FROM DUAL;
--OUTPUT:-----HELLO WORLD

日期型函数

系统日期、时间函数

SYSDATE函数：可以得到系统的当前日期

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS') FROM DUAL;
--OUTPUT:2021-08-15 18:08:12

SYSTIMESTAMP函数：返回系统时间，该时间包含时区信息，精确到微秒

SELECT SYSTIMESTAMP FROM DUAL;
--OUTPUT:15-8月 -21 06.52.55.442000000 下午 +08:00

得到数据库时区函数

DBTIMEZONE函数：返回数据库时区

SELECT DBTIMEZONE FROM DUAL;
--OUTPUT:+00:00

为日期加上指定月份函数

ADD_MONTHS(date,integer)函数：该函数将返回在指定的日期上加一个月份数后的日期

其中date表示指定的日期； integer表示要加的月份数，该值如果为负数，则表示减去的月份数。

需要注意，当指定的日期是月的最后一天时，最后函数返回的结果也将是新月的最后一天。而如果新的月份比指定日期月份的天数少，则函数将自动回调有效日期。

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2021-8-15','YYYY-MM-DD'),3),'YYYY-MM-DD') FROM DUAL;
--OUTPUT:2021-11-15

SESSIONTIMEZONE函数：可以返回当前会话的时区

SELECT SESSIONTIMEZONE FROM DUAL;
--OUTPUT:+08:00

返回指定月份最后一天函数

LAST_DAY(date)函数：该函数返回参数指定日期对应月份的最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;
--OUTPUT:2022/6/30 11:22:33

返回会话所在时区当前日期函数

CURRENT_DATE函数：该函数得到会话时区的当前日期

SELECT SESSIONTIMEZONE, TO_CHAR(CURRENT_DATE,'YYYY-MM-DD') FROM DUAL;
--OUTPUT:+08:00, 2021-08-15

提取指定日期特定部分的函数

EXTRACT (datetime)函数：该函数可以从指定的时间当中提取到指定的日期部分

例如从给定的日期得到年、月、分等

SELECT EXTRACT(YEAR FROM SYSDATE),
       EXTRACT(MINUTE FROM TIMESTAMP '2021-08-27 8:30:56'),
       EXTRACT(SECOND FROM TIMESTAMP '2021-08-27 8:30:56') 
  FROM DUAL;
--OUTPUT:2021, 30, 56

得到两个日期之间的月份数

MONTHS_BETWEEN(date1,date2)函数：该函数返回date1和date2之间的月份数

函数两个参数都为日期型数据。当date1>date2时，如果两个参数表示日期是某月中的同一天，或它们都是某月中的最后一天，则该函数返回一整型数；否则，将返回小数。当date1<date2时，则返回一负值。

SELECT MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-8-15','YYYY-MM-DD')) AS ONE,
       MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-10-15','YYYY-MM-DD')) AS TWO,
       MONTHS_BETWEEN(TO_DATE('2021-9-15','YYYY-MM-DD'), TO_DATE('2021-8-21','YYYY-MM-DD')) AS THREE 
  FROM DUAL;
--OUTPUT:1, -1, 0.8064516129032258064516129032258064516129

时区时间转换函数

NEW_TIME(date,timezone1,timezone2)函数：该函数将返回时间date在时区timezone1转换到时区timezone2的时间

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS'), 
       TO_CHAR(NEW_TIME(SYSDATE,'PDT','EST'),'YYYY-MM-DD HH24:MM:SS')
  FROM DUAL;
--OUTPUT:2021-08-15 20:08:29, 2021-08-15 22:08:29

转换函数

日期转字符串

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MM:SS') FROM DUAL;

数值转字符串

select to_char(88877) from dual;
select to_char(1234567890,'099999999999999')  from dual;
select to_char(12345678,'999,999,999,999')  from dual;
select to_char(123456,'99.999')  from dual;
select to_char(1234567890,'999,999,999,999.9999')  from dual;
select to_char(123,'$99,999.9') from dual;

字符转日期

SELECT TO_DATE('2021-08-27','YYYY-MM-DD') FROM DUAL;

字符串转数字

SELECT TO_NUMBER('1000.6562', '9999.9999') FROM DUAL;
--OUTPUT:1000.6562

NULL函数

替换NULL值函数

NVL(expr1, expr2)函数：替换NULL值，表示如果expr1为NULL值，则返回expr2的值，否则返回expr1的值

该函数要求两个参数类型一致，至少相互间能进行隐式的转换，否则会提示出错。

SELECT NVL(NULL,20),NVL(40,20) FROM DUAL;
--OUTPUT:20, 40

NVL2(expr1,expr2,expr3)函数：该函数同NVL类似，不同的是当expr1为NULL时，函数返回expr3的值；当expr1不为空时，则返回expr2的值

SELECT NVL2(NULL,20,40),NVL2(100,20,40) FROM DUAL;
--OUTPUT:40, 20

COALESCE (expression,value1, value2……, valuen)

这个函数主要用来进行空值处理。

如果expression不为空值则返回expression；否则判断value1是否是空值，
如果value1不为空值则返回value1；否则判断value2是否是空值，
如果value2不为空值则返回value2；……以此类推，如果所有的表达式都为空值，则返回NULL。

相对于NVL来说，coalesce支持多个参数可以很方便的返回第一个不为空的值，如果改用nvl，就要嵌套很多层。

其他函数

DECODE表达式匹配函数

DECODE函数，是ORACLE公司的SQL软件ORACLE PL/SQL所提供的特有函数计算方式，以其简洁的运算方式，可控的数据模型和灵活的格式转换而闻名。

语法：

decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值)

含义：

IF条件=值1 THEN
    RETURN(返回值1)
ELSIF条件=值2 THEN
    RETURN(返回值2)
......
ELSIF条件=值n THEN
    RETURN(返回值n)
ELSE
    RETURN(缺省值)
ENDIF

decode(X，A，B，C，D，E)：这个函数运行的结果是，当X = A，函数返回B；当X != A 且 X = C，函数返回D；当X != A 且 X != C，函数返回E。其中，X、A、B、C、D、E都可以是表达式，这个函数使得某些sql语句简单了许多。

wm_concat函数

在日常的数据查询过程中，经常遇到一条信息分多条记录存储，并以同一个ID关联的情况，比如常见的房产证权利人信息，因为共有权人可能有很多，不可能把所有的权利人都放到权利人表的权利人字段，把所有权利人的证件号都放到权利人证件号字段，所以在数据库设计时候，会采用一个权利人一条记录，并以权利ID关联的方式存放。

但是在数据查询时候，有时候又希望将所有权利人信息一起展示，这里可能就会用到Oracle的wm_concat函数。

示例数据：

示例语句：

select qlrid,wm_concat(qlr) as qlr,wm_concat(qlrzjh) as qlrzjh from qlr t group by qlrid;

运行后的返回结果根据oracle的版本不同而会字段类型不同，在oracle11g中返回clob型，在oracle10g中返回varchar型。

通用版本语句：

select qlrid,to_char(wm_concat(qlr)) as qlr,to_char(wm_concat(qlrzjh)) as qlrzjh from qlr t group by qlrid;

注意：在较新版本的Oracle版本中，已不再使用wm_concat()函数，而是使用性能更高的listagg函数。

listagg函数

listagg函数是Oracle11gR2开始正式推出的字符串聚合函数，在Oracle11之前还可以使用WM_CONTACT函数，但是WM_CONTACT函数效率较低，Oracle12已经废弃不能再用。

listagg函数的主要功能是可以根据分组，将多个行的内容聚合为一条记录，并用指定的分隔符连接起来。

注意项：

使用该函数必须的进行分组(GROUP BY)
listagg函数第一个参数表示需要进行枚举的字段，第二个参数表示枚举数据的分隔符
对于枚举的字段同时还需要排序和分组WITHIN GROUP(ORDER BY xx)

SELECT u_id, LISTAGG(goods||'('||num||'斤)', ',') WITHIN GROUP(ORDER BY u_id) AS goods_sum
FROM shopping
GROUP BY u_id;

posted @ 2022-06-08 10:32 残城碎梦阅读(446) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

残城碎梦