Oracle数据库(常用的函数)

最新推荐文章于 2023-08-02 09:32:53 发布

yuzhaocong

最新推荐文章于 2023-08-02 09:32:53 发布

阅读量4.7k

点赞数 7

本文链接：https://blog.csdn.net/yuzhaocong/article/details/78207238

版权

 
 oracle常用函数 

 
 使用函数可以大大提高select语句操作数据库的能力。在oracle中函数划分为单行函数和多行函数 

 
 单行函数作用于数据库表的某一行并返回一个值：字符函数、数字函数、日期函数、转换函数、其他函数。 

 
 多行函数基于数据库多行进行运算，返回一个值：例如对多行记录的某个字段求和、求最大值运算等 

 
 字符函数 

函数	功能	示例	结果
initcap(char)	首字母大写	initcap('hello')	Hello
lower(char)	转换为小写	lower('FUN')	fun
upper(char)	转换为大写	upper('sun')	SUN
ltrim(char,ser)	左剪裁	ltrim('xyzadams','xyz')	adams
rerim(char,set)	右剪裁	rtrim('xyzadams','ams' )	xyzad
translate(char,from,to)	按字符翻译	translate('jack','abcd','1234')	j13k
replace(char,search_str,replace_str)	字符串替换	replace('jack and jue','j','bl')	black and blue
instr(char,substr[,post])	查找子串位置	instr('worldwide','d')	5
substr(char,pos,len)	取子字符串	substr('abcdefg',3,2)	cd
length(char)	字符串长度	length('adsfd')	5
concat(char1,char2)	连接字符串	concat('Hello','world')	Helloworld

 
 范例： 
 观察转小写的操作，将所有的雇员姓名按照小写字母返回 

 
 SELECT 
   
 LOWER 
 (ename)  
 FROM 
  emp; 

 
 范例： 
 将每一个雇员姓名的开头首字母大写 

 
 SELECT 
  INITCAP(ename)  
 FROM 
  emp; 

 
 范例： 
 查询出每个雇员姓名的长度 

 
 SELECT 
  ename,LENGTH(ename)  
 FROM 
  emp; 

 
 范例： 
 要求查询出姓名长度正好是5的雇员信息 

 
 SELECT 
  ename,LENGTH(ename)  
 FROM 
  emp 

 
 WHERE 
  LENGTH(ename) 
 = 
 5 
 ; 

 
 范例： 
 使用字母“_”替换掉姓名中的所有字母“A” 

 
 SELECT 
   
 REPLACE 
 (ename, 
 'A' 
 , 
 '_' 
 )  
 FROM 
  emp; 

 
 字符串截取操作有两种语法： 

 
 语法一：SUBSTR(字符串 | 列，开始点)，表示从开始点一直截取到结尾； 

 
 SELECT 
  ename,SUBSTR(ename, 
 3 
 )  
 FROM 
  emp; 

 
 语法二：SUBSTR(字符串 | 列，开始点，结束点)，表示从开始点截取到结束点，截取部分内容； 

 
 SELECT 
  ename,SUBSTR(ename, 
 0 
 , 
 3 
 )  
 FROM 
  emp; 

 
 SELECT 
  ename,SUBSTR(ename, 
 1 
 , 
 3 
 )  
 FROM 
  emp; 

 
 范例： 
 要求截取每个雇员姓名的后三个字母 

 
 ·  
 正常思路：通过长度-2确定开始点 

 
 SELECT 
  ename,SUBSTR(ename,LENGTH(ename) 
 - 
 2 
 )  
 FROM 
  emp; 

 
 ·  
 新思路：设置负数，表示从后指定截取位置； 

 
 SELECT 
  ename,SUBSTR(ename, 
 - 
 3 
 )  
 FROM 
  emp; 

 
 面试题： 
 请问SUBSTR()函数截取的时候下标从0还是从1开始？ 

 
 ·  
 在Oracle数据库之中，SUBSTR()函数从0或1开始都是一样的； 

 
 ·  
 SUBSTR()也可以设置为负数，表示由后指定截取的开始点； 

 
 数字函数 

函数	功能	示例	结果
abs(n)	取绝对值	abs(-15)	15
ceil(n)	向上取值	ceil(44.778)	45
sin(n)	正弦	sin(1.571)	.999999979
cos(n)	余弦	cos(0)	1
sign(n)	取符号	sign(-32)	-1
floor(n)	向下取整	floor(100.2)	100
power(m,n)	m的n次幂	power(4,2)	16
mod(m,n)	取余数	mod(10,3)	1
round(m,n)	四舍五入	round(100.256,2)	100.26
trunc(m,n)	截断	tranc(100.256,2)	100.25
sqrt(n)	平方根	sqrt(4)	2

 
 -- 四舍五入 

 
 select round(255,-1) from dual; 

 
 -- 舍弃小数位 

 
 select trunc(255,-1) from dual; 

 
 常用日期函数 

函数	功能
month_between(date1,date2)	返回两个日期间的月份
add_month(date,n)	返回把月份数n加到日期date上的新日期
next_day(date,week)	返回指定日期后的星期对应的新日期
last-day(date)	返回指定日期所在月的最后一天

 
 日期常用的三个公式： 

 
 · 日期 + 数字 = 日期，表示若干天之后的日期； 

 
 SELECT 
  SYSDATE  
 + 
   
 3 
 ,SYSDATE  
 + 
   
 300 
   
 FROM 
  dual; 

 
 · 日期 – 数字 = 日期，表示若干天前的日期； 

 
 SELECT 
  SYSDATE  
 - 
   
 3 
 ,SYSDATE  
 - 
   
 300 
   
 FROM 
  dual; 

 
 · 日期 – 日期 = 数字，表示的是两个日期间的天数，但是肯定是大日期 – 小日期； 

 
 范例： 
 求出每个雇员到今天为止的雇佣天数 

 
 SELECT 
  ename,hiredate,SYSDATE 
 - 
 hiredate  
 FROM 
  emp; 

 
 范例： 
 求出本月的最后一天日期 

 
 SELECT 
  LAST_DAY(SYSDATE)  
 FROM 
  dual; 

 
 · NEXT_DAY(日期，星期数)：求出下一个指定星期X的日期； 

 
 范例： 
 求出下一个周一 

 
 SELECT 
  NEXT_DAY(SYSDATE, 
 '星期一' 
 )  
 FROM 
  dual; 

 
 范例： 
 求出四个月后的日期 

 
 SELECT 
  ADD_MONTHS(SYSDATE, 
 4 
 )  
 FROM 
  dual; 

 
 范例： 
 求出每个雇员到今天为止的雇佣月份 

 
 SELECT 
  ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))  
 FROM 
  emp; 

 
 常用的转换函数 

函数	功能	示例	结果
to_char(字符串 \| 列，格式字符串)	转换成字符串类型	to_char(1234.5,'$9999.9')	$1234.5
to_date(字符串，格式字符串)	转换成日期类型	to_date('1980-01-01','yyyy-mm-dd')	01-1月-80
to_number(字符串)	转换成数值类型	to_number('1234.5')	1234.5

 
 SELECT 
  TO_CHAR(SYSDATE, 
 'yyyy-mm-dd' 
 ),TO_CHAR(SYSDATE, 
 'yyyy' 
 )  
 year 
 , TO_CHAR(SYSDATE, 
 'mm' 
 )  
 month 
 , TO_CHAR(SYSDATE, 
 'dd' 
 )  
 day 
   
 FROM 
  dual; 

 
 TO_CHAR(SY YEAR MO DA 

 
 ---------- ---- -- -- 

 
 2012-08-12 2012 08 12 

 
 但是这个时候的显示数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加入一个“fm”。 

 
 SELECT 
  TO_CHAR(SYSDATE, 
 'fmyyyy-mm-dd' 
 )  
 day 
   
 FROM 
  dual; 

DAY

 
 ---------- 

 
 正常人都加0，所以这个标记知道就行了，可是在Oracle之中，DATE里面是包含了时间的，但是之前的代码没有显示出时间，要想显示时间则需要增加标记： 

 
 SELECT 
  TO_CHAR(SYSDATE, 
 'fmyyyy-mm-dd hh24:mi:ss' 
 )  
 day 
   
 FROM 
  dual; 

DAY

 
 ------------------- 

 
 2012-8-12 16:13:38 

 
 一定要注意，使用TO_CHAR()函数之后，所有的内容都是字符串，不再是之前的DATE型数据，TO_CHAR()函数也可以用于数字的格式化上，这个时候每一个“9”表示一位数字的概念，而不是数字9的概念。 

 
 SELECT 
  TO_CHAR( 
 89078907890 
 , 
 'L999,999,999,999,999' 
 )  
 FROM 
  dual; 

 
 TO_CHAR(89078907890,'L999,999, 

 
 ------------------------------ 

 
       ￥89,078,907,890 

 
 其中的字母“L”，表示的是“Local”的含义，即：当前的所在的语言环境下的货币符号。 

 
 b、TO_DATE()函数 

 
 此函数的主要功能是将一个字符串变为DATE型数据。 

 
 SELECT 
  TO_DATE( 
 '1989-09-12' 
 , 
 'yyyy-mm-dd' 
 )  
 FROM 
  dual; 

 
 -------------- 

 
 12-9月 -89 

 
 常用其他函数 

函数	功能
nvl(exp1,exp2)	如果exp1的值为null，则返回exp2的值，否则返回exp1的值
nvl2(exp1,exp2,exp3)	如果exp1的值不为null，则返回exp2的值，否则返回exp3的值
decode(value,if1,then1,if2,then2,......,else)	如果value的值为if1，则返回then1的值，如果为if2，则返回then2的值，.....，否则返回else的值

 
 a、NVL()函数，处理null 

 
 范例： 
 要求查询出每个雇员的全部年薪 

 
 SELECT 
  ename,sal,comm,(sal 
 + 
 NVL(comm, 
 0 
 )) 
 * 
 12 
 ,NVL(comm, 
 0 
 )  
 FROM 
  emp;  

 
 b、DECODE()函数：多数值判断 

 
 DECODE()函数非常类似于程序中的if…else…语句，唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。 

 
 例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示： 

 
 ·  
 CLERK：办事员； 

 
 ·  
 SALESMAN：销售； 

 
 ·  
 MANAGER：经理； 

 
 ·  
 ANALYST：分析员； 

 
 ·  
 PRESIDENT：总裁； 

 
 这种判断肯定是逐行进行判断，所以这个时候就必须采用DECODE(),而此函数的语法如下： 

 
 DECODE(数值  
 | 
  列 ，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…) 

 
 范例： 
 实现显示的操作功能 

 
 SELECT 
  empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') 

 
 FROM 
  emp; 

 
 DECODE()函数是整个Oracle之中最具特点的函数，一定要将其掌握。 

yuzhaocong

关注

7
点赞
踩
54

收藏

觉得还不错? 一键收藏
5
评论
Oracle数据库(常用的函数)

oracle常用函数使用函数可以大大提高select语句操作数据库的能力。在oracle中函数划分为单行函数和多行函数单行函数作用于数据库表的某一行并返回一个值：字符函数、数字函数、日期函数、转换函数、其他函数。多行函数基于数据库多行进行运算，返回一个值：例如对多行记录的某个字段求和、求最大值运算等字符函数函数功能
复制链接

扫一扫