Oracle 函数

Oracle 函数

1 数值型函数
abs:求绝对值函数,如:abs(?5) 5
sqrt:求平方根函数,如:sqrt(2) 1.41421356
power:求幂函数,如:power(2,3) 8
cos:求余弦三角函数,如:cos(3.14159) ?1
mod:求除法余数,如:mod(1600, 300) 100
ceil:求大于等于某数的最小整数,如:ceil(2.35) 3
floor:求小于等于某数的最大整数,如:floor(2.35) 2
round:按指定精度对十进制数四舍五入,如:round(45.923, 1),结果,45.9
round(45.923, 0),结果,46
round(45.923, ?1),结果,50
trunc:按指定精度截断十进制数,如:trunc(45.923, 1),结果,45.9
trunc(45.923),结果,45
trunc(45.923, ?1),结果, 40


2 字符型函数

ascii:获得字符的ASCII码,如:Ascii('A') 65
chr:返回与ASCII码相应的字符,如:Chr(65) A
lower:将字符串转换成小写,如:lower ('SQL Course') sql course
upper:将字符串转换成大写,如:upper('SQL Course') SQL COURSE
initcap:将字符串转换成每个单词以大写开头,如:initcap('SQL course') Sql Course
concat:连接两个字符串 concat('SQL', ' Course') SQL Course
substr:给出起始位置和长度，返回子字符串,如:substr('String',1,3) Str
Sql代码

   1.   
   2. --substr函数有三个参数，允许你将目标字符串的一部份输出， 
   3. --第一个参数为目标字符串， 
   4. --第二个字符串是将要输出的子串的起点，下标从1开始，如果是正数从前面数，如果是负数从后面数 
   5. --第三个参数是将要输出的子串的长度 
   6. select  substr('0'||'2',2)|| substr('0'||'15',-2) from dual; --215 
   7.  select  substr('0'||'2',2)|| substr('0'||'3',2) from dual;  
   8.   --23 月日比较的话实际要的是203 
   9.   --215 < 23 注意这里返回的是字符串的比较, 
  10.   --所以如果日期转换成月日比较的话 
  11.   --这里日那里要写成-2 substr('0'||'2',2)|| substr('0'||'3',-2) 
  12.   select substr('123',4) from dual; -- 输出空 


--substr函数有三个参数，允许你将目标字符串的一部份输出，
--第一个参数为目标字符串，
--第二个字符串是将要输出的子串的起点，下标从1开始，如果是正数从前面数，如果是负数从后面数
--第三个参数是将要输出的子串的长度
select  substr('0'||'2',2)|| substr('0'||'15',-2) from dual; --215
select  substr('0'||'2',2)|| substr('0'||'3',2) from dual;
  --23 月日比较的话实际要的是203
  --215 < 23 注意这里返回的是字符串的比较,
  --所以如果日期转换成月日比较的话
  --这里日那里要写成-2 substr('0'||'2',2)|| substr('0'||'3',-2)
  select substr('123',4) from dual; --输出空


length:求字符串的长度 length('Wellcom') 7
instr:给出起始位置和出现的次数，求子字符串在字符串中出现的位置,如:instr('String', 'r',1,1) 3
lpad:用字符填充字符串左侧到指定长度,如:lpad('Hi',10,'-') --------Hi
rpad:用字符填充字符串右侧到指定长度,如:rpad('Hi',10,'-') Hi--------
trim:在一个字符串中去除另一个字符串,如:trim('S' FROM 'SSMITH') MITH
replace:用一个字符串替换另一个字符串中的子字符串,如:replace('ABC', 'B', 'D') ADC






3 日期函数

months_between:返回两个日期间的月份,如:months_between ('04-11月-05','11-1月-01'),结果,57.7741935
add_months:返回把月份数加到日期上的新日期,如:add_months('06-2月-03',1),结果,06-3月-03
add_months('06-2月-03',-1),结果,06-1月-03
next_day:返回指定日期后的星期对应的新日期,如:next_day('06-2月-03','星期一'),结果,10-2月-03
last_day:返回指定日期所在的月的最后一天,如:last_day('06-2月-03'),结果,28-2月-03
round:按指定格式对日期进行四舍五入,如:round(to_date('13-2月-03'),'YEAR'),结果,01-1月-03
round(to_date('13-2月-03'),'MONTH'),结果,01-2月-03
round(to_date('13-2月-03'),'DAY'),结果,16-2月-03

(按周四舍五入)
trunc:对日期按指定方式进行截断,如:trunc(to_date('06-2月-03'),'YEAR'),结果,01-1月-03
trunc(to_date('06-2月-03'),'MONTH'),结果,01-2月-03
trunc(to_date('06-2月-03'),'DAY'),结果,02-2月-03
EXTRACT()抽取当前日期的年月日：注意时分秒是抽取不到的
Sql代码

   1. --EXTRACT()抽取当前日期的年月日：注意时分秒是抽取不到的 
   2.  --抽取年月日 
   3.  SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;  --2010 
   4.  SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --2 
   5.  SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;   --24 
   6.  select EXTRACT(HOUR FROM SYSDATE) FROM DUAL; -- 这个是错误的 
   7.   --抽取时分秒方法一 
   8. SELECT EXTRACT(HOUR FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; 
   9. --14  
  10. SELECT EXTRACT(MINUTE FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; 
  11. --46 
  12. SELECT EXTRACT(SECOND FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL; 
  13. --10 
  14.  --抽取时分秒方法二 
  15.   SELECT to_char(Sysdate,'hh24:mi') FROM DUAL; -- 14:49 

--EXTRACT()抽取当前日期的年月日：注意时分秒是抽取不到的
--抽取年月日
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;  --2010
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --2
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;   --24
select EXTRACT(HOUR FROM SYSDATE) FROM DUAL; -- 这个是错误的
  --抽取时分秒方法一
SELECT EXTRACT(HOUR FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL;
--14
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL;
--46
SELECT EXTRACT(SECOND FROM TIMESTAMP '2009-2-24 14:46:10')FROM DUAL;
--10
--抽取时分秒方法二
  SELECT to_char(Sysdate,'hh24:mi') FROM DUAL; -- 14:49





4 转换函数
Oracle的类型转换分为自动类型转换和强制类型转换。常用的类型转换函数有TO_CHAR、TO_DATE或TO_NUMBER

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




5 自动类型转换
Oracle可以自动根据具体情况进行如下的转换：
* 字符串到数值。
* 字符串到日期。
* 数值到字符串。
* 日期到字符串。
以下是自动转换的训练。
自动转换字符型数据到数值型。



6 日期类型转换
将日期型转换成字符串时，可以按新的格式显示。
如格式YYYY-MM-DD HH24:MI:SS表示“年-月-日 小时:分钟:秒”。Oracle的日期类型是包含时间在内的。
主要的日期格式字符的含义

AM、PM:上午、下午,如:08 AM
D:数字表示的星期(1～7),如:1,2,3,4,5,6,7
DD:数字表示月中的日期(1～31),如:1,2,3,…,31
MM:两位数的月份,如:01,02,…,12
Y、YY、YYY、YYYY 年份的后几位,如:3,03,003,2003
RR:解决Y2K问题的年度转换
DY:简写的星期名,如:MON,TUE,FRI,…
DAY:全拼的星期名,如:MONDAY,TUESDAY,…
MON:简写的月份名,如:JAN,FEB,MAR,…
MONTH:全拼的月份名,如:JANUARY,FEBRUARY,…
HH、HH12:12小时制的小时(1～12),如:1,2,3,…,12
HH24:24小时制的小时(0～23),如:0,1,2,…,23
MI:分(0～59),如:0,1,2,…,59
SS:秒(0～59),如:0,1,2,…,59
,./-;: 原样显示的标点符号
'TEXT':引号中的文本原样显示,如:TEXT




7 数值转换符:

9:代表一位数字，如果是正数，前面是空格，如果是负数，前面是?号,如:9999
0:代表一位数字，在相应的位置上如果没有数字则出现0 0000
,:逗号，用作组分隔符,如:99,999
.:小数点，分隔整数和小数,如:999.9
$:$货币符号,如:$999.9
L:本地货币符号,如:L999.99
FM:去掉前后的空格,如:FM999.99
EEEE:科学计数法,如:9.9EEEE
S:负数符号?放在开头,如:S999.9



8 其他函数
nvl:空值转换函数,如:nvl(null, '空'),结果:空
decode:实现分支功能,如:decode(1,1, '男', 2, '女'),结果:男
userenv:返回环境信息,如:userenv('LANGUAGE'),结果:SIMPLIFIED CHINESE_CHINA.ZHS16GBK
greatest:返回参数的最大值,如:greatest(20,35,18,9),结果:35
least:返回参数的最小值,如:least(20,35,18,9),结果:9




9 空值的转换
如果对空值NULL不能很好的处理，就会在查询中出现一些问题。在一个空值上进行算术运算的结果都是NULL。最典型的例子是，在查询雇员表时，将工资sal字段和津贴字段comm进行相加，如果津贴为空，则相加结果也为空，这样容易引起误解。
使用nvl函数，可以转换NULL为实际值。该函数判断字段的内容，如果不为空，返回原值；为空，则返回给定的值。

如下3个函数，分别用新内容代替字段的空值：
nvl(comm, 0)：用0代替空的Comm值。
nvl(hiredate, '01-1月-97')：用1997年1月1日代替空的雇佣日期。
nvl(job, '无')：用“无”代替空的职务。
使用nvl函数转换空值。