Data manipulation

1 Data Manipulation Language
  1) A DML statement is executed when you:
     add new rows to a table
     modify existing row in a table
     remove existing rows from a table
  2) A transaction consist a collection dml statements form a logic unit of work
2 Using Explicit Default Values
 1) default with insert
 insert into departments
 values(200,'ddd',default)
 2) default with update
 update departments
 set manager_id=default where department_id=10
3 The Merge Statement
 1)Provide the ability to conditionaly update or insert data into database
 2)Perform a update if the row exists and an insert if it is a new row
   a Avoid update separatly
   b increase performance and ease of use
   c is useful in data warehousing application
   example
    merge into copy_emp c
    using employees e
    on (c.employee_id=e.employee_id)
    when mathched then
    update set
      c.first_name=e.first_name
      c.last_name=e.last_name
      ..............
      c.department_id=e.department_id
    when not matched then
    insert values(e.employeeid,e.first_name,......e.department_id);
4 Database Transactions
 1)Begin when the first dml statement is executed
 2)end with one of the following events
   a a commit or rollback statement is issued;
   b a ddl or dcl statement execute (commit automatically)
   c the user exist isqllplus
   d the system crashes
 3) advantage of commit and rollback statemnt
   With commit and rollback statement ,you can
    a ensure data consistence
    b Preview data change before making change permant
    c group logic relate operatons
5 State of The Data Before commit or rollback
 1) the previous state of the data can be recovered
 2) The current user can review the result of the dml operation by using the select statment
 3) other user can not view the result of the dml
 4) the affected was locked ,other user cannot change the data within the affecteed row
6 Read Consistency
 1) Read consistency guarantees a consistent view of the data at all times
 2) Changes made by one user do not confilict with changes made by another user
 3) Read consistency ensures that on the same data
    a Readers do not wait for writers
    b Writers do not wait for readers
7Locking
  1) Prevent destructive interaction between concurrent transactions
  2) Reqire no user action
  3) Automatically use the lowest level of restrictiveness
  4) Are held for the duration of the transaction
  5) Are of two types:explicit locking an implicit locking
8 Implicit Locking
  1)Two lock modes
    a Exclusive :Locks out other users
    b Share: Allows other users to accesss
  2)High level of data concurrency
    a DML:Table share,row exclusive
    b Queries: No locks required
    c DDL:Protects object definitions
  3)Locks held until commit or rollback

posted on 2006-09-25 16:25 康文 阅读(233) 评论(0)  编辑  收藏 所属分类: 数据库


只有注册用户登录后才能发表评论。


网站导航:
 
<2006年9月>
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章档案

搜索

最新评论

阅读排行榜

评论排行榜