oracle条件中日期函数怎么用,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'

AssumeSYSDATE = '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;