What Are The DML, DDL, and TCL Commands in SQL?

There are three main types of SQL statements in Oracle (and SQL overall). Learn what they are and what the commands are in this article.

Table of Contents

The Three Main Statement Types

The three types of statements in SQL are:

Let’s take a look at each of these statement types.

DDL – Data Definition Language

DDL stands for Data Definition Language, and these statements are used to define the structure of your database and objects.

The DDL commands in Oracle SQL include:

CREATE: creates objects in the database, such as tables, views, and functions. Read more about the CREATE statement here.
ALTER: changes or alters objects in the database, such as tables and views. Note: this includes all ALTER statements except ALTER SESSION and ALTER SYSTEM. Read more about the ALTER TABLE statement here.
DROP: drops, or deletes, objects in the database. Read more about the DROP TABLE statement here.
TRUNCATE: removes all data from a table. Read more about the TRUNCATE statement here.
RENAME: changes the name of an object in the database.
COMMENT: adds comments for an object to the data dictionary.
GRANT: give privileges to a user or role in the database.
REVOKE: remove certain privileges from a user or role in the database.
ANALYZE: analyses information on a table, index, or cluster.
AUDIT: Track the occurrence of SQL statements in user sessions or on specific schema objects.
NOAUDIT: Disables auditing set up by the AUDIT command.
ASSOCIATE STATISTICS: Associate a statistics type with columns, functions, and other objects.
DISASSOCIATE STATISTICS: Remove the statistics association set up by ASSOCIATE STATISTICS.
FLASHBACK: Restore an earlier version of a table.
PURGE: Remove a table or index from the recycle bin.

DDL commands cannot be rolled back, as they include a COMMIT as part of their execution.

This is an important point to remember, especially if the statements in the transaction may be required to be rolled back.

This includes the TRUNCATE command, which is similar to the DELETE command. There are a few other differences between TRUNCATE and DELETE which I have covered in this article.

The DDL commands CREATE, ALTER, and DROP require exclusive access to the object you’re working on. However, the GRANT, REVOKE, ANALYZE, and AUDIT commands do not.

SQL Developer includes a setting for autocommit, or automatically committing statements. You can find out more about that and how to view and change it in this article.

DML – Data Manipulation Language

DML stands for Data Manipulation Language, and these statements allow you to change or manipulate data. You can store, retrieve, delete, and update data in the database using these statements.

The DML statements in Oracle SQL include:

SELECT: retrieves data from the database.
INSERT: adds new data into a table. Read more about the INSERT statement here.
UPDATE: changes existing data in a table. Read more about the UPDATE statement here.
DELETE: removes data from a table. Read more about the DELETE statement here.
MERGE: evaluates a condition and either inserts or updates data in a table. Read more about the MERGE statement here.
EXPLAIN PLAN: view the estimated execution plan of a query.
LOCK TABLE: adds a lock to a table to allow for dedicated access.
CALL: runs a PL/SQL function or procedure.

DML commands can be rolled back. For example, if you start a new session and run an INSERT statement, you can undo that with a ROLLBACK statement, because the INSERT statement is a DML command.

TCL – Transaction Control Language

TCL stands for Transaction Control Language and includes statements that are used to manage the changes that are made from DML statements. It enhances the transactional nature of Oracle SQL.

The TCL commands in Oracle SQL are:

COMMIT: saves any changes made to the database.
ROLLBACK: undoes any changes made to the database.
SAVEPOINT: creates a point in your transaction to which you can roll back to.
SET TRANSACTION: Sets different options for your transaction, such as isolation level.
SET CONSTRAINT: Sets whether a deferrable constraint is checked for a specific transaction.

The concept of transactions is something I’ll discuss in another post (or several posts), but the TCL commands relate to transactions.

If you try to exit your IDE after running some DML commands (e.g. UPDATE), you could get asked if you want to commit or rollback your changes.

This screenshot here is from Oracle’s SQL Developer.

Selecting Commit or Rollback will run a Commit or Rollback command on the transaction on the tab in the application.

Session Control Statements

There are two session control statements, which manage the properties of a user session. They do not include a COMMIT of the transaction.

These two statements are:

ALTER SESSION: Change the properties of the current session.
SET ROLE: Enable and disable roles for the system.

System Control Statements

Finally, there is a single system control statement that does not fit into the other categories.

ALTER SYSTEM: manage the properties of the Oracle database.

It does not include a COMMIT.

So, those are the differences between DML, DDL, and TCL statements in Oracle SQL. I’ve also included a couple of other statement categories to make things clear. You can learn more about these statements and how to use them in many different SQL tutorials, which I’ve listed here, including my Introduction to Oracle SQL tutorial.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!