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

 

转载于:https://www.cnblogs.com/Remedy/p/7291960.html