Oracle函数

 

 

--TO_CHAR 转换成字符串类型

select to_char (sysdate,'YYYY"年"fmMM"月"fmDD"日"HH24:MI:SS') FROM dual;

 

select to_char(1210.7,'$9,999.00') from dual

 

-------------------------------------------------------------------------
--TO_DATE转换成日期类型
select to_date('2013-07-13','yyyy-mm-dd') from dual;

 

-------------------------------------------------------------------------

--months_between 两个日期相差的月数

select months_between
(to_date('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY')) "MONTHS"
from dual;

-------------------------------------------------------------------------

--日期相减
--两个日期天数之差
select floor(sysdate - to_date('20020405','yyyymmdd'))from dual;

--------------------------------------------------------------------------
--两个日期相差的月数之差
select months_between(sysdate,to_date('20111204','yyyymmdd')) from dual;

 

-------------------------------------------------------------------------
--TO_NUMBER转换成数值类型
select sqrt(to_number('100'))from dual;

-------------------------------------------------------------------------

--months_between 两个日期相差的月数

select months_between
(to_date('02-02-1995','MM-DD-YYYY'),
TO_DATE('01-01-1995','MM-DD-YYYY')) "MONTHS"
from dual;

-------------------------------------------------------------------------

 

--字符转换
--全部小写
select LOWER('SMITH') "CLERK" from DUAL;

-------------------------------------------------------------------------
--全部大写
SELECT UPPER('last_name') "Uppercase"
FROM dual;

 

-------------------------------------------------------------------------
--首字母大写
SELECT INITCAP('the soap') "Capitals"
FROM DUAL;

-------------------------------------------------------------------------
--字符串控制函数
--合并字符串
SELECT CONCAT('happy','boy')from dual;

-------------------------------------------------------------------------
--substr('要截取的字符串',起始位置)
select substr('HappyBoy',5) from dual;

-------------------------------------------------------------------------
--substr('要截取的字符,起始位置,取几个字符)
select substr('HappyBoy',2,3) from dual;

-------------------------------------------------------------------------
--length('字符串')：字符个数统计
--lengthb('字符串')：字节个数统计
select length('快客') 字符数,lengthb('赶来') as 字节数 from dual;

-------------------------------------------------------------------------
--instr('大字符串','小字符串')返回小字符串在大字符串中出现的位置
select instr('corporate floor','or', 3,2) "inserting" from dual;

 select instr('corporate floor','or', -3,2) "inserting" from dual;

 -----------------------------------------------------------------------

--隐式转换
select * from emp where hiredate='17-12月-80';

--显示转换

--to_char()对日期的转换
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

--to_char()对数字的转换
select to_char(sal,'L9,999,99') from emp;

--数字函数
--round()四舍五入
select round(12.45,1) from dual;

--trunc 截断
select trunc(15.79,1)"truncate" from dual;

 

--通用函数
--nv1和nv12 滤空函数

select sal*12工资,comm 奖金,sal*12+nvl(comm,0) from emp;

 

select sal*12工资,comm 奖金,sal*12+nvl2(comm,comm,0) from emp;

--decode函数
select product_id,
decode(warehouse_id,1,'Southlake',
2,'San Francisco',
3,'New Jersey',
4,'Seattle',
'Non domestic') "Location"
from inventories where product_id< 1775 order by product_id,"Location"