Oracle数据库第三课——PL/SQL中的SQL，在PL/SQL中使用DML语句，掌握事务处理

ゝ一世哀愁。 2024-04-17 14:16 47阅读 0赞

**知识点：**在PL/SQL语句块中使用DML语句的基本知识，包括select into、insert、update、delete等操作。事务处理的掌握，理解提交、回滚、保存点等概念。

# 1、什么是DML（知识点回顾） #

SQL语言是针对数据库而言的一门语言，它可以创建数据库、数据表，可以针对数据库的数据进行增、删、改、查等操作，可以创建视图、存储过程，可以赋予用户权限等。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70][]

Oracle数据库的DML表数据的操作有三种：① insert （插入）；② update（更新）；③ delete（删除）。

# 2、在PL/SQL语句块中使用DML #

## 2.1 使用select into进行变量初始化 ##

在 PL/SQL 语句块中，存在两种主要的变量赋值方法。我们在上一章中已经了解过，主要学习了第 1 种方法，也就是使用 “ **:= **” 对变量进行初始化。除了在声明变量的同时进行赋值之外，在 PL/SQL 语句块的声明部分被声明的变量，后期可以使用选择语句进行赋值。这里，我们将学习如何借助于 SELECT INTO 语法，使用选择语句来初始化变量。

select into赋值语法：

### **select** **item\_name ****into** **v\_name from table\_name \[where condition\];** ###

**语法中：**

*  item\_name：表示查询项（表中某一列的列名）。 
 *  v\_name：表示变量名。 
 *  table\_name：表示表名。 
 *  condition：表示查询条件。

**注意：**

（1）查询结果只能返回单行语句才能赋值到变量中保存，返回多行或者零行语句则报错。

（2）任何返回的单行数据可以经过计算再给变量赋值。

### 实例练习：查询emp表中编号为7369的员工，输出员工星姓名和年薪 ###

**实现思路：**①先声明两个变量，分别用来存放员工姓名和年薪。②使用select into进行变量初始化，即将表中的姓名直接赋值给变量v\_name，将表中的薪水乘以12以后再赋值给变量v\_sal。③输出结果。

SQL> DECLARE
      2     v_sal number(7);
      3     v_name varchar2(10);
      4  BEGIN
      5     select ename ,sal*12 into v_name,v_sal from emp where empno=7369;
      6     dbms_output.put_line(v_name || '的年薪是：' ||v_sal);
      7  END;
      8  /

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1][]

## 2.2 使用insert插入语句 ##

insert插入语句的语法：

### insert into 表名 (列名1,列名2,……,列名n)  values (值1,值2,……,值n); ###

### 实例练习：向dept表中插入一行数据（部门编号50，部门名称support，部门地址wuhan） ###

**实现思路：**

①查询dept表的结构，以知道需要插入哪些数据及类型，语句desc dept;

②查询dept表的所有数据，以免重复输入，语句select \* from dept;

③使用PL/SQL语句块插入一行数据到dept表中

④执行成功以后，再次查询dept表中的所有数据，进行验证

SQL> DECLARE
      2     v_deptno number(2) := 50;
      3     v_dname varchar2(14) := 'support';
      4     v_loc varchar2(13) :='wuhan';
      5  BEGIN
      6     insert into dept (deptno,dname,loc) values (v_deptno,v_dname,v_loc);
      7  END;
      8  /

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 2][]

## 2.3 使用update更新语句 ##

update更新语句的语法：

### update 表名 set 列名1 = 值1, 列名2 = 值2, …… , 列名n = 值n \[where 条件\]; ###

### 实例练习：将dept表中部门为50的记录，loc地址修改为hubei ###

SQL> DECLARE
      2     v_loc varchar2(13) := 'hubei';
      3  BEGIN
      4     update dept set loc = v_loc where deptno = 50;
      5  END;
      6  /

**界面演示效果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 3][]

## 2.4 使用delete删除语句 ##

delete删除语句语法：

### delete from 表名 \[where 条件\]; ###

### 实例练习：删除表dept中部门编号为50的记录 ###

SQL> delete from dept where deptno=50;

**界面效果演示：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 4][]

# 3、PL/SQL中事务处理 #

## 3.1 什么是事务 ##

事务（Transaction）是一个由多条SQL语句组成的工作逻辑单元，这些语句要么全部执行成功，要么全部不执行，只要有一条SQL语句执行失败，已执行的SQL语句会全部回滚到执行之前的状态，这样就保证了数据库数据的一致性。

**       举例：**比如银行的转账业务，该业务至少可以分为 A 账户的转出 和 B 账户的转入。也就是从 A 账户的余额中减掉一定的数额，然后再将 B 账户的余额增加一定的数额。整个过程等于是修改了两条记录，这两个操作就可以认为组成了一个事务。它们应该是一个整体，一个单个的逻辑工作单元，要么都执行成功，要么都不执行。

## 3.2 事务的特点 ##

当使用事务修改多个数据表时，如果在处理的过程中出现了某种错误，例如系统死机或突然断电等情况，则返回结果是全部数据均没有被保存。因为事务处理的结果只有两种：一种是在事务处理的过程中，如果发生了某种错误则整个事务全部回滚，使所有对数据的修改全部撤销，事务对数据库的操作是单步执行的，当遇到错误时可以随时地回滚；另一种是如果没有发生任何错误且每一步的执行都成功，则整个事务全部被提交。从而可以看出，有效地使用事务不但可以提高数据的安全性，而且还可以增强数据的处理效率。

事务包含 4 种重要的属性，被统称为 **ACID（原子性、一致性、隔离性和持久性）**特性，对一组 SQL 语句操作构成的事务,数据库操作系统必须确保这些操作的原子性、一致性、隔离性、持久性。

（1）原子性(Atomicity)

事务的原子性是指事务中包含的所有操作要么全做，要么不做，也就是说所有的活动在数据库中要么全部反映，要么全部不反映,以保证数据库的一致性。

（2）一致性(Consistency)

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用事务的修改，以保持所有数据的完整性。如果事务成功，则所有数据将变为一个新的状态； 如果事务失败，则所有数据将处于开始之前的状态。

（3）隔离性(Isolation)

隔离性是指由事务所做的修改必须与其他事务所做的修改隔离。事务查看数据时，数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事物修改它之后的状态，事务不会查看中间状态的数据。

（4）持久性(durability)

事务的持久性是指在事务处理结束后,它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失,这是数据的重要性决定的。

## 3.3 为什么要使用事务 ##

**       事务的作用：用于保持数据库数据的一致性。**

## 3.4 操作事务 ##

**          事务的结束：COMMIT;   和    ROLLBACK;**

Oracle11g 中的事务是隐式自动开始的，它不需要用户显示地执行开始事务语句。但对于事务处理，则需要用户进行指定的操作，通常在以下情况时，Oracle 认为一个事务结束了。

a)  执行 COMMIT 语句提交事务。

b)  执行 ROLLBACK 语句撤销事务。

c)  执行一条数据定义语句，比如 CREATE、DROP 或 ALTER 等语句。如果该语句执行成功，那么 Oracle 系统会自动执行 COMMIT 命令；否则，Oracle 系统会自动执行 ROLLBACK 命令。

d)  执行一个数据控制命令，比如 GRANT、REVOKE 等控制命令，这种操作执行完毕，Oracle 系统会自动执行 COMMIT 命令。

e)  正常的断开数据库的联接、正常地退出 SQL\*Plus 环境，则 Oracle 系统会自动执行 COMMIT 命令；否则，Oracle 系统会自动执行 ROLLBACK 命令。

综上 5 种情况可知，Oracle 结束一个事务归根结底，要么执行 COMMIT 语句，要么执行ROLLBACK 语句，下面我们来做详细介绍。

### （1）保存点（savepoint语句） ###

保存点用于保存事务未开始之前数据库各方面的状态。用于回滚时，如果想要撤回刚刚的操作，可以在这些操作之前设置一个保存点，在需要撤回时使用rollback进行回滚。

**声明一个保存点的语法：****savepoint  保存点名称 ; **

### （2）提交事务 ###

commit语句用于把内存中的数据写到数据库。事务的提交指的是把你刚刚操作的数据真正的写入到数据库中，这个时候你就不能在运用rollback进行撤销你刚才的操作了。**数据库一旦commit了数据，那么就会撤销所有的保存点**。所以，这时rollback会得到一个错误（ORA-01086: 从未在此会话中创建保存点 's' 或者该保存点无效）。当使用commit语句结束事务之后，其他会话将可以查看到事务变化后的新数据。

**提交语句的语法：commit;**

### （3）回滚事务 ###

事务回滚后，数据库各项状态恢复到最近的保存点；该事务好像就没有发生一样。在说回退事务之前，先理解Oracle事务的保存点(savepoint)的概念和作用，保存点是事务中的一点，用于取消部分事务，保存点记录的是当前数据库的状态。

**回滚语句的语法：**

**rollback to 保存点名称 ;     --撤销单个事务**

**　　　**　　　　**　rollback ;                            --不加保存点名称则撤销所有存在的事物**

### （4）Oracle事务实例练习：利用事务模拟银行转账例子（小明给小华转账500元） ###

**目的：**通过该实例，理解Oracle数据库的事务操作，理解提交、回滚事务和保存点

**第一步：**在Oracle数据库中，使用scott用户，创建一个数据库表users，表的结构如下：（编号，卡号，名字，余额）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 5][]

**第二步：**向表users中插入两行数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 6][]

**第三步：**查看当前操作效果（验证提交语句）

再开启一个新的 SQL\*Plus 环境（要求当前的 SQL\*Plus 环境不能退出，如果退出，Oracle 系统会自动执行 commit 语句提交），然后在新环境下查询users 表，会发现新增的记录不存在。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 7][]

如果使用 commit 语句提交事务后，则在另一个 SQL\*Plus 环境下就可以查询到新增的记录了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 8][]

在另一个SQL\*Plus 环境下，再次查询时，可以查到表中的记录。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 9][]

**第四步：**在PL/SQL程序中利用事务实现转账

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 10][]

在另一个SQL\*Plus 环境下，可以看到记录已经发送变化。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 11][]

**第五步：**验证回滚

输入第四步的语句块， 注意把程序中的commit换成rollback后试一试最后结果。

提示：

*  在事务commit提交前，可以使用rollback 到指定的保存点，来回退到指定的保存点。
 *  在事务commit提交后，保存点会被删除，这个时候，就无法进行回退了。

任何commit操作，也就是事务提交操作，都会导致savepoint的被删除！！！

### （5）Oracle事务回滚练习：模拟用户账户金额减少 ###

**第一步：**向users表中插入一行数据，并提交事务

insert into users values(3,'1003','豆豆',2000);
    commit;

此时，再次查询表users中的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 12][]

**第二步：**声明一个保存点：savepoint chushi;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 13][]

**第三步，**再次声明一个保存点：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 14][]

**第四步，**验证事务回滚：rollback to 保存点名称;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 15][]

==============这里是结束分割线================

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7ba010776dd143e38d08250cc6b8f463.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a8380bbf32154b41a276a8bb99d2b2eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/13034df4a67440f5b2d028c4b430e7a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e70432eefb7c45e9a850faeecdf992fd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/661fcd1cb9304ca2afbee3307c032591.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7acdbf15487544e2a8a5455c482df6df.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c54ae64cc7214f25976011e16e66561b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/9d580877e231457b835c7130f13f6a31.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7dbd565242ad4060949812de09ecd0ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/dcae7561255547e99d1f56c3e60d0b63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/03f31a12c01945c88295fe38c4e74d02.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/7677e368fba54b6b8e2b9ba1381f2f04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/fc8ab2d05b024bcd8716b26d4e2eb30d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/aceb16e087c44b019b47b4ed752a5721.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/325d046dd3de49cea01d45099c40da30.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e39831ae10094a7e944b44c0b766de26.png