【Oracle】单行函数

1. 字符函数

A：upper（转大写）

selectUpper('abcde')from dual;

 

 

SELECT *FROM empWHERE ename=UPPER('smith');

 

B：Lower（转小写）

SELECTlower('ABCD')FROM dual;

 

C：Initcap（首字母大写）

SELECTinitcap(ename)from  emp;

 

D：Concat（字符连接函数，等同于||）

SELECTconcat('a','b')FROM dual;

 

 

SELECT'a'||'b'from dual;

E：Substr（求母串中的某个子串）

SELECTsubstr('abcde',length('abcde')-2)from dual;

 

 

F：Length（字符数）

SELECTlength(ename)from emp;

 

注意：lengthb：字节数

G：Replace（字符串替换函数）

selectreplace('Hello Wordl','l','*')from dual;

 

 

SELECTreplace(ename,'SMITH','A')from emp;

 

 

说明：将原字符串替换为新字符串

H：Instr（在母查中查找子串的位置）

SELECTinstr('Hello World','or')from dual;

 

I：Lpad（左侧填充）

lpad('SMITH',10,'*')

左侧填充：

(10-smith的长度)

J：Rpad（右侧填充）

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

K：Trim（去掉字符串前后指定的字符）

selecttrim('H'from'HelloWorldH')from dual;

  

trim(‘   hello  world   ’);过滤首尾空格

2. 数值函数

A：Round（四舍五入）

selectround(412,-2)from dual;

 

selectround(412.313,2)from dual;

 

B：Mod（求余）

select mod(1600,300)from dual;

 

C：Trunc（截断）

SELECTtrunc(412.13,-2)from dual;

 

3. 日期函数

A：Months_between()（两个日期相差的月数）

select months_between(sysdate,hiredate)from emp;

B：Add_months()（在指定日期上加上若干个月）

select add_months(sysdate,1)from dual;

 

C：Next_day()

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

D：Last_day（某个日期当月的最后一天 ）

select last_day(sysdate) from dual;

 

练习

1. 1. 显示当前日期

 

select sysdatefrom dual;

 

1. 1. 显示时间部分 

 

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

 

 

1. 1. 显示昨天，今天和明天，加减数字仍未日期

 

selectsysdate-1昨天,sysdate今天,sysdate+1明天from dual;

 

 

1. 1. 两个日期相减，结果为相差的天数，查询员工信息，显示员工工龄。两个日期不能相加 

 

selectempno,ename,sysdate-hiredate天fromemp; 

 

1. 1. 查询员工信息，显示员工工龄,分别按照天，星期，月显示

 

selectempno,ename,sysdate-hiredate天,(sysdate-hiredate)/7星期, (sysdate-hiredate)/30月fromemp; 

 

1. 1. months_between:两个日期相差的月数

 

select (sysdate-hiredate)/30方式一,months_between(sysdate,hiredate)方式二from emp;

 

1. 1. add_months:在指定日期上加上若干个月

 

selectadd_months(sysdate,1) 下个月, add_months(sysdate,123)"123个月后"from dual

 

1. 1. last_day:某个日期当月的最后一天 

 

select last_day(sysdate)from dual;

 

1. 1. next_day:下周六 

 

selectnext_day(sysdate,'星期五')from dual;

 

1. 1. 对日期进行四舍五入

 

selectround(sysdate,'MONTH') 月,round(sysdate,'YEAR')from dual;

 

1. 1. 对日期进行截断

 

selecttrunc(sysdate,'MONTH') 月,trunc(sysdate,'YEAR')fromdual; 

 

1. 1. 日期格式 

 

select * from empwhere hiredate=to_date('1982-01-23','yyyy-mm-dd');

 

1. 1. 查询当前日期：显示:  2011-09-17 15:12:15今天是星期六 

 

selectto_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day')from dual;

 

1. 1. 查询员工信息，显示员工的编号，姓名，月薪,要求有货币代码(L)，千位符(,)，小数点(.)

 

select empno,ename,to_char(sal,'L9,999.99')from emp; 

4.转换函数

A：To_char

select  to_char(sysdate,’yyyy’)  from dual;

select to_char(sysdate,’fmyyyy-mm-dd’) from dual;

select to_char(sal,’L999,999,999’) from  emp;

select to_char(sysdate,’D’) from dual;//返回星期

B：To_number

select to_number(‘13’)+ to_number(‘14’)  from dual;

C：To_date

select to_date(‘20090219’,’yyyyMMdd’) from  dual;

5.通用函数

A：NVL()函数

select nvl(comm,0)  from emp;

B：NULLIF()函数

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

C：NVL2()函数

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

D：COALESCE()函数

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

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

E：CASE表达式

select empno , ename, sal ,

case  deptno

when 10  then ‘财务部’

when 20  then ‘研发部’

when 30  then ‘销售部’

else ‘未知部门’

end部门

from  emp;

F：DECODE()函数

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

select empno , ename , sal ,

decode(deptno, 10 , ‘财务部’ ,

 20,’研发部’,

 30,’销售部’,

 ‘未知部门’)

 部门

 from emp ;

 

练习：

1. 1. 找出每个月倒数第三天受雇的员工

 

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

 

1. 1. 找出25年前雇的员工

 

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

 

1. 1. 所有员工名字前加上Dear，并且名字首字母大写

 

                           select ‘Dear’ ||  initcap(ename)  from emp;

 

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

 

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

 

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

 

                          select * from emp  where ename  not  like ‘%R%’;

 

1. 1. 显示所有员工的姓名的第一个字

 

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

 

1. 1. 显示所有员工，按名字降序排列，若相同，则按工资升序排序

 

                           select * from emp order byname desc,salary asc;

 

假设一个月为 30 天，找出所有员工的日薪，不计小数
 

 select  empno,ename,round((nvl(sal,0)+nvl(comm,0))/30) from emp; 

     

      9. 找到2月份受雇的员工

 

                       select * from  emp where  to_char(hiredate , ‘fmmm’)= ‘2’;

 

     10. 列出员工加入公司的天数（四舍五入）

  select t.*,round(sysdate-t.hiredate) from emp t

     11. 分别用case和decode函数列出员工所在的部门，deptno=10显示’部门10’，deptno=20显示’部门20’，deptno=30显示’部门30’，deptno=40显示’部门40’否则为’其他部门’

 

select ename,

       case deptno

         when 10 then

         '部门10'

         when 20 then

         '部门20'

         when 30 then

         '部门30'

         when 40 then

         '部门40'

         else

         '其他部门'

      end工资情况

  from scott.emp

select ename,

        decode(deptno,

               10,

              '部门10',

               20,

              '部门20',

               30,

              '部门30',

               40,

              '部门40',

              '其他部门')工资情况

   from scott.emp