Oracle函数篇(1)——单行函数

字符函数

函    数	说                明
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

语句 ：

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

(3)字符串拼接 CONCAT

语句 ：

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

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

select 'ABC' || 'D' from dual;

查询结果同上。

(4)代码示例：

-- 1. 字符函数
-- 1.1 求字符串长度 length()
select length('123') from dual;
-- 1.2 求字符串的子串 substr(字符串,起始位置,数据数量)
select substr('abcd中efg',1,2) from dual;
-- 1.3 求子串在字符串中的位置  instr(字符串,字串) => 位置
SELECT INSTR('abcd中efg', '中') FROM DUAL;
-- 1.4 substr 和 instr 结合使用 获取指定字符串中 指定字符 后的数据
-- 已知字符串 6666@qq.com 获取邮箱类型 ==>qq.com
select substr('6666@qq.com',INSTR('6666@qq.com', '@') + 1,10) from dual;
-- 1.5 字符串拼接 concat(字符串1,字符串2)
select concat('1','2') from dual;
-- 1.6 多个字符串拼接 ||
select 'a'||'b'||'c' 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)截取函数 TRUNC

语句 ：

select trunc(100.567) from dual;

语句 ：

select trunc(100.567, 2) from dual;

(3) 取模   MOD

语句 ：

select mod(10, 3) from dual;

(4)代码示例：

-- 2. 数值函数
-- 2.1 四舍五入函数 round
select round(1.64) from dual;
-- 2.2 指定四舍五入小数位数
select round(1.65,1) from dual;
-- 2.3 截取函数 trunc(数值,小数位数)
select trunc(1.66,1) from dual;
-- 2.4 取模 mod(数值,被除数)
with t1 as (
select mod(10,2) ret from dual)
select * from t1 where ret=0;
-- 2.5 创建表my_test02 创建语句如下
create table my_test02(
    num number
);

转换函数

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

(1)加月函数 ADD_MONTHS：

在当前日期基础上加指定的月，语句 ：

select add_months(sysdate, 2) from dual

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

语句 ：

select last_day(sysdate) from dual

(3)日期截取 TRUNC

语句 ：

select trunc(sysdate) from dual

语句 ：

select trunc(sysdate, 'yyyy') from dual

时间相关函数

(1)to_date是把字符串转化为时间类型:

SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL;

SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL;

SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL;

SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL;

SELECT TO_DATE('2006', 'YYYY') FROM DUAL;

(2)trunc函数截取后的结果依然为时间类型

select sysdate S1,

trunc(sysdate) S2,

trunc(sysdate,'year') YEAR,

trunc(sysdate,'month') MONTH ,

trunc(sysdate,'day') DAY

from dual;

(3)to_char函数的结果为字符串类型

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;

select to_char(sysdate,'yyyy') as nowYear from dual;

select to_char(sysdate,'mm') as nowMonth from dual;

select to_char(sysdate,'dd') as nowDay from dual;

select to_char(sysdate,'hh24') as nowHour from dual;

select to_char(sysdate,'mi') as nowMinute from dual;

select to_char(sysdate,'ss') as nowSecond from dual;

(4)代码示例：

-- 3. 时间函数
-- 3.1 获取系统时间 sysdate
select sysdate from DUAL;
-- 3.2 加月函数 add_months(时间类型,3)
select add_months(sysdate,1) from DUAL;
-- 3.3 加减时间通用方法 select sysdate + interval '1' minute from dual;
select sysdate - interval '1' month from dual;

-- 3.4 时间相关函数汇总
-- Orcale中时间类型date是 必须为 年月日时分秒的样式
-- 和mysql中不一样 没有单独的year时间类型

-- 3.4.1 to_date是把字符串转化为时间类型:
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL;

SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL;

SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL;

SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL;

SELECT TO_DATE('2006', 'YYYY') FROM DUAL;

-- 3.4.2 trunc函数截取后的结果依然为时间类型
select sysdate S1,

trunc(sysdate) S2,

trunc(sysdate,'year') YEAR,

trunc(sysdate,'month') MONTH ,

trunc(sysdate,'day') DAY

from dual;
-- 获取当前日期 和 日期2024-04-10 的差值

-- 3.4.3 to_char函数的结果为字符串类型
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;

select to_char(sysdate,'yyyy') as nowYear from dual;

select to_char(sysdate,'mm') as nowMonth from dual;

select to_char(sysdate,'dd') as nowDay from dual;

select to_char(sysdate,'hh24') as nowHour from dual;

select to_char(sysdate,'mi') as nowMinute from dual;

select to_char(sysdate,'ss') as nowSecond from dual;

-- 3.5 字符串转数字 TO_NUMBER
select to_number('123') from dual;

其它函数

(1) 空值处理函数 NVL

用法 ：NVL (检测的值 ，如果为 null 的值)  ；

语句 ：

select NVL(NULL, 0) from dual

(2) 空值处理函数 NVL2

用法 ：NVL2 (检测的值，如果不为 null 的值，如果为 null 的值)  ；.

语句：

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

(3) 条件取值 decode

语法 ：

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

(4)代码示例：

-- 5. 其他函数
-- 空值处理函数 NVL
-- NVL (检测的值 ，如果为 null 的值)
select nvl(null,1) from dual;
-- NVL2 (检测的值，如果不为 null 的值，如果为 null 的值)
select nvl(null,10,20) from dual;