四、oracle 单 行 函 数

SQL函数

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值

function_name [(arg1, arg2,...)]

大小写控制函数

这类函数改变字符的大小写。

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  last_name = 'higgins';
no rows selected

SELECT employee_id, last_name, department_id
FROM   employees
WHERE  LOWER(last_name) = 'higgins';

字符控制函数

SELECT employee_id, CONCAT(first_name, last_name) NAME, 
       job_id, LENGTH (last_name), 
       INSTR(last_name, 'a') "Contains 'a'?"
FROM   employees
WHERE  SUBSTR(job_id, 4) = 'REP';

数字函数

• ROUND: 四舍五入
  ROUND(45.926, 2) 45.93
• TRUNC: 截断
  TRUNC(45.926, 2) 45.92
• MOD: 求余
  MOD(1600, 300) 100

ROUND 函数

SELECT ROUND(45.923,2), ROUND(45.923,0),
       ROUND(45.923,-1)
FROM   DUAL;

DUAL 是一个‘伪表’，可以用来测试函数和表达式

TRUNC 函数

SELECT  TRUNC(45.923,2), TRUNC(45.923),
        TRUNC(45.923,-2)
FROM   DUAL;

MOD 函数

SELECT last_name, salary, MOD(salary, 5000)
FROM   employees
WHERE  job_id = 'SA_REP';

日 期

Oracle 中的日期型数据实际含有两个值: 日期和时间。

SELECT last_name, hire_date
FROM   employees
WHERE  last_name like 'G%';

函数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


yyyy  年
mm   月
dd   日
day  星期       hh   小时      mi   分钟    ss 秒

转换函数

隐式数据类型转换

Oracle 自动完成下列转换：

显式数据类型转换

TO_CHAR函数对日期的转换

TO_CHAR(date, 'format_model')

格式:

• 必须包含在单引号中而且大小写敏感。
• 可以包含任意的有效的日期格式。
• 日期之间用逗号隔开。

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

日期格式的元素

• 时间格式

   HH24:MI:SS AM 				15:45:32 PM
  

• 使用双引号向日期中添加字符

   DD "of" MONTH				12 of OCTOBER
  

SELECT last_name,
       TO_CHAR(hire_date, 'DD Month YYYY')
       AS HIREDATE
FROM   employees;


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

TO_DATE 函数对字符的转换

• 使用 TO_DATE :

   TO_DATE(char[, 'format_model'])
  

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

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

TO_CHAR函数对数字的转换

	TO_CHAR(number, 'format_model')

下面是在TO_CHAR 函数中经常使用的几种格式:

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

TO_NUMBER 函数对字符的转换

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

   TO_NUMBER(char[, 'format_model'])
  

• 使用 TO_NUMBER :

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

通用函数

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

• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, …, exprn)

NVL 函数

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

• 可以使用的数据类型有日期、字符、数字。

• 函数的一般形式:

   NVL(commission_pct,0)
   NVL(hire_date,'01-JAN-97')
   NVL(job_id,'No Job Yet')
  

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

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

exp1 != null ? exp2 : exp3

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;

条件表达式

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

• 使用两种方法:

   CASE 表达式
   DECODE 函数
  

CASE 表达式

在需要使用 IF-THEN-ELSE 逻辑时:

CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
END

下面是使用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 函数

在需要使用 IF-THEN-ELSE 逻辑时:

DECODE(col|expression, search1, result1 ,
      			   [, search2, result2,...,]
      			   [, default])

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;

使用decode函数的一个例子：

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;

嵌套函数

• 单行函数可以嵌套。
• 嵌套函数的执行顺序是由内到外
  

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

总 结

• 使用函数对数据进行计算
• 使用函数修改数据
• 使用函数控制一组数据的输出格式
• 使用函数改变日期的显示格式
• 使用函数改变数据类型
• 使用 NVL 函数
• 使用IF-THEN-ELSE 逻辑