The acronym DML represents Data Manipulation Language, namely the INSERT, UPDATE, DELETE and MERGE commands. Let's begin by taking a brief look at transactional control.

Transactional Control

Transactional control effectively allows database changes to be stored to the database in groups. A group is a set of DML changes which are saved to the database using a COMMIT command or undone using a ROLLBACK command. Additionally the SAVEPOINT command can be used to unwind a transaction to a labelled point within a group of transactions. The syntax of the COMMIT, ROLLBACK and SAVEPOINT commands are as follows.

COMMIT;

ROLLBACK;

SAVEPOINT label; 
… sql commands …
ROLLBACK TO SAVEPOINT label;

The SET TRANSACTION Command

Additionally there is a SET TRANSACTION command which can be used to set certain behavior for transactions; a transaction once again being a group of DML database change commands. The default values are indicated in red in the syntax diagram following.

SET TRANSACTION 
{
     NAME 'transaction'
   | {
           READ { WRITE | ONLY }
         | ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
         | USE ROLLBACK SEGMENT rollback segment
      } [ NAME 'transaction' ]
};

The READ COMMITTED option forces the transaction to wait for a lock to be released before completing. The SERIALIZABLE option does not wait for locks and the transaction fails immediately on encountering a lock. Using manual rollback segments, large transactions can be allocated explicitly to large rollback segments.

COMMIT versus ROLLBACK Performance

One important difference between COMMIT and ROLLBACK, other than the obvious, is that execution of a COMMIT command is much faster than that of a ROLLBACK command. The reasons are described following but it should be obvious that COMMIT should be faster because, at least hopefully, most applications will execute COMMIT commands much more often than ROLLBACK commands.

Before COMMIT or ROLLBACK is executed.
- The database is changed.
- The logs are written.
- Rollback entries are Written.
When COMMIT is executed only one step occurs since the database is already changed.
- Rollback entries are deleted.
When ROLLBACK is executed.
- Rollback is applied to the database to undo the changes.
- Rollback is recorded in the logs.
- Rollback is deleted.

DML Commands

DML is an acronym for Data Manipulation Language. DML commands manipulate or change data and include the INSERT, UPDATE, DELETE and MERGE commands. DDL commands or Data Definition Language commands are different to DML commands in that they allow changes to database metadata objects such as a table or an index.

The INSERT Command

The INSERT command syntax is shown following.

INSERT INTO [schema.]table [ ( column [, column …] ) ]
VALUES ( { expr | DEFAULT | (subquery) }
		[, { expr | DEFAULT | (subquery) } ... ] )
[ RETURNING expr [, expr ] INTO variable [, variable ]  ];

The RETURNING clause is used in PL/SQL to return values into variables, either single variables or arrays. An example of this format of the INSERT command would be as follows.

INSERT INTO subject(subject_id,parent_id,name) VALUES(100, 1, 'A new subject');

The list of columns is optional if the VALUES clause specifies columns in existing physical column order for the table as shown in the modified syntax following.

INSERT INTO [schema.]table
VALUES ( { expr | DEFAULT | (subquery) }
		[, { expr | DEFAULT | (subquery) } ... ] )
[ RETURNING expr [, expr ] INTO variable [, variable ]  ];

An example of this shortened format is as follows.

INSERT INTO subject VALUES(101, 1, 'Another new subject');

Multiple Table Inserts

The INSERT command can be used to insert rows into more than one table at the same time as shown in the following syntax diagram.

INSERT { FIRST | ALL } 
[ WHEN condition THEN ] INTO table [ VALUES (…) ]
[ WHEN condition THEN ] INTO table [ VALUES (…) ]
…
ELSE INTO table [ VALUES (…) ]
subquery;

WHEN conditions determine if an INTO table VALUES clause is executed. The ELSE condition is executed if all WHEN conditions have failed for a particular row. The ALL clause executes all WHEN conditions on every row of the table. The FIRST clause passes processing to the next row retrieved after processing a passed WHEN condition. Following is a pseudo-example of a multiple table INSERT command.

INSERT ALL
	WHEN id = (SELECT id FROM tableA WHERE name = 'X') THEN
		INTO table1
	WHEN id = (SELECT id FROM tableA WHERE name = 'Y') THEN
		INTO table2
	WHEN id = (SELECT id FROM tableA WHERE name = 'Z') THEN
		INTO table3
	SELECT * FROM tableB;

The UPDATE Command

The UPDATE command syntax is shown following. The WHERE clause applies in the same way as it does for the SELECT command. The UPDATE command can be used to update single or multiple columns in one or many rows in a table.

UPDATE [schema.]table
SET column = expr | (subquery) | DEFAULT
	| ( column [, column … ] ) = (subquery)
[ WHERE ... ]
[ RETURNING expr [, expr] INTO variable [, variable ] ) ];

The DELETE Command

DELETE command syntax is shown such that the WHERE clause has the same application as in both the UPDATE command and the SELECT command.

DELETE [ FROM ] [schema.]table
[ WHERE ... ]
[ RETURNING expr [, expr] INTO variable [, variable ] ) ];

The TRUNCATE command is similar to a DELETE command lacking a WHERE clause such that all rows are deleted. However, the TRUNCATE command is by definition a DDL and not a DML command since TRUNCATE makes permanent changes to a table in that it does not allow rollback to take place. All DDL commands execute an automatic inherent COMMIT command.

The MERGE Command

The MERGE command is used to merge two tables by inserting new rows and updating existing rows into the target table. In the syntax diagram following the following points apply.

The INTO clause is the target table.
The USING clause is the source table.
The ON clause provides a match between the two tables using a column join clause.
The WHEN MATCHED clause allows update of rows in the target table when the ON join clause finds a match.
The WHEN NOT MACTHED clause allows insertion of new rows into the target table when the ON join clause does not find a match.

MERGE INTO [schema.]table [alias]
USING { [schema.] table | view | (subquery) } [ alias ]
ON ( column = column [, column = column … ] )
WHEN MATCHED THEN 
   UPDATE SET { column = { column | expression | DEFAULT }
      [ , column = … ]}
WHEN NOT MATCHED THEN
   INSERT ( column [, column … ] )
      VALUES ( { column | expression | DEFAULT } [ , column …  ] );