使用简单函数

1、函数：接受零个或多个输入参数，并返回一个输出参数。 在oracle数据库中可以使用两种主要类型的函数： 1、单行函数（Single row Function） 同时只能对一行进行操作，对输入的每一行都返回一个结果。 2、聚合函数（aggregate function） 聚合函数同时可以对多行进行操作，并返回一行输出结果。 单行函数 每次只对一行进行操作并且要为每行都获得一行输出结果时，可以使用单 行函数，单行函数主要有5种。 1、字符函数 2、数字函数 3、转换函数 4、日期函数 5、正则表达式函数 字符函数 ASCII(x) 返回字符x的ASCII，如果x是一个字符串，那么返回第一个字符的A

2、SCII CHR(x) 返回ASCII码为x的字符 CONCAT(x,y) 将y附加在x上，并将所得的字符串作为结果返回 INITCAP(x) 将x中的每个单词的首字母都转换为大写，并返回所得的字符串 INSTR(x,find_string,start,occurrence) 在x中查找字符串find_string，然后返回find_string所在的位置，可以提供一 个可选的start位置来指定该函数从这个位置开始查找。同样，也可以指定 一个可选的occurrence参数，来说明应该返回find_string第几次出现的位置。 LENGTH(x) 返回x中字符的个数 LOWER(x) 将x中

3、的字母转换为小写，并返回所得到的字符串 LPAD(x,width,pad_string) 在x的左边补齐空格，得到总长为width个字符的字符串。该函数可以提供 一个可选的pad_string，这个参数用于指定在x左边补齐的字符串 LTRIM(x,trim_string) 从x的左边截取一些字符，可以指定一个可选的参数trim_string来指定要截 取的字符，如果不指定trim_string参数，则默认截取空格 NANVL(x,value) Oracle 10g新增的一个函数，如果x匹配NaN这个特殊值（非数字），就返 回value，否则返回x NVL(x,value) 如果x为空，就返回v

4、alue，否则返回x NVL2(x,value1,value2) 如果x非空，就返回value1，否则返回vlaue2 REPLACE(x,search_string,replace_string) 在x中查找search_string，并将其替换为replace_string RPAD(x,width,pad_string) 与LPAD()类似，不过要对x的右边进行补齐 RTRIM(x,trim_string) 与LTRIM类似，不过要对x的右边进行截取 SOUNDEX(x) 返回一个包含x的发音的字符串，该函数用于对英文发音不同但却比较接近的单 词进行比较 SUBSTR(x,start,l

5、ength) 返回x中的一个子字符串，这个子字符串从start处开始，还可以为这个子字符串 指定一个可选的length参数 TRIM(trim_char FROMx) 从x的左边、右边同时截取一些字母，可以指定一个可选的参数trim_string来指 定要截取的字符；如果不指定trim_string参数，则默认截取空格。 UPPER(x) 将x中的字符转换为大写，并返回所得到的字符串 数字函数 ABS(x) 返回x的绝对值 ACOS(x) 返回x的反余弦 ASIN(x) 返回x的反正弦 ATAN(x) 返回x的反正切 ATAN2(x,y) 返回x和y的反正切 BITAND(x,y) 返回对x和

6、y进行位与操作的结果 bitand(0,0)=0bitand(0,1)=0 bitand(1,1)=1 COS(x) 返回x的余弦，其中x是弧度 COSH(x) 返回x的双曲余弦函数 CEIL(x) 返回大于或等于x的最小整数 EXP(x) 返回e的x次幂，其中e约等于2.71828183 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进行取整的结果；y为可选参数，说明对第几位小数进行取整。如 果没有指定y，则对x在0小

7、数小数处进行取整；如果y是负数，则对x在小 数点的左边的第|y|位处进行取整 SIGN(x) 如果x是负数，则返回-1；如果x是正数，则返回1；如果x为0，则返回0 SIN(x) 返回x的正弦函数 SINH(x) 返回x的双曲正弦函数 SQRT(x) 返回x的平方根 TAN(x) 返回x的正切函数 TANH(x) 返回x的双曲正切函数 TRUNC(x,y) 返回对x的进行截取的结果，y为可选参数，说明对第几位小数进行截断。如果 没有指定y，则对x在0位小数处进行截断，如果y是负数，则对x在小数点左边的 第|y|位处进行截断 转换函数 将一个值从一种数据类型转换为另一种数据类型。 ASCIIST

8、R(x) 将x转换为一个ASCII字符串，其中x可以是由字符集中的任意字符组成的 字符串。 BIN_TO_NUM(x) 将x转换为一个二进制数字，返回值为number类型 CAST(x AS type_name) 将x中的值从一种数据类型转换为type_name所指定的数据类型 CHARTOROWID(x) 将x装换为ROWID类型 COMPOSE(x) 将x转换为一个Unicode编码的字符串。 CONVERT(x,source_char_set,dest_char_set) 将x从source_char_set转换为dest_char_set DECODE(x,search,result,

9、default) 将x与search中的值进行比较，如果相等，decode()就返回result，否则返回 default DECOMPOSE(x) 先对x进行分解，再将其转换为一个Unicode字符串，字符串使用与x完全相等的 字符集 HEXTORAW(x) 将包含十六进制数字的字符x转换为一个二进制数字raw，返回raw类型的数字 NUMTODSINTERVAL(x) 将数字x转换为一个INTERVAL DAY TO SECOND类型 NUMTOYMINTERVAL(x) 将数字x转换为一个INTERVAL YEAR TO MONTH类型 RAWTOHEX(x) 将二进制数字(RAW)x转

10、换为VARCHAR2类型的字符，值为等价的十六进制数字 RAWTONHEX(x) 将二进制数字(RAW)x转换为一个NVARCHAR2类型的字符，值为等价的十六进制 数字 ROWIDTOCHAR(x) 将ROWID x转换为一个VARCHAR2字符 ROWIDTONCHAR(x) 将ROWID x转换为一个NVARCHAR2字符 TO_BINARY_DOUBLE(x) 将x转换为一个BINARY_DOUBLE类型 TO_BINARY_FLOAT(x) 将x转换为一个BINARY_FLOAT类型 TO_CHAR(x,format) 将x转换为VARCHAR2字符串，可以指定一个可选的参数form

11、at来说明x的格式 TO_CLOB(x) 将x转换为一个字符大对象CLOB TO_DATE(x,format) 将x转换为一个DATE类型 TO_DSINTERVAL(x) 将x转换为一个 INTERVAL DAY TO SECOND TO_MULTI_BYTE(x) 将x中的单字节字符转换为对应的多字节字符，返回类型与x类型一致 TO_NCHAR(x) 将数据库字符集中的x转换为一个NCHAR类型 TO_NCLOB 将x转换为一个NCLOB类型 TO_NUMBER(x,format) 将x转换为一个number类型 TO_SINGLE_BYTE(x) 将x中的多字节字符转换为对应的单字节字符

12、，返回类型与x的类型相同 TO_TIMESTAMP(x) 将字符串x转换为一个TIMESTAMP类型 TO_TIMESTAMP_TZ(x) 将x字符串转换为一个TIMESTAMP WITH TIME ZONE TO_YMINTERVAL(x) 将字符串x转换为一个INTERVAL YEAR TO MONTH类型 TRANSLATE(x,from_string,to_string) 将x中所有的from_string转换为to_string UNISTR(x) 将x中的字符转换为国家语言字符集(NCHAR) 123456.99123456.99 数字日期都可以通过这个函数转换为字符串类型。 to

13、_char(sysdate, yyyy/mm/dd); would return 2003/07/09 to_char(sysdate, Month DD, YYYY); would return July 09, 2003 to_char(sysdate, FMMonth DD, YYYY); would return July 9, 2003 to_char(sysdate, MON DDth, YYYY); would return JUL 09TH, 2003 to_char(sysdate, FMMON DDth, YYYY); would return JUL 9TH, 2003

14、to_char(sysdate, FMMon ddth, YYYY); would return Jul 9th, 2003 返回的就是一个字符串类型。 正则表达式函数 正则表达式扩充了我们字符搜索的功能。 正则表达式就是对字符串进行匹配的一种模式。 说明要匹配的字符是一个特殊字符 n 匹配换行符 匹配 (匹配( )匹配) 匹配字符串的开头位置 A匹配以A开头的字符串 $匹配字符串的结束位置 $B匹配B是字符串的最后一个字符 *匹配前面的字符0或者多次 ba*rk匹配brk,bark,baark,baaark +匹配前面的字符1或者多次 ba*rk匹配bark,baark,baaark,但是不

15、能brk ?匹配前面的字符0次或者1次 ba?rk只能匹配bark或者brk n匹配前面的字符恰好n次，其中n是一个整数 ba2rk匹配baark n,m匹配前面的字符至少n次，最多m次 ba2,3rk匹配baark或者baaark (pattern) 匹配指定模式的一个子表达式，可以使用子表达式构成一个复杂的 正则表达式 anatom(y|ies)可以匹配anatomy和anatomies x|y匹配x或者y war|pease可以匹配war或者pease abc匹配中括号内的任意一个字符 abbc可以匹配abc或者bbc a-z匹配指定范围的任意一个字符 a-cbc可以匹配abc、bbc、

16、cbc :指定一个字符类，可以匹配该类中的任何字符 :alphanum:可以匹配0-9、A-Z、a-z :alpha:可以匹配A-Z、a-z :blank:可以匹配空格或者tab键 :digit:可以匹配数字0-9 :graph:可以匹配非空字符 :lower:可以匹配a-z :print:与:graph:类似，不同之处在于:print:包括空格字符 :punct:可以匹配标点符号.,”等等 :space:可以匹配所有的空字符 :upper:可以匹配A-Z :xdigit:可以匹配十六进制数字0-9、A-F和a-f n这是对前一次匹配命中的一个后引用 (.)1可以匹配两个连续相同的字符，(.)

17、可以匹配除了null之外的任 何单个字符，1则重复上次匹配的内容，即再次匹配相同的字符， 因此可以匹配两个相同的字符 REGEXP_LIKE(x,pattern,match_option) 当源字符串x匹配正则表达式pattern时，返回true。 可以使用match_option修改默认匹配选项，该参数可以被设置为 c：说明在进行匹配时区分大小写 i：说明在进行匹配时不区人大小写 n：允许使用可以匹配任意字符的操作符 m：将x作为一个包含多行的字符串 REGEXP_INSTR(x,pattern ,start ,occurrence ,return_option ,match_option)

18、 在x中查找pattern，并返回pattern所在的位置。可以指定以下的可选参数 start开始查找的位置 occurrence说明应该返回第几次出现pattern的位置 return_option说明应该返回什么整数，若该参数是0，则说明要返 回的整数是x中的一个字符的位置，若该参数为非0的整数，则说 明要返回的整数为x中出现在pattern之后的字符的位置 match_option修改默认的匹配设置 REGEXP_REPLACE(x,pattern ,replace_string ,start ,occurrence ,match_option) 在x中查找pattern，并将其替换为r

19、eplace_string，其它选项的意思与 REGEXP_INSTR()函数的参数完全相同 REGEXP_SUBSTR(x,pattern ,start ,occurrence ,match_option) 返回x中可以匹配pattern的一个子字符串，其开始位置由start指定，其 它选项与REGEXP_INSTR()函数的参数完全相同 检索生日在1965到1968之间的顾客。 在first_name中搜索j或者J开头的行。 寻找一个起始位置，以l开头，中间是字符，而且是连续4个字符。 light被替换为sound。 使用聚合函数（分组函数） 聚合函数同时对一组进行操作，对每组返回一行输出结果。 聚合函数主要是数学函数，通常用于对多行的某一列的列值进行操作，结果 返回一个数值。 AVG(x)返回x的平均数 COUNT(x)返回一个包含x的查询返回的行数 MAX(x)返回