ABS 求绝对值
ADD_MONTHS
ADD_MONTHS函数将一个日期上加上一指定的月份数,所以,日期中的日将是不变的。然而,如果开始日期是某月的最后一天,
那么,结果将会调整以使返回值仍对应新的一月的最后一天。如果,结果月份的天数比开始月份的天数少,那么,
也会向回调整以适应有效日期。其语法格式如下:
ADD_MONTHS(date,months)
其中:
date 一个日期数值
months 要加上的月份数。要减去的月份数用负数
下面的例子解释了该函数的使用:
ADD_MONTHS(TO_DATE(’15-Nov-1961’,’d-mon-yyyy’),1) =’15-Dec-1961
ADD_MONTHS(TO_DATE(’30-Nov-1961’,’d-mon-yyyy’),1) =’31-Dec-1961
ADD_MONTHS(TO_DATE(’31-Jan-1999’,’d-mon-yyyy’),1) =’28-Feb-1999
注意,在上面的第三个例子中,函数将不得不将31日往回调整为28日,已使结果对应新一月的最后一天。因为,
1999年的2月份只有28天。在第二个例子中,则是从30往后调整为31,也同样是为了保持对应的最后一天。
ASCII
ASCII函数返回你指向的字符串首字母的十进制表示代表码值。该返回值或许是ASCII码值,也或许不是。
如果用户的数据库字符集是7位的ASCII值,那就得到一ASCII码值。该返回值总是以用户使用的字符集为基础的。
ASCII(string)
其中:string一个字符串。更多的时候就是一字符
下面的例子解释了该函数的使用:
ASCII(‘j’)=74
ASCII(‘jeff’)=74
从上面的第二个例子,我们不难看出,如果指定了一个多字符串,该函数将忽略除首字母外的所有字符。
CEIL
CEIL函数根据输入值返回一个数值。输入参数可以是非整数值,但返回结果则是大于等于输入参数的最小整数。
其语法格式如下:
CEIL(number)
其中:
number 任意数值,十进制数
下面的例子解释了CEIL函数的使用情况:
CEIL(5.1)=6
CEIL(-5.1)=-5
应注意,对于负数使用该函数的结果。大于等于-5。1的最小整数精确地讲应该是-5。这是数学上的情况,但首先也是记数中知觉
上的感觉。
FLOOR
FLOOR函数返回一个小于或等于给定十进制数的最大整数。该函数的工作机制与函数CEIL的情况极为相似,但却正好相反。
其具体的语法格式如下:
FLOOR(number)
其中:
number 任意数,包括十进制数
下面是该函数的使用情况:
FLOOR(5.1)=5
FLOOR(-5.1)=-6
为了理解函数FLOOR和函数CEIL在处理负数时的不同机制,可以比较函数FLOOR(5.1)和函数CEIL(-5.1)的执行结果。
CHR
CHR函数就相应的数据库字符集,返回一个指定数值相应的字符。例如,给定一个ASCII值,使用CHR函数返回该数值表示的字符。
其语法格式如下:
CHR(integer[USING NCHAR_CS])
其中:
integer 在相应的字符集中,代表对应字符的整数类型
USING NCHAR_CS 指明相应的字符集
下面的例子解释了CHR函数的使用情况:
CHR(10)=a tab character
CHR(65)=‘A’
这些例子假设基于ASCII字符集
CONCAT 连接两个字符串
DECODE(一)
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然,如果未能与任何一个实参序偶匹配成功,则函数也有默认的返回值。区别于SQL的其它函数,DECODE函数还能识别和操作空值。
其具体的语法格式如下:
DECODE(input_value,value,result[,value,result…][,default_result]);
其中:
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比 较,以决定最后的返回结果
value 是一组成序偶的数值。如果输入数值与之匹配成功,则相应 的结果将被返回。对应一个空的返回值,可以使用关键字 NULL于之对应
result 是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时,函数返回的默认值
下面的语句,说明了用法
SELECT flowid,decode(FLOWID,1,'is 1',3,'is 3','其他'),flowname FROM tabflow
如果想在这个函数里面进行计算,则必须使用to_char函数,如:
SELECT flowid,decode(FLOWID,3,to_char(flowid*3),'其他'),flowname FROM tabflow
TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNC(date[,fmt])
其中:
date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
TRUNC(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’))
=’24-Nov-1999 12:00:00 am’
TRUNC(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)) =’24-Nov-1999 08:00:00 am’
TRUNC(for number)
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,
而统统截去。
其具体的语法格式如下
TRUNC(number[,decimals])
其中:
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
DECODE(二)
这个SQL语句展示了DECODE函数的左右基础功能特征。函数的输入值时BLOOD_TEST_FLAG列的数据,如果该列的值是‘Y’,那么,
函数返回‘YES’。如果该列的值是NULL,那么,函数返回‘None’。如果没有与任何一个序偶匹配成功,则表示该列当前值无效,
函数返回‘Invalid’。
在SQL疑难问题中,DECODE函数常常发挥非常灵活的作用。其中的一个技术就是为了某种目的可以将一个表的行转换成列。例如:
SQL>SELECT TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’),
2 COUNT(*)
3 FROM AQUATIC_ANIMAL
4 WHERE TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’)
5 IN(‘1995’,’1996’,’1997’)
6 GROUP BY TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’);
TO_C COUNT(*)
--- -------
1995 1
1996 3
1997 1
该例告诉我们,1995、1996和1997年各出生了多少动物。每一行显示不同的年。一旦希望将这些数值显示成3列,
该如何处理?可以通过如表B-1所示的方法处理之。
SQL>SELECT SUM(
DECODE(TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’),’1995’,1,0))BORN_1995
SUM(DECODE(TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’),’1996’,1,0))BORN_1996
SUM(DECODE(TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’),’1997’,1,0))BORN_1997
FROM AQUATIC_ANIMAL
WHERE TO_CHAR(TRUNC(BIRTH_DATE,’YEAR’),’YYYY’)
IN(‘1995’,’1996’,’1997’);
BORN_1995 BORN_1996 BORN_1997
----- ----- -----
1 3 1
该例中,为了生成结果集合中的3列,BIRTH_DATE被引用了3 次。为了过滤出正确的返回年份,每次引用均引用了函数DECODE。
例如,在第一列中,DECODE函数将所有的1995日期值转换成1,其他的均转换成0,再利用SUM函数求出其汇总值,
从而达到求取在1995年诞生的动物数目。1996和1997年的情况类似。
GREATEST
GREATEST函数返回一数值列表中的最高数值。可使用GREATEST函数处理数字数据和字符数据。
其具体的语法格式如下:
GREATEST(value,value,value,…)
其中:
value 数字数据或文本数据。一般地,所有数值的类型应该一致。 如果给定的数据类型相混,返回值将匹配第一个参数类型, 其余的所有参数自然被转换成对应的类型
下面是该函数的使用情况:
GREATEST(1,3,9,45,93,2,-100)=93
GREATEST(’Jenny’,’Jeff’,’Ashley’)= ’Jenny’
GREATEST(’11’,101)=‘11’
第三个例子反映了参数类型相混时的函数处理情况。因为第一的数据类型是字符串,所以,Oracle将数字1转换成字符串‘1’,以匹配对应的数据类型。虽然在数值上101大于11,但如果都转换成字符串,则结果正好相反。这种情况下,‘11’大于‘101’,并且,也是函数的返回结果。
LEAST
LEAST函数返回参数给定的数值列表中的最小值。对数值数据或字符串数据均有效。
其具体的语法格式如下:
LEAST(value,value,value,…)
其中:
value 一数字或文本数值。尤其要注意的是所有的数据类 型应该一致。如果类型相混,返回值将匹配第一个 参数的数据类型,而且,其它所有的参数均被转换 成该参数的数据类型
下面是该函数的使用情况:
LEAST(1,3,9,45,93,2,-100)=-100
LEAST(’Jenny’,’Jeff’,’Ashley’)=‘Ashley’
LEAST(’110’,12)=‘110’
第三个例子说明了,当参数的数据类型相混时函数的处理机制。因为第一个参数实字符串,所以,将数字1转换成字符串‘1’,一匹配对应的数据类型。虽然在数值上110大于12,但如果都转换成字符串,则结果正好相反。这种情况下,‘12’大于‘110’(一顺序为基础),这就是函数的返回结果。
INITCAP
INITCAP函数接收一字符串,并转换该字符串中的所有单词,以使各单词均以大写字母开头。
其的语法格式如下:
INITCAP(string)
其中:
string 任意一VARCHAR2或CHAR型数据
INSTR
函数INSTR会告诉用户,其中的一个字符串(参数)是否在另一个字符串(也是参数)当中。
其具体的语法格式如下:
INSTR(string,substring[,start[,occurrence]])
其中:
string 待查询的字符串
substring 正在搜索的字符串
start 说明开始搜索的字符位置。默认值是1,就是说,搜 索将从字符串的第一个字符开始。如果,参数为负 则表示搜索的位置从右边开始计算,而不是默认的 从左边开始
occurrence 指定试图搜索的子串的第几次出现,默认值是1,意 味着希望其首次出现
该函数反馈一索引顺序值,在该位置发现了要搜索的子串。下面的例子指出了INSTR函数的使用情况:
INSTR(‘AAABAABA’,’B’)=4
INSTR(‘AAABAABA’,’B’,1,2)=7
LAST_DAY
函数LAST_DAY返回实参数指定日期对应月份的最后一天。其具体的语法格式如下:
LAST_DAY(date)
其中:
date 一日期数值
下面是该函数的使用情况:
LAST_DAY(TO_DATE(’29-Dec-1988’,’dd-mon-yyyy’))
=’31-Dec-1988’
LAST_DAY(TO_DATE(’1-Feb-2000’,’dd-mon-yyyy’))
=’29-Feb-2000
LENGTH
LENGTH函数返回字符串的长度。
其具体的语法格式如下:
LENGTH(string)
其中:
string 任意字符串
下面是该函数的使用情况:
LENGTH(’This is short’)=13
LENGTH(’This is a bit longer’)=20
LN
函数LN返回一数值的自然对数。
其具体的语法格式如下:
LN(number)
其中:
number 大于0的任意数值
下面是该函数的使用情况:
LN(10)=2.3025851
EXP(2.3025851)=10
可见,将函数LN的结果作为参数,通过再调用函数EXP可得到函数LN的原参数值。
LOWER
函数LOWER返回参数指定字符串的小写形式。
其具体的语法格式如下:
LOWER(atring)
其中:
string 任意VARCHAR2型或CHAR型的数值
下面是该函数的使用情况:
LOWER(’This IS a Test’)=‘This is a test’
LPAD
LPAD函数从左面填充指定的字符串。
其具体的语法格式如下:
LPAD(string,numchars[,padding])
其中:
string 任意VARCHAR2型或CHAR型的字符串
numchars 返回字符串的字符个数
padding 填充字符。是一可选项,默认为单个空格
下面是该函数的使用情况:
LPAD(’Jenny’,10)=‘Jenny’(5 leading spaces)
LPAD(’Jenny’,10,’*’)=‘*****Jenny’
LPAD(’Jenny’,10,’*!’)=‘*!*!*Jenny’
LTRIM
LTRIM函数删除字符串左边的前缀字符。一般前缀字符总是空格。
其具体的语法格式如下:
LTRIM(string[,trimchars])
其中:
string 任意VARCHAR2型或CHAR型的数值
trimchars 待删除的字符串
下面是该函数的使用情况:
LTRIM(’ Jeff’)=‘Jeff’(leading spaces removed)
LTRIM(’*****Jeff’,’*’)=‘Jeff’
LTRIM(’*!*!*Jeff’,’*!’)=‘Jeff’
MOD
MOD函数返回一个数除以另一数的余数。
其具体的语法格式如下:
MOD(number,divisor)
其中:
number 任意数值
divisor 任意数值。该函数计算number/divisor的余数
下面是该函数的使用情况:
MOD(14,12)=2(1400 hours=2:00 O’Clock)
MOD(10,10)=0
MOD(10,0)=10
注意:如果除数为0则返回原来的数
MONTHS_BETWEEN
MONTHS_BETWEEN函数返回两个日期之间的月份数
其具体的语法格式如下:
MONTHS_BETWEEN(date_1,date_2)
其中:
date_1 日期型数据
date_2 一日期型数据
如果两个参数代表的是某月中的同一日期,或它们军代表的某月中的最后一天,则该函数返回一整型值。否则,将返回一分数。另外,如果第一个参数代表的日期小于第二个参数代表的日期,则返回一负值。
下面是该函数的使用情况:
MONTHS_BETWEEN(TO_DATE(’29-Dec-1999’,’dd-mon-yyyy’),
TO_DATE(’29-Dec-1988’,’dd-mon-yyyy’))=132
MONTHS_BETWEEN(TO_DATE(’29-Dec-1999’,’dd-mon-yyyy’),
TO_DATE(’24-Nov-1988’,’dd-mon-yyyy’))=133.16129
在第一个例子中,两个参数均为某一月中的29号,所以返回一整型值(其间相差的月份数),在第二个例子中,因两个参数表示的日期不是同一天,所以返回一分数(以31天为基础计算处理)
NVL
NVL函数需要两个参数。如果第一个参数为空,则返回第二个参数。第二个参数表示是第一个参数的替换对象。
其具体的语法格式如下:
NVL(value,alternative)
其中:
value 一个可为空的值。它不为空的时候将作为返回值
alternative 只要它不是空的就是将被返回的值
下面是该函数处理数据库的某些列包括空值时的情况:
SELECT emp_id,NVL(emp_name,’Name Missing!’)
FROM emp_table;
在该例中,如果雇员有名字,将通过该函数返回其名字。如果雇员的名字为空,将返回‘Name Missing!’
POWER
函数POWER返回一个数值的指定次幂。
其具体的语法格式如下:
POWER(number,power)
其中:
number 任意数值
power 幂次数。函数的结果其实即:number幂次。如果第 一个参数为负数,则该幂次数务必是一整数。其它 情况可为任意数值
下面是该函数的使用情况:
POWER(10,2)=100(10*10)
POWER(10,3)=1000
POWER(-10,3)=-1000
REPLACE
REPLACE函数在一字符串中搜索一指定的子串,并将其替换成另外一指定串。
其具体的语法格式如下:
REPLACE(string,substring[,replace_string])
其中:
string 被搜索字符串。在其中搜索待替换串
substring 被搜索子串。所有找到的子串均被替换掉
replace_string 替换结果子串。是一可选项。如果该参数被忽略, 则所有被搜索到的子串实质上均被删除
下面是该函数的使用情况:
REPLACE(’This is a test’,’is’,’was’)=‘Thwas was a test’
REPLACE(’This is a test’,’is’)=‘Th a test’
ROUND(for dates)
ROUND函数将一日期处理(舍入)成第二个参数指定的形式。
其具体的语法格式如下:
ROUND(date[,fmt])
其中:
date 一个日期数值
fmt 是一个日期格式说明符。指定日期将被处理成该说明符指定的 形式。省略该参数,则指定日期将被处理到最近的一天
下面是该函数的使用情况:
ROUND(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’))
=’25-Nov-1999 12:00:00 am’
ROUND(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’),’hh’)
=’24-Nov-1999 09:00:00 am’
注意:这种处理过程可能意味着日期数值的改变。在第一个例子中,因为8:00 PM是紧邻第二天的一精确值,所以处理结果被转到下一天(25th)而不是当天(24th).
ROUND(for number)
ROUND函数将一数值处理(舍入)成第二个参数指定的形式的十进制数。
其具体的语法格式如下:
ROUND(value,places)
其中:
value 试图转换的数值
places 结果中的十进制数位置。必须是一正整数,但可能是一负数。如果是负数,数字就被精确地处理在从小数点其左数的十进制数
下面是该函数的使用情况:
ROUND(89,985,2)=89.90(Note that .005 is rounded up.)
ROUND(89,985,-1)=90
ROUND(89,985,-2)=100