Oracle 常用函数详解

106次阅读

共计 17830 个字符，预计需要花费 45 分钟才能阅读完成。

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

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

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

select upper(‘sql’) from dual;

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

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

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

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

CONCAT只能使用两个参数

select concat(‘Outer1 ‘,concat(‘Inner1′,’ Inner2′)) from dual;

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

select length(‘ab cd e’) from dual;

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([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(string[,trimstring])，被修整的 string 是必须的，可以删除多个字符。默认删除空格。
select rtrim(‘abcd ‘) from dual;
select rtrim(‘abcd***’,’*’) from dual; select rtrim(‘abcd*#’,’*#’) from dual;

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

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函数从给定源字符串中给定的位置开始，提取指定长度的字符串。如果起始位置大于源字符串的长度，就会返回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(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;

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函数依据指定的小数精度对数据执行截取运算。数字截取不同于舍入，如果小数精度的正数的话，最后的值依据指定的小数精度删除数字，并不进行向上或者向下舍入。然而，如果指定的小数精度 (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就返回 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函数没有参数，它返回数据库服务器当前的系统日期和时间。

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(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(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(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(start date)。该函数提取start date 参数所属的月，并计算该月最后一天的日期。

select LAST_DAY（’01-JAN-2009′) from dual;

日期 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(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函数返回 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 函数，可以利用各种格式模型将 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函数返回 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函数返回 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(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函数是对 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（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(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函数通过测试前两项的相等性来实现 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表达式在所有第三和第四代编程语言都可以实现。和 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 语句时，就会不断搜索匹配的比较表达式，直到找到匹配项为止。