Oracle：数据操作语句 (DML-insert into,update,delete) 部分、事务控制 (TCL-commit,rollback)

原文链接 http://www.cnblogs.com/tqsummer/archive/2010/07/11/1775209.html

 

数据控制语句 (DML) 部分

(1) INSERT 往数据表里插入记录的语句

INSERT INTO 表名 VALUES(所有字段都必须有值);

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);  

INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 另外的表名;

 

字符串类型的字段值必须用单引号括起来, 例如: 'GOOD DAY'。

如果字段值里包含单引号需要进行字符串转换, 我们把它替换成两个单引号。

例如：INSERT INTO TEST(ID, NAME) VALUES(10001, 'ABC''');

字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验。

日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒；或者用字符串转换成日期型函数TO_DATE('2001-08-01','YYYY-MM-DD')。

INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型, 方法借用ORACLE里自带的DBMS_LOB程序包。

INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号：

CREATE SEQUENCE 序列号的名称(最好是表名+序列号标记) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999

INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

 

(2) DELETE 删除数据表里记录的语句

DELETE FROM 表名  WHERE 条件;

 

删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成UNUSED, 如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间。

TRUNCATE TABLE 表名; --清除表里的所有记录，释放表空间，保留表结构。

 

(3) UPDATE 修改数据表里记录的语句

UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, …… WHERE 条件;

 

如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验。

值N超过定义的长度会出错, 最好在插入前进行长度校验。

 

根据记录本身的某个字段值，来更新表中符合条件的所有记录。例如有个用户表，原来有一个字段2YEARS，用"YES"/"NO"表示用户注册是否满两年，现在需要用另外一个字段XYEARS来表示用户注册满了X年：

UPDATE USERS SET XYEARS = DECODE(2YEARS, 'YES', 2, 0);

 

事务控制 (TCL) 语句

COMMIT;--提交  

ROLLBACK;--回滚

 

DML语句对表都加上了行级锁，确认完成后，必须加上事物处理结束的命令COMMIT才能正式生效，否则改变不一定写入数据库里。如果想撤回这些操作, 可以用命令 ROLLBACK 复原。

在运行INSERT，DELETE和UPDATE语句前最好估算一下可能操作的记录范围，应该把它限定在较小范围内，例如一万条记录，否则ORACLE处理这个事物用到很大的回退段。程序响应慢甚至失去响应。如果记录数上十万以上这些操作。可以把这些SQL语句分段分次完成。其间加上COMMIT确认事物处理。

 

事务的4个基本特征 

1、原子性 (Atomicity ) 

原子性属性用于标识事务是否完全地完成，一个事务的任何更新要在系统上完全完成，如果由于某种原因出错，事务不能完成它的全部任务，系统将返回到事务开始前的状态。(要么全部执行，要么全部不执行)

让我们再看一下银行转帐的例子。如果在转帐的过程中出现错误，整个事务将会回滚。只有当事务中的所有部分都成功执行了，才将事务写入磁盘并使变化永久化。

2、一致性(Consistency ) 

事务在系统完整性中实施一致性，这通过保证系统的任何事务最后都处于有效状态来实现。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。因为事务开始时系统处于一致状态，所以现在系统仍然处于一致状态。 再让我们回头看一下银行转帐的例子，在帐户转换和资金转移前，帐户处于有效状态。如果事务成功地完成，并且提交事务，则帐户处于新的有效的状态。如果事务出错，终止后，帐户返回到原先的有效状态。 

记住，事务不负责实施数据完整性，而仅仅负责在事务提交或终止以后确保数据返回到一致状态。理解数据完整性规则并写代码实现完整性的重任通常落在开发者肩上，他们根据业务要求进行设计。 当许多用户同时使用和修改同样的数据时，事务必须保持其数据的完整性和一致性。

数据一致性指系统中每个用户都能够取得具备一致性的数据，同时还能够看到自己或其他用户所提交的事务对数据的修改。

3、隔离性 (Isolation) 

在隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。 这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。重要的是，在隔离状态执行事务， 系统的状态有可能是不一致的，在结束事务前，应确保系统处于一致状态。但是在每个单独的事务中，系统的状态可能会发生变化。如果事务不是在隔离状态运行， 它就可能从系统中访问数据，而系统可能处于不一致状态。通过提供事务隔离，可以阻止这类事件的发生。在银行的示例中，这意味着在这个系统内，其他过程和事务在我们的事务完成前看不到我们的事务引起的任何变化，这对于终止的情况非常重要。如果有另一个过程根据帐户余额进行相应处理，而它在我们的事务完成前就能看到它造成的变化，那么这个过程的决策可能建立在错误的数据之上，因为我们的事务可能终止。这就是说明了为什么事务产生的变化，直到事务完成，才对系统的其他部分可见。隔离性不仅仅保证多个事务不能同时修改相同数据，而且能够保证事务操作产生的变化直到变化被提交或终止时才能对另一个事务可见，并发的事务彼此之间毫无影响。这就意味着所有要求修改或读取的数据已经被锁定在事务中，直到事务完成才能释放。大多数数据库，通过使用锁定来实现隔离，事务中涉及的各个数据项或数据集使用锁定来防止并发访问。 

4、持久性 (Durabilily) 

持久性意味着一旦事务执行成功，在系统中产生的所有变化将是永久的。应该存在一些检查点防止在系统失败时丢失信息。甚至硬件本身失败，系统的状态仍能通过在日志中记录事务完成的任务进行重建。持久性的概念允许开发者认为不管系统以后发生了什么变化，完 成的事务是系统永久的部分。 在银行的例子中，资金的转移是永久的，一直保持在系统中。这听起来似乎简单，但这，依赖于将数据写入磁盘，特别需要指出的是，在事务完全完成并提交后才写 入磁盘的。 所有这些事务特性，不管其内部如何关联，仅仅是保证从事务开始到事务完成，不管事务成功与否，都能正确地管理事务涉及的数据 ，当事务处理系统创建事务时，将确保事务有某些特性。组件的开发者们假设事务的特性应该是一些不需要他们亲自管理的特性。 

 

为什么需要对事务并发控制 

数据并发访问指多用户同时访问相同的数据。

如果不对事务进行并发控制，我们看看数据库并发操作是会有那些异常情形 

1、丢失更新(Lost update) 

两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。 

2、脏读(Dirty Reads) 

一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。 

3、非重复读(Non-repeatable Reads) (不可重复读)

一个事务对同一行数据重复读取两次，但是却得到了不同的结果。同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生非重复读。 

4、二类丢失更新(Second lost updates problem) 

无法重复读取的特例。有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。 

5、幻像读(Phantom Reads) 

事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据(这里并不要求两次查询的SQL语句相同)。这是因为在两次查询过程中有另外一个事务插入数据造成的。 

 

数据库的隔离级别 

为了兼顾并发效率和异常控制，在标准SQL规范中，定义了4个事务隔离级别:

1、未提交读(Read Uncommitted) 

即使一个更新语句没有提交,但是别的事务可以读到这个改变。这是很不安全的。允许任务读取数据库中未提交的数据更改，也称为脏读。 (幻像读、非重复读和脏读都允许)

2、提交读(Read Committed) 

可防止脏读，意思就是语句提交以后即执行了COMMIT以后别的事务就能读到这个改变。只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别。(允许幻像读、非重复读，不允许脏读)

3、可重复读(Repeatable Read)

在同一个事务里面先后执行同一个查询语句的时候,得到的结果是一样的。在同一个事务内的查询都是事务开始时刻一致的。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读 。(允许幻想读，不允许不可重复读和脏读)

4、串行读(Serializable) 

直译就是"序列化",意思是说这个事务执行的时候不允许别的事务并发执行。完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。(幻想读、不可重复读和脏读都不允许)

 

隔离级别对并发的控制：

 丢失更新脏读非重复读二类丢失更新幻像读未提交读YYYYY提交读NNYYY可重复读NNNNY串行读NNNNN

 

 

 

 

 

 

 

 

Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。所以Oracle不支持脏读。

SQL标准所定义的默认事务隔离级别是SERIALIZABLE，但是Oracle 默认使用的是READ COMMITTED

设置隔离级别SQL：SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

如果在每个事务开始时都使用 SET TRANSACTION 语句，将加重网络及处理器的负担。用户可以使用 ALTER SESSION 语句改变一个会话所有内事务的默认隔离级别：

1、ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;

2、ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

(无法设置会话级别的只读隔离模式)

 

并发一致性问题的解决办法 

1、封锁(Locking) 

封锁是实现并发控制的一个非常重要的技术。所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁。加锁后事务T就对该 数据对象有了一定的控制，在事务T释放它的锁之前，其它的事务不能更新此数据对象。基本的封锁类型有两种：排它锁(Exclusive locks 简记为X锁)和共享锁(Share locks 简记为S锁)。 

排它锁又称为写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其它事务在T释放A上的锁之前不能再读取和修改A。 

共享锁又称为读锁。若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其它事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。 

2、封锁协议 

在运用X锁和S锁这两种基本封锁，对数据对象加锁时，还需要约定一些规则，例如应何时申请X锁或S锁、持锁时间、何时释放等。我们称这些规则为封锁协议 (Locking Protocol)。对封锁方式规定不同的规则，就形成了各种不同的封锁协议。下面介绍三级封锁协议。三级封锁协议分别在不同程度上解决了丢失的修改、不 可重复读和读"脏"数据等不一致性问题，为并发操作的正确调度提供一定的保证。

1级封锁协议是：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束(COMMIT)和非正常结束(ROLLBACK)。 1级封锁协议可防止丢失修改，并保证事务T是可恢复的。在1级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读"脏"数据。 

2级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。2级封锁协议除防止了丢失修改，还可进一步防止读"脏"数据。 

3级封锁协议是：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。3级封锁协议除防止了丢失修改和不读'脏'数据外，还进一步防止了不可重复读。 

 

一般处理并发问题时的步骤： 

1、开启事务。 

2、申请写权限，也就是给对象(表或记录)加锁。 

3、假如失败，则结束事务，过一会重试。 

4、假如成功，也就是给对象加锁成功，防止其他用户再用同样的方式打开。 

5、进行编辑操作。 

6、写入所进行的编辑结果。 

7、假如写入成功，则提交事务,完成操作。 

8、假如写入失败，则回滚事务，取消提交。 

9、(7.8)两步操作已释放了锁定的对象，恢复到操作前的状态。