Oracle学习笔记3 —— 函数

原创已于 2025-10-20 19:16:33 修改 · 1k 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #学习 #笔记 #数据库 #sql

于 2025-10-20 19:15:57 首次发布

Oracle学习笔记专栏收录该内容

3 篇文章

订阅专栏

课后作业和课后作业答案在主页其他专栏，欢迎进入主页查看

函数分类

一、单行函数

1. 数值函数

常用的数值处理函数如下：

abs(数1)          -- 求绝对值，返回类型：数值型 
mod(数1, 数2)     -- 取余，结果符号只和数1相关，返回类型：数值型 
ceil(数1)         -- 向上取整，返回类型：数值型 
floor(数1)        -- 向下取整，返回类型：数值型 
round(数1[,数2])  -- 四舍五入，保留数2位小数，省略时不保留小数，返回类型：数值型 
trunc(数1[,数2])  -- 截断，保留数2位小数，返回类型：数值型 
power(数1, 数2)   -- 计算数1的数2次方 
sign(数1)         -- 判断正负零，数1是负数返回-1，正数返回1，0返回0

2. 字符函数

基础操作：

upper(str)            -- 转大写，返回类型：字符型 
lower(str)            -- 转小写，返回类型：字符型 
initcap(str)          -- 每个单词首字母大写，其余小写，返回类型：字符型 
length(str)           -- 字符长度，返回类型：数值型 
lengthb(str)          -- 字节长度，返回类型：数值型 
trim(str)             -- 去除两端空格，返回类型：字符型 
ltrim(str1[,str2])    -- 去除左端指定字符，省略时去除空格，返回类型：字符型 
rtrim(str1[,str2])    -- 去除右端指定字符，省略时去除空格，返回类型：字符型 
lpad(str1, 数1, str2) -- 左端补齐，直到总字节长度为数1，返回类型：字符型 
rpad(str1, 数1, str2) -- 右端补齐，直到总字节长度为数1，返回类型：字符型

进阶操作：

instr(str1, str2[, 数1[, 数2]]) -- 查找str2在str1中的位置，支持指定起始位置和第几次出现 
substr(str1, 数1[, 数2])        -- 截取字符串，从数1位置开始，截取数2长度 
replace(str1, str2[, str3])     -- 将str1中的str2整体替换为str3，str3省略时替换为空 
translate(str1, str2, str3)     -- 逐一替换str1中的str2为str3

3. 转换函数

常见类型转换：

-- 字符串转数值 
to_number(str) -- 仅支持纯数字字符串，返回类型：数值型 

-- 字符串转日期 
to_date(str, '日期格式') -- 字符串需满足日期格式，返回类型：日期型 
-- 日期格式举例： 
-- yyyy 年 
   mm 月
   dd 日 
   hh/hh24 小时 
   mi 分钟 
   ss 秒 
   day 星期(中文) 
   d 星期(数字, 1为周日, 7为周六) 
   q 季度 
-- 当'日期格式'中缺失年(yyyy)和月(mm)时，会自动补齐当前系统时间，日时分秒不会补齐 

-- 数字转字符串 
to_char(数字[, '数字格式']) -- 支持格式化显示，如占位符0/9、L(本地货币)、$等 

-- 日期转字符串 
to_char(date[, '日期格式']) -- 支持日期格式化，常用格式同上 
-- 日期格式举例： 
-- yyyy 年 
   mm 月 
   dd 日 
   hh/hh24 小时 
   mi 分钟 
   ss 秒 
   day 星期(中文) 
   d 星期(数字, 1为周日, 7为周六) 
   q 季度 
   ddd 当前天是当年的第几天 
   sssss 当前秒是当天的第几秒 

-- 参数转字符串 
to_char(参数)   -- 将参数转换为字符格式 

-- 字符转ASCII码 
ascii(str) -- 仅转换首字符 

-- ASCII码转字符 
chr(数) 

-- 字符串转全宽 
to_multi_byte(str) 

-- 字符串转半宽 
to_single_byte(str)

4. 日期函数

-- 日期计算 
-- date + num1：增加num1天 
-- date1 - date2：日期相差天数 

sysdate()                  -- 当前系统时间，返回类型：日期型 

add_months(日期1, 数1)      -- 增加指定月份 

months_between(日期1, 日期2) -- 相差月份数 

trunc(日期, 日期格式)        -- 截断到指定日期粒度，如年/月/小时 

next_day(日期, '星期几'|数字) -- 下一个指定星期几（数字1-7, 1为周日） 

last_day(日期)               -- 当前月最后一天 

-- 闰年判断： 
-- 1. 二月有29天 
-- 2. 能被4整除但不能被100整除，或能被400整除 
-- 3. 闰年有366天

5. 通用函数

userenv('language') -- 查看当前数据库使用的是哪种字符集 

greatest(参数1,参数2,参数3 ...) -- 返回多个参数中的最大值 

least(参数1,参数2,参数3 ...) -- 返回多个参数中最小值。若参数类型不同，以参数1为准，若参数1为字符型，后面参数默认转换为字符型。如果参数中存在null，最大值、最小值皆为null。 

coalesce(参数1，参数2，参数3...) -- 返回参数中第一个不为空的值 

nvl(参数1, 参数2) -- 若参数1为空返回参数2，否则返回参数1 

nvl2(参数1,参数2,参数3) -- 如果参数1为空，则返回参数3，否则返回参数2 

distinct -- 去重
-- 示例：select distinct 字段 from 表名 -- distinct要紧挨着select才能使用 

case when -- 条件判断 
-- 语法1： select case 字段 when 值1 then 返回值1 
                           when 值2 then 返回值2 
                           else 返回值3 
                  end 
           from 表名 
-- 语法2： select case when 条件1 then 返回值1 
                       when 条件2 then 返回值2 
                       else 返回值3 
                  end 
           from 表名 
-- 注意：必须以end结束，返回值类型需统一，整体作为一个字段处理 

decode(字段名,参数1,返回值1,参数2,返回值2,参数3,返回值3 .....) -- 和case when类似，不过是单行语法

二、多行函数

1. 聚合函数

聚合函数用于将多条数据根据指定维度聚合成一条数据，常见函数如下：

max(字段)           -- 最大值 
min(字段)           -- 最小值 
avg(字段)           -- 平均值 
sum(字段)           -- 求和 
count(字段)         -- 计数 
wm_concat(字段)     -- 不可以指定分隔符，默认为',' 
listagg(字段,'分隔符') within group (order by 字段) -- 可以指定分隔符

聚合函数使用语法

select 分组字段，聚合函数 
from 表名 
[where 限定条件]           -- 分组前限定 
[group by 字段1,字段2 ...] -- 分组字段 
[having 限定条件]          -- 分组后限定 [
order by 字段 [asc|desc]]  -- 排序,asc为升序(默认)，desc为降序

SQL 执行优先级说明

                            --优先级
select 聚合函数                5
from 表名                      1
where 限定条件                 2
group by 字段                  3
having 限定条件                4
order by 字段 [asc|desc]       6

where 与 having 的区别

where：分组前限定，只能用于普通字段
having：分组后限定，只能用于聚合字段

count 函数的区别

count(字段)：只计非空，空值不计
count(1)：计所有行，速度快
count(*)：计所有行，基于数据字典

order by 多条件排序

支持多个排序字段，优先按第一个排序，若相同则按第二个依次类推

2. 分析函数

分析函数在聚合基础上，支持同时看到明细数据，常见用法如下：

-- 组内聚合 
max(字段) over ([partition by 分组字段] [order by 排序字段])   -- 组内最大值 
min(字段) over ([partition by 分组字段] [order by 排序字段])   -- 组内最小值 
avg(字段) over ([partition by 分组字段] [order by 排序字段])   -- 组内平均值 
sum(字段) over ([partition by 分组字段] [order by 排序字段])   -- 组内求和 
count(字段) over ([partition by 分组字段] [order by 排序字段]) -- 组内计数 这里的order by排序是累加的效果(asc|desc) 

-- 排名相关 
row_number() over ([partition by 字段] order by 字段)   -- 行号（不并列） 
rank() over ([partition by 字段] order by 字段)         -- 排名（并列跳级） 
dense_rank() over ([partition by 字段] order by 字段)   -- 排名（并列不跳级） 

-- 行间移动 
lead(字段, 数1[, 数2]) over ([partition by 字段] order by 字段)  -- 上移数1行，空时填数2 
lag(字段, 数1[, 数2]) over ([partition by 字段] order by 字段)   -- 下移数1行，空时填数2