Oracle 11g 数据库核心对象创建与管理详解

原创已于 2025-09-22 21:47:54 修改 · 1.9k 阅读

33 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2025-09-22 21:12:06 首次发布

引言

Oracle 11g 的数据库对象是构建和运行数据库系统的核心单元，不同对象各司其职，相互配合实现数据的高效存储、管理与应用。以下从核心对象的定义、作用、特性以及操作示例等方面展开详细说明。

1. 表（Table）

定义与作用

表是 Oracle 数据库中最基础的对象，用于永久存储结构化数据，所有其他数据库对象（如视图、索引）大多依赖表而存在。

关键特性

结构化存储：由"列（Column）"和"行（Row）"组成
- 列定义数据的类型（如VARCHAR2(50)、NUMBER(10)、DATE）、长度及约束
- 行对应一条具体的数据记录
约束保障完整性：通过约束确保数据准确性
- 主键（Primary Key）：唯一标识表中每条记录，非空且唯一。
- 外键（Foreign Key）：关联两个表，保证关联数据的一致性。
- 非空（NOT NULL）：限制列必须填写数据。
- 唯一（UNIQUE）：限制列的值唯一。

操作示例

创建表

-- 创建"用户表（USER_INFO）"
CREATE TABLE USER_INFO (
    USER_ID NUMBER(10) PRIMARY KEY,      -- 主键（用户ID）
    USER_NAME VARCHAR2(50) NOT NULL,     -- 用户名（非空）
    PHONE VARCHAR2(20) UNIQUE,           -- 手机号（唯一）
    CREATE_TIME DATE DEFAULT SYSDATE     -- 创建时间（默认取当前时间）
);

修改表

-- 添加邮箱字段
ALTER TABLE USER_INFO ADD EMAIL VARCHAR2(100);

-- 修改字段类型
ALTER TABLE USER_INFO MODIFY EMAIL VARCHAR2(150);

-- 删除字段
ALTER TABLE USER_INFO DROP COLUMN EMAIL;

2. 视图（View）

定义与作用

视图是基于一个或多个表（或其他视图）的虚拟表，本身不存储实际数据，仅保存 SQL 查询语句，相当于数据访问的 “过滤镜” 和 “简化器”。

关键特性

简化复杂查询：将多表关联、子查询等复杂逻辑封装成视图。
控制数据权限：通过视图隐藏表中的敏感字段（如密码、身份证号）。
数据独立性：底层表结构修改后，可通过调整视图保持对外接口不变。

操作示例

创建视图

-- 创建用户公开信息视图（隐藏敏感字段）
CREATE VIEW VIEW_USER_PUBLIC AS
SELECT USER_ID, USER_NAME, PHONE FROM USER_INFO;

创建视图

-- 像查询普通表一样使用视图
SELECT * FROM VIEW_USER_PUBLIC WHERE USER_NAME LIKE '张%';

3. 索引（Index）

定义与作用

索引是依附于表的辅助对象，通过构建"字段值-数据位置"的映射关系，让数据库快速定位目标数据，避免全表扫描，大幅提升查询效率（尤其表数据量大时）。

关键特性

依赖表字段：基于表的一个或多个字段创建，表删除时索引自动删除。
类型多样，适配不同场景：
- 普通索引（B-Tree Index）：默认类型，适用于等值查询（=）和范围查询（BETWEEN、>）。
- 唯一索引（Unique Index）：确保索引字段值唯一，可替代UNIQUE约束。
- 位图索引（Bitmap Index）：适用于字段值重复率高的场景（如"性别""状态"字段）。
trade-off 特性：提升查询效率的同时，会降低INSERT/UPDATE/DELETE操作效率（需同步维护索引）。

操作示例

创建索引

-- 创建普通索引（基于用户名）
CREATE INDEX IDX_USER_NAME ON USER_INFO(USER_NAME);

-- 创建唯一索引（基于手机号，确保手机号不重复）
CREATE UNIQUE INDEX IDX_USER_PHONE ON USER_INFO(PHONE);

-- 创建复合索引（基于“用户名+手机号”，适配多字段联合查询）
CREATE INDEX IDX_USER_NAME_PHONE ON USER_INFO(USER_NAME, PHONE);

删除索引

-- 删除无用索引（避免占用资源）
DROP INDEX IDX_USER_NAME;

4. 序列（Sequence）

定义与作用

序列是独立于表的数据库对象，用于生成连续且唯一的数字序列，主要作用是为表的主键（如USER_ID）自动赋值，避免手动维护主键唯一性的麻烦。

关键特性

独立性：不依赖任何表，可被多个表共用（如一个序列为"用户表"和"订单表"同时生成主键）。
可配置性：支持设置起始值、步长、最大值、循环规则、缓存规则。
调用方式：通过NEXTVAL（获取下一个序列值）和CURRVAL（获取当前序列值）调用。

操作示例

创建序列

-- 为用户表主键生成自增ID
CREATE SEQUENCE SEQ_USER_ID
START WITH 1          -- 起始值（从1开始）
INCREMENT BY 1        -- 步长（每次递增1）
NOCYCLE               -- 达到最大值后不循环（避免重复）
NOCACHE;              -- 不缓存序列值（防止数据库异常时丢失缓存值）

使用序列

-- 插入数据时使用序列赋值主键
INSERT INTO USER_INFO (USER_ID, USER_NAME, PHONE)
VALUES (SEQ_USER_ID.NEXTVAL, '张三', '13800138000');

-- 查看当前序列值（需先调用NEXTVAL，否则报错）
SELECT SEQ_USER_ID.CURRVAL FROM DUAL;

5. 存储过程（Stored Procedure）

定义与作用

存储过程是预先编写好并存储在数据库中的 SQL 代码块，通过"调用"即可执行，主要用于封装复杂业务逻辑（如多表联动更新、批量数据处理）。

关键特性

封装性：将多步 SQL 操作（如插入数据 + 记录日志）封装成一个单元，隐藏实现细节。
高效性：在数据库端执行，减少应用与数据库的网络传输（尤其大数据处理场景）。
可复用性：可被多个应用程序（如 Java 后端、Python 脚本）重复调用，降低代码冗余。
事务控制：支持在存储过程中显式提交（COMMIT）或回滚（ROLLBACK）事务。

操作示例

创建存储过程

-- 创建存储过程：新增用户（含日志记录）
CREATE OR REPLACE PROCEDURE PRO_ADD_USER(
    p_user_name IN VARCHAR2,   -- 输入参数：用户名
    p_phone IN VARCHAR2,       -- 输入参数：手机号
    p_result OUT VARCHAR2      -- 输出参数：执行结果（success/fail）
) AS
BEGIN
    -- 1. 插入用户数据（使用序列赋值主键）
    INSERT INTO USER_INFO (USER_ID, USER_NAME, PHONE)
    VALUES (SEQ_USER_ID.NEXTVAL, p_user_name, p_phone);
    
    -- 2. 记录操作日志（假设已存在日志表OPER_LOG和日志序列SEQ_LOG_ID）
    INSERT INTO OPER_LOG (LOG_ID, OPER_TYPE, OPER_CONTENT, OPER_TIME)
    VALUES (SEQ_LOG_ID.NEXTVAL, '新增用户', '添加用户：'||p_user_name, SYSDATE);
    
    -- 3. 提交事务
    COMMIT;                    
    p_result := 'success';     -- 设置输出结果为“成功”

-- 异常捕获：出错时回滚事务并返回错误信息
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;              -- 回滚未提交的操作
        p_result := 'fail: '||SQLERRM;  -- 拼接错误信息（SQLERRM为系统错误描述）
END PRO_ADD_USER;

调用存储过程

-- 调用存储过程并查看结果
DECLARE
    v_result VARCHAR2(100);    -- 定义变量接收输出结果
BEGIN
    -- 调用存储过程，传入参数
    PRO_ADD_USER('李四', '13900139000', v_result);
    -- 输出执行结果（需开启DBMS_OUTPUT功能）
    DBMS_OUTPUT.PUT_LINE('执行结果：'||v_result);
END;