数据库篇（Oracle）---常用函数详解

目录

字符型处理函数

数值型处理函数

日期型处理函数

复杂型数据处理函数

---

字符型处理函数

• lpad（）函数 - 向左补全字符串

lpad(string,padded_length,padded_string) 

#string:原始字符串    padd_length:格式化后的字符串长度    paded_string：不足长度后的填充字符

SELECT LPAD('pad', 4, 'l') IPAD_STR FROM DUAL;

IPAD_STR
--------------------------------------------------------------------------------
lpad

• rpad（）函数 - 向右补全字符串

rpad(string,padded_length,padded_string) 

#string:原始字符串    padd_length:格式化后的字符串长度    paded_string：不足长度后的填充字符

SELECT RPAD('rpa', 4, 'd') RPAD_STR FROM DUAL;

RPAD_STR
--------------------------------------------------------------------------------
rpad

• lower（）函数 - 返回字符串的小写形式

lower(string) 

#string:要转换的字符串

SELECT LOWER('LOwER') LOWER FROM DUAL;

LOWER
--------------------------------
lower

• upper（）函数 - 返回字符串的大写形式

upper(string) 

#string:要转换的字符串

SELECT UPPER('uPper') UPPER FROM DUAL;

UPPER
--------------------------------
UPPER

• initcap（）函数 - 单词首字符大写

initcap(string)

#string:要转换的字符串

SELECT INITCAP('i lOvE you') INITCAP FROM DUAL;

INITCAP
--------------------------------
I Love You

• length（）函数 - 返回字符串长度

length(string)

#string:要计算的字符串或其它数据类型，无论是单双字节都统一视为一个字符

SELECT LENGTH(12.34 || 'ABC长度') LENGTH FROM DUAL;

    LENGTH
----------
        10

• substr（）函数 - 截取字符串

substr(string,start_index,length)

#string:要截取的字符串    start_index:开始截取的位置    length:截取长度，省略的话截取到末尾

SELECT SUBSTR('ABCSUBSTRDEF',4,6) SUBSTR FROM DUAL;

SUBSTR
----------------------------------------------------------------
SUBSTR

• instr（）函数 - 获得字符串出现的位置

instr(string,sub_string,start_index,time)

#string:父字符串    sub_string:子字符串    start_index:从哪个位置开始查(省略就从首位开始查)    time:第几次获得子字符串（省略为首次获得位置）

SELECT INSTR('123456789123456789','45') INSTR FROM DUAL;

     INSTR
----------
         4

• ltrim（）函数 - 删除字符串左侧空格

ltrim(string)

#string:要去除左侧空格的字符串

SELECT LTRIM('   ABCD  EFG') LTRIM FROM DUAL;

LTRIM
--------------------------------
ABCD  EFG

• rtrim（）函数 - 删除字符串右侧空格

rtrim(string)

#string:要去除右侧空格的字符串

SELECT RTRIM('ABCD  EFG   ') RTRIM FROM DUAL;

RTRIM
--------------------------------
ABCD  EFG

• 删除字符串两侧空格

trim(string)

#string:要去除两侧空格的字符串

SELECT TRIM('   ABCD  EFG   ') TRIM FROM DUAL;

TRIM
--------------------------------
ABCD  EFG

• concat（）函数 - 串联字符串

concat(string,con_string)

#string:第一个字符串    con_string:另一个字符串

SELECT CONCAT('CON','CAT') CONCAT FROM DUAL;

CONCAT
----------------------------------------------------------------
CONCAT

• translate（）函数 - 翻译字符串

translate(string,object_string,relpace)

#string:要翻译的字符串    object_string:翻译字符字典    relpace:在字典中找到后替换为的字符

SELECT TRANSLATE('a1B3CfDtTy89RqAPOIN90SLAWHE123456789','TRANSLATEaBCDdftyRqPOIWH1234567890','TRANSLATE') TRANLATE FROM DUAL;

TRANLATE
--------------------------------------------------------------------------------
TRANSLAE

• reverse（）函数 - 反转字符串

reverse(string)

#string:要反转的字符串

SELECT REVERSE('FEDCBA') REVERSE FROM DUAL;

REVERSE
--------------------------------
ABCDEF

数值型处理函数

• abs（）函数 - 返回数字的绝对值

abs(number)

#number:要处理的数字

SELECT ABS(-12.34) ABS FROM DUAL;

       ABS
----------
     12.34

• round（）函数 - 返回数字的“四舍五入”值

round(number,scale)

#number:要处理的数字    scale:正数表示精确到小数点前几位，0表示精确到整数，负数表示精确到小数点后几位，可以忽略(和0一样，表示精确到整数)

SELECT ROUND(12.34) ROUND FROM DUAL;

     ROUND
----------
        12

• ceil（）函数 - 向上取整

ceil(number)

#number:要处理的数字

SELECT CEIL(12.34) CEIL FROM DUAL;

      CEIL
----------
        13

SELECT CEIL(-12.34) CEIL FROM DUAL;

      CEIL
----------
       -12

• floor（）函数 - 向下取整

floor(number)

#number:要处理的数字

SELECT FLOOR(12.34) FLOOR FROM DUAL;

     FLOOR
----------
        12

SELECT FLOOR(-12.34) FLOOR FROM DUAL;

     FLOOR
----------
       -13

• mod（）函数 - 取模

mod(dividend，divisor)

#dividend:被除数    divisor:除数

SELECT MOD(5,2) MOD FROM DUAL;

       MOD
----------
         1

• sign（）函数 - 返回数字的正负性

sign(number)

#number:要判断的数字，返回值1为正数，0为0，-1为负数

SELECT SIGN(123) SIGN FROM DUAL;

      SIGN
----------
         1

• sqrt（）函数 - 返回数字的平方根

sqrt(number)

#number:被开方的数字，要求大于0

SELECT SQRT(81) SQRT FROM DUAL;

      SQRT
----------
         9

• power（）函数 - 乘方运算

power(base_number,index_number)

#base_number:底数    index_number:指数

SELECT POWER(9,2) POWER FROM DUAL;

     POWER
----------
        81

• trunc（）函数 - 截取数字

trunc(number,scale)

#number:要处理的数字    scale:正数表示截取小数点前几位，0表示截取到整数，负数表示截取到小数点后几位，可以忽略(和0一样，表示截取到整数)

SELECT TRUNC(12.34) TRUNC FROM DUAL;

     TRUNC
----------
        12

• chr（）函数 - 将ASCII码转换为字符

chr(number)

#number:要处理的数字

SELECT CHR(65) CHR FROM DUAL;

CHR
---
A

• to_char（）函数 - 格式化数值

to_char(number,format)

#number:要处理的数字    format:格式(0代表数字位，没有数字就强行加0；9也代表数字位，整数位没有的话就不填充，小数位没有的话就强行加0)

SELECT TO_CHAR(12340.56789,'99,990.99999') TO_CHAR FROM DUAL;

TO_CHAR
--------------------------------------------------------------------------------
 12,340.56789

日期型处理函数

• add_months（）函数 - 为日期加上特定月份

add_months(date,number)

#date:日期    number:数字    若加上月份的日期为非法日期 ，则返回该月的最后一天

SELECT SYSDATE,ADD_MONTHS(SYSDATE,1) ADD_MONTHS FROM DUAL;

SYSDATE     ADD_MONTHS
----------- -----------
2018/7/18 1 2018/8/18 1

#月份加1后的日期为非法日期，返回该月的最后一天
 SELECT ADD_MONTHS(TO_DATE('2018-1-30','yyyy-MM-dd'),1) ADD_MONTHS FROM DUAL;

ADD_MONTHS
-----------
2018/2/28

#月末加后的日期为下个月的月末
SELECT ADD_MONTHS(TO_DATE('2018-2-28','yyyy-MM-dd'),1) ADD_MONTHS FROM DUAL;

ADD_MONTHS
-----------
2018/3/31

• last_day（）函数 - 返回指定日期所在月份的最后一天

last_day(date)

#date:要处理的日期


SQL> SELECT LAST_DAY(TO_DATE('2018-2-2','yyyy-MM-dd')) LAST_DAY FROM DUAL;

LAST_DAY
-----------
2018/2/28

• months_between（）函数 - 返回两个日期所差的月数

months_between(date,other_date)

#date:第一个日期    other_date:另一个日期

SELECT MONTHS_BETWEEN(TO_DATE('2018-5-2','yyyy-MM-dd'),TO_DATE('2018-2-2','yyyy-MM-dd')) MONTHS_BETWEEN FROM DUAL;

MONTHS_BETWEEN
--------------
             3

• next_day（）函数 - 返回特定日期后的一周的周几的日期

next_day(date,number)

#date:特定的日期    number:周几(1代表周日，2代表周一，以此类推)

SELECT NEXT_DAY(TO_DATE('2018-5-2','yyyy-MM-dd'),2) NEXT_DAY FROM DUAL;

NEXT_DAY
-----------
2018/5/7

• trunc（）函数 - 截取日期

trunc(date,format)

#date:要处理的日期    format:截取格式，可以截取到任意时刻，如年、月、日、时、分、秒等


SELECT TRUNC(TO_DATE('2018-5-2','yyyy-MM-dd'),'MM') TRUNC FROM DUAL;

TRUNC
-----------
2018/5/1

• to_char（）函数 - 将日期转为字符串

to_char(date,format)

#date:日期    format:格式(下面表格给出了各种格式代码)

SELECT CONCAT('今天是' || TO_CHAR(SYSDATE,'yyyy-MM-dd'),CONCAT(',今年的第',TO_CHAR(SYSDATE,'DDD') || '天')) TO_CHAR FROM DUAL;

TO_CHAR
--------------------------------------------------------------------------------
今天是2018-07-18,今年的第199天

用于格式化时间的格式代码

格式代码	说明	实例
年	年份全拼	二十八岁
YYYY	四位年份	2018
YYY	年份后三位	018
YY	年份后两位	18
ÿ	年份最后一位	8
Q	季度	3
MM	两位月份	07
MON	月份缩写	7月（与当前系统语言区域有关）
月	月份	7月（与当前系统语言区域有关）
WW	一年中的第几周	29
w ^	一月中的第几周	3
d	一周中的第几天	4
天	一周中的星期几	星期三（与当前系统语言区域有关）
DD	一月中的第几天	18
DDD	一年中的第几天	199
DY	一周中星期几的缩写	星期三（与当前系统语言区域有关）
HH	某时刻的小时数	05
HH12	某时刻的12进制小时数	05
HH24	某时刻的24进制小时数	17
MI	某时刻的分钟数	36
SS	某时刻的秒数	22
FF	某时刻的毫秒数	121

复杂型数据处理函数

• max（）函数 - 求最大值

max(data)

#data:数据(可以是各种类型的数据)

SELECT MAX(T.NAME) MAX FROM T_CEM_SYS_EMPLOYEE T;

MAX
----------------------------------------------------------------
张辉辉

SELECT MAX(T.ID) MAX FROM T_CEM_SYS_EMPLOYEE T;

MAX
--------------------------------
2018070400666005

• min（）函数 - 求最小值

min(data)

#data:数据(可以是各种类型的数据)

SELECT MIN(T.NAME) MIN FROM T_CEM_SYS_EMPLOYEE T;

MIN
----------------------------------------------------------------
张辉辉

SELECT MIN(T.ID) MIN FROM T_CEM_SYS_EMPLOYEE T;

MIN
--------------------------------
2018070400666005

• avg（）函数 - 求平均值

avg(data)

#data:要处理的数据

SELECT AVG(T.AGE) AVG FROM T_CEM_SYS_EMPLOYEE T;

AVG
--------------------------------
28

• sum（）函数 - 求和

sum(data)

#data:要处理的数据

SELECT SUM(T.AGE) SUM FROM T_CEM_SYS_EMPLOYEE T;

SUM
--------------------------------
3360

• count（）函数 - 统计记录数

count(data)

#data:要处理的数据

SELECT COUNT(1) COUNT FROM T_CEM_SYS_EMPLOYEE T;

COUNT
--------------------------------
336

•  decode（）函数 - 多值判断

decode(表达式，比较值1，结果值1，比较值2，结果值2，...，默认值)

SELECT DECODE(SIGN(100),1,'正数',-1,'负数',0,'0','不知道') DECODE FROM DUAL;

DECODE
--------------------------------
正数

•  nvl（）函数 - 空值处理

nvl(表达式，新值/新表达式(若表达式结果为空))

SELECT NVL('','空字符串') NVL FROM DUAL;

NVL
--------------------------------
空字符串

•  nvl2（）函数 - 空值处理

nvl2(表达式，新值/新表达式(若表达式结果为空),新值/新表达式(若表达式结果不为空))

SELECT NVL2('ABC','空字符串','非空字符串') NVL2 FROM DUAL;

NVL2
--------------------------------
非空字符串