原创：oracle中单行函数介绍 <五>

最新推荐文章于 2022-02-07 01:37:02 发布
最新推荐文章于 2022-02-07 01:37:02 发布
阅读量182 收藏
点赞数
文章标签： 数据库 java
本文详细介绍了Oracle SQL中的单行函数，包括字符串、数字、日期时间等类型的函数及其应用示例，还讲解了数据类型转换及通用函数的使用方法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

在SQL中有两种函数一种是单行函数，一种是多行函数.在sql与pl/sql中都自带了很多类型的函数,比如有字符、数字、日期、转换和混合型等多种函数用于处理单行数据,因此这些都被称为单行函数.这些函数都可以被用于select、where和oder by等子句中.下面我们就来分析单行函数,在这里我列举了oracle中一些常用的单行函数进行操作.希望你所有收获.

Java代码 复制代码 收藏代码
  1. 1、字符串函数是oracle使用最广泛的一种函数.
  2. LOWER:小写
  3. UPPER：大写
  4. INITCAP：首字母大写
  5. CONCAT：连接
  6. SUBSTR：截取(参数，开始，数目)
  7. LENGTH：返回字符串的长度
  8. INSTR：（参数，字母）返回字母出现的位置
  9. LPAD：（参数,长度，在前补齐参数字母）
  10. |RPAD：（参数，长度,在后补齐参数字母）
  11. TRIM：
  12. REPLACE：（参数,参数[,参数]）:第一个参数操作数,第二是要查找的字符,第三个是替换的字符，如果没有第三个就删除查找的字符。
  13. //查询名称为scott的员工信息（不区分大小写）
  14. SQL>selectempno,ename,job,mgr,hiredate,sal,comm,deptnofromempwherelower(ename)='scott';
  15. SQL>selectempno,ename,job,mgr,hiredate,sal,comm,deptnofromempwhereupper(ename)='SCOTT';
  16. //查询员工信息把员工名称与工作连接在一起
  17. SQL>selectempno,concat(ename,job),mgr,hiredate,sal,comm,deptnofromemp;
  18. //查询员工信息把员工名称与工作连接在一起
  19. SQL>selectempno,concat(ename||'iswork:',job),mgr,hiredate,sal,comm,deptnofromemp;
  20. //查询员工名称中含有O字符的位置
  21. SQL>selectempno,instr(ename,'O'),job,mgr,hiredate,sal,comm,deptnofromemp;
  22. //查询员工名称中含有O字符的位置并且求出了员工名称字符长度
  23. SQL>selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptnofromemp;
  24. //在查询上面结果中过滤出员工工作从第三字母开始为ERK员工信息
  25. SQL>selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptnofromempwheresubstr(job,3)='ERK';
  26. 备注：当含有两个参数的时候,从开始位置直接到参数结束的结束为止
  27. //在效果同上的同时指明了截取个数为3
  28. SQL>selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptnofromempwheresubstr(job,3,3)='ERK';
  29. //查询员工信息薪资是10位位数不够在左部分补*填充
  30. SQL>selectempno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptnofromemp;
  31. //查询员工信息薪资是10位位数不够在右部分补*填充
  32. SQL>selectempno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptnofromemp;
  33. //查询员工信息把员工名称中含有S字符去除掉
  34. SQL>selectempno,TRIM('S'fromename),job,mgr,hiredate,10,comm,deptnofromemp;
  35. 等效于：
  36. SQL>selectempno,TRIM(both'S'fromename),job,mgr,hiredate,10,comm,deptnofromemp;
  37. //查询员工信息把员工名称中前面有S字符去除掉
  38. SQL>selectempno,TRIM(Leading'S'fromename),job,mgr,hiredate,10,comm,deptnofromemp;
  39. //等效于
  40. SQL>selectempno,LTRIM(ename,'S'),job,mgr,hiredate,10,comm,deptnofromemp;
  41. //查询员工信息把员工名称中后面有S字符去除掉
  42. SQL>selectempno,TRIM(trailing'S'fromename),job,mgr,hiredate,10,comm,deptnofromemp;
  43. 等效于：
  44. SQL>selectempno,RTRIM(ename,'S'),job,mgr,hiredate,10,comm,deptnofromemp;
  47. 2、数字函数：
  48. ROUND:四舍五入
  49. ROUND(45.926,2)45.93
  50. TRUNC:截断
  51. TRUNC(45.926,2)45.92
  52. MOD:求余
  53. MOD(1600,300)100
  54. ABS：绝对值
  55. CEIL：返回大于或等于value的最小整数
  56. FLOOR：返回小于或等于value的最大整数
  57. SQRT:返回value的平方根负数无意义。
  58. //四舍五入结果为46
  59. SQL>selectround(45.56)fromdual;
  60. //绝对值结果为45.56
  61. SQL>selectabs(-45.56)fromdual;
  62. //大于等于最小整数结果为-45
  63. SQL>selectceil(-45.56)fromdual;
  64. //小于等于最大整数结果为-46
  65. SQL>selectfloor(-45.56)fromdual;
  66. //求余数结果为300
  67. SQL>selectmod(1800,500)fromdual
  68. //截取的数的操作数是正数的情况下：只操作小数位结果为1800.11
  69. SQL>selecttrunc(1800.11111,2)fromdual;
  70. //截取的数的操作数是负数的情况下：操作的是小数点之前的位,把操作位小数点之前的位数全部改写成0.结果就是1000
  71. SQL>selecttrunc(1899.11111,-3)fromdual;
  76. 3、日期时间函数
  77. Oracle中的日期型数据实际含有两个值:日期和时间。默认的日期格式是DD-MON-RR.日期时间函数用来返回当前系统的日期和时间、以及对日期和时间类型的数据进行处理运算。
  79. add_months(date,count);在指定的日期上增加count个月
  80. last_day(date);返回日期date所在月的最后一天
  81. months_between(date1,dates);返回date1到date2之间间隔多少个月
  82. new_time(date,this’,’other’);将时间date从this时区转换成other时区
  83. next_day(day,’day’);返回指定日期或最后一的第一个星期几的日期,这里day为星期几
  84. sysdate();获取系统的当前日期
  85. current_timestamp();获取当前的时间和日期值
  86. round：日期的四舍五入
  87. trunc日期的截取
  89. 4、日期的数学运算：
  90. ?在日期上加上或减去一个数字结果仍为日期。
  91. ?两个日期相减返回日期之间相差的天数。
  92. ?可以用数字除24来向日期中加上或减去小时。
  94. //获取系统的当前时间显示的格式采用默认格式显示结果：07-4月-1111.15.38.390000上午+08:00
  95. SQL>selectcurrent_timestampfromdual;
  96. //获取系统的当前日期值显示结果：2011-4-711
  97. SQL>selectsysdatefromdual;
  98. //为当前日期加上3个月显示的结果：2011-7-711:18:36
  99. selectadd_months(sysdate,3)fromdual;
  100. //返回当前月的最后一天显示的结果：2011-4-3011:19:4
  101. selectlast_day(sysdate)fromdual;
  102. //返回两个日期之间的间隔月是几：结果为：4
  103. SQL>selectmonths_between(add_months(sysdate,4),sysdate)fromdual;
  104. //从GMT时区转换成AST时区的日期结果
  105. SQL>selectnew_time(sysdate,'GMT','AST')fromdual;
  106. //返回下一个星期一的日期值必须写成星期’几’
  107. SQL>selectnext_day(sysdate,'星期一')fromdual;
  112. 5、转换函数：
  113. 隐式转换：在运算过程中由系统自动完成的
  114. 显式转换：在运算过程中需要调用相应的转换函数实现。
  115. 隐式转换
  116. 转换前数据类型转换后数据类型
  117. Varchar2orcharnumber
  118. Varchar2orchardate
  119. numberVarchar2
  120. dateVarchar2
  122. 显式转换
  123. to_char(date,’format’):按照指定的格式format把数字或日期类型的数据转换成字符串
  125. 格式:
  126. 必须包含在单引号中而且大小写敏感。
  127. 可以包含任意的有效的日期格式。
  128. 日期之间用逗号隔开。
  129. 日期格式如下：
  130. 格式举例
  131. YYYY2011
  132. YEARTWOTHORUSANDANDFOUR
  133. MM01
  134. MONTHJULY
  135. MONJUL
  136. DYMON
  137. DAYMONDAY
  138. DD03
  140. //把当前日期转换成YYYY/MM/DD的格式
  141. SQL>selectto_char(current_timestamp,'YYYY/MM/DD')fromdual;
  142. //把当前日期转换成YYYY/MM/DDHH24/MI/SSAM的格式
  143. SQL>selectto_char(current_timestamp,'YYYY/MM/DDHH24/MI/SSAM')fromdual;
  144. //DD“of”MONTH
  145. SQL>selectto_char(current_timestamp,'YYYYDD"of"MONTHHH/MI/SSAM')fromdual;
  146. //把当数字按照$99,999这种方式返回字符串并且操作数的位数不能够大于5(即$后边的位数)位,否则话结果会是########
  147. SQL>selectto_char(11111,'$99,999')fromdual;
  149. to_number(char);把包含了数字格式的字符串转换成数字数据
  150. to_date(string,’format’);按照指定格式的format把字符串转换成日期数据,如果省略了foramt格式,那么就采用默认的日期格式(DD-MON-YY);
  152. //把当前字符串转换成日期
  153. SQL>selectto_date('2011-02-08','YYYY-MM-DD')fromdual;
  154. //求出两个日期之间相差的天数
  155. SQL>selectto_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD')fromdual;
  156. //求出两个日期之间相差的周次
  157. SQL>select(to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7fromdual;
  158. //对周次进行向上取整
  159. SQL>selectceil((to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7)fromdual;
  161. chartorowid(char);把字符串转换成rowid类型
  162. rowidtochar（x）;把rowid类型转换成字符类型数据
  164. 6、通用函数
  165. 这些函数适用于任何数据类型，同时也适用于空值：
  166. NVL(expr1,expr2)
  167. NVL2(expr1,expr2,expr3)
  168. NULLIF(expr1,expr2)
  169. COALESCE(expr1,expr2,...,exprn)
  171. nvl()
  172. 将空值转换成一个已知的值：
  173. ?可以使用的数据类型有日期、字符、数字。
  174. ?函数的一般形式:
  175. •NVL(commission_pct,0)
  176. •NVL(hire_date,'01-JAN-97')
  177. •NVL(job_id,'NoJobYet')
  178. //将comm为null替换成0
  179. SQL>selectempno,ename,job,mgr,hiredate,sal,nvl(comm,0),deptnofromemp;
  180. //在上面的基础上将日期为空替换成给定的日期
  181. SQL>selectempno,ename,job,mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptnofromemp;
  182. //在上面的基础上将job为空替换成redarmy
  183. SQL>selectempno,ename,nvl(job,'redarmy'),mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptnofromemp;
  184. //计算出员工在该月的工资=（薪资+奖金）
  185. SQL>selectempno,ename,job,mgr,hiredate,(nvl(sal,0)+nvl(comm,0))as"工资",deptnofromemp;
  187. NVL2(expr1,expr2,expr3):expr1不为NULL，返回expr2；为NULL，返回expr3。
  188. 相当于:expr1!=null?exrp2:expr3;
  189. //计算员工的工资
  190. SQL>selectempno,ename,job,mgr,hiredate,nvl2(comm,sal+comm,sal)as"工资",deptnofromemp;
  192. NULLIF(expr1,expr2):相等返回NULL，不等返回expr1
  193. //注意观察理解
  194. SQL>selectenameas"expr1",jobas"expr2",nullif(length(ename),length(job))fromemp;
  196. ?COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值。
  197. ?如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE。
  198. SQL>selectempno,ename,job,mgr,hiredate,coalesce(comm,sal,10000)as"salll",deptnofromemp;
  200. ?重点理解：单行函数可以嵌套。嵌套函数的执行顺序是由内到外。
  202. 条件表达式：IF-THEN-ELSE逻辑
  203. CASEexprWHENcomparison_expr1THENreturn_expr1
  204. [WHENcomparison_expr2THENreturn_expr2
  205. WHENcomparison_exprnTHENreturn_exprn
  206. ELSEelse_expr]
  207. END
  210. DECODE(col|expression,search1,result1
  211. [,search2,result2,...,]
  212. [,default])
  214. //为职位是Manager的员工发放5000元的奖金
  215. SQL>selectename,job,casejobwhen'MANAGER'thennvl(sal,0)+5000endas"工资"fromemp;
  217. //员工的工资
  218. SQL>selectename,job,
  219. 2casejobwhen'MANAGER'thennvl(sal,0)+5000+nvl(comm,0)
  220. 3elsenvl(sal,0)+nvl(comm,0)
  221. 4end
  222. 5fromemp;
  223. //改写成decode的写法
  224. SQL>selectename,job
  225. 2,decode(job,'MANAGER',nvl(sal,0)+5000+nvl(comm,0),
  226. 3'CLERK',nvl(sal,0)+nvl(comm,0)+200,
  227. 4nvl(sal,0)+nvl(comm,0))as"工资"
  228. 5fromemp;
  230. 以上内容版权归redarmy_chen所有,如需转载请附带出处,如有疑问请发送到redarmy_chen@qq.com

下载所需的sql请点击这里附件中下载

iteye_10189
关注
oracle中<>是什么意思,c++中vector<int>和vector<int*>的用法区别
weixin_32445895的博客
04-15 984
c++中vector<int>和vector<int*>的用法区别在使用STL容器(比如map、list、vector等)的时候，是用放一个对象还是放一个对象指针，即是用vector还是vector，这里的vector可以换成其他的容器，int可以换成其他基本类型，也可以自定义的数据结构或类。首先，要说明的是，这两种方式，怎么用都可以实现功能，把一组整型数放到容器里。先看看...
顾新湖的个人学习笔记之Oracle—— 二、表&表空间
guxinhu的博客
04-21 685
二、表&表空间 1、表空间 ①、表空间概述 Ⅰ、概念： ​ 表空间是Oracle数据库内部最高层次的逻辑储存结构，Oracle数据库即是由一个或多个表空间组成的 Ⅱ、要点： ​ 创建空间表需要 create tablespace 权限 Ⅲ、格式： ​ a、create tablespace < 表空间名 > ​ b、create temporary tablespace < 临时表空间名 > ​ c、create undo tablespace
ORACLE分页及&lt 和&gt的区别
燃烧的梦想是信仰
12-23 5920
select * from (select t.*, rownum rn from (select * from t_kpi_fault order by obj_id asc) t where rownum <= 20) where rn > 10; oracle 三层： 最内层排序 中间层最大条数 最外层最小条数 ====================================== &..
oracle <>
乘风晓栈的博客
10-30 1446
场景:数据库中两个表 表1  表2 1 . 其中表2是表1的一个附属表； 2 . 两个表中各有一个生成时间,表1中时间不变,表2中修改用户信息时会变； 3 . 查询sysdate-表1中时间>30且sysdate-表2中时间>30的用户会员号； 语句1： SELECT A.会员号   FROM  (SELECT t.*FROM 表1 twhere SYSDATE - TO...
原创： Oracle的远程数据库的连接和用户操作 <三>
redarmychen的专栏
04-12 5419
     在上面的课程中我们介绍了oracle的安装与在DOS中简单操作oracle命令,发现很多学员已经把我授课中的总结发表了,但为了能够使授课的内容比较全面后续我会继续上传,希望学员能所有得。  1，远程数据库的连接    --前提：1、在客户端程序必须安装oracle客户端程序            2、服务器端的防火墙必须关闭 因为oracle采用的是TCP/IP协议的。
Oracle基础学习之子查询
12-16
2，having：子查询会返回单行单列，同时表示要使用统计函数； 3，from：子查询返回多行多列数据（表结构）； 4，select：返回单行单列 （一般不使用）； 示例详解 where（进行数据行的筛选操作）： a:查询出低于公司...
oracle 包含‘’_Oracle笔试题库 附参考答案
weixin_32192681的博客
02-04 1705
1． 下列不属于ORACLE的逻辑结构的是(C)区段数据文件表空间2. 下面哪个用户不是ORACLE缺省安装后就存在的用户(A)A . SYSDBAB. SYSTEMC. SCOTTD. SYS3 下面哪个操作会导致用户连接到ORACLE数据库，但不能创建表(A)授予了CONNECT的角色，但没有授予RESOURCE的角色没有授予用户系统管理员的角色数据库实例没有启动数据库监听没有启动( )函数通...
Oracle SQL学习笔记
Record_Algorithm
02-07 3547
LAST_DAY®函数：返回指定r日期的当前月份的最后一天日期。
Oracle基操笔记（四）：DQL
J.R.Leonardo的博客
01-04 1152
本文包含大量实际操作，功能可通过索引找到 一、DISTINCT去重 二、WHERE过滤 三、IN范围内匹配 四、BETWEEN...AND...范围查询 五、LIKE模糊匹配 六、AND与OR多条件查询 七、ORDER BY排序 八、GROUP BY分组查询 九、ROWNUM限制查询结果行数 十、使用聚合函数查询 十一、连接查询 十二、子查询 十三、合并查询 十四、表和...
oracle入门10
王硕的专栏
04-17 187
PL/SQL编程 PL/SQL是oracle的专用语言,它对标准的SQL语言的扩展.SQL语句可以嵌套在PL/SQL语言中，并结合处理语句。 PL/SQL程序结构 使用了程序块的结构组织的代码.最简单的程序块是一种被称为”匿名块”的程序块,匿名块是指不会被oracle存储并且不能够重用程序块。 PL/SQL程序通常包括3部分:Declare部分、exception部分、Be...
oracle instr>0,ORACLE instr和substr函数用法
weixin_36350581的博客
04-05 195
悬赏园豆：20[已关闭问题]用pl/SQL写一个函数：传入的值是 {name}xiachufeng{worker_id}123456{email}xiachufeng@126.com 等以{}+value形式的一组有规则的字符要求根据{}中的内容得到value如果输入的{name} 得到xiachufeng输入{worker_id} 得到123456这个可以用instr 和substr函数做吧？？...
oracle amp lt是什么东西,Oracle 函数整理 <慢慢整理>
weixin_29471009的博客
04-03 270
Oracle 常用函数1、数学函数:◆ round(n,[m]) 该函数用于执行四舍五入,如果省掉m，则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后.如果m是负数,则四舍五入到小数点的m位前123.456 round (123.456,2 ) = 123.46◆ trunc(n,[m]) 该函数用于截取数字.如果省掉m，就截去小数部分,如果m是正数就截取到小数点的m位后,如果m是负数,...
oracle存储过程 = gt,使用oracle的存储过程的例子
weixin_39846364的博客
04-12 157
十几年没有用oracle的存储过程了，有些东西已经忘了，没有想到今天又要用。在这里写个例子。它演示了存储过程的格式，游标的使用，循环、判断的使用，还有直接执行一个拼接的SQL的用法。以下是代码：create or replace procedure wanglc_test isv_table_namevarchar2(50);v_sqlvarchar2(4000);cursor cur_get_u...
原创：oracle 事务总结<九>
redarmychen的专栏
04-29 1727
Code: 1、事务概念：     概念：在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确保这一组SQL语句所作的操作要么完全成功执行,完成整个工作单元操作,要么一点也不执行。      主要特性：确保数据库的完整性。    2、事务的ACID特性      对一组SQL语句操
oracle中sid及有关name的解释 <转>
nourewang的专栏
06-17 1201
这篇文章中要讲的几个参数：       DB相关的：DBID, SID       PFILE中的参数：DB_NAME，DB_DOMAIN, INSTANCE_NAME，                            DB_UNIQUE_NAME,SERVICE_NAMES,                            GLOBAL_NAME，GLOBAL_NAMES
原创：oracle 子查询介绍<七>
redarmychen的专栏
04-16 4124
前几节课中我们渐渐的对oracle的知识,有了进一步的了解与掌握,随着时间的推移,在这节课程中我们来讲解SQL子查询的知识,希望通过授课中的内容对你有所帮助!  Java代码   子查询概念 ：当一个查询的结果是另一个查询的条件时，称之为子查询。    使用子查询注意事项：             子查询可以嵌套多层         
Oracle转义符处理
诚的博客
03-14 2062
1,单引号 在ORACLE中单引号有两个作用:1)引用字符串;2)对自身转义。 引用字符串时,单引号是成对出现的;而对自身的转义,也必定是双数的,所以,一个sql语句中,单引号必定是成对的. Select'abc'a,''b, ''''c, ' '' 'd FromDual; 复杂转义字符串可用连接符||拆分,便于理解。连接符号‘||’左右的单引号没有任何的...
ORACLE修改数据库名<转载>
Lixora's DB Home
01-28 1782
引题：在实际生产环境下我们可能需要快速的复制现有的数据库，又希望新库与原库不重名，或者我们需要复制生产库用来做研发测试，如果我们采用DBCA的方式，缺点是慢并且还没有数据，就需要从新导数据，费时又麻烦。下面我就详细介绍如何修改数据库名来完成使命，采用2种方法（1.重建控制文件修改数据库名2.使用nid工具修改数据库名） 一、重建控制文件修改数据库名 步骤概述 生成controlfi
Oracle函数全解析：单行、多行与分组功能详解
Oracle函数大全实例提供了开发过程中常用的Oracle数据库函数概述，这些函数分为单行函数、多行函数以及分组函数三大类，旨在提升开发效率和数据处理能力。 1. **单行函数**：这类函数针对表中的每一行数据进行操作...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值