1. 1、字符串函数是oracle使用最广泛的一种函数.
2. LOWER:小写
3. UPPER:大写
4. INITCAP:首字母大写
5. CONCAT:连接
6. SUBSTR:截取 (参数,开始,数目)
7. LENGTH:返回字符串的长度
8. INSTR:(参数,字母) 返回字母出现的位置
9. LPAD:(参数,长度,在前补齐参数字母)
10.| RPAD:(参数,长度,在后补齐参数字母)
11.TRIM :
12.REPLACE:(参数,参数[,参数]):第一个参数操作数,第二是要查找的字符,第三个是替换的字符,如果没有第三个就删除查找的字符。
13.//查询名称为scott的员工信息 (不区分大小写)
14.SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp wherelower(ename)='scott';
15.SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp whereupper(ename)='SCOTT';
16.//查询员工信息 把员工名称与工作连接在一起
17.SQL> selectempno,concat(ename,job),mgr,hiredate,sal,comm,deptno from emp;
18.//查询员工信息 把员工名称与工作连接在一起
19.SQL> selectempno,concat(ename||'is work:',job),mgr,hiredate,sal,comm,deptno from emp;
20.//查询员工名称中含有O字符的位置
21.SQL> selectempno,instr(ename,'O'),job,mgr,hiredate,sal,comm,deptno from emp;
22.//查询员工名称中含有O字符的位置并且求出了员工名称字符长度
23.SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp;
24.//在查询上面结果中过滤出员工工作从第三字母开始为ERK员工信息
25.SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from empwhere substr(job,3)='ERK';
26.备注:当含有两个参数的时候,从开始位置直接到参数结束的结束为止
27.//在效果同上的同时指明了截取个数为3
28.SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from empwhere substr(job,3,3)='ERK';
29.//查询员工信息 薪资是10位位数不够在左部分补*填充
30.SQL> selectempno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptno from emp;
31.//查询员工信息 薪资是10位位数不够在右部分补*填充
32.SQL> selectempno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptno from emp;
33.//查询员工信息 把员工名称中含有S字符去除掉
34.SQL> selectempno,TRIM('S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
35.等效于:
36.SQL> selectempno,TRIM( both 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
37.//查询员工信息 把员工名称中前面有S字符去除掉
38.SQL> selectempno,TRIM( Leading 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
39.//等效于
40.SQL> selectempno,LTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
41.//查询员工信息 把员工名称中后面有S字符去除掉
42.SQL> selectempno,TRIM( trailing 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
43.等效于:
44.SQL> selectempno,RTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
45.
46.
47.2、数字函数:
48.ROUND: 四舍五入
49.ROUND(45.926, 2) 45.93
50.TRUNC: 截断
51.TRUNC(45.926, 2) 45.92
52.MOD: 求余
53.MOD(1600, 300) 100
54.ABS:绝对值
55.CEIL:返回大于或等于value的最小整数
56.FLOOR:返回小于或等于value的最大整数
57.SQRT :返回value的平方根 负数无意义。
58.//四舍五入 结果为46
59.SQL> select round(45.56) from dual;
60.//绝对值 结果为45.56
61.SQL> select abs(-45.56) from dual;
62.//大于等于最小整数 结果为-45
63.SQL> select ceil(-45.56) from dual;
64.//小于等于最大整数 结果为-46
65.SQL> select floor(-45.56) from dual;
66.//求余数 结果为300
67.SQL> select mod(1800,500) from dual
68.//截取的数的操作数是正数的情况下:只操作小数位 结果为1800.11
69.SQL> select trunc(1800.11111,2) from dual;
70.//截取的数的操作数是负数的情况下:操作的是小数点之前的位,把操作位小数点之前的位数全部改写成0. 结果就是1000
71.SQL> select trunc(1899.11111,-3) from dual;
72.
73.
74.
75.
76.3、日期时间函数
77.Oracle 中的日期型数据实际含有两个值: 日期和时间。默认的日期格式是 DD-MON-RR.日期时间函数用来返回当前系统的日期和时间、以及对日期和时间类型的数据进行处理运算。
78.
79.add_months(date,count);在指定的日期上增加count个月
80.last_day(date);返回日期date所在月的最后一天
81.months_between(date1,dates);返回date1到date2之间间隔多少个月
82.new_time(date,this’,’other’);将时间date从this时区转换成other时区
83.next_day(day,’day’);返回指定日期或最后一的第一个星期几的日期,这里day为星期几
84.sysdate();获取系统的当前日期
85.current_timestamp();获取当前的时间和日期值
86.round:日期的四舍五入
87.trunc 日期的截取
88.
89.4、日期的数学运算:
90.l 在日期上加上或减去一个数字结果仍为日期。
91.l 两个日期相减返回日期之间相差的天数。
92.l 可以用数字除24来向日期中加上或减去小时。
93.
94.//获取系统的当前时间 显示的格式采用默认格式 显示结果:07-4月 -1111.15.38.390000 上午 +08:00
95.SQL> selectcurrent_timestamp from dual;
96.//获取系统的当前日期值 显示结果:2011-4-7 11
97.SQL> select sysdatefrom dual;
98.//为当前日期加上3个月 显示的结果:2011-7-7 11:18:36
99.selectadd_months(sysdate,3) from dual;
100. //返回当前月的最后一天 显示的结果:2011-4-30 11:19:4
101. selectlast_day(sysdate) from dual;
102. //返回两个日期之间的间隔月是几: 结果为:4
103. SQL> selectmonths_between(add_months(sysdate,4),sysdate) from dual;
104. //从GMT时区转换成AST时区的日期结果
105. SQL> selectnew_time(sysdate,'GMT','AST') from dual;
106. //返回下一个星期一的日期值 必须写成星期’几’
107. SQL> selectnext_day(sysdate,'星期一') from dual;
108.
109.
110.
111.
112. 5、转换函数:
113. 隐式转换:在运算过程中由系统自动完成的
114. 显式转换:在运算过程中需要调用相应的转换函数实现。
115. 隐式转换
116. 转换前数据类型 转换后数据类型
117. Varchar2 or charnumber
118. Varchar2 or char date
119. number Varchar2
120. date Varchar2
121.
122. 显式转换
123. to_char(date,’format’):按照指定的格式format把数字或日期类型的数据转换成字符串
124.
125. 格式:
126. 必须包含在单引号中而且大小写敏感。
127. 可以包含任意的有效的日期格式。
128. 日期之间用逗号隔开。
129. 日期格式如下:
130. 格式 举例
131. YYYY 2011
132. YEAR TWO THORUSAND ANDFOUR
133. MM 01
134. MONTH JULY
135. MON JUL
136. DY MON
137. DAY MONDAY
138. DD 03
139.
140. //把当前日期转换成YYYY/MM/DD的格式
141. SQL> selectto_char(current_timestamp,'YYYY/MM/DD') from dual;
142. //把当前日期转换成YYYY/MM/DD HH24/MI/SSAM的格式
143. SQL> selectto_char(current_timestamp,'YYYY/MM/DD HH24/MI/SS AM') from dual;
144. //DD “of” MONTH
145. SQL> selectto_char(current_timestamp,'YYYY DD "of" MONTH HH/MI/SS AM') fromdual;
146. //把当数字按照$99,999这种方式返回字符串 并且操作数的位数不能够大于5(即$后边的位数)位,否则话结果会是########
147. SQL> select to_char(11111,'$99,999') from dual;
148.
149. to_number(char);把包含了数字格式的字符串转换成数字数据
150. to_date(string,’format’);按照指定格式的format把字符串转换成日期数据,如果省略了foramt格式,那么就采用默认的日期格式(DD-MON-YY);
151.
152. //把当前字符串转换成日期
153. SQL> selectto_date('2011-02-08','YYYY-MM-DD') from dual;
154. //求出两个日期之间相差的天数
155. SQL> selectto_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD') fromdual;
156. //求出两个日期之间相差的周次
157. SQL> select(to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7 from dual;
158. //对周次进行向上取整
159. SQL> selectceil((to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7) from dual;
160.
161. chartorowid(char);把字符串转换成rowid类型
162. rowidtochar(x);把rowid类型转换成字符类型数据
163.
164. 6、通用函数
165. 这些函数适用于任何数据类型,同时也适用于空值:
166. NVL (expr1, expr2)
167. NVL2 (expr1, expr2,expr3)
168. NULLIF (expr1, expr2)
169. COALESCE (expr1,expr2, ..., exprn)
170.
171. nvl()
172. 将空值转换成一个已知的值:
173. l 可以使用的数据类型有日期、字符、数字。
174. l 函数的一般形式:
175. • NVL(commission_pct,0)
176. •NVL(hire_date,'01-JAN-97')
177. • NVL(job_id,'No JobYet')
178. //将comm为null替换成0
179. SQL> selectempno,ename,job,mgr,hiredate,sal,nvl(comm,0),deptno from emp;
180. //在上面的基础上将日期为空替换成给定的日期
181. SQL>selectempno,ename,job,mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptno from emp;
182. //在上面的基础上将job为空替换成redarmy
183. SQL> selectempno,ename,nvl(job,'redarmy'),mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptno from emp;
184. //计算出员工在该月的工资=(薪资+奖金)
185. SQL> selectempno,ename,job,mgr,hiredate,(nvl(sal,0)+nvl(comm,0)) as "工资",deptno from emp;
186.
187. NVL2 (expr1, expr2,expr3) : expr1不为NULL,返回expr2;为NULL,返回expr3。
188. 相当于:expr1!=null?exrp2:expr3;
189. //计算员工的工资
190. SQL> selectempno,ename,job,mgr,hiredate,nvl2(comm,sal+comm,sal) as "工资",deptno fromemp;
191.
192. NULLIF (expr1, expr2): 相等返回NULL,不等返回expr1
193. //注意观察理解
194. SQL> select enameas "expr1",job as "expr2",nullif(length(ename),length(job))from emp;
195.
196. l COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
197. l 如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。
198. SQL> selectempno,ename,job,mgr,hiredate,coalesce(comm,sal,10000) as "salll", deptno from emp;
199.
200. l 重点理解:单行函数可以嵌套。嵌套函数的执行顺序是由内到外。
201.
202. 条件表达式 :IF-THEN-ELSE 逻辑
203. CASE expr WHENcomparison_expr1 THEN return_expr1
204. [WHEN comparison_expr2THEN return_expr2
205. WHEN comparison_exprnTHEN return_exprn
206. ELSE else_expr]
207. END
208.
209.
210. DECODE(col|expression,search1, result1
211. [, search2,result2,...,]
212. [, default])
213.
214. //为职位是Manager的员工 发放5000元的奖金
215. SQL> selectename,job,case job when 'MANAGER' then nvl(sal,0)+5000 end as "工资" from emp;
216.
217. //员工的工资
218. SQL> select ename,job,
219. 2 case job when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)
220. 3 else nvl(sal,0)+nvl(comm,0)
221. 4 end
222. 5 from emp;
223. //改写成 decode的写法
224. SQL> selectename,job
225. 2 ,decode(job,'MANAGER', nvl(sal,0)+5000+nvl(comm,0),
226. 3 'CLERK',nvl(sal,0)+nvl(comm,0)+200,
227. 4 nvl(sal,0)+nvl(comm,0)) as "工资"
228. 5 from emp;