Ø  简介

本文介绍 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 ];

注意：删除数据前，该记录如果存在外键关联，需要先删除外键表中的关联数据。

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

        [DELETE WHERE ]

    WHEN NOT MATCHED THEN

        INSERT (col1,col2,...)

        values(value1,value2,...)

        WHERE ;

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