ORACLE 体系结构

1、数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一 样，这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

2、实例

一个 Oracle 实例（Oracle  Instance）有一系列的后台进程（Backguound  Processes)和内存结构（Memory  Structures)组成。一个数据库可以有 n 个实例。

3、数据文件（dbf）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

4、表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成 ，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

注：表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念，oracle 是由用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同用 户可以在同一个表空间建立同一个名字的表。这里区分就是用户了。

5、用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表，ORACLE 的用户与 MYSQL 的数据库类似，每建立一个应用需要创建一个用户。

创建表空间

create tablespace waterboss 
datafile 'c:waterboss.dbf' 
size 100m 
autoextend on 
next 10m;

waterboss  为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on    用于设置自动增长，如果存储量超过初始大小，则开始自动扩容
next 用于设置扩容的空间大小

创建用户

create user wateruser 
identified by boomoom 
default tablespace waterboss;

wateruser  为创建的用户名
identified by 用于设置用户的密码
default tablesapce    用于指定默认表空间名称

用户赋权

grant dba to wateruser;

给用户 wateruser 赋予 DBA 权限后即可登陆

创建表

语法：

CREATE TABLE 表名称(
字段名 类型(长度) primary key,
字段名 类型(长度),
.......
);

数据类型：
1. 字符型
（1）CHAR：固定长度的字符类型，最多存储 2000 个字节
（2）VARCHAR2：可变长度的字符类型，最多存储 4000 个字节
（3）LONG：大文本类型。最大可以存储 2个G

2.数值型
NUMBER：数值类型
例如：NUMBER(5)    最大可以存的数为 99999
NUMBER(5,2)    最大可以存的数为 999.99

3.日期型
（1）DATE：日期时间型，精确到秒
（2）TIMESTAMP：精确到秒的小数点后9位

4.二进制型（大数据类型）
（1）CLOB：存储字符,最大可以存 4个G
（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存4个G

实例：

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

修改表

增加字段

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]...)

给表增加两个字段，语句：

--追加字段
ALTER TABLE T_OWNERS ADD (
REMARK VARCHAR2(20), OUTDATE DATE
)

修改字段

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]...)

修改两个字段的类型，语句：

--修改字段
ALTER TABLE T_OWNERS MODIFY (
REMARK CHAR(20), OUTDATE TIMESTAMP
)

修改字段名

ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名

语句：

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE

删除字段名

--删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名
--删除多个字段
ALTER TABLE 表名称 DROP (列名 1,列名 2...)

语句：

--删除字段
ALTER TABLE T_OWNERS DROP COLUMN REMARK

删除表

语法：

DROP TABLE 表名称

数据增删改

插入数据

语法：

INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值1，值2，...)

执行 INSERT 后一定要再执行 commit 提交事务

语句：

insert into T_OWNERS VALUES (2,'赵大侃 ',1,'2-3','9876',sysdate,1);
commit;

修改数据

语法：

UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；

执行 UPDATE 后一定要再执行 commit 提交事务

需求：将 ID 为 1 的业主的登记日期更改为三天前的日期

update T_OWNERS set adddate=adddate-3 where id=1; 
commit;

删除数据

语法 1：

DELETE FROM 表名 WHERE 删除条件;

执行 DELETE 后一定要再执行 commit 提交事务

需求：删除业主 ID 为 2 的业主信息

delete from T_OWNERS where id=2;
commit;

语法 2：

TRUNCATE TABLE 表名称

比较 truncat 与 delete 实现数据删除？

1. delete 删除的数据可以 rollback
2. delete 删除可能产生碎片，并且不释放空间
3. truncate 是先摧毁表结构，再重构表结构

JDBC连接ORACLE

package com.cnblogs.oracle.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DaoUtil {

    // 加载Oracle驱动
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * 
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin@192.168.56.102:1521:orcl", "water",
                "water");
        return connection;
    }

    /**
     * 释放资源
     * 
     * @param rs
     * @param stmt
     * @param connection
     */
    public static void closeAll(ResultSet rs, Statement stmt, Connection connection) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                rs = null;
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                stmt = null;
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connection = null;
            }
        }
    }
}

DaoUtil

package com.cnblogs.oracle.domain;

import java.util.Date;

public class Owners {
    private Long id;//编号
    private String name;//业主名称
    private Long addressid;//地址编号
    private String housenumber;//门牌号
    private String watermeter;//水表编号
    private Date adddate;//登记日期
    private Long ownertypeid;//业主类型ID
    
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Long getAddressid() {
        return addressid;
    }
    public void setAddressid(Long addressid) {
        this.addressid = addressid;
    }
    public String getHousenumber() {
        return housenumber;
    }
    public void setHousenumber(String housenumber) {
        this.housenumber = housenumber;
    }
    public String getWatermeter() {
        return watermeter;
    }
    public void setWatermeter(String watermeter) {
        this.watermeter = watermeter;
    }
    public Date getAdddate() {
        return adddate;
    }
    public void setAdddate(Date adddate) {
        this.adddate = adddate;
    }
    public Long getOwnertypeid() {
        return ownertypeid;
    }
    public void setOwnertypeid(Long ownertypeid) {
        this.ownertypeid = ownertypeid;
    }

}

Owners

package com.cnblogs.oracle.domain;

import java.sql.SQLException;

import com.cnblogs.oracle.utils.DaoUtil;

public class OwnerDao {
    
    /**
     * 新增业主
     * @param owners
     */
    public static void add(Owners owners) {

        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            conn = DaoUtil.getConnection();
            stmt = conn.prepareStatement("insert into T_OWNERS values(?,?,?,?,?,?,?)");
            stmt.setLong(1, owners.getId());
            stmt.setString(2, owners.getName());
            stmt.setLong(3, owners.getAddressid());
            stmt.setString(4, owners.getHousenumber());
            stmt.setString(5, owners.getWatermeter());
            stmt.setDate(6, new java.sql.Date(owners.getAdddate().getTime()));
            stmt.setLong(7, owners.getOwnertypeid());
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DaoUtil.closeAll(null, stmt, conn);
        }
    }

    /**
     * 更新业主信息
     * @param owners
     */
    public static void update(Owners owners) {

        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            conn = DaoUtil.getConnection();
            stmt = conn.prepareStatement("update T_OWNERS set name=?,addressid=?,housenumber=?,"
                    + "watermeter=?,adddate=?, ownertypeid=? where id=?");

            stmt.setString(1, owners.getName());
            stmt.setLong(2, owners.getAddressid());
            stmt.setString(3, owners.getHousenumber());
            stmt.setString(4, owners.getWatermeter());
            stmt.setDate(5, new java.sql.Date(owners.getAdddate().getTime()));
            stmt.setLong(6, owners.getOwnertypeid());
            stmt.setLong(7, owners.getId());
            stmt.execute();
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            DaoUtil.closeAll(null, stmt, conn);
        }
    }

    /**
     * 删除业主信息
     * @param id
     */
    public static void delete(Long id) {

        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;

        try {
            conn = DaoUtil.getConnection();
            stmt = conn.prepareStatement("delete from T_OWNERS where id=?");
            stmt.setLong(1, id);
            stmt.execute();
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            DaoUtil.closeAll(null, stmt, conn);
        }
    }

}

OwnerDao

数据导出与导入

整库导出与导入

整库导出命令

exp system/boomoom full=y

添加参数 full=y 就是整库导出（导出位置，就是执行该命令时的当前路径）

执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。

如果想指定备份文件的名称，则添加 file 参数即可，命令如下：

exp system/boomoom file=文件名 full=y

整库导入命令

imp system/boomoom full=y

此命令如果不指定 file 参数，则默认用备份文件 EXPDAT.DMP 进行导入。（导入的时候，如果表存在则跳过）

如果指定 file 参数，则按照 file 指定的备份文件进行恢复。

imp system/boomoom full=y file=water.dmp

按用户导出与导入

按用户导出

exp system/boomoom owner=wateruser file=wateruser.dmp

按用户导入

imp system/boomoom file=wateruser.dmp fromuser=wateruser

按表导出与导入

按表导出

exp wateruser/boomoom file=a.dmp tables=t_account,a_area

用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可

按表导入

imp wateruser/boomoom file=a.dmp tables=t_account,a_area

单表查询

简单条件查询

精确查询  where watermeter='30408'

模糊查询  where name like '%刘%'

and 运算符  where name like '%刘%' and housenumber like '%5%'

or 运算符  where name like '%刘%' or housenumber like '%5%'

and 与 or 运算符混合使用   where (name like '%刘%' or housenumber like '%5%') and addressid=3

范围查询  where usenum>=10000 and usenum<=20000  或  where usenum between 10000 and 20000

空值查询  where maxnum is null

去掉重复记录

select distinct addressid from T_OWNERS

排序查询

升序排序  order by usenum

降序排序  order by usenum desc

基于伪列的查询

在 Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就 像表中的列一样，但是在表中并不存储。伪列只能查询，不能进行增删改操作。 例如：ROWID 和 ROWNUM。

ROWID

表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的 物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。由于 ROWID 返回的是该行的物理地址，因此使用 ROWID 可 以显示行是如何存储的。

select rowID,t.* from T_AREA t

可以通过指定 ROWID 来查询记录

select rowID,t.* from T_AREA t
where ROWID='AAAM1uAAGAAAAD8AAC';

ROWNUM

在查询的结果集中，ROWNUM 为结果集中每一行标识一个行号，第一行返回 1， 第二行返回 2，以此类推。通过 ROWNUM 伪列可以限制查询结果集中返回的行数。

select rownum,t.* from T_OWNERTYPE t

分页查询需要用到此伪列。

聚合统计

聚合函数

求和 sum。  语句：select sum(usenum) from t_account where year='2012'

类似的还有 求平均 avg，求最大值 max，求最小值 min，统计记录个数 count。

分组聚合 group by

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

分组后条件查询 having

select areaid,sum(money) from t_account group by areaid having sum(money)>169000

连接查询

多表内连接查询

两表关联查询
select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id

三表关联查询(1-2,1-3)
select o.id 业主编号,o.name 业主名称,ad.name 地址, ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS ad where o.ownertypeid=ot.id and o.addressid=ad.id

四表关联查询(1-2,1-3-4)
select o.id 业主编号,o.name 业主名称,ar.name 区域,ad.name 地址,ot.name 业主类型 from T_OWNERS o ,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA ar where o.ownertypeid=ot.id and o.addressid=ad.id and ad.areaid=ar.id

五表关联查询(1-2,1-3-4,1-3-5)
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

左外连接查询

SQL1999 标准的语法
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow left join T_ACCOUNT ac on ow.id=ac.owneruuid

ORACLE 提供的语法
SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac WHERE ow.id=ac.owneruuid(+)

右外连接查询

SQL1999 标准的语法
select ow.id,ow.name,ac.year,ac.month,ac.money from T_OWNERS ow right join T_ACCOUNT ac on ow.id=ac.owneruuid

ORACLE 提供的语法
select ow.id,ow.name,ac.year,ac.month,ac.money from T_OWNERS ow , T_ACCOUNT ac where ow.id(+) =ac.owneruuid

子查询

where 子句中的子查询

单行子查询

只返回一条记录
单行操作符

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

多行子查询

返回了多条记录
多行操作符

select * from T_OWNERS where addressid in ( 1,3,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 子句中的子查询

select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id) where 业主类型='居民'

select 子句中的子查询

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

分页查询

简单分页

在 ORACLE 进行分页查询，需要用到伪列 ROWNUM 和嵌套查询。

首先显示前 10 条记录：select rownum,t.* from T_ACCOUNT t where rownum<=10。
显示第 11 条到第 20 条的记录，因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用“大于” 符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。所以要用子查询来实现。
select * from (select rownum r,t.* from T_ACCOUNT t where rownum<=20) where r>10

基于排序的分页

执行语句 select rownum r,t.* from T_ACCOUNT t where rownum<=20 order by usenum desc 的结果排序混乱。原因为，ROWNUM 伪列的产生是在表记录扫描是产生的，而排序是后进行的，排序时 R 已经产生了，所以排序后 R 是乱的。

正确的语句为：select * from(select rownum r,t.* from(select * from T_ACCOUNT order by usenum desc) t where rownum<=20 ) where r>10

单行函数

字符函数

求字符串长度 LENGTH
select length('ABCD') from dual;

求字符串的子串 SUBSTR
select substr('ABCD',2,2) from dual;

字符串拼接 CONCAT
select concat('ABC','D') from dual;
select 'ABC'||'D' from dual;

数值函数

四舍五入函数 ROUND
select round(100.567) from dual;
select round(100.567,2) from dual;

截取函数 TRUNC
select trunc(100.567) from dual; -- 默认取小数点前
select trunc(100.567,2) from dual;

取模 MOD
select mod(10,3) from dual;

日期函数

获取当前日期和时间
select sysdate from dual;

加月函数 ADD_MONTHS ：在当前日期基础上加指定的月
select add_months(sysdate,2) from dual;

求所在月最后一天 LAST_DAY
select last_day(sysdate) from dual;

日期截取 TRUNC
select TRUNC(sysdate) from dual;  -- 只显示日期，省略时间

当前年第一天
select TRUNC(sysdate,'yyyy') from dual;

当前月第一天
select TRUNC(sysdate,'mm') from dual;

转换函数

数字转字符串 TO_CHAR
select TO_CHAR(1024) from dual; 或者 select 100||'' from dual;

日期转字符串 TO_CHAR
select TO_CHAR(sysdate,'yyyy-mm-dd') from dual;
select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

字符串转日期 TO_DATE
select TO_DATE('2017-01-01','yyyy-mm-dd') from dual;

字符串转数字 TO_NUMBER
select to_number('100') from dual;

其它函数

空值处理函数 NVL
select NVL(NULL,0) from dual;
需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,则显示 9999999
select PRICE,MINNUM,NVL(MAXNUM,9999999) from T_PRICETABLE where OWNERTYPEID=1

空值处理函数 NVL2
需求：显示价格表中业主类型 ID 为 1 的价格记录，如果上限值为 NULL,显示“不限”。
select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , ' 不 限 ') from T_PRICETABLE where OWNERTYPEID=1

条件取值 decode
decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
功能：根据条件返回相应值
select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') as 类型 from T_OWNERS
或者 select name ,(case ownertypeid when 1 then '居民' when 2 then '行政事业单位' when 3 then '商业' else '其它' end) from T_OWNERS

行列转换

横轴季度，纵轴地区

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

分析函数

RANK 相同的值排名相同，排名跳跃(1,2,2,4)
select rank() over(order by usenum desc),usenum from T_ACCOUNT

DENSE_RANK 相同的值排名相同，排名连续(1,2,2,3)
select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT

ROW_NUMBER 返回连续的排名，无论值是否相等(1,2,3,4)
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.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

创建或修改视图语法

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 的业主信息语句：
create or replace view view_owners1 as select * from T_OWNERS where ownertypeid=1

利用该视图进行查询
select * from view_owners1 where addressid=1;
就像使用表一样去使用视图。

对于简单视图，我们不仅可以用查询，还可以增删改记录。我们下面写一条更新的语句，试一下：
update view_owners1 set name='王刚' where id=2;
结果已经更改成功。我们再次查询表数据，发现表的数据也跟着更改了。由此我们得出结论：视图其实是一个虚拟的表，它的数据其实来自于表。如果更改了视图的数据，表的数据也自然会变化。一个视图所存储的并不是数据，而是一条 SQL 语句。

带检查约束的视图

需求：根据地址表（T_ADDRESS）创建视图 VIEW_ADDRESS2,内容为区域 ID 为 2 的记录。语句：
create or replace view view_address2 as select * from T_ADDRESS where areaid=2 with check option
执行下列更新语句：
update view_address2 set areaid=1 where id=4
系统提示如下错误信息：
Error ORA-01402: view WITH CHECK OPTION where-clause violation

只读视图的创建与使用

如果我们创建一个视图，并不希望用户能对视图进行修改，那我们就需要创建视图时指定 WITH READ ONLY 选项，这样创建的视图就是一个只读视图。
需求：将上边的视图修改为只读视图。语句：
create or replace view view_owners1 as select * from T_OWNERS where ownertypeid=1 with read only
修改后，再次执行 update 语句，会出现错误提示：Error ORA-01733: virtual column not allowed here

创建带错误的视图

我们创建一个视图，如果视图的 SQL 语句所设计的表并不存在，如下：
create or replace view view_TEMP as select * from T_TEMP
T_TEMP 表并不存在，此时系统会给出错误提示：ORA_00942: table or view does not exist

有的时候，我们创建视图时的表可能并不存在，但是以后可能会存在，我们如果 此时需要创建这样的视图，需要添加 FORCE 选项，SQL 语句如下：
create or replace FORCE view view_TEMP as select * from T_TEMP
此时视图创建成功。

复杂视图的创建与使用

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

多表联查的例子：

需求：创建视图，查询显示业主编号，业主名称，业主类型名称 语句：
create or replace view view_owners as select o.id 业主编号,o.name 业主名称,ot.name 业主类型 from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id

使用该视图进行查询 select * from view_owners

修改视图数据，试用下面的语句：
update view_owners set 业主名称='赵四' where 业主编号=1; 可以修改成功。
再试一下下面的语句：
update view_owners set 业主类型='普通居民' where 业主编号=1; 这次，系统弹出错误提示。

原因是我们所需改的列不属于键保留表的列。 什么叫键保留表呢？ 键保留表是理解连接视图修改限制的一个基本概念。该表的主键列全部显示在视图中,并且它们的值在视图中都是唯一且非空的。也就是说，表的键值在一个连接视图中也是键值，那么就称这个表为键保留表。 在我们这个例子中，视图中存在两个表，业主表（T_OWNERS）和业主类型表（T_OWNERTYPE）, 其中 T_OWNERS 表就是键保留表，因为 T_OWNERS 的主键也是作为视图的主键。键保留表的字段是可以更新的，而非键保留表是不能更新的。

分组聚合统计查询的例子：

需求：创建视图，按年月统计水费金额。语句：
create view view_accountsum as  select year,month,sum(money) moneysum from T_ACCOUNT group by year,month order by year,month
此例用到聚合函数，没有键保留表，所以无法执行 update 。

物化视图

物化视图定义

视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次 访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。
物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

创建物化视图语法

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 操作时自动刷新。

物化视图使用案例

创建手动刷新的物化视图

需求：查询地址 ID,地址名称和所属区域名称。
语句：create materialized view mv_address as select ad.id,ad.name adname,ar.name ar_name from t_address ad,t_area ar where ad.areaid=ar.id
上条语句执行完后，查询：select * from mv_address;

这时，我们向地址表（T_ADDRESS）中插入一条新记录，
insert into t_address values(8,'宏福苑小区',1,1);

再次执行上边的语句进行查询，会发现新插入的语句并没有出现在物化视图中。
我们需要通过下面的语句（PL/SQL），手动刷新物化视图：
begin DBMS_MVIEW.refresh('MV_ADDRESS','C');
end;

或者通过下面的命令手动刷新物化视图：
EXEC  DBMS_MVIEW.refresh('MV_ADDRESS','C');
注意：此语句需要在命令窗口中执行。

执行此命令后再次查询物化视图，就可以查询到最新的数据了。
DBMS_MVIEW.refresh 实际上是系统内置的存储过程。

创建自动刷新的物化视图

和上例一样的结果集，语句如下：
create materialized view mv_address2 refresh on commit as select ad.id,ad.name adname,ar.name ar_name from t_address ad,t_area ar where ad.areaid=ar.id
创建此物化视图后，当 T_ADDRESS 表发生变化时，MV_ADDRESS2 自动跟着改变。

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

create materialized view mv_address3 build deferred refresh on commit as select ad.id,ad.name adname,ar.name ar_name from t_address ad,t_area ar where ad.areaid=ar.id;
创建后执行语句 select * from mv_address3; 结果无内容。
执行下列语句生成数据
begin DBMS_MVIEW.refresh('MV_ADDRESS3','C');
end;
再次查询，得到结果。由于我们创建时指定的 on commit，所以在修改数据后能立刻看到最新数据，无须再次执行 refresh。

创建增量刷新的物化视图

如果创建增量刷新的物化视图，必须首先创建物化视图日志
create materialized view log on t_address with rowid; create materialized view log on t_area with rowid;

创建的物化视图日志名称为 MLOG$_表名称

创建物化视图
create materialized view mv_address4 refresh fast as select ad.rowid adrowid ,ar.rowid arrowid, ad.id,ad.name adname,ar.name ar_name from t_address ad,t_area ar where ad.areaid=ar.id;

注意：创建增量刷新的物化视图，必须：
1. 创建物化视图中涉及表的物化视图日志。
2. 在查询语句中，必须包含所有表的 rowid ( 以 rowid 方式建立物化视图日志 )

当我们向地址表插入数据后，物化视图日志有内容：

不同字段含义如下：
SNAPTIME$$：用于表示刷新时间。
DMLTYPE$$：用于表示 DML 操作类型，I 表示 INSERT，D 表示 DELETE，U 表示 UPDATE。
OLD_NEW$$：用于表示这个值是新值还是旧值。N（EW）表示新值，O（LD） 表示旧值，U 表示 UPDATE 操作。 CHANGE_VECTOR$$：表示修改矢量，用来表示被修改的是哪个或哪几个字段。 此列是 RAW 类型，其实 Oracle 采用的方式就是用每个 BIT 位去映射一个列。 插入操作显示为：FE，删除显示为：OO  更新操作则根据更新字段的位置而显示 不同的值。

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。
begin DBMS_MVIEW.refresh('MV_ADDRESS4','C');
end;

序列

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

创建使用简单序列

创建序列语法：
create sequence 序列名称

通过序列的伪列来访问序列的值
NEXTVAL 返回序列的下一个值
CURRVAL 返回序列的当前值
注意：我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。

提取下一个值
select 序列名称.nextval from dual

提取当前值
select 序列名称.currval from dual

创建复杂序列

语法

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

序列使用案例

有最大值的非循环序列

创建序列的语句：
create sequence seq_test1
increment by 10
start with 10
maxvalue 300
minvalue 5

注意：开始值不能小于最小值。
我们执行语句提取序列值，当序列值为300（最大值）的时候再次提取值，系统会报异常信息。

有最大值的循环序列

create sequence seq_test2
increment by 10
start with 10
maxvalue 300
minvalue 5 cycle;

当序列当前值为300（最大值），再次提取的值为：5
select seq_test2.nextval from dual
 
由此我们得出结论，循环的序列，第一次循环是从开始值开始循环，而第二次循环是从最小值开始循环。
注意：创建一个循环的序列，所以必须指定最大值，否则会报错。

带缓存的序列

执行下列语句：
create sequence seq_test3
increment by 10
start with 10
maxvalue 300
minvalue 5
cycle cache 50;
 
上边语句的意思是每次取出 50 个缓存值，但是执行会提示错误。
 错误提示的意思是：缓存设置的数必须小于每次循环的数。 缓存设定的值是 50，而最大值是 300，那么为什么还会提示这样的信息呢？ 其实 cache 虽然是 50，但是每次增长值是 10。这样 50 次缓存提取出 的数是 500    （50*10）

更改为下列的语句：
create sequence seq_test4
increment by 10
start with 10
maxvalue 500
minvalue 10
cycle cache 50;

语句依然报错误，这是因为还存在一个 minvalue ，minvalue 和 maxvalue 之间是 490 个数，也就是一次循环可以提取 490，但是缓存是 500。再次修改语句：

create sequence seq_test5
increment by 10
start with 10
maxvalue 500
minvalue 9
cycle cache 50;

把最小值减 1，或把最大值加 1，都可以通过。

修改和删除序列

修改序列：使用 ALTER SEQUENCE 语句修改序列，不能更改序列的 START WITH 参数
ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

删除序列：
DROP SEQUENCE 序列名称;

同义词

同义词定义

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

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

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

创建与使用同义词

创建同义词的具体语法是：
create [public] SYNONYM synooym for object;
其中 synonym 表示要创建的同义词的名称，object 表示表，视图，序列等我们要创建同义词的对象的名称。

同义词案例

私有同义词

需求：为表 T_OWNERS 创建( 私有 )同义词 名称为 OWNERS
语句：
create synonym OWNERS for T_OWNERS;
使用同义词：
select * from OWNERS ;
查询结果和查原表一样。

公有同义词

需求：为表 T_OWNERS 创建( 公有 )同义词 名称为 OWNERS2：
create public synonym OWNERS2 for T_OWNERS;
以另外的用户登陆，也可以使用公有同义词：
select * from OWNERS2 ;

索引

索引定义

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次 数,从而提高数据访问性能。
索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于一棵“树”，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列（ROWID）

普通索引

语法：create index 索引名称 on 表名(列名);
需求：我们经常要根据业主名称搜索业主信息，所以我们基于业主表的 name 字段来建立索引。
语句：create index index_owners_name on T_OWNERS(name)

索引性能测试

创建一个两个字段的表
create table T_INDEXTEST ( ID NUMBER, NAME VARCHAR2(30));

编写 PL/SQL 插入 100 万条记录
BEGIN
FOR i in 1..1000000
loop
INSERT INTO T_INDEXTEST VALUES(i,'AA'||i);
end loop; commit;
END;

创建完数据后，根据 name 列创建索引
CREATE INDEX INDEX_TESTINDEX on T_INDEXTEST(name)

执行下面两句 SQL 执行
SELECT * from T_INDEXTEST where ID=765432;
SELECT * from T_INDEXTEST where NAME='AA765432';
我们会发现根据 name 查询所用的时间会比根据 id 查询所用的时间要短。

唯一索引

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

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

需求：在业主表的水表编号一列创建唯一索引。
语句：create unique index index_owners_watermeter on T_OWNERS(watermeter);

复合索引

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

语法：create index 索引名称 on 表名(列名,列名.....);根据地址和门牌号对人员表创建索引，语句如下：
create index owners_index_ah on T_OWNERS(addressid,housenumber);

反向索引

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

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

位图索引

使用场景：位图索引适合创建在低基数（性别、名族等有限的值）列上。位图索引是把列上的可能值都建立一个图，再在图上存放数据。查询要走位图索引，只能用等号精确匹配。
位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射。优点：减少响应时间，节省空间占用。

从数据结构上分：位图索引、B+树索引（以上的四种都是）。

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

需求：我们在 T_owners 表的 ownertypeid 列上建立位图索引。语句：
create bitmap index index_owners_typeid on T_OWNERS(ownertypeid)

PL/SQL

PL/SQL定义

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

基本语法结构

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

变量

声明变量的语法：变量名 类型（长度）；
变量赋值的语法：变量名:=变量值

变量的声明

--变量的用法--
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:=8012;--字数
--字数换算为吨数
 v_usenum2:= round( v_usenum/1000,2);
--计算金额
  v_money:=round(v_price*v_usenum2,2);
  dbms_output.put_line('单价：'||v_price||'吨数:'||v_usenum2||'金额：'||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;

属性类型

%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;

异常

在运行程序时发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。异常有两种类型：
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发。
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。

预定义异常
Oracle 预定义异常 21 个

语法结构：

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;

条件判断

基本语法 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;

循环

1．无条件循环
语法结构
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;

2、条件循环
语法结构
while 条件
loop
end loop;

--例子：输出从1开始的100个数
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;

3、for循环
基本语法
for 变量 in 起始值..终止值
loop
end loop;

--例子：输出从1开始的100个数
begin
  for v_num in 1..100
  loop
    dbms_output.put_line(v_num);
  end loop;
end;

游标

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

1、语法结构及示例

在声明区声明游标，语法如下：
cursor 游标名称 is SQL 语句;

使用游标语法：
open 游标名称
  loop
  fetch 游标名称 into 变量
  exit when 游标名称%notfound
end loop;
close 游标名称

--需求：打印业主类型为 1 的价格表
declare
  v_pricetable T_PRICETABLE%rowtype;--价格行对象
  cursor cur_pricetable is select * from T_PRICETABLE where ownertypeid=1;--定义游标
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;

-- 运行结果如下：
-- 2.45 0-5
-- 3.45 5-10
-- 4.45 10-

2、带参数的游标

我们的查询语句的条件值有可能是在运行时才能决定的，比如业主类型，可能是运行时才可以决定，那就要用带参数的游标，修改上述案例：

declare
  v_pricetable T_PRICETABLE%rowtype;--价格行对象
  cursor cur_pricetable(v_ownertypeid number) is select * from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游标
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;

3、for 循环提取游标值

每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的 退出等等。更简单的写法是用 for 循环一切都那么简单， 上例的代码可以改造为下列形式：

declare
  cursor cur_pricetable(v_ownertypeid number) is select * from T_PRICETABLE where ownertypeid=v_ownertypeid;--定义游标
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;

存储函数

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

存储函数语法结构

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

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

存储函数案例

-- 需求：创建存储函数，根据地址 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

存储过程

存储过程定义

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
2、存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

存储过程语法结构

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

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

参数只指定类型，不指定长度
过程参数的三种模式：
IN    传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数

存储过程案例

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

需求：添加业主信息

--增加业主信息序列
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_type number
)
is
begin
  insert into T_OWNERS values(seq_owners.nextval,v_name,v_addressid,v_housenumb er,v_watermeter,sysdate,v_type);
  commit;
end;

PL/SQL 中调用存储过程

call pro_owners_add('赵伟',1,'999-3','132-7',1);

JDBC 调用存储过程

/**
 * 增加
 * @param owners
 */
public static void add(Owners owners){

    java.sql.Connection conn=null;
    java.sql.CallableStatement stmt=null;

    try {
        conn=BaseDao.getConnection();
        stmt=conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");

        stmt.setString(1,  owners.getName());
        stmt.setLong(2,  owners.getAddressid());
        stmt.setString(3,  owners.getHousenumber());
        stmt.setString(4,  owners.getWatermeter());
        stmt.setLong(5,  owners.getOwnertypeid());
        stmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        BaseDao.closeAll(null, stmt, conn);
    }
}

JDBC调用存储过程

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

需求：添加业主信息，传出参数为新增业主的 ID

--增加业主信息存储过程
create or replace procedure pro_owners_add (
  v_name varchar2,
  v_addressid number,
  v_housenumber varchar2,
  v_watermeter varchar2,
  v_type 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_watermete r,sysdate,v_type );
  commit;
end;

PL/SQL 调用该存储过程

declare
  v_id number;--定义传出参数的变量
begin
  pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
  DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;

执行成功后输出结果：
增加成功,ID:12

JDBC 调用存储过程

/**
 * 增加
 * @param owners
 */
public static long add(Owners owners){
    long id=0;
    java.sql.Connection conn=null; 
    java.sql.CallableStatement stmt=null; 
    try {
        conn=BaseDao.getConnection(); 
        stmt=conn.prepareCall("{call pro_owners_add(?,?,?,?,?,?)}");
        stmt.setString(1, owners.getName());
        stmt.setLong(2, owners.getAddressid());
        stmt.setString(3, owners.getHousenumber());
        stmt.setString(4, owners.getWatermeter());
        stmt.setLong(5, owners.getOwnertypeid());
        stmt.registerOutParameter(6, OracleTypes.NUMBER);//注册传出参数类型
        stmt.execute();
        id=stmt.getLong(6);//提取传出参数
    } catch (SQLException e) { 
            e.printStackTrace();
    }finally {
        BaseDao.closeAll(null, stmt, conn);
    }
return id;
}

JDBC 调用存储过程

触发器

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

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

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

创建触发器的语法

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

FOR EACH ROW 作用是标注此触发器是行级触发器（单条数据）还是语句级触发器（一次操作）。

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

触发器案例

1、前置触发器

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

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
  :new.usenum:=:new.num1-:new.num0;
end;

2、后置触发器

需求：当用户修改了业主信息表的数据时记录修改前与修改后的值

--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log (
updatetime date, ownerid number, oldname varchar2(30), newname varchar2(30)
);

--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
  insert into t_owners_log values(sysdate,:old.id,:old.name,:new.name);
end;

测试：

--更新数据
update t_owners set name='杨小花' where id=3;
commit;
--查询日志表
select * from t_owners_log;

案例

1、编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。

水费是实行阶梯计算的，水费价格记录为：
5 吨以下的价格为 2.45
超过 5 吨不足 10 吨的价格为 3.45
超过 10 吨以上的价格为 4.45

考虑到阶梯的层次可能是不确定的，所以我们需要通过游标查询出阶梯价格记录，然后计算每一阶梯的水费，然后相加。伪代码如下：

金额=0
循环价格表{
  if( 上限值为空 或者 总吨数<上限值    ) -- 最高阶梯
  {
    //此为最后阶梯，数量为超过上限值部分的吨数
    金额=金额+ 价格*（总吨数- 上限值）
    退出循环
  }
  else
  {
    //此为非最后阶梯 ，数量为区间内的吨数
    金额=金额+ 价格*（上限值- 下限值）
  }
}

declare
  v_ownertypeid number;--业主类型 ID
  v_usenum2 number(10,2);--总吨数
  v_money number(10,2);--总金额
  cursor cur_pricetable(v_type number) is select * from t_pricetable where ownertypeid=v_type;--价格游标
  v_pricetable t_pricetable%rowtype;--每阶梯价格对象
begin
  v_ownertypeid:=1;
  v_usenum2:=12;
  v_money:=0;

  for v_pricetable in cur_pricetable(v_ownertypeid)
  loop
    if v_pricetable.maxnum is null or v_usenum2<=v_pricetable.maxnum then
      --最后阶梯  （总吨数-下限值）*价格
      v_money:=v_money+ v_pricetable.price*(v_usenum2-v_pricetable.minnum);
      exit;
    else
      --非最后阶梯  (上限值-下限值)* 价格
      v_money:=v_money+ v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minn um);
    end if;
  end loop;
  DBMS_OUTPUT.put_line('阶梯水费金额：'||v_money);
end;

2、存储函数综合案例：创建计算阶梯水费的函数，参数为业主类型、吨数。

create or replace function fn_calmoney(v_ownertypeid number,v_usenum2 number)
return number
is
  v_pricetable t_pricetable%rowtype;--价格行对象
  v_money number(10,2);--金额
cursor cur_pricetable(v_type number) is select * from t_pricetable where ownertypeid=v_type order by minnum;--定义游标
begin
  v_money:=0;--金额
  for v_pricetable in cur_pricetable(v_ownertypeid)
  loop
    --计算阶梯水费
    --如果水费小于最大值，或最大值为 null 表示此阶梯为最后一个阶梯。因为游标中的顺序按照minnum排序，第一分支仅会执行1次，exit执行，循环体结束。
    --价格*（总吨数-此阶梯下限值）
    if v_usenum2<= v_pricetable.maxnum or v_pricetable.maxnum is null then
      v_money:=v_money+ v_pricetable.price* ( v_usenum2 - v_pricetable.minnum);
      exit;
    else -- 价格*（此阶梯上限值-此阶梯下限值）
      v_money:=v_money+  v_pricetable.price* (v_pricetable.maxnum-v_pricetable.minnum);
    end if;
  end loop;
  return v_money;
end;

测试此函数：

select fn_calmoney(1,12) from dual;

3、触发器综合案例：当用户输入本月累计数后，自动计算阶梯水费。

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
  v_usenum2 number(10,2);--吨数
begin
  --使用数赋值
  :new.usenum:=:new.num1-:new.num0;
  v_usenum2:= round( :new.usenum/1000,3);--计算吨数
  :new.money:=fn_calmoney(:new.ownertype,v_usenum2);--对金额列赋值
end;

4、存储过程综合案例。

需求：增加业主信息时，同时在账务表（account）增加一条记录，年份与月份 为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners 表一致。

tips：
1. 如何取得年和月。用 to_char()函数
2. 如何取得区域 ID 。参数中没有直接提供区域 ID，我们可以通过 addressid

到 address 表查询

-- 创建存储过程语句：
create or replace procedure pro_owners_add
(
  v_name varchar2, v_addressid number, v_housenumber varchar2, v_watermeter varchar2, v_type number, v_ownersuuid out number
)
is
  v_area number;--区域编号 v_year char(4);--年份 v_month char(2);--月份
begin
  --提取序列值到变量
  select seq_owners.nextval into v_ownersuuid from dual;
  --根据地址编号查询区域编号
  select areaid into v_area from t_address where id=v_addressid;
  --年份
  v_year:=to_char(sysdate ,'yyyy');
  --月份
  v_month:=to_char(sysdate,'mm');
  --增加业主信息
  insert into t_owners values( v_ownersuuid,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type);
  --增加账务表信息
  insert into t_account (id,owneruuid,ownertype,areaid,year,month,num0) values (seq_account.nextval,v_ownersuuid,v_type,v_area,v_year, v_month,0);
  commit;
exception
  when NO_DATA_FOUND then
    v_ownersuuid:=-1;
    rollback;
end;