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