Oracle增删改（INSERT、DELETE、UPDATE）语句
Ø简介
本⽂介绍 Oracle 中的增删改语句，即 INSERT、DELETE、UPDATE 语句的使⽤。是时候展现真正的技术了，快上车：
1.插⼊数据(INSERT)
2.修改数据(UPDATE)
3.删除数据(DELETE)
4.使⽤ MERGE INTO 语句完成增删改操作
5.回滚(rollback)的使⽤
6.注意事项
1.插⼊数据(INSERT)
u语法：
INSERT INTO TABLE_NAME [(column1[, column2, …]] VALUES(value1[, value2, …]);
说明：
1)INSERT 数据时可以指定列名，也可不指定列名。如果不指定列名，必须为每⼀列都提供数据，并且顺序必须与列名的顺序⼀致；如果指定列名，
提供的数据需要与指定的列名顺序⼀致；
2)插⼊数据时数字类型的列可直接写⼊，字符或⽇期类型的列需要加单引号；
3)插⼊的数据必须满⾜约束规则，主键和 NOT NULL 的列必须提供数据。
u插⼊数据的⽅式
1)⾸先，可以在 PL/SQL Developer 中使⽤ FOR UPDATE 语句
1.⾸先执⾏ SELECT 语句
SELECT * FROM Table01 FOR UPDATE;
2.点击锁表按钮
3.编辑数据 -> 记⼊改变 -> 表解锁按钮
4.最后点击提交
l说明：低版本的 PL/SQL Developer 操作与以上类似。
2)使⽤ INSERT INTO 语句，插⼊⼀条数据
INSERT INTO Table01(Id, Name) VALUES(2, '李四'); --指定所有列
COMMIT; --必须执⾏提交命令
提⽰：在平常开发中，建议显⽰指定插⼊的列名，有助于提⾼代码的可读性。
INSERT INTO Table01(Id) VALUES(3); --指定部分列，其他未指定的列表必须可以为空（即 NULL）
COMMIT;
INSERT INTO Table01 VALUES(4, '王五'); --不指定任何列，必须按顺序插⼊所有列
COMMIT;
3)使⽤ INSERT INTO SELECT 语句，插⼊多条数据
INSERT INTO Table02 SELECT * FROM Table01; --将 Table01 中的所有数据插⼊ Table02 中（注意：可以指定插⼊的列；Table02 必须存在；可指定 Table01 的查询条件）
COMMIT;
4)另外，还可以使⽤ PL/SQL Developer 中使⽤变量的⽅式（该⽅式不怎么实⽤，不做详细介绍）
INSERT INTO Table01 VALUE(&Id, &Name);
5)同时插⼊多条（⽀持多表插⼊）
INSERT ALL
INTO Table01 VALUES(10, '张10')
INTO Table01 VALUES(11, '张11')
INTO Table02 VALUES(20, '李20') --同时插⼊ Table02
SELECT * FROM DUAL;
COMMIT;
注意：
1.INSERT ALL INTO 在效率上，⽐逐条执⾏ INSERT INTO 语句要⾼很多；
2.在使⽤ INSERT ALL INTO 语句插⼊数据时，对于主键使⽤序列插⼊式，多条 INTO 会违反约束条件（即对于同⼀个序列的多条 INTO 会产⽣
相同的序列号），所以使⽤序列插⼊时，并不适⽤使⽤ INSERT ALL INTO 同时插⼊多条数据！
n注意事项：
1.在插⼊数值(number)和字符(char)类型时，Oracle ⽀持数值与字符相互转换，例如：
字符转数值：
INSERT INTO Tab01(id)VALUES('12a');--ORA-01722：⽆效数字
INSERT INTO Tab01(id)VALUES('123');--插⼊成功，结果为123
INSERT INTO Tab01(id)VALUES('456.56');--插⼊成功，结果为457（四舍五⼊）
数值转字符：
INSERT INTO Tab01(name)VALUES(123);--插⼊成功，结果为123
INSERT INTO Tab01(name)VALUES(123.56);--插⼊成功，结果为123.56
提⽰：虽然 Oracle ⽀持这种转换，但是并不建议使⽤该⽅式去写⼊数据，不利于理解和阅读。
2.插⼊字符类型字段时，超过指定长度直接报错，例如：
CREATE TABLE Tab02(name varchar2(2)NOT NULL);
INSERT INTO Tab02(name)VALUES('abcd');--插⼊失败（并不会截断，⽽是直接报错）
INSERT INTO Tab02(name)VALUES('ab');--插⼊成功，结果为ab
3.插⼊字符和⽇期类型时，必须加上单引号，例如：'中国', '22-08-2018'。
4.插⼊的字符类型可以为空（NULL）时，也可以指定为空字符串，例如：
CREATE TABLE Tab03(id number(3)NOT NULL,name varchar2(10)NULL);
INSERT INTO Tab03(id,name)VALUES(1,null);--插⼊成功，结果为NULL
INSERT INTO Tab03(id,name)VALUES(2,'');--插⼊成功，结果也为NULL
INSERT INTO Tab03(id,name)VALUES(3,'  ');--插⼊成功，结果为'  '
SELECT*FROM Tab03 WHERE name IS NULL;
SELECT t.*,dump(name)FROM Tab03 t;
由以上两个查询可以看出，在 Oracle 中对于字符类型，''（空字符串）也将以 NULL 对待，即：空字符串就是 NULL, NULL 也是空字符串。
5.插⼊ DATE 类型的字段时，需要对⽇期格式进⾏转换，例如：
to_date('1985/10/22', 'yyyy/mm/dd')
2.修改数据(UPDATE)
u语法：
UPDATE TABLE_NAME SET column1 = value1[, column2 = value2…] [WHERE 条件];
说明：规则与 INSERT 语句类似。
说明：
u修改数据的⽅式
1)同样，也可以在 PL/SQL Developer 中使⽤ FOR UPDATE 语句，进⾏修改操作
SELECT * FROM Table01 FOR UPDATE;
说明：操作步骤与插⼊数据类似，只是⼀个是修改原有的数据，⼀个是新增数据。
2)使⽤ UPDATE 语句更新
UPDATE Table01 SET Name='张⼭'WHERE Id=1; --更新多个字段，使⽤“,”逗号分隔
COMMIT;
3)⼦查询更新（多列）
UPDATE Table02 SET(Id, Name) = (SELECT Id, Name FROM Table01 WHERE Id=1) WHERE Id=1; --将 Table02 中的 Id, Name 列更新为 Ta ble01 中的 Id, Name，这⾥没有其他列，就以 Id 列代替了
COMMIT;
3.删除数据(DELETE)
u语法：
DELETE FROM TABLE_NAME or VIEW_NAME [WHERE <condition>];
注意：
注意：删除数据前，该记录如果存在外键关联，需要先删除外键表中的关联数据。
u删除数据的⽅式
1)同样，也可以在 PL/SQL Developer 中使⽤ FOR UPDATE 语句，进⾏删除操作
SELECT * FROM Table01 FOR UPDATE;
说明：操作步骤与插⼊数据类似，点击“删除记录”按钮即可。
2)使⽤ DELETE 语句
DELETE FROM Table01 WHERE Id=3;
COMMIT;
3)使⽤ TRUNCATE 语句
TRUNCATE TABLE Table02;
n注意事项
TRUNCATE语句具有以下特征：
1.不能加 WHERE 条件，清除整表数据；
2.不需要 COMMIT 提交，不⽀持事务回滚，并且会结束 SAVEPOINT（回滚点）；
3.效率⾼于 DELETE 语句（速度较快）；
4.不记录⽇志，并清除所占⽤的空间；
5.不会触发 DELETE 出发器等特点。
⽽DELETE语句的特征：
1.可以根据条件删除数据；
2.需要显⽰ COMMIT 提交，⽀持事务回滚；
3.会记录更新⽇志，删除后仍然占⽤物理空间；
4.会触发 DELETE 触发器等。
4.使⽤ MERGE INTO 语句完成增删改操作
MERGE INTO 是 Oracle 9i 中新增的语句，MERGE 语句可以从⼀个或多个源表中选择数据，并将其更新或插⼊到⽬标表中。MERGE 语句允许指定条件，以确定是从⽬标表更新数据还是向⽬标表中插⼊数据。到 Oracle 10g 中⼜对 MERGE INTO 进⾏了改进，改进如下：
1)UPDATE 或 INSERT ⼦句可以是可选的；sql中update什么意思
2)UPDATE 和 INSERT ⼦句可以加 WHERE ⼦句；
3)UPDATE 后⾯可以跟 DELETE ⼦句来删除记录（此时不会更新记录）；
n完整语法
MERGE INTO target_table
USING source_table
ON search_condition
WHEN MATCHED THEN
UPDATE SET col1 = value1, col2 = value2,...
WHERE <update_condition>
[DELETE WHERE <delete_condition>]
WHEN NOT MATCHED THEN
INSERT (col1,col2,...)
values(value1,value2,...)
WHERE <insert_condition>;
n特别说明：
1)使⽤ MERGE INTO 语句固然⽅便，效率应该也理想。但是如果在开发中，需要获取插⼊或更新的⾏数，其实并不好获得。因为，只能通过SQL%
ROWCOUNT获取到受影响的总⾏数。
2)另外，使⽤ MERGE INTO 更新和插⼊数据时，如果使⽤了序列，在不满⾜插⼊条件时，序列也会⾃增长。这是不合理的，但事实是这样。
n⽰例
1)单独使⽤ THEN UPDATE ⼦句，将 A 表的数据更新⾄ B 表
在 SQL Server 中⽀持以下语法将 A 表的数据更新到 B 表（当然 SQL Server 也是⽀持 MERGE 语句）：
UPDATE Tab04 SET [money]=t5.[money] FROM Tab05 AS t5
WHERE Tab04.id = t5.id; --Tab04 遇到 t5 相同的记录，只会更新为 t5 第⼀个记录的值，并不会报错
在 Oracle 中不⽀持以上的更新语法，但可以使⽤ MERGE INTO ⼦句来完成，看⽰例：
--创建表
CREATE TABLE Tab04(id number(3), money number(8,2));
CREATE TABLE Tab05(id number(3), money number(8,2));
--插⼊数据
TRUNCATE TABLE Tab04;
TRUNCATE TABLE Tab05;
INSERT ALL
INTO Tab04 VALUES(1,100)
INTO Tab04 VALUES(2,200)
INTO Tab04 VALUES(6,600)
INTO Tab04 VALUES(6,610)
INTO Tab04 VALUES(7,700)
INTO Tab04 VALUES(9,900)
INTO Tab05 VALUES(1,1000)
INTO Tab05 VALUES(2,2000)
--INTO Tab05 VALUES(2, 2100)
INTO Tab05 VALUES(6,6000)
INTO Tab05 VALUES(8,8000)
INTO Tab05 VALUES(9,9000)
SELECT1FROM DUAL;
COMMIT;
--更新数据
MERGE INTO Tab04 t4
USING Tab05 t5 ON(t4.id = t5.id)
WHEN MATCHED THEN UPDATE = t5.money WHERE t1.id <9;--id=9的记录将不会更新
--查询结果
SELECT*FROM Tab04;
注意事项：
1.Tab04 可以被匹配到多条记录，匹配到多条记录将⼀同更新；
2.Tab04 没有匹配的记录将不会更新，保持原来的值；
3.Tab05 多条记录与 Tab04 中匹配时，将报错：ORA-30926：⽆法在原表中获得⼀组稳定的⾏（与 SQL Server 不⼀样）。因为 MERGE 是确
定性语句，所以不能在同⼀条语句中多次更新⽬标表的同⼀⾏ MERGE。
4.Tab05 的记录与 Tab04 未匹配时，不会报错。
当我希望 Tab05 中相同 Id 中⾦额最⼤的⼀条更新到 Tab04 时，可以这样写：
MERGE INTO Tab04 t4
USING(SELECT id,MAX(money) money FROM Tab05 GROUP BY id) t5 ON(t4.id = t5.id)
WHEN MATCHED THEN UPDATE = t5.money WHERE t4.id <9;    --加了⼀个被处理过的派⽣表（t5）
提⽰：先取消--INTO Tab05 VALUES(2, 2100)的注释
提⽰：
--再次查询
SELECT*FROM Tab04;
2)单独使⽤ THEN INSERT ⼦句，不存在则插⼊数据
1.⾸先，创建⽬标表，并写⼊两条记录
CREATE TABLE tar_dept1 AS SELECT * FROM dept WHERE rownum <= 2;
SELECT * FROM tar_dept1;
2.插⼊源表的记录在⽬标表中不存在的记录
MERGE INTO tar_dept1 t1
USING dept t2 ON(t1.deptno = t2.deptno)
WHEN NOT MATCHED THEN
INSERT (deptno, dname, loc) VALUES(t2.deptno, t2.dname, t2.loc);
SELECT * FROM tar_dept1;
DROP TABLE tar_dept1;    --删除测试表
3)同时更新（存在）或插⼊（不存在）数据
1.创建⽬标表，并写⼊两条记录
CREATE TABLE tar_dept1 AS SELECT * FROM dept WHERE rownum <= 2;
SELECT * FROM tar_dept1;
2.修改源表中的数据（⽤于测试）
UPDATE dept SET loc = loc || 'new';
SELECT * FROM dept;
3.同时更新或插⼊
MERGE INTO tar_dept1 t1
USING dept t2 ON(t1.deptno = t2.deptno)
WHEN MATCHED THEN
UPDATE SET t1.dname = t2.dname, t1.loc = t2.loc
WHEN NOT MATCHED THEN
INSERT (deptno, dname, loc) VALUES(t2.deptno, t2.dname, t2.loc);
SELECT * FROM tar_dept1;
DROP TABLE tar_dept1;    --删除测试表
4)DELETE WHERE ⼦句的使⽤
DELETE ⼦句⼀般⽤的不多，该 DELETE ⼦句仅删除⽬标表中与 ON 和 DELETE WHERE ⼦句同时匹配的⾏（此时，将忽略更新语句）。例如，将以上代码改为下⾯代码时，重新执⾏将得到如下结果：
MERGE INTO tar_dept1 t1
USING dept t2 ON(t1.deptno = t2.deptno)
WHEN MATCHED THEN
UPDATE SET t1.dname = t2.dname, t1.loc = t2.loc
DELETE WHERE t1.deptno > 10--这⾥将删除⼤于10的记录，并忽略更新
WHEN NOT MATCHED THEN
INSERT (deptno, dname, loc) VALUES(t2.deptno, t2.dname, t2.loc);
SELECT * FROM tar_dept1;
n总结
1.MERGE 通常⽤于数据同步的场景，将⼀个数据源中的数据同步到另⼀个数据源（表）中，同时执⾏更新或插⼊操作；
2.注意，在 MERGE 语句中只有当源表中存在查询出记录时，才会执⾏更新或删除操作。可以这样理解，如果不分析执⾏计划，根据推理 MERG
E 的执⾏顺序应该是：
1)⾸先，查询源表中的数据；
2)再根据源表中的每⾏记录，去匹配⽬标表中进⾏匹配；
3)匹配到记录，则执⾏ THEN UPDATE（更新），否则执⾏ THEN INSERT（插⼊）；
4)所以，在源表中不能出现相同的记录，去多次更新⽬标表中的同⼀条记录。
5.回滚(rollback)的使⽤
当我们在编写 SQL 代码进⾏ CUD 时，可以设置回滚点，将当前操作数据回滚到某⼀个状态下。创建回滚点使⽤ SAVEPOINT savepoint_name 语句，回滚到指定位置使⽤ ROLLBACK TO savepoint_name 语句，⽰例如下：
--创建表
CREATE TABLE Tab06(id number(3), money number(8,2));
--创建回滚点:SAVE_INSERT
SAVEPOINT SAVE_INSERT;
--插⼊数据
--TRUNCATE TABLE Tab06;
INSERT ALL
INTO Tab06 VALUES(1,100)
INTO Tab06 VALUES(2,200)
SELECT*FROM DUAL;
--创建回滚点:SAVE_UPDATE
SAVEPOINT SAVE_UPDATE;
--更新数据
UPDATE Tab06 SET money = money *10;
--创建回滚点:SAVE_DELETE
SAVEPOINT SAVE_DELETE;
--删除数据
DELETE FROM Tab06 WHERE id=2;
--可以混滚到任意个回滚点（但只能依次往前回滚）：
ROLLBACK TO SAVE_DELETE;
SELECT*FROM Tab06;
ROLLBACK TO SAVE_UPDATE;
SELECT*FROM Tab06;
ROLLBACK TO SAVE_INSERT;
SELECT*FROM Tab06;
n注意：
当在设置回滚点之后，执⾏了 TRUNCATE 语句，回滚点将失效；
当在执⾏过程中，创建了多个回滚点后，如果回滚到前⼀个回滚点后，就不能再回滚到后⼀个回滚点了，只能依次往前回滚。
6.注意事项
1)在 Oracle 中，所有有关对数据更改的操作（即：INSERT、UPDATE、DELETE）语句，执⾏完成后，都必须执⾏提交命令（COMMIT）。
2)当使⽤ FRO UPDATE 语句增删改时，并点击锁表按钮后会进⾏锁表，锁表后其他会话将不能进⾏增删改操作，所以不建议使⽤该⽅式。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。