Oracle经典练习题—单行函数

基础知识

1、字符函数

 

upper（n）（返回字符串的大写形式）

SELECT * FROM emp WHERE ename=UPPER('smith') ;

 Lower（n）（返回字符串的小写形式）

SELECT lower('ABCDE') FROM dual ;

 Initcap （n）（首字母大写）

Select initcap(ename) from emp;

 Concat（m,n）(连接m和n)

Select concat('a','b ')    from    dual; Select  'a' || 'b ' from dual;

Substr(chr ,m[,n])

描述：取chr的字串，m代表开始位置，n是要取的长度，当m为0时从首字符开始，当m为负时从字符尾部开始截取。（第一个字符为1）

Select substr('abcde',length('abcde')-2) from dual; Select substr('abcde',-3,3) from dual;

 Length(n)(返回字符或者字符串的长度，长度包括空格)

Select length(ename) from emp;

 Replace(chr,search_string,[,replacement_string])

描述：将chr中满足search_string条件的替换为replacement_string指定的字符串，当search_string为null时，返回chr；当replacement_string为null时，返回chr中截取掉search_string部分的字符串。

Select replace(ename,'a','A') from emp;

 Instr (chr1,chr2,[n,[m]])

描述：获取字符串chr2在字符串chr1中出现的位置。n和m可选,省略是默认为1；n代表开始查找的起始位置，当n为负数从尾部开始搜索；m代表字串出现的次数

Select instr('Hello World','or') from dual; 8

Lpad (chr1,n,[chr2])

描述：在chr1左边填充字符chr2，使得字符总长度为n。chr2可选，默认为空格；当chr1字符串长度大于n时，则从左边截取chr1的n个字符显示。

lpad('Smith',10,'*')  左侧填充  lpad() *****Smith

Rpad（chr1，n，chr2）

描述：在chr1右边填充chr2，使返回字符串长度为n..当chr1长度大于n时，返回左端n个字符。参考LPAD()函数。

rpad('Smith',10,'*')  右侧填充  rpad()Smith*****

 Trim

 trim('    Mr Smith          ')  过滤首尾空格  trim() Mr Smith

2、数值函数

MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。

select mod(24,5) from dual;

ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是文章中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。

select round(412,-2) from dual;

TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。退一法

select trunc (412.13,-2) from dual;

3、日期函数

Months_between(m,n)

日期m和n相差的月数  

select months_between(sysdate,hiredate) from emp;

 Add_months(m,n)

当前日期m后推n个月 ,用于从一个日期值增加或减少一些月份

select add_months(sysdate,1) from dual;

Next_day(d, day_of_week)

返回由"day_of_week"命名的，在变量"d"指定的日期之后的第一个工作日的日期。参数"day_of_week"必须为该星期中的某一天。

select  next_day(sysdate,'星期一') from dual;

Last_day

本月最后一天

select last_day(sysdate) from dual;

4、转换函数

To_char

select to_char(sysdate,'yyyy') from dual; select to_char(sysdate,'fmyyyy-mm-dd') fr om dual; select to_char(sal,'L999,999,999') from emp; select to_char(sysdate,’D’) from dual;//返回星期

To_number

select to_number('13')+to_number('14') from dual;

To_date

Select to_date(„20090210‟,‟yyyyMMdd‟) from dual;

 

5、通用函数

NVL(expr1,expr2)

如果第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

select nvl(comm,0) from emp;

NULLIF(expr1,expr2)

 如果表达式exp1 与exp2 的值相等则返回null ，否则  返回exp1 的值  

NVL2(expr1,expr2, expr3)

如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数的值。

select empno, ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;

 coalesce（）

 依次考察各参数表达式，遇到非null 值即停止并返  回该值。

select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入  from emp;

 CASE 表达式

select empno, ename, sal,  case deptno        when 10 then '财务部'      when 20 then '研发部'      when 30 then '销售部'   else '未知部门'         end  部门 from emp;

DECODE() 函数  和case 表达式类似，decode() 函数也用于实现多路分支结构    

select empno, ename, sal,  decode(deptno, 10, '财务部',  20, '研发部',  30, '销售部',  '未知部门')       部门  from emp;

单行函数嵌套

select empno, lpad(initcap(trim(ename)),10,' ')    name, job, sal from emp;

练习题

--1.找出每个月倒数第三天受雇的员工（如：2009-5-29 ）

select * from emp where last_day(hiredate)-2=hiredate;

--2.找出25 年前雇的员工

select * from emp where add_months(hiredate,25*12)<=sysdate;

--3.所有员工名字前加上Dear ,并且名字首字母大写

两种方式：

select lpad(initcap(ename),length(ename)+4,'Dear') from emp;

select 'Dear'||initcap(ename) from emp;

--4.找出姓名为5 个字母的员工

select * from emp where length(ename)=5;

--5.找出姓名中不带R 这个字母的员工

两种方式：

select * from emp where ename not like '%R%';

select * from emp where instr(ename,'R')=0; 

--6.显示所有员工的姓名的第一个字符

select substr(ename,0,1)from emp;