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

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

 

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

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

 

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  /

界面演示效果：

 

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  /

界面演示效果：

 

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  /

界面演示效果：

 

2.4 使用delete删除语句

delete删除语句语法：

delete from 表名 [where 条件];

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

SQL> delete from dept where deptno=50;

界面效果演示：

 

 

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，表的结构如下：（编号，卡号，名字，余额）

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

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

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

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

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

 

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

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

 

第五步：验证回滚

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

提示：

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

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

 

 

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

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

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

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

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

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

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

 

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