ORACLE简单函数

1、单行函数

单行函数主要分为5种：

• 字符函数   可以对有字符组成的字符串进行操作。
• 数字函数   可以对数字进行计算。
• 转换函数   可以将一种数据库类型转换成另外一种数据库类型。
• 日期函数   可以对日期和时间进行处理。
• 正则表达式函数   在查询数据时可以使用正则表达式。

1.1、字符函数

字符函数的输入参数为字符类型，其返回值时字符类型或数字类型。

字符函数

函数	含义	用法
ASCII(x)	用于返回字符x的ASCII码值	select ascii('a')"a" from dual;
LENGTH(x)	返回x中字符的个数	select length(column1) from table_name where column2 = 10;
CONCAT(x,y)	用于将y添加到x上，并将得到的字符串作为结果返回	select concat('hello','oracle') from dual;
LOWER(x)	把x中的字母转换成小写，并返回得到的字符串	select lower(column1) from table_name;
SUBSTR(x,start[,length])	返回x中的一个字符串，这个字符串从start处开始，还可以为这个字符串指定一个可选的length参数	select substr('welecom',2,4) from dual;
INSTE(x,find_string[,start][,occurrence])	在x中查找字符串find_string，然后返回find_string所在的位置。可选的start位置来指定该函数从这个位置开始查找，同时可选occurrence参数，来说明该返回find_string第几次出现的位置	select instr('welecom','e'),instr('welcome','e',2,2) from dual;
INITCAP(x)	将x中每个单词的首字母都转换成大写,并返回得到的字符串	select initcap('welcome to you') from dual;
REPLACE(x,serch_string,replace_string)	在x中查找search_string，并将其替换为replace_string。（replace_string是可选的，当不指定参数时，默认的参数值时空字符串）	select replace('ABCDEFG','CD','123'),replace('ABCDEFG','CD') from dual;
RPAD(x,width[,pad_string])	在x的右边补齐空格，得到总长为width个字符的字符串。可选的pad_string，这个参数用于指定重复使用哪个字符串来补齐x右边的空位	select rpad(column1,10,'*') from table_name;

1.2、数字函数

数字函数

函数	含义	用法
ABS(x)	返回c的绝对值	select abs(10),abs(-10) from dual;
MOD(x,y)	返回x除以y的余数	select mod(8,3),mod(8,4) from dual;
CEIL(x)	返回大于或者等于x的最小整数值	select ceil(13.1),ceil(-5.2) from dual;
FLOOR(x)	小于或者等于数字x的最大整数值	select floor(13.1) from dual;
POWER(x,y)	返回x的y次幂	select power(2,3) from dual;
SQRT(x)	返回x的平方根，并且x必须大于等于0	select sqrt(4) from dual;
ROUND(x[,y])	对x取整的结果；y是可选的参数	select round(123.456,2) from dual;
SIGN(x)	用于检测数字的正负。如果数字x小于0，测函数的返回值为-1；如果数字x等于0，则函数的返回值为0；如果数字x大于0；则函数的返回值为1	select sign(10),sign(0),sign(-10) from dual;
COS(x)	返回数字x（以弧度表示的角度值）的余弦值。	select cos(0.5) from dual;
ACOS(x)	返回数字x的反余弦值，输入值的范围是-1~1，输出值的单位为弧度	select acos(0.3) from dual;

1.3、转换函数

转换函数用于将数值从一种数据类型转换成为另一种数据类型。

1. TO_CHAR()：TO_CHAR(x[,format])用于将x转换为一个字符串，也可以指定一个可选的参数format来说明x的格式，参数format取决于x是数字还是日期。用法如下：

   select to_char(123) || 'hello' from dual;

    

2. TO_NUMBER()：TO_NUMBER(x[,format])用于将x转换为一个数字，可以指定一个可选的字符串format参数来说明x的格式。用法如下：

   select to_number('123') + 23 from dual;

   select to_number('-$12,345.67','&99,999.99') from dual;

    

3. CAST(x AS type)：用于将x转换为type所指定的兼容数据库类型。用法如下：

   select cast(123 as varchar(10)) || 'hello',cast('456' as number(10)) + 123 from dual;

    

4. ASCIISTR(string)：用于将任意字符集的字符集转变为数据库字符集的ASCII字符集。用法如下：

   select asciistr('中国') from dual;

    

5. BIN_TO_NUM(x)：用于将二进制数字x转换为NUMBER类型。用法如下：

   select bin_to_num(1,0) from dual;

    

2、聚合函数

1. AVG()函数：该函数可以返回所提供的所有非NULL值的平均值。（如果在传递给AVG()的记录集合中包含了NULL值，那么就会将这些值完全忽略。）用法如下：

   select avg(column1) from dual;

    

2. COUNT()函数：该函数返回记录集合中的所有非NULL值的总和，NULL不作记录。用法如下：

   select count(column1) from dual;

    

3. MAX()和MIN()函数：MAX(x)和MIN(x)用于计算x的最大值和最小值。用法如下：

   select max(column1),min(column2) from table_name;

   （MAX()和MIN()函数可以用于任何数据类型，包括字符串和日期。在对字符串使用MAX()函数时，字符串按照字符顺序排序，字符串的“最大值”位于列表的底部，“最小值”位于列表的顶部）

4. STDDEV()函数：STDDEV(x)用于计算x的标准差。（标准差是一个统计函数，其定义时方差的平方根。）用法如下：

   select stddev(column1) from table_name;

    

5. SUM()：SUM(x)用于计算并返回x中的所有值的和。

   select sum(column1) from table_name group by column1;

    

6. VARIANCE()：VARIANCE(x)用于计算x的方差。（方差是一个统计函数，其定义为一组样本数据的偏高程度，等于标准差的平方。）用法如下：

   select variance(column1) from table_name;

    

3、日期时间处理函数

日期时间函数用于处理DATE和TIMESTAMP类型的数据。除了函数MONTHS_BETWEEN()返回数字值外，其他日期函数均返回DATE类型的数据。（默认日期显示格式为“DD-MON-YY”）

3.1、使用TO_CHAR()和TO_DATE()转换时间值

TO_CHAR(x[,format])用于将时间值x转换为字符串，该函数还可以提供一个可选的参数format来说明x的格式。格式的一个例子 MONTH DD，YYYY，其中个参数含义如下：

• MONTH  表示大写的月份全名。
• DD  表示两位天数。
• YYYY  表示4位的年份。

例如：

select column1,to_char(column_date,'MONTH DD,YYYY') from table_name where column2 = 30;

 

TO_DATE(x[,format])用于将字符串x转换为时间值，该函数可以指定一个可选的字符串format参数来说明x的格式。（如果没有指定format参数，日期就是用默认的数据库格式。）用法如下：

select to_date('11 -12月-2018') from dual;

3.2、使用时间值函数

1. SYSDATE函数：该函数会根据数据库的时区，使用DATE值返回当前日期。（由于SYSDATE函数没有参数，所以在使用时省略括号。）用法如下：

   select sysdate from dual;

    

2. MONTHS_BETWEEN()函数：MONTHS_BETWEEN(x,y)可以判断日期x和y之间的月份数量。返回值是一个实数，它可以指出x和y之间的完整的月份和月份片段。用法如下：

   select months_between(to_date('2018/12/11','yyyy/mm/dd'),to_date('2018/11/11','yyyy/mm/dd')) as '月份间隔' from dual;