Oracle常用系统函数

 2 DUAL是Oracle与数据字典一起自动创建的一个表，它只有一列：DUMMY，其数据类型为：VARCHAR2(1)。DUAL中只有一行数据：'X'。
DUAL属于SYS模式，但所有用户都可以使用DUAL名称访问它。
用SELECT计算常量表达式、伪列等值时常用该表，因为它只返回一行数据，而使用其它表时可能返回多个数据行。


--nvl(comm,0) 空值默认值
 select *  from emp where nvl(comm,0)<100;
--last_day(hiredate) 本月最后一天
select * from  emp where last_day(hiredate) =hiredate;
--months_between(sysdate,hiredate)  返回日期之间的月数
select * from emp where  months_between(sysdate,hiredate)<8
--add_months   返回服务期限
select ename as 姓名,add_months(hiredate,12*3)  from emp;

--ASCII（c）函数  CHR(I)   小写比大写 大32    小写a 97  小写z 122
select ascii('Z') A,ascii('y') a,ascii('D') D,ascii('#') space  from dual;
select chr(90),chr(122),chr(68),chr(32) s from dual;
--CONCAT(s1,s2) 函数  字符串拼接
select concat('Hello',' World！') information from dual;
--INiTCAP(S)函数  返回每个单词的第一个字母大写，其他小写 
select initcap('gOod lUck For yOu!')  information from dual;
--在Oracle 11g中 ，从第三个字符开始查询字符串“1”第2次出现的位置
instr('s1','s2','1','1')
select instr('o1racle 11g','1',3,2) abc from dual;  --2个参数默认值都是1
--lower(s)小写 upper('') 大写 函数
--LTRIM(s1,s2)-删除字符串s1左边的字符串s2,RTRIM(S1,S2)-删除字符串s1右边的s2, 
--TRIM(s1,s2)函数
select ltrim('####East####','#'),rtrim('East      '),trim('#' from '####East###') from  dual;
--- create or replace### DROP TABLE "SIMON"."T_LOG"; CREATE TABLE "SIMON"."T_LOG"
---除了表对象，其他都可以用functions, procedures, packages, types, synonyms, trigger and views
--REPLACE(s1,s2[,s3])函数
select replace('Bad Luck Bad Girl','Bad','Great') from dual;
translate(string,from_str,to_str) 
 /*将string字符串中的存在的字符串from_str的每个字符从左到右替换成字符串to_str的对应的每个字符，如果没有对应上的则在string中会被替换为空。 区分大小写； to_str不能为空； 
例子1： select translate('项目组24355translate','234','@#$') from dual; 
项目组$#55translate **此时相当于@替换了2，#替换了3，$替换了4 */

--SUBSTR（s,i,[j]）函数***  字符串截取函数
select substr('Message-Pretty-Box',9,6) from dual;  
--CEIL(n)函数 返回大于或等于 数值n的最小整数
--FLOOR
select ceil(7.8),ceil(7),ceil(-7.3) from dual;
--Round(n1,n2)函数  返回该函数舍入小数点右边n2位的n1的值
select round(3.1415926,2) from dual;
--日期和时间函数
select sysdate as 系统日期 from dual;

--在当前的日期下加6个月
select ADD_MONTHS(sysdate,6) from dual;
--转化类函数 ***                        to_date('1992-7-30','YYYY-MM-DD')
select sysdate as 默认格式日期,to_char(sysdate,'YYYY-MM-DD') as 转换后日期 from  dual;
--转化为10进制
select to_number('ABC','XXX') as 十进制 from dual;
--聚合类函数*****
--avg()平均值，count()总记录数，max() --最大值,min() --最小值 ,sum()求和，variance()方差，STDDEV()标准差；
select  count(empno) as 员工数，round(avg(sal),2) as 平均工资 from emp;
select  count(*) from emp;



Lpad（str1,number,str2），这个函数的意思是，如果str1不足number那么多位，则使用str2去补齐左边的空位

--求天数
select to_date('08/06/2015','mm/dd/yyyy')-to_date('07/01/2015','mm/dd/yyyy') from dual;
 select trunc(sysdate-hiredate) from emp;          
--求月数
select months_between(to_date('01/31/2015','mm/dd/yyyy'),to_date('12/31/2014','mm/dd/yyyy')) 
"MONTHS" FROM DUAL; 
--求余函数
mod(9,2)


一、Lpad函数

lpad函数将左边的字符串填充一些特定的字符其语法格式如下：lpad(string,n,[pad_string])

string

字符或者参数

n

字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string

可选参数，这个字符串是要粘贴到string的左边，若这个参数未写，lpad函数将会在string的左边粘贴空格。

lpad('tech', 7); 将返回' tech'

lpad('tech', 2); 将返回'te'

lpad('tech', 8, '0'); 将返回'0000tech'

lpad('tech on the net', 15, 'z'); 将返回'tech on the net'

lpad('tech on the net', 16, 'z'); 将返回'ztech on the net'


二、Rpad函数

rpad函数将右边的字符串填充一些特定的字符其语法格式如下：rpad(string,n,[pad_string])

string

字符或者参数

n

字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string

可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。

rpad('tech', 7); 将返回' tech'

rpad('tech', 2); 将返回'te'

rpad('tech', 8, '0'); 将返回'tech0000'

rpad('tech on the net', 15, 'z'); 将返回'tech on the net'

rpad('tech on the net', 16, 'z'); 将返回'tech on the netz'

abs()--取绝对值 
trunc函数

　　1）trunc函数处理数字

　　　　　　TRUNC（number[,decimals]）
　　　　　　其中：
　　　　　　number 待做截取处理的数值
　　　　　　decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。
　　　　　　trunc就是处理数字的显示位数，如果decimals为负数，就处理整数部分，处理完为0，-1就是各位为零，-2就到了十位，如果超过了 整数部分长度，则整个数字0；
　　2）处理日期　　　　
　　　　trunc函数返回以指定元元素格式截去一部分的日期值。
　　　　其具体的语法格式如下：
　　　　TRUNC（date,[fmt]）
　　　　其中：
　　　　date为必要参数，是输入的一个日期值
　　　　fmt参数可忽略，是日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去
　　　　下面是该函数的使用情况：

trunc(sysdate,'yyyy') --返回当年第一天.
trunc(sysdate,'mm') --返回当月第一天.
trunc(sysdate,'d') --返回当前星期的第一天.
selecttrunc(sysdate,'YYYY')from dual;
selecttrunc(sysdate,'MM')from dual;
selecttrunc(sysdate,'D')from dual;

1。应用到数学上
round()的含义是对操作数四舍五入运算。
第一个参数是操作数，第二个参数为精度要求
SELECT ROUND(1.298, 1) from dual; 1.3
SELECT ROUND(1.298, 0) from dual; 1
SELECT ROUND(23.298, -1) from dual; 20

trunc()的含义对操作数直接砍去一部分
第一个参数是操作数，第二个参数表示要保留小数位
SELECT TRUNC(1.223,1) from dual; 1.2
SELECT TRUNC(1.999,1) from dual; 1.9
SELECT TRUNC(1.999,0) from dual; 1
SELECT TRUNC(-1.999,1) from dual; -1.9
SELECT TRUNCATE(122,-2) from dual; 100
SELECT TRUNC(10.28*100,0) from dual; 1028

2.应用到日期上
round()日期进行四舍五入操作
如：
SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') 01-JAN-01 对月份进行四舍五入操作
SELECT ROUND (TO_DATE ('27-OCT-00'),'MONTH') 01-NOV-00 对日期进行四舍五入操作

round()日期进行砍去操作
如：
SELECT TRUNC (TO_DATE ('27-OCT-00'),'YEAR') 01-JAN-00 对月份进行砍去操作
SELECT TRUNC (TO_DATE ('27-OCT-00'),'MONTH') 01-OCT-00 对日期进行砍去操作

3.对日期进行round(),trunc()操作的意义在于，当我们要对某段时间内，按月份或年份操作时有重要的作用。
比如：查找5月3号到12月8之间的每月的电费
select sum(dianfei) from (
select dianfei, trunc(date) month from dianfeitable where date between 5月3号 and 12月8号)
group by month