Oracle数据库插入语句怎么写_Oracle插入数据语法详解
|
6
|
数据库

3105 字
|
12 分钟

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）

Oracle数据库插入语句怎么写_Oracle插入数据语法详解

笔灵AI论文写作

免费生成毕业论文、课题论文、千字大纲，几万字专业初稿！

Oracle数据库插入语句怎么写_Oracle插入数据语法详解37

查看详情 Oracle数据库插入语句怎么写_Oracle插入数据语法详解

当尝试插入一条记录，其主键值或任何唯一约束列的值已经存在于表中时，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

值。解决办法直接明了：

  1. 提供一个有效的值： 确保你的 
    INSERT

    语句为所有

    NOT NULL

    列都提供了非

    NULL

    的值。

  2. 检查默认值： 如果该列定义了默认值，并且你没有在 
    INSERT

    语句中显式指定该列，Oracle会自动使用默认值。但如果该列既是

    NOT NULL

    又没有默认值，你就必须提供一个值。

  3. 修改表结构（谨慎）： 如果业务确实允许某列为空，但它当前被定义为 
    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

    去指定的路径读取文件内容。

总之，处理LOB数据，关键在于理解其“定位符”的概念，并利用数据库或客户端工具提供的流式写入机制，而不是试图将整个大对象作为字符串或二进制字面量直接插入SQL语句。

oracle excel java 大数据 app 电脑 编程语言 工具 ai 笔记本电脑 Java sql 数据类型 Object NULL select 字符串 union 指针 对象 table oracle 数据库 dba excel

app
上一篇
下一篇

推荐文章

安全显示查询参数值的静态页面搭建指南
安全显示查询参数：简易静态页面实现指南
Composer如何自定义命令
VSCode的配置文件（JSON）如何灵活管理不同工作区？
币安APP安卓版下载 币安交易所iOS官方v3.3.8最新版
如何在Linux中开机自启 Linux systemctl enable配置
如何彻底卸载composer
thinkphp Cookie读写操作失败怎么办
VSCode故障排除怎么启动_VSCode故障排除模式启动
什么是以太坊ETH？新手如何才能获得以太坊？