年初帮一个老项目做过一次数据割接SQL 脚本里密密麻麻全是 INSERT 语句。当时我就发现一个规律出问题的 SQL十有八九都不是业务逻辑多复杂而是 INSERT INTO 本身没用利索——不是列没对上就是日期格式挂了再不然就是没考虑 NULL 和隐式转换结果一行数据就把整个批次干回滚了。Oracle 11g 虽然已经不算年轻但现在存量系统里用它的依然非常多。这篇文章不聊高大上的调优策略就老老实实把 INSERT INTO 的几种写法、插入数据时那些坑、以及我在实际项目里踩过的雷拆开揉碎讲一遍。不管你是刚接触 Oracle 还是已经在写报表 SQL这几点都值得过一眼。1. 先把基础语法夯实INSERT 的几种“标准姿势”1.1 带列名插入最推荐的标准写法带列名插入是应用最广、也最不容易出错的写法INSERT INTO emp (empno, ename, job, hiredate, sal) VALUES (1001, 张伟, 开发工程师, TO_DATE(2025-06-15, YYYY-MM-DD), 12000);关键点在于列名顺序可以和表结构不一致。比如表里字段顺序是 empno、ename、job、mgr、hiredate、sal、comm、deptno我写 SQL 时完全可以根据语义把列打乱INSERT INTO emp (ename, empno, sal) VALUES (李强, 1002, 15000);这种写法对代码的可读性和后期维护都友好得多。别人接手你的脚本一眼能看到哪个字段对应哪个值如果表结构发生变更加一列或减一列带列名的 SQL 受影响范围也小得多。另一个实际好处是可以充分利用默认值机制。比如表里 created_date 字段默认值是 SYSDATE那我 INSERT 时根本不用写它数据库会自动填充反之如果我不带列名又漏掉这个字段就会触发“值数量不足”之类的报错非常冤枉。1.2 不带列名插入能省事但有前提不带列名就是直接写表名后跟 VALUESINSERT INTO emp VALUES (1003, 王芳, 测试工程师, 7788, SYSDATE, 10000, NULL, 20);这里要求括号里的值数量、顺序必须和表结构完全一致多一个、少一个、错一个都不行。表里字段是 8 个你只给 7 个值直接报 ORA-00947不足的值给了 9 个报 ORA-00913过多的值。这种写法在快速测试时确实很方便但实际开发中我不太建议。原因也很简单表结构经常变。今天加了一个字段你的 INSERT 明天就跑不了而且代码可读性差看 SQL 的人还得去翻表结构才能对应上。尤其是那种字段多达几十张列的大宽表不带列名完全是在给自己埋雷。如果实在要用我的习惯是先在开发环境查一下表结构DESC emp;再排好顺序写。但线上变更、生产割接这些场景老老实实带列名。1.3 Oracle 11g 多行插入的替代方案很多从 MySQL 转过来的朋友会有个习惯性动作写多行 VALUES 一次插入多条记录。MySQL 的写法是INSERT INTO emp (empno, ename) VALUES (1, a), (2, b);但 Oracle 11g 直接这样写会报语法错误。旧版 Oracle 不支持这种多行 VALUES 语法直到后面一些新版本才逐步支持。在 11g 里想一次插入多行我常用两种变通方式。第一种是用 INSERT ALL 做无条件的多表插入或单表多行插入INSERT ALL INTO emp (empno, ename, sal) VALUES (1004, 赵磊, 9000) INTO emp (empno, ename, sal) VALUES (1005, 孙梅, 9500) INTO emp (empno, ename, sal) VALUES (1006, 周涛, 8000) SELECT 1 FROM dual;注意这里最后必须有 SELECT 子句哪怕是从 dual 查一个常量语法上也必须有。这个方法本质上是利用多表插入特性在 11g 里用来批量造测试数据非常顺。第二种是用 UNION ALL 配合 SELECT FROM dualINSERT INTO emp (empno, ename, sal) SELECT 1004, 赵磊, 9000 FROM dual UNION ALL SELECT 1005, 孙梅, 9500 FROM dual;这条 SQL 把插入的数据当作查询结果集性能也不错。我之前在生成测试脚本时经常用这种方式一次灌几千行进表也没问题。2. 进阶玩法查询结果灌库、条件插入与序列主键2.1 INSERT INTO ... SELECT从查询结果直接倒进目标表把一张表的数据按条件复制到另一张表是数据迁移、备份表、报表临时表最常见的需求。核心语法INSERT INTO emp_backup (empno, ename, job, sal) SELECT empno, ename, job, sal FROM emp WHERE deptno 20;这里有几个底层逻辑要明白。INSERT 子句里的列和目标表的列按位置对应不是按名字对应。所以 SELECT 出来的第一个列对应 INSERT 的第一个列第二个对应第二个以此类推。目标表可以不存在吗可以但需要先用 CREATE TABLE AS SELECT 建表。实际操作中我经常这样快速备份CREATE TABLE emp_backup AS SELECT * FROM emp WHERE 1 0;先把表结构建出来不导数据再执行 INSERT INTO ... SELECT。这样比手工写建表语句省事多了。INSERT INTO ... SELECT 还有一个常见用法就是从一张表里抽出满足条件的数据做过去重或汇总后灌到统计表里INSERT INTO dept_emp_stats (deptno, emp_count) SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;这种写法在 ETL 环节几乎是标配。执行之前我会先跑一遍 SELECT确认结果集的数量和类型都没问题再改写成 INSERT 执行。养成这个习惯能少挨不少骂。2.2 条件插入与多表插入INSERT ALL / INSERT FIRSTOracle 的多表插入是个非常实用的功能把一条记录根据条件分发到不同表或同一表按条件插入不同数据都能一把梭搞定。无条件的 INSERT ALL 不管条件真假把数据同时插入多张表。最常见的场景一张源表同时备份多份快照。有条件的 INSERT ALL 可以对同一条记录做多条件匹配每条记录可能插入多个目标表INSERT ALL WHEN sal 10000 THEN INTO emp_high (empno, ename, sal) WHEN sal 5000 THEN INTO emp_mid (empno, ename, sal) ELSE INTO emp_low (empno, ename, sal) SELECT empno, ename, sal FROM emp;注意这里的坑INSERT ALL 的 WHEN 条件是逐条判断的匹配多个条件时就插入多份。比如一行 sal12000 的记录既满足 sal10000 又满足 sal5000那它会被同时插入 emp_high 和 emp_mid。想要只插入第一个匹配的分支必须用 INSERT FIRSTINSERT FIRST WHEN sal 10000 THEN INTO emp_high (empno, ename, sal) WHEN sal 5000 THEN INTO emp_mid (empno, ename, sal) ELSE INTO emp_low (empno, ename, sal) SELECT empno, ename, sal FROM emp;INSERT FIRST 逻辑上和 CASE WHEN 一样只进第一个 WHEN 为真的分支。做数据分片处理时这个特性可太有用了。我第一次用 INSERT FIRST 做用户等级分表时差点因为没搞清 ALL 和 FIRST 的区别把数据重复导了两遍还好目标表有唯一约束兜底。2.3 序列主键的插入方式与 RETURNING 的实战坑Oracle 11g 没有 MySQL 那种 AUTO_INCREMENT 直白写法主键自增基本靠序列加触发器或者直接在 INSERT 时用序列取值。推荐的做法是在 INSERT 语句里显式使用序列伪列INSERT INTO emp (empno, ename, sal) VALUES (seq_emp.nextval, 钱进, 8500);这里要注意nextval 每次调用都会递增即使这条 INSERT 回滚了序列值也不会回退。所以主键中间出现跳跃的编号不是什么表坏了是序列的正常行为。如果你需要在插入后拿到主键值做后续业务逻辑处理可以配合 RETURNING 子句。在 PL/SQL 块里常这样写DECLARE v_empno NUMBER; BEGIN INSERT INTO emp (empno, ename, sal) VALUES (seq_emp.nextval, 吴迪, 11000) RETURNING empno INTO v_empno; DBMS_OUTPUT.PUT_LINE(新员工编号: || v_empno); END;RETURNING 对普通程序开发最实用的价值是不用再单独查一次序列的 currval 来拿当前主键程序里一条 INSERT 就直接带回主键值。不过有个细节提醒一下如果是在 SQL*Plus 或很多图形化数据库工具里直接执行带 RETURNING 的 INSERT并不像 PL/SQL 块里那么好用。图形化工具里需要写绑定变量或者用 PL/SQL 包一层。我一般建议想在客户端测 RETURNING先确认工具版本支持程度再动手不然会在语法上卡半天。3. NULL、日期和字符集插入数据时最容易翻车的三件事3.1 NULL 语义与 DEFAULT 值的正确理解Oracle 里 NULL 表示“未知值”它不是空字符串也不是 0。最坑的一点是Oracle 把空字符串 直接当作 NULL 处理。这在从其他数据库迁到 Oracle 时会踩非常大的坑。比如执行INSERT INTO emp (empno, ename, comm) VALUES (1007, 郑欣, );看起来是想插入一个空字符串实际插入的是 NULL。如果你的目标列不允许 NULL这条语句就会直接报 ORA-01400无法将 NULL 插入。很多迁移项目里数据凭空少了几行原因往往就出在这。DEFAULT 值的逻辑也要理清。插入时不写这个列Oracle 会自动填 DEFAULT 定义的值显式插入 NULL则覆盖 DEFAULT结果就是 NULL显式插入 DEFAULT 关键字才会使用默认值。INSERT INTO emp (empno, ename, commission_pct) VALUES (1008, 陈晨, DEFAULT);实际开发中我建议先把表结构里的 NOT NULL 约束和 DEFAULT 值查清楚再写 INSERTSELECT column_name, nullable, data_default FROM user_tab_columns WHERE table_name EMP;3.2 日期插入的坑TO_DATE 是保命函数Oracle 11g 导入日期数据时最常见的报错是 ORA-01843无效月份或者 ORA-01861文字与格式字符串不匹配。这背后是 NLS 参数在作怪。默认日期格式由会话的 NLS_DATE_FORMAT 决定常见为 DD-MON-RR 或 YYYY-MM-DD。你直接给一个字符串 2025-15-06数据库按照默认格式去解析很可能把 15 当成月份或日期结果直接无效。在生产和测试环境之间不同客户端的 NLS 参数还可能不一致同一句 SQL 在 A 机器上跑得好好的到 B 机器上报错。经验就是插入日期值永远不要依赖数据库默认格式老老实实用 TO_DATE 指定格式INSERT INTO emp (empno, ename, hiredate) VALUES (1009, 林峰, TO_DATE(2025-06-15 10:30:00, YYYY-MM-DD HH24:MI:SS));如果只需要日期部分不想保留时分秒可以用 TRUNC 包一层INSERT INTO emp (empno, ename, hiredate) VALUES (1010, 黄佳, TRUNC(SYSDATE));另外有一个不少人忽略的点如果你在 WHERE 条件或 INSERT 选择里想按日期过滤最好也写成 TO_DATE 比较不要让 Oracle 去猜字符串到底表示什么格式。数据量一大隐式转换带来的坑都是百倍放大。3.3 字符串、数字隐式转换引发类型异常的隐形地雷Oracle 自动帮你做类型转换偶尔是帮忙更多时候是添乱。最典型的就是字符类型和数字类型互相转换。看这个例子INSERT INTO emp (empno, ename) VALUES (1011, 许晴);数字类型的 empno 列接收了字符串 1011Oracle 能转换不会报错。但如果字符串根本不是数字像 EMP1011就会报 ORA-01722无效数字。尤其需要注意隐式转换的另一个方向。当你把数字插入 VARCHAR2 列时Oracle 会先转成字符串看起来没事但如果在 WHERE 条件里把 VARCHAR2 列和数字比较SELECT * FROM emp WHERE dept_id 202;这里 dept_id 是 VARCHAR2 类型Oracle 会优先把列值隐式转换成数字再比较。一旦有一行数据是 ABC整条查询直接报 ORA-01722连其他正常的数字行都不返回。写入数据时也一样。如果目标列是 VARCHAR2但值里混入了非数字内容而 SQL 的连接串不小心让 Oracle 做了数字方向转换报错就来了。我在一个导入脚本里就踩过——明细表序号列明明是 VARCHAR2但代码里用了业务数字拼接导致非数字批次一下子全失败。解决方式就是无论什么场景都确保值的类型和列定义的类型保持一致写 SQL 时对日期用 TO_DATE、对数字不要加引号、对字符串尽可能用合适的长度和类型。4. 性能与事务批量灌数时真正该关心的事4.1 大批量插入时先做减法再做加法给已经有大量数据的表插入新数据最拉胯的因素往往不是 INSERT 语句本身而是表上的索引、触发器和约束检查。每插入一行Oracle 都要同步维护每个索引。假设表上有 5 个索引每行插入就要更新 5 个索引结构还有触发器里的业务逻辑以及各种约束校验。我以前给一堆千万级的表灌数据时一个批次跑下来能跑到几个小时后来把索引全部先删除插入完成后重建时间缩短到原来的五分之一。这个操作思路是这样的-- 1. 先禁用或删除索引、触发器 ALTER TRIGGER trg_emp_dml DISABLE; DROP INDEX idx_emp_ename; -- 2. 执行批量插入 INSERT INTO emp (empno, ename, sal) SELECT empno, ename, sal FROM emp_staging; -- 3. 插入完成后重建 CREATE INDEX idx_emp_ename ON emp(ename); ALTER TRIGGER trg_emp_dml ENABLE;实际项目中如果表太大重建索引会花很长时间那就改成分区表操作或者用逻辑裁剪减少重建范围。但总的原则不变批量操作期间索引和触发器能少参与就少参与。4.2 提交时机回滚段不是无限大的Oracle 的 INSERT 操作在 COMMIT 之前所有数据变更都留在内存和撤销表空间里。大批量插入时不提交撤销段会被撑爆最终报出 ORA-30036无法扩展回滚段之类的问题整个事务只能回滚前面的工作全部清零。处理大事务的标准姿势是分批提交。比如插入 100 万行分成 1000 行一个批次BEGIN FOR i IN 1..1000 LOOP INSERT INTO emp (empno, ename, sal) SELECT 100000 LEVEL, 测试用户, 8000 FROM dual CONNECT BY LEVEL 100; COMMIT; END LOOP; END;注意分批提交是有代价的。事务中途失败已经提交的批次不能回滚需要自己通过业务主键清理。所以要不要分批提交取决于业务对原子性的要求纯测试数据分批没问题核心业务上线脚本最好整个事务要么全成要么全败这时候就要控制单批次的数据量确保撤销表空间足够承载。4.3 直接路径插入与 APPEND 提示Oracle 里有一种“直接路径插入”插入时绕过缓冲区和部分完整性检查直接把数据块写入数据文件。性能提升明显代价是不能和普通 DML 走同一条路径的灵活查询。用法就是在 INSERT 后加 APPEND 提示INSERT /* APPEND */ INTO emp (empno, ename, sal) SELECT empno, ename, sal FROM emp_staging;这个提示只对 INSERT INTO ... SELECT 生效对 VALUES 子句基本没用。操作中它有几种使用副作用需要提前知道。APPEND 会锁表阻塞其他会话的 DML如果是数据文件空间有限的环境还会把表的高水位线推高即使删除数据也不会把空间归还操作系统。我之前用 APPEND 给一张日志表灌数据速度快得飞起但后来发现 DELETE 清掉一半数据后表占用空间还是那么大就是因为高水位线被推上去了。所以 APPEND 要慎用尤其不要对频繁增删的核心业务表用。4.4 用 MERGE 实现“有则更新无则插入”实际业务里几乎每天都在面对数据重复的问题。要么用唯一约束加异常处理要么用 MERGE 一句搞定。MERGE 在 Oracle 里的更新插入语法MERGE INTO emp t USING emp_staging s ON (t.empno s.empno) WHEN MATCHED THEN UPDATE SET t.sal s.sal, t.ename s.ename WHEN NOT MATCHED THEN INSERT (t.empno, t.ename, t.sal) VALUES (s.empno, s.ename, s.sal);MERGE 最大的价值是一句话完成“先查再改或插”的流程性能上也比应用层先 SELECT 再 INSERT 再 UPDATE 更高效。但它也有坑ON 条件中的字段必须有唯一性保证否则目标表中出现多条匹配MERGE 会报 ORA-30926无法获取稳定的一组行。数据同步场景里我几乎必用 MERGE。尤其是做每日增量更新源表变化一部分新增一部分MERGE 一把过省心。5. 常见报错与排查思路速查5.1 报错 ORA-00947 / ORA-00913VALUES 数量和列数量对不上ORA-00947 意思是给的 VALUES 数量比目标列少ORA-00913 是给的多了。这个错误定位几乎不用动脑把 INSERT 的列数和 VALUES 里实际值个数数一遍即可。带列名插入时特别注意别漏了 NOT NULL 且无默认值的列。比如下边这条必挂INSERT INTO emp (ename, sal) VALUES (小刘, 8000);如果 emp 表的 empno 列是 NOT NULL 且没有默认值这个 SQL 会报错因为 empno 没出现在列名中Oracle 没法给它一个值。带上序列或手动编号INSERT INTO emp (empno, ename, sal) VALUES (seq_emp.nextval, 小刘, 8000);5.2 ORA-01400无法将 NULL 插入最常见的原因是 NOT NULL 列没给值或者给了空字符串。排查思路分两步。第一步查表结构确认哪些列不能为空第二步回头看 INSERT 语句对应的值里有没有空字符串、NULL 或者根本没写的列。也有可能数据来源是另一张表由于查询逻辑的原因某一列的 NULL 值被带了出来。这时候要在 INSERT INTO ... SELECT 里用 NVL 处理默认值INSERT INTO emp (empno, ename, comm) SELECT empno, ename, NVL(comm, 0) FROM emp_staging;5.3 ORA-12899值太大超过列宽度插入的字符超过 VARCHAR2 列的长度限制时报 ORA-12899。中文占几个字节和数据库字符集有关ZHS16GBK 下一个汉字占 2 字节AL32UTF8 下占 3 字节。同样的 10 个汉字在 GBK 下是 20 字节在 UTF8 下是 30 字节。如果列定义 VARCHAR2(20)GBK 能放下 10 个汉字UTF8 只能放下 6 个。我处理过好几个类似的割接问题都是换字符集后原来能插的数据全超长了。解决办法不是把列无限加宽而是先查一下目标表和源表的字符集评估字段长度设计是否合理。开发阶段就要考虑多语言数据的情况把 VARCHAR2 长度按最大字符集占用预留出来。5.4 ORA-00001违反唯一约束这个错误最有名的场景就是主键重复。通常是插入数据时没有控制好业务主键或者序列出了问题。排查时先确认重复的是哪个约束SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name EMP;然后查一下当前表里最大的主键值SELECT MAX(empno) FROM emp;如果确认是序列和表数据不同步手动把序列值调整到大于当前最大键即可。不建议随便删序列重建生产环境这样做容易引发更多连锁问题。5.5 ORA-02291违反外键约束插入子表记录时外键指向的父表记录不存在就会报这个错。比如在 emp 表里插入了一个 deptno99但 dept 表里没有 99直接挂。实际项目中这种错误经常出现在数据导入顺序有问题时——父表的数据还没导入子表就先进来了。解决思路也比较直接先导入父表再导入子表或者用延迟约束在事务提交时才校验外键但对大多数系统来说没必要引入这个复杂度。还有一种情况是真脏数据源系统里存在孤儿数据导入时被原样搬了过来。针对这种场景我会在数据迁移前先跑一遍检查脚本把找不到父记录的数据提前过滤掉而不是等 INSERT 执行时一条条报错。一点补充别忽略 SAVEPOINT 和异常处理批量插入的脚本越长事务里某个点出错导致全部回滚的代价就越大。Oracle 提供 SAVEPOINT 可以在事务内部打“检查点”部分回滚。INSERT INTO emp_backup_a SELECT * FROM emp WHERE deptno 10; SAVEPOINT sp_a; INSERT INTO emp_backup_b SELECT * FROM emp WHERE deptno 20; SAVEPOINT sp_b; -- 如果后面失败只回滚到 sp_b 之前 ROLLBACK TO SAVEPOINT sp_b;这个控制在 ETL 脚本里很实用。大批量分步骤灌数时每步打一个保存点哪一步挂了就从哪一步重来不至于整个批次推倒。如果是写 PL/SQL 存储过程还要注意 EXCEPTION 里做异常捕获能记日志就记日志。批量处理最怕“闷声失败”跑了一个小时失败后控制台只输出一句错误又得重新调一天。我当时习惯写一个日志表每次执行关键步骤都插一条状态和耗时排查问题会轻松非常多。我个人在实际项目里最多用到的一个小习惯是任何 INSERT 脚本在真正执行到生产库之前一定先开一个事务插入后 SELECT 一遍完整性再 COMMIT。能用 MERGE 的不用裸 INSERT能用 TO_DATE 的绝不扔裸字符串。数据无小事INSERT 看着简单真要写出稳定不出错的脚本里面的细节一点不比复杂业务逻辑少。
