Oracle数据库学习：用SQL完成数据增删改查的实操练习

增删改查通常简称 CRUD，是使用关系型数据库时最基础的一组操作：查询（SELECT）、插入（INSERT）、更新（UPDATE）和删除（DELETE）。学会它们，不只是记住几条 SQL 语句，还要知道数据类型如何匹配、哪些改动需要提交、如何避免误更新整张表，以及应用程序怎样安全地传入参数。

本文以 Oracle 数据库中的员工表为例，逐步完成一组可练习的 SQL 操作。示例使用 Oracle 12c 及以上版本支持的标识列（identity column）。如果使用较老版本，可将建表部分替换为后文的序列方案。建议在测试库或个人练习 schema 中运行，不要直接对生产数据照抄修改和删除语句。

一、准备练习表

员工表包含主键、姓名、部门、薪资、入职日期和邮箱。主键保证每条记录有唯一标识；姓名为必填；邮箱设置唯一约束，避免相同邮箱重复录入。薪资约束用于拒绝负数，部门允许为空。

CREATE TABLE employees (
    employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    full_name   VARCHAR2(100) NOT NULL,
    department  VARCHAR2(50),
    salary      NUMBER(10, 2),
    hire_date   DATE DEFAULT SYSDATE NOT NULL,
    email       VARCHAR2(320),
    CONSTRAINT employees_pk PRIMARY KEY (employee_id),
    CONSTRAINT employees_salary_ck
        CHECK (salary IS NULL OR salary >= 0),
    CONSTRAINT employees_email_uk UNIQUE (email)
);

GENERATED BY DEFAULT AS IDENTITY 表示通常由 Oracle 生成主键值，但插入时也允许显式提供主键。若不希望调用方手动指定标识值，可以选择 GENERATED ALWAYS AS IDENTITY。标识列可用的选项和行为应以目标 Oracle 版本为准。

NUMBER(10, 2) 最多容纳十位有效数字，其中小数部分最多两位；它不是“薪资范围”的业务校验，超出精度或违反检查约束时，插入或更新会报错。VARCHAR2(100) 的长度语义可能受数据库字符集和列长度语义设置影响；实际系统应结合字符集、字段需求和部署配置确定长度，不要把示例长度当作所有场景的通用标准。

邮箱列上的唯一约束也不是完整的邮箱格式校验。它用于维护唯一性，不能代替应用层格式校验。Oracle 唯一约束允许多个 NULL 值，因此没有邮箱的记录可以同时存在多条。

较老版本的序列方案

如果目标版本不支持标识列，可以创建序列，并在插入时调用 NEXTVAL。示例中的初始值和递增值可按项目需要调整。

CREATE SEQUENCE employees_seq
    START WITH 1
    INCREMENT BY 1;

使用序列插入时，需要在每条 INSERT 中显式填写主键列：

INSERT INTO employees (
    employee_id, full_name, department, salary, hire_date, email
) VALUES (
    employees_seq.NEXTVAL, '林晓', '研发部', 18000, DATE '2023-06-01',
    '[email protected]'
);

序列产生的值通常不会因为事务回滚而退回，因此主键出现间隔是正常现象，不应假设序列值连续，也不应用连续编号推断记录是否丢失。

二、查询数据：SELECT

最简单的查询是读取表中的全部列和全部行：

SELECT *
FROM employees;

练习时使用 * 很方便；应用代码和长期维护的查询更适合明确列名。明确列名可以减少不必要的数据读取，也能避免表结构增加列后，查询结果列数和顺序意外变化。

SELECT employee_id, full_name, department, salary, hire_date
FROM employees;

筛选、排序和别名

使用 WHERE 限定结果，使用 ORDER BY 排序。没有指定排序条件时，数据库不保证返回行的顺序。

SELECT employee_id, full_name, salary
FROM employees
WHERE department = '研发部'
  AND salary >= 15000
ORDER BY salary DESC, employee_id ASC;

上例先按薪资从高到低排列；薪资相同时，再按员工编号从小到大排列。为分页或需要稳定顺序的查询补上唯一的排序键（例如主键），有助于避免同值记录之间顺序不确定。

可以为表达式或列设置别名，便于阅读结果：

SELECT full_name AS employee_name,
       salary * 12 AS annual_salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY annual_salary DESC;

这里用 IS NOT NULL 检查空值。SQL 中 NULL 表示缺失或未知，不等同于数字 0、空字符串或普通文本。判断空值要用 IS NULL 或 IS NOT NULL，不能写 salary = NULL；与 NULL 的普通比较结果不是 TRUE，因此不会按预期筛选出空值。

Oracle 会把零长度字符值按 NULL 处理。若业务必须区分“没有输入”和“空文本”，不能仅依赖 VARCHAR2 列保存这两种状态，应另行设计表示方式。

条件组合与范围查询

可用 AND、OR 和括号组合筛选条件。为了让逻辑清晰，尤其在同时使用 AND 与 OR 时，建议显式写括号。

SELECT employee_id, full_name, department
FROM employees
WHERE (department = '研发部' OR department = '数据部')
  AND salary >= 12000;

使用 BETWEEN 查询范围时，两端都包含在范围内：

SELECT employee_id, full_name, hire_date
FROM employees
WHERE hire_date BETWEEN DATE '2023-01-01' AND DATE '2023-12-31'
ORDER BY hire_date;

DATE 'YYYY-MM-DD' 是 Oracle 支持的日期字面量写法，不依赖会话的日期显示格式。Oracle 的 DATE 类型还保存时分秒，所以这里的结束值是 2023 年 12 月 31 日零点；如果列中包含当天其他时间，可能无法覆盖整天。对带时间的日期列，常见写法是使用左闭右开的时间范围：

SELECT employee_id, full_name, hire_date
FROM employees
WHERE hire_date >= DATE '2023-01-01'
  AND hire_date <  DATE '2024-01-01';

分页查询

Oracle 12c 及以上版本可以使用行限制语法：

SELECT employee_id, full_name, salary
FROM employees
ORDER BY employee_id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

将偏移量改为 10 可跳过前十行；将取回行数改为 20 可请求接下来的二十行。分页应结合稳定的 ORDER BY 使用，否则不同执行中同一行可能出现在不同位置。大偏移量也可能带来较高查询成本，可根据数据规模考虑基于主键或排序键的“游标式”分页。

较老版本通常需要用子查询和 ROWNUM 实现分页。ROWNUM 是 Oracle 伪列，过滤顺序与排序组合时需要特别留意；不能简单地在同一层先写 ORDER BY 再用 ROWNUM，并期待它代表排序后的行号。

SELECT employee_id, full_name, salary
FROM (
    SELECT employee_id, full_name, salary
    FROM employees
    ORDER BY employee_id
)
WHERE ROWNUM <= 10;

此例演示取排序后的前十行，并不包含任意页码的完整分页。需要查询后续页面时，通常还要增加嵌套查询和行号范围条件，或使用目标版本支持的行限制语法。

三、插入数据：INSERT

使用标识列时，通常省略主键，让 Oracle 自动生成。INSERT 最好显式列出目标列，不要依赖表中列的默认顺序。

INSERT INTO employees (
    full_name, department, salary, hire_date, email
) VALUES (
    '林晓', '研发部', 18000, DATE '2023-06-01', '[email protected]'
);

语句成功执行后，修改通常仍在当前事务中，是否持久化取决于后续的 COMMIT 或 ROLLBACK。不同客户端对自动提交的默认设置可能不同，练习前应确认工具的事务行为。

要插入多条记录，可以分别执行多条 INSERT。也可以通过 INSERT ALL 写入多行：

INSERT ALL
    INTO employees (full_name, department, salary, hire_date, email)
    VALUES ('周宁', '数据部', 16000, DATE '2022-09-15', '[email protected]')
    INTO employees (full_name, department, salary, hire_date, email)
    VALUES ('陈可', '产品部', 14000, DATE '2024-02-20', '[email protected]')
SELECT 1 FROM dual;

DUAL 是 Oracle 提供的一行单列表，常用于需要 SELECT 来源但不需要实际业务表的场景。插入多行时，每行仍需满足 NOT NULL、唯一约束、检查约束和列类型要求；其中任何一行违反约束，整条 INSERT 语句都会失败。

DEFAULT 与 NULL 的区别

如果列有默认值，插入时省略该列，通常会使用默认值；也可以在适用场景中使用 DEFAULT。显式插入 NULL 通常不会触发默认值。

INSERT INTO employees (full_name, department, salary, email)
VALUES ('王敏', '运营部', 9000, '[email protected]');

上例省略 hire_date，由表定义中的 DEFAULT SYSDATE 提供值。相反，若将 hire_date 显式写入列清单并传入 NULL，因为该列同时定义了 NOT NULL，语句会失败。

四、更新数据：UPDATE

UPDATE 用于修改已有行。下面把指定员工的部门和薪资改为新值：

UPDATE employees
SET department = '数据部',
    salary = 17000
WHERE employee_id = 1;

最重要的边界是 WHERE：省略 WHERE 会更新表中所有行。执行前可先把条件改写成 SELECT，检查命中的记录是否符合预期：

SELECT employee_id, full_name, department, salary
FROM employees
WHERE employee_id = 1;

还可以使用多列条件收窄范围：

UPDATE employees
SET salary = 17500
WHERE employee_id = 2
  AND department = '研发部';

若员工部门已变化，该语句可能影响零行。零行不一定是 SQL 错误，但应用通常应检查影响行数，判断记录是否不存在、条件是否不匹配，或数据是否已被其他操作修改。

有条件的批量更新同样应谨慎。例如：

UPDATE employees
SET salary = salary * 1.05
WHERE department = '研发部'
  AND salary IS NOT NULL;

这会将符合条件员工的薪资增加 5%，并且每次执行都会再次增加 5%。如果脚本可能重跑，应先设计幂等处理方式，或在事务中核实受影响行数，避免重复执行造成累积变更。

五、删除数据：DELETE

DELETE 删除满足条件的行，但保留表结构。练习时可以先查询目标，再执行删除：

SELECT employee_id, full_name, email
FROM employees
WHERE employee_id = 3;

DELETE FROM employees
WHERE employee_id = 3;

不带 WHERE 的 DELETE 会删除表中的所有行：

DELETE FROM employees;

它不会删除表本身；删除表结构是另一类 DDL 操作，例如 DROP TABLE，风险和影响都更大。即使带 WHERE，条件写错或匹配范围过宽，也可能删除不该删除的数据。正式操作前应核对筛选结果和影响行数，并确认备份、权限及审批要求符合组织规范。

如果其他表通过外键引用员工记录，删除可能因引用约束而失败。只有外键明确配置了级联删除等规则时，相关记录才可能按规则一并处理；不要假设删除主表行会自动清理所有关联数据。级联规则涉及数据保留和审计要求，应由数据模型明确设计。

六、事务控制：COMMIT 与 ROLLBACK

在 Oracle 中，常见 DML 操作（如 INSERT、UPDATE、DELETE）会参与事务。COMMIT 提交当前事务，使事务中的修改成为持久结果；ROLLBACK 撤销当前事务中尚未提交的修改。多条相关 DML 可以放在同一事务中，保证要么一起提交，要么在提交前一起撤销。

UPDATE employees
SET salary = salary + 500
WHERE employee_id = 1;

DELETE FROM employees
WHERE employee_id = 99;

COMMIT;

如果执行更新后发现条件选错，而且尚未提交，可以执行：

ROLLBACK;

但回滚只能撤销当前事务中未提交的改动，不能撤销已经提交的事务。部分 DDL 语句会引发隐式提交，因此不要把建表、改表和普通 DML 混在一个事务中，并期待一次 ROLLBACK 撤销所有操作。具体客户端的自动提交行为也会影响练习结果：有些工具或连接配置可能在语句后自动提交，应先确认设置。

一个较安全的人工操作流程是：先用 SELECT 检查目标集合；在事务中执行 UPDATE 或 DELETE；检查影响行数，并再次查询结果；确认无误后 COMMIT，不符合预期则在提交前 ROLLBACK。生产环境还需遵循最小权限、备份恢复和变更审批要求，事务本身并不替代这些措施。

七、应用程序中的参数与 SQL 注入防护

在应用程序中，用户提供的筛选值应通过驱动程序的绑定变量传入，而不是拼接进 SQL 字符串。绑定变量有助于将 SQL 结构与数据值分开，并通常能提高执行计划复用的机会；它不是对业务输入的完整校验，也不能替代权限控制。

Oracle SQL 中可使用冒号形式表示绑定占位符。下面展示 SQL 语句形式，实际绑定调用方式取决于所用语言和数据库驱动：

SELECT employee_id, full_name, salary
FROM employees
WHERE department = :department
  AND salary >= :minimum_salary;

应用程序应将 :department 和 :minimum_salary 的值作为参数绑定，而不是把用户输入直接拼接到 SQL 文本中。不同驱动对位置绑定、名称绑定、类型声明和重复占位符的处理可能不同，应以驱动文档为准。

绑定变量通常用于数据值，不能用来绑定表名、列名、SQL 关键字或排序方向。例如，用户选择排序列时，不能期待把列名作为普通绑定变量传入。应由程序将用户选项映射到预先允许的列名列表，再生成 SQL 结构；不要把未经校验的输入直接拼成标识符或 SQL 片段。

参数化查询能显著降低因拼接数据值造成 SQL 注入的风险，但不代表任意动态 SQL 都安全。还需检查动态排序、动态表名、权限过宽、错误信息泄露等问题。数据库账号应只拥有完成当前任务所需的权限。

八、常见错误与排查方向

• 字段名或表名拼错：检查拼写、当前 schema、对象是否存在，以及对象是否由其他用户拥有。若使用双引号创建了大小写敏感的对象，引用方式还必须匹配创建时的规则。
• 字符或数值类型不匹配：日期、数字和字符串应使用适当类型。不要依赖隐式转换来处理日期字符串；会话的 NLS 设置可能让同一写法在不同环境中解析成不同结果。优先使用日期字面量或通过驱动绑定日期值。
• 违反约束：检查主键是否重复、必填列是否为空、唯一列是否已有相同值，以及检查约束是否满足。约束错误是数据库保护数据一致性的机制，不应为了让语句通过就随意删除约束。
• UPDATE 或 DELETE 影响行数异常：先执行相同 WHERE 条件的 SELECT。若影响行数为零，检查条件值、数据类型和当前数据；若影响过多，停止操作并检查条件，不要立即提交。
• 日期查询漏掉当天记录：Oracle DATE 含时分秒。使用结束日期零点作为闭区间上界时，可能漏掉当天后续时间的记录；优先考虑左闭右开的日期范围。
• 把 NULL 当成普通值：使用 IS NULL 判断。算术表达式含 NULL 时结果也可能为 NULL；需要把缺失值按特定值参与计算时，可评估 NVL 或 COALESCE，同时确认替代值符合业务语义。
• 误以为语句执行成功就已经永久保存：检查事务是否提交，以及客户端是否启用了自动提交。未提交的数据可能在断开连接时回滚，具体还受客户端行为影响。
• 重复执行插入脚本时报唯一约束错误：检查是否已经插入相同主键或邮箱。需要可重复运行的初始化脚本时，应明确设计“已存在如何处理”，不要仅靠吞掉异常掩盖数据问题。

九、参数限制与版本边界

SQL 能处理的数据量和表达式规模受 Oracle 版本、具体语法、列定义、字符集以及客户端驱动等因素影响。比如，许多常见 Oracle 版本中，单个 IN 列表最多允许 1000 个表达式；但版本演进可能改变边界，实际使用前应核对目标版本的官方文档。大量筛选值不适合无限扩展成一个超长 IN 列表，可考虑把值放入临时表、集合参数或其他适合本系统的方案。

标识列和 OFFSET ... FETCH 的支持与语法可用性也有版本要求。本文示例按 Oracle 12c 及以上版本编写；较老版本应使用适配方案，并在目标环境验证。不要将 SQL*Plus 或某种图形客户端的命令误认为 Oracle SQL 本身：例如客户端的脚本命令可能不适用于应用程序驱动。

不同版本和配置可能影响对象长度限制、字符语义、函数行为及优化器选择。遇到限制问题时，应先查清目标数据库版本、列定义和具体报错，再依据官方文档确认可行范围。本文未将某个参数上限视作适用于所有 Oracle 版本和所有语境的统一数字。

十、综合练习

下面的练习先新增一条记录，再查询它，修改薪资并核对结果，最后删除测试记录。请在练习环境执行；若希望保留这条数据，将最后的 DELETE 换成 COMMIT 前的其他处理。

INSERT INTO employees (
    full_name, department, salary, hire_date, email
) VALUES (
    '测试员工', '测试部', 10000, DATE '2024-01-15',
    '[email protected]'
);

SELECT employee_id, full_name, department, salary, hire_date
FROM employees
WHERE email = '[email protected]';

UPDATE employees
SET salary = 11000
WHERE email = '[email protected]';

SELECT employee_id, full_name, salary
FROM employees
WHERE email = '[email protected]';

DELETE FROM employees
WHERE email = '[email protected]';

COMMIT;

实际操作时，建议把最后的删除暂时换成查询，先观察每一步的影响；熟悉事务后，也可以在提交之前执行回滚，验证未提交的修改如何撤销。请注意，如果邮箱唯一约束已经存在相同值，第一次 INSERT 会失败；若此前已提交了测试数据，再次运行该练习也可能遇到唯一约束错误。

结语

掌握 Oracle 的增删改查，核心不只是会写 SELECT、INSERT、UPDATE 和 DELETE，还要建立几条稳定的操作习惯：明确查询列和筛选条件；对 NULL、日期和数据类型使用正确写法；修改或删除前先核验目标行；在事务中检查影响并谨慎提交；应用程序用绑定变量传值，不把不可信输入拼进 SQL。先在测试数据上练熟这些边界，再依据目标 Oracle 版本和项目规范应用到真实业务中。