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 列更新为 Table01 中的 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 子句可以是可选的；

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)

SELECT 1 FROM DUAL;

COMMIT;

--更新数据

MERGE INTO Tab04 t4

USING Tab05 t5 ON(t4.id = t5.id)

WHEN MATCHED THEN UPDATE SET t4.money = 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 SET t4.money = 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 语句中只有当源表中存在查询出记录时，才会执行更新或删除操作。可以这样理解，如果不分析执行计划，根据推理 MERGE 的执行顺序应该是：

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 语句增删改时，并点击锁表按钮后会进行锁表，锁表后其他会话将不能进行增删改操作，所以不建议使用该方式。