Oracle常用函数

--常用函数--
一、-字符串函数 
--1、lower upper
--注意在 Oracle 中变量和列的名称不区分大小写但是数据区分
--正确 select empno,ename,JOB,Mgr from emp where ename='SCOTT';
--错误.. select empno,ename,JOB,Mgr from emp where ename='scott';
--2、INITCAP函数
--作用将字符串转换成混合大小写每一个单词的开始都是一个大写字母
--      其余字符转换成小写字母(改变的只是当前显示格式) select empno,initcap(ename) from emp;
/*
3、SUBSTR函数 作用取子串函数 格式substr(C,P,L) 其中C 表示字符串P 表示要提取的起始的字符串的位置从1开始                           L 表示提取的长度。如果 P 为负值表示从字符串的                           最后一位倒着向前取。 */
select substr('I Love this game',3,4) sub from dual;
-- Love select substr('I Love this game',-3,2) sub from dual;
-- am select substr('I Love this game',-3,-2) sub from dual;
--L<=0时无结果
4、--LENGTH函数
--作用确定要分析的字符串的长度 select ename,length(ename) L0,sal,length(sal) L1 from emp;
5、--LPAD函数       
--作用用于字符串填充。填充的是字符串左侧的区域。
6、--RPAD函数       
--作用填充的是字符串右侧的区域。
select lpad(ename,6,'*') from emp;--ename 长度不够 6 时在左侧填充'*' 如*SMITH select rpad(ename,6,'*') from emp;--ename 长度不够 6 时在右侧填充'*' 如SMITH*
7、--LTRIM函数
8、--RTRIM函数
9、--TRIM函数
select length('   love   game  '),           --相当于('   love   game  ')的长度       
length(ltrim('   love   game  ')),           --相当于('love   game  ')的长度       
length(rtrim('   love   game  ')),           --相当于('   love   game')的长度       
length(trim('   love   game  ')) from dual;  --相当于('love   game')的长度 
10、--replace函数
--作用用于字符串的替换
--格式replace(C,S,R) 其中C 表示将处理的字符串S 表示想要查找的字符串
--                        R 表示将要替换的字符串。类似于”查找与替换“功能。
update emp set ename = upper(ename);
select replace(ename,'A','a') from emp;
select replace(ename,'a','A') from emp;
11、--concat函数
--作用字符串连接函数 || select concat(concat(ename,' '),job) NJ from emp; select ename||' '||job from emp;
12、--instr函数
--作用确定需要查找的字符串在给定字符串的起始位置找不到返回0
select instr('I Love this game','thi') from dual;
select instr('I Love this game','toi') from dual;

 
二、--数字函数—   
1、-- round 函数
-- 作用用来将数字四舍五入的指定的精度 select round(3.1415926,2) from dual;
--小数点后  保留2位 select round(3.1415926,3) from dual; select round(-3.5) from dual;
--注意 结果为-4与JAVA中不同在JAVA中 相当于 floor(x+0.5) 即 floor(-3.5+0.5) 值为-3 
2、--trunc 函数
--作用用来将数字字段截断为指定的精度。
--格式TRUNC(N,P)  select trunc(3.7777,2) num from dual;
--结果为3.77 select -54.75 n1,ceil(-54.75) n2,floor(-54.75) n3 from dual; 
--结果为-54.75  -54   -55  

三、--日期函数
-- select sysdate from dual; 
-- sysdate 为系统当前时间  dual 为系统临时表 select sysdate from emp;
-- 重复显示 select distinct sysdate from dual;
--to_date 日期转换函数
--日期格式元素的值
--MONTH  全部写出的月份名称添加空格达到9个字符的总宽度 APRIL
--MON  月份名称的三个字母简写     APR
--MM  月份的两位数字值     04
--RM  罗马数字的月份      IV
--D             一周中某一天的数值
--DD   一月中某一天的数值
--DDD           一年中某一天的数值
--DAY           一周中某一天的名称添加空格达到9个字符的宽度  Wednesday
--DY  一周中某一天的三个字母简写    WED
--YYYY  显示4位的年份
--YYY、YY、Y    显示年份的最后三位、最后两位或者最后一位
--YEAR          全部写出年份
--B.C. 或者 A.D. insert into emp2(empno,deptno,hiredate) values(9527,10,to_date('1993-1-1','yyyy-mm-DD'));    
--1993-1-1 insert into emp2(empno,deptno,hiredate) values(9528,10,to_date('1993-1-1','yyyy"-"mm"-"DD'));
--1993-1-1 insert into emp2(empno,deptno,hiredate) values(9529,10,to_date('1993年1-1','yyyy"年"mm-DD'));
--1993-1-1
--months_between 函数  前时间-后时间 select months_between(sysdate,to_date('2010-11-30','YYYY-MM-DD')) N from dual;
--  12 select months_between(to_date('2010-11-30','YYYY-MM-DD'),sysdate) N from dual;
--  -12 select months_between(sysdate,to_date('2010-11-10','YYYY-MM-DD')) N from dual;
--  12.6657638888889
-- select sysdate+2 from dual;
-- 结果系统当前时间两天后的时间
 
--nvl函数   nvl函数
--作用可以使用 nvl 函数来解决对可能包含 null 值的字段执行数学运算时导致的问题。
--      在Oracle9ii为internet中null 值不等于空格或者0.在计算中使用 null 值
--      时结果是 null 值。nul 函数使用一个值来代替所有的 null 值。 
select ename,sal,comm,sal+comm from emp;
--当 comm 为 null 值时sal+comm 也为 null 值
-- select ename,sal,nvl(comm,0),sal+nvl(comm,0) from emp;
-- conn 为 null 值时comm = 0,comm 不为 null 值时 comm = comm select ename,sal,nvl(comm,0),sal+nvl2(comm,100,10) from emp;
-- comm 为 null 值时comm = 10comm 不为 null 值时 comm = 100 
--to_char函数
select trim(3.14||' ') from dual; select to_char(sysdate,'yyyy-mm-dd') from dual;     
--  2011-11-30 select to_char(sysdate,'yyyy"-"mm"-"dd') from dual; 
--  2011-11-30 select to_char(sysdate,'yyyy"年"mm"月"dd') from dual;
--  2011年11月30 
--to_number();
select to_number('003000.00','999999.00') from dual;
-- 3000 select to_number('003000.00','000000.00') from dual;
-- 3000
select to_number('003000.00','999999.99') from dual;
-- 3000 
-- 9 显示数字并且忽略前导的 0
-- 0 显示数字不忽略前导的 0
select to_char(03000,'999999.99') from dual;         
--   3000.00 用空格代替 select to_char(03000,'000000.00') from dual;         
-- 003000.00

--case...when...then
--简单Case函数 
CASE sex  WHEN '1' THEN '男'  WHEN '2' THEN '女'  ELSE '其他' END 
--Case搜索函数 
CASE
WHEN sex = '1' THEN '男'  WHEN sex = '2' THEN '女'  ELSE '其他' END 

--decode
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')from   employees;

--distinct与count一起用
select count( distinct user_name) from department_user;
括号中不能使用distinct *