Oracle基础知识点速记

Oracle 数据库

善用 Ctrl+F 和 目录

启动数据库经历三个过程启动顺序

• nomount、mount、open
• 启动命令 startup

停止数据库经历三个过程停止顺序

• close、dismount、shutdown
• 停止命令shutdown immediate

---

数据库实例启动时

1. 先加载参数文件，然后分配 sga 内存，这时实例处于安装状态；
2. 然后访问控制文件，读出数据文件和日志文件信息；
3. 再打开数据文件和日志文件供用户访问。

数据库实例负责数据库中的数据操作，包含:

• 一组后台进程
• 一个大的共享内存区 SGA，

---

SGA 系统全局区

1. 共享内存结构，包括数据库的控制信息、数据信息；
2. 是实例的主要组成部分，最影响数据库性能；
3. 被所有服务器进程和后台进程共享。
4. 实例之间不能相互访问对方的 SGA 区，实例关闭的时候，系统回收内存，SGA 即消失。

SGA 主要包括以下的数据结构：

• 数据缓冲池(Database Buffer Cache)：存储从数据文件读取的数据块，数据修改在缓冲区完成

• 重做（REDO）日志缓冲区(Redo Log Buffer)：存储数据块变化的日志记录

• 共享池(Shared Pool)：包括库高速缓存(the Library cache)、数据字典缓存(the data dictionary cache)、SQL 执行计划、PLSQL 代码

• 大池(Large Pool)：用于大内存分配，常用做备用池

• 流池(Streams Pool — 10g 以上才有)

• Java 池(java Pool)：运行 Java 程序需要的内存空间

• 共享池(Shared Pool)、Java 池(java Pool)、大池(Large Pool)和流池(Streams Pool)这几块内存区的大小是相应系统参数设置而改变的，所以有通称为可变 SGA(Variable SGA)。

PGA 程序全局区

非共享内存区，包含的控制信息和数据信息专属于一个 Oracle 数据库进程

主要包括：

• 私有 SQL 区
• SQL 工作区
• 会话内存区

对于成天在数据库运行的应用，建议使用 PGA 自动内存管理。手动内存管理，适用于大型批处理作业

---

基本后台进程

• PMON 进程监控(Process Monitor)
  监控后台进程，当进程终止时执行进程恢复;
  客户端进程异常结束时，负责释放客户端进程占用资源；
  向监听器进程注册数据库实例信息。

• SMON 系统监控(System Monitor)
  负责系统级的清理任务，包括实例恢复、清除临时空间，以及在字典管理表空间负责合并空闲区。

• DBWn 数据写进程(Database Writer)
  在加载新数据出现数据缓冲区空间不足或检查点发生时，负责将数据缓冲区中的数据块写到磁盘文件

• LGWR 日志写进程(Log Writer)
  负责将日志缓冲区中日志记录写到在线日志文件中。

• CKPT 检查点进程(Checkpoint Process)
  负责更新控制文件和数据文件头中的检查点信息，并发信号通知 DBWn 写缓冲区中数据到磁盘文件

• ARCn 归档进程
  在日志切换发生时,归档进程复制在线日志文件内容到归档文件。

---

数据库的组成文件

控制文件、数据文件、日志文件

• 参数文件

数据库实例启动时读取参数文件
数据库的参数文件通常称之为初始文件，如果没有参数文件，我们就无法启动 Oracle 数据库。
这些文件告诉 oracle 实例在哪里可以找到控制文件，并且指定某些初始化参数，这些参数定义了某种内存结构有多大等设置。

• 控制文件

记录数据库状态和结构信息
这个文件是一个相当小的文件，告诉你数据文件、临时文件和重做日志文件在哪里，还会指出与文件状态有关的其他元数据。

• 日志文件

记录数据库的改变记录，可使用日志文件内容进行数据恢复
日志文件分为 redo 日志文件和归档日志文件，归档日志文件可以看成是 redo 日志文件的备份累积，在数据库开启归档模式下才会产生。
redo 日志文件中，将按照 scn 先后顺序，以重做条目的形式记录数据库的操作。

• 数据文件

包含数据库的存储结构
数据文件和重做日志文件是数据库中最重要的文件。
创建一个数据库至少会有三个数据文件，一个对应 SYSTEM 表空间，一个对应 USER 表空间，另一个对应 SYSAUX 表空间。

• 临时文件

用于临时表或数据磁盘排列

---

逻辑结构

逻辑结构包括
表空间、段、区、块

块：数据的基本存储单位

区：段空间分配的单位，由一组连续块组成

段：指数据段，是存储数据的空间

表空间：存储数据段的空间，对应到磁盘上的数据

• 一个区只属于一个段

• 一个段只能属于一个表空间

• 一个表空间对应多个数据文件

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

• 一个表空间可包含多个段

• 一个段可包含多个区

• 一个段包含的多个区可以分别属于不同的数据文件

• 每个区是由一组连续的数据块组成

• 一个数据文件包含多个区

• 区不能跨数据文件,只能属于一个数据文件

• 一个数据库块包含多个操作系统块

---

数据库语句

DML 语句

• 数据库操作语句(Data Manipulation Language)

Insert（插入）、Update（更新）、Delete（删除）、Select（选择）

TCL 语句

• 事务控制语句(Transaction Control Language)

Commit（事务提交）、Rollback（事务回滚）

DCL 语句

-数据控制语句(Data Control Language)

GRANT（分配权限）、REVOKE（收回权限）

DDL 语句

• 数据库模式定义语言
  (Data Definition Language)

create（添加）、alter（修改）、drop（删除）、truncate（删除）

---

主键外键

• 主键

作用：实现数据的 实体 完整性
列值不能为空、列值必须唯一、表上的主键只能有一个、主键列可以是多个列

• 外键

作用：实现数据的 参照 完整性
列值可为空、表上可有多个外键

---

常用类型

char

• 最长存储 2000 个字节

varchar2

• 最长存储 4000 个字节

number[(p[,s])]

• p 指定精度,取值范围 1~38,指小数点左右数字位数,
• s 指定小数位数,取值范围-84~127 若数据小数位数多于定义位数则进行四五入,
• number 类型数据占用空间最大 22 个字节,计算方法为 length=floor((p+1)/2)+1)。

用于存储大型的字符数据的大对象类型

clob

• 使用数据库字符集，最大可达 (4GB*(数据库块大小))
  如果块为 8KB,最大可达 32TB,数据变长。

nclob

• 使用国家字符集，最大可达 (4GB*(数据库块大小))
  如果块为 8KB,最大可达 32TB,数据变长

用于存储大型的二进制数据的大对象类型

blob

• 最大可达 (4GB*(数据库块大小))，如果块为 8KB,最大可达 32TB，数据变长可存储图片、影音等数据。

bfile

• 存储文件指针,文件存储在数据库外。

---

监听器

可在多个 IP 地址上监听
配置信息包括：IP 地址，协议，端口

• 启动 Isnrctl start
• 关闭 lsnrctl stop
• 查看状态 lsnrctl status
• 配置文件 listener.ora
• 修改配置后重启 lsnrctl reload
• 默认端口 1521

---

创建序列

• 需要 CREATE SEQUENCE 系统权限。序列的创建语法如下：

CREATE SEQUENCE 序列名
[INCREMENT BY n]
[start with n]
[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
[{cycle|nocycle}]
[{CACHE n| NOCACHE}];

---

创建同义词

• 需要 CREATE SYNONYM 系统权限。同义词的创建语法如下：

CREATE [OR REPLACE][public]
SYNONYM [schema.] synonym_name
FOR [schema.] object_name [@dblink];

---

主键约束（primary key constraint）

要求主键列数据唯一，并且不能为空。

• 语法是：

alter table 表名 add constraint 主键名 primary key(字段名);

例如在 student 表中，为字段名为 id（学生 id）的这一列添加主键约束：

alter table student add constraint pk_student primary key(id);

---

约束

唯一约束(unique constraint)

要求该列唯一，允许为空，但不能出现一个空值。

• 语法是：

alter table 表名 add constraint 约束名 unique(字段名);

例如在 student 表中，为字段名为 name（姓名）的这一列添加唯一约束:

alter table student add constraint uq_student unique(name);

外键约束(foreign key constraint)

用于两表间建立连接，需要指定引用主表的哪列。

• 语法是：

alter table 主表名 add constraint 外键名 foreign key(字段名) references 被引用的表名(字段名);

例如在 student 表中，为字段名为 gradeno（年级编号）的这一列添加外键约束，引用的外键为 grade 表中的 gno（年级编号）：

alter table student add constraint fk_student foreign key(gradeno) references grade(gno);

检查约束(check constraint)

检查约束：某列取值范围限制，格式限制等。如有关年龄的约束。

• 语法是：

alter table 表名 add constraint 约束名 check(约束条件);

例如在 student 表中，为字段名为 gender（性别）的这一列添加检查约束：

alter table student add constraint ck_student check(gender in(‘男’,‘女’));

这条语句的意思是在 gender（性别）这一列，数据只能是男或女。

---

函数

分析函数

使用 OVER 子句指定在哪个数据集上执行操作
使用 PARTITION BY 子句将数据集划分为多个分区
使用 ORDER BY 子句指定分析函数的排序顺序
使用 ROW 或 RANGE 窗口定义分析函数的计算范围

• RANK()：
  计算每行的排名，并返回排名相同的行相同的排名值，跳过下一个排名

• DENSE_RANK()：
  计算每行的排名，并返回排名相同的行相同的排名值，不跳过下一个排名

• ROW_NUMBER()：
  为结果集中的每一行分配一个唯一的行号

• SUM()：计算每个分区的总和

• AVG()：计算每个分区的平均值

• COUNT()：计算每个分区的行数

• MAX()：算每个分区的最大值

• MIN()：计算每个分区的最小值

• NTILE(n)：
  将结果集分割成 n 个相等的大小的组，并为每个行分配一个组编号

SELECT name, salary, NTILE(4) 
OVER (ORDER BY salary DESC) AS quartile 
FROM employees;

• LEAD(column, offset, default)：
  返回当前行 后 偏移量为 offset 的行的值。如果没有这样的行，则返回默认值

SELECT name, salary, LEAD(salary, 1, 0) 
OVER (ORDER BY salary DESC) AS next_salary 
FROM employees;

• LAG(column, offset, default)：
  返回当前行 前 偏移量为 offset 的行的值。如果没有这样的行，则返回默认值

SELECT name, salary, LAG(salary, 1, 0) 
OVER (ORDER BY salary DESC) AS previous_salary 
FROM employees;

• 分页

select \* from 表名 
offset 几行之后 rows 
fetch next 显示几行 rows only;

---

PL/SQL

• 语法

以DECLARE开始
类型定义与变量声明

[DECLARE]
  --声明部分  
  [变量名] [变量类型] := [真值]
BEGIN
  --执行部分
[EXCPTEION]
  --异常处理部分
END;

若想要将执行过程的结果显示到控制台，则需要启动SQL * PLUS的环境变量SERVEROUTPUT，语法：
SET SERVEROUTPUT ON [SIZE 缓冲区大小]

• 集合方法

1. COUNT
   COUNT 是函数，返回数组和嵌套表分配空间数目，返回联合数组元素数目。
2. DELETE
   DELETE 是过程，删除元素，可删除单个元素 DELETE(n)，也可删除范围内元素
   DELETE(m,n)。
3. EXISTS
   是函数,判断指定下标元素是否存在于集合中。
   EXISTS(i)
4. EXTEND
   EXTEND 是过程，分配存储空问。
   EXTEND分配1个空间。
   EXTEND(n)分配n个空间。
   EXTEND(n,i)分配n个空间，且将i指定下标对应元素内容复制到新分配的空问中。
5. FIRST
   FIRST 是函数，返回集合最低下标值。
6. LAST
   LAST 是函数，返回集合最高下标值。
7. LIMIT
   LIMIT 是函数，返回集合可以使用的最高下标值。
8. NEXT(n)
   NEXT(n) 是函数，返回基于当前下标的集合的下一个下标值。
   如： NEXT(current)
9. PRIOR (n)
   PRIOR(n)是函数，返回基于当前下标的集合的上一个下标值。
   如：PRIOR(current)
10. TRIM
    TRIM 是过程，删除集合中的下标值。
    TRIM:删除集合中最高元素。
    TRIM(D):从集合末端删除n个元素。

---

控制结构

1. 条件语句

• IF语句
  语法:

//单分支语句块
IF 条件 THEN
  --条件为真执行语句块
[ELSE 
  --条件为假执行语句块]
END IF;

//多分支语句块
IF 条件 THEN
  --条件为真执行语句块
ELSEIF 条件 THEN
  --条件为真执行语句块
[ELSE 
  --条件为假执行语句块]
END IF;

• CASE语句
  语法:

CASE true|false
  WHEN 条件1 THEN 结果1
  WHEN 条件2 THEN 结果2
  ELSE 默认结果
END CASE;

2. 循环语句

• 数值FOR循环
  语法:

FOR index IN [REVERSE] 开始数字...结束数字 LOOP
  --循环语句块;
END LOOP;

开始数字与结束数字必须是整型
REVERSE可以倒序循环

• 游标FOR循环
  语法:

FOR index IN [cursor[(parameter list)] | subquery] LOOP
  --循环语句块;
END LOOP;

• 简单储环
  简单循环要求管理循环素引和退出条件，语法如下：

LOOP
  --循环语句块;
  EXIT WHEN 退出条件;
END LOOP;

或是：

LOOP
  EXIT WHEN 退出条件;
  --循环语句块;
END LOOP;

其中EXIT WHEN 退出条件也可以写成如下形式：

IF 退出条件 THEN
  EXIT;
END IF;

• WHILE循环
  语法:

WHILE 循环条件 LOOP
  --循环语句块;
END LOOP;

---

异常处理

1. 异常捕获
   语法：

EXCEPTION
  WHEN ( predefined_exception | user_defined_exception | OTHERS ) THEN
    exception_handling_statement;

WHEN 语向后指定要捕获的异常，可以是
预定义异常 predefined_exception、
用户定义异常 user_defined_exception,
也可以是OTHERS,使用 OTHERS 可以捕获所有异常。

---

游标

隐式游标

PL/SQL块中的每条SQL语句实际上都是隐式游标。
每个DML语句执行后都可以使用%ROWCOUNT属性知道语句改变的行数

属性	说明
%FOUND	当访问到一行数据时返回TRUE
%ISOPEN	隐式游标总返回FALSE，对打开显示游标返回TRUE
%NOTFOUND	不能返回数据时，反回TRUE
%ROWCOUNT	返回行数

显式游标

FOR 循环中使用显式游标，可自动完成打开、获取和关闭，简单循环或是 WHILE 循环中需要使用 OPEN、FETCH 和 CLOSE 语句完成游标的打开、获取和关闭。
游标的基本操作语句如下：

• 游标定义语法：
  CURSOR cursor_name | (parameter_definition) ] IS AS subquery;
• 打开游标语法：
  OPEN cursor_name[ (parameter_value) ];
• 获取数据语法：
  FETCH cursor_name INTO variablel l, variablez, .. J;
• 关闭游标语法：
  CLOSE cursor_name;

---

函数

函数必须有返回值

• 语法

CREATE OR REPLACE FUNCTION function name [( 
param1 [IN][OUT][NOCOPY] sql_datatype | plsql_datatype [ {: = | DEFAULT} default_value]
[,…]
)]
RETURN {sql_datatype | plsql _datatype}
[ AUTHID { DEFINER | CURRENT_USER } ] //{ 默认权限 | 定义者权限 }
[RESULT_CACHE] //函数执行结果缓存到SGA，下次执行可以直接从SGA调用结果，提高性能
{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION;] //指定是否为自治事务
  declaration statements;
BEGIN
  execution_statements;
  RETURN variable;
[EXCEPTION]
  exception_ handling_statements;
END;

其中

• IN：输入参数

• OUT：输出参数，对副本操作（传值方式）；
  指定NOCOPY直接应用变量地址（传址方式）

• 例：取相对路径的文件名

CREATE OR REPLACE FUNCTION get_file_name(file_path IN VARCHAR2) 
RETURN VARCHAR2 
IS
  file_name VARCHAR2(100);
BEGIN
  file_name := SUBSTR(file_path, INSTR(file_path, '/', -1) + 1);
  RETURN file_name;
END get_file_name;

运行,输入相对路径
/usr/local/data/file.txt
输出结果：File name: file.txt

DECLARE
  file_path VARCHAR2(100) := '/usr/local/data/file.txt';
  file_name VARCHAR2(100);
BEGIN
  file_name := get_file_name(file_path);
  DBMS_OUTPUT.PUT_LINE('File name: ' || file_name);
END;

---

过程

过程不能作为操作数，也不能用于SQL语句
可以在局部声明块中定义局部的过程，
也可以在数据库中定义。

• 语法

CREATE OR REPLACE PROCEDURE procedure_name
[(
param1 [IN][OUT][NOCOPY] sql_datatype | plsql_datatype [ {: = | DEFAULT} default_value]
[,...]
)]
[AUTHID {DEFINER CURRENT_USER}]
{ IS | AS }
{PRAGMA AUTONOMOUS_ TRANSACTION;}
  declaration_statements;
BEGIN
  execut ion_ statements;
[EXCEPTION]
  exception_handling_statements;
END;
/

过程语法中的参数与函数语法中的参数意义相同，与函数的最大区别是没有返回值语句RETURN。
过程的执行可以在SQL * Plus中使用EXECUTE命令执行，
或在PL/SQL 程序中执行。
语法如下：
SQL> EXECUTE proc_1;
或

SQL> BEGIN
  proc_1;
END;

• 例：阶乘的过程：

CREATE OR REPLACE PROCEDURE factorial(n IN NUMBER, result OUT NUMBER) IS
  fact NUMBER := 1;
BEGIN
  IF n < 0 THEN
    result := NULL;
  ELSIF n = 0 THEN
    result := 1;
  ELSE
    FOR i IN 1..n LOOP
      fact := fact * i;
    END LOOP;
    result := fact;
  END IF;
END factorial;

输入求5的阶乘，输出结果：5! = 120

DECLARE
  n NUMBER := 5;
  result NUMBER;
BEGIN
  factorial(n, result);
  DBMS_OUTPUT.PUT_LINE(n || "! = " || result);
END;