DML & transaction ctrl

Select … from

omitted

Insert into

• One at a time
• Syntax:
  • INSERT INTO tablename(col1, col2, ...) VALUES (val1, val2, ...)
  • If (col1, col2, …) is not provided, must provide value for each column
    • data appears exactly like order of column
  • virtual columns must be ignored
  • O’Hara -> (…, ‘O”Hara’, ….)
• Activating DEFAULT option:
  • ignore the column where default value is to be entered
  • insert the column value as DEFAULT
    • if the default value is sysdate, also can use SYSDATE
• Inserting null value:
  • ignore the column
  • insert w/ '' -> VALUES(..., ..., '')
  • insert w/ NULL -> VALUES(..., ..., NULL)
  • MODIFY colname DEFAULT ON NULL value clause:
    • If NULL is inserted, it will be replaced with the value
    • Cannot do this if there is already NULL value
• Inserting from an exsiting table:
  
  INSERT INTO tablename(col1, col2, col3, ...) SELECT col1, col2, col3, ... FROM table2;
  • No need to use 'VALUES'
  • If everything matches (same col number, same data order), column names after INSERT INTO can be omitted

Update … set

• Syntax:
  • UPDATE tablename SET (col1=val1, col2=val2, ...) WHERE condition
  • If WHERE condition is omitted, the whole table is updated
• Interactive update:
  • UPDATE tablename SET col1='&valname1', ... WHERE col2='&valname2', ...
  • Good when needing to update rows over and over again
• v. ALTER TABLE:
  • ALTER is on metadata of table
  • UPDATE is on data in table

Delete from

• Syntax:
  • DELETE FROM tablename WHERE condition
  • If WHERE condition is omitted, the whole table is cleared
• DELETE v. TRUNCATE v. DROP
  • TRUNCATE is DDL, preserves the table itself, storage is released thus cannot undo
  • DROP is DDL, removes the table itself, cannot undo
  • DELETE is DML, preserves the table itself, storage is not released thus can undo

Transaction ctrl

• DDL definition:
  • CREATE/ALTER/TRUNCATE/DROP TABLE
  • Cannot undo
  • Automatically committed
• A bunch of DMLs executed together is called a transaction

Commit

• Confirm all actions
• Cannot undo once committed
• Explicitly occur when executing COMMIT;
• Implicitly occur when:
  • Any DDL is executed, in this case, all previous DMLs are committed
  • User exit the system

Rollback & Savepoint

• Place SAVEPOINT name at any place in the script
• Use ROLLBACK TO name to roll back to the savepoint with that name
  • If no name is given, roll back to the last commit point
• SAVEPOINTS ARE ROLLED BACK AS WELL