Oracle 函数精讲(round、trunc、ceil、floor、dbms_round.random、dbms_random.value、substr、instr、ltrim、initcap..)

最新推荐文章于 2024-04-22 22:14:39 发布
最新推荐文章于 2024-04-22 22:14:39 发布
阅读量651 收藏 7
点赞数 2
分类专栏： Oracle 文章标签： oracle 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45696288/article/details/120956889
版权

initcap：单词首字母大写函数。

substr：取字串函数，得到一个字符串。

DUAL表

DUAL表的所有者是SYS，可以被所有用户使用。DUAL表包含一个字段DUMMY，只有一条记录，值为X。DUAL表非常有用，当你需要数据库返回一个并不存在于任何表中的常数、伪字段或表达式。
SELECT SYSDATE FROM DUAL;
SELECT USER FROM DUAL;
SELECT DBMS_RANDOM.RANDOM FROM DUAL;
SELECT DBMS_RANDOM.VALUE FROM DUAL;

在scott_conn下：
show user;————得user为scott用户
select user from dual;————得c##SCOTT

数学函数


例：

SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923 , -1) FROM dual;
SELECT TRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-1) FROM dual;
Select DBMS_RANDOM.RANDOM from dual;
Select DBMS_RANDOM.value(1,20) from dual;

四舍五入函数：
select round(2345.678,1) from dual;
得：2345.7

select round(2345.678,0) from dual;
得：2346.
第二个参数默认是0，可以省略。

select round(2345.678,-1) from dual;
得：2350

第二个参数默认是复数表示小数点前面的进行四舍五入

select round(2345.678,-2) from dual;
得：2300

截断函数：(不进行四舍五入)
select trunc(2345.678,1) from dual;
得：2345.6

向上取整——天花板函数
select ceil(5.7) from dual;
得：6

select ceil(-5.7) from dual;
得：-5

向下取整——地板函数
select floor(5.7) from dual;
得：5

select floor(-5.7) from dual;
得：-6

生成随机数函数
select dbms_round.random from dual;
说明：

  1. dbms_round是一个包名
  2. 这种方式生成的是一个特别大的随机整数




select dbms_random.value(1,20) from dual;
生成一个[1，20)之间的随机小数
如果想取整数怎么办？
select round(dbms_random.value(1,20)) from dual;
这种取整方式的结果在[1,20]内
select trunc(dbms_random.value(1,20)) from dual;
这种取整方式的结果在[1,20)内

平方、平方根函数
求2的三次方：
select power(2,3) from dual;
得：8

求9的平方根：
select sqrt(9) from dual;
得：3

取余函数：mod函数
求7除以3的余数：
select mod(7,3) from dual;
得：1

字符串函数

截取字符串函数：substr函数
Oracle中的起始位置是1，而不是从0开始的。

select substr(‘abcdefg’,3) from dual;
得：cdefg
说明：只有两个参数的时候，第二个参数表示从第几位开始取字符串，一直取到末尾

select substr(‘abcdefg’,3,3) from dual;
得：cde
说明：有三个参数的时候，第二个参数表示从第几位开始取字符串，第三个参数表示从左往右取几位。

select substr(‘abcdefg’,-3,2) from dual;
得：ef
说明：第二个参数是负数的时候，从字符串的右边往左边数3个位置的地方开始查找，第三个参数表示从左往右取几位。

例：在字符串“MessageBox”中从第8个位置截取长度为3的字符串：
select substr(‘MessageBox’,8,3) from dual;
得：Box

查找字符/字符串函数：instr函数
查找不到就返回0值。

select instr(‘hello world’,‘l’) from dual;
得：3
说明：只有两个参数的时候，第二个参数表示从前往后所需要查找的字符，最终返回的是查找到的位置。

select instr(‘hello world’,‘l’,4) from dual;
得：4
说明有三个参数的时候，第二个参数表示从前往后所需要查找的字符，第三个参数表示从第几位开始查找。

select instr(‘hello world’,‘l’,5) from dual;
得：10
因为是从第5位开始查找，找到的第一个l就是位置10处的。

select instr(‘hello world’,‘l’,2,2) from dual;
当有四个参数的时候，第二个参数表示从前往后所需要查找的字符，第三个参数表示从第几位开始查找，第四个参数表示要查找字符第几次出现的位置。
这个就是表示从第2位开始找，找l第2次出现的位置。

select instr(‘hello world’,‘l’,-2,3) from dual;
得：3
当第三个参数是负数的时候，从右往左找，从右边往左数第2个的位置开始向左查询，但是返回的位数还是从左往右的位置。

删除字符/字符串函数：ltrim函数

select ltrim (‘cccabw’,‘c’) from dual;
得：abw
从左往右，把左边的c删除，有c出现就一直删，一直删到第一个字符开始不是c为止。

select ltrim (‘caccb’,‘c’) from dual;
得：accb

select ltrim (‘caccabw’,‘cac’) from dual;
得：bw
从左往右,删除第二个参数中含有的所有的字符，比如这个就是要删除含有a和c的，那么删除c与a，一直删到第一个字符开始不是c也不是a为止。

select ltrim (‘caccabw’,‘cab’) from dual;
得：w

类似：rtrim是从右往左删除的函数，和ltrim同样的使用规则。

如果第二个参数空缺的话，表示删除空格：
select ltrim (’            caccabw’) from dual;
得：caccabw(删除左侧空格后得到的结果。)
删除右侧的空格用rtrim。

首字母大写其他字母小写函数：initcap函数

将emp表中所有雇员的姓名首字母大写：
select initcap(ename) from emp;

填充函数：lpad函数

在“my book”的左侧填充’th’，使字符串长度为10:
select lpad(‘my book’,10) from dual;
这种是只填充空格，使得字符串长度为10。
select lpad(‘my book’,10,‘th’) from dual;（✅）
得：thtmy book

翻译函数：translate函数

将字符串’abcd’ 按序翻译成‘1234’，以此翻译’jack’:
select translate(‘jack’,‘abcd’,‘1234’) from dual;
得j13k
注意：第二个参数和第三个参数是翻译的对应规则。

字符转ASCII码值函数：ascii函数

分别求得“Z”，“H”，“D”和空格的ASCII值：
select ascii(‘Z’) from dual;
select ascii(‘H’) from dual;
select ascii(‘D’) from dual;
select ascii(’ ') from dual;
这里面有一个空格，得32

ASCII码值转字符函数：chr函数

将ASCII值为122的转换为对应的字符：
select chr(122) from dual;
得：p

字符/字符串替换函数：replace函数

把字符串"Bad Luck Bad Girl"中的’Bad’字符串用’Good’替换：
select replace(‘Bad Luck Bad Girl’,‘Bad’,‘Good’) from dual;

求字符串长度函数：length函数
求字符串“student”的长度：
select length(‘student’) from dual;
得：7

连接字符串函数：contact函数
连接“MY”和“book”两个字符串
select concat(‘my’,‘book’) from dual;
得：mybook

日期函数

显示当前的系统日期：select sysdate from emp;



例：

对于所有工作年限少于70个月的员工，输出员工号、雇佣日期、工作年限（月）、6个月试用期结束的时间、开始上班以后的第一个星期五。
SELECT empno, hiredate,MONTHS_BETWEEN(SYSDATE, hiredate),ADD_MONTHS(hiredate, 6),NEXT_DAY(hiredate, ‘星期五’) FROM emp WHERE MONTH_BETWEEN(SYSDATE, hiredate)<70;

说明一下：
NEXT_DAY()函数查询下一个星期几，周日代表的是1，周一代表的是2
比如现在是周一，要查找下一个星期三，就是查找两天后的周三。如果现在是周四，要查找下一个周三，那么就是查找下个周的周三。

获取当前日期还有一个方法：
select current_date from dual;

求月份间隔函数：months_between()函数

查询emp表中雇员员工的工作年限(月份)：
select months_between(sysdate,hiredate) from emp;

注意：scott表中有一个错误，现在需要修改：
update emp set hiredate = ‘19-4月-1987’ where empno=7788;

增加月份函数：add_months()函数

在今天的基础上，查找增加五个月后的日期：
select add_months(sysdate,5) from dual;

查询下一个星期几函数：next_day()函数

在今天的基础上查询下一个周三的日期：
select next_day(sysdate,4) from dual;
select next_day(sysdate,‘星期三’) from dual;

提取年月日函数：extract函数

提取年份：
select extract(year from sysdate) from dual;
提取月份：
select extract(month from sysdate) from dual;
提取日份：
select extract(day from sysdate) from dual;

类型转换

日期类型格式元素

例：
SELECT empno,TO_CHAR(hiredate,’MM/YY’) FROM emp WHERE ename = ‘BLAKE’;
SELECT TO_CHAR(sal, ‘$99,999’) SALARY FROM emp WHERE ename = ‘SCOTT’;
SELECT EXTRACT(MONTH FROM DATE ‘2003-08-22’) FROM dual;

将数值或日期转换为字符串函数：to_char()函数

查询员工的雇佣日期：
select ename,hiredate from emp;
这样显示的日期格式并不是很好看.

select ename,to_char(hiredate,‘yyyy-mm-dd’) from emp;
美观了，但是没有文字，也不好
select ename,to_char(hiredate,‘yy"年"mm"月"dd"日"’) from emp;
这次好看了，完美

汉字要加双引号

将字符串转换为日期函数：to_date()函数

插入日期：
1.
insert into emp(empno,hiredate) values(111,to_date(‘89-09-23’,‘yy-mm-dd’));
这种插入方式太麻烦
2. alter session set nls_date_format = ‘yy-mm-dd’;
insert into emp(empno,hiredate) values(112,‘84-11-10’);
nls_date_format表示设置本地语言支持的格式

返回指定日期所在月份的最后一天函数：last_day()函数

last_day(time):返回指定日期所在月份的最后一天；

查询当前月份的最后一天：
select last_day(sysdate) from dual;

查询某天所在月份的最后一天：
select last_day(to_date(‘1992-10-09’,‘yyyy-mm-dd’)) from dual;

查询每一个月最后一天过生日的同学：
select * from stu where last_day(birthday) = birthday;

空函数：nvl函数、nvl2函数、nvlif函数(暂不作说明，自己网络找资料)

空值的特点：

  1. 空值最大
  2. 空值和其他数值运算还空值，空值与字符串连接还是字符串。


查询员工每个月的总收入(工资+奖金)：
select ename,sal+comm from emp;
出现了很多null值，因为很多人没有奖金comm，故用nvl或nvl2函数。


select ename,nvl(sal+comm,sal) from emp;

nvl：第一个参数值为null就显示第二个参数值，不为null就显示第一个参数值。


select ename,nvl2(comm,sal+comm,sal) from emp;
comm是用来判断的，comm为null则显示第二个参数值，comm不为null就显示第一个参数值。

练习题：加强巩固

--1. 显示满40年服务年限的员工的姓名和雇佣日期
select ename,hiredate from emp where months_between(sysdate,hiredate)/12>=40;

--2. 以年月日的方式显示所有员工的服务年限
select ename,hiredate,trunc(months_between(sysdate,hiredate)/12)||'年'||trunc(mod(months_between(sysdate,hiredate),12))||'月'||round(mod((mod(months_between(sysdate,hiredate),12)*30),30))||'天'年限 from emp;

select ename,trunc(months_between(sysdate,hiredate)/12)||'年'||trunc(mod((months_between(sysdate,hiredate)),12))||'月'||trunc(sysdate-add_months(hiredate,months_between(sysdate,hiredate)))||'天' 年限 from emp;

--3. 使用函数显示姓名字段的任何位置包含’A’的所有员工的姓名
select ename from emp where instr(ename,'A')<>0;

--4. 对于每个员工，显示其加入公司的天数
select ename,(sysdate-hiredate) days from emp;

--5. 找出在任意年份2月受聘的所有员工
select * from emp where extract(month from hiredate)=2;
select * from emp where to_char(hiredate,'MM')=2;

--6. 显示所有员工的姓名，用a代替A
select translate(ename,'A','a') from emp;

--7. 显示所有员工姓名的前三个字符
select substr(ename,1,3)from emp;

--8. 以首字母大写的方式显示员工的姓名
select initcap(ename) from emp;

--9. 显示不带有’R’的员工姓名
select ename from emp where instr(ename,'R')=0;

--10. 找出各月倒数第3天受雇的所有员工
select ename,hiredate from emp where hiredate=last_day(hiredate)-2;

--11. 统计每年雇佣的人数
select extract(year from hiredate) year,count(*) from emp group by extract(year from hiredate);

select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy');
爱睡觉的小馨
关注
专栏目录
oracle中函数 round(),ceil(),floor(),trunc()用法
秒怂_z
04-28 403
oracle中函数 round(),ceil(),floor(),trunc()用法 round(number ,decimal_places)函数(四舍五入） 1.参数: number:欲处理之数值 decimal_places:四舍五入 , 小数取几位 ( 预设为 0 ) 2.用法： 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果 select round(123.456, ...
oracle减法函数mius_oracle常用函数使用大全 Oracle除法（转）
weixin_39968640的博客
12-21 1805
http://blog.csdn.net/chenmeng2192089/article/details/9155625一、运算符算术运算符：+ - * / 可以在select 语句中使用连接运算符：|| select deptno|| dname from dept;比较运算符：> >= = != < <= like between is null in逻辑运算符：not...
《Oracle PL/SQL实例精讲》学习笔记20——函数
预见未来to50的专栏
11-12 283
本章内容： 1. 创建函数 2. 在SQL语句中使用函数 3. 在SQL中优化函数执行 基本概念： 1. 函数与过程的区别： Functions are another type of stored code and are very similar to procedures. The  significant difference between the two is that a...
Oracle-函数-substr, instr, trunc
weixin_30466953的博客
12-15 319
--Oracle trunc()函数的用法 /**************日期********************/ 　　SELECT TRUNC(SYSDATE) FROM dual --2013-01-06 今天的日期为2013-01-06　　SELECT TRUNC(SYSDATE, 'mm') FROM dual --2013-01-01 返回当月第一天.　　SELE...
oracle ceil和floor函数
weixin_30667301的博客
03-14 502
ceil和floor函数在一些业务数据的时候，有时还是很有用的。 ceil(n) 取大于等于数值n的最小整数； floor(n)取小于等于数值n的最大整数 如下例子 SQL> select ceil(9.5) from dual;CEIL(9.5)---------- 10SQL> select floor(9.5) from dual;FLOOR(9.5...
Oracle中trunc函数、round_函数、ceil函数和floor函数的使用
winingblog
04-20 365
Oracle中trunc函数、round 函数、ceil函数和floor函数的使用1.1trunc函数处理数字trunc函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。其具体的语法格式如下TRUNC（number[,decimals]）其中：number 待做截取处理的数值decimals 指明需保留小数点后面的位数。可选...
Oracle函数大全.pdf
04-19
- **dbms_random**: 包含用于生成随机数的函数。 - **utl_inaddr.get_host_address**: 获取主机地址。 以上内容涵盖了Oracle中常见的函数及其用途，学习和掌握这些函数对于有效地使用Oracle数据库至关重要。在实际...
Oracle_Database_11g完全参考手册.part3/3
08-18
附录部分内容丰富、便予参照，包括Oracle命令、关键字、功能以及函数等。 目录 第1部分 关键的数据库概念 第1章 OracleDatabase11g体系结构 1.1 数据库和实例 1.2 数据库技术 1.2.1 存储数据 1.2.2 数据保护 1.2.3...
oracle数据库面试题目汇总及答案解析.docx
最新发布
05-17
### Oracle数据库面试知识点详解 #### 一、字符串操作函数 1. **ASCII()**: 返回给定字符表达式的第一个字符的ASCII码值。 2. **CHR()**: 将给定的ASCII码值转换为对应的字符。需要注意的是，输入值必须位于0~255...
Oracle_Database_11g完全参考手册.part2/3
08-18
附录部分内容丰富、便予参照，包括Oracle命令、关键字、功能以及函数等。 目录 第1部分 关键的数据库概念 第1章 OracleDatabase11g体系结构 1.1 数据库和实例 1.2 数据库技术 1.2.1 存储数据 1.2.2 数据保护 1.2.3...
Oracle函数大全三十七：ROUND函数
yixiaobing的博客
04-22 1701
需要注意的是，ROUND函数不仅可以处理数值类型的列或变量，也可以处理可以被Oracle转换为数字的字符串类型的数据。但是，如果传递给ROUND函数的参数无法转换为数字，则会引发错误。Oracle的ROUND函数是一个内置的数学函数，用于对数字进行四舍五入计算。它可以根据指定的精度对单个数字或一组数字进行处理。ROUND函数在数据处理、财务计算、科学计算等领域中非常有用，因为它可以帮助用户按照指定的精度对数字进行格式化或调整。（向上取整），而第二个查询将返回。
trunc() instr() substr()
好红的博客
12-02 1987
在这里整理一些oracle的函数，碰到的时候会总结进来，希望有点用
SQL中函数TRUNC使用与对比 SUBSTRING(),SUBSTR(),round(),replace()等函数
weixin_49210944的博客
11-21 1938
SQL中函数TRUNC使用与对比 SUBSTRING(),SUBSTR(),round(),replace()等函数
oracle 数据库ceil,[数据库]Oracle中trunc函数、round 函数、ceil函数和floor函数的使用...
weixin_36158990的博客
04-04 218
[数据库]Oracle中trunc函数、round 函数、ceil函数和floor函数的使用02016-08-20 10:00:111.1trunc函数处理数字trunc函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。其具体的语法格式如下TRUNC(number[,decimals])其中：number 待做截取处理的数值...
Oracle中trunc、round 、ceil和floor函数的区别
yLazL0000的博客
02-06 2021
1、trunc函数 处理数值，对给定数值进行截取处理，不会对给定数值做四舍五入处理。 语法格式： TRUNC（number[,decimals]）--number 待处理的数据，decimals指明需保留小数点后的位数，默认为0 如果参数为负数，则小数点往前取位数，如果负数位数大于或等于待处理数据位数，则返回0. selecttrunc(123.456)from dual;--1
Oracle中trunc函数、round 函数、ceil函数和floor函数的使用
qq7652401741的博客
01-17 216
Oracle中trunc函数、round 函数、ceil函数和floor函数的使用 1.1trunc函数处理数字 trunc函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。 其具体的语法格式如下 TRUNC（number[,decimals]） 其中： number 待做截取处理的数值 decimals 指明需保留小数点...
java trunc函数_Oracle trunc()函数，decode()函数，substr函数，GREATEST函数，java中substring函数的用法...
weixin_39864261的博客
02-24 285
--Oracle trunc()函数的用法/**************日期********************/1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-062.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.3.selec...
Oracle中常用的函数详解
小郑要做干饭人的博客
11-07 1013
字符函数 1: ASCII(‘A’) 返回对应ASCII码表中的位置 SELECT ASCII('A') FROM DUAL 效果图如下: 2：concat(‘HELLO’,‘oracle’) 字符串拼接或’HELLO’’,ORACLE’ SELECT CONCAT('Hello',',Oracle') FROM DUAL 效果图如下: 3：LENGTH(‘HE LLO’) 返回字符串长度 SELECT LENGTH('HELLO') FROM DUAL 效果图如下: ...
oracle substr instruction trunc
03-28 550
substr(字符串，-10）Oracle 字符串函数 substr(字符串,截取开始位置,截取长度) 1. 如果最后一个截取长度参数为空，则表示从截取开始位置起截到最末 2. 如果截取开始位置 为大于0的数字，则表示从字符串左数几位开始 3. 如果截取开始位置 为小于0的数字，则表示从字符串右数几位开始 --1.去掉;@ update  GGZYJYZX_ZFCGXX t set t.zbr
trunc(dbms_RANDOM.VALUE(1,71)) 如何限制一条
05-31
如果您想要在SQL语句中使用`trunc(dbms_random.value(1,71))`来生成一个随机数，并且需要将该随机数限制为一个固定值，可以将该随机数作为子查询的结果，然后在外层查询中使用`WHERE`子句来限制结果集。例如，如果您需要将该随机数限制为50，可以使用如下SQL语句： ```sql SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50; ``` 这里使用了`dual`表作为子查询的来源，同时使用`WHERE`子句来过滤结果集，只返回随机数等于50的记录。如果您需要返回多条记录，可以使用`UNION ALL`来连接多个子查询。例如，如果您需要返回10条随机数等于50的记录，可以使用如下SQL语句： ```sql SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50 UNION ALL SELECT trunc(dbms_random.value(1,71)) as rand_num FROM dual WHERE trunc(dbms_random.value(1,71)) = 50; ``` 这里使用了10个子查询来生成10条随机数等于50的记录。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱睡觉的小馨

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值