oracle基础知识2_单行函数

 

dual 是一个 用来测试函数和表达式的伪表

 

 

内容分为 单行  多行 函数两块:

 

 

 

字符函数:

 

1 大小写函数

 

select lower('Hello world') as 转小写, upper('Hello World') as 转大写,initcap('zm') as 首字母大写  from dual;

转小写	转大写	首字
hello world	HELLO WORLD	Zm

 

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;

字符数	字节数
2	4

4 instr 查询子串位置

instr(a,b) 从a中查找b，找到返回下标（从1开始计数），否则返回0

select instr('hello world','ll') from dual;

LOCATION
3

 

5 左/右填充  lpad(src,10填满后长度,str用这个字符来填充)

select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

左	右
******abcd	abcd******

 

6 trim(不仅仅是去掉空格)

select trim('H' from  'Hello WorldH') as trim from dual;

 

TRIM
ello World

 7 替换函数 replace

select replace('hello world','l','*') as replaceres from dual;

REPLACERES
he**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.93	45.9	46	50	0

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.92	45.9	45	40

 

 

日期函数:

 

oracle只有  date, datestamp 两种类型， mysql有三种，date,datatime, datestamp;

 

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time from dual;

TIME
2014-06-04 13:53:15

select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff') as time from dual;

TIME
2014-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;

 

TODAY
2014-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;

 

年薪
72000
9600
19500
15500
35700
16400
34200

 

 

条件表达式:

 

 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;

 

ENAME	JOB	原始薪	长后薪
Tom_ABCD		6000	6300
SMITH	CLERK	800	1100
ALLEN	SALESMAN	1600	1900
WARD	SALESMAN	1250	1550

 

 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;

 

ENAME	JOB	涨前薪æ	涨后薪æ
Tom_ABCD		6000	6400
SMITH	CLERK	800	1200
ALLEN	SALESMAN	1600	2000
WARD	SALESMAN	1250	1650

 

 

 案例: 统计 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;

 

TOTAL	1980	1981	1982	1987
15	1	10	1	2