Difference Between DDL, DML and DCL Commands
Whether you are a Developer or DBA, you need to understand DDL, DML and DCL commands. Once you understand the difference, you can create & alter database objects, you can manipulate the data in database and many more database transactions.
Lets see what are the DDL, DML & DCL commands and their use in Oracle Database.
1) Data Definition Language (DDL)
Data Definition Language commands are used to define the structure or schema of a database.
CREATE: This command is used to create database objects like (TABLE, VIEW and INDEX etc.)
ALTER: This command alters the structure of database.
DROP: Removes objects from database.
TRUNCATE: To remove all rows from a Table. (Can’t be rolled back)
COMMENT: To add comments to the data dictionary.
RENAME: To rename an Object.
2) Data Manipulation Language (DML)
Data Manipulation Language commands are used for managing or manipulating data within schema objects.
SELECT: To retrieves data from the database and objects.
INSERT: To insert records into a Table.
UPDATE: To update existing data within a Table.
DELETE: To remove records from a Table.
MERGE: To create UPSERT operation (Insert or Update)
CALL: To call a PL/SQL or Java Program
EXPLAIN PLAN: It explains access path to data.
LOCK TABLE: To control concurrency
3) Data Control Language (DCL)
Data Control Language commands are used to control the database objects.
GRANT: To give users access privileges to database.
REVOKE: Withdrawal access privileges given by the GRANT command.
4) Transaction Control (TC)
These commands manage the changes made by DML commands.
COMMIT: To make changes permanent to database.
ROLLBACK: To restore database to its original state since the last COMMIT occurred.
SAVEPOINT: You can identify a point in a transaction to which you can later on Rollback.
SET TRANSACTION: This option is used to change transaction options like isolation level etc.
If I have missed some commands, write below in comment section. We will incorporate the same in this tutorial.