Oracel:常用函数：单行函数、多行函数（组函数）

文章目录

一、单行函数

1、字符函数

（1）大小写控制函数

（2）字符控制

（3）ASCII码函数

2、数值函数

3、日期函数

4、转换函数

（1）隐形转换

（2）显性转换

5、通用函数

（1）空值转换函数

（2）字符比较函数

二、多行函数

---

一、单行函数

单行函数是指每一行数据执行操作后都会返回一行数据

单行函数可以进行嵌套，嵌套函数的顺序是由内到外

单行函数分为5类：字符、数值、日期、转换、通用函数

1、字符函数

（1）大小写控制函数

lower('str'):大写转小写

select lower('ORACLE') from dual;--oracle

upper('str')：小写转大写

select upper('oracle') from dual;--ORACLE

initcap('str'):字符串首字母大写，其他全部小写

select initcap('oraCLE') from dual;--Oracle

（2）字符控制

concat('str1,'str2')：字符串连接

select concat('oracle','study') from dual;--oraclestudy

substr('str',start,n）：对字符str从位置start开始，往后截取n个字符 （字符串str字母下标从1开始）

select substr('oracle',2,4) from dual;--racl

length('str')：获取字符串长度

select length('oracle') from dual;--6

instr('str','value')：查找该字母在字符串首次出现的位置（字符串str字母下标从1开始）

instr('str','value',start,n)：指定从start位置开始查找字符value出现第n次的位置（字符串str字母下标从1开始）

select instr('hellooracle','o') from dual;--5
select instr('hellooracle','o',3,2) from dual;--6

lpad('str',num,'value')：左填充,当字符str的长度小于num，则以'value'来填充字符左边缺失的位置（字符串str字母下标从1开始）

select lpad('oracle',8,'$') from dual;--$$oracle

rpad('str',num,'value')：右填充,当字符str的长度小于num，则以'value'来填充字符右边缺失的位置（字符串str字母下标从1开始）

select rpad('oracle',8,'$') from dual;--oracle$$

trim([leading/trailing/both] 'value' from 'str')：剔除字符串左/右/两边字符value（/空格）

trim('str'):不指明剔除方式，只能剔除字符串两边的空格

 参数value只能是一个字符

 leading:从字符串左边开始剔除字符value

trailing:从字符串右边开始剔除字符value

both:从字符串两边开始同时剔除字符value

select trim(leading 'h' from 'hhoraclehh') from dual;--oraclehh
select trim(trailing 'h' from 'hhoraclehh') from dual;--hhoracle
select trim(both 'h' from 'hhoraclehh') from dual;--oracle
select trim('   hhoraclehh') from dual;--hhoraclehh

replace('str','value1','value2')：将字符穿中所有字符value1均替换为value2

select replace('hhoraclehh','hh','hi') from dual;--hioraclehi

（3）ASCII码函数

ascii(value)：返回一个字符的ASCII码，参数str只能是一个字符

select ascii('d') from dual;--100

chr(num)：返回给出ASCII码值所对应的字符，参数num表示一个ASCII码值

select chr(100) from dual;--d

2、数值函数

round(num1,num2)：四舍五入（保留num2位小数,不给定num2时默认不保留小数位）

select round(123.123,2) from dual;--123.12
select round(123.523) from dual;--124

trunc(num1,num2)：小数截断（保留num2位小数，不进行四舍五入）

select trunc(123.126123,2) from dual;--123.12

mod(num1,,num2)： 求余

select mod(13,6) from dual;--1

abs(num):返回num的绝对值

select abs(-100) from dual;--100

ceil(num)：返回大于或等于num的最小整数

select ceil(7.8),ceil(8) from dual;--8    8

floor(num)：返回小于或等于num的最大整数

select floor(7.8),floor(8) from dual;--7    8

power(num1,num2)：返回num1的num2次方

select power(2,3) from dual;--8

sign(num)：若num为正数，返回1；若为负数，返回-1；若为0，返回0

select sign(-2),sign(2),sign(0) from dual;--    -1    1    0

sqrt(num)：返回num的平方根

select sqrt(4) from dual;--2

3、日期函数

两个日期相减，返回两个日期之间相差的天数（2个日期之间不允许用加法）

日期相关字符含义：

yyyy：年（如2024）

            yyy：年份的最后三位 ，如024   

            yy：年份的最后两位，如24    

            y：年份的最后一位，如4   

mm：月（01~12）

            month：九个字符表示的月份，右边缺少部分用空格填充，如：‘1月 ’

            mon：三位字符表示的月份，右边缺少部分用空格填充，如‘1月 ’ 

dd：日（01~31）

            d：星期中的第几天，默认周日为一周的第一天（1~7）   

            ddd： 一年中的第几天（001~365）

            day：九个字符表示的天， 右边缺少部分用空格填充，如‘星期三’

hh/HH12：小时，12进制表示（01~12）

            hh24：小时（01~24）    

mi：分钟    

ss：秒（一分钟中的秒数）

            sssss：从午夜12点开始，直至现在的秒数

w：该月中的第几个星期

            ww：该年中的第几个星期

q：季度（1~4）

日期相关变量含义：

sysdate：当前系统时间，精确到秒

current_date：当前系统日期，精确到秒

systimestamp:：当前系统时间，包含时区信息，精确到微秒

dbtimezone：返回数据库时区

select sysdate from dual;--2024/1/17 19:35:53
select current_date from dual;--2024/1/17 19:36:19
select systimestamp from dual;--17-1月 -24 07.37.09.588000 下午 +08:00
select dbtimezone from dual;--+00:00

具体函数：

months_between(date1, date2)：返回date1与date2之间相差几个月（差值计算是用date1-date2）

select months_between(to_date('2024-01-01','yyyy-mm-dd'),to_date('2023-09-01','yyyy-mm-dd')) from dual;--4

add_months(date,num)：返回在当前日期上加num个月

select add_months(to_date('2024-01-01','yyyy-mm-dd'),4) from dual;--2024/5/1

next_day(date,'星期几')：返回在当前日期的基础上，下一个星期几对应日期

select next_day(to_date('2024-01-01','yyyy-mm-dd'),'星期一') from dual;--2024/1/8

last_day(date)：返回本月最后一天

select last_day(to_date('2024-01-01','yyyy-mm-dd')) from dual;--2024/1/31

round(date,'mm')：日期按月进行四舍五入，返回四舍五入后该月第一天

round(date,'yyyy')：日期按年进行四舍五入，返回四舍五入后该年第一个月第一天

select round(to_date('2024-01-17','yyyy-mm-dd'),'mm') from dual;--2024/2/1
select round(to_date('2024-01-17','yyyy-mm-dd'),'yyyy') from dual;--2024/1/1

trunc(date,'yyyy')：返回当年第一天

trunc(date,'mm')：返回当月第一天

trunc(date,['dd'])：返回日期date

trunc(date,'d')：返回当前日期所在星期的第一天（默认周日为一周的第一天）

trunc(sysdate,'hh')：返回当前日期和时间，时间具体到小时

trunc(sysdate,'mi')：返回当前日期和时间，时间具体到分钟

select trunc(to_date('2024-01-17','yyyy-mm-dd'),'yyyy') from dual;--2024/1/1
select trunc(to_date('2024-01-17','yyyy-mm-dd'),'dd') from dual;--2024/1/17
select trunc(to_date('2024-01-17','yyyy-mm-dd'),'d') from dual;--2024/1/14
select trunc(sysdate,'hh') from dual;--2024/1/17 16:00:00
select trunc(sysdate,'mi') from dual;--2024/1/17 16:12:00

4、转换函数

（1）隐形转换

date<—>varchar2<—>number（若字符串中没有特殊的字符，oracle可以自动完成）

/*varchar2和number类型之间转换*/
select '12'+4 from dual;--16    
/*date和number类型之间转换*/
select to_date('2024-01-17','yyyy-mm-dd')+2 from dual;--2024/1/19  
/*date和varchar2类型之间转换*/  
select to_date('2024-01-17','yyyy-mm-dd')+'2' from dual;--2024/1/19   

（2）显性转换

（2.1）to_char

作用1：用于将字段转换为字符串

select to_char(999) from dual;--999

作用2：用作日期转换：to_char(date,'日期格式')

常用日期格式：yyyy-mm-dd，yyyy/mm/dd

                         yyyy"年"mm"月"dd"日" ，mm"月"dd"日"yyyy"年"

                         YYYY-MM-DD HH24:MI:SS

select to_char(sysdate,'yyyy-mm-dd') from dual;--2024-01-17
select to_char(sysdate,'yyyy/mm/dd') from dual;--2024/01/17
select to_char(sysdate,'yyyy"年"mm"月"dd"日"' ) from dual;--2024年01月17日

作用3：用作数据处理：to_char(num,'格式')

格式：

，：千分位，可以作为分组符号使用，根据需要也可以当百分位、十分位使用，根据两个，

        之间间隔的数字个数而定

 .：小数点，只能出现在小数点对应的位置，且只能出现一次

$：美元符，可以出现在任意位置

0：零，每一个位置返回对应的字符，若没有则用0填充

9：数字，在小数位表示转换为对应字符，没有则用0表示；在整数位，没有则不填充字符，

      为空

L：人民币，可以放在最前面或者最后面   

/*，：千/百/十/分位*/
select to_char(123456789,'999,999,999') from dual;-- 123,456,789 
select to_char(12345,'99,99,99') from dual;--  1,23,45
select to_char(12345,'9,9,9,9,9') from dual;-- 1,2,3,4,5
/*.：小数点*/
select to_char(1234,'9999.9') from dual;;--  1234.0
/*$：美元符*/
select to_char(1234,'9999.$9') from dual;-- $1234.0
/*0：零*/
select to_char(1234,'09999.99') from dual;--01234.00
/*9：数字*/
select to_char(1234,'9999.99') from dual;-- 1234.00
/*L：人民币*/
select to_char(1234,'9999.99L') from dual;--  1234.00￥

作用4：可以进行进制转换，10进制转换为16进制

数值必须是大于等于0的整数，前面只能是0或者FM组合使用

select to_char(123,'xx') from dual;-- 7b

（2.2）to_number

作用1：将varchar类型转换为number类型

select to_number('123456') from dual;--123456
select to_number('123,456.89','999,999.99') from dual;--123456.89

作用2：可用来实现进制转换，16进制转换为10进制

select to_number('17f','xxx') from dual;--383
select to_number('f','x') from dual;--15

（2.3）to_date

            可以用作日期转换：to_date('date','格式')

常用格式：yyyy-mm-dd，yyyy/mm/dd

                  yyyy"年"mm"月"dd"日" ，mm"月"dd"日"yyyy"年"

                  yyyy-mm-dd hh24:mi:ss，yyyy-mm-dd hh:mi:ss

select to_date('2022-06-10','yyyy-mm-dd') from dual;--2022/6/10
select to_date('2022-06-10 16:23:54','yyyy-mm-dd hh24:mi:ss') from dual;--2022/6/10 16:23:54

5、通用函数

可用于任何数据类型，也适用于空值

（1）空值转换函数

nvl(str1,str2)：将空值转换为一个已知的值，可以使用的数据类型有日期、字符、数字

select t.empno,t.ename,t.comm,nvl(t.comm,0) comm_1 from emp t;

nvl2(str1,str2,str3)：当str 1不为null，返回str 2；为null，则返回str 3

select t.empno,t.ename,t.comm,nvl2(t.comm,'非空','空') comm_1 from emp  t;

coalesce(expr1,expr2,...,exprn)：返回所有表达式中第一个非空的表达式，若expr1为空，返回expr2的值，以此类推，若所有表达式均为空返回null

--原表数据
select t.empno,t.ename,t.comm,t.mgr,t.sal from emp t
where t.empno in ('7369','7566','7788','7839');

--若员工comm为空，则显示他的mgr；若两者都为空，则显示sal；若三者都为空，则显示空值
select t.empno,t.ename,coalesce(t.comm,t.mgr,t.sal) from emp t
where t.empno in ('7369','7566','7788','7839');

--若员工comm为空，则显示他的mgr；若两者都为空，则显示空值
select t.empno,t.ename,coalesce(t.comm,t.mgr) from emp t
where t.empno in ('7369','7566','7788','7839');

（2）字符比较函数

nullif(str1,str2)：相等返回null,不等返回str1

select nullif(1,2),nullif(2,2) from dual;

二、多行函数

多行函数是指多行数据执行完操作返回一行数据，也称为分组函数或聚合函数

对avg、sum、min、max、count、stddev、variance这几个函数，null不参与其运算

avg(str)：求平均值

select avg(t.comm) from emp t;--550

sum(str)：求和

select t.empno,t.ename,sum(t.comm) from emp t;--2200

min(str)：取最小值

select min(t.comm) from emp t;--0

max(str)：取最大值

select max(t.comm) from emp t;--1400

count(str)：统计数据记录数

select count(t.empno) from emp t;--14

stddev( [ distinct | all ] column )：统计数据标准差，（distinct表示只统计不重复出现的数据， all表示统计满足条件的所有数据，不指定时默认是all）

select stddev(grade),stddev(all grade),stddev(distinct grade)
from student_score
where subject = '数学';--17.6974574445032  17.6974574445032  19.55334583475

variance( [ distinct | all ] column ):统计数据方差（distinct表示只统计不重复出现的数据， all表示统计满足条件的所有数据，不指定时默认是all）

select variance(grade),stddev(all grade),stddev(distinct grade)
from student_score
where subject = '数学';--313.2 17.6974574445032  19.55334583475

group by、order by、having一般会结合组函数一起使用

group by str1,str2……：按字段str1和str2进行分组（str1，str2值均相同的分为一组）

select t.deptno, t.empno, max(t.sal)
  from emp t
 where t.empno in ('7654', '7566', '7839', '7788', '7782')
 group by t.deptno, t.empno;

order by str [desc/asc]：按字段str排序，默认是asc升序

select t.deptno, t.empno, max(t.sal)
  from emp t
 where t.empno in ('7654', '7566', '7839', '7788', '7782')
 group by t.deptno, t.empno
 order by t.deptno desc, t.empno desc;

having 条件：对分组后的数据进行筛选

where与having的区别：where是对数据行的筛选，having是对分组后的数据进行筛选

select t.deptno,t.empno,max(t.sal) from emp t
where t.empno in ('7654','7566','7839','7788','7782')
group by t.deptno,t.empno
having t.deptno = '10'
order by t.deptno desc,t.empno desc
;