Introduction to DML and DDL commands in Oracle

DML commands in Oracle and DDL commands in Oracle deals with the data and database objects respectively. Meaning, DML commands tells the database to insert, delete, update data whereas the DDL commands instruct Oracle to create, update, delete the objects containing the data in it.

We have come across Oracle SELECT queries till now. A SELECT query to fetch all the id, full names, numbers from employees table would look like the following.

select employee_id
     , first_name || ' ' || last_name "Full Name"
     , phone_number
  from employees;

These are the SELECT Queries. These queries neither modify data or Oracle database objects.

DML commands in Oracle

Sometimes we may want to do insert new data, delete some old data or update old data to latest. We need the DML Commands in Oracle for the same. The Oracle DML commands are listed and briefly explained below.

• INSERT
• UPDATE
• DELETE

DDL commands in Oracle

There are situations when we want to remove a table from the Oracle database, add a column to an existing table or even changing the datatype of a column etc. Whether we want to rename a database object or completely remove from the Oracle database, we need the Data Definition Language also known as DDL commands in Oracle.

• CREATE
• ALTER
• DROP
• TRUNCATE

Let us understand the DDL and DML commands in Oracle in step by step with syntax.

First of all, let us CREATE a TABLE. We need to know CREATE TABLE DDL Command for the same.

DML & DDL Commands in Oracle with Syntax & Examples

CREATE TABLE DDL Commands In Oracle

CREATE TABLE command allows creating a new table in the Oracle database. Here is its Syntax with examples.

CREATE TABLE Syntax & Example

create table table_name(
column1 datatype1,
column2 datatype2,
column3 datatype3,
...
...
column1 datatypeN
);

Let us create a student_t table. The table contains student_name, student_sex, student_phone number of datatypes varchar2(30), char(1) and number(10) respectively.

create table student_t(
 student_name varchar2(30),
 student_sex char(1),
 student_phone number(10)
);

Result:
Table STUDENT_T created.

Also understand: What are the differences between varchar2 and char datatype in Oracle?

INSERT INTO – DML Commands In Oracle

We created the student_t table. Now, we will insert a row into that table using Oracle INSERT INTO command.

The syntax for the INSERT INTO command is as follows.

insert into table_name(column1, column2, column3, ..., columnN)
values(value1, value2, value3, ..., valueN);

So, here is the query to insert a row or record into the student_t table.

insert into student_t(student_name, student_sex, student_phone)
values('Sameer', 'M', 8249410773);

Result:
1 row inserted.

ALTER – DDL Commands In Oracle

ALTER Command is one of the DDL Commands in Oracle which helps to modify database objects.

Let us say we want to store the country code along with the phone number. Hence the student_phone number(10) datatype needs to be changed. We need to modify the Oracle table student_t’s student_phone datatype to the number(12). So, how do you do that?

Here is the existing student_t structure.

DESC student_t;

Result:
Name          Null? Type         
------------- ----- ------------ 
STUDENT_NAME        VARCHAR2(30) 
STUDENT_SEX         CHAR(1)      
STUDENT_PHONE       NUMBER(10)

Data inside the student_t is as follows.

select * from student_t

Result:
STUDENT_NAME                   S STUDENT_PHONE
------------------------------ - -------------
Sameer                         M    8249410773

We can change the column datatype with the help of ALTER TABLE command.

alter table student_t modify
    student_phone number(12);

DESC student_t;

Result:
Name          Null? Type 
------------- ----- ------------ 
STUDENT_NAME        VARCHAR2(30) 
STUDENT_SEX         CHAR(1) 
STUDENT_PHONE       NUMBER(12)

The column student_phone can now hold 12 digit number i.e. 918249410773. We did not have to remove the data and change. We can always change the datatype to higher if the data present in that column is compliant to the new datatype.

For more please visit, ALTER DDL Commands in Oracle with syntax and examples.

UPDATE – DML Commands In Oracle

UPDATE Oracle command helps to update a row or bunch of rows in a table. Let us see the syntax for the update and understand it with an example.

update table_name
set column_name = value
where ...;

Now we will change the mobile number of Sameer from 8249410773 to 918249401773. We can achieve that with the following query.

update student_t
set student_phone = 918249410773
where student_name = 'Sameer';

Result:
1 row updated.

Now if you query the student_t table. It will show you the updated phone number. As we manipulated or changed or modified data we call this as one on the DML Commands in SQL.

Please note that it did not change the structure or table metadata. It just updated the data inside the table.

DELETE – DML Commands In Oracle

Now let us say we do not need the student_t data anymore. We want to delete or clean up the data. How do we do that?

The answer is the DELETE statement. The DELETE command is one of the DML commands which allows to delete or remove rows from a table in Oracle database.

DELETE Syntax & Example

Delete from table_name where condition;

The query to delete the student with student_name Sameer would be as follows.

Delete from student_t where student_name = ‘Sameer’;

Now let us query the student_t table using “select * from student_t”. Well, it is having no data. Expected right !!

Now I want you to create a new session and try to run the same select query. Please keep the existing one open. You will see the data. The reason is we have not told Oracle database to make the changes permanent. We do that using the TCL Commands in Oracle.

Please note that we need to issue COMMIT command to make permanent changes to Oracle database for DML commands (INSERT, UPDATE, DELETE).

Also understand: What is the difference between DELETE and TRUNCATE command in Oracle?

DROP – DDL Commands In Oracle

DROP command removes the object from the Oracle database. So, if the object is removed then data inside the objects are also removed. Let us see DROP TABLE syntax in Oracle.

DROP Table Syntax & Example

DROP TABLE table_name;

We need to use the following query to remove the table object from the database. Here is the query to drop the table or remove the table from the Oracle database.

drop table student_t;

Also understand: How to recover a dropped table in Oracle?

TRUNCATE – DDL Commands In Oracle

Oracle TRUNCATE DDL Command removes all the rows from the Oracle table. It is faster than a DELETE on a table without the where clause.

TRUNCATE Syntax & Example

truncate table table_name;

Now let us consider the students table. We could have deleted the records in that table using DELETE DML Command without a where clause. But, it is faster and has other benefits as well. The query for the same would be as follows.

truncate tabe students;

We do not need to issue COMMIT command for TRUNCATE. However, we need to execute the COMMIT ( DCL Commands in Oracle ) for DELETE.

Also understand: Can we remove all the rows of a table without using truncate or delete command?

Please note that this oracle tutorial post is to give you a brief idea on DML Commands in Oracle and DDL Commands in Oracle. There are other commands like DCL Commands, TCL commands in Oracle. We will discuss those in the later section.