Oracle 的常用函数

1、获取随机值函数

1.1 产生一个随机值

SELECT dbms_random.random from ;

1.2 产生一个1-30的随机值

SELECT dbms_random.value(1,30) from dual ;

2、四舍五入函数

round 四舍五入截取 trunc直接截取

round(x,y),y小数位多少位，为负数时，小数点左边位数为0，四舍五入，y可以忽略，默认为整数

trunc(x,y)同round

SELECT round(1.99) from dual ; --2

SELECT Round(266.77,-2) from dual ;--300

SELECT Round(266.77,1) from dual ;--266.8

SELECT trunc(1.99) from dual ; --1

SELECT trunc(266.77,-1) from dual ;--260

SELECT trunc(266.77,1) from dual ;--266.7

3、字符相关函数

3.1 返回字符串长度函数 LENGTH(str)

SELECT LENGTH('哈h手动') from dual ; --4

SELECT LENGTH('c') from dual ;--1

SELECT LENGTH(1) from dual ;--1

3.2 合并字符串函数concat(s1,s2)

SELECT concat(1,'hh') from dual ;--1hh

3.3 字符串搜索函数Instr(s,x,y) 返回第y次x出现在s中的位置,Y可以忽略，返回x出现在s的第一次位置

SELECT Instr('abcdaa','a') from dual ;--1

SELECT Instr('abcdaa','a',2) from dual ;--5

3.4 字母大小写转换函数 Upper lOWER INITCAP

Upper(str) 把str转换成大写

lOWER(str) 把str转换成小写

INITCAP(str) 把str首字母大写

SELECT Upper('hh') from dual;--HH

SELECT LOWER('HH') from dual;--hh

SELECT Initcap('hhhdsa') from dual ;--fg

3.5 截取指定字符串长度函数 substr(s,m,n) s字符串 m截取位置，n截取长度 m为负数时，从右边截取

SELECT substr('abcdefg',2,3) from dual ;--bcd

SELECT substr('abcdefg',2) from dual ;--bcdefg

SELECT substr('abcdefg',-2) from dual ;--bcdefg

3.6 替换字符串函数 replace(s1,s2,s3) s1 目标字符串 s2 要搜索的字符 s3要替换成的字符

s3 参数不写：代表要删除的字符

SELECT replace('aaabcdd','aa','bbb') from dual ;--bbbabcdd

SELECT replace('aaabcdd','aa') from dual ;--abcdd

3.7 删除字符串首尾的指定字符串函数 ltrim(s,n) rtrim(s,n) s是目标字符串 n需要删除的字符 n不写默认删除空格

SELECT ltrim('  aaaa') from dual;--aaaa

SELECT ltrim('baaa','b') from dual;--aaa

SELECT rtrim('  aaaa   ') from dual;--  aaaa

SELECT rtrim('baaa','a') from dual;--b

SELECT TRIM('  abcda  ') FROM dual;--abcda

3.8 trim(LEADING|TRALING|BOTH TRIM_CHARACTER FROM TRIM_SOURCE)

LEADING 前面

TRALING 后面

BOTH两端

SELECT TRIM(BOTH 'a' FROM 'a  abcda  a') FROM dual;--  abcda

4、时间相关函数

4.1 获取当前系统日期 Sysdate

SELECT Sysdate from dual ;--2022/12/22 19:10:25

4.2 获取指定格式时间 to_char

SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') from dual ;--2022-12-22 19:11:57

4.3获取当前系统时间 systimestamp

SELECT systimestamp from dual ;--22-12月-22 07.12.49.327418 下午 +08:00

4.4 获取系统时区函数 dbtimezone

SELECT dbtimezone from dual ; --+00:00

4.5 获取当前系统所在时区函数 sessiontimezone

SELECT sessiontimezone from dual ;--+08:00

4.6 获取指定月份后最后一条函数 LAST_day(date)

SELECT LAST_day(sysdate) from dual ;--2022/12/31 19:17:32

SELECT last_day(systimestamp) FROM dual;--2022/12/31 19:18:10

4.7 获取指定日期后一周后的函数next_day(date,char) 时间，char表示星期几

SELECT sysdate,next_day(sysdate,'星期一') FROM dual;--12022/12/22 19:21:102022/12/26 19:21:10

4.8 获取指定日期特定部分的函数 extract

SELECT extract(YEAR FROM Sysdate) from dual ;--2022

4.9 计算月份差 months_between

months_between（sysdate,以前时间） 计算月分差

5、转化函数

5.1 字符串换数字 CAST or to_NUMBER

SELECT CAST('11231.9999' AS Number(11,2)) AS RESULT from dual ;--11232.00

SELECT to_NUMBER('11231.9999') from dual ;--11231.9999

5.2 数字转字符串 to_char

SELECT to_char(11231.9999,'99999.99') from dual ;-- 11232.00

SELECT to_char(11231.9999) from dual ;-- 11231.9999;

5.3 字符串转日期函数 YYYY-MM-DD HH:SI:SS FF

select to_char(sysdate, 'yyyy' )  from dual; --年

select to_char(sysdate, 'MM' )  from dual; --月

select to_char(sysdate, 'dd' )  from dual; --日

select to_char(sysdate,'Q')  from dual; --季

select to_char(sysdate,'iw')  from dual;--周

SELECT to_char(to_date('1999-12-22','YYYY-MM-DD'),'mm') from dual ;

select to_date('1999-12-22 23:59:00','YYYY-MM-DD HH24:MI:SS') FROM dual;--1999/12/22 23:59:00

SELECT to_timestamp ('1999-12-22 23:59:00 9999','YYYY-MM-DD HH24:MI:SS FF') FROM dual;--22-12月-99 11.59.00.999900000 下午

6、系统信息函数

6.1 USER函数返回当前登录会话的用户

SELECT USER from dual ;--test

6.2 USERENV函数用于返回会话以及上下文信息

语法格式如下USERENV(paeameter)

--当参数是LANGUAGE,返回对应的语言，字符集

--当参数是SESSIONID,返回会话的id

--当参数是ISDBA,返回当前用户是否是DBA

--当参数是LANG,返回ISO缩写语言名称

--当参数是LANG,返回终端

--当参数是INSTANCE,返回当前INSTANCE的标志

SELECT USERENV('LANGUAGE') from dual ;--SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SELECT USERENV('SESSIONID') FROM dual;--36725

SELECT USERENV('ISDBA') FROM dual;--FALSE

SELECT USERENV('LANG') FROM dual;--ZHS

select userenv('terminal') from dual;--JV5165

select userenv('INSTANCE') from dual;--1

6.3 sys_context() 函数用于返回会话以及上下文信息

第一个参数为'USERENV'是固定的，不区分大小写。第二个参数也是固定的，但是可多选，可选的值如下所示

select sys_context('USERENV','BG_JOB_ID') from dual;--当前指定id的会话是否为oracle后台程序建立，不是则返回null

select sys_context('USERENV','CLIENT_INFO') from dual;--通过dbms_application_info包可以存储高达64字节的用户会话信息

select sys_context('USERENV','CURRENT_SCHEMA') from dual;--默认的schema将被当做当前的schema。当在当前会话中使用ALTER SESSION SET CURRENT_SCHEMA语句的时候，它的查询返回值将被改变

select sys_context('USERENV','CURRENT_SCHEMAID') from dual;--当前schema的id

select sys_context('USERENV','CURRENT_USER') from dual;--当前的登陆用户

select REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'\',':') from dual;--当前会话主机操作系统名

select sys_context('USERENV','CURRENT_USERID') from dual;--当前登陆的用户的id

select sys_context('USERENV','DB_DOMAIN') from dual;--为数据库的域指定初始化参数

select sys_context('USERENV','DB_NAME') from dual;--数据库实例名

select sys_context('USERENV','ENTRYID') from dual;--可用的审计标示符。不能再分布式sql语句中使用此选项。使用USERENV关键字必须置AUDIT_TRAIL的初始化参数为真。

select sys_context('USERENV','EXTERNAL_NAME') from dual;--数据库用户的扩展名

select sys_context('USERENV','FG_JOB_ID') from dual;--返回作业id当此会话是客户端进程创建。否则，返回null

select sys_context('USERENV','INSTANCE') from dual;--当前数据库实例的标示id

select sys_context('USERENV','ISDBA') from dual;--当前用户是否是以dba身份登录

select sys_context('USERENV','LANG') from dual;--iso对‘LANGUAGE’的简称，查询的参数比“LANGUAGE”短

select sys_context('USERENV','LANGUAGE') from dual;--结果为当前数据库使用的存储语言，跟上面查询意义一样

select sys_context('USERENV','NETWORK_PROTOCOL') from dual;--用于通信的网络协议

select sys_context('USERENV','NLS_CALENDAR') from dual;--当前会话使用的，格林尼治时间

select sys_context('USERENV','NLS_CURRENCY') from dual;--本地化的货币符，如人民币为￥，美元符为$

select sys_context('USERENV','NLS_DATE_FORMAT') from dual;--当前使用的日期格式，一般中国为dd-mon-rr

select sys_context('USERENV','NLS_DATE_LANGUAGE') from dual;--表示日期的语言，如中文简体SIMPLIFIED CHINESE

select sys_context('USERENV','NLS_TERRITORY') from dual;--数据库服务器所在区域，如中国CHINA

select sys_context('USERENV','OS_USER') from dual;--操作系统的用户名

select sys_context('USERENV','PROXY_USER') from dual;--是否使用代理用户。否返回null

select sys_context('USERENV','PROXY_USERID') from dual;--代理用户id

select sys_context('USERENV','SESSION_USER') from dual;--当前认证的数据库用户名

select sys_context('USERENV','SESSION_USERID') from dual;--当前认证的数据库用户名id

select sys_context('USERENV','SESSIONID') from dual;--当前会话id

select sys_context('USERENV','TERMINAL') from dual;--操作系统用户组

select sys_context('USERENV','IP_ADDRESS') from dual;--当前会话主机ip

select sys_context('USERENV','HOST') from dual;--当前会话主机操作系统名

7、other

7.1 decode()判断函数

当decode条件，值1满足时，就输出值1，值2满足时，就输出值2，。。。。以此类推，

decode(字段值，值1，值2，值3……)

7.2 NVL()函数实现空值的转换。

例如NVL(string1,replace_with)中：

当第一个参数（string1）为空时，返回第二个参数（replace_with）；

当第一个参数（string1)不为空时，则返回第一个参数（string1）。

7.3 NVL2()函数：Oracle在NVL函数的功能上扩展，提供了NVL2函数

如NVL2(E1,E2,E3)中：

当E1为NULL时，返回E3；当E1不为NULL时，返回E2

后面用到别的函数继续补充ing