当前位置：首页 > article >正文

【Oracle11g SQL详解】INSERT INTO 的用法及插入数据注意事项

article 2024/12/4 19:14:03

INSERT INTO 的用法及插入数据注意事项

在 Oracle 11g 中，INSERT INTO 语句用于向表中插入数据，是数据写入操作中最常用的 SQL 语句之一。本文将详细介绍 INSERT INTO 的基本语法、常见场景、注意事项及常见错误处理。

一、INSERT INTO 的基本语法

INSERT INTO 支持两种基本用法：

指定列插入（推荐，明确指定列名，有助于维护性）。
完整插入（省略列名，插入所有列的值）。

1. 指定列插入

INSERT INTO 表名 (列名1, 列名2, ...)
VALUES (值1, 值2, ...);

示例：向 employees 表插入一个新员工记录

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (207, 'Tom', 'Hanks', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 5500);

说明：

需要严格按照列的顺序提供值。
数据类型必须匹配列定义（如日期、数字、字符等）。

2. 完整插入

INSERT INTO 表名
VALUES (值1, 值2, ...);

示例：插入所有列数据

INSERT INTO departments
VALUES (280, 'HR', 101, 1700);

注意：

此用法会插入表中所有列的数据，因此必须提供表中所有列的值。
表中定义了默认值或允许 NULL 的列可自动填充，但不推荐省略。

二、INSERT INTO 的变体

1. 插入多行数据

Oracle 支持通过多次 INSERT 实现批量插入，但不支持单条语句直接插入多行。

示例：批量插入多个员工记录

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (208, 'John', 'Doe', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 6000);

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (209, 'Jane', 'Smith', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 7200);

优化方式： 使用 PL/SQL 块或外部数据工具（如 SQL*Loader）实现批量插入。

2. 从其他表插入数据

通过 INSERT INTO ... SELECT 的方式将查询结果插入目标表。

INSERT INTO 表名 (列名1, 列名2, ...)
SELECT 列名1, 列名2, ...
FROM 另一个表
[WHERE 条件];

示例：将 temp_employees 表中员工数据复制到 employees 表

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
SELECT employee_id, first_name, last_name, hire_date, salary
FROM temp_employees
WHERE hire_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

说明：

确保目标表和源表的列类型匹配。
可以配合 WHERE 子句实现条件插入。

3. 使用子查询插入默认值

当需要插入某些列的默认值时，可以使用子查询。

示例：插入新部门并设置默认值

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (290, 'Marketing', NULL, (SELECT location_id FROM locations WHERE city = 'New York'));

三、INSERT INTO 的常见注意事项

插入列顺序与数据匹配
列的顺序必须与提供的值一一对应，否则可能出现错误。

错误示例：
```
INSERT INTO employees (first_name, employee_id)
VALUES (207, 'Tom'); -- 错误，列顺序不匹配
```
遵守表约束
插入数据时需满足表的约束条件：
- 主键 (PRIMARY KEY)：禁止重复。
- 唯一性约束 (UNIQUE)：插入值必须唯一。
- 非空约束 (NOT NULL)：禁止插入空值。
- 外键约束 (FOREIGN KEY)：插入值必须在父表中存在。
数据类型匹配
- 插入值需与列的数据类型一致，否则可能触发类型转换错误。
- 日期类型值需使用 TO_DATE 转换。
性能优化建议
- 批量插入建议使用 PL/SQL 批处理或外部工具，减少事务开销。
- 插入前可禁用索引或触发器，完成后再启用，提升性能。

四、常见错误及解决方法

违反主键约束
错误信息： ORA-00001: unique constraint violated
原因： 插入了重复的主键值。
解决方法：
- 确认新插入的数据主键是否唯一。
- 预先检查主键值是否已存在：
```
SELECT employee_id FROM employees WHERE employee_id = 207;
```
违反非空约束
错误信息： ORA-01400: cannot insert NULL into ("列名")
原因： 未为 NOT NULL 列提供值。
解决方法： 提供必要的非空列值或修改表结构允许空值。
数据类型不匹配
错误信息： ORA-01722: invalid number
原因： 插入的值类型与列定义类型不匹配。
解决方法： 检查并转换插入数据的类型。

五、实践案例

向表中插入一条完整记录

INSERT INTO employees 
(employee_id, first_name, last_name, hire_date, salary, department_id)
VALUES 
(210, 'Alice', 'Brown', TO_DATE('2024-11-26', 'YYYY-MM-DD'), 8000, 10);

复制符合条件的数据到新表

INSERT INTO high_salary_employees (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 7000;

插入缺省值与计算结果

INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (300, 'Research', NULL, (SELECT location_id FROM locations WHERE city = 'San Francisco'));