【郑州校区】Oracle-day02 下

五、单行函数
（一）字符函数


常用字符函数讲解：
（1）求字符串长度 LENGTH
语句：

[AppleScript] 纯文本查看 复制代码

select length('ABCD') from dual;

显示结果为：

（2）求字符串的子串 SUBSTR
语句：

[AppleScript] 纯文本查看 复制代码

select substr('ABCD',2,2) from dual;

显示结果为：

（3）字符串拼接 CONCAT
语句：

[AppleScript] 纯文本查看 复制代码

select concat('ABC','D') from dual;

查询结果如下：



我们也可以用|| 对字符串进行拼接

[AppleScript] 纯文本查看 复制代码

select 'ABC'||'D' from dual;

查询结果同上。
（二）数值函数


常用数值函数讲解：
（1）四舍五入函数 ROUND
语句：

[AppleScript] 纯文本查看 复制代码

select round(100.567) from dual

查询结果如下：

语句：

[AppleScript] 纯文本查看 复制代码

select round(100.567,2) from dual

查询结果如下：

（2）截取函数 TRUNC
语句：

[AppleScript] 纯文本查看 复制代码

select trunc(100.567) from dual

查询结果：

语句：

[AppleScript] 纯文本查看 复制代码

select trunc(100.567,2) from dual

（3）取模 MOD
语句：

[AppleScript] 纯文本查看 复制代码

select mod(10,3) from dual

结果：

（三）日期函数


我们用 sysdate 这个系统变量来获取当前日期和时间
语句如下：

[AppleScript] 纯文本查看 复制代码

select sysdate from dual

查询结果如下：

常用日期函数讲解：
（1）加月函数 ADD_MONTHS ：在当前日期基础上加指定的月
语句：

[AppleScript] 纯文本查看 复制代码

select add_months(sysdate,2) from dual

查询结果如下：

（2）求所在月最后一天 LAST_DAY
语句：

[AppleScript] 纯文本查看 复制代码

select last_day(sysdate) from dual

查询结果如下：

（3）日期截取 TRUNC
语句：

[AppleScript] 纯文本查看 复制代码

select TRUNC(sysdate) from dual

查询结果如下：

语句：

[AppleScript] 纯文本查看 复制代码

select TRUNC(sysdate,'yyyy') from dual

查询结果如下：

语句：

[AppleScript] 纯文本查看 复制代码

select TRUNC(sysdate,'mm') from dual

查询结果如下：

（四）转换函数


常用转换函数讲解：
（1）数字转字符串 TO_CHAR
语句：

[AppleScript] 纯文本查看 复制代码

select TO_CHAR(1024) from dual

查询结果：

（2）日期转字符串 TO_CHAR
语句：

[AppleScript] 纯文本查看 复制代码

select TO_CHAR(sysdate,'yyyy-mm-dd') from dual

查询结果：

语句：

[AppleScript] 纯文本查看 复制代码

select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

查询结果：



（3）字符串转日期 TO_DATE
语句：

[AppleScript] 纯文本查看 复制代码

select TO_DATE('2017-01-01','yyyy-mm-dd') from dual

查询结果如下：

（4）字符串转数字 TO_NUMBER
语句：

[AppleScript] 纯文本查看 复制代码

select to_number('100') from dual

（五）其它函数
（1）空值处理函数 NVL
用法：
NVL（检测的值，如果为 null 的值）；
语句：

[AppleScript] 纯文本查看 复制代码

select NVL(NULL,0) from dual

查询结果如下：

需求：
显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
语句：

[AppleScript] 纯文本查看 复制代码

select PRICE,MINNUM,NVL(MAXNUM,9999999)
from T_PRICETABLE where OWNERTYPEID=1

查询结果：

（2）空值处理函数 NVL2
用法：
NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；
需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不
限”.

语句：

[AppleScript] 纯文本查看 复制代码

select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')
from T_PRICETABLE where OWNERTYPEID=1

（3）条件取值 decode
语法：

[AppleScript] 纯文本查看 复制代码

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值
需求：显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）




语句：

[AppleScript] 纯文本查看 复制代码

select name,decode( ownertypeid,1,' 居 民 ',2,' 行 政 事 业 单 位
',3,'商业') as 类型
from T_OWNERS

上边的语句也可以用 case when then 语句来实现

[AppleScript] 纯文本查看 复制代码

select name ,(case ownertypeid
when 1 then '居民'
when 2 then '行政事业单位'
when 3 then '商业'
else '其它'
end
) from T_OWNERS

还有另外一种写法：

[AppleScript] 纯文本查看 复制代码

select name,(case
when ownertypeid= 1 then '居民'
when ownertypeid= 2 then '行政事业'
when ownertypeid= 3 then '商业'
end )
from T_OWNERS

六、行列转换
需求：按月份统计 2012 年各个地区的水费，如下图


需求：按季度统计 2012 年各个地区的水费，如下图




语句如下：

七、分析函数
以下三个分析函数可以用于排名使用。
下图为三种排名方式的举例

（1） RANK
相同的值排名相同，排名跳跃
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃
语句：

[AppleScript] 纯文本查看 复制代码

select rank() over(order by usenum desc ),usenum from
T_ACCOUNT

结果：

（2） DENSE_RANK 相同的值排名相同，排名连续
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续
语句：

[AppleScript] 纯文本查看 复制代码

select dense_rank() over(order by usenum desc ),usenum
from T_ACCOUNT

结果：

（3） ROW_NUMBER
返回连续的排名，无论值是否相等
需求：对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是
否相等
语句：

[AppleScript] 纯文本查看 复制代码

select row_number() over(order by usenum desc ),usenum
from T_ACCOUNT

用 row_number()分析函数实现的分页查询相对三层嵌套子查询要简单的多:

[AppleScript] 纯文本查看 复制代码

select * from
(select row_number() over(order by usenum desc )
rownumber,usenum from T_ACCOUNT)
where rownumber>10 and rownumber<=20

查询结果如下：

八、集合运算
（一）什么是集合运算
集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算
包括：
··UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
··UNION(并集)，返回各个查询的所有记录，不包括重复记录。
··INTERSECT(交集)，返回两个查询共有的记录。
··MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之
后剩余的记录。


（二）并集运算

UNION ALL 不去掉重复记录

[AppleScript] 纯文本查看 复制代码

select * from t_owners where id<=7
union all
select * from t_owners where id>=5

结果如下：

UNION 去掉重复记录

[AppleScript] 纯文本查看 复制代码

select * from t_owners where id<=7
union
select * from t_owners where id>=5

（三）交集运算

[AppleScript] 纯文本查看 复制代码

select * from t_owners where id<=7
intersect
select * from t_owners where id>=5

结果：

（四）差集运算

[AppleScript] 纯文本查看 复制代码

select * from t_owners where id<=7
minus
select * from t_owners where id>=5

结果:

如果我们用 minus 运算符来实现分页，语句如下：

[AppleScript] 纯文本查看 复制代码

select rownum,t.* from T_ACCOUNT t where rownum<=20
minus
select rownum,t.* from T_ACCOUNT t where rownum<=10

九、总结
（一）知识点总结
（二）上机任务布置
为《自来水收费系统》开发统计模块相关的功能
1.收费日报单（总）
统计某日的收费，按区域分组汇总，效果如下：


2.收费日报单（收费员）
统计某收费员某日的收费，按区域分组汇总，效果如下：

3.收费月报表（总）
统计某年某月的收费记录，按区域分组汇总

4.收费月报表（收费员）
统计某收费员某年某月的收费记录，按区域分组汇总
5.收费年报表（分区域统计）
统计某年收费情况，按区域分组汇总，效果如下：

6.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下

7.收费年报表（分月份统计）
统计某年收费情况，按月份分组汇总，效果如下

8.统计用水量，收费金额（分类型统计）
根据业主类型分别统计每种居民的用水量（整数，四舍五入）及收费金额 ，如
果该类型在台账表中无数据也需要列出值为 0 的记录 ， 效果如下：

分析：这里所用到的知识点包括左外连接、sum()、分组 group by 、round() 和
nvl()
9.统计每个区域的业主户数，并列出合计

10.统计每个区域的业主户数，如果该区域没有业主户数也要列出 0
如图:

更多

【郑州校区】Oracle-day01 上

【郑州校区】Oracle-day01 中

【郑州校区】Oracle-day01 下

【郑州校区】Oracle-day02 上

【郑州校区】Oracle-day02 中

传智播客·黑马程序员郑州校区地址

河南省郑州市高新区长椿路11号大学科技园（西区）东门8号楼三层

联系电话0371-56061160 / 61/62

来校路线地铁一号线梧桐街站A口出

我来占层楼啊