Oracle常用系统函数
1 2 DUAL是Oracle与数据字典一起自动创建的一个表,它只有一列:DUMMY,其数据类型为:VARCHAR2(1)。DUAL中只有一行数据:'X'。 3 DUAL属于SYS模式,但所有用户都可以使用DUAL名称访问它。 4 用SELECT计算常量表达式、伪列等值时常用该表,因为它只返回一行数据,而使用其它表时可能返回多个数据行。 5 6 7 --nvl(comm,0) 空值默认值 8 select * from emp where nvl(comm,0)<100; 9 --last_day(hiredate) 本月最后一天 10 select * from emp where last_day(hiredate) =hiredate; 11 --months_between(sysdate,hiredate) 返回日期之间的月数 12 select * from emp where months_between(sysdate,hiredate)<8 13 --add_months 返回服务期限 14 select ename as 姓名,add_months(hiredate,12*3) from emp; 15 16 --ASCII(c)函数 CHR(I) 小写比大写 大32 小写a 97 小写z 122 17 select ascii('Z') A,ascii('y') a,ascii('D') D,ascii('#') space from dual; 18 select chr(90),chr(122),chr(68),chr(32) s from dual; 19 --CONCAT(s1,s2) 函数 字符串拼接 20 select concat('Hello',' World!') information from dual; 21 --INiTCAP(S)函数 返回每个单词的第一个字母大写,其他小写 22 select initcap('gOod lUck For yOu!') information from dual; 23 --在Oracle 11g中 ,从第三个字符开始查询字符串“1”第2次出现的位置 24 instr('s1','s2','1','1') 25 select instr('o1racle 11g','1',3,2) abc from dual; --2个参数默认值都是1 26 --lower(s)小写 upper('') 大写 函数 27 --LTRIM(s1,s2)-删除字符串s1左边的字符串s2,RTRIM(S1,S2)-删除字符串s1右边的s2, 28 --TRIM(s1,s2)函数 29 select ltrim('####East####','#'),rtrim('East '),trim('#' from '####East###') from dual; 30 --- create or replace### DROP TABLE "SIMON"."T_LOG"; CREATE TABLE "SIMON"."T_LOG" 31 ---除了表对象,其他都可以用functions, procedures, packages, types, synonyms, trigger and views 32 --REPLACE(s1,s2[,s3])函数 33 select replace('Bad Luck Bad Girl','Bad','Great') from dual; 34 translate(string,from_str,to_str) 35 /*将string字符串中的存在的字符串from_str的每个字符从左到右替换成字符串to_str的对应的每个字符,如果没有对应上的则在string中会被替换为空。 区分大小写; to_str不能为空; 36 例子1: select translate('项目组24355translate','234','@#$') from dual; 37 项目组$#55translate **此时相当于@替换了2,#替换了3,$替换了4 */ 38 39 --SUBSTR(s,i,[j])函数*** 字符串截取函数 40 select substr('Message-Pretty-Box',9,6) from dual; 41 --CEIL(n)函数 返回大于或等于 数值n的最小整数 42 --FLOOR 43 select ceil(7.8),ceil(7),ceil(-7.3) from dual; 44 --Round(n1,n2)函数 返回该函数舍入小数点右边n2位的n1的值 45 select round(3.1415926,2) from dual; 46 --日期和时间函数 47 select sysdate as 系统日期 from dual; 48 49 --在当前的日期下加6个月 50 select ADD_MONTHS(sysdate,6) from dual; 51 --转化类函数 *** to_date('1992-7-30','YYYY-MM-DD') 52 select sysdate as 默认格式日期,to_char(sysdate,'YYYY-MM-DD') as 转换后日期 from dual; 53 --转化为10进制 54 select to_number('ABC','XXX') as 十进制 from dual; 55 --聚合类函数***** 56 --avg()平均值,count()总记录数,max() --最大值,min() --最小值 ,sum()求和,variance()方差,STDDEV()标准差; 57 select count(empno) as 员工数,round(avg(sal),2) as 平均工资 from emp; 58 select count(*) from emp; 59 60 61 62 Lpad(str1,number,str2),这个函数的意思是,如果str1不足number那么多位,则使用str2去补齐左边的空位 63 64 --求天数 65 select to_date('08/06/2015','mm/dd/yyyy')-to_date('07/01/2015','mm/dd/yyyy') from dual; 66 select trunc(sysdate-hiredate) from emp; 67 --求月数 68 select months_between(to_date('01/31/2015','mm/dd/yyyy'),to_date('12/31/2014','mm/dd/yyyy')) 69 "MONTHS" FROM DUAL; 70 --求余函数 71 mod(9,2) 72 73 74 一、Lpad函数 75 76 lpad函数将左边的字符串填充一些特定的字符其语法格式如下:lpad(string,n,[pad_string]) 77 78 string 79 80 字符或者参数 81 82 n 83 84 字符的长度,是返回的字符串的数量,如果这个数量比原字符串的长度要短,lpad函数将会把字符串截取成从左到右的n个字符; 85 86 pad_string 87 88 可选参数,这个字符串是要粘贴到string的左边,若这个参数未写,lpad函数将会在string的左边粘贴空格。 89 90 lpad('tech', 7); 将返回' tech' 91 92 lpad('tech', 2); 将返回'te' 93 94 lpad('tech', 8, '0'); 将返回'0000tech' 95 96 lpad('tech on the net', 15, 'z'); 将返回'tech on the net' 97 98 lpad('tech on the net', 16, 'z'); 将返回'ztech on the net' 99 100 101 二、Rpad函数 102 103 rpad函数将右边的字符串填充一些特定的字符其语法格式如下:rpad(string,n,[pad_string]) 104 105 string 106 107 字符或者参数 108 109 n 110 111 字符的长度,是返回的字符串的数量,如果这个数量比原字符串的长度要短,lpad函数将会把字符串截取成从左到右的n个字符; 112 113 pad_string 114 115 可选参数,这个字符串是要粘贴到string的右边,如果这个参数未写,lpad函数将会在string的右边粘贴空格。 116 117 rpad('tech', 7); 将返回' tech' 118 119 rpad('tech', 2); 将返回'te' 120 121 rpad('tech', 8, '0'); 将返回'tech0000' 122 123 rpad('tech on the net', 15, 'z'); 将返回'tech on the net' 124 125 rpad('tech on the net', 16, 'z'); 将返回'tech on the netz' 126 127 abs()--取绝对值 128 trunc函数 129 130 1)trunc函数处理数字 131 132 TRUNC(number[,decimals]) 133 其中: 134 number 待做截取处理的数值 135 decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分。 136 trunc就是处理数字的显示位数,如果decimals为负数,就处理整数部分,处理完为0,-1就是各位为零,-2就到了十位,如果超过了 整数部分长度,则整个数字0; 137 2)处理日期 138 trunc函数返回以指定元元素格式截去一部分的日期值。 139 其具体的语法格式如下: 140 TRUNC(date,[fmt]) 141 其中: 142 date为必要参数,是输入的一个日期值 143 fmt参数可忽略,是日期格式,用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去 144 下面是该函数的使用情况: 145 146 trunc(sysdate,'yyyy') --返回当年第一天. 147 trunc(sysdate,'mm') --返回当月第一天. 148 trunc(sysdate,'d') --返回当前星期的第一天. 149 selecttrunc(sysdate,'YYYY')from dual; 150 selecttrunc(sysdate,'MM')from dual; 151 selecttrunc(sysdate,'D')from dual; 152 153 1。应用到数学上 154 round()的含义是对操作数四舍五入运算。 155 第一个参数是操作数,第二个参数为精度要求 156 SELECT ROUND(1.298, 1) from dual; 1.3 157 SELECT ROUND(1.298, 0) from dual; 1 158 SELECT ROUND(23.298, -1) from dual; 20 159 160 trunc()的含义对操作数直接砍去一部分 161 第一个参数是操作数,第二个参数表示要保留小数位 162 SELECT TRUNC(1.223,1) from dual; 1.2 163 SELECT TRUNC(1.999,1) from dual; 1.9 164 SELECT TRUNC(1.999,0) from dual; 1 165 SELECT TRUNC(-1.999,1) from dual; -1.9 166 SELECT TRUNCATE(122,-2) from dual; 100 167 SELECT TRUNC(10.28*100,0) from dual; 1028 168 169 2.应用到日期上 170 round()日期进行四舍五入操作 171 如: 172 SELECT ROUND (TO_DATE ('27-OCT-00'),'YEAR') 01-JAN-01 对月份进行四舍五入操作 173 SELECT ROUND (TO_DATE ('27-OCT-00'),'MONTH') 01-NOV-00 对日期进行四舍五入操作 174 175 round()日期进行砍去操作 176 如: 177 SELECT TRUNC (TO_DATE ('27-OCT-00'),'YEAR') 01-JAN-00 对月份进行砍去操作 178 SELECT TRUNC (TO_DATE ('27-OCT-00'),'MONTH') 01-OCT-00 对日期进行砍去操作 179 180 3.对日期进行round(),trunc()操作的意义在于,当我们要对某段时间内,按月份或年份操作时有重要的作用。 181 比如:查找5月3号到12月8之间的每月的电费 182 select sum(dianfei) from ( 183 select dianfei, trunc(date) month from dianfeitable where date between 5月3号 and 12月8号) 184 group by month