ORACLE SQL单行函数（三）【weber出品必属精品】

16.L:代表本地货币符，这个和区域有关。这个时候我们想来显示一下人民币的符号：￥

$ vi .bash_profile       ---写入如下内容：
 
export NLS_LANG='SIMPLIFIED CHINESE'_CHINA.AL32UTF8   ---修改成简体中文+地区+字符集
 
source .bash_profile       ---让环境变量生效
 
[oracle@oracle ~]$ sqlplus scott/tiger
 
SQL*Plus: Release 10.2.0.5.0 - Production on 星期五 8月 15 18:38:22 2014
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL>---如果你是用x-shell或者putty远程连接linux的话，需要将你的客户端的字符集也改回来

17.TO_NUMBER:将一个字符串转化为数字

把十进制转换为十六进制：（to_char）

SQL> select to_char(10,'xxx') from dual;
 
TO_CHAR(10,'
------------
   a

把十六进制转换为十进制：（to_number）

SQL> select to_number('a','xxx') from dual;
 
TO_NUMBER('A','XXX')
--------------------
          10

18.日期的两位表示方式：RR和YY

SQL> select * from emp where hiredate=to_date('17-12-80','dd-mm-rr');
  
EMPNO ENAME  JOB         MGR HIREDATE              SAL       COMM DEPTNO
----- ------ --------- ----- ------------------- ----- ---------- ------
 7369 SMITH  CLERK      7902 1980-12-17 00:00:00   800                20
 
日期的两位表示方式：RR和YY
 
YY:始终与当前世纪一致
 
RR：得到的是与当前年最近的年份

 19.操作NULL值的函数

   NVL(expr1, expr2)

   如果expr1为null,则返回expr2,否则返回expr1

   可以使用的数据类型是数字、日期、字符型.

   数据类型必须能够匹配（expr1和expr2的类型必须要一致）

SQL> select ename ,comm,nvl(comm,0) nvl from emp;
 
ENAME                    COMM    NVL
------------------------------ ---------- ----------
SMITH                             0
ALLEN                     300    300
WARD                      500    500
JONES                             0
MARTIN                   1400    1400

20.NVL(expr1, expr2，expr2)

如果expr1非空，就返回expr2

如果expr1为空，就返回expr3

SQL> select ename,sal,comm,sal+comm,sal+nvl(comm,0),nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp;
 
ENAME    SAL       COMM   SAL+COMM SAL+NVL(COMM,0) 收入类别
------ ----- ---------- ---------- --------------- ---------------
SMITH    800                                   800 纯工资
ALLEN   1600        300       1900            1900 工资加奖金
WARD    1250        500       1750            1750 工资加奖金
JONES   2975                                  2975 纯工资
MARTIN  1250       1400       2650            2650 工资加奖金
BLAKE   2850                                  2850 纯工资
CLARK   2450                                  2450 纯工资
SCOTT   4000                                  4000 纯工资
KING    5000                                  5000 纯工资
TURNER  1500          0       1500            1500 工资加奖金
ADAMS   1100                                  1100 纯工资
JAMES    950                                   950 纯工资
FORD    3000                                  3000 纯工资
MILLER  1300                                  1300 纯工资

21.NULLIF(expr1,expr2)

如果expr1和expr2相同就返回空，否则返回expr1

SQL> select ename,nullif(ename,'KING') from emp;
 
ENAME  NULLIF(ENAME,'KING')
------ ------------------------------
SMITH  SMITH
ALLEN  ALLEN
WARD   WARD
JONES  JONES
MARTIN MARTIN
BLAKE  BLAKE
CLARK  CLARK
SCOTT  SCOTT
KING
TURNER TURNER
ADAMS  ADAMS
JAMES  JAMES
FORD   FORD
MILLER MILLER

22. COALESCE(expr1,expr2,expr3,…exprn)：返回括号内第一个非空的值

23.条件表达式函数

 

decode与case when

 

区别：

 

     1. decode是oracle自己的函数，case when是SQL99标准函数

 

     2. decode只能进行等值判断，case when可以进行非等值判断

​SQL> select ename,job,case job when 'CLERK' then '1.clerk'
     when 'SALESMAN' then '2.saleman'
          else 'other'  end
          from emp
  
ENAME  JOB   CASEJOBWHEN'CLERK'THEN'1.CL
------ --------- ---------------------------
SMITH  CLERK     1.clerk
ALLEN  SALESMAN  2.saleman
WARD   SALESMAN  2.saleman
JONES  MANAGER   other
MARTIN SALESMAN  2.saleman
BLAKE  MANAGER   other
CLARK  MANAGER   other
SCOTT  ANALYST   other
KING   PRESIDENT other
TURNER SALESMAN  2.saleman
ADAMS  CLERK     1.clerk
JAMES  CLERK     1.clerk
FORD   ANALYST   other
MILLER CLERK     1.clerk

 decode用法实例

SQL> select ename,decode(job,'SALESMAN','CLERK','UNKNOWN') from emp;
 
ENAME  DECODE(JOB,'SALESMAN'
------ ---------------------
SMITH  UNKNOWN
ALLEN  CLERK
WARD   CLERK
JONES  UNKNOWN
MARTIN CLERK
BLAKE  UNKNOWN
CLARK  UNKNOWN
SCOTT  UNKNOWN
KING   UNKNOWN
TURNER CLERK
ADAMS  UNKNOWN
JAMES  UNKNOWN
FORD   UNKNOWN
MILLER UNKNOWN

使用case when进行非等值判断：

SQL> select ename,sal,case when sal between 700 and 1200 then 1
when sal between 1201 and 1400 then 2
when sal between 1401 and 2000 then 3
when sal between 2001 and 5000 then 4
else 5 end grade from emp
 
ENAME         SAL      GRADE
------ ---------- ----------
SMITH         800      1
ALLEN        1600      3
WARD         1250      2
JONES        2975      4
MARTIN       1250      2
BLAKE        2850      4
CLARK        2450      4
SCOTT        3000      4
KING         5000      4
TURNER       1500      3
ADAMS        1100      1
JAMES         950      1
FORD         3000      4
MILLER       1300      2

24.函数的嵌套

单行的函数可以嵌套到任何一个层次

嵌套函数的计算顺序——先计算深层嵌套，再计算浅层嵌套.

SQL> select ename,mgr,nvl(to_char(mgr),'no manager') from emp;
 
ENAME    MGR NVL(TO_CHAR(MGR),'NOMANAGER')
------ ----- ----------------------------------------
SMITH   7902 7902
ALLEN   7698 7698
WARD    7698 7698
JONES   7839 7839
MARTIN  7698 7698
BLAKE   7839 7839
CLARK   7839 7839
SCOTT   7566 7566
KING         no manager
TURNER  7698 7698
ADAMS   7788 7788
JAMES   7698 7698
FORD    7566 7566
MILLER  7782 7782