Oracle常用函数汇总
Oracle常⽤函数汇总
在Oracle OCP考试中，相当⼀部分知识点涉及到对于Oracle常见函数的考查。尽管Oracle官⽅⽂档中Functions⼀章内列举了所有Oracle⾃带函数，但如果要系统的看⼀遍，还是要花费相当的精⼒，更何况还是英⽂呢。如果碰到⼀个不熟悉的，就查⼀下，不经常⽤，⼜很容易遗忘。下⾯就对Oracle常见函数做个整理，⽅便以后查询。
⼀、 ⼤⼩写转换函数1. LOWER函数
LOWER函数将字符以⼩写形式输出。其语法为LOWER(char)。
例如：SQL> select lower(100+100),lower(sysdate),lower('HELLO') from dual;
LOW LOWER(SYS LOWER--- --------- -----200 25-sep-14 hello
注意：返回的均是字符类型，在执⾏LOWER函数之前先计算数据和⽇期表达式，并隐式转换为字符数据。2. UPPER函数
UPPER函数将字符以⼤写形式输出。其语法为UPPER(char)。
例如：SQL> select upper('hello world') from dual;
UPPER('HELL-----------HELLO WORLD
3. INITCAP函数
INITCAP函数将字符串中每个单词的第⼀个字母⼤写，⽽该单词的剩余字母⼩写。
例如：SQL> select initcap('hello WORLD! that is funny!') from dual;
INITCAP('HELLOWORLD!THATISF---------------------------Hello World! That Is Funny!
⼆、 字符操作函数1. CONCAT函数
CONCAT函数⽤于连接两个字符串。有两个参数，语法为CONCAT(s1,s2)。
例如：SQL> select concat('hello','world') from dual;
CONCAT('HE----------helloworld
SQL> select concat(concat(ename,'''s job is '),job) from emp where empno=7788;
CONCAT(CONCAT(ENAME,'''SJOBIS-----------------------------SCOTT's job is ANALYST
2. LENGTH函数
LENGTH函数返回字符串的字符数，包括空格，制表符和特殊字符。其语法为LENGTH(s)。
例如：SQL> select length('hello world!') from dual;LENGTH('HELLOWORLD!')---------------------12
3. LPAD和RPAD函数
LPAD和RPAD函数分别被称为左填充和右填充函数。它们分别在给定字符串左边或右边填充指定数量的字符。其语法为LPAD(s,n,p)和RPAD(s,n,p),其中s表⽰源字符串，n表⽰返回字符串的最终长度，p指定⽤于填充的字符串。注意：如果参数n⼩于或者等于源字符串s的长度，就不会添加任何字符，此时只返回源字符串s的前n个字符。p默认为空格。
例如：SQL> select lpad('hello',10,'*'),lpad('hello',10,'*#'),lpad('hello',10),lpad('hello',4,'*') from dual;
LPAD('HELL LPAD('HELL LPAD('HELL LPAD---------- ---------- ---------- ----*****hello *#*#*hello hello hell
SQL> select rpad('hello',10,'*'),rpad('hello',10,'*#'),rpad('hello',10),rpad('hello',4,'*') from dual;
RPAD('HELL RPAD('HELL RPAD('HELL RPAD---------- ---------- ---------- ----hello***** hello*#*#* hello hell
4. TRIM函数
TRIM函数从字符串的开头或者结尾删除⼀些字符。其语法为TRIM([trailing|leading|both] trimstring from s)。其中
TRIM(s)删除输⼊字符串两边的空格
TRIM(trailing trimstring from s)从字符串s的结尾删除所有trimstring（如果存在的话）。
TRIM(leading trimstring from s)从字符串s的开头删除所有trimstring。
TRIM(both trimstring from s)从字符串s的开头和结尾删除所有trimstring。
SQL> select trim(both '*' from '***hello****') from dual;
TRIM(-----hello
SQL> select trim(trailing '*' from '***hello****') from dual;
TRIM(TRA--------***hello
SQL> select trim(leading '*' from '***hello****') from dual;
TRIM(LEAD---------hello****
注意：trimstring只能是⼀个字符。SQL> select trim(leading '*!' from '*!*!hello') from dual;select trim(leading '*!' from '*!*!hello') from dual*ERROR at line 1:ORA-30001: trim set should have only one character
5. INSTR函数（In-string）
INSTR函数确定搜索字符串在给定字符串中的位置。它返回数字位置，如果搜索字符串不存在，则该函数返回0。其语法为INSTR(sourcestring,search string,[search start position],[nth occurrence])。其中，后两个参数是可选的，search start position的默认值是1，即sourcestring的开头。nth occurrence的默认值是1，即第⼀次出现。
例如：SQL> select instr('1*3*5*7*9','*',-3,2) from dual;
INSTR('1*3*5*7*9','*',-3,2)--------------------------- 4
SQL> select instr('1*3*5*7*9','*') from dual;
INSTR('1*3*5*7*9','*')---------------------- 2
SQL> select instr('1*3*5*7*9','*',2) from dual;
INSTR('1*3*5*7*9','*',2)------------------------ 2
6. SUBSTR函数（Substring）
SUBSTR函数从给定源字符串中给定的位置开始，提取指定长度的⼦字符串。其语法为SUBSTR(source string,start position,[number ofcharacters to extrace])。最后⼀个参数可选，倘若没有指定，默认从start position到source string结尾的字符数。
例如：SQL> select substr('1*3*5*7',3) from dual;
SUBST-----3*5*7
SQL> select substr('1*3*5*7',3,2) from dual;
SU--3*
SQL> select substr('1*3*5*7',-3,2) from dual;
SU--5*
7. REPLACE函数
REPLACE函数⽤替换项取代源字符串中出现的所有搜索项。其语法为REPLACE(source string,search item,[replacement term])。replacement term默认为空字符。
例如：SQL> select replace('1*3*5*7','*','->') from dual;
REPLACE('1----------1->3->5->7
SQL> select replace('1*3*5*7','*') from dual;
REPL----1357
三、 数字函数1. 数字round函数
ROUND函数依据指定的⼩数精度对数值进⾏四舍五⼊运算。其语法为ROUND(source number,decimal precision)。decimal precision参数指定舍⼊的精度。默认是0，即对源数字四舍五⼊到整数。如果指定的⼩数精度n为正数，则要舍⼊的有效数字在⼩数点右边（n+1）个位置。如果n为负数，则要舍⼊的有效数字在⼩数点左边n个位置。
例如：SQL> select round(145.78,-1) from dual;
ROUND(145.78,-1)----------------150SQL> select round(145.78) from dual;
ROUND(145.78)-------------146
SQL> select round(145.78,1) from dual;
ROUND(145.78,1)---------------145.8
2. TRUNC函数（Truncate）
TRUNC函数依据指定的⼩数精度对数值进⾏截取运算。其语法为TRUNC(source number,decimal precision)。
例如：SQL> select trunc(145.78,-1) from dual;
TRUNC(145.78,-1)----------------140
SQL> select trunc(145.78) from dual;
TRUNC(145.78)-------------145
SQL> select trunc(145.78,1) from dual;
TRUNC(145.78,1)---------------145.7
3. MOD函数（Modulus）
MOD函数返回除法运算的余数。
例如：SQL> select mod(5,2) from dual;
MOD(5,2)---------- 1
SQL> select mod(5.2,2.2) from dual;
MOD(5.2,2.2)------------ .8
四、 ⽇期函数1. MONTHS_BETWEEN函数
MONTHS_BETWEEN函数返回两个⽇期参数之间⽉数的数值。其语法为MONTHS_BETWEEN(date1,date2)。该函数计算date1和date2之间⽉份的差值（每⽉31天），即date1-date2。返回值可能由整数和⼩数部分组成。其中，整数表⽰这两个⽇期之间相差的⽉数。⼩数部分表⽰剩余的天数和时间，以31天的⽉份为基础。
ORACLE_分析函数大全
ORACLE_分析函数大全
Oracle分析函数是一种高级SQL函数，它可以在查询中实现一系列复杂的分析操作。这些函数可以帮助我们在数据库中执行各种数据分析和报表生成任务。本文将介绍Oracle数据库中的一些常用分析函数。
1.ROW_NUMBER函数：该函数为查询结果中的每一行分配一个唯一的数字。可以用它对结果进行排序或分组。例如，可以使用ROW_NUMBER函数在结果集中为每个员工计算唯一的编号。
2.RANK和DENSE_RANK函数：这两个函数用于计算结果集中每个行的排名。RANK函数返回相同值的行具有相同的排名，并且下一个排名值将被跳过。DENSE_RANK函数类似，但是下一个排名值不会被跳过。
G和LEAD函数：LAG函数返回结果集中指定列的前一个（上一个）行的值，而LEAD函数返回后一个（下一个）行的值。这些函数通常用于计算增长率或发现趋势。
4.FIRST和LAST函数：这两个函数用于返回结果集中分组的第一个和最后一个行的值。可以与GROUPBY子句一起使用。
5.CUME_DIST函数：该函数用于计算给定值的累积分布。它返回值的累积分布在结果集中的位置（百分比）。
6.PERCENT_RANK函数：该函数用于计算结果集中每个行的百分位数排名。它返回值的百分位数排名（0到1之间的小数）。
7. NTILE函数：该函数用于将结果集分成指定数量的桶（Bucket），并为每个行分配一个桶号。通常用于将数据分组为更小的块。 8.LISTAGG函数：该函数将指定列的值连接成一个字符串，并使用指定的分隔符分隔每个值。可以用它将多个值合并在一起形成一个字符串。
9.AVG、SUM、COUNT和MAX/MIN函数：这些是常见的聚合函数，可以在分析函数中使用。它们用于计算结果集中的平均值、总和、计数和最大/最小值。
以上只是Oracle数据库中的一些常用分析函数。还有其他一些分析函数，如PERCENTILE_CONT、PERCENTILE_DISC等可以用于更高级的分析计算。通过使用这些函数，我们可以更轻松地进行数据分析、数据报表生成和业务决策。
oracle常用函数汇总（分享）
oracle常⽤函数汇总（分享）
算术运算符：+ - * / 可以在select 语句中使⽤
连接运算符：|| select deptno|| dname from dept;
⽐较运算符：> >= = != < <= like between is null in
逻辑运算符：not and or
集合运算符： intersect ，union， union all， minus
对应集合的列数和数据类型相同 查询中不能包含long 列
列的标签是第⼀个集合的标签
使⽤order by时，必须使⽤位置序号，不能使⽤列名
例：集合运算符的使⽤：
复制代码 代码如下:
intersect ，union， union all， minus
select * from emp intersect select * from emp where deptno=10 ;
select * from emp minus select * from emp where deptno=10;
select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复⾏
select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复⾏
TO_DATE格式(以时间:2007-11-02 13:45:25为例)
Year:
yy two digits 两位年 显⽰值:07
yyy three digits 三位年 显⽰值:007
yyyy four digits 四位年 显⽰值:2007
Month:
mm number 两位⽉ 显⽰值:11
mon abbreviated 字符集表⽰ 显⽰值:11⽉,若是英⽂版,显⽰nov
oracle常用函数
oracle常用函数
1.按字段排序时会出现空字段在前的情况，处理方法：order by
datatime desc nulls last
2.Left join左关联right join 右关联inner join 两个表的关联的字段数据相等的那些条数
据才关联出来
3.分组排序：
rank()over(partitionbysubstr('1'||t.BUSIDEPARTCODE,0,1)orderby t2.cost_rate descnullslast)
4.排序：ROW_NUMBER() OVER (ORDERBY column_name
DESC)
5.分组求数量：count(字段a)over (partition by 字段a) 根据字段a分组求同个字段a的有多少数
据量
6.求占百分比的分析函数：
create table test
(
name varchar(20),
kemuvarchar(20),
score number
);
insert into test values('testa','yuwen',10);
insert into test values('testa','英语',100);
insert into test values('testb','yuwen',60);
insert into test values('testb','yuwen',120);
insert into test values('testc','yuwen',40);
select name,
score,
ratio_to_report(score) over() as "占所有科目的百分比", ratio_to_report(score) over(partition by kemu) as "占各科目的百分比"
from test ;
7.Sql函数
avg函数：计算查询中某一特定字段资料的算术平均值。
ORACLE常用函数汇总
ORACLE常用函数汇总
Oracle是一种常用的关系型数据库管理系统，它提供了许多方便的函数来处理和操作数据。以下是一些常用的Oracle函数的汇总：
1.字符串函数：
-CONCAT：用于连接两个字符串。
-UPPER：将字符串转换为大写。
-LOWER：将字符串转换为小写。
-LENGTH：返回字符串的长度。
-SUBSTR：返回字符串的子串。
2.数值函数：
-ABS：返回数字的绝对值。
-ROUND：返回一个四舍五入的数字。
-TRUNC：返回数字的整数部分。
-MOD：返回两个数的余数。
3.日期函数：
-SYSDATE：返回当前日期和时间。
-TO_CHAR：将日期转换为特定的格式。
-TO_DATE：将字符串转换为日期。
-ADD_MONTHS：在给定的日期上添加指定的月份。 -MONTHS_BETWEEN：返回两个日期之间的月份数。
4.聚合函数：
-SUM：计算指定列的总和。
-AVG：计算指定列的平均值。
-COUNT：计算指定列的行数。
-MAX：返回指定列的最大值。
-MIN：返回指定列的最小值。
5.条件函数：
-NVL：用于将空值替换为指定的值。
-NULLIF：如果两个表达式相等，则返回空值；否则返回第一个表达式的值。
-COALESCE：返回第一个非空表达式的值。
-CASE：对满足条件的表达式进行分支处理。
6.集合函数：
-UNION：将两个结果集合并成一个结果集。
-INTERSECT：返回两个结果集的交集。
-MINUS：返回第一个结果集中不在第二个结果集中的行。
7.窗口函数：
-ROW_NUMBER：为查询结果中的每一行分配一个唯一的行号。 -RANK：根据指定的列对结果进行排序，并为相同值的行分配相同的排名。
-DENSE_RANK：根据指定的列对结果进行排序，并为相同值的行分配连续的排名。
8.系统函数：
-USER：返回当前用户的名称。
-CURRENT_TIMESTAMP：返回当前的日期和时间。
详细整理Oracle中常用函数
详细整理Oracle中常⽤函数⽬录⼀、字符串函数⼆、数字函数三、⽇期函数四、转换函数五、其它单⾏函数六、聚合函数oracle数据库中主要使⽤两种类型的函数：
1. 单⾏函数：操作⼀⾏数据，返回⼀个结果常⽤的单⾏函数有：字符串函数：对字符串操作。数字函数：对数字进⾏计算，返回⼀个数字。⽇期函数：对⽇期和时间进⾏处理。转换函数：可以将⼀种数据类型转换为另外⼀种数据类型。2. 聚合函数(多⾏函数、分组函数、组函数)：操作多⾏数据，并返回⼀个结果。⽐如 SUM
⼀、字符串函数
字符函数接受字符参数，这些参数可以是表中的列，也可以是⼀个字符串表达式。
常⽤的字符函数：
函数说明ASCII(X)返回字符X的ASCII码CONCAT(X,Y)连接字符串X和YINSTR(X,STR[,START][,N)从X中查找str，可以指定从start开始，也可以指定从n开始LENGTH(X)返回X的长度LOWER(X)X转换成⼩写UPPER(X)X转换成⼤写LTRIM(X[,TRIM_STR])把X的左边截去trim_str字符串，缺省截去空格RTRIM(X[,TRIM_STR])把X的右边截去trim_str字符串，缺省截去空格TRIM([TRIM_STR FROM]X)把X的两边截去trim_str字符串，缺省截去空格REPLACE(X,old,new)在X中查找old，并替换成newSUBSTR(X,start[,length])返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
上⾯各函数的例⼦：
⽰例⽰例结果SELECT ASCII('a') FROM dual;97SELECT CONCAT('Hello','world') FROM dual;HelloworldSELECT INSTR('Hello world','or') FROM dual;8SELECT LENGTH('Hello') FROM dual;5SELECT LOWER('Hello') FROM dual;helloSELECT UPPER('hello') FROM dual;HELLOSELECT LTRIM('=Hello=','=') FROM dual;Hello=SELECT RTRIM('=Hello=','=') FROM dual;=HelloSELECT TRIM('='FROM'=Hello=') FROM dual;HelloSELECT REPLACE('ABCDE','CD','AAA')FROM dual;ABAAAESELECT SUBSTR('ABCDE',2,3) FROM dual;BCD⼆、数字函数
oracle常用函数 2
Oracle 常用SQL函数
单行函数包括，字符、数字、日期、转换和普通函数。
字符函数举例：
•全小写 LOWER('SQL Course') sql course
•全大写 UPPER('SQL Course') SQL COURSE
•首字母大写 INITCAP('SQL Course') Sql Course
拼接 CONCAT('Good', 'String') GoodString
取子串 SUBSTR('String',1,3) Str
求长度 LENGTH('String') 6
数字函数举例：
ROUND -- 对数值进行四舍五入操作
TRUNC -- 截断。注意截断和舍入的位数，可以是负数。
ROUND (45.923, 2) 45.92
ROUND (45.923, 0) 46
ROUND (45.923, -1) 50
TRUNC (45.923, 2) 45.92
TRUNC (45.923) 45
TRUNC (45.923, -1) 40
MOD -- 返回两数相除的余数
MOD(1600, 300) -- 100
日期函数：
•Oracle将日期按照内部格式将日期存成以下七个字节-世纪、年、月、日、
时、分、秒。•在Oracle中日期缺省的显示格式为：DD - MON - YY 。•可以使
用系统变量 SYSDATE 获取系统时间•。查看 SYSDATE时，可以使用虚拟表DUAL。
举例：
求两个日期间有多少个月MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
1.9774194
在日期上加指定的月数ADD_MONTHS('11-JAN-94',6)'11-JUL-94'
Oracle数据库---Oracle常用函数
1. 求最大值----max()
Max()一般用于求某一个数值列的最大值。
Max()引用与字符型，获取规则为如果是中文，则获取中文拼音字母的最后一位。如果有英文，英文永远排在中文前面。
Max()用于日期，则获取的是最晚的日期。
2. 求最小值----min()
3. 求平均值----avg()
只能用于数值型 4. 求和----sum()
只能用于数值型
5. 统计记录数---count()
5.1统计单列
当列值为空时，计数为0，当列值为非空时，计数为1。
因为新插入一行id不为空，而age为空。
5.2统计所有列
5.3 count(1)，count(1)和count(*)统计结果相同，每条记录都记录为1。
6. 空值处理----nvl() Nvl(表达式，新值/表达式)
数据库中偶尔会出现空值，nvl()函数可以判断前面那个表达式是否为空，如果为空，则返回第二个参数的值，如果不为空，则返回第一个参数表达式的值。意思是说，如果为空，返回不会为空，而是用后面的那个赋值进去了
7. 结果集的行号----rownum()
就是可以对每一行进行顺序编号了。
从上可以看出，是先获得行号的，然后再进行排序。
8.强制转换数据类型---cast()
8. Oracle中的运算
8.1数学运算：+,-,*,/四种
8.2逻辑运算
=：用于比较是否相等
<>:为不等于 !=：也是为不等于
NOT:取反操作
AND:布尔值的与操作
OR：布尔值的或操作。
9. Oracle的特殊判式
9.1between
9.2模糊测试----like
对于like判式，最常用的通配符是‘%’
匹配单个字符‘_’
9.3 空值判断----in null
9.4 存在性判断----exists()
Where Exists(XXX)
只要xxx存在，where前面就执行。
110个oracle常用函数总结.
1. ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(A A,ascii(a
a,ascii(0 zero,ascii( space from dual; A A ZERO SPACE --------- --------- --------- ---------
65 97 48 32 2. CHR 给出整数,返回对应的字符; SQL> select chr(54740 zhao,chr(65
chr65 from dual; ZH C -- - 赵 A 3. CONCAT 连接两个字符串; SQL> select
concat('010-','88888888'||'转23 高乾竞电话' from dual; 高乾竞电话 ---------------- 010-********转23 4. INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL>
select initcap('smith' upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr(oracle
traning,ra,1,2 instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度;
SQL> select name,length(name,addr,length(addr,sal,length(to_char(sal from
gao.nchar_tst; NAME LENGTH(NAME ADDR LENGTH(ADDR SAL
LENGTH(TO_CHAR(SAL ------ ------------ ---------------- ------------ --------- -------------------- 高乾竞 3 北京市海锭区 6 9999.99 7 7.LOWER 返回字符串,并将所有的字符小写
oracle常用函数简介
1. 字符串函数
• CONCAT: 连接两个字符串。
• SELECT CONCAT('Hello', ' World') FROM dual; -- 结果: 'Hello World'
• SUBSTR: 提取子字符串。
• SELECT SUBSTR('Oracle', 2, 3) FROM dual; -- 结果: 'rac'
• INSTR: 返回子字符串在字符串中的位置。
• SELECT INSTR('Oracle Database', 'Database') FROM dual; -- 结果: 8
• LENGTH: 返回字符串的长度。
• SELECT LENGTH('Oracle') FROM dual; -- 结果: 6
• REPLACE: 替换字符串中的子字符串。
• SELECT REPLACE('Oracle Database', 'Database', 'RDBMS') FROM dual; -- 结果:
'Oracle RDBMS'
• UPPER: 将字符串转换为大写。
• SELECT UPPER('oracle') FROM dual; -- 结果: 'ORACLE'
• LOWER: 将字符串转换为小写。
• SELECT LOWER('ORACLE') FROM dual; -- 结果: 'oracle'
• TRIM: 去除字符串两端的空格或指定字符。
• SELECT TRIM(' Oracle ')
FROM dual; -- 结果: 'Oracle'
2. 数值函数
• ROUND: 对数值进行四舍五入。
• SELECT ROUND(123.456, 2) FROM dual; -- 结果: 123.46
• TRUNC: 截取数值的小数部分。
• SELECT TRUNC(123.456, 2) FROM dual; -- 结果: 123.45
oracle常用函数trunc用法
oracle常用函数trunc用法
Oracle中的TRUNC函数有多种用法，它主要用于截取日期、数值和字符串类型的数据。下面将介绍常用的几种TRUNC函数的用法。
1.日期截取：
TRUNC函数可以从一个日期表达式中截取出指定的部分，如年、月、日、小时、分钟等。
- TRUNC(date, 'YEAR')：截取日期表达式的年份部分，将月、日、时、分和秒均截取掉，返回结果为该年的1月1日的日期。例如：SELECT
TRUNC(SYSDATE, 'YEAR') FROM DUAL;
- TRUNC(date, 'MONTH')：截取日期表达式的月份部分，将日、时、分和秒截取掉，返回结果为该月的1日的日期。例如：SELECT
TRUNC(SYSDATE, 'MONTH') FROM DUAL;
- TRUNC(date, 'DD')：截取日期表达式的日部分，将时、分和秒截取掉，返回结果为当天的日期。例如：SELECT TRUNC(SYSDATE, 'DD')
FROM DUAL;
2.数值截取：
TRUNC函数可以截取出数值的整数部分或指定的小数位数，并将小数部分舍去。
- TRUNC(number)：截取出数值的整数部分，例如：SELECT
TRUNC(3.14) FROM DUAL; -- 结果为3；
3.字符串截取： TRUNC函数还可以用于截取字符串的指定长度。
- TRUNC(string, n)：截取字符串的前n个字符。例如：SELECT
TRUNC('Hello world', 5) FROM DUAL; -- 结果为'Hello'；
以上是TRUNC函数的一些常用用法，下面再介绍一些更高级的用法：
4.日期计算：
TRUNC函数可以与其他日期函数结合使用，实现日期的计算。
- TRUNC(date, 'YYYY') + 1：计算出下一年的1月1日的日期。例如：SELECT TRUNC(SYSDATE, 'YYYY') + 1 FROM DUAL;