oracle心得5--oracle的增删改查

1.Oracle 中的增删改查 DML(Data Manipulation Language – 数据操作语言 ) 可以在下列条件下执行:向表中插入数据、修改现存数据、删除现存数据。 事务是由完成若干项工作的DML语句组成的 2. 插入数据操作 按列的默认顺序列出各个列的。 在 INSERT 子句中随

1.Oracle中的增删改查

DML(Data Manipulation Language – 数据操作语言) 可以在下列条件下执行:向表中插入数据、修改现存数据、删除现存数据。

事务是由完成若干项工作的DML语句组成的

2.插入数据操作

按列的默认顺序列出各个列的值。 在 INSERT 子句中随意列出列名和他们的值。字符和日期型数据应包含在单引号中。

在SQL 语句中使用 & 变量指定列值。& 变量放在VALUES子句中。&变量就是一个占位符，就是在控制台输入值

INSERT INTO departments

(department_id, department_name, location_id)

VALUES (&department_id, '&department_name',&location);

3.更新数据操作

添加一次只能添加一条数据,但是更新可以一次多条数据。

如果省略WHERE子句，则表中的所有数据都将被更新

4.删除数据操作

5.数据库事务由以下的部分组成:

一个或多个DML 语句

一个 DDL(Data Definition Language – 数据定义语言) 语句

一个 DCL(Data Control Language – 数据控制语言) 语句

以第一个 DML 语句的执行作为开始；以下面其中之一作为结束:

COMMIT 或 ROLLBACK 语句；DDL 或 DCL 语句（自动提交）；用户会话正常结束；系统异常终了。

使用COMMIT 和 ROLLBACK语句,我们可以: 确保数据完整性。

数据改变被提交之前预览。将逻辑上相关的操作分组。

提交或回滚前的数据状态

自动提交在以下情况中执行:DDL 语句；DCL 语句。

不使用 COMMIT 或 ROLLBACK 语句提交或回滚，正常结束会话。

会话异常结束或系统异常会导致自动回滚

提交后的数据状态

改变前的数据状态是可以恢复的

执行 DML 操作的用户可以通过 SELECT 语句查询之前的修正

其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

DML语句所涉及到的行被锁定， 其他用户不能操作。

数据回滚后的状态

使用 ROLLBACK 语句可使数据变化失效:

数据改变被取消。修改前的数据状态被恢复。锁被释放。

6. 数据库的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事物 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.

幻读: 对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.

一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

7.事务的概念特性

概念：在数据库中事务是工作的逻辑单元,一个事务是由一个或多个完成一组的相关行为的SQL语句组成,通过事务机制确保这一组SQL语句所作的操作要么完全成功执行,完成整个工作单元操作,要么一点也不执行。

主要特性：确保数据库的完整性。

事务的ACID特性:

对一组SQL语句操作构成事务,数据库操作系统必须确保这些操作的原子性,一致性,隔离性,持久性.

原子性(Atomicity)

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

一致性(Consistency)

事务的一致性是指数据库在事务操作前和事务处理后,其中数据必须满足业务的规则约束。

隔离性(Isolation)

隔离性是指数据库允许多个并发的事务同时对其中的数据进行读写或修改的能力,隔离性可以防止多个事务的并发执行时,由于它们的操作命令交叉执行而导致数据的不一致性。

持久性(durability)

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

8.事务的控制语句

在oracle数据库中,没有提供开始事务处理语句,所有的事务都是隐式开始的,也就是说在oracle中,用户不可以显示使用命令来开始一个事务.oracle任务第一条修改数据库的语句,或者一些要求事务处理的场合都是事务的隐式开始。但是当用户想要终止一个事务处理时,必须显示使用commit和rollback语句结束。

根据事务ACID属性,oracle提供了如下的事务控制语句：

Set transaction 设置事物属性

Set constrains 设置事物的约束模式

约束模式是指：在事务中修改数据时,数据库中的约束立即应用于数据,还是将约束推迟到当前事务结束后应用。

Savepoint 在事务中建立一个存储的点.当事务处理发生异常而回滚事务时,可指定事务回滚到某存储点.然后从该存储点重新执行。

Release savepoint 删除存储点

Rollback 回滚事务 取消对数据库所作的任何操作

Commit 提交事务 对数据库的操作做持久的保存。

9.设置事物的属性

set transaction语句可用来设置事物的各种属性。该语句必须放在事务处理的第一个语句.

也就是说，必须在任何insert、update 、delete语句以及其他的事务处理。Set transaction的语句可以让用户对事务的以下属性进行设置:指定事务隔离层、规定回滚事务所使用的存储空间、命名事务

备注：在使用set transaction语句设置属性时,对于规定回滚事务所使用的存储空间的设置很少使用.对于命名事务也非常简单,只有在分布式事务处理中才会体现出命名事务的用途.

注意：set transaction只对当前事务有效,事务终止,事务当前的设置将会失效。

10.数据异常

事务的隔离性定义了一个事务与其它事务的隔离程度.为了更好的理解隔离层,首先讨论一下并发事务对同一个数据库进行访问可能发生的情况.在并发事务中总体来说会发生如下3种情况

错读 |脏读

非重复读取|不可重复读

假读|幻读

错读|脏读：当一个事务修改数据时,另一事务读取了该数据,但是第一事务由于某种原因取消对数据修改,使数据返回了原状态,这是第二个事务读取的数据与数据库中数据不一致.这就叫错读。

非重复读取：是指一个事务读取数据库中的数据后,另一个事务则更新了数据,当第一个事务再次读取其中的数据时,就会发现数据已经发生了改变,这就是非重复读取。非重复读取所导致的结果就是一个事务前后两次读取的数据不相同。

假读：如果一个事务基于某个条件读取数据后,另一个事务则更新了同一个表中的数据,这时第一个事务再次读取数据时,根据搜索的条件返回了不同的行,这就是假读。

事务中遇到的这些异常与事务的隔离性设置有关,事务的隔离性设置越多,异常就出现的越少,但并发效果就越低,事务的隔离性设置越少,异常出现的越多,并发效果越高。

选择隔离层

针对3中读取的数据时产生的不一致现象,在ANSI SQL标准92中定义了4个事务的隔离级别.如下图所示：

Oracle支持上述四种隔离层中的两种:read committed 和serializable。除此之外oralce中还定义read only 和 read write隔离层。

Read committed 这是oracle默认的隔离层。

Serializable：设置事物的隔离层位它时,事务与事务之间完全隔开,事务以串行的方式执行,这并不是说一个事务必须结束才能启动另外一个事务,而是说这些事务的执行的结果于一次执行的事务的结果一致。

Read only和 read write 当使用read only时,事务中不能有任何修改数据库中数据的操作语句，这包括 insert、update、delete、create语句。Read only是serializable的一个子集，区别是read only 只读,而serialzable可以执行DML操作。Read write它是默认设置,该选项表示在事务中可以有访问语句、修改语句.但不经常使用.

建立set transaction的语句

列举如下：

Set transaction read only

Set transaction read write

Set transaction isolation level read committed

Set transaction isolation level serializable

注意：这些语句是互斥的.即不能够同时设置两个或者两个以上的选项。

结束事务：

什么情况下代表事务的结束呢?

Commit 提交事务

rollback 回滚事务

11.只读的案例

SQL> set transaction read only;

 

Transaction set

 

SQL> select * from users where username='mj';

 

USERNAME PASSWORD NAME ADDRESS ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj redarmy 陈红军 

 

SQL> update users set password='123' where username='mj';

 

update users set password='123' where username='mj'

 

ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

 

//脏读的案例

SQL> set transaction isolation level read committed;

 

Transaction set

 

SQL> select * from users where username='mj';

 

USERNAME PASSWORD NAME ADDRESS ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj redarmy 陈红军 

 

SQL> update users set password='123' where username='mj';

 

1 row updated

 

SQL> select * from users where username='mj';

 

USERNAME PASSWORD NAME ADDRESS ZIP

-------------------- -------------------- -------------------- -------------------- -------

mj 123 陈红军 

 

SQL> rollback;