Oracle基础

数据库是用户存放数据、访问数据、操作数据的存储仓库用户的各种数据被存放在数据库中。

数据库的类型:网状型数据库、层次型数据库、关系型数据库

一个Oracle实例有一系列的后台进程和内存结构组成。一个数据库可以有n个实例。

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

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

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

1.  -- 创建表空间
2.  create tablespace 表名称
3.  datafile '存储路径' size 100m
4.  autoextend on next 10m;

1.  -- 创建用户
2.  create user 用户名
3.  identified by 密码
4.  default tablespace 所归属的表空间名称;

二、Oracle数据类型

1.字符型

CHAR：固定长度字符类型，最多存储2000字节

VARCHAR2：可变长度的字符类型，最多存储4000字节

LONG:大文本类型，最多存储2个G

2.数值型

NUMBER:数值类型

3．日期型

DATE:日期时间型   TIMESTAMP:精确到秒小数点后9位

三、Oracle数据的导出与导入

当我们使用一个数据库时，总希望数据库内容可靠、正确。但由于计算机系统的故障影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库全部或部分数据丢失。

四、Oracle数据查询

1、基于伪列的查询

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

ROWID

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

查询语句：

select rowID,别名。* from 表名别名

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

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

ROWNUM

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

1.  select rownum,别名.* from 表名 别名
2.  -- 示例： select rownum,t.* from T_OWNERTYPE t where id>3

外连接

左外连接

1.  select
2.      t1.name,  
3.      t1.age, 
4.      t2.name  
5.  from
6.      表1 t1,
7.      表2 t2
8.  where 
9.      t1.表1字段 = t2.表2字段(+);

右外连接

在where条件的等号左端加（+）

2.单行函数

（1）.字符函数

（2）数值函数

（3）日期函数

（4）转换函数

（5）其他函数

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

1.  select rank() over(order by 字段 desc ),字段,.... from 表名
2.  -- 示例： select rank() over(order by usenum desc ),usenum from T_ACCOUNT

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

1.  select dence_rank() over(order by 字段 desc ),字段,.... from 表名
2.  -- 示例： select dence_rank() over(order by usenum desc ),usenum from T_ACCOUNT

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

1.  select row_number() over(order by 字段 desc ),字段,.... from 表名
2.  -- 示例： select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

1. 集合运算

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

UNION ALL(并集)：返回各个查询的所有记录，包括重复记录

1.  -- UNION ALL 并集（不去掉重复记录）
2.  select * from t_owners where id<=7 
3.  union 
4.  all select * from t_owners where id>=5

UNION（并集），返回各个查询的所有记录，不包括重复记录

1.  -- UNION 并集（去掉重复记录）
2.  select * from t_owners where id<=7 
3.  union 
4.  select * from t_owners where id>=5

INTERSECT（交集），返回两个查询共有的记录

1.  -- intersect 交集
2.  select * from t_owners where id<=7 
3.  intersect 
4.  select * from t_owners where id>=5

MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录

1.  -- minus 差集
2.  select * from t_owners where id<=7
3.  minus 
4.  select * from t_owners where id>=5

五、Oracle的其他对象

物化视图

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

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

创建物化视图的语法：

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:则在创建时不生成数据，以后根据需要再生成数据。

REFRESH（刷新的方式）指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。

FAST:刷新采用增量刷新，只刷新自上次刷新以后进行修改。

COMPLETE：刷新对整个物化视图进行完全的刷新，即先把原来的物化视图删掉，再自动使用创建物化视图语句重新创建物化视图。

FORCE: Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。

刷新的模式有两种：ON DEMAND和ON COMMIT

ON DEMAND:指需要手动刷新物化视图

ON COMMIT：指在基表发生COMMIT操作时自动刷新

在采用FAST增量刷新前，需要先创建物化日志表，语法如下：

create materialized view log on 基表名称 with 标识;

标识：一般为基表的id，是增删改物化视图中数据的依据

创建完日志后，会生成一个物化日志表，记录相关信息；更新完物化视图后，日志表中的数据会被删除。

手动刷新物化视图的方法：

1.  begin
2.      DBMS_MVIEW.refresh('物化视图名称','C');  -- 系统内置的存储过程
3.  end;

1.  -- 方法二：在SQL命令窗口执行下列语句
2.  EXEC DBMS_MVIEW.refresh('物化视图名称','C');

若使用 BUILD DEFERRED 参数（创建物化视图时不生成数据），在第一次生成数据时需要手动刷新物化视图：

1.  begin
2.      DBMS_MVIEW.refresh('物化视图名称','C');
3.  end;

序列

序列是ORACLE提供的用于产生一系列唯一数字的、且独立于表的数据库对象。表示表，序列是序列，序列可以产生一系列唯一数字供表使用。

创建简单序列语法：

create sequence 序列名称

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

NEXTVAL返回序列的下一个值

CURRVAL返回序列的当前值

1.  -- 提取下一个值
2.  select 序列名称.nextval from dual
3.  -- 提取当前值
4.  select 序列名称.currval from dual

同义词

同义词简称外号，实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

创建同义词：

1.  create [public] SYNONYM 同义词名称 for object;
2.-- ，object 表示:表、视图、序列等我们要创建同义词的对象的名称

PL/SQL

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

基本语法结构：

1.  [declare] --声明变量
2.  begin
3.   --代码逻辑 
4.  [exception] --异常处理
5.  end;

变量赋值的语法

-- 方法一：直接赋值
1.  变量名:=变量值;
2.  -- 方法二：Select into 方式赋值，结果必须是一条记录 ，有多条记录和没有记录都会报错
3.  select 列名 into 变量名 from 表名 where 条件;

存储函数

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

创建或修改存储函数的语法如下：

1.  CREATE [ OR REPLACE ] FUNCTION 函数名称(参数名称 参数类型, ...)
2.  RETURN 结果变量数据类型
3.  IS
4.       变量声明部分;
5.  BEGIN
6.       逻辑部分;
7.       RETURN 结果变量;
8.      [EXCEPTION 异常处理部分]
9.  END;

调用自定义函数：

select 函数名(参数...) from 表名;

存储过程

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

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

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

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

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

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

  CREATE [ OR REPLACE ] PROCEDURE 存储过程名称（参数名 in/out/inout 类型, ...）
1.  IS|AS
2.       变量声明部分;
3.  BEGIN
4.       逻辑部分
5.      [EXCEPTION 异常处理部分]
6.  END;

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数，主要用于返回程序运行结果

IN OUT 传入传出参数

触发器

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

触发器可用于

数据确认

实施复杂的安全性检查

做审计，跟踪表上所做的数据操作等

数据的备份和同步

触发器分类：

前置触发器  后置触发器

创建触发器的语句：

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

FOR EACH ROW作用是标注此触发器是行级触发器，即每影响一行数据就就会触发一次触发器。与之相对应的是语句级触发器，即每执行一条语句就会触发一次触发器。

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