oracle基础知识2_单行函数

n61z39g71r

　　
　　dual 是一个 用来测试函数和表达式的伪表
　　
　　
　　内容分为 单行  多行 函数两块:
　　
　　

　　
　　
　　字符函数:
　　
　　1 大小写函数
　　
　　select lower('Hello world') as 转小写, upper('Hello World') as 转大写,initcap('zm') as 首字母大写  from dual;
转小写转大写首字hello worldHELLO WORLDZm　　
　　2 截取函数
　　--substr(a,b) 从a中，第b位开始取，取右边所有的字符
　　select substr('hello world',3) from dual;
　　
　　--substr(a,b,c) 从a中，第b位开始取，取c位
　　select substr('hello world',3,4) from dual;
　　
　　3 字符/字节长度
　　--length 字符数  lengthb 字节数
　　select length('中国')  字符数,lengthb('中国')  字节数 from dual;
字符数字节数24
　　4 instr 查询子串位置
　　instr(a,b) 从a中查找b，找到返回下标（从1开始计数），否则返回0
　　select instr('hello world','ll') from dual;
LOCATION3　　
　　5 左/右填充  lpad(src,10填满后长度,str用这个字符来填充)
　　select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
左右******abcdabcd******　　
　　6 trim(不仅仅是去掉空格)
　　select trim('H' from  'Hello WorldH') as trim from dual;
　　
TRIMello World　　
 7 替换函数 replace
　　select replace('hello world','l','*') as replaceres from dual;
REPLACEREShe**o wor*d　　
　　
　　
　　数字函数:
　　
　　1 四舍五入 ROUND(srcNum, 保留小数位数) 
　　select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三,ROUND(45.926, -1) 四, ROUND(45.926, -2) 五   from dual;
　　
一二三四五45.9345.946500
　　2 截断函数 TRUNC （仅仅是截断，不会四舍五入）
　　select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,
        TRUNC(45.926, -1) 四, TRUNC(45.926, -2) 五   from dual
　　
一二三四五45.9245.94540　　
　　
　　日期函数:
　　
　　oracle只有  date, datestamp 两种类型， mysql有三种，date,datatime, datestamp;
　　
　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time from dual;
TIME2014-06-04 13:53:15　　select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') as time from dual;
TIME2014-06-04 13:50:56:045000
　　
　　日期格式的元素:

格式	举例
YYYY	2010
YEAR	年的英文名称  twenty ten
MM	两位数字 不足补0   06
MONTH	4月
DY	星期一
DAY	星期一
DD	一个月的第几天 02

　　
　　
　　转换函数:
　　
　　数据类型转换分为两种: 显示 和  隐式
　　
　　oracle自动隐式转换如下:
　　

源数据类型	目标数据类型
varchar2/char	number
varchar2/char	date
number	varchar2
date	varchar2

　　
　　eg: select * from emp where hiredate = '17-11月-81';  这就是典型将符合oracle日期格式的字符串隐式转换成日期后在查询的例子。
　　
　　显示函数:
　　
　　1 to_char
　　to_char函数常用到的格式：

9	数字
0	零
$	美元
L	本地货币符号
.	小数点
,	千位符

　　
　　select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是" day') as today from dual;
　　
TODAY2014-06-04 14:51:25今天是 星期三　　
　　利用 to_char函数，将工资显示的转化成我们想看到的格式:
　　select to_char(sal,'L9,999.99') as sal from emp;
SAL￥6,000.00￥800.00￥1,600.00￥1,250.00￥2,975.00￥1,250.00￥2,850.00　　
　　
　　
　　通用函数:
　　
　　nvl2(a,b,c) 当a=null时，返回c，否则返回b
　　select sal*12+nvl2(comm,comm,0) from emp;
　　
年薪7200096001950015500357001640034200　　
　　
　　条件表达式:
　　
　　1 case:
　　select ename,job,sal as 原始薪 ,
    case job when 'PRESIDENT'  then sal + 1000
             when 'MANAGER' then sal+800
             else sal + 300
    end as 长后薪

from emp;
　　
ENAMEJOB原始薪长后薪Tom_ABCD 60006300SMITHCLERK8001100ALLENSALESMAN16001900WARDSALESMAN12501550　　
　　2 decode
　　
　　decode (col|expression,  search1, result1[, search2, result2,...,]
　　[,default])
　　看col|expression的数值，如果是search1，则执行 result1，如果是search2，则执行 result2... 如果都不说，则执行default
　　
　　select ename,job,sal 涨前薪水,
          decode(job,'PRESIDENT',sal+1000,
                      'MANAGER',sal+800,
                                sal+400) 涨后薪水
    from emp;
　　
ENAMEJOB涨前薪æ涨后薪æTom_ABCD 60006400SMITHCLERK8001200ALLENSALESMAN16002000WARDSALESMAN12501650　　
　　
　　案例: 统计 1980,1981,1982,1987年入职的员工:
　　
　　select count(*) Total,
           sum(decode(to_char(hiredate,'RR'),'80',1,0)) "1980",
           sum(decode(to_char(hiredate,'RR'),'81',1,0)) "1981",
           sum(decode(to_char(hiredate,'RR'),'82',1,0)) "1982",
           sum(decode(to_char(hiredate,'RR'),'87',1,0)) "1987"
    from emp;
　　
TOTAL19801981198219871511012