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. - Production on 星期五 8月 :: Copyright (c) , , Oracle. All Rights Reserved. Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5. - 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(,'xxx') from dual; TO_CHAR(,'
------------
a
把十六进制转换为十进制：（to_number）
SQL> select to_number('a','xxx') from dual;
TO_NUMBER('A','XXX')
--------------------
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
----- ------ --------- ----- ------------------- ----- ---------- ------
SMITH CLERK -- ::
日期的两位表示方式：RR和YY
YY:始终与当前世纪一致
RR：得到的是与当前年最近的年份
19.操作NULL值的函数
NVL(expr1, expr2)
如果expr1为null,则返回expr2,否则返回expr1
可以使用的数据类型是数字、日期、字符型.
数据类型必须能够匹配（expr1和expr2的类型必须要一致）
SQL> select ename ,comm,nvl(comm,) nvl from emp; ENAME COMM NVL
------------------------------ ---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
20.NVL(expr1, expr2，expr2)
如果expr1非空，就返回expr2
如果expr1为空，就返回expr3
SQL> select ename,sal,comm,sal+comm,sal+nvl(comm,),nvl2(comm,'工资加奖金','纯工资') "收入类别" from emp; ENAME SAL COMM SAL+COMM SAL+NVL(COMM,) 收入类别
------ ----- ---------- ---------- --------------- ---------------
SMITH 纯工资
ALLEN 工资加奖金
WARD 工资加奖金
JONES 纯工资
MARTIN 工资加奖金
BLAKE 纯工资
CLARK 纯工资
SCOTT 纯工资
KING 纯工资
TURNER 工资加奖金
ADAMS 纯工资
JAMES 纯工资
FORD 纯工资
MILLER 纯工资
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 .clerk
ALLEN SALESMAN .saleman
WARD SALESMAN .saleman
JONES MANAGER other
MARTIN SALESMAN .saleman
BLAKE MANAGER other
CLARK MANAGER other
SCOTT ANALYST other
KING PRESIDENT other
TURNER SALESMAN .saleman
ADAMS CLERK .clerk
JAMES CLERK .clerk
FORD ANALYST other
MILLER CLERK .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 and then
when sal between and then
when sal between and then
when sal between and then
else end grade from emp ENAME SAL GRADE
------ ---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
24.函数的嵌套
单行的函数可以嵌套到任何一个层次
嵌套函数的计算顺序——先计算深层嵌套，再计算浅层嵌套.
SQL> select ename,mgr,nvl(to_char(mgr),'no manager') from emp; ENAME MGR NVL(TO_CHAR(MGR),'NOMANAGER')
------ ----- ----------------------------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING no manager
TURNER
ADAMS
JAMES
FORD
MILLER