oracle常用函数（学习笔记）

最新推荐文章于 2024-04-20 00:26:31 发布
最新推荐文章于 2024-04-20 00:26:31 发布
阅读量361 收藏 5
点赞数 7
分类专栏： oracle 文章标签： oracle 学习 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Bee8523/article/details/131076560
版权

系统函数

单行函数

数值函数

  1. 绝对值函数—ABS(n)

    SELECT ABS(-1),ABS(1),ABS(0) FROM DUAL;
输出为 1 1 0

  2. 取余函数—MOD(n1,n2)

    SELECT MOD(5,2) FROM dual;
输出为 1

  3. 取整类函数
    向下取整—floor(n)

    SELECT FLOOR(3.98) FROM DUAL;
输出为 3

    向上取整—ceil(n)

    SELECT CEIL(3.1) FROM DUAL;
输出为 4

    四舍五入—round(n1,[n2])

    --第二个参数n2设置精度，即保留至小数点后n位且四舍五入小数点第n位
SELECT ROUND(3.14),
   	   ROUND(3.98),
       ROUND(3.1415926,6)
    FROM DUAL;
输出为 3 4 3.141593

    截断—trunc(n1,[n2])
    注意：trunc函数在日期函数中表示截断到日期之初。

    --截断函数不考虑四舍五入，默认情况下截断至整数部分
SELECT TRUNC(3.98,1) FROM DUAL;
输出为 3.9

  4. 判断正负—sign(n)

    SELECT SIGN(-8),SIGN(8),SIGN(0) FROM DUAL;
输出为 -1 1 0

  5. 次方、平方—power(n1,n2)

    SELECT POWER(4,1/2) FROM DUAL;
输出为 2

字符函数

注意：oracle中的所有字符要用单引号修饰，在Mysql中可以使用单引号也可以使用双引号。

  1. 转大写—upper(str)

  2. 转小写—lower(str)

  3. 首字母大写—initcap(str)

    SELECT UPPER('hhhh'),LOWER('GHHH'),INITCAP('hello-world') FROM dual;
输出为 HHHH ghhh Hello-World
首字母转大写中注意：只有在一串完整的字符串中会首字母大写，如果中间出现分隔符就不再是一个字符串。

  4. 返回长度类
    返回字符的长度—length(str)

    SELECT LENGTH('hhh'),LENGTH('我') FROM dual;
输出为 3 1

    返回字节的长度—lengthb(str)

    SELECT LENGTHB('hhh'),LENGTHB('我') FROM dual;
输出为 3 2

    注意：中文一个字符占两个字节，英文一个字符占一个字节

  5. 去除类
    去除两端字符—trim(s from str)

    --去除字符串两端的字符s
SELECT TRIM('s' FROM 'sdfsdafasdfdsss') FROM dual;
输出为 dfsdafasdfd

    去除两端空格—trim(str)

    SELECT TRIM('   sdfsdafasdfdsss    ') FROM dual;
输出为 sdfsdafasdfdsss

    去除右边字符或空格—Rtrim(str1,[str2])
    去除左边字符或空格—Ltrim(str1,[str2])

    SELECT TRIM('  ASAS   '),
       LTRIM('  ASAS   '),
       RTRIM('  ASAS   ')
 FROM DUAL;
 输出为 ASAS   ASAS[ 空格 ]    [ 空格 ]ASAS

    SELECT RTRIM('ABAAAAAABBBPPPSSS','S') FROM DUAL;
输出为 ABAAAAAABBBPPP

  6. 填充类
    左填充—-Lpad(str1,n2,str3)
    右填充—-Rpad(str1,n2,str3)

    str1为源字符串，n2表示填充后的位数，str3为想要填充的字符
SELECT RPAD('AAA',5,'*') FROM DUAL;
输出为 AAA**
-- 给emp表中所有的员工左右两边都填充两个*   **smith**
SELECT rpad(LPAD('smith',7,'*'),9,'*') FROM dual;

    较复杂功能实现：查询显示emp表中的员工姓名列，并在姓名前后加两个*
SELECT rpad(lpad(ename,LENGTH(ename)+2,'*'),LENGTH(ename)+4,'*') FROM emp;

  7. 获取字符位置—instr(str1,str2,[,n3,n4])

    str1为源字符串，str2为需要查找的字符，n3表示从哪里找，n4表示找第几次出现的。（默认情况下不写后两个参数时，表示从源字符串的开头找第一次出现要查找字符的位置）
SELECT INSTR('ABCDEFGEFDDASSS','S',1,2) FROM DUAL;
输出 14

  8. 截取字符串—substr(str1,n2[,n3])

    str1为源字符串，n2表示从哪个位置截取，n3表示截取的长度（默认不写n3的情况下，截取到最后面）
SELECT SUBSTR('HELLOWORLD',2,3) FROM DUAL;
输出为 ELL
SELECT SUBSTR('HELLOWORLD',2) FROM DUAL;
输出为 ELLOWORLD

  9. 替换类
    整体替换—replace(str1,str2[,str3])

    str1为源字符串，str2为要整体替换的字符，str3为整体替换的内容，默认没有写str3的情况下替换为空值。
SELECT REPLACE('HELLOWORLD','HELLO','789') FROM DUAL;
输出为 789WORLD

    逐字替换—translate(str1,str2,str3)

    str1为源字符串，str2为要替换的字符，str3为要替换的内容。（逐字替换中，如果str3的位数小于str2的位置则默认补空进行一一对应）
SELECT TRANSLATE('HELLOWORLD','HELLO','7') FROM DUAL;
输出为 7WRD 即用7替换H，空值替换E、L、O

  10. 拼接类
    函数拼接—concat(str1,str2)
    符号拼接—||

    注意：CONCAT函数中的参数只能是两个，因此如果需要拼接多个字符串的时候常常用符号拼接。
SELECT CONCAT('HELLO','WORLD') FROM DUAL;
SELECT 'HELLO' || 'WORLD' FROM DUAL;
输出结果一致

  11. 转半角/全角
    转半角—to_single_byte(str)
    转全角—to_multi_byte(str)

    --全角转半角
SELECT to_single_byte('ＨＨＨ') FROM DUAL;
--半角转全角
SELECT to_multi_byte('HHH') FROM DUAL;

转换函数

  1. 字符转ASCII码—ASCII(str)

    SELECT ASCII('A') FROM dual;
输出为 65

  2. 数字转字符(按照ASCII码)—chr(n)

    SELECT CHR(65) FROM DUAL;
输出为 A

  3. 字符型转数值型—to_number(str)

    SELECT to_number('123') FROM dual;

  4. 字符型转日期型—to_date(str,‘格式’)

    --日期格式：YYYY-MM-DD HH:MI:SS；q:季度；day/dy/d:周；hh、hh12:12小时制；hh24 :24小时制
--am 是上午  pm是下午
SELECT to_date('2023-6-7 下午 09:09:09','YYYY-MM-DD pm HH:MI:SS') FROM dual;
输出为 2023/6/7 21:09:09

  5. to_char

    --1、按照日期格式提取字符
语法：to_char(dt,'格式');
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; --输出为2023
SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; --输出为2，第二季度
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; --输出为星期三
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; --输出为 4 (每周日为第一天，周一为第二天，以此类推，周三为第四天)
--2、格式化数字
语法：to_char(N,'格式') 格式化数字 --9 占位符 0占位符  $美元 L 本地货币（占位符要和数字一一对应）
SELECT TO_CHAR(1800,'L9999.999') FROM DUAL; --输出为￥1800.000
 SELECT to_char(5000,'$0000.000') FROM dual; --输出为 $5000.000
--3、转字符
语法：to_char() --可以转为字符
SELECT TO_CHAR(SYSDATE) FROM DUAL; --输出为07-6月 -23

日期函数

  1. 返回当前系统时间—sysdate

    SELECT SYSDATE FROM dual; -- 返回系统当前时间
SELECT SYSDATE + 1 FROM DUAL; -- 一天后
SELECT SYSDATE + 1 / 24 FROM dual; -- 一小时后
SELECT SYSDATE + 1 / 24 / 60 FROM DUAL;-- 一分钟后
	
常用到关键字 INTERVAL 可以定义年月日、时分秒 用以进行日期的加减
SELECT SYSDATE + Interval '1' YEAR  FROM dual; --一年后
SELECT SYSDATE + Interval '1' MONTH FROM dual; --一个月后
SELECT SYSDATE + Interval '1' DAY FROM dual; --一天后
SELECT SYSDATE + Interval '1' HOUR FROM dual; --一小时后
SELECT SYSDATE + Interval '1' MINUTE FROM dual; --一分钟后

  2. 将字符格式转换为日期格式—date

    SELECT DATE '2022-02-03' FROM dual; --输出为2022/2/3
--求emp表中1980年1月1日后入职的信息
SELECT * FROM EMP WHERE HIREDATE > DATE '1980-01-01';

  3. 给dt加上n个月—add_months(dt,n)

    --n可正可负
SELECT add_months(SYSDATE,-1) FROM dual; --输出为2023/5/7 10:30:50
SELECT add_months(SYSDATE,5) FROM dual; --输出为2023/11/7 10:31:58

  4. 返回两个日期之间相差的月数—months_between(dt1,dt2)

    SELECT MONTHS_BETWEEN(SYSDATE,DATE'1949-10-01') / 12 FROM DUAL; --输出为73.683980112505

  5. 返回下个星期几的日期—next_day(dt,‘星期几’/对应得数字)

    SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual; --输出2023/6/13 10:38:35
SELECT NEXT_DAY(SYSDATE,7) FROM dual; --输出2023/6/10 10:38:50

  6. 返回日期所在月的最后一天—last_day(dt)

    SELECT LAST_DAY(SYSDATE) FROM DUAL; --输出为2023/6/30 10:39:21

  7. 截断到日期之初—trunc(dt,‘格式’)

    SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --输出为2023/1/1
SELECT TRUNC(SYSDATE,'Q') FROM DUAL; --输出为2023/4/1(季度之初)
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --输出为2023/6/1
SELECT TRUNC(SYSDATE,'DAY') FROM DUAL; --输出为2023/6/4
SELECT TRUNC(SYSDATE,'DD') FROM DUAL; --输出为2023/6/7

通用函数

  1. 返回客户端的字符集—USERENV(‘LANGUAGE’

  2. nvl(参1,参2)

    参数1为空返回参数2  否则返回参数1 
SELECT NVL(NULL,2),NVL(1,2) FROM DUAL; --输出为 2 1
--多用于处理表中的有空值的列
SELECT EMP.*,NVL(SAL,0) + NVL(COMM,0) FROM EMP; --计算工资+奖金(由于空值不能参与计算，所有要用nvl)

  3. NVL2(参1,参2,参3)

    参数1为空返回参数3 否则返回参数2

  4. 去重—DISTINCT

    当 DISTINCT 后面跟多个列的时候，会进行复合去重，会将多个列当作一个整体，只有全部相同才会去重.
注意：在sql优化中，多用exists代替distinct。

  5. 判断

    --1、case when(有两种，我最喜欢用的是第二种)
语法：case  WHEN 条件1 THEN  值1 
       		WHEN 条件2 THEN  值2
         	..........
      	 	[ELSE 值N]
     END
--2、decode
语法：decode(列,列中值1,值1,列中值2,值2,......值N)

    注意：在sql优化中，多用decode函数，使用该函数可以避免重复扫描相同记录或重复连接形同的表。

  6. nullif(参1,参2)

    判断两个参数是否相等 相等返回空  不等返回参数1
常用于除数运算中，避免除数为0
SELECT SAL / NULLIF(COMM,0) FROM EMP;

聚合函数

  1. MAX(列)、MIN(列)、AVG(列)、SUM(列)

    分别是求最大值、最小值、平均值、求和类的聚合函数
进行分组后的select查询中，可以使用聚合函数。

  2. COUNT

    count(*)、count(列)、count(1) 都用来计算行数，重点在区别上。
1、最常见的角度，执行结果来看，count(1)和count(*)都不会过滤空值，而count(列)会过滤空值，通常在做表的左外连接中，要根据不同需求去使用count。
2、从执行效率看
	如果某列为主键，那么count(列)效率优于count(1)，否则反之；
	如果表中只有一列，count(*)是最优的；
	如果表中有多列，并且不存在主键，则count(1)优于count(*)。

分析函数（窗口函数）

  1. 不考虑重复值，序号连续—ROW_NUMBER()OVER([PARTITION BY] ORDER BY)

    先介绍ROWNUM，ROWNUM是Oracle数据库中的一个伪列，用于返回结果集中每行的唯一编号。
注意：不能直接大于某个值，也不能直接等于一个大于1的值。
如果想要查询第5到第10行的记录，在Mysql中可以使用limit实现，但在oracle中需要把这个伪列通过子查询变为一个真实的列再去查询。
SELECT * FROM (SELECT e.*,ROWNUM r FROM emp e) t WHERE t.r BETWEEN 5 AND 10;

    --按照部门号分组显示序号且按照工资的降序排列
SELECT EMP.*,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS R FROM EMP；
结果见下图

在这里插入图片描述

  1. 考虑重复值 序号不连续—RANK()OVER([PARTITION BY] ORDER BY)

    SELECT EMP.*,RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC NULLS LAST ) FROM EMP ;
结果见下图 (重复的两行并列第一，之后排名不连续)

    在这里插入图片描述

  2. 考虑重复值 序号连续—DENSE_RANK()OVER([PARTITION BY] ORDER BY)

    SELECT EMP.*,dense_RANK()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC NULLS LAST ) FROM EMP ;
结果见下图 (重复的两行并列第一，之后的排名连续)

    在这里插入图片描述
    分析函数注意：
    1、over 之后的括号里可以什么都不写，如果写了partition by意味着分组统计，如果加了order by 是累计统计。
    2、如果考虑重复值则用rank类，如果不考虑重复值则考虑row_number。
    3、记忆技巧：RANK()OVER 1134 DENSE_RANK()OVER 1123。

  3. 位移分析函数

    1、返回当前行后面的指定行数的值(我的理解是基于当前列向上推n行并生成新的列，下面默认补空值，可以自己定义)
语法：LEAD(列[,数[,值]])OVER([PARTITION BY 列]ORDER BY 列) 
SELECT EMP.*,LEAD(SAL,5,666)OVER(ORDER BY SAL) FROM EMP;
结果见下图

    在这里插入图片描述

    2、返回当前行前面的指定行数的值(我的理解向下推)
语法：LAG(列[,数[,值]])OVER([PARTITION BY 列]ORDER BY 列)
SELECT EMP.*,LAG(SAL,2,888)OVER(ORDER BY SAL) FROM EMP;
结果见下图

    在这里插入图片描述

自定义函数

函数是通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中，函数提供一个返回值，返回给使用者。

语法：create or replace function 函数名称 [(参数)]
	  return 返回值类型
	  is
	   声明变量部分
	  begin
	   函数主体部分
	   return 返回值
	  [exception]
	   异常部分
	  end
调用：1、select 函数名() from dual;
	 2、程序块调用
	 	BEGIN
	 	DBMS_OUTPUT.PUT_LINE(函数名());
	 	END;

函数与存储过程的区别：可以理解函数就是存储过程的一种，存过可以定义返回类型，也可以不定义返回类型，可以返回多个参数，但是函数在申请时必须定义返回类型，并且程序块中必须定义return语句，函数也不能独立执行，必须作为表达式的一部分调用。

牛刀小试~
关注
  • 7
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 7
    评论
专栏目录
Oracle函数手册.zip
09-13
Ora9iSQL参考手册 oracle函数大全 分类显示 Oracle函数大全 Oracle函数手册 ORACLE九阴真经 oracle知识库 SQLCodes Oracle错误代码与消息解释 SQL语言参考大全
oracle最全函数大全
11-08
oracle最全函数大全(分析函数-聚合函数-转换函数-日期型函数-字符型函数-数值型函数-其他函数
Oracle函数大全三十五：MOD函数
最新发布
yixiaobing的博客
04-20 518
请注意，虽然MOD函数的基本功能在各种版本的Oracle数据库中都是相同的，但在具体使用时，可能会因为数据库版本、系统环境等因素而略有差异。MOD函数在Oracle数据库处理、编程和计算中非常有用，特别是在需要得到两个数相除后的余数的情况下。例如，在数据处理中，可以使用MOD函数来识别数据的周期性模式，或者在编程中用于实现循环、条件判断等逻辑。这里需要注意的是，如果除数为0，函数会直接返回错误，因为在数学中除数不能为0。在Oracle中，MOD函数是一个求余数的函数，其作用是返回两个数值相除后的余数。
oracle函数大全中文api文档
11-07
里面包含oracle所有函数介绍，可以随时随地查找。里面包含oracle所有函数介绍，可以随时随地查找。
oracle函数大全中文
04-10
oracle函数大全中文,数值型行数，字符型函数，日期函数，转换函数，聚组函数，分析函数，其他函数，
ORACLE内置函数使用手册
09-02
Oracle内置函数手册，包括函数的参数类型定义、参数说明以及功能说明和返回值类型，还有简单的示例，便于大家快速查询。
Oracle 9i 分析函数参考手册
09-27 938
Oracle 9i 分析函数参考手册 AVG ,CORR,COVAR_POP ,COVAR_SAMP, COUNT ,CUME_DIST ,DENSE_RANK ,FIRST ,FIRST_VALUE ,LAG,LAST ,LAST_VALUE,LEAD ,MAX,MIN ,NTILE ,PERCENT_RANK,PERCENTILE_CONT ,PERCENTILE_DISC ,RANK ,RA
oracle函数手册
五谷杂粮
06-08 284
SQL中的单记录函数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给出整数,返回对应的...
oracle函数大全
12-21
Oracle工具书，包含了Oracle所有的函数及函数使用方法，可作为Oracle的API文档，帮助你随时查询函数方法及使用规则
oracle 函数大全 参考函数 手册 速查 chm格式
03-15
oracle 函数大全 参考函数 手册 速查 chm格式。。。
Oracle_分析函数参考手册
01-26
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行
oracle 分析函数学习笔记
06-12
分析函数是oracle中强大的功能,附件是分析函数学习笔记
Oracle高级操作函数学习笔记
12-19
oracle高级操作：分支判断函数，集合操作，排序函数，聚合函数……
Oracle学习笔记.pdf
09-11
oracle学习笔记，包含所有oracle概念，包，函数，oracle的组成，oracle的plsq，oracle的存储过程，oracle的事务等
Oracle学习笔记(一)
12-15
2“AS”符号oracle：select 列名 as 新列名 from 表名 （空格，不能有as）新表名sql：select 列名 as 新列名 from 表名T as（as可有可无） 新表名 二.数据排序与数据过滤查询与sql相同 in ,like, is null,between ...
oracle数据库学习笔记
09-21
oracle学习笔记；oracle DML数据库操作语言；oracle常用函数使用方法及使用例子，存储过程等的创建
Oracle常用函数
08-04
Oracle常用函数包括字符串函数、ASCII函数和日期函数。 字符串函数中，常用的函数包括INSTR函数，它用于在字符串中查找字符集合的位置，并可以指定查找的次数和起始位置。[1] ASCII函数用于返回字符串中第一个字符...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 7
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值