【ORACLE】ORACLE DML、DDL和事务

Myth丶恋晨 2023-07-17 12:52 43阅读 0赞

最近在准备OCP考试，复习071的时候，发现对于DML、DDL和事务还有一些理解不足的地方，现在记录下来加深下印象  
其中比较关键的地方就是 DML和事务的关系

## DML介绍 ##

DML（data manipulation language）数据操纵语言：

就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。 主要用来对数据库的数据进行一些操作。

比如：  
SELECT 列名称 FROM 表名称  
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值  
INSERT INTO table\_name (列1, 列2,…) VALUES (值1, 值2,…)  
DELETE FROM 表名称 WHERE 列名称 = 值

## DDL介绍 ##

DDL（data definition language）数据库定义语言：

其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。

比如：  
CREATE TABLE 表名称(列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,…)ALTER TABLE table\_nameALTER COLUMN column\_name datatypeDROP TABLE 表名称DROP DATABASE 数据库名称

## Oracle事务 ##

一般事务（DML）即数据修改（增、删、改）的事务  
事务会将所有在事务中被修改的数据行加上锁（行级锁），来阻止其它人（会话）同时对这些数据的修改操作。  
当事务被提交或回滚后，这些数据才会被释放锁。

举个例子：  
当A操作一条数据N1后，暂未提交事务 ，此时B又上来操作同一条数据N1，这时的情况是：  
1、所有除A以外的人看不到被A所修改后的数据  
2、B会处于等待状态，直到A提交或回滚了针对这条数据的修改（这也就是行级锁的概念）  
3、当A提交事务后，所有人可以看到被A修改后的数据，看不到B修改后的数据。但B能看到自己修改后的数据（与A一样，因为B还未提交事务）。  
4、当B提交事务后，所有人可以看到被B修改后的数据。

##### 事务的考点之一： #####

在PS/SQ和SQL*PLUS中，对数据修改完后，如果用户未提交事务，但关闭或断开了PS/SQL，此时ORACLE会立即提交此会话的事务。

我们可以在事务中使用保存点来回滚到指定的时间节点上来，但如果用提交事务(commit)后，所有保存点将被删除。

举个例子：

savepoint a1;    --设置一个保存点 a1;
    update tmp set username='张三' where userid='101'  --修改一条数据
    savepoint a2;   --设置一个保存点 a1;
    update tmp set username='李四' where userid='102  --再修改一条数据
    rollback to a2;   --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。
    rollback to a1;   --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。
    rollback;           --回滚全部。即撤消至上一次提交事务后的所有数据修改。
    commit;  --提交事务 将回滚后的事务提交，并会删除所有保存点。

## 以上三者的联系 ##

个人理解，在DDL、DML和事务的联系，就在于事务完成时的提交上

## 提交 ##

提交的类型：

提交数据有三种类型：  
显式提交、隐式提交及自动提交。  
1、显式提交：用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；  
2、隐式提交：用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。(此隐式提交是在自己的session，如果在其他人的session(如用户a)中正在修改相同的数据，则引起隐式提交的语句（用户a的k另一个session)则必需等待)  
3、自动提交：若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

查看当前是否是自动提交：show autocommit；

##### 隐式提交： #####

隐式提交的定义  
又名自动提交，即无需显示执行commit语句，session中的操作被自动提交到数据库的过程。  
隐式提交的方式  
1、正常执行完ddl语句。包括create，alter，drop，truncate，rename。  
2、正常执行完dcl语句。包括grant，revoke。  
3、正常退出isql\*plus，没有明确发出commit或者rollback。

##### 考点之二： #####

隐式提交的注意事项 
    1、执行ddl语句时，前面的dml操作也会被提交到数据库中 
    因为是在一个session里，那执行ddl语句的时候前面的dml语句肯定也会“不可幸免”的被提交到库中。 
    2、即使ddl语句执行失败，前面的dml操作也会被提交到数据库中 
    这就有点儿让人奇怪了，ddl都执行失败了，怎么还会提交呢？这就需要探究一下隐式提交的本质了（下文有叙述）。  
    3、在前面1和2的基础上总结 
    为了避免隐式提交或者回滚，尽量保证一条或者几条DML操作完成后有显示的提交或者回滚，防止后续执行的DCL或者DDL自动提交前期的DML操作。

隐式提交的本质

##### 考点之三： #####

1、一条ddl语句执行了两次commit 
    commit; 
    ddl statement; 
    commit; 
    第一个commit将当前session中未提交的事务隐式提交，以保证ddl语句失败时的回滚位置。 
    第二个commit将ddl

2、为什么需要隐式提交？  
为了保证事务的一致性。我们在执行ddl语句的时候，oracle需要在它的系统表中进行元数据的记录操作（即：除了建表还会进行不少insert操作），如果它不隐式提交就无法保证一致性；从内部运行机制来看ddl语句和dml语句还是有很大区别的，dml会对每个语句的每条记录都做日志记录以便于回滚，而ddl往往没必要搞这么复杂，从功能和易用性上看隐式提交都是最好的选择。