Saturday, April 08, 2006

Oracle - DML & Locks

INSERT INTO tablename [(column list ,....,....)]
VALUES
(,....,....)

Note : If column list is omitted then the value list must contain a value for each column in the table in the same order.

INSERT INTO tablename (column list....)
SELECT columns, ..., ...
FROM table2
WHERE condition;
Note 1: Using Sub-query in place of VALUE clause.
Note 2: Match the table1 column list to the column list of table2.

INSERT INTO tablename
SELECT * FROM table1;
Note 1: copy all rows into an existing table.

INSERT INTO tablename
(SELECT columns,...,...
FROM table2
WHERE clause [WITH CHECK OPTION]
)

VALUES (values....);

Note 1: Using Sub-query in place of tablename.
Note 2: Any rules on the columns of the base table must be followed.
Note 3: WITH CHECK OPTION limits the scope of the SQL command to the data-set fitting the Subquery. If a subquery is used in place of tablename in INSERT/UPDATE/DELETE statements specifying WITH CHECK OPTION, no changes that would produce rows not included in the sub-query will be permitted.

UPDATE tablename
SET column= value
[ , ..=...]

[WHERE condition];

Note 1: value : can be a value or a sub-query for the column.
Note 2: Specific rows are updated with WHERE clause. Without the WHERE condition all the rows are modified.

DELETE [FROM] tablename
[WHERE condition];
Note 1: Specific rows are deleted with WHERE clause. Without the WHERE condition all the rows are deleted.

MERGE INTO tablename
USING (table/view/subquery)
ON (join condition)
WHEN MATCHED THEN
[ UPDATE SET
col1=value, col2=value
]
WHEN NOT MATCHED THEN
[ INSERT [(column list)]
VALUES (values...,...,...)]
;

INTO : target table being updated or inserted upon
USING : source of data (table/view/subquery)
ON : condition of merge

TRANSACTION CONTROL
COMMIT
SAVEPOINT name
ROLLBACK [TO SAVEPOINT name]

Note : DDL/DCL statements automatically commits thereby implicitly committing current transaction.

Purpose:
COMMIT : Ends current transaction by making all pending data changes permanent & releasing space in the undo segment.

ROLLBACK : Ends current transaction by discarding all pending data changes. The old values are restored back from the undo segment.

SAVEPOINT : Since these are logical, there is no way to list the created savepoints.

SET AUTOCOMMIT [ON|OFF]: If SET then each DML statement is committed as soon as it is executed.

Status before commit:
Status after Commit:
Statement level rollback:
Read Consistency:
LOCKS

Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google