Oracle

Oracle基础

一、ORACLE 简介

1、oracle概述

ORACLE 数据库系统以分布式数据库为核心的一组软件产品

1. 支持多用户、大事务量的事务处理
2. 数据安全性和完整性控制
3. 支持分布式数据处理
4. 可移植性强，跨平台

2、oracle体系架构

1）数据库

Oracle 数据库是数据的物理存储。

Oracle 数据库的概念和其它数据库不一样，Oracle 就只有一个大数据库。

2）实例

一个数据库可以有多个实例，开多个Oracle进程。

3）数据文件dbf

数据文件是数据库的物理存储单位。

一个数据文件只能属于一个表空间。

4）表空间

对物理数据库上相关数据文件的逻辑映射。

5）用户

用户是在表空间下建立的。用户登陆后只能看到和操作自己的表。

Oracle的用户与 mysql 的数据库类似，每建立一个应用需要创建一个用户。

3、命令行界面

sqlplus 用户名/密码

4、使用Navicat远程连接

1、在虚拟机打开Net Manager软件

2、选择本地-监听程序-LISTENER，添加地址，ip为本机ip，端口号为1521

3、重启服务OracleOraDB19Home1TNSListener

4、在Navicat中选择Oracle数据库连接

5、成功连接

二、操作用户和表空间

1、创建表空间

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

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

2、创建用户

oracle_12版本的特性，在CDB容器中用户名必须加c##前缀才能创建成功。

create user c##wateruser
identified by rexhao
default tablespace waterboss

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

3、用户赋权

grant dba to c##wateruser

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

4、删除用户

drop user 用户名 cascade

5、创建表

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

create table t_owners
(
    id number primary key,      -- 主键
    name varchar2(30),          -- 业主名称
    addressid number,           -- 地址 ID
    housenumber varchar2(30),   -- 门牌号
    watermeter varchar2(30),    -- 水表编号
    adddate date,               -- 登记日期
    ownertypeid number          -- 业主类型 ID
);

Oracle 数据类型

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

6、修改表

1）增加字段

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

2）修改字段

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

3）修改字段名

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

-- 修改字段名
ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE

4）删除字段名

-- 删除一个字段
ALTER TABLE 表名称 DROP COLUMN 列名

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

7、删除表

DROP TABLE 表名称

三、数据的CRUD

1、添加数据

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

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

Navicat软件会自动提交事务

2、修改数据

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

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

3、删除数据

DELETE FROM 表名 WHERE 删除条件
TRUNCATE TABLE 表名称

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

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

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

4、查询数据

SELECT * FROM 表名

四、JDBC 连接 ORACLE

1、引入驱动包

JDBC 驱动：oracle.jdbc.OracleDriver

连接字符串（瘦连接）：jdbc:oracle:thin:@虚拟机的 IP:1521:orcl

2、BaseDao类

package work.rexhao.dao;

import java.sql.SQLException;

public class BaseDao {
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static java.sql.Connection getConnection() throws SQLException {
        return java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.61:1521:orcl", "c##wateruser", "rexhao");
    }

    public static void closeAll(java.sql.ResultSet rs, java.sql.Statement stmt, java.sql.Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3、添加方法

public class OwnersDao {
    public static void add(Owners owners) {
        java.sql.Connection conn = null;
        java.sql.PreparedStatement stmt = null;
        try {
            conn = BaseDao.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 {
            BaseDao.closeAll(null, stmt, conn);
        }
    }
}

五、数据导出与导入

1、整库导出与导入

整库导出命令

exp system/123.COM full=y

exp system/123.COM file=文件名 full=y

添加参数 full=y 就是整库导出

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

指定备份文件的名称，则添加 file 参数

整库导入命令

imp system/123.COM full=y

imp system/123.COM full=y file=water.dmp

此命令如果不指定 file 参数，则默认用备份文件 EXPDAT.DMP 进行导入

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

2、按用户导出与导入

### 按用户导出
exp system/123.COM owner=wateruser file=wateruser.dmp

### 按用户导入
imp system/123.COM file=wateruser.dmp fromuser=wateruser

3、按表导出与导入

### 按表导出
exp wateruser/itcast file=a.dmp tables=t_account,a_area

### 按表导入
imp wateruser/itcast file=a.dmp tables=t_account,a_area

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

Oracle查询

一、单表查询

1、简单条件查询

1）精确查询

SELECT
    * 
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）范围查询

-- 运算符
SELECT
    * 
FROM
    T_ACCOUNT 
WHERE
    usenum >= 10000 
    AND usenum <= 20000

-- between and
SELECT
    * 
FROM
    T_ACCOUNT 
WHERE
    usenum BETWEEN 10000 
    AND 20000

6）空值查询

-- is null
SELECT
    * 
FROM
    T_PRICETABLE t 
WHERE
    maxnum IS NULL

-- is not null
SELECT
    * 
FROM
    T_PRICETABLE t 
WHERE
    maxnum IS NOT NULL

2、去掉重复记录

SELECT DISTINCT
    * 
FROM
    T_OWNERS

3、排序查询

1）升序排序

SELECT
    * 
FROM
    T_ACCOUNT 
ORDER BY
    usenum

2）降序排序

SELECT
    * 
FROM
    T_ACCOUNT 
ORDER BY
    usenum DESC

4、基于伪列的查询

1）ROWID

ROWID 伪列是该行的物理地址。使用 ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的 标识表中的一行。

SELECT 
    ROWID,
    t.* 
FROM
    T_AREA t

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

SELECT 
    ROWID,
    t.* 
FROM
    T_AREA t 
WHERE
    ROWID = 'AAAM1uAAGAAAAD8AAC';

2）ROWNUM

ROWNUM 为结果集中每一行标识一个行号，第一行返回 1， 第二行返回 2...

SELECT 
    ROWNUM,
    t.* 
FROM
    T_OWNERTYPE t

5、聚合统计

1）聚合函数

1. 求和 sum

SELECT
    sum( usenum ) 
FROM
    T_ACCOUNT 
WHERE
    year = '2012'

2. 求平均 avg

SELECT
    avg( usenum ) 
FROM
    T_ACCOUNT 
WHERE
    year = '2012'

3. 求最大值 max

SELECT
    max( usenum ) 
FROM
    T_ACCOUNT 
WHERE
    year = '2012'

4. 求最小值 min

SELECT
    count( * ) 
FROM
    T_OWNERS t 
WHERE
    ownertypeid =1

5. 统计记录个数 count

select count(*) from T_OWNERS t where ownertypeid=1

2）分组聚合 group by

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

SELECT
    areaid,
    sum( money ) 
FROM
    t_account 
GROUP BY
    areaid

3）分组后条件查询 having

SELECT
    areaid,
    sum( money ) 
FROM
    t_account 
GROUP BY
    areaid 
HAVING
    sum( money ) > 169000

二、连接查询

1、多表内连接查询

SELECT
    o.id 业主编号,
    o.name 业主名称,
    ot.name 业主类型 
FROM
    T_OWNERS o,
    T_OWNERTYPE ot 
WHERE
    o.ownertypeid = ot.id

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

2、左外连接查询

-- 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 ( + )

3、右外连接查询

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

三、子查询

1、where子句中的子查询

1）单行子查询

• 子查询只返回一条记录
• 单行操作符（=、>、<...）

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）多行子查询

• 子查询返回了多条记录
• 多行操作符（in 、any、all）

in运算符

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

-- in
SELECT * 
FROM T_OWNERS 
WHERE addressid IN ( SELECT id FROM t_address WHERE name LIKE '%花园%' )

-- not in             
SELECT * 
FROM T_OWNERS 
WHERE addressid NOT IN ( SELECT id FROM t_address WHERE name LIKE '%花园%' )

2、from子句中的子查询

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

3、select子句中的子查询

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

SELECT
    id,
    name,
    ( SELECT name FROM t_address WHERE id = addressid ) addressname 
FROM
    t_owners

四、简单分页查询

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

-- 前十条记录
SELECT 
    ROWNUM,
    t.* 
FROM
    T_ACCOUNT t 
WHERE
    ROWNUM <= 10

-- 11至20记录（错误写法）
-- 因为 rownum 是在查询语句扫描每条记录时产生的，所以不能使用"大于"符号
SELECT 
    ROWNUM ,
    t.* 
FROM
    T_ACCOUNT t 
WHERE
    ROWNUM > 10 
    AND ROWNUM <= 20

-- 11至20记录（正确写法）
-- 利用子查询
SELECT
    * 
FROM
    (
    SELECT 
        ROWNUM r,
        t.* 
    FROM
        T_ACCOUNT t 
    WHERE
        ROWNUM <= 20
    )
WHERE 
    r > 10

五、单行函数

1、字符函数

1）求字符串长度 LENGTH

dual 伪表：为了补全语法而存在

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;

2、数值函数

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

3、日期函数

0）当前日期与时间 sysdate

select sysdate from dual

1）加月函数 ADD_MONTHS

在当前日期基础上加指定的月

select add_months(sysdate,2) from dual

2）求所在月最后一天 LAST_DAY

select last_day(sysdate) from duall

3）日期截取 TRUNC

以2016/10/11为例

select TRUNC(sysdate) from dual

select TRUNC(sysdate,'yyyy') from dual

select TRUNC(sysdate,'mm') from dual

4、转换函数

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

5、其它函数

1）空值处理函数 NVL

NVL（检测的值，如果为 null 的值）

select NVL(NULL,0) from dual

2）空值处理函数 NVL2

NVL2（检测的值，如果不为 null 的值，如果为 null 的值）

SELECT
    PRICE,
    MINNUM,
    NVL2( MAXNUM, to_char( MAXNUM ), '不限' ) 
FROM
    T_PRICETABLE 
WHERE
    OWNERTYPEID =1

3）条件取值 decode

根据条件返回相应值

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

-- decode 语句实现
SELECT
    name,
    decode(
        ownertypeid,
        1, ' 居 民 ',
        2, ' 行 政 事 业 单 位 ',
        3, '商业' 
    ) 类型 
FROM
    T_OWNERS

-- 也可以用 case when then 语句来实现
SELECT
    name,
    (
    CASE ownertypeid 
        WHEN 1 THEN '居民' 
        WHEN 2 THEN '行政事业单位' 
        WHEN 3 THEN'商业' 
        ELSE '其它' 
    END 
    ) 
FROM
    T_OWNERS

-- case when then 语句的另外一种写法
SELECT
    name,
    (
    CASE    
        WHEN ownertypeid = 1 THEN '居民' 
        WHEN ownertypeid = 2 THEN '行政事业' 
        WHEN ownertypeid = 3 THEN '商业' 
    END 
    )
FROM
    T_OWNERS

六、分析函数

1、RANK

相同的值排名相同，排名跳跃

SELECT
    rank ( ) over ( ORDER BY usenum DESC ),
    usenum 
FROM
    T_ACCOUNT

2、DENSE_RANK

相同的值排名相同，排名连续

SELECT
    dense_rank ( ) over ( ORDER BY usenum DESC ),
    usenum 
FROM
    T_ACCOUNT

3、ROW_NUMBER

返回连续的排名，无论值是否相等

SELECT
    row_number ( ) over ( ORDER BY usenum DESC ),
    usenum 
FROM
    T_ACCOUNT

七、集合运算

1、并集运算

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

2、交集运算

SELECT
    * 
FROM
    t_owners 
WHERE
    id <= 7 INTERSECT SELECT * FROM t_owners WHERE id >=5

3、差集运算

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

Oracle对象

一、视图

1、视图概述

视图是一种数据库对象

视图 => 封装sql语句 => 虚拟表

2、视图的优点

1. 简化操作：视图可以简化用户处理数据的方式。
2. 着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。
3. 视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
4. 提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

3、视图的创建与修改

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 操作

4、视图的删除

DROP VIEW view_name

5、案例

1）简单视图

简单视图：视图中的语句只是单表查询，并且没有聚合函数

-- 创建简单视图
CREATE 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语句。

2）带检查约束的视图

create view view_address2 as
select * from T_ADDRESS where areaid=2
with check option

-- 无法修改的条件：因为视图的条件是areaid=2
update view_address2 set areaid=1 where id=4

3）只读视图

create or replace view view_owners1 as
select * from T_OWNERS where ownertypeid=1
with read only

尝试执行修改操作：

4）带错误的视图

我们创建一个视图，如果视图的 SQL 语句所设计的表并不存在

CREATE FORCE VIEW view_TEMP AS 
SELECT * FROM T_TEMP

用途：表会运行时产生

5）复杂视图

1. 多表关联查询

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

-- 创建复杂视图
CREATE 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;

只能修改键保留表（主键所在的那个表）的数据

2. 分组聚合统计查询

-- 创建视图，按年月统计水费金额
create view view_accountsum as
select year,month,sum(money) moneysum 
from T_ACCOUNT 
group by year,month
order by year,month

此例用到聚合函数，没有键保留表，所以无法执行 update 。

二、物化视图

1、物化视图概述

物化视图与普通的视图相比的区别是物化视图是建立的副本。

• 优点：查询效率高
• 缺点：占用存储空间

2、物化视图创建

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 操作后，物化视图何时采用哪种方式和基表进行同步
  • FAST：增量刷新，通过日志修改
  • COMPLETE ：完全刷新，每次修改之后重新生成
  • FORCE：自动选择，判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式（FORCE 是默认的方式）
• 刷新的模式
  • ON DEMAND ：需要手动刷新物化视图（默认）
  • ON COMMIT ：基表发生 COMMIT 操作时自动刷新

3、物化视图创建

drop materialized view view_address1

4、案例

1）手动刷新

-- 创建手动刷新的物化视图
CREATE MATERIALIZED VIEW mv_address 
AS 
SELECT
    ad.id,
    ad.name ad_name,
    ar.name ar_name 
FROM
    t_address ad,
    t_area ar 
WHERE
    ad.areaid = ar.id

-- 查询物化视图
select * from mv_address

手动刷新物化视图

-- 1、PL/SQL
BEGIN
    DBMS_MVIEW.refresh ( 'MV_ADDRESS', 'C' );
END;

### 2、sql命令窗口
EXEC DBMS_MVIEW.refresh('MV_ADDRESS','C');

c：COMPLETE，完全刷新

2）自动刷新

-- 创建自动刷新的物化视图
CREATE MATERIALIZED VIEW mv_address2 
refresh 
ON commit 
AS 
SELECT
    ad.id,
    ad.name ad_name,
    ar.name ar_name 
FROM
    t_address ad,
    t_area ar 
WHERE
    ad.areaid = ar.id

3）不生成数据

第一次必须手动刷新，之后（ON commit）会自动刷新

-- 创建不生成数据的物化视图
CREATE MATERIALIZED VIEW mv_address3 
build deferred
refresh 
ON commit 
AS 
SELECT
    ad.id,
    ad.name ad_name,
    ar.name ar_name 
FROM
    t_address ad,
    t_area ar 
WHERE
    ad.areaid = ar.id;

4）增量刷新

物化视图日志：记录基表发生了哪些变化，用这些记录去更新物化规图

如果创建增量刷新的物化视图，必须首先创建物化视图日志

-- 创建物化视图日志
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 方式建立物化视图日志）

当我们手动刷新物化视图后，物化视图日志被清空，物化视图更新。

三、序列

1、序列概述

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

类似于mysql的自动增长

2、简单序列

-- 创建序列
create sequence 序列名称

通过序列的伪列来访问序列的值

• NEXTVAL：返回序列的下一个值
• CURRVAL：返回序列的当前值

我们在刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。

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

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

3、复杂序列

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

4、修改序列

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

ALTER SEQUENCE 序列名称 MAXVALUE 5000 CYCLE;

5、删除序列

DROP SEQUENCE 序列名称;

6、案例

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

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

当序列值为最大值的时候再次提取值，系统会报异常信息。

2）有最大值的循环序列

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

第一次循环是从开始值开始循环，而第二次循环是从最小值开始循环

3）带缓存的序列

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

四、同义词

1、同义词概述

指定方案对象的一个别名

2、同义词的创建与使用

create [public] SYNONYM synooym for object;

• synonym ：要创建的同义词的名称
• object ：表，视图，序列等要创建同义词的对象的名称
• public
  • 私有同义词：只有当前用户能用
  • 公有同义词：所有的用户都可以使用

3、案例

1）私有同义词

-- 创建私有同义词
create synonym OWNERS for T_OWNERS;

-- 使用私有同义词
select * from OWNERS;

2）公有同义词

以另外的用户登陆，也可以使用公有同义词

-- 创建公有同义词
create public synonym OWNERS2 for T_OWNERS;

-- 使用公有同义词
select * from OWNERS2;

五、索引

1、索引概述

相当于书的目录、字典的索引

索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于一棵"树"。

树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列ROWID。

• 优点：查询效率高
• 缺点：占用存储空间

2、普通索引

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

3、唯一索引

如果索引列的值是不会重复的，可以创建唯一索引。

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

4、复合索引

基于两个以上的列建立一个索引

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

5、反向键索引

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。会增加查询的层数，性能会下降。

建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

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

6、位图索引

位图索引适合创建在低基数列上 。

位图索引不直接存储 ROWID，而是存储字节位到 ROWID 的映射。

减少响应时间，节省空间占用。

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

Oracle编程

一、PL/SQL

1、PL/SQL概述

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，使 SQL 语言具有过程处理能力。

基本语法结构

[declare 
    -- 声明变量
]

begin
    -- 代码逻辑 

[exception
    -- 异常处理
]

end;

2、变量

1）变量的声明与赋值

-- 声明变量
变量名 类型(长度);

-- 变量赋值
变量名:=变量值;

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:=8012;

    -- 字数换算为吨数
    v_usenum2:= round( v_usenum/1000,2 );

    -- 计算金额
    v_money:=round(v_price*v_usenum2,2);

    -- 文字的输出
    dbms_output.put_line('金额：'||v_money); 
end;

2）select into 赋值

-- select into 语法
select 列名 into 变量名 from 表名 where 条件

select into 结果必须是一条记录 ，有多条记录和没有记录都会报错

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 into赋值
    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_money||'上月字数：'||v_num0||'本月字数'||v_num1);
end;

3、属性类型

1）%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);               -- 水费金额

2）%ROWTYPE 记录型

标识某个表的行记录类型

一个记录型代表一行数据，类似java编程的实体类

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赋值
    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_money);
end;

4、异常（例外）

发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

• 预定义异常：Oracle预先定义的异常
  • NO_DATA_FOUND：使用 select into 未返回行
  • TOO_MANY_ROWS：执行 select into 时，结果集超过一行
• 用户定义异常：用户定义异常，通过 RAISE 语句显式引发

-- 异常语法结构

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 into赋值
    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_money);

-- 异常处理
exception
    when NO_DATA_FOUND then
        dbms_output.put_line('未找到数据');
    when TOO_MANY_ROWS then
        dbms_output.put_line('查询条件有误，返回多条信息');
end;

5、条件判断

-- 1、if
if 条件 then
    代码;
end if;

-- 2、if else
if 条件 then
    代码;
else
    代码;
end if;

-- 3、if elif eles
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赋值
    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_money);

-- 异常处理
exception
    when NO_DATA_FOUND then
        DBMS_OUTPUT.put_line('没有找到数据');
    when TOO_MANY_ROWS then
        DBMS_OUTPUT.put_line('返回的数据有多行');
end;

6、循环

1）loop无条件循环

loop
    代码
    exit when 退出条件;
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条件循环

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;

7、游标

1）游标概述

存放 SQL 语句执行的结果集

2）游标的语法

-- 声明游标
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); 
    end loop;

    -- 关闭游标
    close cur_pricetable;
end ;

3）带参数的游标

条件值有可能是在运行时才能决定的

类似于java的传参

declare
    -- 价格的行对象
    v_pricetable T_PRICETABLE%rowtype;
    -- 定义游标
    cursor cur_pricetable(v_ownertype number) is select * from T_PRICETABLE where ownertypeid=v_ownertype;
begin
    -- 打开游标
    open cur_pricetable(1);

    loop
        -- 提取游标到变量
        fetch cur_pricetable into v_pricetable;

        -- 当游标到最后一行下面退出循环
        exit when cur_pricetable%notfound;

        -- 打印数据
        dbms_output.put_line('价格:'||v_pricetable.price); 
    end loop;

    -- 关闭游标
    close cur_pricetable;
end ;

4）for 循环提取游标值

declare
    -- 定义游标
    cursor cur_pricetable(v_ownertypeid number) is select * from T_PRICETABLE where ownertypeid=v_ownertypeid;
begin
    -- for循环
    for v_pricetable in cur_pricetable(3)
    loop 
        dbms_output.put_line('价格:'||v_pricetable.price); 
    end loop; 
end ;

二、存储函数

1、存储函数概述

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。

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

-- 函数在子查询的应用
select id 编号,name 业主名称,fn_getaddress(addressid) 地址 from t_owners

三、存储过程

1、存储过程概述

存储过程没有return，但是可以通过传出函数，传出多个返回值

应用程序可以调用存储过程，执行相应的逻辑，对业务逻辑的封装。

与MVC框架的思想冲突

效率比MVC框架高

2、存储过程语法结构

create [ or replace ] procedure 存储过程名称
(参数名 类型, 参数名 类型, 参数名 类型)
is|as
    变量声明部分;
begin
    逻辑部分;
[exception 
    异常处理部分;]
end;

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

过程参数的三种模式：

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

3、案例

1）不带传出参数的存储过程

-- 创建
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_housenumber,v_watermeter,sysdate,v_type);
    commit;
end;

-- 调用
-- 1、call
call pro_owners_add('赵伟',1,'999-3','132-7',1);
-- 2、begin end
begin
    pro_owners_add('赵伟',1,'999-3','132-7',1);
end;

// JDBC 调用存储过程
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);
    }
}

2）带传出参数的存储过程

-- 创建
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_watermeter,sysdate,v_type);
    commit;
end;

-- 调用
declare
    v_id number;
begin
    pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
    DBMS_OUTPUT.put_line('增加成功，ID:'||v_id);
end;

// JDBC 调用存储过程
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;
}

四、触发器

1、触发器概述

触发器是一个与表相关联的、存储的PL/SQL程序。

每当一个特定的操作，Oracle自动地执行触发器中定义的语句序列。

触发器可用于

• 数据确认：录入的合法性验证
• 实施复杂的安全性检查
• 做审计，跟踪表上所做的数据操作等
• 数据的备份和同步

触发器分类

• 前置触发器（BEFORE）：sql执行前执行触发器，可以修改sql执行的值
• 后置触发器（AFTER）：sql执行后执行触发器

2、创建触发器

create [or replace] trigger 触发器名
    before|after
    [delete][[or] insert] [[or] update[of 列名]]
    on 表名
    [for each row][when(条件)]
declare
    变量
begin
    代码
end;

• FOR EACH ROW ：作用是标注此触发器是行级触发器，不标注为语句级触发器
  • 行级触发器：每影响一行触发一次
  • 语句级触发器：每个语句只触发一次

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

触发语句	:old（修改前的行数据）	:new（修改后的行数据）
insert	所有字段都是空（null）	将要插入的数据
update	更新以前该行的值	更新后的值
delete	删除以前该行的值	所有字段都是空（null）

3、案例

1）前置触发器

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

CREATE OR REPLACE TRIGGER tri_account_update_num1
    before 
    UPDATE OF num1 
    ON t_account 
    FOR each ROW
DECLARE
BEGIN
    : new.usenum :=: new.num1 -: new.num0;
END;

2）后置触发器

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

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;