文档章节

Oracle 增删改(INSERT、DELETE、UPDATE)语句

o
 osc_zoa3moe9
发布于 2019/12/07 22:07
字数 3155
阅读 24
收藏 0

精选30+云产品,助力企业轻松上云!>>>

Ø  简介

本文介绍 Oracle 中的增删改语句,即 INSERTDELETEUPDATE 语句的使用。是时候展现真正的技术了,快上车clip_image002

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.   点击锁表按钮

clip_image003

3.   编辑数据 -> 记入改变 -> 表解锁按钮

clip_image004

4.   最后点击提交

clip_image005

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;

clip_image006

SELECT t.*, dump(name) FROM Tab03 t;

clip_image007

由以上两个查询可以看出,在 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)   单独使用 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;

clip_image008

注意事项:

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;

clip_image009

 

2)   单独使用 THEN INSERT 子句,不存在则插入数据

1.   首先,创建目标表,并写入两条记录

CREATE TABLE tar_dept1 AS SELECT * FROM dept WHERE rownum <= 2;

SELECT * FROM tar_dept1;

clip_image010

 

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;

clip_image011

DROP TABLE tar_dept1;    --删除测试表

 

3)   同时更新(存在)或插入(不存在)数据

1.   创建目标表,并写入两条记录

CREATE TABLE tar_dept1 AS SELECT * FROM dept WHERE rownum <= 2;

SELECT * FROM tar_dept1;

clip_image012

 

2.   修改源表中的数据(用于测试)

UPDATE dept SET loc = loc || 'new';

SELECT * FROM dept;

clip_image013

 

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;

clip_image014

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;

clip_image015

 

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_INSERT;

SELECT * FROM Tab06;

 

ROLLBACK TO SAVE_UPDATE;

SELECT * FROM Tab06;

 

ROLLBACK TO SAVE_DELETE;

SELECT * FROM Tab06;

 

n  注意:

当在设置回滚点之后,执行了 TRUNCATE 语句,回滚点将失效;

当在执行过程中,创建了多个回滚点后,如果回滚到前一个回滚点后,就不能再回滚到后一个回滚点了,只能依次往前回滚。

 

6.   注意事项

1)   Oracle 中,所有有关对数据更改的操作(即:INSERTUPDATEDELETE)语句,执行完成后,都必须执行提交命令(COMMIT)。

2)   当使用 FRO UPDATE 语句增删改时,并点击锁表按钮后会进行锁表,锁表后其他会话将不能进行增删改操作,所以不建议使用该方式。

o
粉丝 1
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
Oracle数据库的触发器

一:概念   是oracle在发生某些事件时,可以自动触发并调用的plsql程序,可以定义在事件上,由事件自动触发。 二:分类   1)系统触发器:由系统事件触发的plsql程序,比如登陆oracle数据...

osc_pfth9njc
2018/05/17
0
0
PG TO Oracle 增量同步-外部表

背景   最近在负责公司数据Oracle转PG;老平台数据库:Oracle11g;新平台数据库:PostgreSQL12。由于平台统计规则有变动;所以正在推广的游戏数据无法全部迁移过来;只能在老平台上运行。而...

lottu
04/28
0
0
Oracle Kafka 测试环境搭建

搭建一个 Oracle + Oracle Golden Gate + Kafka Connector 测试环境 ,把类似的oracle的增删改操作发布到Kafka上。所有工具都是有现成的。 类似于这个: https://www.pythian.com/blog/golde...

zb1481207860565
2017/02/03
42
0
二十一、Oracle学习笔记:编写函数和触发器

一、函数或过程的选择 1.如果要进行DML(增删改),选择存储过程 2.如果没有DML,选择函数 二、函数 1.格式 2.实例 函数的参数类型也和存储过程一样规则 三、触发器 1.定义:是ORACLE在发生某...

BigDodo
03/31
0
0
SQL Server温故系列(1):SQL 数据操作 CRUD 之增删改合

1、插入语句 INSERT INTO 1.1、用 INSERT 插入单行数据 1.2、用 INSERT 插入多行数据 1.3、用 INSERT 插入子查询结果行 1.4、INSERT 小结及特殊字段插入方法 2、删除语句 DELETE 2.1、用 DE...

osc_syqp8xt3
04/16
2
0

没有更多内容

加载失败,请刷新页面

加载更多

【Nginx】实现负载均衡、限流、缓存、黑白名单和灰度发布,这是最全的一篇了!

写在前面 在《【高并发】面试官问我如何使用Nginx实现限流,我如此回答轻松拿到了Offer!》一文中,我们主要介绍了如何使用Nginx进行限流,以避免系统被大流量压垮。除此之外,Nginx还有很多...

osc_6l5fg87g
24分钟前
9
0
一小时完成后台开发:DjangoRestFramework开发实践

DjangoRestFramework开发实践 在这之前我写过一篇关于Django与Drf快速开发实践的博客,Django快速开发实践:Drf框架和xadmin配置指北,粗略说了一下Drf配置和基本使用,不过里面只是涉及到最...

osc_z2ru77w0
25分钟前
14
0
数据载入、存储及文件格式知识图谱-《利用Python进行数据分析》

所有内容整理自《利用Python进行数据分析》,使用MindMaster Pro 7.3制作,emmx格式,源文件已经上传Github,需要的同学转左上角自行下载或者右击保存图片。

osc_161difcz
26分钟前
8
0
Java异常

一、异常? java系统中将java.lang.Throwable类作为异常的最根类 [java.lang.Throwable是所有异常或错误的顶级类,可以处理任何异常] * java.lang.Throwable * |-----java.lang.Error:一般...

osc_o44vh5qb
28分钟前
23
0
(1)Linux系统中到底应该怎么理解系统的平均负载

每次发现系统变慢时,我们通常做的第一件事,就是执行 top 或者 uptime 命令,来了解系统的负载情况。比如像下面这样,我在命令行里输入了 uptime 命令,系统也随即给出了结果。 $ uptime...

osc_i5oyb1xr
29分钟前
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部