Oracle学习笔记_03_单行函数

1.SQL函数:

        单行函数、多行函数

 

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

 

2.单行函数分类

字符、数字、日期、转换、通用函数

 

3.字符函数

(1)大小写控制函数:LOWER、UPPER、INITCAP

LOWER('SQL Course')                  sql course
UPPER('SQL Course')          ——>     SQL COURSE
INITCAP('SQL Course')                Sql Course

 

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

 

(2)字符控制函数

CONCAT('Hello'     ,'World')                HelloWorld
SUBSTR('HelloWorld', 1    , 5   )           Hello
LENGTH('HelloWorld')                        10
INSTR('HelloWorld' ,'W')          ——>       6
LPAD(salary        , 10    , '*')           *****24000       (用*左填充)
RPAD(salary        , 10    , '*')           24000*****        
TRIM('H' FROM 'HelloWorld')                 elloWorld
REPLACE(‘abcd’     , ’b’   , ’m’)           amcd

 

4.数字函数

                                        个位数的下标为0

ROUND: 四舍五入                                   

                                       ROUND(45.926)  = ROUND(45.926,0)                 46   

                                       ROUND(45.926, 2)                                                  45.93          

TRUNC: 截断

                                       TRUNC(45.926)  =TRUNC(45.926,0)                   46
                                       TRUNC(45.926, 2)                                                   45.92

                                       TRUNC(45.926, -2)                                                  0   

                                       TRUNC(45.926, -1)                                                  40                              (保留十位,后面截去,补0)

MOD: 求余
                                       MOD(1600, 300)                                                      100

                                       MOD(salary, 5000)

 

5.日期函数

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

     函数SYSDATE 返回:  日期、时间

(1)日期的数学运算:

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

                                       两个日期相减返回日期之间相差的天数。        (日期不允许做加法运算,无意义)

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

(2)日期函数

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'

NEXT_DAY ('01-SEP-95',1)                                         指定日期的下一个星期1对应的日期


LAST_DAY ('01-FEB-95')                                        : 本月的最后一天                                             '28-FEB-95'

ROUND('25-JUL-95' ,'MONTH')                              : 日期四舍五入                                                '01-AUG-95'

TRUNC('25-JUL-95' ,'MONTH')                               : 日期截断                                                       '01-JUL-95'

 

6.转换函数

          数据类型转换:隐式 、显式

隐式: Oracle 自动完成下列转换

 

显式:

 

 (1) TO_CHAR(date, 'format_model')

 

select employee_id,hire_date
from employees
where to_char(hire_date,'yyyy/mm/dd') = '1994/06/07';
--where to_date('1994/06/07','yyyy/mm/dd') = hire_date;

 

 

TO_CHAR(number, 'format_model')

 

select to_char(12345678.99,'L999,999,999.99') from dual;

 

 

 

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

      

 

 

 

 

 

(2)TO_DATE(char[ , 'format_model' ])

select employee_id,hire_date
from employees
--where to_char(hire_date,'yyyy/mm/dd') = '1994/06/07';
where to_date('1994/06/07','yyyy/mm/dd') = hire_date;

 

(3)TO_NUMBER(char[ ,  'format_model' ])

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

 

7.通用函数

这些函数适用于任何数据类型,同时也适用于空值:
                   NVL (expr1, expr2)                                                若expr1不为空,则expr1=expr1 ; 为空,expr1=expr2           
                   NVL2 (expr1, expr2, expr3)                                   若expr1不为空,则expr1=expr2 ; 为空,expr1=expr3
                   NULLIF (expr1, expr2)                                           相等返回NULL,不等返回expr1 
                   COALESCE (expr1, expr2, ..., exprn)                    返回表达式中第一个非空表达式,如果都为空则返回空值。

 (1)  NVL (expr1, expr2)  

select last_name,nvl(to_char(department_id),'没有部门')
from employees;

(2)  NVL2 (expr1, expr2, expr3)  

select employee_id,  nvl2(commission_pct  ,commission_pct + 0.015    , 0.01)
from employees;

(3) NULLIF (expr1, expr2)     

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

(4) COALESCE (expr1, expr2, ..., exprn)  

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

 

(5)条件表达式之 CASE表达式

 

语法:

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

示例:

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;

 

(6)条件表达式之  DECODE 函数

语法: 若expression=search1,则结果为 result1 

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;

 

   

 

                      

 

posted @ 2017-07-18 21:02  shirayner  阅读(259)  评论(0编辑  收藏  举报