Oracle 常用函数的使用

截取函数

round         

Round(number,[decimal_places])四舍五入函数
    number:需要处理的数据
    decimal_places:可选，四舍五入时小数留几位 ( 默认为 0 )
    例子：
        select round(12.345) from dual；     ----result:12
        select round(12.345,1) form dual;    ----result:12.3
        select round(12.345,2) from dual;    ----result:12.35

              
trunc

trunc(date,[fmt])
    date：需要被截取的date数据
    fmt:可选, 日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去
    例子：
        select trunc(sysdate,'yyyy') from dual;    ----result:返回当年第一天
        select trunc(sysdate,'mm') from dual;      ----result:返回当月第一天
        select trunc(sysdate,'d') from dual;       ----result:返回当周第一天（以礼拜天为第一天）    
        select trunc(sysdate,'dd') from dual;      ----result:返回当天零点时间

extract

extract(time from dateTime)截取特定内容从日期数据
    time:日期数据中的特定数据, { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }   
    dateTime:被截取的日期数据, { date_value | interval_value }
    例子：
        select extract(year from sysdate) from dual;    ----result:2019
        select extract(month from sysdate) from dual;   ----result:8
        select extract(day from sysdate) from dual;     ----result:7

    //获取两个日期之间的具体时间间隔，extract函数是最好的选择
    例子：
        //取sysdate1和sysdate2两个时间之间间隔的天数
        select extract(day from (sysdate1-sysdate2)) from dual;
        select extract(day from (systimestamp1-systimestamp2)) from dual;

ceil/floor

ceil(n)/floor(n)
    ceil(n) 取大于等于数值n的最小整数；
    floor(n)取小于等于数值n的最大整数
    例子：
        select ceil(5.6) from dual;    ----result:6
        select floor(5.6) from dual;   ----result:5
    
    实际案例：
        //计算员工加入公司的天数
        select ceil(fireDate-employDate) from dual;
    

条件判断函数

decode

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
    
    例子：
        //比较大小，取num1和num2之间数值小的一方
        select decode(sign(num1-num2),-1,num1,num2) from dual;

        //和order by 一起进行排序,例如，对tb_subject表的subject_name里面的内容以理化生顺序进行排序
        select * from tb_subject order by decode(subject_name,'物理'，1，'化学'，2，'生物'，3)；

nvl

nvl(expression1,repression2)
    expression1:表达式1，字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值
    expression2:表达式2
    逻辑：判断expression1的内容是否为null，如果为null则返回expression2，如果expression1不为null则返回expression1;

    注意：expression1的数据类型必须和expresssion2的数据类型一致；除非显式的使用to_char()将表达式的值进行转换。

nvl2(E1, E2, E3)            

nul2(expression1,expression2,expression3)
    expresssion1:表达式1
    expresssion2:表达式2
    expresssion3:表达式3

    nvl2()是nvl()的增强版， 逻辑为：如果expresssion1为NULL，则函数返回expresssion3，否则返回expresssion2

sign                   

sign(expression)
    逻辑：根据expression是0、正数还是负数，分别返回0、1、-1

    例子：
        //判断大小,结合decode()
        select decode(sign(num1-num2),1,'num1>num2','num2>=num1') from dual;    

转换函数

to_number/to_char

to_number(x [, format], [ nls_language ])  ||  to_char(n [, fmt [, 'nlsparam']])
    to_number()和to_char()函数作用相反

    to_number():将字符串转换为数值型
        x: 是将要被转换成number的字符串
        format: 可选项，是用来将x转换成number的格式
        nls_language: 可选项，是nls语言，用来将x转换成number

    to_char():将数值型转化为字符串
        n: 需要被转换为字符串的数据 timestamp、int、float、numeric
        fmt: 需要被转换的数据格式
        nlsparam：指定由数值格式的元素返回的字符
            ．小数点字符
            ．组分隔符
            ．本地钱币符号
            ．国际钱币符号

Element	Example	Description
, .	1,234.56	Commas and decimal points  逗号和小数点
$	$123.45	Leading dollar sign. 前置$符号
0	0012.34	Leading or trailing 0. 前置或后置0
9	123	Any digit. 任意数字
B	B123	Leading blank for integers. integer类型前置空白符
C	C123	The ISO currency symbol defined in the NLS_ISO_CURRENCY parameter.定义在NLS_ISO_CURRENCY参数中的国际标准货币符号
D	123D99	The current decimal character defined in the NLS_NUMERIC_CHARACTERS parameter. The default value is a period.
EEEE	1.2EEE	Returns a value in scientific notation.用科学计数法表示返回值
G	9G123	Returns the group separator (e.g., a comma).
L	L123	Returns the local currency symbol.
MI	123MI	negative value with trailing minus sign; returns positive value with a trailing blank.
PR	123PR	The negative values in angle brackets.
RN	I	values in Roman numerals, uppercase.
rn	i	values in Roman numerals, lowercase.
S (prefix)	S1234	negative values with a leading minus sign, positive values with a leading positive sign.
S (suffix)	1234S	negative values with a trailing minus sign, positive values with a trailing positive sign.
TM	TM	The text minimum number format model returns the smallest number of characters possible.
U	U123	the Euro currency symbol or the NLS_DUAL_CURRENCY parameter.
V	123V99	a value multiplied by 10n, where n is the number of 9s after the V.
X	XXXX	the hexadecimal value.十六进制值

to_timestamp/to_date

to_date(string1 [, format_mask] [, nls_language]) || to_timestamp(string1 [, format_mask] ['nlsparam'] ) 

    string1： 需要被转化为date的字符串
    format_mask： 可选，转化格式
    nls_language： 可选，用于转化date的nls语言
    例子：
        TO_DATE('2003/07/09', 'yyyy/mm/dd')    ----Result: date value of July 9, 2003
        TO_DATE('070903', 'MMDDYY')            ----Result: date value of July 9, 2003
        TO_DATE('20020315', 'yyyymmdd')        ----Result: date value of Mar 15, 2002

        TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')    ----'13-DEC-03 10.13.18.000000000 AM'

            
bitand

bitand( expr1, expr2 )
    逻辑: 在expr1和expr2之间进行位运算
    注意：expr1和expr2必须为非负整型数据
    
    例子：
        select bitand(15,7) from dual;    ----result:7
        select bitand(5,0) from dual;     ----result:0
      
        15=2^3+2^2+2^1+2^0      1111
        7=2^2+2^1+2^0           0111
                               -------按位与运算
                                0111