Oracle中的数字函数、日期、条件表达式

1.数字函数

 ①ROUND:

四舍五入    

    ROUND(45.926, 2)  45.93

②TRUNC:  截断

    TRUNC(45.926, 2)  45.92

③MOD: 求余

    MOD(1600, 300)  100

例子：

SELECT last_name, salary, MOD(salary,5000)

FROM   employees

WHERE  job_id = 'SA_REP';

 

2.oracle中的日期和时间

默认的日期格式是 DD-MON- YY，函数SYSDATE 返回日期和时间

在日期上加上或减去一个数字结果仍为日期；

两个日期相减返回日期之间相差的 天数；

可以用数字除24来向日期中加上或减去天数；

 

①日期的数学运算

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE  department_id = 90;

②日期函数

 

• MONTHS_BETWEEN('01-SEP-95','11-JAN-94')    ------19.6774194

• ADD_MONTHS('11-JAN-94',6)                               ------'11-JUL-94'

• NEXT_DAY('01-SEP-95',' FRIDAY ')                        ------'08-SEP-95'

• LAST_DAY('01-FEB-95')                                         ------'28-FEB-95'

 

Assume SYSDATE = '25-JUL-95':

• ROUND(SYSDATE,'MONTH')         01-AUG-95

• ROUND(SYSDATE,'YEAR')         01-JAN-96

• TRUNC(SYSDATE,'MONTH')        01-JUL-95

• TRUNC(SYSDATE,'YEAR')         01-JAN-95

 

③TO_CHAR 函数对日期的转换

SELECT TO_CHAR(sysdate,‘yyyy-mm-dd hh:mi:ss’) FROM dual;

格式要求:

• 必须包含在单引号中而且大小写敏感 。

• 可以包含任意的 有效的日期格式 。

• 日期之间用逗号隔开 。

select employee_id,last_name,hire_date
from employees
where to_char(hire_date,’yyyy-mm-dd’) = ‘1987-09-17’

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM   employees
WHERE  last_name = 'Ernst';

• 使用 TO_NUMBER 函数将字符转换成数字 :

TO_NUMBER(‘￥1,234,567,890.00’,’L999,999,999,999.99’)from dual

• 使用 TO_DATE 函数将字符 转换成日期 :

TO_DATE(‘2012年10月29日 08:10:21’,’yyyy“年”mm”月”dd“日”hh:mi:ss’)
From dual

2.通用函数

      

  ①使用NVL函数

这些函数适用于任何数据类型，同时也适用于空值：

• NVL (expr1, expr2)

• NVL2 (expr1, expr2, expr3)

• NULLIF (expr1, expr2)

• COALESCE (expr1, expr2, ..., expr n )

 

将一个已知的空值转换成一个已知的值：

SELECT last_name, salary, NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

②使用NVL2函数

NVL2(expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

SELECT last_name,  salary, commission_pct,
       NVL2(commission_pct, 
            'SAL+COMM', 'SAL') income
FROM   employees WHERE department_id IN (50, 80);

③使用NULLIF函数

NULLIF (expr1, expr2):  相等返回NULL，不等返回expr1

SELECT first_name, LENGTH(first_name) "expr1", 
       last_name,  LENGTH(last_name)  "expr2",
       NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

④使用COALESCE 函数

• COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE

SELECT   last_name,
         COALESCE(commission_pct, salary, 10) comm
FROM     employees
ORDER BY commission_pct;

 

3.条件表达式

• 在 SQL 语句中使用 IF-THEN-ELSE 逻辑

• 使用两种方法 :

– CASE 表达式

– DECODE 函数

说明：job_id列有:'IT_PROG','ST_CLERK','SA_REP' 根据这列进行判断

①case函数

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "REVISED_SALARY"
FROM   employees;

②decode函数

说明：REVISED_SALARY是一个新列

SELECT last_name, job_id, salary,
       DECODE(job_id, 'IT_PROG',  1.10*salary,
                      'ST_CLERK', 1.15*salary,
                      'SA_REP',   1.20*salary,
              			    salary)
       REVISED_SALARY
FROM   employees;

例子：

SELECT last_name, salary,
       DECODE (TRUNC(salary/2000, 0),
                         0, 0.00,
                         1, 0.09,
                         2, 0.20,
                         3, 0.30,
                         4, 0.40,
                         5, 0.42,
                         6, 0.44,
                            0.45) TAX_RATE
FROM   employees
WHERE  department_id = 80;

 

4.嵌套函数由里到外执行

SELECT last_name,
       NVL(TO_CHAR(manager_id), 'No Manager')
FROM   employees
WHERE  manager_id IS NULL;