Oracle数据库入门之DML与事务控制

insert语句 语法：insert into table [(column1, column2...)] values (value1, value2...); 说明：insert语句每次只能向表中插入一条记录。缺省字段名列表时，应为新插入记录中的每个字段显式的设定新值 也可在insert语句中指定赋值字段列表，只为部分字段

　　insert语句

　　语法：insert into table [(column1, column2...)] values (value1, value2...);

　　说明：insert语句每次只能向表中插入一条记录。缺省字段名列表时，应为新插入记录中的每个字段显式的设定新值

　　也可在insert语句中指定赋值字段列表，只为部分字段显式设定新值，其余字段将被缺省赋值为null

　　举例：insert into dept values(88,'研发部','北京');--此句便是缺省字段名列表的情况

　　insert into dept values(66,'美工部');--这句将执行出错，提示为没有足够的值。即必须为所有字段设定值

　　insert into dept(deptno,dname) values(99,'财务部');--也可以单独为deptno和dname字段赋值

　　select table_name from user_tables;--用到了数据字典表。目的是查询当前用户方案下所有的表的名字

　　select user from dual;--查询当前连接到数据库的用户名

　　补充：可以在insert语句中使用子查询，实现表间数据拷贝。在现实开发中很少用的到，有点类似Java中的数组拷贝

　　比如insert into dept1(id, name) select deptno, dname from dept;

　　此时不必再给出values子句。子查询中的值列表应与insert子句中的字段列表相匹配

　　实现数据复制的时候，也可以在应用程序层面采用这种方式进行数据备份，但一般来说这种备份是不可靠的

　　应该在数据库的层面做备份，也可以由DBA对整个数据库做自动的数据备份处理，包括出现问题的时候的恢复

　　update语句

　　语法：update table set column1=value1 [, column2=value2, ...] [where condition];

　　说明：update语句用于更新表中的数据。update语句每次可更新多条记录

　　可使用where子句限定要更新的记录，如果缺省where子句，则更新表中的所有记录

　　举例：update emp set sal=sal+88;--将所有员工的工资都涨88元钱

　　update student2 set phOne='010-51288984' where;--将张三的电话改为010-51288984

　　delete语句

　　语法：delete [from] table [where condition];

　　说明：delete语句用于从表中删除数据。delete语句每次可删除多条记录

　　可使用where子句限定要删除的记录，如果缺省where子句，则删除表中的所有记录

　　delete语句并不是删除整个表，只是删除表中的记录，表仍然存在，还可用来存放数据

　　举例：delete emp;--等价于delete from emp;

　　delete emp where empno=7778;--删除empno字段的值为7778的所有记录

　　merge语句

　　概述：merge语句用于进行数据合并，它是根据条件在表中执行数据的修改或插入操作

　　如果要插入的记录在目标表中已经存在，则执行更新操作，否则执行插入操作

　　实际开发的过程中使用它的机会并不是很多

　　语法：merge into table [alias]

　　using(table|view|sub_query) [alias]

　　on(join_condition)

　　when matched then

　　update set col1=col1_val, col2=col2_val

　　when not matched then

　　insert (column_list) values(column_values);

　　举例：create table test1(eid number(10), name varchar2(20), birth date, salary number(8,2));

　　insert into test1 values (1001, 'Stone', '21-1月-10', 8888);

　　insert into test1 values (1002, 'Smith', '04-1月-09', 6666);

　　select * from test1;

　　create table test2(eid number(10), name varchar2(20), birth date, salary number(8,2));

　　select * from test2;

　　merge into test2

　　using test1

　　on(test1.eid=test2.eid)

　　when matched then

　　update set name=test1.name,birth=test1.birth,salary=test1.salary

　　when not matched then

　　insert (eid, name, birth) values(test1.eid, test1.name, test1.birth);

　　select * from test2;

　　说明：set设定的是目标表中的字段。即将源表中的字段值赋给目标表中的字段

　　insert设定的也是将字段值插入到目标表中。如果要插入全部的字段的值，那么column_list是可以省略的

　　注意：由于set和insert默认都是对目标表进行操作，所以，它们后面的字段不可以加目标表前缀

　　即本例中若出现set test2.name=test1.name或insert (test2.eid)，执行则出错，提示信息为标识符无效

　　事务控制

　　概述：也叫做事务处理。是通过将一组相关操作组合为一个要么全部成功，要么全部失败的逻辑工作单元

　　以简化错误恢复，提高应用程序的可靠性。这里指的是在数据库层面所进行的一种数据库操作的集成或者说单元化控制

　　也可以在应用程序层面进行类似的处理。比如说逐个向数据库发送多条DML指令，通过先后更新两个表以对应转账操作

　　如果后面的一个操作出错了，那么就取消前一个指令，或者进行相反的对冲的操作

　　但应用程序层面的这种原子的组合，实际上是不可靠的。在数据库的层面或者在底层来进行这种集成，会更有效有些

　　事务：组成单个逻辑工作单元的一系列操作被称为事务(Transaction)。实际上事务不是仅限于在数据库领域中的一个概念

　　数据库事务通常由0到多条DML语句或1条DDL(Data Define Language)语句或1条DCL(Data Control Language)语句组成

　　所谓的单个逻辑工作单元就是能够完成一个相对独立的功能，或者说是不应该分隔开的一个操作系列

　　比如银行的转账业务，该业务至少可以分为A账户的转出和B账户的转入。也就是从A账户的余额中减掉一定的数额

　　然后再将B账户的余额增加一定的数额。整个过程等于是修改了两条记录，这两个操作就可以认为就组成了一个事务

　　它们应该是一个单个的逻辑工作单元，也就是银行的一个单笔业务

　　ACID：事务必须满足ACID属性，即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

　　原子性：即事务中所有操作要么全成功，要么全失败，它们应该作为一个整体被处理

　　一致性：事务执行完毕之后，数据必须处于一致性的状态，不能出现数据状态上的错误。比如将员工工资级别从C升到B级

　　但工资却没有从应该的4000升到6000，于是就出现了数据的不一致。也就是说这时的工资级别虽然是B级

　　但工资的数额却属于C级的范围。这就说明事务结束的时候，数据处于一种不一致性的状态，会影响后续的使用

　　隔离性：这是相对其它事务而言的。有时可能出现多个事务并发执行的情况，比如多个用户同时对同一个数据库表进行操作

　　碰巧操纵的又是该表的同一个数据。此时应该有这样的一个保证，即当前事务在执行过程中所做的数据状态的改变

　　是不受其它事务影响的。其它事务所读取或查看的数据，仍是当前事务执行之前的状态

　　直到当前事务结束，其它事务看到的才会是当前事务结束之后的数据状态

　　所有事务中间的状态对其它事务而言，是被隔离开的，不受其它事务影响

　　持久性：当事务执行完毕并提交操作之后，数据将永久生效，永久保存在数据库中，将来不可撤销，不可恢复

　　开始：事务开始于第一条可执行语句

　　结束：当遇到commit或rollback语句、遇到DDL或DCL语句、用户会话结束、系统崩溃等情况时会结束事务

　　提交：在执行一个DDL语句、执行一个DCL语句、正常结束会话等情况下会自动提交事务

　　提交就是让事务永久生效，不可撤销。回滚就是撤销先前操作，但回滚到事务开始之前的状态时，数据仍然是一致的

　　回滚：当会话异常终止或系统崩溃时，事务会被自动回滚。事务的显式的提交和回滚操作是为了更好的保证数据的一致性

　　状态：事务回滚后：数据的修改被撤销。数据恢复到修改前的状态。记录锁被释放

　　事务提交后：数据的修改永久生效，不可撤销。数据以前的状态永久性丢失，无法恢复。保存点(savepoints)被清除

　　所有用户(会话)都将看到操作后的结果。记录锁被释放，其它用户此时才可以对这些数据进行修改操作

　　提交或回滚前：事务中DML操作结果只对当前用户(会话)可见，其它用户(会话)看不到当前事务中数据的改变，直到事务结束

　　事务中DML语句所涉及到的行会被锁定，其它用户(会话)不能对其进行修改操作，但可以查询

　　事务中数据状态的改变是可以恢复的

　　SqlPlus的自动提交

　　概述：SqlPlus中执行SQL语句时可以设置是否自动提交，缺省为非自动提交。这里的提交指的不是事务，而是SqlPlus的每条语句

　　将来在commit或正常关闭窗口即关闭本地到数据库连接的时候，，Sql Plus中的语句也会被自动的提交

　　设置：show autocommit;--查看设置。其中autocommit OFF表示当前设置为非自动提交，而autocommit IMMEDIATE代表自动提交

　　set autocommit on;--更改为自动提交。set autocommit off;--更改为非自动提交

　　举例：insert into dept values(88,'Stone','Beijing');

　　select * from dept;

　　说明：自动提交状态为OFF的情况下，这是一个未提交的事务。插入新记录的操作并没有永久生效，只是当前用户(会话)可见

　　其它会话中看不见这个未提交事务，即使当前用户再一次连接到数据库，即建立了一个新的会话，也查询不到88号记录

　　比如不关闭当前SQLPlus，然后再打开一个SQLPlus窗口，并使用当前登录的scott用户再一次登录新打开的SQLPlus窗口

　　然后执行查询，在结果中根本没有查询到前一次会话中没有提交的操作指令，即查询结果中没有88号记录

　　这时可以在原SQLPlus窗口中显式的提交一下，即执行commit指令。提交完成后，插入88号记录的操作便永久生效了

　　然后回到后打开的SQLPlus窗口中执行查询，查询结果中很自然的就查到了88号记录

　　也就是说已经提交的事务影响所有其它的事务和会话。而对于未提交的事务，其它用户(会话)是看不见的

　　保存点(Savepoint)

　　概述：通过保存点在当前的事务中创建标记，将来可回退到指定的标记(保存点)处，实现事务的部分回滚

　　举例：insert into dept values(55,'Adv','Beijing');

　　insert into dept values(56,'Sec','Shanghai');

　　savepoint p1;

　　insert into dept values(57,'Acc','Dalian');

　　select * from dept;

　　rollback to p1;

　　select * from dept;

　　说明：前提是当前SqlPlus的设置是非自动提交。所以这些都是当前会话中未提交的事务

　　代码执行时p1之后的数据状态的改变被撤销，p1之前的操作仍然存在

　　若执行rollback;则回滚到整个事务的最初