Oracle数据库插入语句怎么写_Oracle插入数据语法详解-SQL-PHP中文网

Oracle插入数据的核心是INSERT语句，支持插入单行、多行、查询结果及LOB对象。1. 可指定列或全部列插入；2. 用INSERT ALL或SELECT结合UNION ALL实现批量插入；3. 处理主键冲突推荐使用MERGE语句实现“存在则更新，否则插入”；4. 非空约束需确保提供有效值或利用默认值；5. 插入BLOB/CLOB时先插入空定位符（EMPTY_BLOB/EMPTY_CLOB），再通过DBMS_LOB或客户端流式写入数据；6. 大规模数据导入推荐SQL*Loader工具，效率更高。

oracle数据库插入语句怎么写_oracle插入数据语法详解

Oracle数据库插入数据，核心就是利用

INSERT

登录后复制

语句。它允许我们把新行添加到现有的表中，无论是插入单条记录、多条记录，还是从其他表查询结果来填充。理解其不同用法，是进行数据操作的基础。

解决方案

在Oracle中，插入数据有几种常见的语法形式，每种都适用于不同的场景。

最基础的，是向表中插入一行数据，并指定所有列或部分列的值。

1. 插入指定列的值

这种方式最常用，也最安全，因为它明确指出了哪些列要被赋值。

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

登录后复制

例如，向

employees

登录后复制

表插入一条新员工记录：

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1001, '张', '三', 'zhangsan@example.com', SYSDATE, 'IT_PROG', 6000);

登录后复制

这里

SYSDATE

登录后复制

是一个Oracle函数，用于获取当前系统日期。

2. 插入所有列的值

如果我们要为表的所有列按定义的顺序提供值，可以省略列名列表。但这种方式风险较高，因为表的结构一旦改变（例如增加或删除列，或列顺序调整），语句就可能失效。

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

登录后复制

假设

employees

登录后复制

表所有列的顺序是

employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id

登录后复制

：

INSERT INTO employees
VALUES (1002, '李', '四', 'lisi@example.com', '515.123.4567', SYSDATE, 'SA_REP', 8000, NULL, 100, 80);

登录后复制

注意，这里我为

commission_pct

登录后复制

传入了

NULL

登录后复制

，因为该列可能允许空值。

3. 从其他表查询数据并插入

当我们需要将一个或多个表的查询结果插入到另一个表中时，这种方式非常高效。这在数据迁移、报表生成或历史数据归档时非常有用。

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

登录后复制

比如，将所有IT部门的员工数据归档到

employees_archive

登录后复制

表中：

INSERT INTO employees_archive (employee_id, first_name, last_name, email, hire_date, job_id, salary)
SELECT employee_id, first_name, last_name, email, hire_date, job_id, salary
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT');

登录后复制

这种方式的灵活性在于

SELECT

登录后复制

语句的强大，可以包含连接（JOIN）、聚合函数等。

4. 批量插入多行数据（INSERT ALL）

Oracle提供了一个非常方便的

INSERT ALL

登录后复制

语句，可以一次性插入多行数据，或者根据条件将数据插入到不同的表中。

一次插入多行到同一张表：

INSERT ALL
INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (1003, '王', '五', 'wangwu@example.com', SYSDATE, 'IT_PROG', 7000)
INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (1004, '赵', '六', 'zhaoliu@example.com', SYSDATE, 'SA_REP', 9000)
SELECT * FROM DUAL; -- DUAL是一个只有一行的虚拟表，用于满足SELECT语法要求

登录后复制

SELECT * FROM DUAL

登录后复制

在这里只是为了完成

INSERT ALL

登录后复制

的语法结构，它本身并不提供数据。

条件性插入到不同表（或同一表的不同列）：

INSERT ALL WHEN condition THEN INTO table ...

登录后复制

这个稍微复杂些，但功能强大，可以根据某个条件将数据分发到不同的目标表。

INSERT ALL
WHEN salary >= 8000 THEN
    INTO high_salary_employees (emp_id, emp_name, emp_salary) VALUES (employee_id, first_name || ' ' || last_name, salary)
WHEN salary < 8000 THEN
    INTO normal_salary_employees (emp_id, emp_name, emp_salary) VALUES (employee_id, first_name || ' ' || last_name, salary)
SELECT employee_id, first_name, last_name, salary FROM employees;

登录后复制

这里，

SELECT

登录后复制

语句从

employees

登录后复制

表获取数据，然后根据

salary

登录后复制

的值，将数据分别插入到

high_salary_employees

登录后复制

或

normal_salary_employees

登录后复制

表中。

Oracle数据库如何一次性插入多条数据？

一次性插入多条数据在实际开发中非常常见，尤其是在数据导入或批量处理时。除了上面提到的

INSERT ALL

登录后复制

，我们还有其他一些高效的方式。

个人经验告诉我，当需要插入的数据量不大，且数据是硬编码或者通过程序少量生成时，

INSERT ALL

登录后复制

配合

VALUES

登录后复制

子句确实很方便。它能减少数据库与应用程序之间的往返次数，提高效率。

INSERT ALL
INTO products (product_id, product_name, price) VALUES (1, '笔记本电脑', 8999.00)
INTO products (product_id, product_name, price) VALUES (2, '无线鼠标', 129.50)
INTO products (product_id, product_name, price) VALUES (3, '机械键盘', 499.00)
SELECT * FROM DUAL;

登录后复制

如果数据来源于其他表，或者需要从多个子查询中组合数据，那么

INSERT INTO ... SELECT ...

登录后复制

语句就显得尤为强大。结合

UNION ALL

登录后复制

，我们可以构造出任意多行数据来插入。

例如，我们想插入一些虚拟的用户数据到

users

登录后复制

表：

INSERT INTO users (user_id, username, email)
SELECT 101, 'alice', 'alice@example.com' FROM DUAL
UNION ALL
SELECT 102, 'bob', 'bob@example.com' FROM DUAL
UNION ALL
SELECT 103, 'charlie', 'charlie@example.com' FROM DUAL;

登录后复制

这种模式非常灵活，因为每个

SELECT ... FROM DUAL

登录后复制

都可以被替换成更复杂的子查询，只要它们返回的列数和类型与目标表匹配。

对于大规模的数据导入，尤其是从文件（如CSV、Excel）中导入，Oracle的

SQL*Loader

登录后复制

工具是无可替代的利器。

SQL*Loader

登录后复制

是Oracle提供的一个命令行工具，专门用于将外部数据文件高效地加载到数据库表中。它支持复杂的加载规则，比如跳过行、处理分隔符、条件加载、数据转换等等。虽然它不是一个SQL语句，但它是在讨论“一次性插入多条数据”时，一个非常重要的、实用的解决方案。它的效率通常远高于通过SQL语句逐行或小批量插入。

插入数据时遇到主键冲突或非空约束怎么办？

处理插入数据时遇到的约束冲突，是数据库操作中非常常见且关键的一环。如果不妥善处理，轻则导致事务失败，重则影响应用程序的稳定性。

主键冲突（ORA-00001: unique constraint (...) violated）

法语写作助手

法语助手旗下的AI智能写作平台，支持语法、拼写自动纠错，一键改写、润色你的法语作文。

查看详情

当尝试插入一条记录，其主键值或任何唯一约束列的值已经存在于表中时，Oracle会抛出

ORA-00001

登录后复制

错误。我的经验是，这种错误通常意味着业务逻辑需要更精细地处理数据“存在即更新，不存在即插入”的场景，也就是所谓的“upsert”操作。

在Oracle中，解决这种问题的最佳实践是使用

MERGE

登录后复制

语句。

MERGE

登录后复制

语句允许我们根据一个源表或子查询的数据，有条件地对目标表进行插入、更新或删除操作。它能优雅地处理“如果记录存在就更新，如果不存在就插入”的逻辑。

MERGE INTO target_table tt
USING (SELECT col1, col2, ... FROM source_table_or_subquery) st
ON (tt.primary_key_column = st.primary_key_column)
WHEN MATCHED THEN
    UPDATE SET tt.column1 = st.column1, tt.column2 = st.column2, ...
WHEN NOT MATCHED THEN
    INSERT (tt.primary_key_column, tt.column1, tt.column2, ...)
    VALUES (st.primary_key_column, st.column1, st.column2, ...);

登录后复制

举个例子，假设我们有一个

products

登录后复制

表，想更新或插入产品信息：

MERGE INTO products p
USING (
    SELECT 101 AS product_id, '新版键盘' AS product_name, 599.00 AS price FROM DUAL UNION ALL
    SELECT 102 AS product_id, '鼠标垫' AS product_name, 89.00 AS price FROM DUAL
) new_products
ON (p.product_id = new_products.product_id)
WHEN MATCHED THEN
    UPDATE SET p.product_name = new_products.product_name, p.price = new_products.price
WHEN NOT MATCHED THEN
    INSERT (product_id, product_name, price)
    VALUES (new_products.product_id, new_products.product_name, new_products.price);

登录后复制

在这个例子中，如果

product_id

登录后复制

为101的产品已存在，则更新其名称和价格；如果102的产品不存在，则插入新记录。这比先尝试插入，捕获异常再尝试更新要高效和简洁得多。

非空约束（ORA-01400: cannot insert NULL into (...)）

这个错误意味着你尝试向一个定义为

NOT NULL

登录后复制

的列插入

NULL

登录后复制

值。解决办法直接明了：

提供一个有效的值： 确保你的
```
INSERT
```
登录后复制
语句为所有
```
NOT NULL
```
登录后复制
列都提供了非
```
NULL
```
登录后复制
的值。
检查默认值： 如果该列定义了默认值，并且你没有在
```
INSERT
```
登录后复制
语句中显式指定该列，Oracle会自动使用默认值。但如果该列既是
```
NOT NULL
```
登录后复制
又没有默认值，你就必须提供一个值。
修改表结构（谨慎）： 如果业务确实允许某列为空，但它当前被定义为
```
NOT NULL
```
登录后复制
，那么可能需要考虑修改表结构，将其改为允许
```
NULL
```
登录后复制
。这通常需要DBA的批准，并且要评估对现有数据和应用程序的影响。

例如，如果

employees

登录后复制

表的

email

登录后复制

列是

NOT NULL

登录后复制

，而你尝试：

INSERT INTO employees (employee_id, first_name, last_name, hire_date, job_id, salary)
VALUES (1005, '钱', '七', SYSDATE, 'IT_PROG', 6500);
-- 这会报错，因为email列没有提供值，且是NOT NULL

登录后复制

正确的做法是提供

email

登录后复制

值：

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1005, '钱', '七', 'qianqi@example.com', SYSDATE, 'IT_PROG', 6500);

登录后复制

在处理大量数据插入时，如果担心部分数据会触发约束错误，一个策略是在应用程序层面预先校验数据，或者利用事务的原子性，将每次插入操作放在独立的事务中，或者在一个大事务中使用

SAVEPOINT

登录后复制

，遇到错误时回滚到

SAVEPOINT

登录后复制

，然后继续处理下一批数据。

Oracle插入BLOB/CLOB大数据对象有哪些技巧？

插入BLOB（Binary Large Object）和CLOB（Character Large Object）这类大数据对象，与插入普通数据类型（如数字、字符串）有所不同，因为它们通常存储的是大文件内容（图片、视频、文档、长文本等），直接在SQL语句中嵌入完整内容不现实也不高效。

在Oracle中，处理BLOB/CLOB的常见技巧是分两步走：

1. 插入LOB定位符（Locator）

首先，我们插入一行记录，并为BLOB或CLOB列插入一个空的LOB定位符。这个定位符实际上是一个指向存储实际LOB数据的指针。

使用

EMPTY_BLOB()

登录后复制

和

EMPTY_CLOB()

登录后复制

函数来完成这一步：

INSERT INTO documents (doc_id, doc_name, file_content, text_content)
VALUES (1, '合同草稿', EMPTY_BLOB(), EMPTY_CLOB());

登录后复制

这条语句会创建一行记录，其中

file_content

登录后复制

（BLOB）和

text_content

登录后复制

（CLOB）列都包含一个空的定位符，表示它们目前没有实际数据。

2. 更新LOB内容

插入定位符之后，我们就可以通过编程方式（例如，使用JDBC、ODBC、PL/SQL或SQL*Plus）将实际的大数据内容写入到这个定位符所指向的存储空间。

使用PL/SQL和

DBMS_LOB

登录后复制

包：

DBMS_LOB

登录后复制

包提供了一系列函数和过程来操作LOB数据。例如，你可以从一个文件路径读取内容并写入BLOB/CLOB列。

这是一个通过

DBMS_LOB

登录后复制

写入CLOB内容的PL/SQL示例（通常在数据库内部使用或通过客户端调用存储过程）：

DECLARE
    v_clob CLOB;
    v_data VARCHAR2(32767) := '这是一段很长的文本内容，可能会包含合同条款、文章正文等。';
    v_offset INTEGER := 1;
BEGIN
    -- 确保有一行数据，并且CLOB列是空的定位符
    INSERT INTO documents (doc_id, doc_name, text_content) VALUES (2, '文章草稿', EMPTY_CLOB())
    RETURNING text_content INTO v_clob; -- 获取新插入的CLOB定位符

    -- 将数据写入CLOB
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_data), v_data);

    -- 如果是更新现有记录，可以这样：
    -- SELECT text_content INTO v_clob FROM documents WHERE doc_id = 1 FOR UPDATE;
    -- DBMS_LOB.TRUNCATE(v_clob, 0); -- 清空原有内容
    -- DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_data), v_data);

    COMMIT;
END;
/

登录后复制

对于BLOB，操作类似，只是处理的是二进制数据。通常，客户端应用程序会读取文件内容到字节数组，然后通过JDBC/ODBC的API将字节数组写入到LOB列。

使用客户端编程语言（如Java JDBC）：

在Java中，你可以通过

PreparedStatement

登录后复制

来设置LOB参数。

// 假设conn是数据库连接
String sql = "INSERT INTO documents (doc_id, doc_name, file_content, text_content) VALUES (?, ?, EMPTY_BLOB(), EMPTY_CLOB())";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 3);
pstmt.setString(2, "图片文件");
pstmt.executeUpdate(); // 插入带有空LOB定位符的行

// 获取LOB定位符并写入数据
sql = "SELECT file_content, text_content FROM documents WHERE doc_id = 3 FOR UPDATE";
ResultSet rs = conn.createStatement().executeQuery(sql);
if (rs.next()) {
    oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("file_content");
    // oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("text_content");

    // 假设imageData是图片的字节数组
    try (OutputStream os = blob.getBinaryOutputStream()) {
        os.write(imageData);
    }
    // 对于CLOB类似，使用getWriter()
}
conn.commit();

登录后复制

这种方式避免了将整个大对象内容加载到内存中再通过SQL传输，而是通过流式写入，效率更高，也更节省内存。

*使用`SQLLoader`导入文件：**

再次提到
```
SQL*Loader
```
登录后复制
，它对于从文件系统导入BLOB/CLOB数据同样非常强大。你可以在控制文件中指定源文件路径，
```
SQL*Loader
```
登录后复制
会自动处理将文件内容加载到LOB列。这对于批量导入大量LOB文件是首选方案。

例如，在控制文件中可以这样定义：
```
LOAD DATA
INFILE 'data.csv'
INTO TABLE documents
FIELDS TERMINATED BY ','
(
    doc_id,
    doc_name,
    file_content LOBFILE(CONSTANT 'images/') TERMINATED BY EOF, -- file_content列从'images/'目录下的文件加载
    text_content LOBFILE(CONSTANT 'texts/') TERMINATED BY EOF   -- text_content列从'texts/'目录下的文件加载
)
```
登录后复制
这里的
```
LOBFILE
```
登录后复制
指令告诉
```
SQL*Loader
```
登录后复制
去指定的路径读取文件内容。