9 Oracle函数

单行函数

字符函数

函数
ASCII
返回对应字符的十进制值
CHR
给出十进制返回字符
CONCAT
拼接两个字符串，与 || 相同
INITCAT
将字符串的第一个字母变为大写
INSTR
找出某个字符串的位置
INSTRB
找出某个字符串的位置和字节数
LENGTH
以字符给出字符串的长度
LENGTHB
以字节给出字符串的长度
LOWER
将字符串转换成小写
LPAD
使用指定的字符在字符的左边填充
LTRIM
在左边裁剪掉指定的字符
RPAD
使用指定的字符在字符的右边填充
RTRIM
在右边裁剪掉指定的字符
REPLACE
执行字符串搜索和替换
SUBSTR
取字符串的子串
SUBSTRB
取字符串的子串(以字节)
SOUNDEX
返回一个同音字符串
TRANSLATE
执行字符串搜索和替换
TRIM
裁剪掉前面或后面的字符串
UPPER
将字符串变为大写

常用字符函数讲解:
(1)求字符串长度 LENGTH

select length('ABCD') from dual;

(2)求字符串的子串 SUBSTR（循环从1开始的）

select substr('ABCD',2,2) from dual;

(3)字符串拼接 CONCAT

select concat('ABC','D') from dual;

我们也可以用|| 对字符串进行拼接

select 'ABC' || 'F' from dual;

数值函数

ABS(value)
绝对值
CEIL(value)
大于或等于 value 的最小整数
COS(value)
余弦
COSH(value)
反余弦
EXP(value)
e 的 value 次幂
FLOOR(value)
小于或等于 value 的最大整数
LN(value)
value 的自然对数
LOG(value)
value 的以 10 为底的对数
MOD(value,divisor)
求模
POWER(value,exponent)
value 的 exponent 次幂
ROUND(value,precision)
按precision 精度4舍5入
SIGN(value)
value 为正返回 1;为负返回-1;为 0 返回 0.
SIN(value)
余弦
SINH(value)
反余弦
SQRT(value)
value 的平方根
TAN(value)
正切
TANH(value)
反正切
TRUNC(value,按 precision)
按照 precision 截取 value
VSIZE(value)
返回 value 在 ORACLE 的存储空间大小

常用数值函数讲解:
(1)四舍五入函数 ROUND

select round(100.567) from dual

精确到整数

select round(100.567, 2) from dual

精确到小数点后2位
(2)截取函数 TRUNC

select trunc(100.567) from dual

截取整数：100
(3)取模 MOD

select mod(10,3) from dual

结果：1

日期函数

ADD_MONTHS
在日期 date 上增加 count 个月
GREATEST(date1,date2,. . .)
从日期列表中选出最晚的日期
LAST_DAY( date )
返回日期 date 所在月的最后一天
LEAST( date1, date2, . . .)
从日期列表中选出最早的日期
MONTHS_BETWEEN(date2, date1)
给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY( date,’day’)
给出日期 date 之后下一天的日期，这里的 day 为星期， 如: MONDAY,Tuesday 等。
NEW_TIME(date,’this’,’other‘)
给出在 this 时区=Other 时区的日期和时间
ROUND(date,’format’)
未指定 format 时，如果日期中的时间在中午之前，则 将日期中的时间截断为 12 A.M.(午夜，一天的开始),否 则进到第二天。时间截断为 12 A.M.(午夜，一天的开始), 否则进到第二天。
TRUNC(date,’format’)
未指定 format 时，将日期截为 12 A.M.( 午夜，一天的 开始).

我们用 sysdate 这个系统变量来获取当前日期和时间
语句如下:

select sysdate from dual

常用日期函数讲解:
(1)加月函数 ADD_MONTHS :在当前日期基础上加指定的月

select add_months(sysdate, 2) from dual

(2)求所在月最后一天 LAST_DAY

select last_day(sysdate) from dual

2022-07-31 11:18:21
(3)日期截取 TRUNCsele

select trunc(sysdate, 'yyyy') from dual

2022-01-01 00:00:00

select trunc(sysdate, 'mm') from dual

2022-01-01 星期六

转换函数

CHARTOROWID
将 字符转换到 rowid 类型
CONVERT
转换一个字符节到另外一个字符节
HEXTORAW
转换十六进制到 raw 类型
RAWTOHEX
转换 raw 到十六进制
ROWIDTOCHAR
转换 ROWID 到字符
TO_CHAR
转换日期格式到字符串
TO_DATE
按照指定的格式将字符串转换到日期型
TO_MULTIBYTE
把单字节字符转换到多字节
TO_NUMBER
将数字字串转换到数字
TO_SINGLE_BYTE
转换多字节到单字节

常用转换函数讲解:
(1)数字转字符串 TO_CHAR

select TO_CHAR(1024) from dual

(2)日期转字符串 TO_CHAR

select TO_CHAR(SYSDATE, 'yyyy-mm-dd') from dual

2022-07-07
(3)字符串转日期 TO_DATE

select to_date('2017-01-01', 'yyyy-mm-dd') from dual

2017-01-01 00:00:00
(4)字符串转数字 TO_NUMBER

select to_number('100') from dual

其它函数

(1)空值处理函数 NVL
用法:

NVL(检测的值，如果为 null 的值);

语句：

select nvl(null, 0) from dual

需求:
显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999

select price, minnum, nvl(maxnum, 999999) from t_pricetable where ownertypeid = 1

(2)空值处理函数 NVL2
用法:
NVL2(检测的值，如果不为 null 的值，如果为 null 的值);
需求:显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”.

select price, minnum, nvl2(maxnum, to_char(maxnum), '不限') from t_pricetable where ownertypeid = 1

(3)条件取值 decode
语法:

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
【功能】根据条件返回相应值

需求:显示下列信息(不要关联查询业主类型表，直接判断 1 2 3 的值)
语句:

select name,decode(ownertypeid, 1, '居民', 2, '事业单位', 3, '商业') as leixing1 from t_owners

上边的语句也可以用 case when then 语句来实现

select name ,(case ownertypeid
	when 1 then '居民'
	when 2 then '行政事业单位'
	when 3 then '商业'
	else '其它'
	end	)
from t_owners

还有另外一种写法:

select name,(case
	when ownertypeid= 1 then '居民'
	when ownertypeid= 2 then '行政事业'
	when ownertypeid= 3 then '商业'
			end )
from T_OWNERS

行列转换

分析函数

以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例
(1) RANK 相同的值排名相同，排名跳跃
需求:对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃

select rank() over(order by usenum  desc ), usenum from t_account

(2) DENSE_RANK 相同的值排名相同，排名连续
需求:对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续

select dense_rank() over(order by usenum desc), usenum from t_account

(3) ROW_NUMBER 返回连续的排名，无论值是否相等
需求:对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等

select row_number() over(order by usenum desc), usenum from t_account

用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

select * from
(select row_number() over(order by usenum desc)
rownumber, usenum from t_account )
where rownumber > 10 and rownumber <= 20