SQL内置函数（二）

SQL内置函数（二）
• DECODE(一)
DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。区别于SQL的其它函数，DECODE函数还能识别和操作空值。
其具体的语法格式如下：
DECODE(input_value,value,result[,value,result…][,default_result]);
其中：
input_value 试图处理的数值。DECODE函数将该数值与一系列的序偶相比 较，以决定最后的返回结果
value 是一组成序偶的数值。如果输入数值与之匹配成功，则相应 的结果将被返回。对应一个空的返回值，可以使用关键字 NULL于之对应
result 是一组成序偶的结果值
default_result 未能与任何一序偶匹配成功时，函数返回的默认值
下面的例子说明了，人们如何读取用户CHECKUP表SEAPARK中的BLOOD_TEST_FLAG列下的项目，作为DECODE函数的实参支持值。
SELECT checkup_type,
DECODE(blood_test_flag,’Y’,’Yes’,’N’,’No’,NULL,’None’,
’Invalid’)
FROM checkup;
SQL内置函数
• 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

SQL内置函数
该例告诉我们，1995、1996和1997年各出生了多少动物。每一行显示不同的年。一旦希望将这些数值显示成3列，该如何处理？可以通过如表B-1所示的方法处理之。
SQL内置函数
• EMPTY_BLOB
EMPTY_BLOB函数返回一空的BLOB指针，该指针可以用在INSERT或UPDATE命令中，以初始化BLOB列项。
其具体的语法格式如下：
EMPTY_BLOB()
该函数没有参数。下面的例子说明了如何使用该函数初始化BLOB列，并作为一个新行插入到表中。
INSERT INTO some_table
(blob_column)
VALUEＳ(EMPTY_BLOB());
SQL内置函数
• EMPTY_CLOB
EMPTY_CLOB函数与EMPTY_BLOB函数的功能类似，只不过对应CLOB而已。它返回一空的CLOB指针，该指针可以用在INSERT或UPDATE命令中，以初始化CLOB列。
其具体的语法格式如下：
EMPTY_CLOB()
该函数没有参数。下面的例子说明了如何使用该函数初始化CLOB列，并作为一个新行插入到表中。
INSERT INTO some_table
(clob_column)
VALUE(EMPTY_CLOB());

SQL内置函数
• EXP
EXP函数返回e的一个幂。在数学上e经常被用来表示一指定的特殊数值（一个十进制的无限数）2.178…，它是数学的基础。其具体的语法格式如下：
EXP(exponent)
其中：
exponent 返回e的少次（exponent）幂
下面是该函数的使用情况：
EXP(1)=2.7182818
EXP(3)=20.085537(2.71828182.71828182.7182818)
SQL内置函数
• FLOOR
FLOOR函数返回一个小于或等于给定十进制数的最大整数。该函数的工作机制与函数CEIL的情况极为相似，但却正好相反。
其具体的语法格式如下：
FLOOR(number)
其中：
number 任意数，包括十进制数
下面是该函数的使用情况：
FLOOR（5.1）=5
FLOOR（-5.1）=-6
为了理解函数FLOOR和函数CEIL在处理负数时的不同机制，可以比较函数FLOOR（5.1）和函数CEIL(-5.1)的执行结果。
SQL内置函数
• 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’，并且，也是函数的返回结果。

SQL内置函数
• HEXTORAW
函数HEXTORAW将一个由十六进制字符组成的字符串转换成一个raw数值。
其具体的语法格式如下：
HEXTORAW（string）
其中：
string 十六进制字符组成的字符串数据
SQL内置函数
• INITCAP
INITCAP函数接收一字符串，并转换该字符串中的所有单词，以使各单词均以大写字母开头。
其的语法格式如下：
INITCAP(string)
其中：
string 任意一VARCHAR2或CHAR型数据
下面是该函数的使用情况。
INITCAP(‘This is a test’=‘This is a test’
SQL内置函数
• INSTR
函数INSTR会告诉用户，其中的一个字符串（参数）是否在另一个字符串（也是参数）当中。
其具体的语法格式如下：
INSTR（string,substring[,start[,occurrence]]）
其中：
string 待查询的字符串
substring 正在搜索的字符串
start 说明开始搜索的字符位置。默认值是1，就是说，搜 索将从字符串的第一个字符开始。如果，参数为负 则表示搜索的位置从右边开始计算，而不是默认的 从左边开始
occurrence 指定试图搜索的子串的第几次出现，默认值是1，意 味着希望其首次出现
该函数反馈一索引顺序值，在该位置发现了要搜索的子串。下面的例子指出了INSTR函数的使用情况：
INSTR(‘AAABAABA’,’B’)=4
INSTR(‘AAABAABA’,’B’,1,2)=7
SQL内置函数
• INSTRB
该函数与INSTR的功能极为类似，也将反馈其中的一个字符串（参数）是否在另一个字符串（也是参数）当中。只是该函数返回的是一字节索引位置，而不是一字符索引位置。其实，仅仅在多个字符集同时被使用时，这种不同才有意义。
其具体的语法格式如下：
INSTRB（string,substring[,start[,occurrence]]）
其中：
string 待查询的字符串
substring 正在搜索的字符串
start 说明开始搜索的字符位置。默认值是1，就是说，搜索将从字 符串的第一个字符开始。如果，参数为负则表示搜索的位置从 右边开始计算，而不是默认的从左边开始
occurrence 指定试图搜索的子串的第几次出现，默认值是1，意味着希望 其首次出现
该函数反馈一索引顺序值，在该位置发现了要搜索的子串。下面的例子指出了INSTRB函数的使用情况：
INSTRB(‘AAABAABA’,’B’)=4
INSTRB(‘AAABAABA’,’B’,1,2)=7
SQL内置函数
• 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
SQL内置函数
• 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’（一顺序为基础），这就是函数的返回结果。