第八章修改表内容.ppt

《第八章修改表内容.ppt》由会员分享，可在线阅读，更多相关《第八章修改表内容.ppt（32页珍藏版）》请在三一办公上搜索。

1、Oracle 10g,第八章 修改表内容,一、使用insert语句添加行二、使用update语句修改行三、使用delete语句删除行四、数据库完整性五、使用默认值六、使用merge合并行七、数据库事务八、查询闪回,一、使用insert语句添加行,Insert语句用于向表中添加行；在insert语句中，可以指定以下信息：要插入的行所在的表要为其指定的列的一个列表要为这些列指定的值列表在添加行时，通常需要为主键和其他被定义为not null的列指定值。如果不想，可以不为定义为null的列指定值；默认情况下，这些列都会被设置为空值1.忽略列的列表当所有的列都提供值时可以忽略列的列表,2.为列指定空值

2、Null关键字可以用来为一列指定一个值，如Insert into custmoer values(8,sophie,white,null,null);3.在列值中使用单引号和双引号在列值中可以使用单引号和双引号，如Insert into custmoer values(9,kyle,0,malley,null,null);4.从一个表向另一个表复制行在insert语句中，可以不使用列值，而是使用查询从一个表向另外一个表复制行；此时要求源表和目标表的列数和列的类型必须匹配,一、使用insert语句添加行,二、使用update语句修改行,Update语句用于修改表中行的内容。在update语句中，

3、通常要指定以下信息：要修改的行所在的表指定要修改哪些行的where子句要修改的列的一个列表，以及其新增，使用set子句指定Update语句可以同时修改一行或多行记录，如果指定了多行，就对所有这些行进行相同的修改操作Update customers set last_name=orange where customer_id=2;,二、使用update语句修改行,Sql*plus会确认已经修改了一行的内容，如果忽略了where子句，就会修改所有的行。在update语句中使用set语句来指定要修改的列以及该列的新值Select*from customers where customer_id=2;

4、在update语句中，可以同时对多行或多列进行修改，如：Update products set price=price*1.20,name=lower(name)where price=20;,二、使用update语句修改行,Returning子句在oracle 10g中，可以使用returning子句返回使用聚合函数计算的结果Variable average_product_price;Update products set price=price*0.75 returning avg(price)into:average_product_price;Print average_produc

5、t_price;,三、使用delete语句删除行,Delete语句用于从表中删除行，此时通常应该使用where子句来限定想要删除哪些行，如果不指定where子句，就会删除所有的行如果已经执行了insert,update和delete语句，使用rollback命令可以回滚所作的修改,四、数据库的完整性,在执行DML语句(例如insert、update或delete)时，数据库会确保表总的行都可以维护自身的完整性，这就是说对表中所做的任何修改都必须总能保持表的主、外键关系1.主键约束主键中的每一个值必须是唯一的，如果试图插入一个与主键列值重复的行，数据库就会返回ORA-00001错误2.外键约束所

6、谓外键关系就是在一个表中引用了其他表中的列，如果试图插入一个具有不存在的id的行，数据库就会返回ORA-02291错误，这个错误说明数据库无法找到一个匹配的父键值,四、数据库的完整性,如果试图从父表中删除已经有依赖子行的一行，数据库就会返回ORA-02292错误，这个错误是说找到了子记录,五、使用默认值,数据库允许为列定义默认值，如Create table order_status(Order_status_id integerConstraint default_example_pk primary key,Status varchar2(20)default order placed no

7、t null,Last_modified date default sysdate);如：Insert into order_status(order_status_id)values(1);,五、使用默认值,在update语句中，可以使用default关键字修改列的值，并将其重新设置为默认值。Update order_status set status=default where order_status_id=2;,六、使用merge合并行,Merge语句可以用来将一个表中的行合并到另一个表中,如：megre into products p using product_changes pc

8、 on(p.product_id=pc.productid)when matched then update set p.product_type_id=pc.product_type_id,p.name=pc.name,p.description=pc.description,p.price=pc.price when not matched then insert(p.prodduct_id,p.product_type_id,p.name,p.description,p.price)values(pc.product_id,pc.product_type_id,pc.name,pc.de

9、scription,pc.price);,六、使用merge合并行,关于megre语句要注意以下几点：Megre into子句指明了合并操作的目标表Using on子句指定了一个表连接When matched then子句指定了当一行满足using on子句的条件时要执行的操作When not matched子句指定了当一行不满足using on子句的条件时要执行的操作,七、数据库事务,数据库事务就是一组sql语句，这组sql语句是一个逻辑工作单元，我们可以认为事务就是一组不可分割的sql语句，在执行时，应该作为一个整体永久性的修改数据库的内容，或者作为一个整体取消对数据库的修改。1.事务的提

10、交和回滚要永久性的记录事务中sql语句的结果，需要执行commit语句，从而提交事务。要取消sql语句的结果，需要执行rollback语句，从而回滚事务，rollback语句可以将行重新设置为原始状态，在执行回滚操作之前对数据库进行的任何修改操作都会被取消，条件是此前没有断开与数据库的连接在sql中，如果执行commit语句，将会永久性的保存对数据库所进行的修改,七、数据库事务,如：Insert into customer values(6,fred,green,01-JAN-1970,800-555-1215);Commit;Commit语句将这种变化永久性的保存到数据库中，rollback

11、语句会取消这个操作,七、数据库事务,事务的开始与结束事务是用来分割数据库活动的逻辑工作单元，事务既有起点，也有终点，当发生下列事件之一时，事务就开始了：1.连接到数据库上，并执行第一条DML语句2.前一个事务结束后，又输入了另外一条DML语句当下列事件之一发生时，事务就结束：1.执行commit或rollback语句2.执行一条DDL语句，如create table，这种情况下会自动执行commit语句,七、数据库事务,3.执行一条DCL语句，如grant语句，这种情况会自动执行commit语句4.断开与数据库的连接，如果sql*plus被意外终止，就会自动执行rollback语句5.执行了一

12、条DML语句，该语句却失败了；在这种情况中，会为这个无效的DML语句执行rollback语句事务完成之后，都要执行commit或rollback语句,8.7.3 保存点,在事务的任何地方都可以设置一个保存点，这样可以将修改回滚到保存点处。如果有一个很大的事务，这将 非常有用，因为这样如果在保存点后进行了误操作，并不需要将整个事务一直回滚到最开头。如：Savepoint save1;,8.7.4 事务的ACID特性,事务定义为逻辑工作单元，即一组相关的sql语句，他们要么作为一个单位被提交，要么作为一个单位被回滚数据库理论对事务采用了更严格的定义，说明事务有3个基本的特性，称为ACID特性1.原

13、子性(atomicity)事务必须成组的提交或回滚，因此事务是原子的，这就是说一个事务中包含的所有的sql语句都是一个不可分割的单元,8.7.4 事务的ACID特性,一致性(consistency)事务必须确保数据库的状态保持一致，这就是说事务开始时，数据库的状态是一致的；咋事务结束时，数据库的状态也必须是一致的隔离性(isolation)多个事务可以独立运行，而不会彼此产生影响持久性(durability)一旦事务被提交之后，数据库的变化就会被永远保留下来，即使运行数据库软件的机器后来崩溃也是如此Oracle数据库软件确保每个事务都有ACID特性，并且具有非常丰富的恢复特性，可以在机器由于各

14、种原因崩溃时恢复数据库,8.7.5 并发事务,Oracle数据库支持多个用户同时与数据库进行交互，每个用户都可以同时运行自己的事务，这种事务被称为并发事务如果用户同时运行多个事务，而这些事务都对同一个表产生影响，那么这些事务的影响都是独立的，直到执行一条commit语句时才会彼此产生影响,8.7.5 并发事务,比如使用了两个事务T1和T2，这两个事务都会访问customers表，以下的时间序列展示了事务的隔离性：T1和T2分别执行select语句，对customers表进行检索T1执行了insert语句，向customers表插入数据，而T1此时并不执行commit语句T2在执行一条selec

15、t语句，检索结果与步骤1中相同，T2并没有“看到”T1在步骤2中插入的新行T1最后执行commit语句，永久性的保存在步骤2中插入的新行T2执行一条select语句，最终看到了T1所插入的新行,8.7.6 事务锁,事务锁要支持并发事务，oracle数据库必须确保表中的数据一直有效，这可以通过锁来实现，比如：T1执行一条update语句修改顾客#1的记录，但是T1并没有执行commit语句，此时就成为T1对该行“加锁”了T2也试图执行一条update语句修改#1的记录，但是由于该行早已被T1加锁了，因此T2现在就不能获得该行的锁，T2的update语句必须一直等，直到T1结束并释放该行上的锁为止

16、T1执行commit语句并结束，从而释放该行上的锁T2获得该行上的锁，并执行update语句，T2获得该行上的锁后一直持有，知道T2结束为止,8.7.6 事务锁,加锁机制：读程序不会阻塞读程序；写程序不会阻塞读程序；只有在试图对相同的行进行修改时，写程序才会阻塞写程序,8.7.7 事务隔离级别,事务隔离性级别(transaction isolation level)是一个事务对数据库的修改与并行的另外一个事务的隔离程度。当两个并发事务正在访问相同的行，可能存在三种问题：1、幻象读取 事务T1读取一条指定的where子句所返回的结果集，然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的

17、查询中的where子句的条件，然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行，这个新行就称为“幻象”，因此对于T1来说这一行就像是突然出现的一样2.不可重复读取 事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录的内容，然后T1又再次读取这一行记录，发现他与刚才独缺的结果不同了，这种现象称为“不可重复”读，因为T1原来读取的那一行记录已经发生了变化,8.7.7 事务隔离级别,脏读 事务T1更新了一行记录的内容，但是并没有提交所作的修改。事务T2读取更新后的行，然后T1执行回滚操作，取消了刚才所做的修改，现在T2所读取的行就无效了，因为在T2读取

18、这行记录时，T1所做的修改并没有提交,8.7.7 事务隔离级别,数据库实现了不同级别的事务隔离性，以防止并发事务会相互影响，sql隔离事务级别从低到高依次为：READ UNCOMMITTED 幻象读、不可重复读和脏读都允许READ COMMITTED 允许幻象读和不可重复读，但是不允许脏读REPEATABLE READ允许幻象读，但是不允许不可重复读和脏读SERIALIZABLE 幻影读、不可重复读和脏读都不允许,8.7.7 事务隔离级别,Oracle数据库支持READ COMMITTED和SERIAIZABLE两种事务隔离性级别，不支持READ UNCOMMITTED和REPEATABLE

19、READ这两种隔离性级别Sql标准所定义的默认事务隔离性级别是SERIALIZABLE,但是oracle数据库默认使用的事务隔离性级别却是READ COMMITTED,这几乎对于所有的应用程序来说都可以接受事务隔离性级别语句设置如Set transaction isolation level serializable,8.8 查询闪回,如果错误的提交修改操作，并向查看所修改的行的原来的值，可以使用查询闪回,如果需要，就可以使用查询闪回的结果将这些行手工的修改回原来的值闪回操作使用PL/SQL中的DBMS_FLASHBACK包，要想执行这种操作必须具备EXECUTE权限，授权语句：Grant e

20、xecute on sys.dbms_flashback to store时间查询闪回DBMS_FLASHBACK.ENABLE_AT_TIME()过程，该过程可以将数据库状态闪回到一个特定的时间值，这个过程接受一个时间值参数，如EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE-10/1440);如果要禁用闪回操作，可以执行DBMS_FLASHBACK.DISABLE(),八、查询闪回,系统变更号查询闪回根据系统便更号(SCN)进行闪回操作比根据时间进行闪回操作更精准，因为数据库就是使用SCN来跟踪数据库的变化的，要获得当前的SCN，可以执行DBMS_

21、FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()如：Variable current_scn NUMBERExecute:current_scn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();PRINT current_scn,八、查询闪回,DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER()可以闪回到一个SCN的状态，这个过程接受一个SCN参数，如EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);要禁用闪回操作，可以执行DBMS_FLASHBACK.DISABLE()，如EXECUTE DBMS_FLASHBACK.DISABLE();,