本章将介绍Oracle数据库的内置函数。函数可以接受零个或多个参数,并返回一个输出参数。在Oracle数据库中可以使用两种主要类型的参数:
单行函数(single row function)单行函数同时只能对一行进行操作,且对输入的每一行返回一行输出结果。单行函数的示例是CONCAT(x,y),其功能是将y添加到x上,并返回所得到的字符串
聚合函数(aggregate function)聚合函数同时可以对多行进行操作,并返回一行输出结果。聚合函数的一个例子是AVG(x),其功能是返回x的平均值,其中x可以是一列,或者说一个表达式
使用单行函数。单行函数主要有5种:
字符函数。可以对由字符组成的字符串进行操作
数字函数。可以对数字进行计算
转换函数。可以将一种数据库类型转换成另一中数据库类型
日期函数。可以对日期和时间进行处理
正则表达式函数。在查询数据时可以使用正则表达式
1.1 字符函数
字 符 函 数 列 表
函数
说明
ASCII(x)
返回字符x的ASCII码
CHR(x)
返回ASCII码为x的字符
CONCAT(x,y)
将y添加到x上,并将得到的字符串作为结果返回
INITCAP(x)
将x中每个单词的首字母都转换成大写,并返回处理结果
INSET(x,find_string[,start][,occurentce])
在x中查找字符串find_string,然后返回find_string所在的位置。可以提供一个可选的start位置来指定该函数从这个位置开始查找。同样,也可以指定一个可选的occurrence参数来说明应该返回find_string第几次出现的位置
LENGTH(x)
返回x字符串的长度
LOWER(x)
1.2 数字函数
数字函数可以用于计算。数字函数可以接受输入的数字参数,这些数字可以来自一个表的数字列,也可以是一个结果为数字的表达式。数字函数会对这些输入的数字进行计算返回一个数字结果。数字函数的一个例子是SQRT(x),该函数返回x的平方根
函数
说明
例子
ABS(x)
返回x的绝对值
ABS(10) = 10
ABS(-10) = 10
ACOS(x)
返回x的反余弦
ACOS(1) = 0
ACOS(-1) = 3.1415926
ASIN(x)
返回x的反正弦
这些有兴趣的朋友可以自行验证。
ATAN(x)
返回x的反正切
ATAN2(x)
返回x和y的反正切
BITAND(x,y)
返回对x和y进行位与(AND )操作的结果
COS(x)
返回x的余弦
COSH(x)
返回x的双曲线余弦函数
CEIL(X)
返回大于或等于x的最小整数
EXP(X)
返回e的x次幂,x约等于2.7.18
FLOOR(X)
返回小于或等于x的最大整数
LOG(X,Y)
返回以x为底y的对数
LN(x)
返回x的自然对数
MOD(x,y)
返回x除以y的余数
POWER(x,y)
返回x的y次幂
ROUND(x[,y])
返回对x取整的结果
SIGN(x)
如果x是负数则返回-1,如果是整数,返回1,如果是0,返回0
SIN(X)
返回x的正弦函数
SINH(x)
返回x的双曲线正弦函数
SQRT(x)
返回x的平方根
TAN(x)
返回x的正切函数
1.3 转换函数
有时可能需要将一个值从一种数据类型转换为另一种数据类型。
ASCIISTR(x)
将x转换为一个ASCII字符串
BIN_TO_NUM(x)
将二进制数字x转换为NUMBER类型
DECODE(x,search,result,default)
将x与search中的值进行比较,如果相等,返回result的值,否则返回default
TO_BINARY_DOUBLE(x)
将x转换为一个BINARY_DOUBLE类型
TO_BINARY_FLOAT(x)
将x转换为一个BINARY_FLOAT类型
TO_BLOB(x)
将x转换为一个二进制大对象(BLOB)类型。
TO_CHAR(x[,format])
将x转换为一个VARCHAR2字符串。可以指定一个可选参数format来说明x的格式
TO_CLOB(x)
将x转换为一个字符大对象(CLOB)类型。
TO_DATE(x[,format])
将x转换为一个DATE类型。
TO_TIMESTAMP(x)
将字符串x转换为一个TIMESTAMP类型
1.4 正则表达式函数
正则表达式中包含许多元字符。^可以匹配一个字符串的开头:[5-8]可以匹配介于5~8之间的数字;$可以匹配一个字符串的结尾。
orcle中常用的元字符
元字符
说明
例子
\
说明要匹配的字符是一个特殊字符、常量
\n匹配换行符
\\匹配\
\(匹配(
\)匹配)
^
匹配字符串的开头位置
$
匹配字符串的末尾位置
*
匹配前面的字符0次或多次
ba*rk可以匹配brk,bark,baark
+
匹配前面字符串1此或多次
ba+rk可以匹配bark、baark等
?
匹配前面的字符0次或1次
{n}
匹配一个字符恰好是n次,
hob{2}it可以匹配hobbit
{n,m}
匹配一个字符至少n次,最多m次
.
匹配出null之外的任意单个字符
(pattern)
匹配一个指定的子表达式
x|y
匹配x或者y,
{abc}
匹配abc中的任意一个字符
[a-z]
匹配指定范围内的任意一个字符
[::]
指定一个字符类,可以匹配该类中的任何字符
[:alphanum:]可以匹配字符0-9、A-Z和a-z
[:alpha:]可以匹配字符A-Z和a-z
[:blank:]可以匹配空格和tab键
[:digit:]可以匹配数字0-9
[:graph:]可以匹配非空字符
[:lower:]可以匹配小写字母
[==]
指定等价类
\n
这是对前一次捕获的一个反向引用,其中n是一个正整数
\d
数字字符
\D
非数字字符
\w
字母字符
\W
非字母字符
\s
空白字符
\S
非空白字符
\A
只匹配字符串的开头位置
\Z
只匹配字符串的末尾位置
*?
匹配前面的模式元素0次或多次
+?
匹配前面的模式元素1次或多次
??
匹配前面的模式元素0次或
正则表达式函数
函数
说明
REGEXP_LIKE(x,pattern[,match_option])
从x中搜索pattern参数中定义的正则表达式。可以使用match_option修改默认匹配选项
REGEXP_INSRT(x,pattern[,start[,occurence[,rerturn_option[,match_option]]]])
在x中查找pattern,并返回pattern所在的位置。可以指定一下可选参数:
start:开始查找的位置。默认是1,指x的第一个字符
occurrence 说明应该返回当前第几次出现pattern的位置
return_option 说明应该返回什么整数
match_option 修改默认的匹配设置
REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[,match_option]]])
在x中查找pattern,并将其替换为replace_string。其他选项请参照上一个方法
二、使用聚合函数
函数
说明
AVG(x)
返回x的平均值
COUNT(x)
返回一个包含x的查询的行数
MAX(x)
返回x的最大值
MEDIA(x)
返回x的中间值
MIN(x)
返回x的最小值
STDDEV(x)
返回x的标准差
SUM(x)
返回x的和
VARIANCE(x)
返回x的方差
三、对行进行分组GROUP BY,使用HAVING子句过滤行组。Having放在group不要之后.group by可以不与having一起使用,但是having必须与group by一起使用。