Oracle数据库

mrcsunflower

已于 2023-04-18 21:13:14 修改

阅读量1k

点赞数 1

分类专栏： MySQL 文章标签：数据库 oracle

于 2023-04-18 21:10:29 首次发布

本文链接：https://blog.csdn.net/qq_45134488/article/details/130231310

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

基础

数据库分类：

关系型数据库Relational Database
1. Oracle
2. MySQL
3. SQL Server
4. PostgreSQL
5. DB2
6. SQLite
文档型数据库Document Database
1. MongoDB
2. Couchbase
3. Firebase
4. CouchDB
键值数据库Key-value Database
1. Redis
2. Memcached
搜索引擎Search Engine
1. Elasticsearch
2. Splunk
宽列数据库Wide Column
1. Cassandra
2. HBase
图形数据库Graph
1. Neo4j
时序数据库Time Series
1. InfluxDB

建立数据库的六个步骤：

需求分析
设计概念结构
逻辑结构设计（E-R图）
实体设计
执行数据库
数据库的运作和维护

创建表

格式：

CREATE TABLE 表名称(
	字段名 类型(长度)
)

某自来水公司数据库为例

--建立价格区间表
create  table t_pricetable
(
    id number primary key,
    price number(10,2),
    ownertypeid number,
    minnum number,
    maxnum number
);


--业主类型
create table t_ownertype
(
    id number primary key,
    name varchar2(30)
);

--业主表
create table t_owners
(
    id number primary key,
    name varchar2(30),
    addressid number,
    housenumber varchar2(30),
    watermeter varchar2(30),
    adddate date,
    ownertypeid number
);



--区域表
create table t_area
(
    id number,
    name varchar2(30)
);

--收费员表
create table t_operator
(
    id number,
    name varchar2(30)
);


--地址表
create table t_address
(
    id number primary key,
    name varchar2(100),
    areaid number,
    operatorid number
);


--账务表--
create table t_account 
(
    id number primary key,
    owneruuid number,
    ownertype number,
    areaid number,
    year char(4),
    month char(2),
    num0 number,
    num1 number,
    usenum number,
    meteruser number,
    meterdate date,
    money number(10,2),
    isfee char(1),
    feedate date,
    feeuser number
);


create sequence seq_account;

--业主类型
insert into t_ownertype values(1,'居民');
insert into t_ownertype values(2,'行政事业单位');
insert into t_ownertype values(3,'商业');

--地址信息--
insert into t_address values( 1,'明兴花园',1,1);
insert into t_address values( 2,'鑫源秋墅',1,1);
insert into t_address values( 3,'华龙苑南里小区',2,2);
insert into t_address values( 4,'河畔花园',2,2);
insert into t_address values( 5,'霍营',2,2);
insert into t_address values( 6,'回龙观东大街',3,2);
insert into t_address values( 7,'西二旗',3,2);

--业主信息
insert into t_owners values(1,'范冰',1,'1-1','30406',to_date('2015-04-12','yyyy-MM-dd'),1 );
insert into t_owners values(2,'王强',1,'1-2','30407',to_date('2015-02-14','yyyy-MM-dd'),1 );
insert into t_owners values(3,'马腾',1,'1-3','30408',to_date('2015-03-18','yyyy-MM-dd'),1 );
insert into t_owners values(4,'林小玲',2,'2-4','30409',to_date('2015-06-15','yyyy-MM-dd'),1 );
insert into t_owners values(5,'刘华',2,'2-5','30410',to_date('2013-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(6,'刘东',2,'2-2','30411',to_date('2014-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(7,'周健',3,'2-5','30433',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(8,'张哲',4,'2-2','30455',to_date('2016-09-11','yyyy-MM-dd'),1 );
insert into t_owners values(9,'昌平区中西医结合医院',5,'2-2','30422',to_date('2016-10-11','yyyy-MM-dd'),2 );
insert into t_owners values(10,'美廉美超市',5,'4-2','30423',to_date('2016-10-12','yyyy-MM-dd'),3 );


--操作员
insert into t_operator values(1,'马小云');
insert into t_operator values(2,'李翠花');



--地区--
insert into t_area values(1,'海淀');
insert into t_area values(2,'昌平');
insert into t_area values(3,'西城');
insert into t_area values(4,'东城');
insert into t_area values(5,'朝阳');
insert into t_area values(6,'玄武');


--价格表--

insert into t_pricetable values(1,2.45,1,0,5);
insert into t_pricetable values(2,3.45,1,5,10);
insert into t_pricetable values(3,4.45,1,10,null);

insert into t_pricetable values(4,3.87,2,0,5);
insert into t_pricetable values(5,4.87,2,5,10);
insert into t_pricetable values(6,5.87,2,10,null);

insert into t_pricetable values(7,4.36,3,0,5);
insert into t_pricetable values(8,5.36,3,5,10);
insert into t_pricetable values(9,6.36,3,10,null);

--账务表--
insert into t_account values( seq_account.nextval,1,1,1,'2012','01',30203,50123,0,1,sysdate,34.51,'1',to_date('2012-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','02',50123,60303,0,1,sysdate,23.43,'1',to_date('2012-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','03',60303,74111,0,1,sysdate,45.34,'1',to_date('2012-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','04',74111,77012,0,1,sysdate,52.54,'1',to_date('2012-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','05',77012,79031,0,1,sysdate,54.66,'1',to_date('2012-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','06',79031,80201,0,1,sysdate,76.45,'1',to_date('2012-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','07',80201,88331,0,1,sysdate,65.65,'1',to_date('2012-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','08',88331,89123,0,1,sysdate,55.67,'1',to_date('2012-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','09',89123,90122,0,1,sysdate,112.54,'1',to_date('2012-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','10',90122,93911,0,1,sysdate,76.21,'1',to_date('2012-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','11',93911,95012,0,1,sysdate,76.25,'1',to_date('2012-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,1,1,1,'2012','12',95012,99081,0,1,sysdate,44.51,'1',to_date('2013-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,2,1,3,'2012','01',30334,50433,0,1,sysdate,34.51,'1',to_date('2013-02-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','02',50433,60765,0,1,sysdate,23.43,'1',to_date('2013-03-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','03',60765,74155,0,1,sysdate,45.34,'1',to_date('2013-04-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','04',74155,77099,0,1,sysdate,52.54,'1',to_date('2013-05-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','05',77099,79076,0,1,sysdate,54.66,'1',to_date('2013-06-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','06',79076,80287,0,1,sysdate,76.45,'1',to_date('2013-07-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','07',80287,88432,0,1,sysdate,65.65,'1',to_date('2013-08-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','08',88432,89765,0,1,sysdate,55.67,'1',to_date('2013-09-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','09',89765,90567,0,1,sysdate,112.54,'1',to_date('2013-10-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','10',90567,93932,0,1,sysdate,76.21,'1',to_date('2013-11-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','11',93932,95076,0,1,sysdate,76.25,'1',to_date('2013-12-14','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,2,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-14','yyyy-MM-dd'),2 );

insert into t_account values( seq_account.nextval,100,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2014-01-01','yyyy-MM-dd'),2 );
insert into t_account values( seq_account.nextval,101,1,3,'2012','12',95076,99324,0,1,sysdate,44.51,'1',to_date('2015-01-01','yyyy-MM-dd'),2 );

update t_account set usenum=num1-num0;
update t_account set money=usenum*2.45;
commit;

举例：

CREATE TABLE T_USER(
	ID NUMBER PRIMARY KEY, --设为主键
    NAME VARCHAR2(20),
    GENDER VARCHAR2(10),
    ADDRESS VARCHAR2(20)
)

以下操作以 T_USER表为例。

一、插入语句

插入单条

--不指定列名，依次填写所有对应的字段值
insert into T_USER values (1,'张三','男','四川成都');
commit;

--指定列名，可以只给部分字段添加值
insert into T_USER (ID,NAME,ADDRESS) values (2,'李四','陕西西安');
commit;

插入多条（批量插入）

insert all
  into T_USER values (3,'王五','男','北京')
  into T_USER values (4,'唐玄奘','男','长安')
  into T_USER values (5,'孙悟空','男','花果山')
select * from dual;
commit;

insert all
  into T_USER (ID,NAME,ADDRESS) values (6,'猪八戒','高老庄')
  into T_USER (ID,NAME,ADDRESS) values (7,'沙悟净','黑沙河')
  into T_USER (ID,NAME,ADDRESS) values (8,'白龙马','不知道')
select * from dual;
commit;

二、修改语句

update T_USER set ADDRESS='西海龙宫' where id=8;
commit;

三、删除语句

delete from T_USER where id=1;
commit;

truncate table T_OWNERTYPE;

truncate 比 delete 执行效率更高，truncate 是先摧毁整个表，再创建一个新的空表。

四、单表查询

以下操作以某自来水公司的数据库为例

（一）简单条件查询

1.精确查询

--完整显示
select * from T_OWNERS where watermeter = '30408';
--显示指定字段
select id,name from T_OWNERS where watermeter = '30408';

2.模糊查询

select * from T_OWNERS where name like '%刘%';

3.and运算符

select * from T_OWNERS where name like '%刘%' and housenumber like '%5%';

4.or运算符

select * from T_OWNERS where name like '%刘%' or housenumber like '%5%';

5.and与or运算符混合使用

select * from T_OWNERS where (name like '%刘%' or housenumber like '%5%') and addressid = 3;

6.范围查询

select * from T_ACCOUNT where usenum >= 10000 and usenum <=20000;
select * from T_ACCOUNT where usenum between 10000 and 20000;

7.空值查询

select * from T_PRICETABLE where maxnum is null;

8.非空查询

select * from T_PRICETABLE where maxnum is not null;

（二）去掉重复记录

不重复显示

select DISTINCT addressid from T_OWNERS;
select DISTINCT addressid,ownertypeid from T_OWNERS;

（三）排序查询

1.升序排序

select * from T_ACCOUNT order by usenum ASC;
select * from T_ACCOUNT order by usenum;  --ASC可省略

2.降序排序

select * from T_ACCOUNT order by usenum DESC;

（四）基于伪列的查询

1.ROWID

select rowid,t.* from T_OWNERS t;
select rowid,id,name,addressid,watermeter from T_OWNERS;
select rowid,t.* from T_OWNERS t where rowid = 'AAAM1cAAGAAAABcAAE';

2.ROWNUM

select rownum,t.* from T_OWNERS T;
select rownum,t.* from T_OWNERS T where id > 3;

（五）聚合统计

1.聚合函数

（1）求和 sum

select sum(usenum) from T_ACCOUNT;
select sum(usenum) from T_ACCOUNT where year='2012';

（2）求平均 avg

select avg(usenum) from T_ACCOUNT;
select avg(usenum) from T_ACCOUNT where year='2012';

（3）求最大值 max

select max(usenum) from T_ACCOUNT;
select max(usenum) from T_ACCOUNT where year='2012';

（4）求最小值 min

select min(usenum) from T_ACCOUNT;
select min(usenum) from T_ACCOUNT where year='2012';

（5）统计记录个数

select count(*) from t_owners where ownertypeid = 1;

2.分组聚合 Group by

分组聚合统计（ select 后一定是分组聚合的条件或者是聚合函数）

select sum(money) from t_account;
select sum(money) from t_account group by areaid;
select areaid,sum(money) from t_account group by areaid;
select areaid,year,sum(money) from t_account group by areaid,year;
select year,areaid,sum(money) from t_account group by areaid,year;

3.分组后条件查询 having

--查询水费合计大于16900的区域及水费合计
select areaid,sum(money) from t_account group by areaid having sum(money) > 169000;

五、多表查询（连接查询）

内连接查询

（1）需求：查询显示业主编号，业主名称，业主类型名称

select * from t_owners;
select * from t_ownertype;
select * from t_owners ow,t_ownertype ot where ow.ownertypeid = ot.id;

select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 from t_owners ow,t_ownertype ot where ow.ownertypeid = ot.id;

（2）查询显示业主编号，业主名称、地址和业主类型

此查询需要三表关联查询。分别是业主表，业主分类表和地址表

select ow.id 业主编号,ow.name 业主名称, ad.name 地址,ot.name 业主类型 from 
t_owners ow,t_ownertype ot,t_address ad 
where ow.ownertypeid = ot.id and ow.addressid = ad.id;

（3）查询显示业主编号、业主名称、地址、所属区域、业主分类

这里需要四个表关联查询

select ow.id 业主编号,ow.name 业主名称, ad.name 地址,ar.name 区域,ot.name 业主类型 from 
t_owners ow,t_ownertype ot,t_address ad,t_area ar 
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id;

（4）查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类

需要五个表关联查询

select ow.id 业主编号,ow.name 业主名称,ad.name 地址,ar.name 区域,op.name 收费员,ot.name 业主类型 
from t_owners ow,t_ownertype ot,t_address ad,t_area ar,t_operator op
where ow.ownertypeid = ot.id and ow.addressid = ad.id and ad.areaid = ar.id and ad.operatorid = op.id;

左外连接查询

查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名

我们要查询这个结果，需要用到 T_OWNERS（业主表），T_ACCOUNT（台账表）按照查询结果，业主表为左表、账务表为右表

--内连接
select ow.id,ow.name,ac.year,ac.month,money from t_owners ow,t_account ac
where ow.id = ac.owneruuid;
--SQL1999
select ow.id,ow.name,ac.year,ac.month,money from t_owners ow left join t_account ac
on ow.id = ac.owneruuid;
--orcale语法
select ow.id,ow.name,ac.year,ac.month,money from t_owners ow,t_account ac
where ow.id = ac.owneruuid(+);

右外连接查询

查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录

--SQL1999
select ow.id,ow.name,ac.year,ac.month,money from t_owners ow right join t_account ac
on ow.id = ac.owneruuid;
--orcale语法
select ow.id,ow.name,ac.year,ac.month,money from t_owners ow,t_account ac
where ow.id(+) = ac.owneruuid;

六、子查询

（一）where子句中的子查询

（1）单行子查询

查询 2012 年 1 月用水量大于平均值的台账记录

--查询2012年1月用水量的平均值
select avg(usenum) from t_account where year = '2012' and month = '01';
--查询2012年1月用水量大于20009.5
select * from t_account where year = '2012' and month = '01' and usenum > 20009.5;

select * from t_account where year = '2012' and month = '01' 
and usenum > (select avg(usenum) from t_account where year = '2012' and month = '01');

（2）多行子查询

查询地址编号为 1 、3、4 的业主记录

select * from t_owners where addressid=1 or addressid=3 or addressid=4;

如果我们用 or 运算符编写，SQL 非常繁琐，所以我们用 in 来进行查询

select * from T_OWNERS
where addressid in ( 1,3,4 )

查询地址含有“花园”的业主的信息

--查询地址含有“花园”的编号
select id from t_address where name like '%花园%';
--查询地址编号含有1，4的业主信息
select * from t_owners where addressid in (1,4);

select * from t_owners where addressid in (select id from t_address where name like '%花园%');

查询地址不含有“花园”的业主的信息

select * from t_owners where addressid not in (select id from t_address where name like '%花园%');

（二）from 子句中的子查询

from 子句的子查询为多行子查询

查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”

select * from (
    select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 
    from t_owners ow,t_ownertype ot where ow.ownertypeid=ot.id   
) where 业主类型='居民';

（三）select 子句中的子查询

select 子句的子查询必须为单行子查询

列出业主信息，包括 ID，名称，所属地址

select id,name,(select name from t_address where id=addressid) addressname from t_owners;

列出业主信息，包括 ID，名称，所属地址，所属区域

select id,name,
    (select name from t_address where id=addressid) addressname,
    (select (select name from t_area where id=areaid) from t_address where id=addressid) areaname 
from t_owners;

七、分页查询

（一）简单分页

分页查询台账表 T_ACCOUNT，每页 10 条记录

--我们在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询
--我们首先显示前 10 条记录
select rownum,t.* from T_ACCOUNT t where rownum<=10

显示第 11 条到第 20 条的记录

select rownum,t.* from T_ACCOUNT t where rownum>10 and rownum<=20  --没有结果
--因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行
--使用子查询来实现
select * from
	(select rownum r,t.* from T_ACCOUNT t where rownum<=20)
where r>10

（二）基于排序的分页

分页查询台账表 T_ACCOUNT，每页 10 条记录，按使用字数降序排序。我们查询第 2 页数据，基于上边的语句添加排序

select * from
    (select rownum r,t.* from T_ACCOUNT t where rownum<=20 order
    by usenum desc)
where r>10

--刚才的语句是错误的
select rownum r,t.* from T_ACCOUNT t where rownum<=20 order by usenum desc
--发现排序后的 R 是乱的。这是因为 ROWNUM 伪列的产生是在表记录扫描是产生的
--而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的

--只要再嵌套一层循环（一共三层），让结果先排序
--然后对排序后的结果再产生 R，这样就不会乱了
select * from
    (select rownum r,t.* from
        (select * from T_ACCOUNT order by usenum desc) t
    where rownum<=20 )
where r>10

八、单行函数

（一）字符函数

常用字符函数讲解：

（1）求字符串长度 LENGTH

select * from dual;
select length('ABCD') from dual;

（2）求字符串的子串 SUBSTR

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

（3）字符串拼接 CONCAT

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

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

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

（二）数值函数

常用数值函数讲解：

（1）四舍五入函数 ROUND

select round(100.456) from dual;
--保留两位小数
select round(100.456,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;
select mod(10,2) from dual;

（三）日期函数

用 sysdate 这个系统变量来获取当前日期和时间

select sysdate from dual

常用日期函数讲解：

（1）加月函数 ADD_MONTHS ：在当前日期基础上加指定的月

select add_months(sysdate,2) from dual;
select add_months(sysdate,-2) from dual;

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

select last_day(sysdate) from dual;
select last_day(sysdate-4) from dual;

（3）日期截取 TRUNC

select trunc(sysdate) from dual;

select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'yyyy') from dual;
select trunc(sysdate,'hh') from dual;
select trunc(sysdate,'mi') from dual

（四）转换函数

常用转换函数讲解：

（1）数字转字符串 TO_CHAR

select to_char(100) from dual;
select 100 from dual;

（2）日期转字符串 TO_CHAR

select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月'||to_char(sysdate,'dd')||'日' from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

（3）字符串转日期 TO_DATE

select to_date('2023-04-16','yyyy-mm-dd') from dual;

（4）字符串转数字 TO_NUMBER

select to_number(100) from dual;
select to_number(100)+10 from dual;
select '100'+10 from dual;
select 'a100'+10 from dual;

（五）其他函数

（1）空值处理函数 NVL

--NVL（检测的值，如果为 null 的值）；
select nvl(null,0) from dual;
select nvl(100,0) from dual;

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

select nvl(maxnum,9999999) from t_pricetable where ownertypeid=1;

（2）空值处理函数 NVL2

--NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；
select nvl2(20,0,100) from dual;

显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”

select nvl2(maxnum,to_char(maxnum),'不限') from t_pricetable where ownertypeid=1;

（3）条件取值 decode

decode(条件,值 1,翻译值 1,值 2,翻译值 2,…值 n,翻译值 n,缺省值)；【功能】根据条件返回相应值

select decode (400,1,2,400,4,400,5) from dual;
select decode (400,1,2,300,4,6) from dual;

显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）

select name, decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') from t_owners;

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

--case when then sql1999
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 '商业'
                  else '其他'
             end) from t_owners;

九、行列转换

按月份统计 2012 年各个地区的水费

select (select name from t_area where id=areaid) 区域,
       sum(case when month='01' then money else 0 end) 一月,
       sum(case when month='02' then money else 0 end) 二月,
       sum(case when month='03' then money else 0 end) 三月,
       sum(case when month='04' then money else 0 end) 四月,
       sum(case when month='05' then money else 0 end) 五月,
       sum(case when month='06' then money else 0 end) 六月,
       sum(case when month='07' then money else 0 end) 七月,
       sum(case when month='08' then money else 0 end) 八月,
       sum(case when month='09' then money else 0 end) 九月,
       sum(case when month='10' then money else 0 end) 十月,
       sum(case when month='11' then money else 0 end) 十一月,
       sum(case when month='12' then money else 0 end) 十二月
       from t_account where year='2012' group by areaid;

按季度统计 2012 年各个地区的水费

select (select name from t_area where id=areaid) 区域,
       sum(case when month>='01' and month<='03' then money else 0 end) 第一季度,
       sum(case when month>='04' and month<='06' then money else 0 end) 第二季度,
       sum(case when month>='07' and month<='09' then money else 0 end) 第三季度,
       sum(case when month>='10' and month<='12' then money else 0 end) 第四季度
       from t_account where year='2012' group by areaid;

十、分析函数

（1） RANK 相同的值排名相同，排名跳跃

对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名跳跃

--值相同，排名相同，序号跳跃
select rank() over(order by usenum desc) 排名,t.* from t_account t;

（2） DENSE_RANK 相同的值排名相同，排名连续

对 T_ACCOUNT 表的 usenum 字段进行排序，相同的值排名相同，排名连续

--值相同，排名相同，序号连续
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t;

（3） ROW_NUMBER 返回连续的排名，无论值是否相等

对 T_ACCOUNT 表的 usenum 字段进行排序，返回连续的排名，无论值是否相等

--序号连续，不管值是否相同
select row_number() over(order by usenum desc) 排名,t.* from t_account t;

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

--用分析函数来实现分页
select * from
       (select row_number() over(order by usenum desc) rownumber,t.* from t_account t)
       where rownumber<=20 and rownumber>=10;

十一、集合运算

（一）什么是集合运算

集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

UNION ALL(并集)，返回各个查询的所有记录，包括重复记录
UNION(并集)，返回各个查询的所有记录，不包括重复记录
INTERSECT(交集)，返回两个查询共有的记录
MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

（二）并集运算

--并集（去掉重复记录）
select * from t_owners where id>5
union
select * from t_owners where id<8
--并集（包括重复记录）
select * from t_owners where id>5
union all
select * from t_owners where id<8

（三）交集运算

--交集（两个结果集的重复部分）
select * from t_owners where id>5
intersect
select * from t_owners where id<8

（四）差集运算

select * from t_owners where id>5
minus
select * from t_owners where id<8

select * from t_owners where id<8
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.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

（二）创建或修改视图语法

CREATE [OR REPLACE] [FORCE] VIEW view_name
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY]

选项解释：

OR REPLACE ：若所创建的试图已经存在， ORACLE 自动重建该视图；
FORCE ：不管基表是否存在 ORACLE 都会自动创建该视图；
subquery ：一条完整的 SELECT 语句，可以在该语句中定义别名；
WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY ：该视图上不能进行任何 DML 操作。

（三）删除视图语法

DROP VIEW view_name

（四）案例

（1）简单视图的创建与使用

什么是简单视图？如果视图中的语句只是单表查询，并且没有聚合函数，我们就称之为简单视图

需求：创建视图：业主类型为 1 的业主信息

--创建简单视图
create view view_owners1 as
select * from t_owners where ownertypeid=1;

--查询简单视图
select * from view_owners1

select * from view_owners1 where addressid=1;
select count(*) from view_owners1 where addressid=1;

--修改视图数据
update view_owners1 set name='范小冰' where id=1;
commit;

select * from t_owners; --范小冰

（2）带检查约束的视图

需求：根据地址表（ T_ADDRESS）创建视图 VIEW_ADDRESS2 ,内容为区域 ID为 2 的记录

--带检查约束的视图
create view view_address1 as
select * from t_address where areaid=2
with check option

select * from view_address1

--修改失败，因为该视图的条件的是 areaid=2；其他字段可以修改
update view_address1 set areaid=3 where id=4;

（3）只读视图的创建与使用

如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视图时指定 WITH READ ONLY 选项，这样创建的视图就是一个只读视图

需求：将上边的视图修改为只读视图

--只读视图
--创建失败，该视图已经被创建
create view view_owners1 as
select * from t_owners where ownertypeid=1
with read only;

--or replace 自动重建该视图
create or replace view view_owners1 as
select * from t_owners where ownertypeid=1
with read only;

--修改视图数据  修改失败，这是只读视图
update view_owners1 set name='范大冰' where id=1;
commit;

（4）创建带错误的视图

--创建带错误的视图
--创建失败，表不存在
create view view_test as
select * from t_test;

--创建成功 force
create force view view_test as
select * from t_test;

（5）复杂视图的创建与使用

复杂视图，就是视图的 SQL 语句中，有聚合函数或多表关联查询

1.多表关联查询的例子

创建视图，查询显示业主编号，业主名称，业主类型名称

--复杂视图
--多表关联查询
--创建视图， 查询显示业主编号， 业主名称，业主类型名称
create or replace view view_owners as
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 
from t_owners ow,t_ownertype ot where ow.ownertypeid=ot.id;

--查询复杂视图（多表关联）
select * from view_owners;
select * from view_owners where 业主类型='居民';

--修改复杂视图（多表关联）的数据
update view_owners set 业主名称='林大玲' where 业主编号=4;

select * from view_owners; --林大玲
select * from t_owners; --林大玲

update view_owners set 业主类型='商业' where 业主编号=4; --错误
--键保留表：把主键保留下来的那个表

2.分组聚合统计查询

创建视图，按年月统计水费金额

--聚合统计的复杂视图
create view view_accountsum as
select year,month,sum(money) money 
from t_account 
group by year,month 
order by year,month;

select * from view_accountsum;

select * from view_accountsum where year='2012' and month='03';

--不能修改
update view_accountsum set money=10000 where year='2012' and month='03';
update view_accountsum set month='04' where year='2012' and month='03';

十三、物化视图

（一）什么是物化视图

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

（二）创建物化视图语法

CREATE METERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE]
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT
(next_time)
]
AS
subquery

BUILD IMMEDIATE 是在创建物化视图的时候就生成数据
BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。默认为 BUILD IMMEDIATE
刷新（ REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步
REFRESH 后跟着指定的刷新方法有三种：FAST、 COMPLETE、 FORCE。 FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。 COMPLETE 刷新对整个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。 FORCE 是默认的方式
刷新的模式有两种： ON DEMAND 和 ON COMMIT。 ON DEMAND 指需要手动刷新物化视图（默认）。 ON COMMIT 指在基表发生 COMMIT 操作时自动刷新

（三）案例

（1）创建手动刷新的物化视图

需求：查询地址 ID,地址名称和所属区域名称

--创建手动刷新的物化视图
--查询地址 ID,地址名称和所属区域名称
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar where ad.areaid=ar.id;

--删除物化视图
drop materialized view mv_address1;

--查询物化视图
select * from mv_address1;

--向基表插入数据
insert into t_address values (8,'西三旗',2,2);
commit;
insert into t_address values (9,'西四旗',2,2);
commit;

--查询基表数据
select * from t_address;

--执行下列语句进行刷新
begin
  DBMS_MVIEW.refresh('mv_address1','C');
end;

--或在命令窗口执行 connected windows  EXEC DBMS_MVIEW.refresh('mv_address1','C');

（2）创建自动刷新的物化视图

--创建自动刷新的物化视图  基表发生commit操作，自动刷新物化视图
--查询地址 ID,地址名称和所属区域名称
create materialized view mv_address2 
refresh 
on commit 
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar where ad.areaid=ar.id;

--向基表插入数据
insert into t_address values (10,'西五旗',2,2);
commit;

--查询基表数据
select * from t_address;

--查询物化视图
select * from mv_address2;

（3）创建时不生成数据的物化视图

--创建时不生成数据的物化视图
--查询地址 ID,地址名称和所属区域名称
create materialized view mv_address3 
build deferred
refresh 
on commit 
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar where ad.areaid=ar.id;

--查询物化视图
select * from mv_address3; --没有数据，可执行刷新语句刷新

--向基表插入数据
insert into t_address values (11,'西六旗',2,2);
commit;

--执行PLSQL语句进行刷新 第一次必须手动执行刷新
begin
  DBMS_MVIEW.refresh('mv_address3','C');
end;

select * from mv_address3;
insert into t_address values (12,'西七旗',2,2);
commit;

（4）创建增量刷新的物化视图

--前提是必须先创建物化视图日志：记录基表发生了哪些变化，用这些记录更新物化视图
create materialized view log on t_address with rowid;
create materialized view log on t_area with rowid;
--创建物化视图中的语句中，必须有基表的标识 rowid
create materialized view mv_address4 
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid, ad.id,ad.name,ar.name arname 
from t_address ad,t_area ar where ad.areaid=ar.id;

select * from mv_address4;

--向基表插入数据
insert into t_address values (13,'西八旗',2,2);
commit;
insert into t_address values (14,'西九旗',2,2);
commit;
--向基表删除数据
delete from t_address where id=13;
commit;
--手动刷新
begin
  DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

十四、序列

（一）什么是序列

序列是 ORACLE 提供的用于产生一系列唯一数字的数据库对象

（二）创建与使用简单序列

创建序列语法

create sequence 序列名称

通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值

--创建简单序列
create sequence seq_test;

--查询序列的下一个值
select seq_test.nextval from dual;
--查询序列的当前值
select seq_test.currval from dual;

（三）创建复杂序列

语法：

CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默
认是 1
[START WITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中

（四）案例

（1）有最大值的非循环序列

--有最大值的非循环序列
create sequence seq_test1
maxvalue 20;

select seq_test1.nextval from dual;

create sequence seq_test2
increment by 10
start with 10
maxvalue 100;

select seq_test2.nextval from dual;

create sequence seq_test3
increment by 10
start with 10
minvalue 5
maxvalue 100;

select seq_test3.nextval from dual;

（2）有最大值的循环序列

--循环序列
create sequence seq_test4
increment by 10
start with 10
minvalue 10
maxvalue 210
cycle;

select seq_test4.nextval from dual;

create sequence seq_test5
increment by 10
start with 10
minvalue 10
maxvalue 201
cycle;

select seq_test5.nextval from dual;

（3）带缓存的序列

create sequence seq_test6
increment by 10
start with 10
minvalue 10
maxvalue 100
cycle
cache 5;

select seq_test6.nextval from dual;

--一次缓存的数有多少 cache值*增长值
--一次循环的值不能小于 一次缓存的数

create sequence seq_test7
increment by 10
start with 10
minvalue 10
maxvalue 100
cache 20;

select seq_test7.nextval from dual;

（五）修改和删除序列

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 STARTWITH 参数

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列

DROP SEQUENCE 序列名称;

十五、同义词

（一）什么是同义词

同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

你可以创建公共同义词和私有同义词。其中，公共同义词属于 PUBLIC 特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。

同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。

（二）创建与使用同义词

创建同义词的具体语法

create [public] SYNONYM synooym for object;

synonym 表示要创建的同义词的名称， object 表示表，视图，序列等我们要创建同义词的对象的名称。

（三）案例

（1）私有同义词

--创建私有同义词
create synonym owner for t_owners;

select * from owner;

--跨用户访问
select * from wateruser.owner;
select * from wateruser.t_owners;

（2）公有同义词

--创建公有同义词
create public synonym owner2 for t_owners;

select * from owner2;

十六、索引

（一）什么是索引

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ ROWID）

（二）普通索引

语法：

create index 索引名称 on 表名(列名);

需求：我们经常要根据业主名称搜索业主信息，所以我们基于业主表的 name 字段来建立索引

--创建普通索引
create index index_owners_name on t_owners(name);

select * from t_owners where name='张哲';

索引性能测试

--性能测试 创建一个表
create table t_indextest(id number,name varchar2(30));

--向表中插入1000000条数据
begin
  for x in 1..1000000
  loop
    insert into t_indextest values(x,'A'||x);
  end loop;
  commit;
end;

--创建索引
create index index_test on t_indextest(name);

select * from t_indextest where id=765432;

select * from t_indextest where name='A765432';

select rowid,t.* from t_indextest t where name='A765432';

select * from t_indextest where rowid='AAAM2HAAGAAAAigAC6';

（三）唯一索引

如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可以创建唯一索引

语法

create unique index 索引名称 on 表名(列名);

需求：在业主表的水表编号一列创建唯一索引

--创建唯一索引
create unique index index_owners_meter on t_owners(watermeter);

（四）复合索引

我们经常要对某几列进行查询，比如，我们经常要根据学历和性别对学员进行搜索，如果我们对这两列建立两个索引，因为要查两棵树，查询性能不一定高。那如何建立索引呢？我们可以建立复合索引，也就是基于两个以上的列建立一个索引。

语法：

create index 索引名称 on 表名(列名,列名.....);

根据地址和门牌号对学员表创建索引

select * from t_owners where addressid=1 and housenumber='1-3';

--创建复合（组合）索引
create index index_owners_ah on t_owners(addressid,housenumber);

（五）反向键索引

应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

语法：

create index 索引名称 on 表名(列名) reverse;

（六）位图索引

使用场景：位图索引适合创建在低基数列上位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射
优点：减少响应时间，节省空间占用

语法：

create bitmap index 索引名称 on 表名(列名);

十七、oracle编程

（一）PL/SQL

（1）什么是PL/SQL

PL/SQL（ Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用

基本语法：

[declare
--声明变量
]
begin
--代码逻辑
[exception
--异常处理
]
end;

（2）变量

声明变量的语法：

变量名 类型（长度）；

变量赋值的语法：

变量名:=变量值

变量的声明

声明变量水费单价、水费字数、吨数、金额。对水费单价、字数、进行赋值。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额=单价*吨数。输出单价、数量和金额

--变量声明与赋值
declare
  v_price number(10,2);--单价
  v_usenum number;--水费字数
  v_usenum2 number(10,2);--吨数
  v_money number(10,2);--金额 
begin
  v_price:=2.45;--单价赋值
  v_usenum:=9213;--水费字数
  v_usenum2:=round(v_usenum/1000,2);--吨数
  v_money:=v_price*v_usenum2;--金额
  
  DBMS_OUTPUT.put_line('金额：'||v_money);
end;

Select into 方式赋值

语法结构

select 列名 into 变量名 from 表名 where 条件
--结果必须是一条记录 ，有多条记录和没有记录都会报错

declare
	v_price number(10,2);--单价
    v_usenum number;--水费字数
    v_num0 number;--上月字数
    v_num1 number;--本月字数
    v_usenum2 number(10,2);--使用吨数
    v_money number(10,2);--水费金额
begin
    --对单价进行赋值
    v_price:=3.45;
    --变量赋值
    select usenum,num0,num1 into v_usenum,V_num0,V_num1 from T_ACCOUNT
    where year='2012' and month='01' and owneruuid=1;
    v_usenum2:= round(v_usenum/1000,2);
    v_money:=v_price*v_usenum2;
    DBMS_OUTPUT.put_line('单价： '||v_price||'吨数： '
    ||v_usenum2||'金额： '||v_money||'上月字数： '||v_num0||'本月字数'||v_num1);
end;

（3）属性类型

%TYPE 引用型

作用：引用某表某列的字段类型

declare
    v_price number(10,2);--单价
    v_usenum T_ACCOUNT.USENUM%TYPE;--水费字数
    v_num0 T_ACCOUNT.NUM0%TYPE;--上月字数
    v_num1 T_ACCOUNT.NUM1%TYPE;--本月字数
    v_usenum2 number(10,2);--使用吨数
    v_money number(10,2);--水费金额
begin
--对单价进行赋值
    v_price:=3.45;
    --v_usenum:=8090;
    select usenum,num0,num1 into v_usenum,V_num0,V_num1 from
    T_ACCOUNT
    where year='2012' and month='01' and owneruuid=1;
    --使用吨数
    v_usenum2:= round(v_usenum/1000,2);
    --计算金额
    v_money:=v_price*v_usenum2;
    DBMS_OUTPUT.put_line('单价： '||v_price||'吨数： '
    ||v_usenum2||'金额： '||v_money||'上月字数： '||v_num0||'本月字数'||v_num1);
end;

%ROWTYPE 记录型 ，上例中的例子可以用下面的代码代替

-变量的用法--
declare
    v_price number(10,2);--单价
    v_account T_ACCOUNT%ROWTYPE;--记录型
    v_usenum2 number(10,2);--使用吨数
    v_money number(10,2);--水费金额
begin
    --对单价进行赋值
    v_price:=3.45;
    --赋值
    select * into v_account from T_ACCOUNT
    where year='2012' and month='01' and owneruuid=1;
    --使用吨数
    v_usenum2:= round(v_account.usenum/1000,2);
    --计算金额
    v_money:=v_price*v_usenum2;
    DBMS_OUTPUT.put_line('单价： '||v_price||'吨数： '
    ||v_usenum2||'金额： '||v_money||'上月字数：'||v_account.num0||'本月字数'||v_account.num1);
end;

（4）异常

在运行程序时出现的错误叫做异常。发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

命名的系统异常	产生原因
ACCESS_INTO_NULL	未定义对象
CASE_NOT_FOUND	CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL	集合元素未初始化
CURSER_ALREADY_OPEN	游标已经打开
DUP_VAL_ON_INDEX	唯一索引对应的列上有重复的值
INVALID_CURSOR	在不合法的游标上进行操作
INVALID_NUMBER	内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND	使用 select into 未返回行
TOO_MANY_ROWS	执行 select into 时，结果集超过一行
ZERO_DIVIDE	除数为 0
SUBSCRIPT_BEYOND_COUNT	元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT	使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR	赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED	PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON	PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR	PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH	宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL	使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR	运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID	无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE	Oracle 在等待资源时超时

语法结构：

exception
    when 异常类型 then
    	异常处理逻辑

根据上例中的代码，添加异常处理部分

--变量的用法--
declare
    v_price number(10,2);--水费单价
    v_usenum T_ACCOUNT.USENUM%type; --水费字数
    v_usenum2 number(10,3);--吨数
    v_money number(10,2);--金额
begin
    v_price:=2.45;--水费单价
    select usenum into v_usenum from T_ACCOUNT where
    owneruuid=1 and year='2012' and month='01';
    --字数换算为吨数
    v_usenum2:= round( v_usenum/1000,3);
    --计算金额
    v_money:=round(v_price*v_usenum2,2);
    dbms_output.put_line('单价： '||v_price||'吨数:'||v_usenum2||'金额： '||v_money);
    exception
		when NO_DATA_FOUND then
			dbms_output.put_line('未找到数据，请核实');
		when TOO_MANY_ROWS then
			dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;

（5）条件判断

基本语法1

if 条件 then
    业务逻辑
end if;

基本语法2

if 条件 then
	业务逻辑
else
	业务逻辑
end if;

基本语法3

if 条件 then
	业务逻辑
elsif 条件 then
	业务逻辑
else
	业务逻辑
end if;

需求：设置三个等级的水费 5 吨以下 2.45 元/吨 5 吨到 10 吨部分 3.45 元/吨，超过 10 吨部分 4.45 ，根据使用水费的量来计算阶梯水费。

declare
  v_price1 number(10,2);--不足 5 吨的单价
  v_price2 number(10,2);--超过 5 吨不足 10 吨单价
  v_price3 number(10,2);--超过 10 吨单价
  v_account T_ACCOUNT%ROWTYPE;--记录型
  v_usenum2 number(10,2);--使用吨数
  v_money number(10,2);--水费金额
begin
  --对单价进行赋值
  v_price1:=2.45;
  v_price2:=3.45;
  v_price3:=4.45;
  --赋值
  select * into v_account from T_ACCOUNT
  where year='2012' and month='01' and owneruuid=1;
  --使用吨数
  v_usenum2:= round(v_account.usenum/1000,2);
  --计算金额(阶梯水费)
  if v_usenum2<=5 then--第一个阶梯
     v_money:=v_price1*v_usenum2;
  elsif v_usenum2>5 and v_usenum2<=10 then --第二个阶梯
        v_money:=v_price1*5 + v_price2*( v_usenum2-5);
  else --第三个阶梯
    v_money:=v_price1*5 +v_price2*5 +
    v_price3*( v_usenum2-10 );
  end if;
    DBMS_OUTPUT.put_line('吨数： '
    ||v_usenum2||'金额： '||v_money||'上月字数：
    '||v_account.num0||'本月字数'||v_account.num1);
  exception
    when NO_DATA_FOUND then
         DBMS_OUTPUT.put_line('没有找到数据');
    when TOO_MANY_ROWS then
         DBMS_OUTPUT.put_line('返回的数据有多行');
end;

（6）循环

无条件循环

语法结构：

loop
	--循环语句
	
end loop;

范例：输出从1开始的100个数

declare
	v_num number:=1;
	begin
		loop
            dbms_output.put_line(v_num);
            v_num:=v_num+1;
            exit when v_num>100;
		end loop;
end;

条件循环

语法结构：

while 条件
	loop
	
end loop;

declare
    v_num number:=1;
	begin
    	while v_num<=100
    	loop
            dbms_output.put_line(v_num);
            v_num:=v_num+1;
    	end loop;
end;

for循环

基本语法：

for 变量 in 起始值..终止值
	loop
	
end loop;

begin
      for v_num in 1..100
      loop
          dbms_output.put_line(v_num);
      end loop;
end;

（7）游标

什么是游标

游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。我们可以把游标理解为 PL/SQL 中的结果集

语法结构及示例

在声明区声明游标，语法如下：

cursor 游标名称 is SQL 语句;

使用游标语法

open 游标名称
	loop
		fetch 游标名称 into 变量
		exit when 游标名称%notfound
	end loop;
close 游标名称

需求：打印业主类型为 1 的价格表

--需求：打印业主类型为1的价格表

select * from t_pricetable where ownertypeid=1;

--游标  输出结果集
declare
 cursor cur_pricetable is select * from t_pricetable where ownertypeid=1;--声明游标
 v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable;--打开游标
  loop
    fetch cur_pricetable into v_pricetable;--提取游标
    exit when cur_pricetable%notfound;--推出循环游标
    DBMS_OUTPUT.put_line('价格：'||v_pricetable.price||
    ' 吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
  end loop;
  close cur_pricetable;--关闭游标
end;

带参数的游标

--带参数的游标
declare
 cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype;--声明游标
 v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable(2);--打开游标
  loop
    fetch cur_pricetable into v_pricetable;--提取游标
    exit when cur_pricetable%notfound;--推出循环游标
    DBMS_OUTPUT.put_line('价格：'||v_pricetable.price||
    ' 吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
  end loop;
  close cur_pricetable;--关闭游标
end;

for循环提取游标

--for循环 带参数的游标
declare
 cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype;--声明游标
 --v_pricetable t_pricetable%rowtype;
begin
  for v_pricetable in cur_pricetable(3)
  loop
    DBMS_OUTPUT.put_line('价格：'||v_pricetable.price||
    ' 吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum);
  end loop;
end;

（二）存储函数

（1）什么是存储函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理

（2）存储函数语法结构

创建或修改存储过程的语法如下

CREATE [ OR REPLACE ] FUNCTION 函数名称
（ 参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
	变量声明部分;
BEGIN
	逻辑部分;
RETURN 结果变量;
[EXCEPTION
    异常处理部分]
END;

（3）案例

需求：创建存储函数，根据地址 ID 查询地址名称

create function fn_getaddress(v_id number)
return varchar2
is
	v_name varchar2(30);
begin
	select name into v_name from t_address where id=v_id;
	return v_name;
end;

测试此函数

select fn_getaddress(3) from dual

查询业主 ID，业主名称，业主地址，业主地址使用刚才我们创建的函数来实现

select id 编号,name 业主名称,fn_getaddress(addressid) 地址
from t_owners

（三）存储过程

（1）什么是存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。

2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。

3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

（2）存储过程语法结构

创建或修改存储过程的语法如下:

CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
	变量声明部分;
BEGIN
	逻辑部分
[EXCEPTION
	异常处理部分]
END;

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数

（3）案例

创建不带传出参数的存储过程：添加业主信息

--增加业主信息序列
create sequence seq_owners start with 11;

--增加业主信息存储过程  不带传出参数的存储过程
create or replace procedure pro_owners_add
(
   v_name varchar2,--业主姓名
   v_addressid number,--地址编号
   v_housenumber varchar2,--门牌号
   v_watermeter varchar2,--水表编号
   v_ownertypeid number --业主类型
)
is

begin
  insert into t_owners values
         (seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;
end;

PL/SQL 中调用存储过程

select * from t_owners;

--调用不带传出参数的存储过程
call pro_owners_add('马大哈',2,'2-3','67776',1);

begin
  pro_owners_add('马小哈',2,'2-3','67777',1);
end;

创建带传出参数的存储过程

--创建带传出参数的存储过程
create or replace procedure pro_owners_add2
(
   v_name varchar2,--业主姓名
   v_addressid number,--地址编号
   v_housenumber varchar2,--门牌号
   v_watermeter varchar2,--水表编号
   v_ownertypeid number,--业主类型
   v_id out number
)
is

begin
  --对传出参数赋值
  select seq_owners.nextval into v_id from dual;
  --新增业主记录
  insert into t_owners values
         (v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;
end;

PL/SQL 调用该存储过程

--调用传出参数的存储过程
declare
  v_id number;
begin
  pro_owners_add2('马二哈',2,'2-3','67778',1,v_id);
  dbms_output.put_line(v_id);
end;

select * from t_owners;

（四）触发器

（1）什么是触发器

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时， Oracle 自动地执行触发器中定义的语句序列。

触发器可用于：

数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步

触发器分类

前置触发器（ BEFORE）
后置触发器（ AFTER）

（2）创建触发器的语法

CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
……
begin
PLSQL 块
End ；

FOR EACH ROW 作用是标注此触发器是行级触发器语句级触发器

在触发器中触发语句与伪记录变量的值

触发语句	:ole	:new
Insert	所有字段都是空(null)	将要插入的数据
Update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空(null)

（3）案例

前置触发器

需求：当用户输入本月累计表数后，自动计算出本月使用数

--前置触发器
--需求：当用户输入本月累计表数后，自动计算出本月使用数
create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row --行级触发器
declare

begin
  --通过伪记录变量修改usenum字段的值
  :new.usenum:=:new.num1-:new.num0;
end;

后置触发器

创建后置触发器，自动记录业主更改前后日志

--后置触发器
--自动记录业主更改前后日志
--创建日志表，记录业主名称修改前和修改后的名称
create table t_owners_log(
  updatetime date,
  ownerid number,
  oldname varchar2(30),
  newname varchar2(30)
);

--创建后置触发器
create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row
declare

begin
  --向日志表插入记录
  insert into t_owners_log values (sysdate,:new.id,:old.name,:new.name);
  
end;

--测试更新数据
update t_owners set name='张信哲' where id=8;
commit;

--查询日志表
select * from t_owners_log;