Oracle 的转换函数实例展示
- --自动类型转换select 1 + '1'from dual;
- --前面是字符串,会自动转换成date类型select add_months('1 - 7月 - 2017', 2) from dual;
- --to_number to_char to_date select sysdate from dual; --把日期函数转换成字符串:to_char() select sysdate,
- to_char(sysdate) from dual;
- --自己定义日期的格式select sysdate,
- to_char(sysdate, 'YYYY - MM - DD HH24: MI: ss') from dual;
- select sysdate,
- to_char(sysdate, 'YYYY - MM - DD') from dual;
- select sysdate,
- to_char(sysdate, 'HH24: MI: ss') from dual;
- --将数字转换成字符串:to_char select 123,
- to_char(123) from dual; --9代表占位符,如果位数少于前面的数字,都会显示#--L代表本地货币符号select 123,
- to_char(123, '999') from dual;
- --to_date将字符串转换成日期类型select months_between(sysdate, '2017 - 12 - 17') from dual; --to_date()必须要指定日期的格式select months_between(sysdate, to_date('2017 - 12 - 17', 'YYYY - MM - DD')) from dual;
- --to_number将字符串转数字字符串必须只有数字select'123',
- to_number('123.000') from dual;
- --nvl相当于java中的条件运算符a ? b: c--查询所有员工的姓名,职位,薪水,提成和总工资select ename,
- job,
- sal,
- comm,
- sal + comm income from emp;
- select ename,
- job,
- sal,
- nvl(comm, o) comm,
- sal + nvl(comm, 0) income from emp;
- --nvl2: 有三个参数,第一个参数为null,返回第三个参数的值,否则返回第二个select ename,
- job,
- sal,
- comm,
- nvl2(comm, sal + comm, sal) income from emp; --两者数据类型一定要一致select comm,
- nvl(comm, '无') from; --如果数据类型不一致,将出现报错现象。
- --decode功能类似
- switch...
- case...--查询所有员工的职位,并且以中文显示select distinct job from emp;
- select decode(job, 'CLERK', '职员', 'SALESMAN', '销售员', 'PRESIDENT', '不知道', 'MANAGER', '经理', 'ANALYST', '分析师') as job from emp;