Oracle 常用函数

Oracle 函数

    函数接受一个或多个参数并返回一个值。

单行函数

    也称标量函数，对于从表中查询的每一行，该函数都返回一个值。

    单行函数出现在 SLEECT / WHERE 子句中。

1、日期函数

    对日期值进行运算，根据用途产生日期/数值类型的结果。

ADD_MONTHS(d, n)    返回 指定日期加上月数后的 日期值

MONTHS_BETWEEN(d1, d2)    返回 两个日期间的 月数

LAST_DAY(d)        返回 指定日期当前的最后一天的 日期值

RONUD(d,[fmt])        返回 指定日期四舍五入格式(YEAR、MONTH、DAY)后的 日期值

NEXT_DAY(d,day)        返回 指定日期下一个星期几的 日期值

TRUNC(d,[fnt])        返回 指定日期截断为格式后的 日期值

EXTRACT(fmt FROM d)    返回 指定日期提取的格式的 值

2、字符函数

    字符函数接受字符输入，并返回字符或数值。

INITCAP(char)        首字母大写

LOWER(char)        转换为小写

UPPER(char)        转换为大写

LTRIM(char, set)    左裁切

RTRIM(char, set)    右裁切

TRANSLATE(char, from, to)        按字母翻译

REPLACE(char, search_str, replace_str)    字符串替换

INSTR(char, substr[,pos])        查找子串位置

SUBSTR(char, pos, len)            取子字符串

CONCAT(char1, char2)            连接字符串

CHR(ascii)        根据 ASCII 码返回对应字符串

LPAD / RPAD        左 / 右 填充

    LPAD ('function', 15 , '=') 返回    '=======function'

TRAM            开头或结尾(或 开头和结尾)裁剪特定的字符，默认裁剪空格。

    TRIM ([LEADING | TRAILING] trim_char)

LENGTH(char)        返回字符串长度

DECODE            逐个值进行字符串替换

    DECODE (expr, search1, result1, search2, result2, [ ,default])

    DECODE (ostalus, 'p', '准备处理', 'c', '已完成')

3、数字函数

    数字函数接受数字输入并返回数值作为输出结果。

ABS(n)        取绝对值

CEIL(n)        向上取值

FLOOR(n)    向下去整

SIN(n)        正弦值

COS(n)        余弦值

POWER(m, n)    指数函数

SQRT(n)        平方根

MOD(m, n)    取余

ROUND(m, n)    小数点后精度四舍五入

TRUNC(m, n)    小数点后精度截断

4、转换函数

    转换函数将一种数据类型转换为另一种数据类型。

TO_CHAR (d|n, [,fmt])        格式化 日期 / 数值

TO_DATE (char [,fmt])        将 fmt模型格式的字符串 转换为日期型

TO_NUMBER (char)        将 包含数字的的字符串转换为 数值型

5、其他函数

NVL (exp, exp2)        如果 exp 为空返回 exp2；如果非空返回 exp

NVL2 (exp, exp2, exp3)    如果 exp 为空返回 exp3；如果非空返回 exp2

NULLIF (exp1, exp2)    比较两表达式，相等返回空值，不等则返回 exp1

分组函数 / 聚合函数

    分组函数基于一组行返回结果，即为每一组行返回单个值。

AVG (columname)         返回指定列的平均值

MAX (columname)         返回指定列的最大值

MIN (columname)         返回指定列的最小值

SUM (columname)         返回指定列的总值

COUNT   

    COUNT (*)        统计所有行个数，包括重复行和空值得行

    COUNT (columname)    统计指定列非空值的个行数

    COUNT (DISTINCR columname)    统计指定列中 非重复，非空值得行个数   

GROUP BY 子句

    用于将信息表划分为组，对查询结果按组进行聚合运算，为每组返回一个结果。

HAVING 子句

    用来指定 GROUP BY 子句的检索条件。

分析函数

    分析函数根据一组行来计算聚合值。这些函数通常用来完成对聚集的累积排名、移动平均数和报表计算。

    分析函数与聚合函数不同的是他们为每组记录返回多个行。

ROW_NUMBER () OVER ([PARTITION BY colum] ORDER BY colum)

    为有序组中的每一行返回一个唯一的排序值，序号由 ORDER BY 子句指定，从 1 开始，即使具有相等的值，排位也不同。

    PARTITION BY colum 按列值进行区分，各分组内在进行排序。

RANK () OVER ([PARTITION BY colum] ORDER BY colum)

    计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，序数随后跳跃相应的数值。

DENSE_RANK () OVER ([PARTITION BY colum] ORDER BY colum)

    计算一个值在一个组中的地位，由 1 开头，具有相等值得行排位相同，并且排位是连续的。