Oracle 常用函数 详解

2017-01-04 10:31:44来源:作者:Linux公社人点击

一、大小写转换函数 LOWER

LOWER 函数用小写形式替换字符串中的大写字符。语法:LOWER(s).

select lower(100+100),lower('SQL'),lower(sysdate) from dual;

UPPER

UPPER 函数用大写形式替换字符串中的小写字符。语法:UPPER(s).

select upper('sql') from dual;

INITCAP

INITCAP 函数将字符串转换为首字母大写的形式。字符串中每个单词的第一个字母都被转换为大写形式,面每个单词余下的字母被转换为小写字母形式。单词之间用空格或者下划线分开,但有些字符( 如百分比符号(%) 、感叹号(!) 或者美元符号($)) 也都是有效的单词分隔符。标点符号和特殊字符也是有效的单词分隔符。语法:INITCAP(s)

select initcap('init cap or init_cat or init%cap') from dual;

二、字符操作函数 CONCAT

CONCAT 函数 连接两个 字符字面值、列或者表达式从而生成一个更大的字字符表达式。语法 :CONCAT(s1,s2)

select concat('Today is:',SYSDATE) FROM DUAL;

CONCAT 只能使用两个参数

select concat('Outer1 ',concat('Inner1',' Inner2')) from dual;

LENGTH

LENGTH 函数返回组成字符串的字符数。空格、制表符和特殊字符都被LENGTH 函数计算在内。 只有一个参数 , LENGTH(s) 。(制表符算1 )

select length('ab cd e') from dual;

LPAD 和RPAD

LPAD(RPAD) 函数返回给定字符串左( 右) 边填充指定数量的字符后形成的合成字符串。用于填充的字符串包括字符字面值、列值、表达式、空格( 默认) 、制表符和特殊字符。

LPAD 和RPAD 函数有三个参数,语法:RPAD(s,n,p) 和LPAD(s,n,p) 。s 表示源字符串,n 表示返回字符串的最终长度,p 指定用于填充的字符串。

select LPAD('abc',6,'*'),RPAD('abc',6,'*') from dual;

TRIM

TRIM 函数从字符值的开头或结尾删除一些字符,从面生成一个更简短的字符项。

TRIM 函数使用的参数由一个强制组成部分和一个可选组成部分构成。语法:TRIM([trailing|leading|both] trimstring from s) 。被修整的字符串(s) 是强制的。 只能指定一个修正字符。

TRIM(s) 删除输入字符串两边的空格。

TRIM(trailing trimstring from s) 从字符串s 的结尾删除所有trimstring( 如果存在的话) 。

TRIM(leading trimstring from s) 从字符串s 的开头删除所有trimstring( 如果存在的话) 。

TRIM(both trimstring from s) 从字符串s 的开头和结尾删除所有trimstring( 如果存在的话) 。

select trim(both '*' from '*****Hidden*****'),trim(leading '*' from '*****Hidden*****'),trim(trailing '*' from '*****Hidden*****'),trim(both from ' Hidden '),trim(trailing from ' Hidden'),trim(leading from 'Hidden ') from dual;

R TRIM

RTRIM函数从字符值的结尾删除一些字符,从而生成一个更简短的字符串。 语法:RTRIM(string[,trimstring]),被修整的string是必须的, 可以删除多个字符 。默认删除空格。 select rtrim('abcd ') from dual; select rtrim('abcd***','*') from dual; select rtrim('abcd*#','*#') from dual;

LTRIM

LTRIM函数从字符值的开头删除一些字符,从而生成一个更简短的字符串。 语法:RTRIM(string[,trimstring]),被修整的string是必须的, 可以删除多个字符 。默认删除空格。 select ltrim(' abcd') from dual; select ltrim('***abcd','*') from dual; select ltrim('*#abcd','*#') from dual;

INSTR

INSTR 函数确定搜索字符串在给定字符串内的位置。它返回数字位置,在这个位置上,搜索字符串开始第n 次出现( 相对于指定的起始位置而言) 。如果搜索字符串不存在,则返回0.

INSTR 函数使用两个可选参数和两个强制参数。语法:INSTR(source string,search string,[search start position],[nth occurrence]) 。search start position 的默认值是1 或者source string 的开头。nth occurrence 的默认值是1 或者第一次出现。

select instr('1#3#5#7#9#','#') from dual;

从左第1 个字符往右,返回'#' 第1 次出现的位置。

select instr('1#3#5#7#9#','#',5) from dual;

从左第5 个字符往右,返回'#' 第1 次出现的位置。

select instr('1#3#5#7#9#','#',3,4) from dual;

从左第3 个字符往右,返回'#' 第4 次出现的位置。

select instr('1#3#5#7#9#','#',3,10) from dual;

从左第3 个字符往右,返回'#' 第10 次出现的位置,没有找到返回0.

select instr('1#3#5#7#9#','#',-1) from dual;

从右第1 个字符往左,返回'#' 第1 次出现的位置。

select instr('1#3#5#7#9#','#',-1,3) from dual;

从右第1 个字符往左,返回'#' 第3 次出现的位置。

select instr('1#3#5#7#9#','#',-3,3) from dual;

从右第3 个字符往左,返回'#' 第3 次出现的位置。

SUBSTR

SUBSTR 函数从给定源字符串中给定的位置开始,提取指定长度的字符串。如果起始位置大于源字符串的长度,就会返回null 。如果从给定起始位置提取的字符数大于源字符串的长度,返回的部分是从起始位置到字符串结尾的子字符串。

SUBSTR 函数有三个参数,前两个是强制的。语法:SUBSTR(source string,start position,[number of characters to extract]) 。要提取的默认字符数是从start position 到source string 结尾的字符数。

select substr('1#3#5#7#9#',5) from dual;

从从左到右数第5 个字符处开始提取,从左到右提取,一直到源字符串结尾。

select substr('1#3#5#7#9#',5,3) from dual;

从从左到右数第5 个字符处开始提取,从左到右提取,提取3 个字符。

select substr('1#3#5#7#9#',-3,2) from dual;

从从右到左数第3 个字符处开始提取,从左到右提取,提取2 个字符。

select substr('1#3#5#7#9#',-3,-2) from dual;

REPLACE

REPLACE 函数用替换项取代源字符串中出现的所有搜索项。如果替换项的长度与搜索项的长度不同,那么返回字符串的长度与源字符串的长度也不同。如果没有找到搜索字符串,就会原封不动的返回源字符串。

REPLACE 函数有三个参数,前两个是强制的。语法:REPLACE(source string,search item[,replacement term]) 。如果省略replacement term 参数,就会从source string 中删除所有出现的search item 。

select replace('1#3#5#7#9#','#','->') from dual;

select replace('1#3#5#7#9#','#') from dual;

TRANSLATE 三、数字函数 ROUND

ROUND 函数依据指定的小数精度对数值进行舍入运算。返回依据有效数字以指定的小数精度进行 上舍入或者下舍入的值。如果指定的的小数精度为 n ,则要舍入的有效数据在小数点右边(n+1) 个位置。如果n 为负数,那么要舍入的有效数字在小数点右边n 个位置。如果有效数据的数据大于或者等于5 ,就进行“上舍入”,其他情况进行“下舍入”。

ROUND 函数有两个参数。语法:ROUND(source number,decimal precision) 。source number 参数表示任何数字值。decimal precision 参数指定舍入的精度,它是可选的。如果没有指定decimal precision 参数,则舍入的默认精度是0 ,也就是说将源数字舍入为最接近的整数。

select round(1601.916,1) from dual;

select round(1601.916,2) from dual;

select round(1601.916,-1) from dual;

select round(1601.916,-3) from dual;

select round(1601.916) from dual;

TRUNC

TRUNC 函数依据指定的小数精度对数据执行截取运算。数字截取不同于舍入,如果小数精度的正数的话,最后的值依据指定的小数精度删除数字,并 不进行向上或者向下舍入。然而, 如果指定的小数精度 (n) 为负数,输入值从小数点左边第n 个数位开始向后归0 。

TRUNC 函数有两个参数。语法:TRUNC(source number,decimal precision) 。Source number 表示任何数字值。Decimal precision 指定截取的精度,它是可选的。如果没有指定decimal precision 参数,那么默认精度为0 ,即将source number 截取到最接近的整数。

select trunc(1601.916,1) from dual;

select trunc(1601.916,2) from dual;

select trunc(1601.916,-1) from dual;

select trunc(1601.916,-3) from dual;

select trunc(1601.916) from dual;

MOD

MOD 函数返回除法运算的余数。提供两个数——被除数和除数,执行除法运算。如果除数是被除数的因数,MOD 就返回0 ,因为没有余数。如果除数等于0 ,则返回no division by zero 错误,MOD 函数也返回0 。如果除数大于被除数,那么MOD 函数返回被除数作为结果。

MOD 函数有两个参数。语法:MOD(dividend,divisor) 。dividend 和divisor 参数都可以表示数字字面值、列或者表达式。可以是正数也可以是负数。

select mod(6,2) from dual;

select mod(5,3) from dual;

select mod(7,35) from dual;

select mod(5.2,3) from dual;

select mod(-5,3) from dual;

MOD 函数通常用来区分奇数和偶数。

四、日期函数 SYSDATE

SYSDATE 函数没有参数,它返回数据库服务器当前的系统日期和时间。

select sysdate from dual;

日期运算

Date1-Date2=Num1

可以从另一个日期中减去日志。这两个日期项之间的差值表示它们之间的天数。可以将所有数字(包括小数)添加到日期项或者从日期项中减去。在该上下文中,数字表示天数。数字和日期项之间的和或者差值总是返回日期项。不允许相加、相乘或者相除两个日期项。

select to_date('31-jan-01')-to_date('01-jan-01') from dual;

select sysdate + 1 from dual;

MONTHS_BETWEEN

MONTHS_BETWEEN 函数返回表示两个强制的日期参数之间月数的数值。语法:MONTHS_BETWEEN(date1,date2) 。计算date1 和date2 之间朋份的差值( 每月31 天) 。如果date1 在date2 之前就反加负数。这两个日期参数之间的差值可能由整数和小数部分组成。整数表示这两个日期之间的朋数。小数部分表示计算年和月之间整数差值这后剩余的天数和时间,以31 天的月份为基础。如果要比较的日期的日组成部分相同或者是各自月份的最后一天,那么就返回没有小数部分的整数。

select months_between(sysdate,sysdate-31) from dual;

select months_between('29-mar-2008','28-feb-2008') from dual;

select months_between('29-mar-2008','28-feb-2008')*31 from dual;

ADD_MONTHS

ADD_MONTHS 函数返回日期项,这个日期项通过将指定月数添加到给定日期计算得出。

ADD_MONTHS 函数有两个强制参数。语法:ADD_MONTHS(start date,number of months) 。在将指定的月数添加到start date 之后,函数才计算目标日期。月数可能是负数,这样返回的目标日期就早于起始日期。number of months 可以是小数,但会忽略小数部分,而使用整数部分。

select add_months('07-APR-2009',1) from dual;

select add_months('07-APR-2009',2.5) from dual;

select add_months('07-APR-2009',-12) from dual;

NEXT_DAY

NEXT_DAY 函数返回的日期是星期内指定的日子下一次出现时的日期。

NEXT_DAY 函数有两个强制参数。语法:NEXT_DAY(start date,day of the week) 。函数计算在start date 之后day of the week 参数下一次出现的日期。day of the week 参数可以是字符值或者整数值。可接受的值由NLS_DATE_LANGUATE 数据库参数确定,但默认值至少是日子名称的前三个字符或者整数值,其中1 表示星期日,2 表示星期一,以此类推。在任何情况下都应该指定表示星期几的字符值。简短名称可以大于三个字符,例如星期日可以表示为sun 、sund 、sunda 或者sunday 。

select next_day('01-JAN-2009','tue') from dual;

select next_day('01-JAN-2009','WEDNE') from dual;

select next_day('01-JAN-2009',5) from dual;

LAST_DAY

LAST_DAY 函数返回指定日子所属的月的最后一天的日期。

LAST_DAY 函数有一个强制参数。语法:LAST_DAY(start date) 。该函数提取start date 参数所属的月,并计算该月最后一天的日期。

select LAST_DAY ('01-JAN-2009') from dual;

日期ROUND

日期ROUND 函数依据指定的日期精度格式对值进行舍入运算。返回的值要么向上舍入要么向下舍入为最接近的日期精度格式。

日期ROUND 函数使用一个强制参数和一个可选参数。语法:ROUND(source date[,date precision format]) 。source date 参数表示任意日期项。date precision format 参数指定舍入的精度,是可选的,如果没有指定,默认的舍入精度是日。date precision formats 包括世纪(CC) 、年(YYYY) 、季度(Q) 、月(MM) 、星期(W) 、日(DD) 、时(HH) 和分(MI) 。

向上传入到世纪相当于给当前世纪加1 个世纪。如果日部分大于16 ,就会向上舍入到下一个月,否则就会向下舍入到当月的开头。如果月在1 和6 之间,那么舍入到年就会返回当年开头的日期,否则返回下一年开头的日期。

select round(sysdate) day,round(sysdate,'w') week,round(sysdate,'month') month,round(sysdate,'year') year from dual;

select round(sysdate,'cc') cc,round(sysdate,'q') q,round(sysdate,'hh'),round(sysdate,'mi') min hour from dual;

日期TRUNC

日期TRUNC 函数依据指定的日期精度格式对值进行截取运算。

日期TRUNC 函数使用一个强制参数和一个可选参数。语法:TRUNC(source date[,date precision format]) 。source date 参数表示任意日期项。date precision format 参数指定截取的精度,它是可选的,如果没有指定,默认的截取精度是日。即source date 的所有时间部分都设置为午夜(00:00:00) 。月级别上的截取将source date 的日期设置为该月的第一天。年级别上的截取返回当年开头的日期。

select trunc(sysdate) day,trunc(sysdate,'w') week,trunc(sysdate,'month') month,trunc(sysdate,'year') year from dual;

五、隐式数据类型转换

如果可能,可以将数据类型与函数所需参数的数据类型不相符的值隐式转换为所需的格式。VARCHAR2 和CHAR 数据类型统称为字符类型。字符字段非常灵活,几乎允许存储所有类型的信息。因此,可以方便地将DATE 和NUMBER 值转换为它们的字符形式。这些转换称为数字到字符(number to character) 和日期到字符(date to character) 转换。

select length(1234567890) from dual;

select length(0123456789) from dual;

select length(sysdate) from dual;.

将字符数据隐式转换为数字数据类型的情况并不常见,因为出现这种情况的唯一条件是该字符数据表示有效数字。

当字符串符合下面的日期格式时,可以实现隐式字符到日期(character to date) 的转换:[D|DD] separator1 [MON|MONTH] separator2 [R|RR|YY|YYYY] 。D 和DD 分别表示月份中1 位和2 位的日子。MON 是月的三字符缩写词,而MONTH 是月的全名。R 和RR 分别表示满意位和2 位数字的年。YY 和YYYY 分别表示2 位和4 位数字的年。separator1 和separator2 元素可以是大多数标点符号、空格和制表符。

'24-JAN-09' DD-MON-RR

'1//january/8' D//MONTH/R

'13*jan*8' DD*MON*R

'13/feb/2008' DD/MON/YYYY

'01$jan/08' DD$MON/RR

'24-JAN-09 18:45' DD-MON-RR HH24:MI

六、转换函数 TO_CHAR 函数将数据转换为字符

TO_CHAR 函数返回VARCHAR2 数据类型的值。当将它应用于NUMBER 数据类型的值时TO_CHAR(num1[,format mask[,nls_parameters]])

num 参数是强制性的,它必须是一个数字值。可选的format 参数用来指定数字格式信息——例如宽度、货币符号、小数点的位置和组( 或者千位) 分隔符,必须将它们包含在单引号内。除此之外,对于要转换为字符的数字而言,还有其他一些格式信息的选项。

select to_char(00001) from dual;

select to_char(00001,'099999') from dual;

TO_CHAR 函数将数据转换为字符

格式元素

元素说明

格式

数字

字符结果

9

数字宽度

9999

12

12

0

显示前面的0

09999

0012

00012

.

小数点的位置

09999.999

030.40

00030.400

D

小数分隔符的位置( 默认为名点)

09999D999

030.40

00030.400

,

逗号的位置

09999,999

03040

00003,040

G

组分隔符的位置( 默认为逗号)

09999G999

03040

00003,040

$

美元

$099999

03040

$003040

L

当地货币

L099999

03040

GBP003040( 如果nls_currency 设置为GBP)

MI

表示负数的减号的位置

99999MI

-3040

3040-

PR

包围在括号内的负数

99999PR

-3040

<3040>

EEEE

科学计数法

99.99999EEEE

121.976

1.21976E+02

U

Nls_dual_currency

U099999

03040

CAD003040( 如果nls_dual_currency 设置为CAD)

V

乘以10n 次(n 是V 之后9 的数量)

9999V99

3040

304000

S

前面加上+ 或者-

S999999

3040

+3040

TO_CHAR 函数将日期转换为字符

使用TO_CHAR 函数,可以利用各种格式模型将DATE 项转换为几乎所有日期的字符表示形式。

语法:TO_CHAR(date1[,format[,nls_parameter]])

只有date1 参数是强制的,date1 必须是可以被隐式转换为日期的值。可选的format 参数区分大小写,必须奖它包含在单引号内。格式掩码指定哪些日期元素,是用长的名称还是用缩写名称来描述这个元素。还会自动给日和月的名称填充空格。可以使用格式掩码的修饰符来删除这些空格,这个修饰符称为填充模式(fm) 运算符。在格式模型之前添加字母fm ,就会命令 Oracle 从日和月的名称中删除所有空格。对于被转换为字符串的日期而言还有许多格式选项。

select to_char(sysdate) || ' is today''s date' from dual;

select to_char(sysdate,'Month') || 'is special time ' from dual;

select to_char(sysdate,'fmMonth') || 'is special time ' from dual;

to_char 把日期转换为字符串

假设格式元素作用于日期02-JUN-1975

格式元素

说明

结果

Y

年的最后一位

5

YY

年的最后两位

75

YYY

年的最后三位

975

YYYY

4 位数字表示的年

1975

RR

两们数字表示的年( 已知世纪)

75

YEAR,year,Year

区分大小写并用英语拼写的年

NINETEEN SEVENTY FIVE,

Nineteen seventy five,

Nineteen Seventy Five

MM

两位数表示的月

06

MON,mon,Mon

月的三个字母缩写

JUN,jun,Jun

MONTH,month,Month

区分大小写并用英语拼写的月

JUNE,june,June

D

星期的第几天

2

DD

月的两位数日

02

DDD

年的日

153

DY,dy,Dy

星期的三个字母缩写

MON,mon,Mon

DAY,day,Day

区分大小写并用英语拼写的星期

MONDAY,Monday,Monday

提取日期时间数据类型的时间部分,表中使用的日期为27-JUN-2010 21:35:13

格式元素

说明

结果

AM,PM,A.M. 和P.M.

子午线指示器

PM

HH 、HH2 和HH24

一天的小时,1-12 时和0-23 时

09,09,21

MI

分(0~59)

35

SS

秒(0~59)

13

SSSSS

午夜之后的秒(0~86399)

77713

其他一些能够在日期时间格式模型中使用的元素。标点符号用来分隔格式元素。有三种类型的后缀可以格式化日期时间元素的组件。而且,如果将字符字面值包含在双引号内,那么就能够在返回值中包含它们。使用日期12/SEP/08 14:31

格式元素

说明和格式掩码

结果

/ . , ? # ! -

标点符号:’MM.YY’

09.08

“any character literal”

字符字面值:’”Week” W “of” Month’

Week 2 of September

TH

位置或者序数文本:’DDth ”of” Month’

12TH of September

SP

拼写出数字:’MmSP month Yyyysp’

Nine September Two Thousand Eight

THSP or SPTH

拼写出位置或者序数:’hh24SpTh’

Fourteenth

使用TO_DATE 函数将字符转换为日期

TO_DATE 函数返回DATE 类型的值。转换为日期的字符串可能包含所有或者部分组成DATE 的日期时间元素。当只转换包含日期时间元素子集的字符串时,Oracle 提供资金默认值来构造完整的日期。字符串的组成部分通过格式模型或掩码与不同的日期时间元素相关联。

语法:TO_DATE(string1[,format,[nls_parameter]])

只有string1 参数是强制性的,如果没有提供格式掩码,string1 会隐式转换为日期。几乎总是使用可选的fromat 参数,在单引号内指定它,与TO_CHAR 的格式掩码相同。TO_DATE 函数有fx 修饰,表示string1 和格式掩码必须完全匹配,否则报错。

select to_date('25-DEC-2010') from dual;

select to_date('25-DEC') from dual; -- 错误

select to_date('25-DEC','DD-MON') from dual;

select to_date('25-DEC-2010 18:03:45','DD-MON-YYYY HH24:MI:SS') from dual;

select to_date('25-DEC-10','fxDD-MON-YYYY') from dual;-- 错误

TO_NUMBER 函数将字符转换为数字

TO_NUMBER 函数返回NUMBER 类型的值。转换为数字的字符串必须有合适的格式,以便用相应的格式掩码转换或删除所有非数字组成部分。

语法:TO_NUMBER(string1[,format,[nls_parameter]])

只有string1 参数是强制性的,如果没有提供格式掩码,string1 就必须是可以隐式转换为数字的值。用单引号指定可选的format 参数。与TO_CHAR 转换数字到字符串中的格式掩码相同。

select to_number('$1,000.55') from dual;-- 错误

select to_number('$01,000.55','$0999,999.999') from dual;

注:TO_NUMBER 函数将字符项转换为数字。如果使用较短的格式掩码转换数字,就会返回错误,如果使用较长的格式掩码转换数字,就会返回原数字。

七、条件函数 NVL

NVL 函数评估任何数据类型的列或者表达式是不是空值。如果原始项是空值,返回备选的非空值;否则,返回原始项。

NVL 函数有两个强制参数。语法:NVL(original,ifnull) 。其中original 表示要测试的项,如果original 项计算为空,就返回ifnull 。 original 和ifnull 参数的数据类型必须一致。它们必须是相同的类型,或者可能将ifnull 隐式转换为original 参数的类型 。 NVL 函数返回值的数据类型与original 参数的数据类型相同。

select nvl(1234) from dual;-- 错误

select nvl(null,1234) from dual; ---1234

select nvl(substr('abc',4),'No substring exists') from dual;

NVL2

NVL2 函数是对NVL 函数的增强,但功能非常类似。NVL2 函数评估任何数据类型的列或者表达式是不是空值。如果第一项不是空值,那么返回第二个参数,否则返回第三个参数。

NVL2 函数有三个强制参数。语法:NVL2(original,ifnotnull,ifnull), 其中original 表示被测试的项。如果original 不是空值,就返回ifnotnull ;如果original 是空值,就返回ifnull 。 ifnotnull 和ifnull 参数的数据类型必须一致或者ifnull参数可以转换为ifnotnull参数的数据类型 ,它们不能是 LONG 数据类型。它们可以是相同的类型, 或者可以将 ifnull 转换为ifnotnull 参数的类型。 NVL2 函数返回的数据类型与ifnotnull 参数的数据类型相同。

select nvl2(1234,1,'a string') from dual; -- 错误

select nvl2(null,1234,5678) from dual; --5678

select nvl2(substr('abc',2),'Not bc','No substring') from dual;

NULLIF

NULLIF 函数测试两项的相等性。如果它们相等,函数就返回空值,否则返回这两个测试项的第一项。

NULLIF 函数有两个可以是任何数据类型的强制参数,两个参数类型必须一致,第一个参数不可以为空。 语法 :NULLIF (ifunequal,comparison_term) ,其中比较参数ifunequal 和comparison_term 。如果它们相同,返回NULL 。如果它们不同,返回ifunequal 参数。

select nullif(1234,1234) from dual;

select nullif('24-JUL-2009','24-JUL-09') from dual;

select nullif(1,null) from dual; --1

select nullif(null,null) from dual;----返回ORA-00932: inconsistent datatypes: expected - got CHAR

COALESCE

COALESCE 函数从参数列表中返回第一个非空值。如果所有参数为空,那么返回空值。

COALESCE 函数有两个强制参数和任何数量的可选参数。语法:COALESCE(expr1,expr2,...,exprn) ,如果expr1 不是空值,就返回它,否则,如果expr2 不是空值,就返回它,以此类推。COALESCE 函数是NVL 函数的一般形式:

COALESCE(expr1,expr2)=NVL(expr1,expr2)

COALESCE(expr1,expr2)=NVL(expr1,NVL(expr2,expr3))

如果找到非空值, COALESCE 返回的数据类型与第一个非空参数的数据类型相同。为了避免出现“ ORA-00931:inconsistent date types ”错误,所有非空参数的数据类型必须与第一个非空参数的数据类型一致。

select coalesce(null,null,null,'a string') from dual;

select coalesce(null,null,null) from dual;

select coalesce(substr('abc',4),'not bc','no substring') from dual;

select coalesce(substr('abc',4),'not bc',123) from dual;-- 错误

DECODE

DECODE 函数通过测试前两项的相等性来实现if-then-else 条件逻辑,如果它们相等,则返回第三个参数,如果它们不相等,可能返回另一项。

DECODE 函数至少使用三个强制参数,但可以使用更多参数。语法:DECODE(expr1,comp1,iftrue1[,comp2,iftrue2...[,compN,iftrueN]][,iffalse]) 。这些参数的计算如下面的伪代码示例:

if expr1 = comp1 then return iftrue1

else if expr1 = comp2 then return iftrue 2

...

...

else if expr1 = compN then return iftrueN

else return null | iffalse;

DECODE 函数的所有参数都可以是表达式。返回的数据类型与第一个匹配比较选项的数据类型相同的。 表达式 expr1 被隐式转换为第一个比较参数comp1 的数据类型。计算其他比较参数comp2...compn 时,也会将它们隐式转换为comp1 相同的数据类型。 DECODE 认为两个空值相等 ,因此如果 expr1 是空值,并且comp3 是出现的第一个空值比较参数,那么就会返回对应的结果参数iftrue3 。

select decode(1234,123,'123 is a match') from dual;

select decode(1234,123,'123 is a match','No match') from dual;

select decode('search','comp1','true1','comp2','true2','search','true3',substr('2search',2,6),'true4','false') from dual;

select decode(null,'comp1','true1','comp2','true2',null,'true3','false') from dual;

select decode(1234,123,123,'No match') from dual; --‘No match'ORA-01722: invalid number

CASE 表达式

CASE 表达式在所有第三和第四代编程语言都可以实现。和DECODE 函数一样,CASE 表达式使用if-then-else 条件逻辑。CASE 表达式有两个变体。简单的CASE 表达式列出条件搜索项一次,由每个比较表达式来测试与搜索项的相等性。搜索的CASE 表达式列出每个比较表达式的单独条件。

CASE 表达式至少使用三个强制参数,但可以使用更多参数。其语法取决于是使用简单CASE 表达式还是使用搜索的CASE 表达式。

简单CASE 表达式的语法:

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison_expr2 THEN iftrue2

...

WHEN comparison_exprN THEN iftureN]

[ELSE iffalse]

END

简单CASE 表达式包含在CASE...END 代码块内,由至少一个WHEN...THEN 语句组成。在最简单的情况下——只有一个WHEN...THEN 语句,search_expr 与comparison_expr1 进行比较。如果它们相等,姥返回结果iftrue1 。如果不相等,那么返回一个空值,除非定义了ELSE 组件,这个情况返回默认的iffalse 值。当CASE 表达式中有多个WHEN...THEN 语句时,就会不断搜索匹配的比较表达式,直到找到匹配项为止。

搜索、比较和结果参数可以是列值、表达式或都字面值,但必须都是相同数据类型。

select

case substr(1234,1,3)

when '134' then '1234 is a match'

when '1235' then '1235 is a match'

when concat('1','23') then concat('1','23')|| ' is a match'

else 'no match'

end

from dual;

搜索的CASE 表达式的语法:

CASE

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

...

WHEN conditionN THEN iftrueN]

[ELSE iffalse]

END

搜索的CASE 表达式包含在CASE...END 代码块内,由至少一个WHEN...THEN 语句组成。在最简单的情况下——只有一个WHEN...THEN 语句,计算condition1 ;如果它是true ,那么返回结果iftrue1 。如果不是,那么就返回一个空值,除非定义了ELSE 组件,这种情况下,返回默认的iffalse 值。当CASE 表达式中有多个WHEN...THEN 语句时,就会不断搜索匹配的比较表达式,直到找到匹配项为止。

select

case

when length(substr(1234,1,3)) = 1 then 'length of substring is 1'

when length(substr(1234,1,3)) = 2 then 'length of substring is 2'

when length(substr(1234,1,3)) = 3 then 'length of substring is 3'

else 'no match'

end

from dual;

参考:OCP/OCA认证考试指南全册 Oracle 11g(1Z0-051,1Z0-052,1Z0-053)中文完整版PDF 下载见 http://www.linuxidc.com/Linux/2017-01/139049.htm

本文永久更新链接地址 : http://www.linuxidc.com/Linux/2017-01/139064.htm

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台