Oracle-day02 下

发布时间：2020-12-12 13:35:34 所属栏目：百科来源：网络整理

导读：五、单行函数（一）字符函数常用字符函数讲解：（1）求字符串长度 LENGTH 语句： select length(‘ABCD‘) from dual; 显示结果为：（2）求字符串的子串 SUBSTR 语句： select substr(‘ABCD‘,2,2) from dual; 显示结果为：（3）字符串拼接 CONCAT 语

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

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

select length(‘ABCD‘) from dual;

显示结果为：

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

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

显示结果为：

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

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

查询结果如下：

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

select ‘ABC‘||‘D‘ from dual;

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

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

select round(100.567) from dual

查询结果如下：

语句：

select round(100.567,2) from dual

查询结果如下：

（2）截取函数 TRUNC
语句：

select trunc(100.567) from dual

查询结果：

语句：

select trunc(100.567,2) from dual

（3）取模 MOD
语句：

select mod(10,3) from dual

结果：

（三）日期函数

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

select sysdate from dual

查询结果如下：

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

select add_months(sysdate,2) from dual

查询结果如下：

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

select last_day(sysdate) from dual

查询结果如下：

（3）日期截取 TRUNC
语句：

select TRUNC(sysdate) from dual

查询结果如下：

语句：

select TRUNC(sysdate,‘yyyy‘) from dual

查询结果如下：

语句：

select TRUNC(sysdate,‘mm‘) from dual

查询结果如下：

（四）转换函数

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

select TO_CHAR(1024) from dual

查询结果：

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

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

查询结果：

语句：

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

查询结果：

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

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

查询结果如下：

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

select to_number(‘100‘) from dual

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

select NVL(NULL,0) from dual

查询结果如下：

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

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

查询结果：

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

语句：

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

（3）条件取值 decode
语法：

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

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

语句：

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

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

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

还有另外一种写法：

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 字段进行排序，相同的值排名相同，排名跳跃
语句：

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

结果：

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

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

结果：

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

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

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

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 不去掉重复记录

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

结果如下：

UNION 去掉重复记录

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

（三）交集运算

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

结果：

（四）差集运算

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

结果:

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

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
如图:

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!