第八章修改表内容.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语句用于

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

4、stomers where customer_id=2; 在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:averag

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

6、图插入一个与主键列值重复的行，数据库就会返回ORA-00001错误 2.外键约束 所谓外键关系就是在一个表中引用了其他表中的列，如果试图插入一个具有不存在的id的行，数据库就会返回ORA-02291错误，这个错误说明数据库无法找到一个匹配的父键值,四、数据库的完整性,如果试图从父表中删除已经有依赖子行的一行，数据库就会返回ORA-02292错误，这个错误是说找到了子记录,五、使用默认值,数据库允许为列定义默认值，如 Create table order_status( Order_status_id integer Constraint default_example_pk primary k

7、ey, Status varchar2(20) default order placed not 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语句可以用来将一个表中的行合并到另

8、一个表中,如： megre into products p using product_changes pc 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.pric

9、e) values(pc.product_id,pc.product_type_id,pc.name,pc.description,pc.price);,六、使用merge合并行,关于megre语句要注意以下几点： Megre into子句指明了合并操作的目标表 Using on子句指定了一个表连接 When matched then子句指定了当一行满足using on子句的条件时要执行的操作 When not matched子句指定了当一行不满足using on子句的条件时要执行的操作,七、数据库事务,数据库事务就是一组sql语句，这组sql语句是一个逻辑工作单元，我们可以认为事务就是一组不

10、可分割的sql语句，在执行时，应该作为一个整体永久性的修改数据库的内容，或者作为一个整体取消对数据库的修改。 1.事务的提交和回滚 要永久性的记录事务中sql语句的结果，需要执行commit语句，从而提交事务。要取消sql语句的结果，需要执行rollback语句，从而回滚事务，rollback语句可以将行重新设置为原始状态，在执行回滚操作之前对数据库进行的任何修改操作都会被取消，条件是此前没有断开与数据库的连接 在sql中，如果执行commit语句，将会永久性的保存对数据库所进行的修改,七、数据库事务,如： Insert into customer values(6,fred,green,01

11、-JAN-1970,800-555-1215); Commit; Commit语句将这种变化永久性的保存到数据库中，rollback语句会取消这个操作,七、数据库事务,事务的开始与结束 事务是用来分割数据库活动的逻辑工作单元，事务既有起点，也有终点，当发生下列事件之一时，事务就开始了： 1.连接到数据库上，并执行第一条DML语句 2.前一个事务结束后，又输入了另外一条DML语句 当下列事件之一发生时，事务就结束： 1.执行commit或rollback语句 2.执行一条DDL语句，如create table，这种情况下会自动执行commit语句,七、数据库事务,3.执行一条DCL语句，如gra

12、nt语句，这种情况会自动执行commit语句 4.断开与数据库的连接，如果sql*plus被意外终止，就会自动执行rollback语句 5.执行了一条DML语句，该语句却失败了；在这种情况中，会为这个无效的DML语句执行rollback语句 事务完成之后，都要执行commit或rollback语句,8.7.3 保存点,在事务的任何地方都可以设置一个保存点，这样可以将修改回滚到保存点处。如果有一个很大的事务，这将 非常有用，因为这样如果在保存点后进行了误操作，并不需要将整个事务一直回滚到最开头。 如： Savepoint save1;,8.7.4 事务的ACID特性,事务定义为逻辑工作单元，即一

13、组相关的sql语句，他们要么作为一个单位被提交，要么作为一个单位被回滚 数据库理论对事务采用了更严格的定义，说明事务有3个基本的特性，称为ACID特性 1.原子性(atomicity)事务必须成组的提交或回滚，因此事务是原子的，这就是说一个事务中包含的所有的sql语句都是一个不可分割的单元,8.7.4 事务的ACID特性,一致性(consistency)事务必须确保数据库的状态保持一致，这就是说事务开始时，数据库的状态是一致的；咋事务结束时，数据库的状态也必须是一致的 隔离性(isolation)多个事务可以独立运行，而不会彼此产生影响 持久性(durability)一旦事务被提交之后，数据库

14、的变化就会被永远保留下来，即使运行数据库软件的机器后来崩溃也是如此 Oracle数据库软件确保每个事务都有ACID特性，并且具有非常丰富的恢复特性，可以在机器由于各种原因崩溃时恢复数据库,8.7.5 并发事务,Oracle数据库支持多个用户同时与数据库进行交互，每个用户都可以同时运行自己的事务，这种事务被称为并发事务 如果用户同时运行多个事务，而这些事务都对同一个表产生影响，那么这些事务的影响都是独立的，直到执行一条commit语句时才会彼此产生影响,8.7.5 并发事务,比如使用了两个事务T1和T2，这两个事务都会访问customers表，以下的时间序列展示了事务的隔离性： T1和T2分别执

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

16、了 T2也试图执行一条update语句修改#1的记录，但是由于该行早已被T1加锁了，因此T2现在就不能获得该行的锁，T2的update语句必须一直等，直到T1结束并释放该行上的锁为止 T1执行commit语句并结束，从而释放该行上的锁 T2获得该行上的锁，并执行update语句，T2获得该行上的锁后一直持有，知道T2结束为止,8.7.6 事务锁,加锁机制：读程序不会阻塞读程序；写程序不会阻塞读程序；只有在试图对相同的行进行修改时，写程序才会阻塞写程序,8.7.7 事务隔离级别,事务隔离性级别(transaction isolation level)是一个事务对数据库的修改与并行的另外一个事务的

17、隔离程度。 当两个并发事务正在访问相同的行，可能存在三种问题： 1、幻象读取 事务T1读取一条指定的where子句所返回的结果集，然后事务T2新插入一行记录，这行记录恰好可以满足T1所使用的查询中的where子句的条件，然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行，这个新行就称为“幻象”，因此对于T1来说这一行就像是突然出现的一样 2.不可重复读取 事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录的内容，然后T1又再次读取这一行记录，发现他与刚才独缺的结果不同了，这种现象称为“不可重复”读，因为T1原来读取的那一行记录已经发生了变化,8.7

18、.7 事务隔离级别,脏读 事务T1更新了一行记录的内容，但是并没有提交所作的修改。事务T2读取更新后的行，然后T1执行回滚操作，取消了刚才所做的修改，现在T2所读取的行就无效了，因为在T2读取这行记录时，T1所做的修改并没有提交,8.7.7 事务隔离级别,数据库实现了不同级别的事务隔离性，以防止并发事务会相互影响，sql隔离事务级别从低到高依次为： READ UNCOMMITTED 幻象读、不可重复读和脏读都允许 READ COMMITTED 允许幻象读和不可重复读，但是不允许脏读 REPEATABLE READ允许幻象读，但是不允许不可重复读和脏读 SERIALIZABLE 幻影读、不可重复

19、读和脏读都不允许,8.7.7 事务隔离级别,Oracle数据库支持READ COMMITTED和SERIAIZABLE两种事务隔离性级别，不支持READ UNCOMMITTED和REPEATABLE READ这两种隔离性级别 Sql标准所定义的默认事务隔离性级别是SERIALIZABLE,但是oracle数据库默认使用的事务隔离性级别却是READ COMMITTED,这几乎对于所有的应用程序来说都可以接受 事务隔离性级别语句设置如 Set transaction isolation level serializable,8.8 查询闪回,如果错误的提交修改操作，并向查看所修改的行的原来的值，可

20、以使用查询闪回,如果需要，就可以使用查询闪回的结果将这些行手工的修改回原来的值 闪回操作使用PL/SQL中的DBMS_FLASHBACK包，要想执行这种操作必须具备EXECUTE权限，授权语句： Grant execute on sys.dbms_flashback to store 时间查询闪回 DBMS_FLASHBACK.ENABLE_AT_TIME()过程，该过程可以将数据库状态闪回到一个特定的时间值，这个过程接受一个时间值参数，如 EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE-10/1440); 如果要禁用闪回操作，可以执行DBMS_FL

21、ASHBACK.DISABLE(),八、查询闪回,系统变更号查询闪回 根据系统便更号(SCN)进行闪回操作比根据时间进行闪回操作更精准，因为数据库就是使用SCN来跟踪数据库的变化的，要获得当前的SCN，可以执行DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() 如： Variable current_scn NUMBER Execute: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();,