Inserting Rows

• Insert is how we add data to the database
• Inserting one row:

Rounding on Insert

Returning Values from DML

• The RETURNING clause returns values from the INSERT
• Triggers can change, add values

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Inserting Multiple Rows

9i Multi-Table INSERT…

• INSERT statement can now affect multiple tables
• Syntax:

…9i Multi-Table INSERT

• Example:

Deleting Rows

• Deleting one or more rows

TRUNCATE Command

• Concepts
  • Used to delete all rows in a table
  • Faster than DELETE with no WHERE clause because it does not generate rollback data
  • The operation cannot be rolled back!
  • It does not fire any delete triggers
• Syntax:

Updating a Single Row

Updating Multiple Rows

MERGE statement...

• Oracle9i feature
• Allows one to merge the rows of two tables
• Some columns of the merged table may also be updated simultaneously
• Since updates and inserts are performed this is known as an “upsert” function
• Processing of MERGE is more efficient than writing an equivalent PL/SQL routine

...MERGE Statement

• Register all employees as customers

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Transaction Control

• Transaction: A transaction is a set of SQL DML statements handled as a single logical unit of work
•  Key concept: Logical unit of work
  • Sometimes referred to as a LUW
  • a logical unit of work is a group of statements that are indivisible
  • i.e. they either all complete or none of them complete.
• Implementation
  • COMMIT and ROLLBACK commands

COMMIT & ROLLBACK

• Committing changes
  • Use COMMIT to make changes permanent and release any locks held by your transaction
• Rolling back changes
  • Use ROLLBACK to undo uncommitted changes and release any locks held by your session

Transaction Control Who Sees What?

• You can see all DML changes that you make regardless of whether or not you have done a COMMIT
• No other userid or session can see your DML changes until you COMMIT them

Transaction Control Locking Mechanisms

• Any DML to any row will lock that row until you do a COMMIT or a ROLLBACK
• Other users can view locked rows but will only see the old version before your DML
• Other users cannot perform DML on locked rows
  • if they try, their session will “lock” until the session with the lock releases that lock

Savepoints

• Provides ability to ROLLBACK to a specific point
• Used as a way to reverse changes to a certain point without having to undo all of your changes

Commits in SQL*Plus

• SQL*Plus does not automatically issue a COMMIT after each DML command
• Uncommitted DML commands are automatically committed when:
  • SQL*Plus successfully terminates
  • SQL*Plus connects to another userid
• Oracle automatically issues a COMMIT after each DDL command

Setting AUTOCOMMIT

• Automatically COMMIT your SQL*Plus DML commands

Data Manipulation Workshop

• Using Data Manipulation

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.