Oracle函数大全

附录Ⅱ

Oracle11g SQL函数

函数名 返回

类型

说明

字符串函数

ASCII(s) 数值 返回s首位字母的ASCII码

CHR(i) 字符 返回数值i的ASCII字符

CONCAT(s1,s2) 字符 将s2连接到字符串s1的后面

INITCAP(s) 字符 将每个单词首位字母大写其它字母小写 INSTR(s1,s2[,i[,j]]) 数值 返回s2在s1中第i位开始第j次出现的位置 INSTRB(s1,s2[,i[,j]]) 数值 与INSTR(s)函数相同，但按字节计算 LENGTH(s) 数值 返回s的长度。

LENGTHb(s) 数值 与LENGTH(s)相同，但按字节计算。

lower(s) 字符 返回s的小写字符

LPAD(s1,i[,s2]) 字符 在s1的左侧用s2字符串补足到总长度i LTRIM(s1,s2) 字符 循环去掉在s2中存在的s1左边字符

RPAD(s1,i[,s2]) 字符 在s1的右侧用s2字符串补足到总长度i RTRIM(s1,s2) 字符 循环去掉在s2中存在的s1右边字符 REPLACE(s1,s2[,s3]) 字符 用s3替换出现在s1中的s2

REVERSE(s) 字符 返回s倒排的字符串

SUBSTR(s,i[,j]) 字符 从s的第i位开始截得长度j的子字符串 SUBSTRB(s,i[,j]) 字符 与SUBSTR相同，但i,j按字节计算。 SOUNDEX(s) 返回与s发音相似的词

TRANSLATE(s1,s2,s3) 字符 将s1中与s2相同的字符以s3代替

TRIM(s) 字符 删除s的首部和尾部空格

UPPER(s) 字符 返回s的大写

正则表达式函数

REGEXP_LIKE() 布尔 功能与LIKE的功能相似

REGEXP_INSTR() 数值 功能与INSTR的功能相似

REGEXP_SUBSTR() 字符 功能与SUBSTR的功能相似

REGEXP_REPLACE() 字符 功能与REPLACE的功能相似

数字函数

ABS(i) 数值 返回i的绝对值

ACOS(i) 数值 反余玄函数，返回-1到1之间的数

ASIN(i) 数值 反正玄函数，返回-1到1之间的数

ATAN(i) 数值 反正切函数，返回i的反正切值

CEIL(i) 数值 返回大于或等于n的最小整数

COS(i) 数值 返回n的余玄值

COSH(i) 数值 返回n的双曲余玄值

EXP(i) 数值 返回e的i次幂，e=2.71828183

FLOOR(i) 数值 返回小于等于i的最大整数

LN(i) 数值 返回i的自然对数，i>0

LOG(i,j) 数值 返回以i为底j的对数

MOD(i) 数值 返回i除以j的余数

POWER(i,j) 数值 返回i的j次方

ROUND(i,j) 数值 返回i四舍五入值，j是小数点位数

SIGN(i) 数值 i>0返回1,i=0返回0，i<0返回-1

SIN(i) 数值 返回i的正玄值

SINH(i) 数值 返回i的双曲正玄值

SQRT(i) 数值 返回i的平方根

TAN(i) 数值 返回i的正切值

TANH(i) 数值 返回i的双曲正切值

TRUNC(I,j) 数值 返回i的结尾值，j可正、零、负数

转换函数

CONVERT(s,ds,ss) 字符 将s，由ss字符集转换为ds字符集

HEXTORAW(s) 字符 将16进制的s转换为RAW数据类型。 RAWTOHEX(s) 字符 将RAW类型s转换为16进制的数据类型。 ROWIDTOCHAR(s) 字符 将ROWID类型s转换为CHAR数据类型。

TO_CHAR(p,fmt) 字符 将p转换成fmt指定格式的char类型，若p为日期 TO_DATE(s,fmt) 日期 将字符串s转换成date数据类型

TO_MULTI_BYTE(s) 字符 将s的单字节字符转换成双字节字符

TO_NUMBER(s) 数值 将返回s代表的数值。

TO_SINGLE_BYTE() 字符 将s中的多字节字符转化成单字节字符

日期函数

ADD_MONTHS(d,i) 日期 返回日期d加上i个月后的结果

LAST_DAY(d) 日期 返回日期d月份的最后一天

MONTHS_BETWEEN(d1,d2) 数值 返回d1和d2之间月的数目

NEW_TIME(d,tz1,tz2) 日期 将日期d由时区tz1转换到时区tz2 NEW_TIME(sysdate,'GMT','CST')

NEXT_DAY(d,w) 日期 返回d后w给出的第一星期w，w=1--7(周日--周六) ROUND(d,fmt) 日期 fmt=YYYY|MM|DD|D，返回舍入d后fmt格式的第一天 TRUNC(d,fmt) 日期 fmt=YYYY|MM|DD|D，返回截去d后fmt格式的第一天 SYADATE 日期 无参数，返回当前日期和时间。

其它函数

NVL(s1|p1,s2|p2) 不定 如果s1或p1是空值，返回s2或p2

BFILENAME(dir,file) 指针 初始化BFILE变量或BFILE列，返回空BFILE位置指针 DECODE(p,p1,p2,...) 不定 if p=p1 then p2;elsif p=p3 then p4…else pn

DUMP(s,fmt,I,j) 字符 返回s的类型编号，s从i截取j个字符fmt进制的ASCII DUMP('A1cbd',1010,1,2)

EMPTY_BLOB() 指针 初始化BLOB变量或BLOB列，返回空的BLOB位置指针 EMPTY_CLOB() 指针 初始化CLOB变量或CLOB列，返回空的CLOB位置指针 GREATEST(p,p1,p2,...) 不定 返回其中最大的表达式

LEAST(p,p1,p2,...) 不定 返回其中最小的表达式

UID 数值 返回唯一标示当前数据库用户的编号。

USER 字符 返回当前用户的用户名

USERENV(OPTION) 字符 返回当前会话信息，OPTION取值参见最后一页。 SYS_CONTEXT(s1,s2) 字符 返回当前会话信息，s1='USERENV'，s2=OPTION。 VSIZE(s) 数值 返回s的字节数

组函数

AVG(col) 数值 返回数值列col的平均值

COUNT(col|*) 数值 返回列col的行数目，*表示返回所有的行

MAX(col) 不定 返回数值列col的最大值

MIN(col) 不定 返回数值列col的最小值

STDDEV(col) 数值 返回数值列col的标准差，标准差是方差的平方根 SUM(col) 数值 返回数值列col的总和

VARIANCE(col) 数值 返回数值列col的统计方差

WM_CONCAT(col) 字符 返回列col值的合并行，用逗号分隔。

OVER分组排序函数

OVER([分组]排序) 按字段分组、排序,与

下面函数联合使用

OVER(PARTITION BY列

ORDER BY 列) 或 OVER(ORDER BY 列)

RANK()OVER() 数值 增加序号伪列：1、2、2、4、…

DENSE_RANK()OVER() 数值 增加序号伪列：1、2、2、3、… ROW_NUMBER()OVER() 数值 增加序号伪列：1、2、3、4、… SUM(列)OVER() 数值 求和、分组求和、求累计

LAG(exp,n,defval) 不定 读取某列的上第n行

LEAD(exp,n,defval) 不定 读取某列的下第n行 LAG、LEAD：

exp列名、第n行、无返回值时取代值

s、s1、s2、s3为串、串表达式，p、p1、p2数值、数值表达式，

i、j为整数,fmt为数据格式，d、d1、d2为日期

SYS_CONTEXT('USERENV',Option) 返回当前会话信息SYS_CONTEXT('USERENV','TERMINAL') terminal

SYS_CONTEXT('USERENV','LANGUAGE') language

SYS_CONTEXT('USERENV','SESSIONID') sessionid