Oracle 数据库基础：体系结构与 PL/SQL

FreeGuideOnline 最新 2026-06-13

Oracle 数据库核心概念

Oracle 数据库是一个关系型数据库管理系统 (RDBMS)，它将数据组织成表，并通过 SQL 进行访问。与其他数据库不同，Oracle 采用多租户架构（从 12c 开始），并强调事务一致性与高可用性。学习 Oracle 的第一步，是理解其逻辑存储结构（如表空间、段、区）与物理存储结构（如数据文件、控制文件、重做日志）之间的关系，然后掌握 PL/SQL 这一强大的过程式语言扩展。

体系结构全景

Oracle 的体系结构可以分解为三个主要部分：实例、数据库和服务器进程。实例由内存结构（SGA）与后台进程组成，数据库由物理文件构成，两者结合提供数据服务。

实例与数据库

实例 (Instance)：启动 Oracle 时，会在内存中分配一块共享区域，并启动一组后台进程，这就是实例。实例管理数据并提供访问入口。
数据库 (Database)：真正存储在磁盘上的物理文件集合，包括数据文件、控制文件、重做日志文件等。一个实例只能挂载一个数据库（单实例环境），但通过 RAC 可以让多个实例同时访问同一个数据库。

用简单的话说：实例是“大脑和心脏”（处理请求，协调资源），数据库是“身体”（实际保存数据）。

系统全局区 (SGA)

SGA 是实例启动时分配的一大块共享内存，所有用户进程都可以访问。它主要包含以下组件：

数据库缓冲区高速缓存 (Database Buffer Cache)：缓存从数据文件中读取的最近使用的数据块。如果查询的数据在缓存中（命中），则无需访问磁盘，极大提升性能。
重做日志缓冲区 (Redo Log Buffer)：记录对数据块的变更信息（重做项），用于实例恢复。它是一个循环缓冲区，内容会被 LGWR 后台进程周期性写入在线的重做日志文件。
共享池 (Shared Pool)：
- 库缓存 (Library Cache)：存储解析过的 SQL 语句、PL/SQL 代码以及它们的执行计划。相同的 SQL 可共享执行计划，减少硬解析开销。
- 数据字典缓存 (Data Dictionary Cache)：缓存表定义、用户权限等字典信息。
大池 (Large Pool)：为大型 I/O、备份恢复、共享服务器模式等提供独立的内存区，避免争夺共享池资源。
Java 池 (Java Pool)：若在数据库内运行 Java 存储过程，则此区域管理 Java 代码。

关键后台进程

这些进程维护数据库的物理状态与逻辑一致性：

进程缩写	进程名称	核心职责
DBWn	数据库写入进程	将 Buffer Cache 中修改过的数据块（脏块）写入数据文件。采用惰性写入策略，不是提交即写。
LGWR	日志写入进程	将 Redo Log Buffer 中的重做记录写入在线的重做日志文件。提交事务时 LGWR 必须将重做信息写入磁盘以保证持久性。
CKPT	检查点进程	更新控制文件和数据文件头部，记录当前系统的一个一致性点。当实例崩溃时，只需从此点开始应用重做日志进行恢复。
SMON	系统监控进程	实例启动时负责崩溃恢复，回收临时段，合并相邻的空闲空间等。
PMON	进程监控进程	清理异常终止的用户进程，释放其持有的锁和资源。
ARCn	归档进程	当数据库运行在归档模式下时，将已满的在线重做日志复制归档到指定位置，支持介质恢复。

物理与逻辑存储结构

物理文件：
- 数据文件 (Data Files)：保存表和索引的实际数据。
- 控制文件 (Control Files)：记录数据库的名称、文件位置、检查点信息等元数据，多路复用保障安全。
- 在线重做日志 (Online Redo Logs)：循环使用，记录所有数据变更，用于崩溃恢复。
- 参数文件 (Parameter Files)：定义实例启动时的配置参数。
逻辑存储（数据文件内按以下层级组织）： 表空间 (Tablespace) → 段 (Segment) → 区 (Extent) → 数据块 (Block)
- 表空间是逻辑存储的最大容器，一个表空间可包含多个数据文件。
- 段是某个特定对象（如表或索引）所分配的空间集合，一个段只能属于一个表空间。
- 区是一组连续的数据块，段空间不足时以区为单位进行空间扩展。
- 数据块是 Oracle 中最小的 I/O 单元，大小通常在创建数据库时设定（如 8KB）。

PL/SQL 基础

PL/SQL (Procedural Language/SQL) 是 Oracle 对 SQL 的过程化扩展。它将编程逻辑与 SQL 数据操作无缝集成，可编写函数、存储过程、包和触发器，运行在数据库服务器端，减少网络往返，提升性能。

程序块结构

一个 PL/SQL 程序由代号为“块”的基本单元组成。块有清晰的声明、执行和异常处理三部分：

DECLARE
   -- 可选：声明变量、常量、游标、用户定义异常
   v_employee_name VARCHAR2(100);
BEGIN
   -- 必选：可执行语句（SQL 语句、流程控制、赋值等）
   SELECT first_name || ' ' || last_name INTO v_employee_name
   FROM employees
   WHERE employee_id = 101;
   DBMS_OUTPUT.PUT_LINE('员工姓名：' || v_employee_name);
EXCEPTION
   -- 可选：错误处理
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('未找到该员工。');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('发生其他错误。');
END;

变量、常量和常用类型

声明变量使用 变量名类型 [NOT NULL] [:= 默认值]; 的形式。常量使用 CONSTANT 关键字：

DECLARE
   v_counter NUMBER := 0;            -- 数值变量
   v_status VARCHAR2(20) DEFAULT 'ACTIVE';
   v_hire_date DATE;
   c_tax_rate CONSTANT NUMBER(3,2) := 0.08;  -- 常量
   v_employee employees%ROWTYPE;    -- 用 %ROWTYPE 表示一整行记录类型
   v_dept_id employees.department_id%TYPE;  -- 用 %TYPE 锚定列类型

常用类型：NUMBER, VARCHAR2, DATE, BOOLEAN, CLOB, BLOB。PL/SQL 中的 VARCHAR2 最大支持 32767 字节，但数据库列限制依旧是 4000 字节。

流程控制

PL/SQL 提供标准条件判断与循环结构：

条件语句

IF salary > 10000 THEN
   bonus := salary * 0.1;
ELSIF salary > 5000 THEN
   bonus := salary * 0.05;
ELSE
   bonus := 500;
END IF;

CASE rating
   WHEN 'A' THEN remark := '优秀';
   WHEN 'B' THEN remark := '良好';
   ELSE remark := '一般';
END CASE;

循环语句

-- 基本循环 (用 EXIT 跳出)
LOOP
   counter := counter + 1;
   EXIT WHEN counter > 10;
END LOOP;

-- WHILE 循环
WHILE counter <= 10 LOOP
   -- 处理逻辑
   counter := counter + 1;
END LOOP;

-- FOR 循环 (自动管理索引)
FOR i IN 1..10 LOOP
   DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- 游标 FOR 循环 (隐式打开、提取、关闭游标)
FOR emp_rec IN (SELECT employee_id, last_name FROM employees) LOOP
   DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ' ' || emp_rec.last_name);
END LOOP;

游标

游标用于逐行处理查询结果集。有显式游标与隐式游标之分。显式游标步骤：声明、打开、提取、关闭。

DECLARE
   CURSOR emp_cursor IS
      SELECT employee_id, salary FROM employees WHERE department_id = 100;
   v_emp emp_cursor%ROWTYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO v_emp;
      EXIT WHEN emp_cursor%NOTFOUND;
      -- 加工每一行
      UPDATE employees SET salary = salary * 1.1 WHERE CURRENT OF emp_cursor;
   END LOOP;
   CLOSE emp_cursor;
   COMMIT;
END;

注意 WHERE CURRENT OF 只能用于带有 FOR UPDATE 子句的游标，用于定位修改当前行。对于一般查询，游标FOR循环更简洁且自动处理一切。

存储过程与函数

存储过程 (Procedure)：执行特定操作，可以没有返回值。
函数 (Function)：必须返回一个值，通常在 SQL 语句中作为表达式调用。

创建语法示例：

-- 过程
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_percent NUMBER)
IS
   v_old_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_old_salary FROM employees WHERE employee_id = p_emp_id;
   UPDATE employees SET salary = salary * (1 + p_percent)
   WHERE employee_id = p_emp_id;
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('工资从 ' || v_old_salary || ' 更新');
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
END update_salary;

-- 函数
CREATE OR REPLACE FUNCTION get_annual_salary(p_emp_id NUMBER)
RETURN NUMBER
IS
   v_salary NUMBER;
BEGIN
   SELECT salary * 12 INTO v_salary FROM employees
   WHERE employee_id = p_emp_id;
   RETURN v_salary;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_annual_salary;

调用：SELECT get_annual_salary(101) FROM dual; 或 PL/SQL 块内 v_sal := get_annual_salary(101);

包 (Package)

包将相关的存储过程、函数、变量、常量、游标等组织为一个逻辑单元，通过公开规范 (Specification) 和私有体 (Body) 分离接口与实现。包在首次调用时整体加载到内存，减少磁盘 I/O，提高性能。

-- 包规范
CREATE OR REPLACE PACKAGE emp_pkg IS
   FUNCTION calc_bonus(p_salary NUMBER) RETURN NUMBER;
   PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER);
   g_tax_rate CONSTANT NUMBER := 0.1;
END emp_pkg;

-- 包体
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
   FUNCTION calc_bonus(p_salary NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN p_salary * (1 - g_tax_rate) * 0.05;
   END calc_bonus;

   PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER) IS
   BEGIN
      UPDATE employees SET salary = salary * (1 + p_percent)
      WHERE employee_id = p_emp_id;
      COMMIT;
   END raise_salary;
END emp_pkg;

调用时使用包名点式：SELECT emp_pkg.calc_bonus(salary) FROM employees;

异常处理

PL/SQL 中错误分为预定义异常（如 NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX）和用户自定义异常。可以在 EXCEPTION 块中捕获处理。

DECLARE
   e_invalid_id EXCEPTION;
   v_id NUMBER := -1;
BEGIN
   IF v_id < 0 THEN
      RAISE e_invalid_id;
   END IF;
EXCEPTION
   WHEN e_invalid_id THEN
      DBMS_OUTPUT.PUT_LINE('无效的员工ID');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('错误代码：' || SQLCODE || ' 信息：' || SQLERRM);
      ROLLBACK;
END;

新手学习路线建议

环境搭建：安装 Oracle Database Express Edition (XE) 或使用 Oracle Live SQL 在线工具，练习 SQL 和 PL/SQL。
掌握基础 SQL：熟练 SELECT、INSERT、UPDATE、DELETE、COMMIT 和 ROLLBACK。
理解体系结构：能够说明实例与数据库的区别，描述 SGA 的组件，这是性能调优的根基。
编写 PL/SQL 块：从匿名块开始，学会声明变量、使用条件与循环、处理异常。
开发存储过程与函数：创建可重用代码，并通过包进行封装。
管理工具：使用 SQL*Plus 或 SQL Developer 管理数据库对象。

体系结构与 PL/SQL 是 Oracle 开发和管理的两大基石。深入理解实例如何工作以及如何在数据库中编写高性能的过程化逻辑，将为后续的性能优化、数据建模和高可用设计打下扎实基础。