Oracle数据库整体简介

数据库基础概念

管理数据的发展历程：人工管理数据、文件系统管理数据、数据库系统管理数据。

数据库管理数据的模型：层次模型、网状模型、关系模型。

数据库概念：存放数据的仓库。

数据库系统：采用数据库技术的计算机系统，包括数据库和数据库管理系统。

数据库系统由下列几部分组成：

1. 计算机硬件：提供基本的操作环境。
2. 数据库（DB）：保存各种各样的数据。
3. 数据库管理系统（DBMS）：提供管理数据库的功能。
4. 数据库管理员（DBA）：利用数据库管理系统管理数据库。

Oracle数据库

Oracle数据库是一种关系型数据库管理系统（RDBMS），采用关系模型（二维表）管理数据，利用SQL（结构化查询语言）对数据进行增删改查以及进阶的操作。

Oracle数据库逻辑结构：

Oracle数据库是由多个的表空间组成。

默认有以下五种：

1. SYSTEM
   系统表空间，存放系统内部表和数据字典等。
2. SYSAUX
   系统扩展表空间，降低SYSTEM的负荷，存放数据字典之外的对象。
3. TEMP
   临时表空间，存放临时表和临时数据，多用于排序和汇总等。
4. UNDO
   撤销表空间，存放撤销信息的表空间。
5. USERS
   用户表空间，给用户使用的表空间。

每个表空间又由多个段（数据对象：表、索引、视图等）组成，段由数据区构成，数据区由数据块构成。

其中数据区是逻辑最小单位，数据块是物理最小单位。

Oracle物理结构

就是由各种各样的数据文件构成。

主要的数据文件

1. 数据文件（.dbf）
   用于保存用户数据和系统内部数据的文件。

2. 控制文件（.ctl）
   保存数据库名、数据文件、日志文件的名字和位置以及数据库相关信息。

3. 日志文件（.log）

    1. 重做日志文件
    2. 归档日志文件
    3. 警告日志文件
   

   记录对数据和数据库的修改操作。

4. 参数文件

5. 口令文件

Oracle实例

oracle实例也被称为服务器，是在计算机系统上的一套进程集以及相应的内存区域，用于管理和操作数据库。

系统全局区：oracle使用的内存区域

1. 高速数据缓冲区
   保存从数据文件中读取的数据的副本。
2. 重做日志缓冲区
   保存对数据库操作的日志信息。
3. 共享池
   缓存SQL语句、PL/SQL语句、数据字典、资源锁、字符集等其他控制结构。
4. 大型池
   存储大内存的配置。
5. Java池
   提供支持Java对象实例的空间。
6. 流池
   用于数据库与数据库之间共享信息的空间。

后台进程：有200多个，但只有5个必要的进程。

1. 数据写入进程（DBWR）
   将高速数据缓冲区的脏数据块写入到数据文件中。
   脏数据块：就是被修改过的数据。
   触发条件：

   1. 高速数据缓冲区满了
   2. 检查点进程启动
   3. 脏数据块在高速数据缓冲区存放超过3秒
   

2. 检查点进程（CKPT）
   联系日志写入和数据写入进程，命令数据写入进程工作。
   触发条件：
   日志写入进程发生日志切换动作

3. 日志写入进程（LGWR）
   将重做日志缓冲区的日志信息写入日志文件中。
   触发条件：

   1. 发生提交命令
   2. 重做日志缓冲区满1/3
   3. 日志信息存放超过3秒
   

   日志写入进程分两种模式：归档模式和非归档模式
   

4. 系统监控进程（SMON）
   利用强制性手段保证数据库系统的正常运行。

5. 进程监控进程（PMON）
   监控其他进程的状态，有进程启动失败时立刻清除进程并释放资源。

表结构与数据类型

表结构

二维表由行和列组成。

列由数据类型和长度构成。

行由多个字段组成，字段是列的值。

数据类型

数据类型	value
CHAR	固定长度
VARCHAR2	可变长度
NUMBER	数字，可限制长度和小数位数
DATE	日期
BLOB	二进制对象，音频、视频、图像等
CLOB	字符型对象，最大128MB
BFILE	二进制文件对象,存放文件的物理位置，最大128MB
ROWID	伪列，存放实际数据文件的位置，加快速度
XMLType	用于读取XML文档和管理节点，有很多内置函数。创建时使用sys.XMLType

选择、投影、连接

类型	含义
选择	将符合条件的行选择出来
投影	将某些列截取出来
连接	将两个表连接起来并按照条件选取数据

事务

概念：事务是由一系列语句构成的逻辑工作单元。

事务的工作流程是单步操作，当发生错误时可以定点回滚恢复数据，若是在事务执行过程中出现致命错误则立即中断执行回滚数据。因此事务的结果有两种，一是成功执行数据被修改，二是执行失败数据恢复执行前状态。

事务的特性

特性	含义
原子性	事务是一个整体，对数据库操作要么全部成功要么全部取消
一致性	数据在事务完成时必须都保持一致的状态
隔离性	当一个事务操作数据时其他事务不可以对该数据进行修改
持久性	事务对数据库的改变应该是永久性的

事务的状态

活动状态：事务执行时的状态。

部分提交状态：事务执行完成，实际输出在内存中并未持久化时的状态。

失败状态：事务不能正常执行的状态。

提交状态：事务执行成功并持久化完毕的状态。

中止状态：事务执行失败，数据回滚到执行前的状态。

事务的结束：要么执行COMMIT，要么执行ROLLBACK。

方式
执行COMMIT语句提交事务
执行ROLLBACK语句回滚事务
执行数据定义语句，成功Oracle自动执行COMMIT，失败Oracle自动执行ROLLBACK
执行数据控制语句，成功Oracle自动执行COMMIT
正常断开数据库连接，成功Oracle自动执行COMMIT，失败Oracle自动执行ROLLBACK

回退点：可以利用savepoint保存想要回退的时间点，然后用rollback to 回退点进行回滚。

锁

为在并发环境下保护数据的线程安全性，很多语言都提供锁机制以确保数据不会在同一时间被多个事务同时操作而导致混乱的情况。

Oracle提供两种锁机制：独占锁和共享锁

独占锁：对于某数据，同一时间只有拥有该数据独占锁的事务可以处理它，若想切换事务处理，必须等有锁的事务释放他的锁。

共享锁：可以指定某些事务共享同样的资源，可以提高并发性能。

死锁：死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。

死锁产生条件：

条件	说明
互斥条件	即在一段时间内某资源只由一个进程占用，如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
请求和保持条件	指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
不剥夺条件	指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
循环等待条件	指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

SQL语句集

数据库

1. 创建数据库

   CREATE DATABASE 数据库名
   [ USER 用户名 IDENTIFIED BY 密码 ]
   [ CONTROLFILE REUSE ]
   [ LOGFILE[GROUP integer]日志文件,... ]
   [ MAXLOGFILES 整数 ]
   [ MAXLOGMEMBERS 整数 ]
   [ MAXLOGHISTORY 整数 ]
   [ MAXDATAFILES 整数 ]
   [ MAXINSTANCES 整数 ]
   [ ARCHIVELOG | NOARCHIVELOG ]
   [ CHARACTER SET 字符集 ]
   [ NATIONAL CHARACTER SET 民族字符集 ]
   [ FORCE LOGGING ]
   [ DATAFILE 数据文件,... ]
   [ SYSAUX DATAFILE 数据文件,... ]
   [ DEFAULT TABLESPACE 表空间名 ]
   [ DEFAULT TEMPPORARY TABLESPACE 临时表空间名 TEMPFILE 临时文件名 ]
   [ UNDO TABLESPACE 撤销表空间名 DATAFILE 文件名 ]
   [ SET TIME_ZONE='time zone' ]
   

2. 修改数据库

   ALTER DATABASE 数据库名 操作代码
   

3. 删除数据库

   DROP DATABASE 数据库名
   

4. 设定系统表空间

   ALTER SYSTEM SET 系统表空间标识=表空间名
   

   系统表空间标识是指五种表空间在Oracle中的表示。

表空间

1. 创建表空间

   CREATE [SMALLFILE|BIGFILE|UNDO] TABLESPACE 表空间名
   DATAFILE '路径' SIZE 大小 REUSE
   [ AUTOEXTEND [ON | OFF] NEXT 大小 [ MAXSIZE [UNLIMITED | 大小]]]
   [ MININUM EXTENT 大小 ]
   [ DEFAULT STORAGE storage ]
   [ ONLINE | OFFLINE ]
   [ LOGGING | NOLOGGING ]
   [ PERMANENT | TEMPORARY ]
   [ EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM [SIZE 大小 ]]]
   
   

2. 修改表空间

   ALTER TABLESPACE 表空间名 操作代码
   

3. 删除表空间

   DROP TABLESPACE 表空间名
   

表

1. 创建表

   CREATE TABLE [用户名.]表名(
   列名 类型 [约束],
   列名 类型 [约束],
   列名 类型 [约束],
   [CONSTRAINT 约束名 约束类型]
   ...)
   

   约束类型

   类型	Value
   非空约束	NOT NULL
   默认约束	DEFAULT(默认值)
   唯一约束	UNIQUE
   主键约束	PRIMARY KEY
   外键约束	FOREIGN KEY （本表列）REFERENCES 外表（列）

2. 修改表

   ALTER TABLE 表名 操作代码
   

   常用操作代码

   类型	Value
   ADD	添加新列,ALTER TABLE 表名 ADD(列名 类型 DEFAULT 默认值,…)
   MODIFY	修改列,ALTER TABLE 表名 MODIFY(列名 类型 DEFAULT 默认值)
   DROP COLUMN	删除列,ALTER TABLE 表名 DROP COLUMN 列名
   DISABLE CONSTRAINT	禁用约束,ALTER TABLE 表名 DISABLE CONSTRAINT 约束名
   ENABLE CONSTRAINT	启用约束,ALTER TABLE 表名 ENABLE CONSTRAINT 约束名
   DROP CONSTRAINT	删除约束,ALTER TABLE 表名 DROP CONSTRAINT 约束名

3. 删除表

   DROP TABLE 表名
   

数据操作

1. 增加表数据

   INSERT INTO 表名[列,...] VALUES(值,...)
   

   可以根据某个表进行多条数据插入

   INSERT INTO 表名[列,...] SELECT语句
   

   注意事项：列和值要一一对应，省略列时代表全部列。

   可以利用INSERT插入较少的XML文档，插入前要保证表里有XMLType类型的列。使用sys.XMLType.createXML()函数创建，括号里写XML文档内容。

2. 删除表数据

   DELETE FROM 表名 [WHERE 条件]
   

   TRUNCATE TBALE 表名 [DROP STORAGE | REUSE STORAGE ]
   

   说明：

   DELETE FROM 加条件表示只删除符合条件的数据。

   DELETE FROM 不加条件==TRUNCATE TABLE 。

   两者区别：DELETE FROM 支持回滚，TRUNCATE TABLE 不支持。

3. 修改表数据

   UPDATE 表名 SET 列=值,... [WHERE 条件]
   

   说明：

   UPDATE 加条件表示只更改符合条件的行，不加表示更新该列所有行。

4. 查询表数据

   SELECT [DISTINCT | ALL] [*|列,...] [INTO 表名]
   FROM 表、视图或其他可查询对象的名
   [各种JOIN [ON 条件]]
   [WHERE 条件]
   [GROUP BY 列 [HAVING 条件] ]
   [ORDER BY 列 [ASC|DESC] 
   

   说明：

   1. DISTINCT：消除结果重复行。
   2. * |列：表示结果取哪些列，可以用AS关键字给列起别名，也可以对列应用一系列函数。

部分函数	格式应用
AVG	格式：AVG(列),求某列的平均值，例：AVG(age)
COUNT	格式：COUNT(列),求某列的值的个数，例：COUNT(age)
MAX	格式：MAX(列),求某列的最大值，例：MAX(age)
MIN	格式：MIN(列),求某列的最小值，例：MIN(age)
SUM	格式：SUM(列), 求某列的和，例：SUM(age)
ASCII	格式：ASCII(字符),返回某字符的ASCII码，例：ASCII(‘C’)
CHR	格式：CHR(数字),返回某ASCII码对应的字符，例：CHR(97)
CONCAT	格式：CONCAT(S1,S2),连接两个字符串，例：CONCAT(‘Hello’,‘World’)
INITCAP	格式：INITCAP(S),将字符串内每个单词首字母大写，以空格、控制字符、标点符号进行区分，例：INITCAP(‘oh my god’)
INSTR	格式：INSTR(S1,S2[,I,J])判断第二个字符串在第一个字符串第J次出现的位置，I控制从S1字符串从左到右或从右到左哪个位置开数，返回值都是从左到右的位置。例：INSTR(‘HEHHO’,‘H’,2,1)
LENGTH	格式：LENGTH(S),返回字符串长度，例：LENGTH(‘HELLO’)
LOWER	格式：LOWER(S),将字符串全部小写，例：LOWER(‘HELLO’)
UPPER	格式：UPPER(S),将字符串全部大写，例：UPPER(‘hello’)
LTRIM	格式：LTRIM(s1,s2)，删除s1左边出现的s2（默认空格）字符，例：LTRIM(’###EAST###’,’#’)
RTRIM	格式：RTRIM(s1,s2)，删除s1右边出现的s2（默认空格）字符，例：RTRIM(’###EAST###’,’#’)
TRIM	格式：TRIM(s1,s2)，删除s1两边边出现的s2（默认空格）字符，例：TRIM(,’#’ from ‘###EAST###’)
REPLACE	格式：REPLACE(S1,S2[,S3]),用S3替换在S1出现的S2，默认空字符串。例如：REPLACE(‘Bad Luck Bad Gril’,‘Bad’,‘Good’)
SUBSTR	格式：SUBSTR(S,I[,J]),截取字符串，I表示起始位置，J表示长度。例如：SUBSTR(‘Message’,3,3)
CEIL	格式：CEIL(数字), 返回大于或等于该数的最小整数，例如：CEIL(7.3)
ROUND	格式：ROUND(数字,小数位数), 对该数进行四舍五入，保留指定小数位数。例如：ROUND(3.14159256,2)
POWER	格式：POWER(a,b),返回a的b次幂，例：POWER(2,3)
SYSDATE	格式：SYSDATE(),返回当前系统时间，例：SYSDATE()

3. INTO：将查询的结果插入到另一个表里，要求两表结构相同。
4. FROM：表示从对哪个表进行取数据，可以用空格给表起别名。
5. JOIN：连接两个表，用ON指定连接条件。

连接名称	格式和结果
内连接	[INNER] JOIN，直接通过on连接两个表
左外连接	LEFT [OUTER] JOIN，左表数据完整保留，将右表符合条件的数据融入到左表，不符合条件的左表空缺位置用null占位
右外连接	RIGHT [OUTER] JOIN，右表数据完整保留，将左表符合条件的数据融入到右表，不符合条件的右表空缺位置用null占位
完全外连接	FULL [OUTER] JOIN，各执行一次左、右外连接，将结果合并然后去除重复项
自然连接	NATURAL JOIN，以相同列名的相同值里连接两个表
交叉连接	CROSS JOIN，结果是笛卡尔积，即以一个表为基准，用该表的每一列去加上另一个表的每一行

6. WHERE：用于筛选符合条件的行。

可用判断方式	Value
比较运算符	>、<、=、!= | <>、>=、<=
模式匹配	LIKE _ | %, _表示仅有1个，%表示0或多个，例如：SELECT * FROM EMP WHERE ENAME LIKE ‘s%’
范围比较	[NOT] IN(值1，值2,…)
	[NOT] ANY(值1，值2,…)
	[NOT] BETWEEN 值1 AND 值2,包括两端
空值比较	IS NULL
子查询	在WHERE条件中加SELECT语句，如果返回结果是多行就要用范围比较

7. GROUP BY：根据某列进行分组，HAVING筛选符合条件的数据。
   HAVING可以应用聚合函数，WHERE不可以。
8. ORDER BY ：以某列或某几列为标准进行排序，默认升序ASC，DESC表示降序。

视图

视图相当于是基于表的屏幕，可以只看想看的部分，并且可以通过视图对表进行数据操作。视图分为可更新视图和只读视图。

可更新视图：可以进行增删改查操作。

条件：

• 没有使用连接函数、聚合函数和组函数。
• 创建视图的SELECT语句中没有聚合函数也没有GROUP BY、ORDER BY、START WITH子句以及DISTINCT关键字。
• 创建视图的SELECT语句不包括从基表通过计算所得的列。
• 视图不包含WITH READ ONLY。

只读视图：只可以进行查询操作。

1. 创建视图

   CREATE [OR REPLACE] VIEW 视图名 [列别名,...]
   AS 查询语句
   [WITH CHECK OPTION][CONSTRAINT 约束名]
   [WITH READ ONLY]
   

2. 修改视图

   修改视图也用创建视图的命令（带OR REPLACE）
   ALTER VIEW 是重新编译或验证现有视图

3. 删除视图

   DROP VIEW 视图名
   

索引

索引：快速找到每行的ROWID（伪列）值，索引都是建立在某列或某几列上的，作用就是加快数据访问速度。

索引分类

分类标准	类型
索引数据的存储方式	B树索引（默认）
	位图索引
	反向键索引
	基于函数的索引
索引的唯一性	唯一索引
	非唯一索引
索引列的个数	单列索引
	复合索引

B树索引：

采用平衡二叉树的索引，该索引保存的数据是升序排序并且访问数据所经过的路径是相同的。

位图索引：

所有数据可以用很少的值分类，例如人类可以分为男性和女性。

1. 创建索引

   CREATE [UNIQUE|BITMAP] INDEX 索引名 ON 表名(列)
   TABLESPACE 表空间名
   [STORAGE 表空间参数]
   [LOGGING|NOLOGGING]
   [COMPOTE STATISTUS]
   [NOCOMPRESS|COMPRESS(大小)]
   [NOSORT|REVERSE]
   [PARTITION|NOPARTITION]
   

2. 修改索引

   ALTER INDEX 索引名 操作代码
   

3. 删除索引

   DROP INDEX 索引名
   

PL/SQL

PL/SQL是Oracle的专用语言，在PL/SQL中可以定义变量、进行流程控制等，以此控制数据在语句中传输交流，提高操作效率。

格式

	[DECLARE]
	变量声明部分
	BEGIN
	语句执行部分
	[EXCEPTION]
	异常处理部分
	END;

声明部分

变量声明格式：变量名 类型[(长度):=初值]

常量声明格式：常量名 constant 类型:=初值

变量名命名规范：字母、数字和符号，最大30个字符，不区分大小写。

变量类型

类型	Value
数值类型	NUMBER
	PLS_INTEGER
	BINARY_INTEGER
字符类型	CHAR
	VARCHAR2
	LONG
	NCHAR和NVARCHAR2
日期类型	DATE
布尔类型	BOOLEAN
%TYPE	取某表的某列类型，例如：emp_name emp.ename%type
RECORD	变量集合，类似JAVA的枚举,例如：type emp_record is record(e numeber,a varchar2)
%ROWTYPE	取某表的一行的数据类型,例如：emp_rowtype emp%rowtype

执行部分

语句执行部分的结构：顺序结构、分支结构、循环结构。

分支结构：执行语句为空时可以用NULL代替。

• 单分支：

  IF 条件 THEN 执行语句 END IF;
  

• 双分支：

  IF 条件 THEN 执行语句 ELSE 执行语句 END IF;
  

• 多分支：

  1. if嵌套：

     IF 条件 THEN 执行语句 ELSE IF 条件 THEN 执行语句 ... END IF;
     

  2. case语句

     CASE 变量
     WHEN 值1 THEN 执行语句;
     WHEN 值2 THEN 执行语句;
     ELSE 执行语句;
     END CASE;
     

循环结构：

• loop循环：先执行语句，在判断条件跳出循环。

  LOOP
  执行语句
  END WHEN 条件
  END LOOP;
  

• while循环：先判断条件，在执行语句。

  WHILE 条件 LOOP
  执行语句
  END LOOP;
  

• for循环：可控制次数的循环，reverse的作用是变量从后向前取值。

   FOR 变量  IN [REVERSE] 值的范围（例如：1.\.100） LOOP
   执行语句
   END LOOP;
  

异常部分

异常分为系统预定义异常和自定义异常。

系统预定义异常是Oracle定义并封装好的异常类型，在程序段直接使用就可以。

自定义异常是我们根据实际需要定义并处理的异常，需要先在声明部分定义然后在使用。

自定义异常声明格式：异常名 exception

为内部异常类命名：PRAGMA EXCEPTION_INIT(异常名,Oracle错误号)

整体使用流程：

DECLARE
异常名 EXCEPTION;
PRAGMA EXCEPTION_INIT(异常名,错误号);
BEGIN
IF 条件 THEN
RAISE 定义的异常名;
END IF;
EXCEPTION
WHEN 定义的异常名 THEN
处理语句;
END;

函数

1. 创建和替换函数

   CREATE [ OR REPLACE] FUNCTION 函数名(接收参数1,...) RETURN 返回参数类型 
   IS [内部变量]
   变量声明部分
   BEGIN
   语句执行部分
   [EXCEPTION]
   异常处理部分
   END;
   

2. 调用函数
   放在PL/SQL语句中直接用函数名调用，一定要有一个变量去接收函数的返回值。

3. 删除函数

   DROP FUNCTION 函数名
   

游标

提供从表中检索数据并进行操作的对象，实际上就是一组基于表的结果集，有些许类似视图。

游标可分为显示游标和隐式游标，显示游标是我们自己定义并操作的，隐式游标是Oracle在执行一个SQL语句时自动创建的，使用时要把sql标识符显示写出来，例如：sql%found。

也可以分为静态游标和动态游标，静态游标就是在声明游标的同时把select语句写上，因此该游标所获得的结果集是固定的。动态游标是在使用时写select语句，所以动态游标获得的结果集是变化的。

显示游标使用过程

1. 声明静态游标：放在PL/SQL语句的declare部分。

   //输入参数格式：参数名 [in] 数据类型[:=|default默认值]
   CURSOR 游标名(输入参数,...)
   [RETURN 返回值类型]
   IS SELECT语句
   

2. 打开静态游标

   OPEN 游标名[(参数)]
   

   打开游标时会完成以下这些事：

   检查联编变量的取值。

   根据联编变量的取值，确定活动集。

   活动集的指针指向第一行。

3. 读取游标

   FETCH 游标名 INTO 变量
   

   可以使用%found属性判断游标指针所在行是否有值。

4. 关闭游标

   CLOSE 游标名 
   

5. 游标变量

   是一种动态游标，使用方式与静态游标相同，仅是语句不同。

   定义游标变量

   TYPE 类型名 IS REF CURSOR
   RETURN 返回类型
   

   打开游标变量

   OPEN 游标变量名 FOR SELECT语句
   

   读取和关闭游标变量和静态游标相同。

程序包

程序包就是一系列相关对象的组合，例如将某些相关功能的函数、存储过程等封装到一个程序包里，然后就可以通过该程序包调用内部的功能函数。使用程序包可以提高其内部的函数、变量等加载速度。

定义程序包规范：一定要先于创建程序包主题进行。

CREATE [OR REPLACE] PACKAGE 包名 IS
[DECLARE 变量];
[DECLARE 类型];
[DECLARE 游标];
[DECLARE 函数];
[DECLARE 存储过程];
END[PACKAGE];

在规范的函数和存储过程只有头部声明，没有函数体和存储过程主体。

程序包主体创建：实现定义的程序包规范

CREATE [OR REPLACE] PACKAGE BODY 包名 IS
[内部变量]
[游标主体]
[函数主体实现]
[存储过程实现]

存储过程

存储过程是有名字的PL/SQL语句块，可以有输入和输出。

1. 创建存储过程

   CREATE [OR REPLACE] PROCEDURE 过程名[(接收参数,...)] IS|AS
   声明部分
   BEGIN
   执行语句
   END;
   

2. 调用存储过程

   1.EXECUTE 过程名
   2.在PL/SQL语句块的执行部分直接存储名调用
   

3. 删除存储过程

   DROP PROCEDURE 过程名
   

触发器

是一种特殊的存储过程，可以关联数据库的某些事件（触发条件），在触发条件为真时触发器就会开始执行相关代码。

1. 创建触发器

   CREATE [OR REPLACE] TRIGGER 触发器名
   [BEFORE|AFTER|INSTEAD OF] 触发事件
   ON 表名|视图名|用户名|数据库名|SCHEMA
   [FOR EACH ROW[WHEN 条件]]
   DECLARE
   声明部分
   BEGIN
   执行语句
   END 触发器名
   

   FOR EACH ROW：行级触发器，所关联的触发事件每影响一行就执行一次触发器。
   触发事件可以有多个，中间用or相连接。

2. 启用 | 禁用触发器

   ALTER TRIGGER 触发器名 DISABLE|ENABLE;
   

3. 删除触发器

   DROP TRIGGER 触发器名
   

4. 触发器分类

   触发器可以分为三类：语句级触发器、替换触发器、系统事件触发器。

   语句级触发器：针对DML事件引起的触发器。

   项目	DML事件
   增加	INSERT
   改变	UPDATE
   删除	DELETE

   可以使用谓词（inserting、updating、deleting）判断具体的触发事件。谓词也可以判断到具体某列，例如：updating(列名)。

   替换触发器：仅用于视图的触发器，因为视图一般不允许用户使用DML操作，所以可以使用触发器来进行。替换触发器的时机采用INSTEAD OF。

   系统事件触发器：针对DDL事件和数据库系统引起的触发器。

   项目	DDL事件
   创建	CREATE
   修改	ALTER
   删除	DROP
   删除	TRUNCATE
   授权	GRANT
   改名	RENAME
   连接	COMMENT
   移除授权	REVOKE
   登陆	LOGON
   启动服务	STARTUP（只能AFTER）
   关闭服务	SHUTDOWN（只能BEFORE）
   服务出错	SERVERERROR（只能AFTER）

系统安全

用户

数据库的使用者，每个用户可以接触的功能不尽相同。

1. 创建用户

   CREATE USER 用户名 IDENTIFIED BY 密码
   [OR IDENTIFIED EXETERNALLY]
   [OR IDENTIFIED GLOBALLY AS 'CN=USER']
   [DEFAULT TABLESPACE 默认表空间]
   [TEMPORARY TABLESPACE 临时表空间]
   [QUOTA[大小][UNLIMITED]] ON 表空间1,...
   [PROFILES 概要文件名]
   [ACCOUNT LOCK OR ACCOUNT UNLOCK]
   

   注意事项：

   1. 创建用户时不指定默认表空间的话，Oracle会使用SYSTEM表空间作为用户默认表空间。
   2. 创建用户时不指定临时表空间的话，Oracle会使用临时表空间作为用户临时表空间。
   3. 新建的用户没有任何权限，为保证能连接数据库必须赋予其CREATE SESSION权限。
   4. 创建用户时没指定QUOTA子句，用户将不能在相应表空间创建数据对象。

2. 修改用户

   ALTER USER 用户名 操作代码
   

3. 删除用户

   DROP USER 用户名
   

权限

权限指明用户可对数据库进行的操作，可分为系统权限和对象权限，二者最大的区别就是面向的对象以及相关权限不同。

系统权限是在系统对数据库进行存取和使用的机制，实际上就是用户对数据的操作权限。

含义	权限列举（不完全）
连接数据库	SESSION
创建	CREATE
修改	ALTER
删除	DROP

对象权限是某一个用户对其他用户的表、视图、序列、存储过程、函数、包等的操作权限，实际上就是用户对表等数据对象的操作权限。

含义	权限列举（不完全）
执行	EXECUTE
查询	SELECT
修改	ALTER
更新	UPDATE
插入	INSERT

1. 授予权限

   GRANT 权限|所有 [ON 表等数据对象名] TO 用户名|角色名|PUBLIC [WITH ADMIN OPTION]
   

   多个权限之间用逗号隔开。

   采用（ON 表等）表示授予用户对象权限。

   WITH ADMIN OPTION：表示被授权者可以在授权给其他用户。

2. 回收权限

   REVOKE 权限|所有 [ON 表等数据对象名] FROM 用户名|角色名|PUBLIC
   

角色

角色是包括一个或多个权限的集合，不被某个用户拥有。

使用角色可以简化权限的管理，用一条语句就可以授予或回收一批用户的权限。

1. 创建角色

   CREATE ROLE 角色名 [NO IDENTIFIED | IDENTIFIED BY [密码]|[EXETERNALLY]|[GLOBALLY]]
   

2. 修改角色

   ALTER ROLE 角色名 操作代码
   

3. 设置角色是否生效

   角色创建之后是需要进行生效设置的，如果不生效，该角色连接的用户也不会有相应的权限。

   SET ROLE 角色名[IDENTIFIED BY 密码] 
   

4. 删除角色

   DROP ROLE 角色名	
   

概要文件

通过一些密码管理选项、密码管理功能确保用户的密码安全性。

使用概要文件可以实现的密码管理方式：账户锁定、密码过期时间、密码历史、密码复杂度。

CREATE PROFILE 概要文件名 LIMIT
//限制用户登陆系统可失败的次数
[FAILED_LOGIN_ATEMPTS 次数]
//指定用户失败后被锁定的天数
[PASSWORD_LOCK_TIME 天数]
//指定密码的有效天数
[PASSWORD_LIFE_TIME 天数]
//指定密码到期后可用的天数，称为宽限天数。宽限天数过去之后系统会强制要求改密码
[PASSWORD_GRACE_TIME 天数]
//指定密码可重用的天数，他与PASSWORD_REUSE_MAX冲突，只能用其中一个，另一个设UNLIMITED
[PASSWORD_REUSE_TIME 天数]
//指定密码能被重用之前必须改变的次数
[PASSWORD_REUSE_MAX 次数]
//指定验证密码的函数
[PASSWORD_VERIFY_FUNCTION 函数名]

数据字典

数据字典就是Oracle用于保存数据库内部信息、运行和管理信息的地方，实际来看就是一堆表，可以使用select语句查询其中的信息。

基本数据字典

数据字典名称	说明
dba_tablespaces	关于表空间的信息
dba_ts_quotas	所有用户表空间限额
dba_free_space	所有表空间中的自由分区
dba_segments	数据库中所有段的存储空间
dba_extents	数据库所有分区的信息
dba_tables	数据库所有表
dba_tab_columns	所有表、视图以及簇的列
dba_views	数据库所有视图
dba_synonyms	关于同义词的信息查询
dba_sequnences	所有用户序列信息
dba_constraints	所有用户表的约束信息
dba_indexs	关于数据库中所有索引的描述
dba_ind_columns	在所有表及簇上亚索索引的列
dba_triggers	所有用户的触发器信息
dba_source	所有用户的存储过程信息
dba_data_files	关于数据库文件的信息
dba_tab_grants/privs	关于对象的授权信息
dba_objects	数据库所有对象
dba_users	数据库所有用户信息

常用动态性能视图

数据字典名称	说明
v$database	描述关于数据库的相关信息
v$datafile	描述数据库使用的数据文件信息
v$log	有关重做日志组的信息
v$logfile	有关实例重置日志组文件名及其位置的信息
v$archived_log	记录归档日志文件的基本信息
v$archived_dest	记录归档日志文件的路径信息
v$controlfile	描述控制文件的信息
v$instance	记录实例的信息
v$system_parameter	显示实例当前有效的参数信息
v$sga	显示实例的sga区大小
v$sgastat	统计sga使用情况的信息
v$parameter	记录初始化参数文件所有项的值
v$lock	设置对象锁所有信息
v$session	记录有关会话的信息
v$sql	记录SQL语句的详细信息
v$sqltext	记录SQL语句的语句信息
v$bgprocess	显示后台进程信息
v$process	显示当前进程信息

备份和恢复

为应对数据的物理丢失和逻辑丢失，Oracle采用了相应的物理数据备份和逻辑数据备份，方便在操作失误或其他突发状况引起的数据库异常情况下恢复数据库数据保证，物理备份只是复制数据库的文件，不管其逻辑内容，逻辑备份则是解决该问题。

备份有三种标准方式：导出、脱机备份、联机备份。导出是逻辑备份，常用EXP和EXPDP；脱机备份和联机备份是物理备份，常用RMAN。脱机备份指备份时数据库系统要关闭，联机备份指备份时数据库系统可以打开。

RMAN备份

RMAN备份可以分为两种类型：完全备份和增量备份，完全备份是指对数据进行整体的备份，增量备份是指对变化的数据进行备份。控制文件和日志文件只可以进行完全备份。

启动RMAN：在cmd命令窗口下进行。

RMAN TARGET 用户名/密码

在RMAN中运行多条语句可以用RUN{语句…}。

1. 一致性备份数据库

   //关闭数据库实例
   SHUTDOWN IMMEDIATE
   //以MOUNT模式重新启动
   STARTUP MOUNT
   //备份数据库可以FORMAT对数据名格式化
   BACKUP DATABASE FORMAT '路径'
   //打开数据库
   ALTER DATABASE OPEN
   

2. 备份表空间

   BACKUP TABLESPACE 表空间名 FORMAT '路径'
   

3. 备份数据文件

   BACKUP DATAFILE 数据文件名 FILESPERSET 数量
   

4. 备份控制文件

   可以设置CONFIGURE CONTROLFILE AUTOBACKUP的属性为ON，这样RMAN就会启动自动备份功能。

   手动备份控制文件

   BACKUP CURRENT CONTROFILE
   或是
   BACKUP TABLESPACE 表空间名 INCLUDE CURRENT CONTROFILE
   

5. 备份归档重做日志

   BACKUP ARCHIVELOG ALL DELETE ALL INPUT
   

   在对其他对象备份时指定PLUS ARCHIVELOG属性会顺带备份归档重做日志。

增量备份

可以具有不同的级别，级别标识要不小于0。级别0的备份相当于一次完全备份，之后的高级别备份可以分为差异备份和累积备份，差异备份指仅备份与上次同级或低级的备份产生变化的数据，累积备份会备份上次低级备份以来所有变化的数据。主要区别就在于差异备份先找到上次同级，若没有才找上次低级。累积备份直接找上次低级。

BACKUP INCREMENTAL LEVEL=等级
FORMAT '路径'
[CUMULATIVE]TABLESPACE 表空间

CUMULATIVE：采用累积备份方式。

数据恢复：在RMAN环境运行。

1. NOARCHIVELOG模式数据库恢复

   RESTORE DATABASE
   

2. ARCHIVELOG模式数据恢复

   命令	含义
   RESTORE TABLESPACE 表空间	修复一个表空间
   RESTORE DATABASE	修复数据库的文件
   RESTORE DATAFILE 数据文件	修复数据文件
   RESTORE CONTROLFILE TO 路径	将控制文件备份修复到指定目录
   RESTORE ARCHIVELOG ALL 路径	将全部归档文件复制到指定目录
   RECOVER DATABASE	恢复整个数据库
   RECOVER DATAFILE 数据文件	恢复数据文件
   RECOVER TABLESPACE 表空间	恢复表空间

数据泵

用于实现逻辑备份和逻辑恢复的工具。

SQL*Loader可以将外部文件中的数据或其他数据库的数据加载到当前数据库中，使用这个工具导入数据时必须有一个控制文件.CTL和一个数据文件.DAT。

自由格式加载数据：数据没有固定格式。

例：自由格式加载TXT文件代码

1. 先创建表student

   create table student(
   stuno number(4),
   stuname varchar2(20),
   sex varchar2(4),
   old number(4)
   );
   

2. 新建TXT文件

    1001      东方      男      30
    1002      开心      女      25
   

3. 创建CTL文件

   load data
   infile 'TXT文件路径'
   into table student(
   stuno position(01:04) integer external,
   stuname position(11:14) char,
   sex position(21:22) char,
   old position(29:30)integer external
   )
   

4. 加载数据：cmd窗口下运行。

   sqlldr 用户名/密码 control=控制文件路径 log=日志文件路径
   

固定格式加载数据：数据有固定格式。

例：固定格式加载CSV(以逗号分割)文件代码

1. 先创建表person

   create table person(
   code number(4),
   name varchar2(20),
   sex varchar2(4),
   old number(4)
   );
   

2. 新建CSV文件
   

3. 创建CTL文件

   load data
   infile 'CSV文件路径'
   append into table persons
   fields terminated by ','
   (code,name,sex,old)
   

4. 加载数据：cmd窗口下运行。

   sqlldr 用户名/密码 control=控制文件路径 
   

闪回

闪回操作是在不对数据库进行完全恢复的情况下，对某个表或数据库进行指定恢复，能够使数据库在任何逻辑操作失误中迅速恢复。

闪回操作前提：

1. 设置DB_RECOVERY_FILE_DEST：确定闪回日志的存放路径。
2. 设置DB_RECOVERY_FILE_DEST_SIZE：指定恢复区大小，默认为空。
3. 设置DB_FLASHBACK_RETENTION_TARGET：设定闪回数据库的保存时间，单位分钟，默认是一天。
4. 设置数据库的模式为ARCHIVELOG，ALTER DATABASE ARCHIVELOG。
5. 设置闪回FLASHBACK打开，ALTER DATABASE FLASHBACK ON。

闪回数据库

FLASHBACK [STANDBY] DATABASE 数据库名
TO [SCN|TIMESTAMP] 时间表达式 | TO BEFORE [SCN|TIMESTAMP] 时间表达式

闪回数据表

FLASHBACK TBALE 表名
TO [BEFORE DROP[RENAME TO TABLE]] | [SCN|TIMESTAMP]时间表达式 [[ENABLE|DISABLE] TRIGGERS]

数据库连接

提供本地和远程数据库之间的连接交互操作，可分为公共和私有，公共就是全部用户都可以使用，私有就是只能某个用户使用。

1. 创建数据库连接

   CREATE [PUBLIC] DATABASE LINK 连接名
   [CONNECT TO 用户名 IDENTIFIED BY 密码]
   USING '远程数据库名'
   

2. 使用数据库连接

   在增删改查的操作中表名后加@连接名，例如：select * from emp@mylink。

3. 删除数据库连接

   DROP [PUBLIC] DATABASE LINK 连接名
   

序列

保存一段数字的对象，可用于插入数据。

1. 创建序列

   CREATE SEQUENCE 序列名
   [START WITH 开始数字]
   [INCREMENT BY 增量]
   [MINVALUE 最小数字|NOMINVALUE]
   [MAXVALUE 最大数字|NOMAXVALUE]
   [CACHE 缓存数字|NOCACHE]
   [CYCLE|NOCYCLE]
   [ORDER|NOORDER]
   

2. 使用序列

   序列有两个属性：NEXTVAL和CURRVAL。NEXTVAL表示下一个数字，CURRVAL表示当前数字，使用时可以用序列名.属性获取数字。

3. 修改序列

   ALTER SEQUENCE 序列名 操作代码
   

4. 删除序列

   DROP SEQUENCE 序列名
   

---

Author

小葳宝贝最爱吃饭

Reference

Oracle数据库管理与开发（慕课版） 人民邮电出版社