oracle中函数主要有两种类型:
- 单行函数(single row function):对一行进行操作,返回一个值,如对某一行进行大小写的转换
- 聚合函数(aggregate function):对多行进行操作,返回一个值,如求和函数。(sql中需要having子句的原因是因为where子句中不能使用聚合函数)
单行函数主要有5种:
- 字符函数:对由字符组成的字符串进行操作,结果返回字符串
- 数字函数:对数字进行操作,结果返回数字
- 转化函数:将一种数据类型转换成另外一种数据类型
- 正则表达式函数:在查询数据时可以使用正则表达式
- 日期函数:对日期进行处理
- 字符函数
- ASCII(x)和CHR(x):ASCII(x)是获得字符x的ASCII码,CHR(x)是获得ASCII码为x的字符
- CONCAT(x,y):将y附加在x后面,该函数会返回得到的字符串(与||操作符功能相同)
- INITCAP(x):将x中的每个单词的首字母转换成大写
- INSTR(x,find_string [,start] [,occurence]):在x中查找find_string,INSTR()函数返回find_string所在的位置,该函数可以使用可选参数start来说明从x中的那个位置开始查找,还可以使用可选参数occurence说明应该返回find_string第几次出现的位置(注意:数据库中字符串中字符的编号是从1开始)
- LENGTH(x):获取x中的字符个数
- LOWER(x)和UPPER(x):LOWER(x)用于将x中的字母转换成小写,UPPER(x)用于将x中的字母转换成大写
- LPAD(x,width [,pad_string]):用于在x的左边补齐空格,使x的总长度达到width个字符,如果在pad_string参数中指定了一个字符串,那么就用这个字符串来反复填充x左边的空位,以补齐x的长度。同理,RPAD(x,width [,pad_string])函数用于在x的右边补齐空字符串
- LTRIM(x [,trim_string]),RTRIM(x [,trim_string]),TRIM():LTRIM(x [,trim_string])函数用于从x的左边截去一些字符,该函数还可以使用可选的参数trim_stirng来指定要截去的字符;如果没有指定trim_string参数,默认情况下会截去空格。同理,RTRIM函数用于从右边截去一些字符,TRIM用于从x的左边和右边各截去一些字符
- NVL(x,value):NVL函数用于将空值转换成一个已知的值。NVL(x,value)的结果如下:如果x为空,则返回value,否则返回x。
- NVL2(x,value1,value2):函数的返回结果如下:如果x非空,则返回value1;否则返回value2.
- REPLACE(x,search_string,replace_string):用于在x中查找search_string,并将其替换为replace_string(REPLACE()函数并不会真正对数据库中的数据进行修改,而是只对结果集中返回的行进行修改)
- SOUNDEX(x):用于获取包含x发音的一个字符串,该函数用于对英文拼写不同但发音相似的单词进行比较
- SUBSTR(x,start [,length]):用于从x中获取从start位置开始的一个子字符串,还可以使用可选参数length指定子字符串的长度
- 数字函数
- ABS(x):返回x的绝对值
- BITAND(x,y):返回对x和y进行位与操作的结果
- CEIL(x):返回大于或等于x的最小整数
- FLOOR(x):返回小于或等于x的最大整数
- MOD(x,y):返回x除以y的对数
- POWER(x,y):返回x的y次幂
- ROUND(x [,y]):返回对x进行取整的结果;y为可选参数,说明对第几位小数取整。如果没有指定y,则对x在第o位小数处进行取整;如果y是负数,则对x在小数点的左边的第|y|处进行取整
- SQRT(x):返回x的平方根
- TRUNC(x [,y]):返回对x进行截断的结果;y为可选参数,说明对第几位小数进行截取。如果没有指定y,则对x在0位小数进行截取;如果y是负数,则对x在小数点左边的第|y|位处进行截取。(注意:只有大于5才进位)
-
正则表达式函数
- \ :说明要匹配的字符是一个特殊字符,常量或者引用 例如:\n 匹配换行符
- ^ :匹配字符串的开头位置 例如:如果A是字符串的第一个字符,^A匹配A
- $ :匹配字符串的末尾位置 例如:如果B是字符串的最后一个字符,$B匹配B
- * :匹配前面的字符0次或多次 例如:ba*rk可以匹配brk,bark,baark等等
- + :匹配前面的字符1次或多次 例如: ba+rk可以匹配bark,baark等等
- ? :匹配前面的字符0次或1次 例如: ba?rk可以匹配brk和bark
- {n}:匹配一个字符恰好n次,其中n是一个整数 例如:hob{2}it可以匹配hobbit
- {n,m}:匹配一个字符最少n次,最多m次,其中n和m都是整数例如:hob{2,3}it可以匹配hobbit和hobbbit
- . :匹配除null之外的任意单个字符 例如:hob.it可以匹配hobait,hobbit等等
- (pattern) :匹配指定模式的一个子表达式。可以使用子表达式构成复杂的正则表达式。在这种子表达式中,可以访问单词的匹配,称为匹配命中(captuer) 例如: anatom(y|ies)可以匹配anatomy和anatomies
- x|y :匹配x或y,其中x和y是一个或多个字符 例如:war|peace 可以匹配war或peace
- [abc] :匹配中括号内的任意一个字符 例如:[ab]bc可以匹配abc和bbc
- [a-z] :匹配指定范围的任意一个字符 例如:[a-c]bc可以匹配abc,bbc和cbc
- [::] :指定一个字符类,可以匹配该类中的任何字符 例如:[:alphanum:] 可以匹配字符0-9,A-Z,a-z,[:alpha:]可以匹配字符A-Z和a-z,[:blank:]可以匹配空格或tab键,[:digit:]可以匹配数字0-9,[:graph:]可以匹配非空字符,[:lower:]可以匹配小写a-z,[:print:]与[:graph:]类似,不同之处在于包括空格字符,[:punct:]可以匹配标点符号.,"",[:space:]可以匹配所有的空字符,[:upper:]可以匹配所有的大学字母A-Z,[:xdigit:]可以匹配十六进制数字0-9,A-F和a-f
- [..] :匹配一个组合元素,例如多字符元素
- [==] :指定等价类
- \n:这是对前一次匹配命中的一个后应用,其中n是一个正整数 例如(.)\1可以匹配连个连续相同的字符
- REGEXP_LIKE(x,pattern [,match_option])
- 'c',说明在进行匹配时区分大小写(默认选项)
- 'i',说明在进行匹配时不区分大小写
- 'n',允许使用可以匹配的任意字符的操作符
- 'm',将x作为一个包含多行的字符串
- REGEXP_INSTR(x,pattern [,start [,occurrence [,return_ooption [,match_option]]]]) 在x中查找pattern,并返回pattern所在位置,可以指定以下可选参数:
- start 开始的位置
- occurence 说明应该返回第几次出现pattern的位置
- return_option 说明应该返回什么整数,若该参数为0,则说明要返回的是x中的一个字符的位置;若该参数为非0的整数,则说明要返回的整数为x中出现在pattern之后的字符的位置
- match_option ,修改默认的匹配设置
- REGEXP_REPLACE(x,pattern [,replace_string [,start [,occurence [,match_option]]]]) 在x中查找pattern,并将其替换为replace_string.其他选项的意思与REGEXP_INSTR()函数的参数完全相同
- REGEXP_SUBSTR(x.pattern [,start [,occurence [,match_option]]]) 返回x中可以匹配pattern中的一个子字符串,其开始位置由start指定,其他选项的意思与REGEXP_INSTR()函数的参数完全相同