Oracle 常用函数详解___

当前位置: 数据库>oracle

Oracle 常用函数详解

来源: 互联网发布时间：2017-06-25

本文导语: 一、大小写转换函数 LOWER LOWER函数用小写形式替换字符串中的大写字符。语法:LOWER(s). select lower(100+100),lower('SQL'),lower(sysdate) from dual; UPPER UPPER函数用大...

一、大小写转换函数 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;

RTRIM

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;.

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

'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函数将数据转换为字符

格式元素

元素说明

格式

数字

字符结果

数字宽度

9999

显示前面的0

09999

0012

00012

小数点的位置

09999.999

030.40

00030.400

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

09999D999

030.40

00030.400

逗号的位置

09999,999

03040

00003,040

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

09999G999

03040

00003,040

美元

$099999

03040

$003040

当地货币

L099999

03040

GBP003040(如果nls_currency设置为GBP)

表示负数的减号的位置

99999MI

-3040

3040-

包围在括号内的负数

99999PR

-3040

EEEE

科学计数法

99.99999EEEE

121.976

1.21976E+02

Nls_dual_currency

U099999

03040

CAD003040(如果nls_dual_currency设置为CAD)

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

9999V99

3040

304000

前面加上+或者-

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

格式元素

说明

结果

年的最后一位

年的最后两位

YYY

年的最后三位

975

YYYY

4位数字表示的年

1975

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

YEAR,year,Year

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

NINETEEN SEVENTY FIVE,

Nineteen seventy five,

Nineteen Seventy Five

两位数表示的月

MON,mon,Mon

月的三个字母缩写

JUN,jun,Jun

MONTH,month,Month

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

JUNE,june,June

星期的第几天

月的两位数日

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.

子午线指示器

HH、HH2和HH24

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

09,09,21

分(0~59)

秒(0~59)

SSSSS

午夜之后的秒(0~86399)

77713

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

格式元素

说明和格式掩码

结果

/ . , ? # ! -

标点符号:’MM.YY’

09.08

“any character literal”

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

Week 2 of September

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

12TH of September

拼写出数字:’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 下载见

：