《oracle函数列表-程序员必备宝典》由会员分享，可在线阅读，更多相关《oracle函数列表-程序员必备宝典（9页珍藏版）》请在金锄头文库上搜索。
1、PL/SQL 单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在 SQL 中 Oracle 内建了一系列函数，这些函数都可被称为 SQL 或 PL/SQL 语句，函数主要分为两大类: 单行函数; 组函数本文将讨论如何利用单行函数以及使用规则。SQL 中的单行函数SQL 和 PL/SQL 中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于 SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了 TO_CHAR,UPPER,SOUNDEX 等单行函数。SELECT ena
2、me,TO_CHAR(hiredate,day,DD-Mon-YYYY)FROM empWhere UPPER(ename) Like AL%ORDER BY SOUNDEX(ename)单行函数也可以在其他语句中使用，如 update 的 SET 子句，INSERT 的 VALUES 子句，DELET 的 WHERE 子句,认证考试特别注意在 SELECT 语句中使用这些函数，所以我们的注意力也集中在 SELECT 语句中。NULL 和单行函数在如何理解 NULL 上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。 NULL 值表示一个未知数据或者一个空值，算术操作符的任何一个操作数
3、为 NULL 值，结果均为提个 NULL 值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE 在调用了 NULL 参数时能够返回非 NULL 值。在这些中 NVL 函数时最重要的，因为他能直接处理 NULL 值，NVL 有两个参数:NVL(x1,x2),x1和 x2都式表达式，当 x1为 null 时返回 X2,否则返回x1。下面我们看看 emp 数据表它包含了薪水、奖金两项，需要计算总的补偿column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype
4、number number numberlength 11.2 11.2不是简单的将薪水和奖金加起来就可以了，如果某一行是 null 值那么结果就将是 null，比如下面的例子:update empset salary=(salary+bonus)*1.1这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即 salary + null,那么就会得出错误的结论，这个时候就要使用 nvl 函数来排除 null 值的影响。所以正确的语句是:update empset salary=(salary+nvl(bonus,0)*1.1单行字符串函数单行字符串函数用于操作字符串数据，他们大
5、多数有一个或多个参数，其中绝大多数返回字符串ASCII()c1是一字符串，返回 c1第一个字母的 ASCII 码，他的逆函数是 CHR()SELECT ASCII(A) BIG_A,ASCII(z) BIG_z FROM empBIG_A BIG_z65 122CHR()NCHAR_CSi 是一个数字，函数返回十进制表示的字符select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z BCONCAT(,)c1,c2均为字符串，函数将 c2连接到 c1的后面，如果 c1为 null,将返回 c2.如果 c2为 null,则返回
6、c1，如果 c1、c2都为null，则返回 null。他和操作符|返回的结果相同select concat(slobo ,Svoboda) username from dualusernameslobo SyobodaINITCAP()c1为一字符串。函数将每个单词的第一个字母大写其它字母小写返回。单词由空格，控制字符，标点符号限制。select INITCAP(veni,vedi,vici) Ceasar from dualCeasarVeni,Vedi,ViciINSTR(,)c1,c2均为字符串，i,j 为整数。函数返回 c2在 c1中第 j 次出现的位置，搜索从 c1的第 i 个字符开
7、始。当没有发现需要的字符时返回0,如果 i 为负数，那么搜索将从右到左进行，但是位置的计算还是从左到右，i 和 j 的缺省值为1.select INSTR(Mississippi,i,3,3) from dualINSTR(MISSISSIPPI,I,3,3)11select INSTR(Mississippi,i,-2,3) from dualINSTR(MISSISSIPPI,I,3,3)2INSTRB(,i,j)与 INSTR()函数一样，只是他返回的是字节，对于单字节 INSTRB()等于 INSTR()LENGTH()c1为字符串，返回 c1的长度，如果 c1为 null，那么将返回
8、 null 值。select LENGTH(Ipso Facto) ergo from dualergo10LENGTHb()与 LENGTH()一样，返回字节。lower()返回 c 的小写字符，经常出现在 where 子串中select LOWER(colorname) from itemdetail WHERE LOWER(colorname) LIKE %white%COLORNAMEWinterwhiteLPAD(,)c1,c2均为字符串，i 为整数。在 c1的左侧用 c2字符串补足致长度 i,可多次重复，如果 i 小于 c1的长度，那么只返回i 那么长的 c1字符，其他的将被截去。
9、c2的缺省值为单空格，参见 RPAD。select LPAD(answer,7,) padded,answer unpadded from question;PADDED UNPADDED Yes YesNO NOMaybe maybeLTRIM(,)把 c1中最左边的字符去掉，使其第一个字符不在 c2中，如果没有 c2，那么 c1就不会改变。select LTRIM(Mississippi,Mis) from dualLTRppiRPAD(,)在 c1的右侧用 c2字符串补足致长度 i,可多次重复，如果 i 小于 c1的长度，那么只返回 i 那么长的 c1字符，其他的将被截去。c2的缺省值为
10、单空格,其他与 LPAD 相似RTRIM(,)把 c1中最右边的字符去掉，使其第后一个字符不在 c2中，如果没有 c2，那么 c1就不会改变。REPLACE(,)c1,c2,c3都是字符串，函数用 c3代替出现在 c1中的 c2后返回。select REPLACE(uptown,up,down) from dualREPLACEdowntownSTBSTR(,)c1为一字符串，i,j 为整数，从 c1的第 i 位开始返回长度为 j 的子字符串，如果 j 为空，则直到串的尾部。select SUBSTR(Message,1,4) from dualSUBSMessSUBSTRB(,)与 SUBS
11、TR 大致相同，只是 I,J 是以字节计算。SOUNDEX()返回与 c1发音相似的词select SOUNDEX(dawes) Dawes SOUNDEX(daws) Daws, SOUNDEX(dawson) from dualDawes Daws DawsonD200 D200 D250TRANSLATE(,)将 c1中与 c2相同的字符以 c3代替select TRANSLATE(fumble,uf,ar) test from dualTEXTrambleTRIM( from c3)将 c3串中的第一个，最后一个，或者都删除。select TRIM( space padded ) tr
12、im from dual TRIMspace paddedUPPER()返回 c1的大写，常出现 where 子串中select name from dual where UPPER(name) LIKE KI%NAMEKING单行数字函数单行数字函数操作数字数据，执行数学和算术运算。所有函数都有数字参数并返回数字值。所有三角函数的操作数和值都是弧度而不是角度，oracle 没有提供内建的弧度和角度的转换函数。ABS()返回 n 的绝对值ACOS()反余玄函数，返回-1到1 之间的数。n 表示弧度select ACOS(-1) pi,ACOS(1) ZERO FROM dualPI ZERO3
13、.14159265 0ASIN()反正玄函数，返回-1到1 ，n 表示弧度ATAN()反正切函数，返回 n 的反正切值，n 表示弧度。CEIL()返回大于或等于 n 的最小整数。COS()返回 n 的余玄值，n 为弧度COSH()返回 n 的双曲余玄值，n 为数字。select COSH() FROM dualCOSH(1.4)2.15089847EXP()返回 e 的 n 次幂，e=2.71828183.FLOOR()返回小于等于 N 的最大整数。LN()返回 N 的自然对数，N 必须大于0LOG(,)返回以 n1为底 n2的对数MOD()返回 n1除以 n2的余数，POWER(,)返回 n
14、1的 n2次方ROUND(,)291911320返回舍入小数点右边 n2位的 n1的值，n2的缺省值为0，这回将小数点最接近的整数，如果 n2为负数就舍入到小数点左边相应的位上，n2必须是整数。select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54SIGN()如果 n 为负数，返回-1,如果 n 为正数，返回1，如果 n=0返回0.SIN()返回 n 的正玄值,n 为弧度。SINH()返回 n 的双曲正玄值,n 为弧度。SQRT()返回 n 的
15、平方根,n 为弧度TAN()返回 n 的正切值,n 为弧度TANH()返回 n 的双曲正切值,n 为弧度TRUNC(,)返回截尾到 n2位小数的 n1的值，n2缺省设置为0，当 n2为缺省设置时会将 n1截尾为整数，如果 n2为负值，就截尾在小数点左边相应的位上。单行日期函数单行日期函数操作 DATA 数据类型，绝大多数都有 DATA 数据类型的参数，绝大多数返回的也是 DATA 数据类型的值。ADD_MONTHS(,)返回日期 d 加上 i 个月后的结果。 i 可以使任意整数。如果 i 是一个小数，那么数据库将隐式的他转换成整数，将会截去小数点后面的部分。LAST_DAY()函数返回包含日期
16、 d 的月份的最后一天MONTHS_BETWEEN(,)返回 d1和 d2之间月的数目,如果 d1和 d2的日的日期都相同，或者都使该月的最后一天，那么将返回一个整数，否则会返回的结果将包含一个分数。NEW_TIME(,)d1是一个日期数据类型，当时区 tz1中的日期和时间是 d 时，返回时区 tz2中的日期和时间。tz1和 tz2时字符串。NEXT_DAY(,)返回日期 d 后由 dow 给出的条件的第一天， dow 使用当前会话中给出的语言指定了一周中的某一天，返回的时间分量与 d 的时间分量相同。select NEXT_DAY(01-Jan-2000,Monday) 1st Monday,NEXT_DAY(01-Nov-2004,Tuesday)+7 2nd Tuesday) from
