Types SQL/PLSQL Statement

In this blog, we will be learning the classification of SQL Statements -

Classifications are as follows -

1. DDL - These are Data Definition Language. Some references also say, these are Data Description Language. It mainly deals with Database structures and Objects creation. It doesn't deal with Data, only the structure of database objects. 

Exmaples of DDL Statements are as follows -

• CREATE
• DROP
• ALTER 
• TRUNCATE -  Explains below , as this is a special type.

2. DML - DML Stands for Data Manipulation Statements. It deals with the manipulation of Data in Oracle. Examples of DML are as follows -

• SELECT
• INSERT
• UPDATE
• DELETE
• MERGE
• CALL - These statements are used to CALL a function or any other subprograms.
• EXPLAN PLAN -  These are used to interpret the way of accessing the Data. Most often, we use Explan Plan for Performance Tuning.

3.  DCL - DCL Stands for Data Control Language. It deals with controlling the Access to Data. Examples of DCL are as follows - 

• GRANT
• REVOKE

4. TCL - TCL Stands for Transaction Control Language. It mainly deals with managing transactions in the Database. Transaction means any operations made on the Data. Examples of TCL are as follows -

• COMMIT
• SAVEPOINT
• ROLLBACK

Below are the frequent questions which one should be aware of - 

DDL Statements do not require explicit commit - why ?

Before starting any answer, let go back to Data Dictionary -  Data Dictionary in a Database are nothing but flatfiles which contains Metadata i.e Data about the Data. Mainly Data Dictionary contains the following -

• Names of all the Tables, along with their owners, constraints and relationships with other relational database objects, how it is organised across Partitions, if any, etc.  If you query DBA_TABLES, ALL_OBJECTS, etc, you will get these metadata details 

Please see the below screenshot - 

The reason, why DDL statements do not require explicit Commit is rather a difficult question - I will try to jot down a few points, may be that will help -

• DDL Statements directly writes to Data Dictionary. 
• Architecture was made long back - Thinking should be like this - Nobody will try rolling back ( saving the intermediate transaction in any segments ) while creating database objects.  If they want they can drop the Structure, so no need of undo logs, etc.
• Since DDL affects Data Dictionary, its affecting query running sessions.

DELETE - DDL or DML - Many opinions are there - ?

DML deals with Manipulation of Data, theirs nothing to do with structure of the database. Keeping in mind this, we can say, DELETE deletes the Data and not the Structure of the Data. Tables remain as it is - hence, DELETE is DML.

What about TRUNCATE  - DML or DDL? 

Truncate deletes the Data ( all the Data ) from a table without saving any intermediate forms in rollback segments, thus doesn't requires explicit Commit.  Although, it doesn't act on Database structures but on Data ( deleting or manipulating the Data ), Oracle classify Truncate as DDL, not a DML. Some references say, its a special type of DML/DDL, etc.

We will talk more about Truncate in "Differences between Drop, Delete and Truncate"





Well, that's all for now - Thank You